Excel ima mnogo funkcija - njih oko 450+.
A mnogi od njih su jednostavno fenomenalni. Količina posla koju možete obaviti s nekoliko formula i dalje me iznenađuje (čak i nakon što ste Excel koristili više od 10 godina).
A među svim tim zadivljujućim funkcijama ističe se kombinacija funkcija INDEX MATCH.
Obožavam kombinaciju INDEX MATCH i to sam već dosta puta jasno rekao.
Čak sam napisao članak o Index Match VS VLOOKUP -u koji je izazvao malu raspravu (neki odjeljak s komentarima možete provjeriti radi vatrometa).
I danas pišem ovaj članak isključivo fokusiran na Index Match kako bih vam pokazao neke jednostavne i napredne scenarije u kojima možete koristiti ovu moćnu kombinaciju formula i obaviti posao.
Bilješka: Postoje i druge formule za pretraživanje u Excelu - poput VLOOKUP -a i HLOOKUP -a i one su sjajne. Mnogi ljudi smatraju da je VLOOKUP lakši za korištenje (i to je također istina). Vjerujem da je INDEX MATCH bolja opcija u mnogim slučajevima. No, budući da je ljudima teško, manje se koristi. Stoga pokušavam pojednostaviti pomoću ovog vodiča.Prije nego što vam pokažem kako kombinacija INDEX MATCH -a mijenja svijet analitičara i podatkovnih znanstvenika, prvo ću vas upoznati s pojedinim dijelovima - funkcijama INDEX i MATCH.
INDEX funkcija: pronalazi vrijednost na temelju koordinata
Najlakši način za razumijevanje funkcioniranja funkcije Index je razmišljanje o njoj kao GPS satelitu.
Čim satelitu kažete koordinate zemljopisne širine i dužine, on će točno znati kamo otići i pronaći to mjesto.
Dakle, unatoč nevjerojatno velikom broju kombinacija dugačkih, satelit bi znao točno gdje tražiti.
Brzo sam pretražio svoje radno mjesto i to sam dobio.
U svakom slučaju, dosta je geografije.
Baš kao što satelitu trebaju koordinate zemljopisne širine i dužine, funkciji INDEX u Excelu trebat će broj retka i stupca da bi znao na koju ćeliju mislite.
A to je Excel INDEX funkcija u ljusci oraha.
Dopustite mi da vam to definiram jednostavnim riječima.
INDEX funkcija će koristiti broj retka i broj stupca za pronalaženje ćelije u zadanom rasponu i vraćanje vrijednosti u nju.
INDEX je sam po sebi vrlo jednostavna funkcija, bez uslužnih programa. Uostalom, u većini slučajeva vjerojatno nećete znati brojeve redaka i stupaca.
Ali…
Činjenica da ga možete koristiti s drugim funkcijama (savjet: MATCH) koje mogu pronaći broj retka i broj stupca čini INDEX iznimno moćnom Excel funkcijom.
Ispod je sintaksa funkcije INDEX:
= INDEX (niz, broj redaka, [broj_kolona]) = INDEKS (niz, broj redaka, [broj_kolona], [broj_područja])
- niz - a raspon ćelija ili konstanta niza.
- red_broj - broj retka iz kojeg se dohvaća vrijednost.
- [broj_kolona] - broj stupca iz kojeg se dohvaća vrijednost. Iako je ovo izborni argument, ali ako broj_reda nije naveden, potrebno ga je navesti.
- [broj_područja] - (Izborno) Ako se argument niza sastoji od više raspona, ovaj će se broj koristiti za odabir reference iz svih raspona.
INDEX funkcija ima 2 sintakse (samo FYI).
U većini slučajeva koristi se prvi. Drugi se koristi samo u naprednim slučajevima (kao što je trosmjerno pretraživanje) što ćemo obraditi u jednom od primjera kasnije u ovom vodiču.
No, ako ste tek počeli koristiti ovu funkciju, sjetite se samo prve sintakse.
Ispod je videozapis koji objašnjava kako koristiti funkciju INDEX
MATCH funkcija: Pronalazi poziciju prema vrijednosti traženja
Vraćajući se na moj prethodni primjer zemljopisne dužine i širine, MATCH je funkcija koja može pronaći te pozicije (u svijetu Excel proračunskih tablica).
Jednostavnim jezikom rečeno, funkcija Excel MATCH može pronaći položaj ćelije u rasponu.
I na temelju čega bi pronašao položaj ćelije?
Na temelju tražene vrijednosti.
Na primjer, ako imate popis kao što je prikazano u nastavku i želite u njemu pronaći položaj imena ‘Označi’, tada možete upotrijebiti funkciju MATCH.
Funkcija vraća 3 jer je to položaj ćelije s imenom Mark u njoj.
MATCH funkcija počinje tražiti odozgo prema dolje vrijednost pretraživanja (koja je ‘Označi’) u navedenom rasponu (što je A1: A9 u ovom primjeru). Čim pronađe ime, vraća poziciju u tom određenom rasponu.
Ispod je sintaksa funkcije MATCH u Excelu.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value - Vrijednost za koju tražite podudaranje u lookup_array.
- lookup_array - Raspon ćelija u kojima tražite vrijednost lookup_value.
- [match_type] - (Izborno) Ovo određuje kako bi Excel trebao tražiti odgovarajuću vrijednost. Može uzeti tri vrijednosti -1, 0 ili 1.
Razumijevanje argumenta vrste podudaranja u funkciji MATCH
Postoji još jedna stvar koju trebate znati o funkciji MATCH, a to je kako prolazi kroz podatke i pronalazi položaj ćelije.
Treći argument funkcije MATCH može biti 0, 1 ili -1.
U nastavku je objašnjenje kako ovi argumenti funkcioniraju:
- 0 - ovo će tražiti točno podudaranje vrijednosti. Ako se pronađe točno podudaranje, funkcija MATCH vratit će položaj ćelije. Inače će vratiti grešku.
- 1 - ovo pronalazi najveću vrijednost koja je manja ili jednaka vrijednosti traženja. Da bi ovo funkcioniralo, raspon podataka morate sortirati uzlaznim redoslijedom.
- -1 - ovo pronalazi najmanju vrijednost koja je veća ili jednaka vrijednosti traženja. Da bi ovo funkcioniralo, raspon podataka morate sortirati prema opadajućem redoslijedu.
Ispod je videozapis koji objašnjava kako koristiti funkciju MATCH (zajedno s argumentom vrste podudaranja)
Ukratko i jednostavno rečeno:
- INDEX -u je potreban položaj ćelije (broj retka i stupca) i daje vrijednost ćelije.
- MATCH pronalazi poziciju pomoću vrijednosti pretraživanja.
Kombinirajmo ih za stvaranje moćne kuće (INDEX + MATCH)
Sada kada imate osnovno razumijevanje o tome kako funkcije INDEX i MATCH funkcioniraju pojedinačno, spojimo ovo dvoje i saznajmo sve prekrasne stvari koje može učiniti.
Da bih to bolje razumio, imam nekoliko primjera koji koriste kombinaciju INDEX MATCH.
Počet ću s jednostavnim primjerom, a zatim ću vam pokazati i neke napredne slučajeve uporabe.
Kliknite ovdje za preuzimanje datoteke primjera
Primjer 1: Jednostavno pretraživanje pomoću kombinacije INDEX MATCH
Učinimo jednostavno pretraživanje pomoću INDEX/MATCH.
Ispod je tablica na kojoj imam ocjene za deset učenika.
Iz ove tablice želim pronaći oznake za Jima.
Ispod je formula koja to lako može učiniti:
= INDEKS ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)
Sada, ako mislite da se to lako može učiniti pomoću funkcije VLOOKUP, u pravu ste! Ovo nije najbolja upotreba INDEX MATCH -a. Unatoč činjenici da sam ljubitelj INDEX MATCH -a, to je malo teže od VLOOKUP -a. Ako želite dohvatiti podatke iz stupca s desne strane, preporučujem vam da koristite VLOOKUP.
Razlog zbog kojeg sam pokazao ovaj primjer, koji se također može lako učiniti s VLOOKUP -om je da vam pokažem kako INDEX MATCH radi u jednostavnom okruženju.
Dopustite mi sada da pokažem korist od INDEX MATCH -a.
Pretpostavimo da imate iste podatke, ali umjesto da ih imate u stupcima, imate ih u redovima (kao što je prikazano u nastavku).
Znate što, još uvijek možete koristiti kombinaciju INDEX MATCH za dobivanje Jimovih ocjena.
Ispod je formula koja će vam dati rezultat:
= INDEKS ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))
Imajte na umu da morate promijeniti raspon i promijeniti dijelove retka/stupca kako bi ova formula radila i za horizontalne podatke.
To se ne može učiniti s VLOOKUP -om, ali i dalje to možete učiniti jednostavno pomoću HLOOKUP -a.
Kombinacija INDEX MATCH može jednostavno rukovati horizontalnim i vertikalnim podacima.Kliknite ovdje za preuzimanje datoteke primjera
Primjer 2: Pretraživanje ulijevo
Češći je nego što mislite.
Mnogo puta ćete možda morati dohvatiti podatke iz stupca koji se nalazi lijevo od stupca koji ima vrijednost pretraživanja.
Nešto kao što je prikazano ispod:
Da biste saznali Michaelovu prodaju, morat ćete pregledati lijevo.
Ako razmišljate o VLOOKUP -u, dopustite mi da vas zaustavim ovdje.
VLOOKUP nije napravljen za traženje i dohvaćanje vrijednosti s lijeve strane.
Možete li to i dalje učiniti pomoću VLOOKUP -a?
Da, možeš!
Ali to se može pretvoriti u dugu i ružnu formulu.
Dakle, ako želite izvršiti pretraživanje i dohvaćanje podataka iz stupaca s lijeve strane, bolje je da koristite kombinaciju INDEX MATCH.
Ispod je formula koja će dobiti Michaelov prodajni broj:
= INDEKS ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
Primjer 3: Dvosmjerno pretraživanje
Do sada smo vidjeli primjere u kojima smo htjeli dohvatiti podatke iz stupca u susjedstvu stupca koji ima vrijednost pretraživanja.
No, u stvarnom životu podaci se često prostiru kroz više stupaca.
INDEX MATCH može jednostavno podnijeti dvosmjerno pretraživanje.
Ispod je skup učeničkih ocjena iz tri različita predmeta.
Ako želite brzo postići ocjene učenika iz sva tri predmeta, to možete učiniti pomoću INDEX MATCH.
Formula u nastavku će vam dati ocjene za Jima za sva tri predmeta (kopirajte i zalijepite u jednu ćeliju i povucite da biste popunili druge ćelije ili kopirajte i zalijepite u druge ćelije).
= INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Dopustite mi da brzo objasnim i ovu formulu.
INDEX formula koristi B2: D11 kao raspon.
Prvi MATCH koristi ime (Jim u ćeliji F3) i dohvaća njegov položaj u stupcu imena (A2: A11). To postaje broj retka iz kojeg je potrebno dohvatiti podatke.
Druga formula MATCH koristi naziv subjekta (u ćeliji G2) kako bi dobila položaj tog naziva subjekta u B1: D1. Na primjer, matematika je 1, fizika 2 i kemija 3.
Budući da se te pozicije MATCH unose u funkciju INDEX, ona vraća rezultat na temelju imena učenika i naziva predmeta.
Ova je formula dinamična, što znači da bi, ako promijenite ime učenika ili nazive predmeta, i dalje radila i donijela točne podatke.
Jedna sjajna stvar u korištenju INDEX/MATCH je ta što će vam čak i ako promijenite nazive subjekata i dalje dati točan rezultat.Primjer 4: Traženje vrijednosti iz više stupaca/kriterija
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dohvatiti oznake za "Mark Long".
Budući da su podaci u dva stupca, ne mogu potražiti Marka i dobiti podatke.
Ako to učinim na taj način, dobit ću podatke o ocjenama za Marka Frosta, a ne za Marka Longa (jer će mi funkcija MATCH dati rezultat za MARK koji zadovoljava).
Jedan od načina za to je stvaranje pomoćnog stupca i kombiniranje imena. Nakon što dobijete pomoćni stupac, možete koristiti VLOOKUP i dobiti podatke o oznakama.
Ako ste zainteresirani naučiti kako to učiniti, pročitajte ovaj vodič o korištenju VLOOKUP -a s više kriterija.
No s kombinacijom INDEX/MATCH ne trebate pomoćni stupac. Možete stvoriti formulu koja obrađuje više kriterija u samoj formuli.
Formula u nastavku će dati rezultat.
= INDEKS ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Dopustite mi da brzo objasnim čemu služi ova formula.
Dio formule MATCH kombinira vrijednost traženja (Označi i Dugo), kao i cijeli niz traženja. Kada se $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 koristi kao niz za pretraživanje, on zapravo provjerava vrijednost pretraživanja u odnosu na kombinirani niz imena i prezimena (odvojen simbolom cijevi).
To osigurava da ćete dobiti pravi rezultat bez korištenja pomoćnih stupaca.
Ovu vrstu pretraživanja (gdje postoji više stupaca/kriterija) možete izvršiti i s VLOOKUP -om, ali morate upotrijebiti pomoćni stupac. Kombinacija INDEX MATCH olakšava to bez ikakvih pomoćnih stupaca.Primjer 5: Dobijte vrijednosti iz cijelog retka/stupca
U gornjim primjerima koristili smo funkciju INDEX za dobivanje vrijednosti iz određene ćelije. Navedite broj retka i stupca, a on vraća vrijednost u toj određenoj ćeliji.
Ali možete učiniti više.
Također možete koristiti funkciju INDEX za dobivanje vrijednosti iz cijelog retka ili stupca.
A pitajte kako ovo može biti korisno!
Pretpostavimo da želite znati ukupni rezultat Jima u sva tri predmeta.
Možete koristiti funkciju INDEX da biste prvo dobili sve Jimove ocjene, a zatim upotrijebiti funkciju ZBOR da biste dobili ukupan zbroj.
Pogledajmo kako to učiniti.
U nastavku imam ocjene svih učenika iz tri predmeta.
Formula u nastavku će mi dati ukupan rezultat Jima u sva tri predmeta.
= SUM (INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Dopustite mi da objasnim kako ova formula radi.
Trik ovdje je da koristite 0 kao broj stupca.
Kada koristite 0 kao broj stupca u funkciji INDEX, vratit će se sve vrijednosti retka. Slično, ako koristite 0 kao broj retka, vratit će se sve vrijednosti u stupcu.
Dakle, donji dio formule vraća niz vrijednosti - {97, 70, 73}
INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Ako samo unesete gornju formulu u ćeliju u Excelu i pritisnete enter, vidjet ćete #VREDNOST! pogreška. To je zato što ne vraća jednu vrijednost, već niz vrijednosti.
Ali ne brinite, niz vrijednosti je još uvijek tu. To možete provjeriti odabirom formule i pritisnite tipku F9. Pokazat će vam rezultat formule koja je u ovom slučaju niz od tri vrijednosti - {97, 70, 73}
Sada, ako ovu formulu INDEX umotate u funkciju SUM, dobit ćete zbroj svih ocjena koje je Jim postigao.
Također ih možete koristiti za dobivanje najvećih, najnižih i prosječnih ocjena Jima.
Baš kao što smo to učinili za studenta, to možete učiniti i za predmet. Na primjer, ako želite prosječni rezultat u predmetu, broj retka možete zadržati kao 0 u formuli INDEX i on će vam dati sve vrijednosti stupaca tog predmeta.
Kliknite ovdje za preuzimanje datoteke primjera
Primjer 6: Pronađite ocjenu učenika (približna tehnika podudaranja)
Do sada smo koristili formulu MATCH kako bismo dobili točno podudaranje vrijednosti traženja.
Ali možete ga upotrijebiti i za približno podudaranje.
Što je, dovraga, približna utakmica?
Dopustite mi da objasnim.
Kada tražite stvari poput imena ili ID -a, tražite točno podudaranje. No, ponekad morate znati raspon u kojem se nalaze vaše vrijednosti pretraživanja. To je obično slučaj s brojevima.
Na primjer, kao razrednik, možda biste htjeli znati koja je ocjena svakog učenika u predmetu, a ocjena se donosi na temelju bodova.
Ispod je primjer gdje želim ocjenu za sve učenike, a ocjenjivanje se vrši na temelju tablice s desne strane.
Dakle, ako učenik dobije manje od 33, ocjena je F, a ako dobije manje od 50, ali više od 33, to je E, i tako dalje.
Ispod je formula koja će to učiniti.
= INDEKS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Dopustite mi da objasnim kako ova formula radi.
U funkciji MATCH koristili smo 1 kao argument [match_type]. Ovaj argument će vratiti najveću vrijednost koja je manja ili jednaka vrijednosti traženja.
To znači da formula MATCH prolazi kroz raspon oznaka, i čim pronađe raspon oznaka koji je jednak ili manji od vrijednosti oznaka za pretraživanje, tu će se zaustaviti i vratiti svoj položaj.
Dakle, ako je vrijednost oznake pretraživanja 20, funkcija MATCH vratila bi 1, a ako je 85, vratila bi 5.
I INDEX funkcija koristi ovu vrijednost pozicije za dobivanje ocjene.
VAŽNO: Da bi ovo funkcioniralo, vaše podatke morate sortirati uzlaznim redoslijedom. Ako nije, možete dobiti pogrešne rezultate.Imajte na umu da se gore navedeno može učiniti i pomoću donje formule VLOOKUP:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, ISTINA)
No, funkcija MATCH može otići korak dalje kada je u pitanju približno podudaranje.
Također možete imati silazne podatke i možete koristiti kombinaciju INDEX MATCH za pronalaženje rezultata. Na primjer, ako promijenim redoslijed tablice ocjena (kao što je prikazano u nastavku), još uvijek mogu pronaći ocjene učenika.
Da bih to učinio, sve što moram učiniti je promijeniti argument [match_type] na -1.
Ispod je formula koju sam koristio:
= INDEKS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP također može napraviti približno podudaranje, ali samo kada su podaci razvrstani uzlaznim redoslijedom (ali ne radi ako su podaci sortirani silaznim redoslijedom).
Primjer 7: Pretraživanje osjetljivo na velika i mala slova
Dosad su sve pretrage koje smo obavili bile osjetljive na velika i mala slova.
To znači da nije važno je li vrijednost pretraživanja bila Jim ili JIM ili jim, nije važno. Dobit ćete isti rezultat.
Ali što ako želite da pretraživanje bude osjetljivo na velika i mala slova?
To je obično slučaj kada imate velike skupove podataka i mogućnost ponavljanja ili različitih naziva/ID -ova (s jedinom razlikom u tome)
Na primjer, pretpostavimo da imam sljedeći skup podataka učenika gdje postoje dva učenika s imenom Jim (jedina razlika je u tome što je jedan upisan kao Jim, a drugi kao jim).
Imajte na umu da postoje dva učenika s istim imenom - Jim (ćelija A2 i A5).
Budući da uobičajeno pretraživanje ne bi funkcioniralo, morate izvršiti pretraživanje osjetljivo na velika i mala slova.
Ispod je formula koja će vam dati pravi rezultat. Budući da je ovo formula niza, morate koristiti Control + Shift + Enter.
= INDEKS ($ B $ 2: $ B $ 11, MATCH (ISTINA, TOČNO (D3, A2: A11), 0), 1)
Dopustite mi da objasnim kako ova formula radi.
Funkcija EXACT provjerava točno podudaranje vrijednosti traženja (što je u ovom slučaju 'jim'). Prolazi kroz sve nazive i vraća se FALSE ako se ne podudara i TRUE ako se podudara.
Dakle, izlaz funkcije EXACT u ovom primjeru je - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Imajte na umu da postoji samo jedno TRUE, kada je funkcija EXACT pronašla savršeno podudaranje.
Funkcija MATCH tada pronalazi položaj TRUE u nizu koji vraća funkcija EXACT, što je 4 u ovom primjeru.
Kad dobijemo položaj, funkcija INDEX koristi ga za pronalaženje oznaka.
Primjer 8: Pronađite najbliže podudaranje
Idemo sad malo napredovati.
Pretpostavimo da imate skup podataka u kojem želite pronaći osobu koja ima radno iskustvo najbliže potrebnom iskustvu (spomenuto u ćeliji D2).
Iako formule za pretraživanje nisu stvorene za to, možete ih kombinirati s drugim funkcijama (kao što su MIN i ABS) kako biste to učinili.
Ispod je formula koja će pronaći osobu s iskustvom najbližim traženom i vratiti ime osobe. Imajte na umu da iskustvo mora biti najbliže (što može biti manje ili više).
= INDEKS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Budući da je ovo formula niza, morate koristiti Control + Shift + Enter.
Trik u ovoj formuli je promijeniti vrijednost pretraživanja i niz pretraživanja kako bi se pronašla minimalna razlika iskustva u potrebnim i stvarnim vrijednostima.
Prije nego objasnim formulu, shvatimo kako biste to učinili ručno.
Proći ćete kroz svaku ćeliju u stupcu B i pronaći razliku u iskustvu između onoga što je potrebno i onoga što osoba ima. Nakon što savladate sve razlike, pronaći ćete onu koja je minimalna i dohvatiti ime te osobe.
Upravo to radimo s ovom formulom.
Dopustite mi da objasnim.
Tražena vrijednost u formuli MATCH je MIN (ABS (D2-B2: B15)).
Ovaj dio daje minimalnu razliku između danog iskustva (koje iznosi 2,5 godine) i svih ostalih iskustava. U ovom primjeru vraća 0,3
Imajte na umu da sam upotrijebio ABS kako bih bio siguran da tražim najbliže (što može biti više ili manje od navedenog iskustva).
Sada ova minimalna vrijednost postaje naša tražilica.
Niz traženja u funkciji MATCH je ABS (D2- $ B $ 2: $ B $ 15).
To nam daje niz brojeva od kojih je oduzeto 2,5 (potrebno iskustvo).
Dakle, sada imamo vrijednost traženja (0,3) i niz traženja ({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})
MATCH funkcija nalazi poziciju 0,3 u ovom nizu, što je ujedno i pozicija imena osobe koja ima najbliže iskustvo.
Taj broj pozicije zatim koristi funkcija INDEX za vraćanje imena osobe.
Povezano čitanje: Pronađite najbliže podudaranje u Excelu (primjeri pomoću formula za pretraživanje)
Kliknite ovdje za preuzimanje datoteke primjera
Primjer 9: Upotrijebite INDEX MATCH sa zamjenskim znakovima
Ako želite potražiti vrijednost kada postoji djelomično podudaranje, tada morate koristiti zamjenske znakove.
Na primjer, ispod je skup podataka o nazivu tvrtke i njihovoj tržišnoj kapitalizaciji, a želite poželjeti dobiti tržišnu kapitalizaciju. podaci za tri tvrtke s desne strane.
Budući da se ne radi o potpunom podudaranju, u ovom slučaju ne možete redovito tražiti.
No svejedno možete dobiti prave podatke pomoću zvjezdice (*), koja je zamjenski znak.
Ispod je formula koja će vam dati podatke tako što ćete upariti nazive tvrtki iz glavnog stupca i dohvatiti vrijednost tržišne kapitalizacije za nju.
= INDEKS ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Dopustite mi da objasnim kako ova formula radi.
Budući da ne postoji točno podudaranje vrijednosti pretraživanja, upotrijebio sam D2 & ”*” kao vrijednost pretraživanja u funkciji MATCH.
Zvjezdica je zamjenski znak koji predstavlja bilo koji broj znakova. To znači da bi Apple* u formuli značio bilo koji tekstualni niz koji počinje s riječju Apple i može imati bilo koji broj znakova iza sebe.
Pa kad Jabuka* koristi se kao vrijednost pretraživanja i formula MATCH traži je u stupcu A, vraća poziciju "Apple Inc.", jer počinje s riječi Apple.
Također možete koristiti zamjenske znakove za pronalaženje tekstualnih nizova gdje je vrijednost traženja između. Na primjer, ako koristite * Apple * kao vrijednost pretraživanja, on će pronaći bilo koji niz koji ima riječ jabuka bilo gdje u sebi.
Napomena: Ova tehnika dobro funkcionira ako imate samo jednu instancu podudaranja. No ako imate više instanci podudaranja (na primjer Apple Inc i Apple Corporation, tada bi funkcija MATCH vratila položaj samo prve instance koja se podudara.
Primjer 10: Trosmjerno pretraživanje
Ovo je napredna upotreba INDEX MATCH -a, ali svejedno ću to pokriti kako bih vam pokazao snagu ove kombinacije.
Zapamtite, rekao sam da funkcija INDEX ima dvije sintakse:
= INDEX (niz, broj redaka, [broj_kolona]) = INDEKS (niz, broj redaka, [broj_kolona], [broj_područja])
Do sada smo u svim našim primjerima koristili samo prvi.
No za trosmjerno pretraživanje morate koristiti drugu sintaksu.
Dopustite mi prvo da objasnim što znači trosmjerni izgled.
U dvosmjernom traženju koristimo formulu INDEX MATCH za dobivanje ocjena kada imamo ime učenika i naziv predmeta. Na primjer, dohvaćanje Jimovih ocjena u matematici dvosmjerno je pretraživanje.
Trosmjerni izgled dodao bi mu još jednu dimenziju. Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite znati rezultat Jima iz matematike na srednjoročnom ispitu, onda bi ovo bilo trosmjerno pretraživanje.
Ispod je formula koja će dati rezultat.
= INDEKS (($ 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))))
Gornja formula provjerila je tri stvari - ime studenta, predmet i ispit. Nakon što pronađe pravu vrijednost, vraća je u ćeliju.
Dopustite mi da objasnim kako ova formula funkcionira razbijanjem formule na dijelove.
- niz - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Umjesto korištenja jednog niza, u ovom slučaju, koristio sam tri niza unutar zagrada.
- broj_reda - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH funkcija koristi se za pronalaženje mjesta imena učenika u ćeliji $ F $ 5 na popisu imena učenika.
- col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): MATCH funkcija koristi se za pronalaženje položaja imena subjekta u ćeliji $ B $ 2 na popisu naziva subjekta.
- [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): Vrijednost broja područja govori funkciji INDEX koji od tri niza koristiti za dohvaćanje vrijednosti. Ako je ispit jedinica termina, funkcija IF vratila bi 1, a funkcija INDEX koristila bi prvi niz za dohvaćanje vrijednosti. Ako je ispit srednjoročni, formula IF vratila bi 2, inače će vratiti 3.
Ovo je napredni primjer korištenja INDEX MATCH -a i malo je vjerojatno da ćete pronaći situaciju kada ovo morate koristiti. No, ipak je dobro znati što Excel formule mogu učiniti.
Kliknite ovdje za preuzimanje datoteke primjera
Zašto je INDEX/MATCH bolji od VLOOKUP -a?
Ili je?
Da, to je - u većini slučajeva.
Izložit ću svoj slučaj za neko vrijeme.
Ali prije nego što to učinim, dozvolite mi da kažem ovo - VLOOKUP je izuzetno korisna funkcija i sviđa mi se. U Excelu može učiniti mnogo toga i ja ga tu i tamo koristim. Rekavši to, ne znači da ne može biti ništa bolje, a INDEX/MATCH (s više fleksibilnosti i funkcionalnosti) je bolji.
Dakle, ako želite napraviti osnovno pretraživanje, bolje je koristiti VLOOKUP.
INDEX/MATCH je VLOOKUP na steroidima. A kad jednom naučite INDEX/MATCH, uvijek biste ga radije koristili (posebno zbog fleksibilnosti koju ima).
Bez da previše razvlačim, dopustite mi da vam na brzinu navedem razloge zašto je INDEX/MATCH bolji od VLOOKUP -a.
INDEX/MATCH može gledati lijevo (kao i desno) vrijednosti pretraživanja
Opisao sam to u jednom od gornjih primjera.
Ako imate vrijednost koja se nalazi lijevo od vrijednosti traženja, to ne možete učiniti pomoću VLOOKUP -a
Barem ne samo s VLOOKUP -om.
Da, možete kombinirati VLOOKUP s drugim formulama i obaviti to, ali postaje komplicirano i neuredno.
S druge strane, INDEX/MATCH traži se svugdje (bilo lijevo, desno, gore ili dolje)
INDEX/MATCH može raditi s okomitim i vodoravnim rasponima
Opet, uz puno poštovanje prema VLOOKUP -u, to nije stvoreno za to.
Uostalom, V u VLOOKUP -u znači okomito.
VLOOKUP može prolaziti samo kroz podatke koji su okomiti, dok INDEX/MATCH može prolaziti kroz podatke i okomito i vodoravno.
Naravno, postoji funkcija HLOOKUP koja se brine za vodoravno pretraživanje, ali to tada nije VLOOKUP … zar ne?
Sviđa mi se činjenica da je kombinacija INDEX MATCH dovoljno fleksibilna za rad s vertikalnim i horizontalnim podacima.
VLOOKUP ne može raditi s silaznim podacima
Što se tiče približnog podudaranja, VLOOKUP i INDEX/MATCH su na istoj razini.
No INDEX MATCH uzima u obzir to što može obrađivati i podatke koji su u opadajućem redoslijedu.
To pokazujem u jednom od primjera u ovom vodiču gdje moramo pronaći ocjenu učenika na temelju tablice ocjenjivanja. Ako je tablica sortirana prema opadajućem redoslijedu, VLOOKUP ne bi radio (ali INDEX MATCH bi).
INDEX/MATCH može biti nešto brži
Bit ću iskren. Nisam sam vodio ovaj test.
Oslanjam se na mudrost Excel majstora - Charleyja Kyda.
Razlika u brzini u VLOOKUP -u i INDEX/MATCH -u teško je uočljiva kada imate male skupove podataka. No, ako imate tisuće redaka i mnogo stupaca, to može biti odlučujući faktor.
U svom članku Charley Kyd navodi:
“U najgorem slučaju, metoda INDEX-MATCH je otprilike jednako brza kao VLOOKUP; u svom najboljem slučaju, to je mnogo brže. "
INDEX/MATCH neovisan je o stvarnom položaju stupca
Ako imate skup podataka kao što je prikazano u nastavku dok dohvaćate rezultat Jima u fizici, to možete učiniti pomoću VLOOKUP -a.
A da biste to učinili, možete odrediti broj stupca kao 3 u VLOOKUP -u.
Sve je u redu.
Ali što ako izbrišem stupac Math.
U tom će slučaju formula VLOOKUP puknuti.
Zašto? - Budući da je bilo teško koristiti treći stupac, a kad izbrišem stupac između, treći stupac postaje drugi stupac.
Korištenje INDEX/MATCH -a u ovom je slučaju bolje jer broj stupca možete učiniti dinamičnim pomoću MATCH -a. Dakle, umjesto broja stupca, provjerava naziv predmeta i koristi ga za vraćanje broja stupca.
Sigurno to možete učiniti kombiniranjem VLOOKUP -a s MATCH -om, ali ako ipak kombinirate, zašto to ne biste učinili s INDEX -om koji je puno fleksibilniji.
Kada koristite INDEX/MATCH, možete sigurno umetnuti/izbrisati stupce u skupu podataka.
Unatoč svim tim čimbenicima, postoji razlog zašto je VLOOKUP toliko popularan.
I to je veliki razlog.
VLOOKUP je lakši za korištenje
VLOOKUP uzima samo četiri argumenta. Ako možete omotati glavu oko ove četiri, spremni ste.
A budući da većinu osnovnih slučajeva pretraživanja rješava i VLOOKUP, brzo je postala najpopularnija Excel funkcija.
Zovem ga kralj Excel funkcija.
S druge strane, INDEX/MATCH malo je teže koristiti. Možda ćete se objesiti ako ga počnete koristiti, ali za početnike je VLOOKUP daleko lakše objasniti i naučiti.
I ovo nije igra s nultom sumom.
Dakle, ako ste tek počeli tražiti svijet i ne znate koristiti VLOOKUP, bolje to naučite.
Imam detaljan vodič o korištenju VLOOKUP -a u Excelu (s puno primjera)
Moja namjera u ovom članku nije usporediti dvije sjajne funkcije jedna protiv druge. Htio sam vam pokazati moć kombinacije INDEX MATCH i sve velike stvari koje može učiniti.
Nadam se da vam je ovaj članak bio koristan.
Javite mi svoja razmišljanja u odjeljku za komentare, a u slučaju da pronađete bilo kakvu pogrešku u ovom vodiču, obavijestite me.
