Miks on INDEX ja MATCH paremad kui VLOOKUP Excelis?

Oleme eelnevalt selgitanud algajatele, kuidas kasutada VLOOKUP (inglise keeles VLOOKUP, lühend tähistab "vertikaalse otsingu funktsiooni") põhifunktsioone. Ja kogenud kasutajatele näidati mitmeid keerulisemaid valemeid.

Ja selles artiklis püüame anda teavet teise vertikaalse otsinguga töötamise meetodi kohta.

Võite küsida: "Miks see vajalik on?". Ja see on vajalik kõigi võimalike otsingumeetodite kuvamiseks. Lisaks takistavad paljud VLOOKUP-i piirangud sageli soovitud tulemuse saavutamist. Sellega seoses on INDEX( ) MATCH( ) palju funktsionaalsem ja mitmekesisem ning neil on ka vähem piiranguid.

Põhitõed INDEX MATCH

Kuna selle juhendi eesmärk on näidata, kui hea see funktsioon on, siis me Vaatame põhiteavet selle toimimise põhimõtete kohta. Ja me näitame näiteid ja kaalume ka, miks see on parem kui VLOOKUP ().

INDEX Funktsiooni süntaks ja kasutamine

See funktsioon aitab veeru või rea numbri alusel kindlaksmääratud otsingualade hulgast soovitud väärtuse leida. süntaksit:

=INDEKS(massiiv, rea number, veeru number):

• massiiv – ala, kus otsitakse;
• rea number – määratud massiivist otsitava rea ​​number. Kui rea number on teadmata, tuleb määrata veeru number;
• veeru number – määratud massiivist leitava veeru number. Kui väärtus on teadmata, on vaja reanumbrit.

Lihtsa valemi näide:

=INDEKS(A1:S10,2,3;XNUMX;XNUMX)

Funktsioon otsib vahemikus A1 kuni C10. Numbrid näitavad, millisest reast (2) ja veerust (3) soovitud väärtust näidata. Tulemuseks on lahter C2.

Päris lihtne, eks? Kuid kui töötate tõeliste dokumentidega, ei ole teil tõenäoliselt teavet veergude numbrite või lahtrite kohta. Selleks on funktsioon MATCH().

MATCH funktsiooni süntaks ja kasutamine

Funktsioon MATCH() otsib soovitud väärtust ja näitab selle ligikaudset arvu määratud otsingualal.

Searchpos() süntaks näeb välja selline:

=MATCH(väärtus otsingule, massiiv otsingule, vaste tüüp)

• otsingu väärtus – otsitav number või tekst;
• searched array – ala, kus otsitakse;
• vaste tüüp – määrab, kas otsida täpset väärtust või sellele lähimaid väärtusi:
  • 1 (või väärtus puudub) – tagastab suurima väärtuse, mis on võrdne määratud väärtusega või sellest väiksem;
  • 0 – näitab täpset vastet otsitud väärtusega. Kombinatsioonis INDEX() MATCH() vajate peaaegu alati täpset vastet, seega kirjutame 0;
  • -1 – näitab väikseimat väärtust, mis on valemis määratud väärtusest suurem või sellega võrdne. Sorteerimine toimub kahanevas järjekorras.

Näiteks vahemikus B1:B3 on registreeritud New York, Pariis, London. Allolev valem näitab numbrit 3, sest London on nimekirjas kolmas:

=EXPOSE(London,B1:B3,0)

Kuidas töötada funktsiooniga INDEX MATCH 

Tõenäoliselt olete juba hakanud mõistma põhimõtet, mille järgi nende funktsioonide ühistöö on üles ehitatud. Ühesõnaga siis INDEX() otsib soovitud väärtust määratud ridade ja veergude hulgast. Ja MATCH() näitab nende väärtuste numbreid:

=INDEX(veerg, kust väärtus tagastatakse, MATCH(otsitav väärtus, veerg, millest otsida, 0))

Kas teil on endiselt raske mõista, kuidas see töötab? Ehk selgitab mõni näide paremini. Oletame, et teil on nimekiri maailma pealinnadest ja nende elanikkonnast:

Teatud pealinna, näiteks Jaapani pealinna rahvaarvu väljaselgitamiseks kasutame järgmist valemit:

=INDEKS(C2:C10, MATCH(Jaapan, A2:A10,0))

