Af hverju INDEX og MATCH eru betri en VLOOKUP í Excel

Við höfum áður útskýrt fyrir byrjendum hvernig á að nota grunnaðgerðir VLOOKUP (enska VLOOKUP, skammstöfunin stendur fyrir „lóðrétt uppflettingaraðgerð“). Og reyndum notendum voru sýndar nokkrar flóknari formúlur.

Og í þessari grein munum við reyna að gefa upplýsingar um aðra aðferð til að vinna með lóðrétta leit.

Þú gætir verið að velta fyrir þér: "Af hverju er þetta nauðsynlegt?". Og þetta er nauðsynlegt til að sýna allar mögulegar leitaraðferðir. Að auki koma fjölmargar VLOOKUP takmarkanir oft í veg fyrir að viðkomandi niðurstaða fáist. Í þessu sambandi er INDEX( ) MATCH( ) miklu virkari og fjölbreyttari og þeir hafa einnig færri takmarkanir.

Grunnatriði INDEX MATCH

Þar sem tilgangur þessarar handbókar er að sýna hversu góður þessi eiginleiki er, við Við skulum skoða helstu upplýsingar um meginreglur um starfsemi þess. Og við munum sýna dæmi, og einnig íhuga hvers vegna, það er betra en VLOOKUP ().

INDEX Falla setningafræði og notkun

Þessi aðgerð hjálpar til við að finna æskilegt gildi meðal tilgreindra leitarsvæða byggt á dálknum eða línunúmerinu. Setningafræði:

=INDEX(fylki, línunúmer, dálknúmer):

• fylki – svæðið þar sem leitin fer fram;
• línunúmer – númer línunnar sem leita á í tilgreindu fylki. Ef línunúmerið er óþekkt þarf að tilgreina dálknúmerið;
• dálknúmer – númer dálksins sem er að finna í tilgreindu fylki. Ef gildið er óþekkt þarf línunúmer.

Dæmi um einfalda formúlu:

=VÍSITALA(A1:S10,2,3;XNUMX)

Aðgerðin mun leita á bilinu frá A1 til C10. Tölurnar sýna úr hvaða röð (2) og dálk (3) á að sýna æskilegt gildi. Niðurstaðan verður reit C2.

Frekar einfalt, ekki satt? En þegar þú vinnur með raunveruleg skjöl er ólíklegt að þú hafir upplýsingar um dálkanúmer eða frumur. Til þess er MATCH() fallið.

MATCH falla setningafræði og notkun

MATCH() aðgerðin leitar að æskilegu gildi og sýnir áætlaða tölu þess á tilgreindu leitarsvæði.

Setningafræði searchpos() lítur svona út:

=MATCH(gildi til að fletta, fylki til að fletta, samsvörunartegund)

• leitargildi – númerið eða textinn sem á að finna;
• leitað fylki – svæðið þar sem leitin fer fram;
• samsvörunartegund – tilgreinir hvort leita eigi að nákvæmu gildinu eða þeim gildum sem eru næst því:
  • 1 (eða ekkert gildi tilgreint) – skilar stærsta gildinu sem er jafnt eða minna en gildið sem var tilgreint;
  • 0 – sýnir nákvæma samsvörun við gildið sem leitað er að. Í samsetningunni INDEX() MATCH() þarftu næstum alltaf nákvæma samsvörun, svo við skrifum 0;
  • -1 – Sýnir minnsta gildi sem er stærra en eða jafnt gildinu sem tilgreint er í formúlunni. Flokkun fer fram í lækkandi röð.

Til dæmis, á bilinu B1:B3 New York, París, London eru skráð. Formúlan hér að neðan mun sýna töluna 3 vegna þess að London er í þriðja sæti listans:

=EXPOSE(London,B1:B3,0)

Hvernig á að vinna með INDEX MATCH aðgerðina 

Þú hefur líklega þegar byrjað að skilja meginregluna sem byggir á sameiginlegu starfi þessara aðgerða. Í stuttu máli þá INDEX() leitar að æskilegu gildi meðal tilgreindra raða og dálka. Og MATCH() sýnir tölur þessara gilda:

=INDEX(dálkur sem gildið er skilað úr, MATCH(gildi til að leita, dálkur til að leita í, 0))

Áttu enn erfitt með að skilja hvernig það virkar? Kannski skýrir dæmi betur. Segjum sem svo að þú sért með lista yfir höfuðborgir heimsins og íbúa þeirra:

Til þess að komast að stærð íbúa ákveðinnar höfuðborgar, til dæmis höfuðborgar Japans, notum við eftirfarandi formúlu:

=INDEX(C2:C10, MATCH(Japan, A2:A10,0))

Útskýring:

• MATCH() fallið leitar að gildinu – „Japan“ í fylkinu A2:A10 og skilar tölunni 3, því Japan er þriðja gildið á listanum. 
• Þessi tala fer tillínanúmer” í INDEX() formúlunni og segir fallinu að prenta gildi úr þessari línu.

Svo ofangreind formúla verður staðlað formúla INDEX(C2:C10,3;XNUMX). Formúlan leitar úr hólfum C2 til C10 og skilar gögnum úr þriðja hólfinu á þessu bili, það er C4, vegna þess að niðurtalningin byrjar í annarri röð.

Viltu ekki skrifa fyrir nafn borgarinnar í formúlunni? Skrifaðu það síðan í hvaða reit sem er, segðu F1, og notaðu það sem tilvísun í MATCH() formúluna. Og þú endar með kraftmikla leitarformúlu:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Mikilvægt! Fjöldi lína inn array INDEX() verður að vera það sama og fjöldi lína í talið fylki í MATCH(), annars færðu ranga niðurstöðu.

Bíddu aðeins, hvers vegna ekki bara að nota VLOOKUP() formúluna?

=FLOOKUP(F1, A2:C10, 3, Ósatt)

 Hver er tilgangurinn með því að eyða tíma í að reyna að átta sig á öllum þessum margbreytileika INDEX MATCH?

Í þessu tilviki skiptir ekki máli hvaða aðgerð á að nota. Þetta er bara dæmi til að skilja hvernig INDEX() og MATCH() aðgerðir vinna saman. Önnur dæmi munu sýna hvað þessar aðgerðir geta í aðstæðum þar sem VLOOKUP er máttlaus. 

INDEX MATCH eða VLOOKUP

Þegar þeir ákveða hvaða leitarformúlu á að nota eru margir sammála um að INDEX() og MATCH() séu miklu betri en VLOOKUP. Hins vegar nota margir enn VLOOKUP(). Í fyrsta lagi er VLOOKUP() einfaldara og í öðru lagi skilja notendur ekki til fulls alla kosti þess að vinna með INDEX() og MATCH(). Án þessarar þekkingar mun enginn fallast á að eyða tíma sínum í að rannsaka flókið kerfi.

Hér eru helstu kostir INDEX() og MATCH() yfir VLOOKUP():

• Leitaðu frá hægri til vinstri. VLOOKUP() getur ekki leitað frá hægri til vinstri, þannig að gildin sem þú ert að leita að verða alltaf að vera í dálkum töflunnar lengst til vinstri. En INDEX() og MATCH() ráða við þetta án vandræða. Þessi grein mun segja þér hvernig það lítur út í reynd: hvernig á að finna viðeigandi gildi vinstra megin.

1. Örugg viðbót eða fjarlæging dálka. VLOOKUP() formúlan sýnir rangar niðurstöður þegar dálkum er fjarlægt eða bætt við vegna þess að VLOOKUP() þarf nákvæmlega dálknúmerið til að ná árangri. Auðvitað, þegar dálkum er bætt við eða fjarlægð, breytist fjöldi þeirra líka. 

Og í formúlunum INDEX() og MATCH() er úrval dálka tilgreint, ekki einstakir dálkar. Fyrir vikið geturðu örugglega bætt við og fjarlægt dálka án þess að þurfa að uppfæra formúluna í hvert sinn.

1. Engar takmarkanir á leitarmagni. Þegar VLOOKUP() er notað, má heildarfjöldi leitarskilyrða ekki vera meiri en 255 stafir eða þá færðu #VALUE! Þannig að ef gögnin þín innihalda mikinn fjölda stafa, þá eru INDEX() og MATCH() besti kosturinn.
2. Hár vinnsluhraði. Ef borðin þín eru tiltölulega lítil, þá er ólíklegt að þú taki eftir neinum mun. En ef taflan inniheldur hundruð eða þúsundir lína, og í samræmi við það eru hundruðir og þúsundir formúla, munu INDEX () og MATCH () takast á við mun hraðar en VLOOKUP (). Staðreyndin er sú að Excel mun aðeins vinna úr dálkunum sem tilgreindir eru í formúlunni, í stað þess að vinna alla töfluna. 

