OGLED z MATCH | Ustvarite prilagodljivo formulo z VLOOKUP MATCH

Formula Vlookup deluje le, če se matrična tabela v formuli ne spremeni, če pa je v tabelo vstavljen nov stolpec ali je stolpec izbrisan, formula da napačen rezultat ali odraža napako, da formula v njej ne vsebuje napak. v takih dinamičnih situacijah uporabljamo funkcijo ujemanja, da dejansko ujemamo indeks podatkov in vrnemo dejanski rezultat.

Združite VLOOKUP z Match

Formula vlookup je najpogosteje uporabljena funkcija, ki se uporablja za iskanje in vrnitev enake vrednosti v podanem indeksu stolpca ali vrednosti iz drugega indeksa stolpca s sklicevanjem na ujemajočo se vrednost iz prvega stolpca. Glavni izziv, s katerim se soočamo pri uporabi vlookupa, je, da je indeks stolpca, ki ga je treba določiti, statičen in nima dinamične funkcionalnosti. Še posebej, če delate na več kriterijih, ki zahtevajo ročno spreminjanje indeksa referenčnega stolpca. Tako je ta potreba izpolnjena z uporabo formule "MATCH" za boljši oprijem ali nadzor pogosto spreminjajočega se indeksa stolpca v formuli VLOOKUP.

VLookup in Formula tekme

# 1 - Formula VLOOKUP

Formula funkcije VLOOKUP v Excelu

Tu so vsi argumenti, ki jih je treba vnesti, obvezni.

  • Lookup_value - Tu je treba vnesti referenčno celico ali besedilo z dvojnimi narekovaji, da se prepozna v obsegu stolpcev.
  • Matrika tabele -   Ta argument zahteva, da se vnese obseg tabele, kjer je treba iskati Lookup_value in podatki, ki jih je treba pridobiti, se nahajajo v določenem obsegu stolpcev.
  • Col_index_num - V tem argumentu je treba vnesti indeksno številko stolpca ali število stolpcev iz referenčnega prvega stolpca, iz katerega je treba potegniti ustrezno vrednost z istega položaja kot vrednost, ki jo iščete v prvem stolpcu.
  • [Range_lookup] - Ta argument bo imel dve možnosti.
  • TRUE - Približno ujemanje: - Argument je mogoče vnesti kot TRUE ali številčno “1”, kar vrne približno ujemanje, ki ustreza referenčnemu stolpcu ali prvemu stolpcu. Poleg tega je treba vrednosti v prvem stolpcu polja tabele razvrstiti po naraščajočem vrstnem redu.
  • FALSE - Natančno ujemanje: - Tu je argument, ki ga je treba vnesti, lahko FALSE ali številčno “0”. Ta možnost bo vrnila le natančno ujemanje vrednosti, ki ustreza prepoznavanju iz položaja v območju prvega stolpca. Če ne iščete vrednosti iz prvega stolpca, se prikaže sporočilo o napaki »# N / A«.

# 2 - Formula tekme

Funkcija Match vrne položaj celice vrednosti, vnesene za dano matrično tabelo.

Vsi argumenti v sintaksi so obvezni.

  • Lookup_value - Tu je lahko vneseni argument sklic na celico vrednosti ali besedilni niz z dvojnimi narekovaji, katerih položaj celice je treba potegniti.
  • Lookup_array - Vnesti je treba obseg matrike za tabelo, katere vrednost ali vsebina celice naj bo identificirana.
  • [vrsta ujemanja] - Ta argument ponuja tri možnosti, kot je razloženo spodaj.
  • "1-manj kot" - tu je argument, ki ga je treba vnesti, številčni "1", ki bo vrnil vrednost, ki je manjša ali enaka iskalni vrednosti. In tudi iskalno polje je treba razvrstiti po naraščajočem vrstnem redu.
  • "0-natančno ujemanje" - Tu mora biti argument, ki ga je treba vnesti, številka "0". Ta možnost bo vrnila natančen položaj ujemajoče se vrednosti iskanja. Iskalno polje je lahko v poljubnem vrstnem redu.
  • "-1-večje od" -  Argument, ki ga je treba vnesti, mora biti številka "-1". Tretja možnost poišče najmanjšo vrednost, ki je večja ali enaka iskalni vrednosti. Tu je treba vrstni red iskalnega polja postaviti v padajočem vrstnem redu.

# 3 - OGLED s formulo MATCH

= VLOOKUP (lookup_value, tabela_ matrika, MATCH (lookup_value, lookup_array, [match_type]), [iskanje po obsegu])

Kako uporabiti VLOOKUP s formulo ujemanja v Excelu?

