Kombinirane funkcije INDEX & MATCH v Excelu (10 enostavnih primerov)

Excel ima veliko funkcij - približno 450+.

In mnogi od teh so preprosto čudoviti. Količina dela, ki ga lahko opravite z nekaj formulami, me še vedno preseneča (tudi po tem, ko sem Excel uporabljal več kot 10 let).

Med vsemi temi neverjetnimi funkcijami izstopa kombinacija funkcij INDEX MATCH.

Sem velik oboževalec kombinacije INDEX MATCH in to sem že velikokrat jasno povedal.

Napisal sem celo članek o Index Match Vs VLOOKUP, ki je sprožil malce razprav (za ognjemet lahko preverite v razdelku s komentarji).

Danes pišem ta članek, osredotočen izključno na Index Match, da vam pokažem nekaj preprostih in naprednih scenarijev, kjer lahko uporabite to močno kombinacijo formul in opravite delo.

Opomba: V Excelu obstajajo še druge iskalne formule - na primer VLOOKUP in HLOOKUP, ki so odlične. Mnogi ljudje menijo, da je VLOOKUP lažji za uporabo (in to tudi drži). Menim, da je INDEX MATCH v mnogih primerih boljša možnost. Ker pa je ljudem težko, se manj uporablja. Zato poskušam poenostaviti s pomočjo te vadnice.

Preden vam pokažem, kako kombinacija INDEX MATCH spreminja svet analitikov in podatkovnih znanstvenikov, vam najprej predstavim posamezne dele - funkcije INDEX in MATCH.

INDEX funkcija: poišče vrednost na podlagi koordinat

Delovanje funkcije Index najlažje razumete tako, da si o njej razmišljate kot o satelitu GPS.

Takoj, ko satelitu poveš koordinate zemljepisne širine in dolžine, bo natančno vedel, kam naj gre in poišče to lokacijo.

Torej bi satelit kljub neverjetno velikemu številu dolgih kombinacij vedel, kam naj gleda.

Hitro sem iskal svojo delovno lokacijo in to sem dobil.

Kakorkoli, dovolj geografije.

Tako kot satelit potrebuje koordinate zemljepisne širine in dolžine, bi funkcija INDEX v Excelu potrebovala številko vrstice in stolpca, da bi ugotovila, na katero celico se nanašate.

In to je funkcija Excel INDEX v lupini lupine.

Dovolite mi, da vam to opišem s preprostimi besedami.

Funkcija INDEX bo s številko vrstice in številko stolpca poiskala celico v danem obsegu in v njej vrnila vrednost.

INDEX je sam po sebi zelo preprosta funkcija, brez uporabnosti. Navsezadnje v večini primerov verjetno ne boste poznali številk vrstic in stolpcev.

Ampak…

Dejstvo, da ga lahko uporabljate z drugimi funkcijami (namig: MATCH), ki lahko najdejo številko vrstice in številko stolpca, naredi INDEX izjemno zmogljivo funkcijo Excel.

Spodaj je sintaksa funkcije INDEX:

= INDEX (matrika, vrstica_številka, [stolpec_številka]) = INDEKS (matrika, vrstica_številka, [stolpec_številka], [površina_številka])

• matrika - a obseg celic ali konstanta matrike.
• vrstica_število - številko vrstice, iz katere je treba pridobiti vrednost.
• [col_num] - številka stolpca, iz katerega je treba pridobiti vrednost. Čeprav je to neobvezen argument, če pa številka vrstice ni navedena, jo je treba podati.
• [število_območja] - (Izbirno) Če je argument niza sestavljen iz več obsegov, bi to število uporabili za izbiro sklica iz vseh obsegov.

Funkcija INDEX ima 2 sintaksi (samo FYI).

V večini primerov se uporablja prvi. Drugi se uporablja samo v naprednih primerih (na primer pri tristranskem iskanju), kar bomo obravnavali v enem od primerov kasneje v tej vadnici.

Če pa ste novi pri tej funkciji, si zapomnite samo prvo skladnjo.

Spodaj je videoposnetek, ki pojasnjuje, kako uporabljati funkcijo INDEX