Selgitus:

• Funktsioon MATCH() otsib massiivist A2:A10 väärtuse – “Jaapan” ja tagastab arvu 3, sest Jaapan on loendis kolmas väärtus. 
• See näitaja lähebrea number” valemis INDEX() ja käsib funktsioonil printida väärtus sellelt realt.

Seega saab ülaltoodud valemist standardvalem INDEKS(C2:C10,3). Valem otsib lahtritest C2 kuni C10 ja tagastab andmed selle vahemiku kolmandast lahtrist, st C4, kuna loendus algab teisest reast.

Kas te ei soovi valemis linna nime ette kirjutada? Seejärel kirjutage see mis tahes lahtrisse, öelge F1, ja kasutage seda valemis MATCH() viitena. Ja saate dünaamilise otsinguvalemi:

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

Tähtis! Ridade arv sisse massiivi INDEX() peab olema sama, mis ridade arv peetakse massiivi MATCH(), muidu saad vale tulemuse.

Oota natuke, miks mitte kasutada lihtsalt VLOOKUP() valemit?

=VLOOKUP(F1, A2:C10, 3, vale)

 Mis mõte on raisata aega, püüdes välja selgitada kõik need INDEX MATCHi keerukused?

Sel juhul pole vahet, millist funktsiooni kasutada. See on vaid näide, et mõista, kuidas funktsioonid INDEX() ja MATCH() koos töötavad. Teised näited näitavad, milleks need funktsioonid on võimelised olukordades, kus VLOOKUP on jõuetu. 

INDEX MATCH või VLOOKUP

Otsustades, millist otsinguvalemit kasutada, nõustuvad paljud, et INDEX() ja MATCH() on VLOOKUP-ist tunduvalt paremad. Kuid paljud inimesed kasutavad endiselt VLOOKUP(). Esiteks on VLOOKUP() lihtsam ja teiseks ei mõista kasutajad täielikult kõiki funktsioonidega INDEX() ja MATCH() töötamise eeliseid. Ilma nende teadmisteta ei nõustu keegi kulutama oma aega keeruka süsteemi õppimisele.

Siin on INDEX() ja MATCH() peamised eelised VLOOKUP() ees:

• Otsige paremalt vasakule. VLOOKUP() ei saa otsida paremalt vasakule, seega peavad otsitavad väärtused alati asuma tabeli kõige vasakpoolsemates veergudes. Kuid INDEX() ja MATCH() saavad sellega probleemideta hakkama. See artikkel räägib teile, kuidas see praktikas välja näeb: kuidas leida vasakult poolt soovitud väärtus.

1. Veergude ohutu lisamine või eemaldamine. Valem VLOOKUP() näitab veergude eemaldamisel või lisamisel valesid tulemusi, kuna VLOOKUP() vajab edukaks toimimiseks täpset veeru numbrit. Loomulikult muutub veergude lisamisel või eemaldamisel ka nende arv. 

Ja valemites INDEX() ja MATCH() on määratud veergude vahemik, mitte üksikud veerud. Selle tulemusel saate veerge turvaliselt lisada ja eemaldada, ilma et peaksite valemit iga kord värskendama.

1. Otsingumahtudel pole piiranguid. VLOOKUP() kasutamisel ei tohi otsingukriteeriumide koguarv ületada 255 tähemärki, vastasel juhul saate #VALUE! Nii et kui teie andmed sisaldavad palju märke, on INDEX() ja MATCH() parim valik.
2. Kõrge töötlemiskiirus. Kui teie lauad on suhteliselt väikesed, ei märka te tõenäoliselt mingeid erinevusi. Kuid kui tabel sisaldab sadu või tuhandeid ridu ja vastavalt sellele on sadu ja tuhandeid valemeid, saavad INDEX () ja MATCH () hakkama palju kiiremini kui VLOOKUP (). Fakt on see, et Excel töötleb kogu tabeli töötlemise asemel ainult valemis määratud veerge. 

Funktsiooni VLOOKUP() mõju jõudlusele on eriti märgatav, kui teie tööleht sisaldab palju valemeid, nagu VLOOKUP() ja SUM(). Iga massiivi väärtuse sõelumiseks on vaja funktsiooni VLOOKUP() eraldi kontrollida. Seega peab Excel töötlema tohutul hulgal informatsiooni ja see aeglustab tööd oluliselt.