Afköst áhrif VLOOKUP() verða sérstaklega áberandi ef vinnublaðið þitt inniheldur mikinn fjölda formúla eins og VLOOKUP() og SUM(). Aðskildar athuganir á VLOOKUP() aðgerðunum eru nauðsynlegar til að flokka hvert gildi í fylki. Excel þarf því að vinna mikið magn upplýsinga og það hægir verulega á vinnunni.

Formúludæmi 

Við höfum þegar fundið út gagnsemi þessara aðgerða, svo við getum haldið áfram að áhugaverðasta hlutanum: notkun þekkingar í reynd.

Formúla til að leita frá hægri til vinstri

Eins og áður hefur komið fram getur VLOOKUP ekki framkvæmt þessa leit. Svo, ef æskileg gildi eru ekki í dálknum lengst til vinstri, mun VLOOKUP() ekki gefa niðurstöðu. INDEX() og MATCH() aðgerðirnar eru fjölhæfari og staðsetning gildanna spilar ekki stórt hlutverk til að þau virki.

Til dæmis munum við bæta við röðunardálki vinstra megin við töfluna okkar og reyna að reikna út hvaða stöðu höfuðborg landsins okkar er með tilliti til íbúafjölda.

Í reit G1 skrifum við gildið sem á að finna og notum síðan eftirfarandi formúlu til að leita á bilinu C1:C10 og skila samsvarandi gildi frá A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Hvetja. Ef þú ætlar að nota þessa formúlu fyrir margar frumur, vertu viss um að laga sviðin með því að nota alger heimilisfang (til dæmis, $A$2: $A$10 og $C$2: 4C$10).

VÍSITALA MEIRA FYRIR MEIRA  að leita í dálkum og línum

Í ofangreindum dæmum höfum við notað þessar aðgerðir í staðinn fyrir VLOOKUP() til að skila gildum úr fyrirfram skilgreindu línubili. En hvað ef þú þarft að gera fylkisleit eða tvíhliða leit?

Það hljómar flókið, en formúlan fyrir slíka útreikninga er svipuð stöðluðu INDEX() MATCH() formúlunni, með aðeins einum mun: MATCH() formúluna verður að nota tvisvar. Í fyrra skiptið til að fá línunúmerið og í seinna skiptið til að fá dálknúmerið:

=INDEX(fylki, MATCH(lóðrétt leitargildi, leitardálkur, 0), MATCH(lárétt leitargildi, leitarlína, 0))

Skoðum töfluna hér að neðan og reynum að búa til formúlu INDEX() EXPRESS() EXPRESS() til að birta lýðfræði í tilteknu landi fyrir valið ár.

Marklandið er í reit G1 (lóðrétt uppfletting) og markárið er í reit G2 (lárétt uppfletting). Formúlan mun líta svona út:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Hvernig þessi formúla virkar

Eins og með allar aðrar flóknar formúlur er auðveldara að skilja þær með því að skipta þeim niður í einstakar jöfnur. Og þá geturðu skilið hvað hver einstök aðgerð gerir:

• MATCH(G1,A2:A11,0) – leitar að gildi (G1) á bilinu A2:A11 og sýnir númer þessa gildis, í okkar tilviki er það 2;
• SEARCH(G2,B1:D1,0) – leitar að gildi (G2) á bilinu B1:D1. Í þessu tilviki var niðurstaðan 3.

Línu- og dálknúmerin sem fundust eru send á samsvarandi gildi í INDEX() formúlunni:

=VÍSITALA(B2:D11,2,3;XNUMX;XNUMX)

Þar af leiðandi höfum við gildi sem er í reit á skurðpunkti 2 lína og 3 dálka á bilinu B2:D11. Og formúlan sýnir æskilegt gildi, sem er í reit D3.

Leitaðu eftir mörgum skilyrðum með INDEX og MATCH

Ef þú hefur lesið leiðbeiningar okkar um VLOOKUP(), hefur þú líklega prófað margar leitarformúlur. En þessi leitaraðferð hefur eina marktæka takmörkun - þörfina á að bæta við viðbótardálki.

En góðu fréttirnar eru þær Með INDEX() og MATCH() geturðu leitað að mörgum skilyrðum án þess að þurfa að breyta eða breyta vinnublaðinu þínu.

Hér er almenna fjölskilyrða leitarformúlan fyrir INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Athugasemdin: þessa formúlu verður að nota ásamt flýtilykla CTRL+SHIFT+ENTER.

Segjum að þú þurfir að finna gildið sem þú ert að leita að út frá 2 skilyrðum: Kaupandi и Vara.