Funkcija MATCH: Poišče položaj, ki temelji na iskalni vrednosti

Če se vrnem na moj prejšnji primer zemljepisne dolžine in zemljepisne širine, je MATCH funkcija, ki lahko najde te položaje (v svetu preglednic Excel).

Preprosto povedano, funkcija Excel MATCH lahko najde položaj celice v obsegu.

In na podlagi česa bi ugotovil položaj celice?

Na podlagi iskalne vrednosti.

Na primer, če imate seznam, kot je prikazano spodaj, in želite v njem poiskati položaj imena 'Označi', lahko uporabite funkcijo UJEMANJE.

Funkcija vrne 3, saj je to položaj celice z imenom Mark v njej.

Funkcija MATCH začne iskati od zgoraj navzdol iskalno vrednost (ki je "Označi") v podanem obsegu (kar je v tem primeru A1: A9). Takoj, ko najde ime, vrne položaj v določenem obsegu.

Spodaj je sintaksa funkcije MATCH v Excelu.

= MATCH (iskalna_vrednost, lookup_array, [vrsta_vžiganja])

• lookup_value - Vrednost, za katero iščete ujemanje v lookup_array.
• lookup_array - Obseg celic, v katerih iščete lookup_value.
• [match_type] - (Izbirno) To določa, kako mora Excel iskati ujemajočo se vrednost. Lahko ima tri vrednosti -1, 0 ali 1.

Razumevanje argumenta vrste ujemanja v funkciji MATCH

O funkciji MATCH morate vedeti še eno stvar, in sicer o tem, kako gre skozi podatke in najde položaj celice.

Tretji argument funkcije MATCH je lahko 0, 1 ali -1.

Spodaj je razlaga, kako ti argumenti delujejo:

• 0 - to bo iskalo natančno ujemanje vrednosti. Če najdete natančno ujemanje, bo funkcija MATCH vrnila položaj celice. V nasprotnem primeru bo vrnil napako.
• 1 - to najde največjo vrednost, ki je manjša ali enaka iskalni vrednosti. Če želite to narediti, morate obseg podatkov razvrstiti po naraščajočem vrstnem redu.
• -1 - to najde najmanjšo vrednost, ki je večja ali enaka iskalni vrednosti. Če želite to narediti, morate obseg podatkov razvrstiti po padajočem vrstnem redu.

Spodaj je videoposnetek, ki pojasnjuje, kako uporabljati funkcijo MATCH (skupaj z argumentom vrste ujemanja)

Če povzamem in povem s preprostimi besedami:

• INDEX potrebuje položaj celice (številko vrstice in stolpca) in poda vrednost celice.
• MATCH najde položaj z uporabo iskalne vrednosti.

Združimo jih za ustvarjanje elektrarne (INDEX + MATCH)

Zdaj, ko imate osnovno razumevanje, kako funkciji INDEX in MATCH delujeta posamično, združimo to dvoje in spoznajmo vse čudovite stvari, ki jih lahko naredi.

Da bi to bolje razumeli, imam nekaj primerov, ki uporabljajo kombinacijo INDEX MATCH.

Začel bom s preprostim primerom in vam nato pokazal tudi nekaj naprednih primerov uporabe.

Kliknite tukaj, če želite prenesti primer datoteke

Primer 1: Enostavno iskanje z uporabo kombinacije INDEX MATCH

Naredimo preprosto iskanje z INDEX/MATCH.

Spodaj je tabela, kjer imam ocene za deset študentov.

V tej tabeli želim najti oznake za Jima.

Spodaj je formula, ki to enostavno naredi:

= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Če mislite, da je to mogoče enostavno narediti s funkcijo VLOOKUP, imate prav! To ni najboljša uporaba odličnosti INDEX MATCH. Kljub temu, da sem ljubitelj INDEX MATCH, je to nekoliko težje kot VLOOKUP. Če želite le pridobiti podatke iz stolpca na desni, vam priporočam, da uporabite VLOOKUP.