Valemi näited 

Oleme juba aru saanud nende funktsioonide kasulikkusest, nii et saame liikuda kõige huvitavama osa juurde: teadmiste praktikasse rakendamine.

Valem paremalt vasakule otsimiseks

Nagu juba mainitud, ei saa VLOOKUP seda otsinguvormi teha. Seega, kui soovitud väärtused pole kõige vasakpoolsemas veerus, ei anna VLOOKUP() tulemust. Funktsioonid INDEX() ja MATCH() on mitmekülgsemad ning väärtuste asukoht ei mängi nende töös suurt rolli.

Näiteks lisame oma tabeli vasakusse serva auastmeveeru ja proovime välja mõelda, millisel rahvaarvul on Meie Riigi pealinn.

Lahtrisse G1 kirjutame leitava väärtuse ja seejärel kasutame järgmist valemit, et otsida vahemikust C1:C10 ja tagastada vastav väärtus väärtusest A2:A10:

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

Viip. Kui kavatsete seda valemit kasutada mitme lahtri jaoks, parandage vahemikud absoluutse adresseerimise abil (näiteks 2 dollarit: 10 dollarit ja 2 dollarit: 4 dollarit 10 dollarit).

INDEKS ROHKEM VÄLJAKUJUTATUD ROHKEM PALJUTATUD  veergudes ja ridades otsimiseks

Ülaltoodud näidetes oleme kasutanud neid funktsioone VLOOKUP() asendajana, et tagastada väärtused eelnevalt määratletud ridade vahemikust. Aga mis siis, kui peate tegema maatriksi- või kahepoolse otsingu?

See kõlab keeruliselt, kuid selliste arvutuste valem on sarnane standardsele INDEX() MATCH() valemile, ainult ühe erinevusega: MATCH() valemit tuleb kasutada kaks korda. Esimene kord reanumbri saamiseks ja teine ​​kord veeru numbri saamiseks:

=INDEKS(massiivi, VASTUVÕTE(vertikaalne otsingu väärtus, otsingu veerg, 0), MATCH(horisontaalne otsingu väärtus, otsingurida, 0))

Vaatame allolevat tabelit ja proovime koostada valemit INDEX() EXPRESS() EXPRESS() et kuvada valitud aasta demograafilist teavet konkreetses riigis.

Sihtriik asub lahtris G1 (vertikaalne otsing) ja sihtaasta on lahtris G2 (horisontaalne otsing). Valem näeb välja selline:

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

Kuidas see valem töötab

Nagu kõigi teiste keerukate valemite puhul, on neid lihtsam mõista, jagades need üksikuteks võrranditeks. Ja siis saate aru, mida iga funktsioon teeb:

• MATCH(G1,A2:A11,0) – otsib väärtust (G1) vahemikus A2:A11 ja näitab selle väärtuse numbrit, meie puhul on see 2;
• OTSING(G2,B1:D1,0) – otsib väärtust (G2) vahemikus B1:D1. Sel juhul oli tulemuseks 3.

Leitud rea- ja veerunumbrid saadetakse valemis INDEX() vastavale väärtusele:

=INDEKS(B2:D11,2,3;XNUMX;XNUMX)

Selle tulemusena on meil väärtus, mis asub lahtris 2 rea ja 3 veeru ristumiskohas vahemikus B2:D11. Ja valem näitab soovitud väärtust, mis on lahtris D3.

Otsige mitme tingimuse järgi funktsioonidega INDEX ja MATCH

Kui olete lugenud meie VLOOKUP(i) juhendit, olete tõenäoliselt proovinud mitut otsinguvalemit. Kuid sellel otsingumeetodil on üks oluline piirang – vajadus lisada abiveerg.

Kuid hea uudis on see Funktsioonidega INDEX() ja MATCH() saate otsida mitut tingimust, ilma et peaksite oma töölehte redigeerima või muutma.

Siin on üldine mitme tingimusega otsingu valem INDEX() MATCH() jaoks:

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

Märkus: seda valemit tuleb kasutada koos kiirklahviga CTRL+SHIFT+ENTER.

Oletame, et peate leidma otsitava väärtuse kahe tingimuse alusel: Ostja и Toode.

Selleks on vaja järgmist valemit:

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