Þetta krefst eftirfarandi formúlu:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Í þessari formúlu er C2:C10 sviðið þar sem leitin fer fram, F1 - þetta ástand, A2:A10 — er svið til að bera saman ástandið, F2 – skilyrði 2, V2:V10 – svið til að bera saman ástand 2.

Ekki gleyma að ýta á samsetninguna í lok vinnunnar með formúlunni CTRL+SHIFT+ENTER - Excel mun sjálfkrafa loka formúlunni með krulluðum axlaböndum, eins og sýnt er í dæminu:

Ef þú vilt ekki nota fylkisformúlu fyrir vinnu þína, bættu svo annarri INDEX() við formúluna og ýttu á ENTER, það mun líta út eins og í dæminu:

Hvernig þessar formúlur virka

Þessi formúla virkar á sama hátt og staðlaða INDEX() MATCH() formúlan. Til að leita að mörgum skilyrðum býrðu einfaldlega til margar rangar og sannar aðstæður sem tákna réttar og rangar einstakar aðstæður. Og þá eiga þessi skilyrði við um alla samsvarandi þætti fylkisins. Formúlan breytir ósönnum og réttum rökum í 0 og 1, í sömu röð, og gefur út fylki þar sem 1 er samsvarandi gildi sem fundust í strengnum. MATCH() finnur fyrsta gildið sem passar við 1 og sendir það í INDEX() formúluna. Og það mun aftur á móti skila þegar æskilegu gildi í tilgreindri línu frá viðkomandi dálki.

Formúla án fylkis er háð getu INDEX() til að höndla þær á eigin spýtur. Önnur INDEX() í formúlunni samsvarar falsy (0), þannig að hún sendir allt fylkið með þessum gildum yfir í MATCH() formúluna. 

Þetta er frekar löng útskýring á rökfræðinni á bak við þessa formúlu. Fyrir frekari upplýsingar lesið greinina “INDEX MATCH með mörgum skilyrðum'.

AVERAGE, MAX og MIN í INDEX og MATCH

Excel hefur sínar sérstöku aðgerðir til að finna meðaltöl, hámark og lágmark. En hvað ef þú vilt fá gögn úr reitnum sem tengist þessum gildum? Í þessu tilfelli AVERAGE, MAX og MIN verður að nota í tengslum við INDEX og MATCH.

INDEX MATCH og MAX

Til að finna stærsta gildið í dálki D og birta það í dálki C, notaðu formúluna: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH og MIN

Til að finna minnsta gildið í dálki D og birta það í dálki C, notaðu eftirfarandi formúlu:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX og SERPENT

Til að finna meðalgildið í dálki D og birta þetta gildi í C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Það fer eftir því hvernig gögnin þín eru skrifuð, þriðja rökin fyrir MATCH() er annað hvort 1, 0 eða -1:

• ef dálkarnir eru flokkaðir í hækkandi röð, stilltu 1 (þá mun formúlan reikna út hámarksgildi, sem er minna en eða jafnt og meðalgildi);
• ef flokkunin er lækkandi, þá -1 (formúlan mun gefa út lágmarksgildi sem er stærra en eða jafnt og meðaltalinu);
• ef uppflettifylki inniheldur gildi sem er nákvæmlega jafnt meðaltali, stilltu það á 0. 

 Í dæminu okkar er þýðinu raðað í lækkandi röð, þannig að við setjum -1. Og niðurstaðan er Tókýó, þar sem íbúagildið (13,189) er næst meðalgildinu (000).

VLOOKUP() getur líka framkvæmt slíka útreikninga, en aðeins sem fylkisformúla: VLOOKUP með AVERAGE, MIN og MAX.

INDEX MATCH og ESND/IFERROR

Þú hefur sennilega þegar tekið eftir því að ef formúlan getur ekki fundið viðeigandi gildi, veldur hún villu # N / A. Þú getur skipt út venjulegu villuboðunum fyrir eitthvað meira upplýsandi. Til dæmis, stilltu rökin í formúlunni Í XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Með þessari formúlu, ef þú slærð inn gögn sem eru ekki í töflunni, mun eyðublaðið gefa þér tilgreind skilaboð.

Ef þú vilt ná öllum villum, þá nema fyrir Í XNUMX Hægt er að nota IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Eitthvað fór úrskeiðis!")

En mundu að gríma villur á þennan hátt er ekki góð hugmynd, því staðlaðar villur tilkynna brot í formúlunni.

Við vonum að þér hafi fundist handbókin okkar um notkun INDEX MATCH() aðgerðarinnar gagnleg.