Razlog, da sem pokazal ta primer, ki ga je mogoče enostavno narediti tudi z VLOOKUP, je, da vam pokažem, kako deluje INDEX MATCH v preprosti nastavitvi.

Zdaj pa naj pokažem korist INDEX MATCH.

Recimo, da imate iste podatke, vendar jih namesto v stolpcih imate v vrsticah (kot je prikazano spodaj).

Veste kaj, še vedno lahko uporabite kombinacijo INDEX MATCH, da dobite Jimove ocene.

Spodaj je formula, ki vam bo dala rezultat:

= INDEX ($ B $ 1: $ K $ 2,2, MATCH (»Jim«, $ B $ 1: $ K $ 1,0))

Upoštevajte, da morate spremeniti obseg in preklopiti dele vrstice/stolpca, da bo ta formula delovala tudi za horizontalne podatke.

Tega z VLOOKUP -om ni mogoče storiti, vendar lahko s HLOOKUP -om še vedno preprosto.

Kombinacija INDEX MATCH lahko enostavno obdeluje vodoravne in navpične podatke.

Kliknite tukaj, če želite prenesti primer datoteke

Primer 2: Iskanje v levo

To je pogostejše, kot si mislite.

Velikokrat boste morda morali pridobiti podatke iz stolpca, ki je levo od stolpca, ki ima iskalno vrednost.

Nekaj, kot je prikazano spodaj:

Če želite izvedeti Michaelovo prodajo, boste morali poiskati na levi strani.

Če razmišljate o VLOOKUP -u, naj vas ustavim tukaj.

VLOOKUP ni namenjen iskanju in pridobivanju vrednosti na levi.

Ali lahko to še vedno storite z uporabo VLOOKUP?

Ja lahko!

Toda to se lahko spremeni v dolgo in grdo formulo.

Torej, če želite poiskati in pridobiti podatke iz stolpcev na levi, je bolje, da uporabite kombinacijo INDEX MATCH.

Spodaj je formula, ki bo dobila Michaelovo prodajno številko:

= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Še ena točka tukaj za INDEX MATCH. VLOOKUP lahko pridobi podatke samo iz stolpcev, ki so desno od stolpca, ki ima iskalno vrednost.

Primer 3: Dvosmerno iskanje

Doslej smo videli primere, kjer smo želeli pridobiti podatke iz stolpca, ki meji na stolpec, ki ima iskalno vrednost.

Toda v resničnem življenju se podatki pogosto raztezajo skozi več stolpcev.

INDEX MATCH lahko enostavno upravlja dvosmerno iskanje.

Spodaj je nabor študentskih ocen pri treh različnih predmetih.

Če želite hitro pridobiti ocene študenta iz vseh treh predmetov, lahko to storite z INDEX MATCH.

Spodnja formula vam bo dala oznake za Jim za vse tri predmete (kopirajte in prilepite v eno celico in povlecite, da zapolnite druge celice ali kopirajte in prilepite v druge celice).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Naj hitro razložim tudi to formulo.

Formula INDEX uporablja B2: D11 kot obseg.

Prvi MATCH uporablja ime (Jim v celici F3) in pridobi njegov položaj v stolpcu imen (A2: A11). To postane številka vrstice, iz katere je treba pridobiti podatke.

Druga formula MATCH uporablja ime predmeta (v celici G2), da dobi položaj tega posebnega imena subjekta v B1: D1. Na primer, matematika je 1, fizika 2 in kemija 3.

Ker se ti položaji MATCH vnesejo v funkcijo INDEX, vrne rezultat na podlagi imena študenta in imena predmeta.

Ta formula je dinamična, kar pomeni, da bi, če spremenite ime študenta ali imena predmetov, še vedno delovala in pridobila pravilne podatke.

Odlična stvar pri uporabi INDEX/MATCH je, da vam bo tudi če zamenjate imena predmetov, še naprej dajal pravilen rezultat.

Primer 4: Iskanje vrednosti iz več stolpcev/meril

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite pridobiti oznake za „Mark Long“.

Ker so podatki v dveh stolpcih, ne morem poiskati Marka in pridobiti podatkov.