Spodnji primer vam bo pomagal razumeti delovanje vlookupa in formule ujemanja pri sestavljanju.

Ta VLookup z ujemanje predloge Excel lahko prenesete tukaj - VLookup z ujemanje predloge Excel

Upoštevajte spodnjo podatkovno tabelo, ki opisuje specifikacije določenega vozila, ki ga želite kupiti.

Da bomo dobili jasnost kombinirane funkcije za vlookup in funkcijo ujemanja, nam dovolite, da razumemo, kako deluje posamezna formula, in nato skupaj dobimo rezultate ujemanja vlookup.

1. korak - Uporabimo formulo vlookup na posamezni ravni, da pridemo do rezultata.

Rezultat je prikazan spodaj:

Tu se vrednost iskanja nanaša na $ B9, ki je model "E", in iskalno polje je podano kot obseg podatkovne tabele z absolutno vrednostjo "$", indeks stolpca pa na stolpec "4", kar je število za stolpec "Type" in iskanje obsega se natančno ujema.

Tako se uporabi naslednja formula za vrnitev vrednosti za stolpec "Gorivo".

Rezultat je prikazan spodaj:

Tu iskalna vrednost z absolutnim nizom »$«, uporabljena za iskalno vrednost in lookup_array, pomaga popraviti referenčno celico, tudi če se formula kopira v drugo celico. V stolpcu »Gorivo« moramo spremeniti indeks stolpca na »5«, saj se spremeni vrednost, iz katere so potrebni podatki za pridobivanje.

2. korak -  Zdaj uporabimo formulo Match, da pridobimo položaj za dano vrednost iskanja.

Rezultat je prikazan spodaj:

Kot je razvidno iz zgornjega posnetka zaslona, ​​tukaj poskušamo pridobiti položaj stolpca iz polja tabele. V tem primeru je številka stolpca, ki jo je treba izvleči, označena kot celica C8, ki je stolpec "Tip", obseg iskanja, ki ga je treba iskati, pa je podan kot obseg glav stolpcev, vrsta ujemanja pa natančno, kot " 0 ".

Tako bo spodnja tabela dala želeni rezultat za položaje stolpca "Gorivo".

Zdaj je tukaj stolpec, ki ga želimo iskati, celica D8, želeni indeks stolpca pa se vrne v "5".

3. korak - Zdaj bo formula Match uporabljena v funkciji vlookup za pridobitev vrednosti iz identificiranega položaja stolpca.

Rezultat je prikazan spodaj:

V zgornji formuli je funkcija ujemanja namesto parametra indeksa stolpca funkcije vlookup. Tu bo funkcija ujemanja identificirala referenčno celico iskalne vrednosti “C8” in vrnila številko stolpca skozi dano matrično matriko. Ta položaj stolpca bo namenjen vhodu v argument indeksa stolpca v funkciji vlookup. Kaj pa bo vlookup pomagalo prepoznati vrednost, ki jo je treba vrniti iz rezultatske številke indeksa stolpca?

Podobno smo uporabili vlookup s formulo ujemanja tudi za stolpec "Gorivo".

Rezultat je prikazan spodaj:

Tako lahko to kombinacijsko funkcijo uporabimo tudi za druge stolpce "Vrsta" in "Gorivo".

Stvari, ki si jih je treba zapomniti

  • VLOOKUP je mogoče uporabiti za iskalne vrednosti samo na njegovi skrajni levi strani. Vse vrednosti, ki jih je treba iskati na desni strani podatkovne tabele, bodo vrnile vrednost napake "# N / A".
  • Obseg table_array, vnesenega v drugem argumentu, mora biti absolutna referenca celice "$", to bo ohranilo fiksno območje polja tabele pri uporabi iskalne formule za druge celice, sicer pa se bodo referenčne celice za obseg polja tabele premaknile v naslednjo celico sklic.
  • Vrednost, vnesena v iskalno vrednost, ne sme biti manjša od najmanjše vrednosti v prvem stolpcu polja tabele, sicer pa bo funkcija vrnila vrednost napake "# N / A".
  • Preden v zadnjem argumentu uporabite približno ujemanje »TRUE« ali »1«, vedno ne pozabite razvrstiti polja tabele v naraščajočem vrstnem redu.
  • Funkcija ujemanja vrne le položaj vrednosti v matriki tabele vlookup in ne vrne vrednosti.
  • Če funkcija ujemanja ne more določiti položaja iskalne vrednosti v matriki tabele, potem formula vrne "# N / A" v vrednosti napake.
  • Funkcije iskanja in ujemanja ne razlikujeta med velikimi in malimi črkami, če ujemate vrednost iskanja z ujemajočo se besedilno vrednostjo v tabeli.