Selles valemis on C2:C10 vahemik, milles otsing toimub, F1 - see tingimus, A2:A10 — on vahemik, mille abil võrrelda seisukorda, F2 - tingimus 2, V2:V10 – vahemik 2. seisundi võrdlemiseks.

Ärge unustage valemiga töö lõpus vajutada kombinatsiooni CTRL+SHIFT+ENTER – Excel sulgeb valemi automaatselt lokkis sulgudega, nagu on näidatud näites:

Kui te ei soovi oma töös kasutada massiivi valemit, siis lisage valemile veel üks INDEX() ja vajutage ENTER, see näeb välja nagu näites:

Kuidas need valemid töötavad

See valem töötab samamoodi nagu standardvalem INDEX() MATCH(). Mitme tingimuse otsimiseks loote lihtsalt mitu valet ja tõest tingimust, mis esindavad õigeid ja valesid üksiktingimusi. Ja siis kehtivad need tingimused massiivi kõikide vastavate elementide kohta. Valem teisendab argumendid False ja True vastavalt 0-ks ja 1-ks ning väljastab massiivi, kus 1 on stringist leitud sobivad väärtused. MATCH() leiab esimese väärtuse, mis vastab 1-le, ja edastab selle INDEX() valemisse. Ja see omakorda tagastab soovitud veerust määratud real juba soovitud väärtuse.

Ilma massiivita valem sõltub INDEX() võimest neid iseseisvalt käsitleda. Teine INDEX () valemis vastab valele (0), seega edastab see kogu massiivi nende väärtustega valemile MATCH (). 

See on selle valemi taga oleva loogika üsna pikk selgitus. Lisateabe saamiseks lugege artiklit "INDEX MATCH mitme tingimusega'.

AVERAGE, MAX ja MIN loendis INDEX ja MATCH

Excelil on oma erifunktsioonid keskmiste, maksimumide ja miinimumide leidmiseks. Aga mis siis, kui soovite saada andmeid nende väärtustega seotud lahtrist? Sel juhul AVERAGE, MAX ja MIN tuleb kasutada koos funktsioonidega INDEX ja MATCH.

INDEX MATCH ja MAX

Veerus D suurima väärtuse leidmiseks ja veerus C kuvamiseks kasutage valemit: 

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

INDEX MATCH ja MIN

Väikseima väärtuse leidmiseks veerus D ja kuvamiseks veerus C kasutage järgmist valemit:

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

OTSI INDEKSI ja MADU

Veerus D keskmise väärtuse leidmiseks ja selle väärtuse kuvamiseks C-s:

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

Sõltuvalt sellest, kuidas teie andmed on kirjutatud, on MATCH() kolmas argument kas 1, 0 või -1:

• kui veerud on järjestatud kasvavas järjekorras, määrake 1 (siis arvutab valem maksimaalse väärtuse, mis on keskmisest väiksem või sellega võrdne);
• kui sortimine on kahanev, siis -1 (valem väljastab minimaalse väärtuse, mis on keskmisest suurem või sellega võrdne);
• kui otsingumassiivis on väärtus, mis on täpselt võrdne keskmisega, siis määrake selle väärtuseks 0. 

 Meie näites sorteeritakse populatsioon kahanevas järjekorras, seega paneme -1. Ja tulemuseks on Tokyo, kuna rahvastiku väärtus (13,189 000) on keskmisele väärtusele (XNUMX) kõige lähemal.

VLOOKUP() saab ka selliseid arvutusi teha, kuid ainult massiivivalemina: VLOOKUP koos AVERAGE, MIN ja MAX.

INDEX MATCH ja ESND/IFERROR

Tõenäoliselt olete juba märganud, et kui valem ei leia soovitud väärtust, annab see vea # N / A. Standardveateate saate asendada millegi informatiivsemaga. Näiteks määrake valemis argument XNUMX. aastal:

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

Selle valemiga, kui sisestate andmed, mida tabelis pole, annab vorm teile määratud teate.

Kui tahad tabada kõiki vigu, siis v.a XNUMX. aastal saab kasutada VIGA:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Midagi läks valesti!")

Kuid pidage meeles, et vigade maskeerimine sel viisil ei ole hea mõte, sest standardvead teatavad valemis esinevatest rikkumistest.

Loodame, et meie juhend funktsiooni INDEX MATCH() kasutamise kohta oli teile kasulik.