Če bom tako storil, bom dobil podatke o ocenah za Mark Frost in ne za Mark Long (ker mi bo funkcija MATCH dala rezultat za MARK, ki ga izpolnjuje).

Eden od načinov za to je ustvariti pomožni stolpec in združiti imena. Ko imate pomožni stolpec, lahko uporabite VLOOKUP in dobite podatke o oznakah.

Če vas zanima, kako to storiti, preberite to vadnico o uporabi VLOOKUP z več merili.

Toda s kombinacijo INDEX/MATCH ne potrebujete stolpca za pomoč. Ustvarite lahko formulo, ki obravnava več meril v sami formuli.

Spodnja formula bo dala rezultat.

= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Naj hitro razložim, kaj počne ta formula.

Del formule MATCH združuje iskalno vrednost (označi in dolgo) ter celotno iskalno polje. Ko se kot iskalno polje uporabi $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11, dejansko preveri iskalno vrednost glede na kombinirani niz imena in priimka (ločen s simbolom črte).

Tako boste zagotovili pravilen rezultat brez uporabe pomožnih stolpcev.

Tovrstno iskanje (kjer obstaja več stolpcev/meril) lahko izvedete tudi z VLOOKUP -om, vendar morate uporabiti stolpec za pomoč. Kombinacija INDEX MATCH nekoliko olajša to brez pomožnih stolpcev.

Primer 5: Pridobite vrednosti iz celotne vrstice/stolpca

V zgornjih primerih smo uporabili funkcijo INDEX za pridobivanje vrednosti iz določene celice. Podate številko vrstice in stolpca in vrne vrednost v tej določeni celici.

Lahko pa storite več.

S funkcijo INDEX lahko dobite vrednosti iz celotne vrstice ali stolpca.

In kako je to lahko koristno, vprašate!

Recimo, da želite vedeti skupni rezultat Jima pri vseh treh predmetih.

S funkcijo INDEX lahko najprej pridobite vse Jimove ocene, nato pa s funkcijo SUM dobite vsoto.

Poglejmo, kako to storiti.

Spodaj imam ocene vseh učencev iz treh predmetov.

Spodnja formula mi bo dala skupno oceno Jima pri vseh treh predmetih.

= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Naj razložim, kako deluje ta formula.

Trik tukaj je, da uporabite 0 kot številko stolpca.

Ko uporabite 0 kot številko stolpca v funkciji INDEX, bo vrnila vse vrednosti vrstice. Podobno, če za številko vrstice uporabite 0, bo vrnila vse vrednosti v stolpcu.

Torej spodnji del formule vrne niz vrednosti - {97, 70, 73}

INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Če samo vnesete to zgornjo formulo v celico v Excelu in pritisnete enter, se prikaže #VREDNOST! napaka. To je zato, ker ne vrača ene same vrednosti, ampak niz vrednosti.

Ampak ne skrbite, niz vrednosti je še vedno tam. To lahko preverite tako, da izberete formulo in pritisnete tipko F9. Prikazal vam bo rezultat formule, ki je v tem primeru niz treh vrednosti - {97, 70, 73}

Če to formulo INDEX zavijete v funkcijo SUM, vam bo dala vsoto vseh ocen, ki jih je dosegel Jim.

Z istim lahko uporabite tudi najvišje, najnižje in povprečne ocene Jima.

Tako kot smo to storili za študenta, lahko to storite tudi za predmet. Na primer, če želite povprečno oceno pri predmetu, lahko v formuli INDEX obdržite številko vrstice kot 0, ki vam bo dala vse vrednosti stolpcev tega predmeta.

Kliknite tukaj, če želite prenesti primer datoteke

Primer 6: Poiščite oceno učenca (približna tehnika ujemanja)

Doslej smo uporabili formulo MATCH za natančno ujemanje iskalne vrednosti.

Lahko pa ga uporabite tudi za približno ujemanje.

Kaj za vraga je približna tekma?

Naj razložim.

Ko iščete stvari, kot so imena ali ID -ji, iščete natančno ujemanje. Včasih pa morate poznati obseg, v katerem so vaše iskalne vrednosti. Običajno je tako pri številkah.

Kot razredni učitelj boste na primer želeli vedeti, kakšna je ocena posameznega učenca pri predmetu, ocena pa se določi na podlagi ocene.

Spodaj je primer, kjer želim oceno za vse učence, ocenjevanje pa se določi na podlagi tabele na desni.

Torej, če študent dobi manj kot 33, je ocena F in če dobi manj kot 50, vendar več kot 33, je to E itd.

Spodaj je formula, ki bo to naredila.

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Naj razložim, kako deluje ta formula.

V funkciji MATCH smo kot argument [match_type] uporabili 1. Ta argument bo vrnil največjo vrednost, ki je manjša ali enaka iskalni vrednosti.

To pomeni, da formula MATCH gre skozi obseg oznak in takoj, ko najde obseg oznak, ki je enak ali manjši od vrednosti iskalnih oznak, se bo tam ustavil in vrnil svoj položaj.

Torej, če je oznaka iskanja 20, bi funkcija MATCH vrnila 1, če pa je 85, bi vrnila 5.

In funkcija INDEX uporablja to vrednost položaja za pridobitev ocene.

POMEMBNO: Da bi to delovalo, je treba vaše podatke razvrstiti po naraščajočem vrstnem redu. Če ne, lahko dobite napačne rezultate.

Upoštevajte, da je zgoraj navedeno mogoče narediti tudi s spodnjo formulo VLOOKUP:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, PRAV)

Toda funkcija MATCH lahko gre še korak dlje, ko gre za približno ujemanje.

Lahko imate tudi padajoče podatke in za iskanje rezultata uporabite kombinacijo INDEX MATCH. Če na primer spremenim vrstni red tabele ocen (kot je prikazano spodaj), lahko še vedno najdem ocene učencev.

Če želite to narediti, morate le spremeniti argument [match_type] na -1.

Spodaj je formula, ki sem jo uporabil:

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP lahko izvede tudi približno ujemanje, vendar le, če so podatki razvrščeni po naraščajočem vrstnem redu (vendar ne deluje, če so podatki razvrščeni po padajočem).

Primer 7: Iskanje glede na velike in male črke

Doslej so bile vse poizvedbe, ki smo jih naredili, neobčutljive na velike in male črke.

To pomeni, da je bila vrednost iskanja Jim ali JIM ali jim, ni pomembno. Dobili boste enak rezultat.

Kaj pa, če želite, da je iskanje občutljivo na velike in male črke.

To je ponavadi tako, če imate velike nabore podatkov in možnost ponavljanja ali različnih imen/identifikatorjev (edina razlika je v tem)

Recimo, da imam na primer naslednji niz študentov, kjer sta dva študenta z imenom Jim (razlika je le v tem, da je eden vnesen kot Jim, drugi pa kot jim).

Upoštevajte, da sta dva učenca z istim imenom - Jim (celica A2 in A5).

Ker običajno iskanje ne bi delovalo, morate poiskati velike in male črke.

Spodaj je formula, ki vam bo dala pravi rezultat. Ker je to matrična formula, morate uporabiti Control + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Naj razložim, kako deluje ta formula.

Funkcija EXACT preveri, ali se natančno ujema z iskalno vrednostjo (ki je v tem primeru 'jim'). Gre skozi vsa imena in vrne FALSE, če se ne ujema, in TRUE, če se ujema.

Torej je izhod funkcije EXACT v tem primeru - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Upoštevajte, da obstaja samo ena TRUE, takrat se funkcija EXACT popolnoma ujema.

Funkcija MATCH nato poišče položaj TRUE v matriki, ki jo vrne funkcija EXACT, kar je v tem primeru 4.

Ko imamo položaj, ga funkcija INDEX uporablja za iskanje oznak.

Primer 8: Poiščite najbližje ujemanje

Zdaj pa malo napredujmo.

Recimo, da imate nabor podatkov, kjer želite najti osebo, ki ima delovne izkušnje najbližje zahtevanim izkušnjam (omenjeno v celici D2).

Čeprav iskalne formule za to niso narejene, jih lahko združite z drugimi funkcijami (na primer MIN in ABS), da to naredite.

Spodaj je formula, ki bo našla osebo z izkušnjami, ki so najbližje zahtevani, in vrnila ime osebe. Upoštevajte, da mora biti izkušnja najbližja (kar je lahko manj ali več).

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Ker je to matrična formula, morate uporabiti Control + Shift + Enter.

Trik v tej formuli je spremeniti vrednost iskanja in iskalno polje, da bi našli najmanjšo razliko v izkušnjah v zahtevanih in dejanskih vrednostih.

Preden razložim formulo, razumejmo, kako bi to naredili ročno.

Skozi vsako celico v stolpcu B boste našli razliko v izkušnjah med zahtevanim in tistim, ki ga ima oseba. Ko boste ugotovili vse razlike, boste našli tisto, ki je minimalna, in dobili ime te osebe.

Ravno to počnemo s to formulo.

Naj razložim.

Iskalna vrednost v formuli MATCH je MIN (ABS (D2-B2: B15)).

Ta del vam daje minimalno razliko med danimi izkušnjami (to je 2,5 leta) in vsemi drugimi izkušnjami. V tem primeru vrne 0,3

Upoštevajte, da sem uporabil ABS, da bi se prepričal, da iščem najbližjega (kar je lahko več ali manj od danih izkušenj).

Zdaj ta najmanjša vrednost postane naša iskalna vrednost.

Iskalno polje v funkciji MATCH je ABS (D2- $ B $ 2: $ B $ 15).

Tako dobimo niz številk, od katerih smo odšteli 2,5 (zahtevane izkušnje).

Zdaj imamo iskalno vrednost (0,3) in iskalno polje ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Funkcija MATCH najde položaj 0,3 v tem nizu, kar je tudi položaj imena osebe, ki ima najbližje izkušnje.

To številko položaja nato funkcija INDEX uporabi za vrnitev imena osebe.

Sorodno branje: Poiščite najbližje ujemanje v Excelu (primeri z uporabo iskalnih formul)

Kliknite tukaj, če želite prenesti primer datoteke

Primer 9: Uporabite INDEX MATCH z nadomestnimi znaki

Če želite poiskati vrednost, ko je delno ujemanje, morate uporabiti nadomestne znake.

Spodaj je na primer nabor podatkov o imenu podjetja in njegovih tržnih kapitalizacijah in želite doseči tržno mejo. podatki za tri podjetja na desni.

Ker se to ne ujemata natančno, v tem primeru ne morete redno iskati.

Še vedno pa lahko dobite prave podatke z zvezdico (*), ki je nadomestni znak.

Spodaj je formula, ki vam bo dala podatke, tako da ujemate imena podjetij iz glavnega stolpca in za to pridobite tržno vrednost.

= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Naj razložim, kako deluje ta formula.

Ker ni natančnega ujemanja iskalnih vrednosti, sem uporabil D2 & ”*” kot iskalno vrednost v funkciji MATCH.

Zvezdica je nadomestni znak, ki predstavlja poljubno število znakov. To pomeni, da bi Apple* v formuli pomenil kateri koli besedilni niz, ki se začne z besedo Apple in ima lahko za seboj poljubno število znakov.

Torej kdaj Jabolko* se uporablja kot iskalna vrednost in formula MATCH jo išče v stolpcu A, vrne položaj „Apple Inc.“, saj se začne z besedo Apple.

Z nadomestnimi znaki lahko poiščete tudi besedilne nize, kjer je iskalna vrednost vmes. Če na primer za iskalno vrednost uporabite * Apple *, bo kjer koli našel kateri koli niz, ki vsebuje besedo jabolko.

Opomba: Ta tehnika dobro deluje, če imate le en primerek ujemanja. Če pa imate več primerkov ujemanja (na primer Apple Inc in Apple Corporation, bi funkcija MATCH vrnila le položaj prvega ujemajočega se primerka.

Primer 10: Trosmerno iskanje

To je napredna uporaba INDEX MATCH -a, vendar ga bom vseeno obravnaval, da vam pokažem moč te kombinacije.

Ne pozabite, da sem rekel, da ima funkcija INDEX dve sintaksi:

= INDEX (matrika, vrstica_številka, [stolpec_številka]) = INDEKS (matrika, vrstica_številka, [stolpec_številka], [površina_številka])

Doslej smo v vseh naših primerih uporabljali le prvega.

Toda za tristransko iskanje morate uporabiti drugo sintakso.

Naj najprej razložim, kaj pomeni tristranski pogled.

Pri dvosmernem iskanju uporabljamo formulo INDEX MATCH, da dobimo ocene, ko imamo ime študenta in ime predmeta. Na primer, pridobivanje oznak Jim v matematiki je dvosmerno iskanje.

Trosmerni videz bi mu dodal še eno dimenzijo. Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite vedeti, kakšen je rezultat Jima iz matematike na vmesnem izpitu, potem bi to bilo tristransko iskanje.

Spodaj je formula, ki bo dala rezultat.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))

Zgornja formula je preverjala tri stvari - ime študenta, predmet in izpit. Ko najde pravo vrednost, jo vrne v celico.

Naj razložim, kako ta formula deluje, tako da formulo razdelim na dele.

• matrika - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): V tem primeru sem namesto ene same matrike uporabil tri matrike v oklepaju.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH funkcija se uporablja za iskanje položaja študentovega imena v celici $ F $ 5 na seznamu študentskega imena.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): Funkcija MATCH se uporablja za iskanje položaja imena predmeta v celici $ B $ 2 na seznamu imen subjekta.
• [area_num] - IF (G $ 3 = "Test enote", 1, IF (G $ 3 = "Mid Term", 2,3)): Vrednost številke območja pove funkciji INDEX, katero od treh nizov naj uporabi za pridobivanje vrednosti. Če je izpit enoten izraz, bi funkcija IF vrnila 1, funkcija INDEX pa bi uporabila prvo polje za pridobitev vrednosti. Če je izpit vmesni, bi formula IF vrnila 2, sicer pa 3.

To je napreden primer uporabe INDEX MATCH in verjetno ne boste našli situacije, ko bi morali to uporabiti. Še vedno pa je dobro vedeti, kaj zmorejo formule Excel.

Kliknite tukaj, če želite prenesti primer datoteke

Zakaj je INDEX/MATCH boljši od VLOOKUP -a?

Ali pač?

Da, v večini primerov je.

Čez nekaj časa bom predstavil svoj primer.

Toda preden to storim, naj povem tole - VLOOKUP je izredno uporabna funkcija in všeč mi je. V Excelu lahko naredi veliko stvari in vsake toliko ga uporabljam. Ob tem ne pomeni, da ne more biti nič boljšega, in INDEX/MATCH (z večjo prilagodljivostjo in funkcionalnostmi) je boljši.

Če torej želite nekaj osnovnega iskanja, raje uporabite VLOOKUP.

INDEX/MATCH je VLOOKUP na steroidih. Ko se enkrat naučite INDEX/MATCH, ga boste morda raje uporabljali (zlasti zaradi prožnosti).

Ne da bi preveč raztezal, naj vam na hitro predstavim razloge, zakaj je INDEX/MATCH boljši od VLOOKUP -a.

INDEX/MATCH lahko gleda levo (pa tudi desno) vrednosti iskanja

To sem opisal v enem od zgornjih primerov.

Če imate vrednost na levi strani iskalne vrednosti, tega ne morete storiti z VLOOKUP

Vsaj ne samo z VLOOKUP -om.

Da, lahko združite VLOOKUP z drugimi formulami in to naredite, vendar postane zapleteno in grdo.

INDEX/MATCH pa je namenjen iskanju povsod (pa naj bo levo, desno, gor ali dol)

INDEX/MATCH lahko deluje z navpičnimi in vodoravnimi območji

Še enkrat, ob polnem spoštovanju VLOOKUP -a tega ne stori.

Navsezadnje V v VLOOKUP pomeni navpično.

VLOOKUP lahko prehaja le skozi navpične podatke, INDEX/MATCH pa po navpičnih in vodoravnih.

Seveda obstaja funkcija HLOOKUP, ki skrbi za vodoravno iskanje, vendar to potem ni VLOOKUP … kajne?

Všeč mi je dejstvo, da je kombinacija INDEX MATCH dovolj prilagodljiva za delo z navpičnimi in vodoravnimi podatki.

VLOOKUP ne more delovati s padajočimi podatki

Ko gre za približno ujemanje, sta VLOOKUP in INDEX/MATCH na isti ravni.

Toda INDEX MATCH upošteva bistvo, saj lahko obravnava tudi podatke v padajočem vrstnem redu.

To pokažem v enem od primerov te vadnice, kjer moramo najti oceno učencev na podlagi tabele ocenjevanja. Če je tabela razvrščena po padajočem vrstnem redu, VLOOKUP ne bi deloval (vendar bi INDEX MATCH deloval).

INDEX/MATCH je lahko nekoliko hitrejši

Bom iskrena. Tega preizkusa nisem opravil sam.

Zanašam se na modrost Excelovega mojstra - Charleyja Kyda.

Razlika v hitrosti v VLOOKUP -u in INDEX/MATCH je komaj opazna, če imate majhne nabore podatkov. Če pa imate na tisoče vrstic in veliko stolpcev, je to lahko odločilni dejavnik.

Charley Kyd v svojem članku navaja:

»V najslabšem primeru je metoda INDEX-MATCH približno tako hitra kot VLOOKUP; v najboljšem primeru je veliko hitreje. "

INDEX/MATCH je neodvisen od dejanskega položaja stolpca

Če imate niz podatkov, kot je prikazano spodaj, ko pridobivate rezultat Jim v fiziki, lahko to storite z uporabo VLOOKUP.

Če želite to narediti, lahko v VLOOKUP določite številko stolpca kot 3.

Vse je v redu.

Kaj pa, če izbrišem stolpec Matematika.

V tem primeru se formula VLOOKUP zlomi.

Zakaj? - Ker je bilo za uporabo tretjega stolpca težko kodirano in ko vmes izbrišem stolpec, tretji stolpec postane drugi stolpec.

V tem primeru je bolje uporabiti INDEX/MATCH, saj lahko z uporabo MATCH -a naredite dinamično številko stolpca. Namesto številke stolpca preveri ime predmeta in ga uporabi za vrnitev številke stolpca.

Zagotovo lahko to storite tako, da združite VLOOKUP z MATCH, če pa vseeno kombinirate, zakaj tega ne storite z INDEX -om, ki je veliko bolj prilagodljiv.

Ko uporabljate INDEX/MATCH, lahko varno vstavite/izbrišete stolpce v nabor podatkov.

Kljub vsem tem dejavnikom obstaja razlog, da je VLOOKUP tako priljubljen.

In to je velik razlog.

VLOOKUP je enostavnejši za uporabo

VLOOKUP sprejme največ štiri argumente. Če si lahko ovijete glavo okoli teh štirih, ste pripravljeni.

In ker večino osnovnih primerov iskanja obravnava tudi VLOOKUP, je ta hitro postala najbolj priljubljena Excelova funkcija.

Imenujem ga kralj funkcij Excela.

INDEX/MATCH pa je nekoliko težje uporabljati. Morda se boste obesili, če ga boste začeli uporabljati, vendar je za začetnika VLOOKUP veliko lažje razložiti in se naučiti.

In to ni igra z ničelno vsoto.

Torej, če ste nov v iskalnem svetu in ne veste, kako uporabljati VLOOKUP, se tega bolje naučite.

Imam podroben vodnik o uporabi VLOOKUP v Excelu (z veliko primeri)

Moj namen v tem članku ni namestiti dve odlični funkciji drug proti drugemu. Želel sem vam pokazati moč kombinacije INDEX MATCH in vse odlične stvari, ki jih lahko naredi.

Upam, da vam je bil ta članek koristen.

Sporočite mi svoje misli v oddelku za komentarje, če pa v tej vadnici odkrijete kakšno napako, mi to sporočite.