Ako već neko vrijeme čitate Excelove savjete, uvijek ste pronašli nekoga tko govori o upotrebi funkcija Excel INDEX () & MATCH () umjesto Excel VLOOKUP-a. Govoreći u svoje ime, uvijek je bilo preteško pokušati svladati DVIJE nove funkcije istovremeno. Ali, to je cool trik. Dajte mi pet minuta i pokušat ću to objasniti na jednostavnom engleskom.
Pregled VLOOKUP-a od 30 sekundi
Recimo da imate tablicu evidencije zaposlenika. Prvi stupac je broj zaposlenika, a preostali stupci su razni podaci o zaposleniku. Kad god na radnom listu imate broj zaposlenika, možete koristiti VLOOKUP za vraćanje određenog podatka o zaposleniku. Sintaksa je VLOOKUP (vrijednost, raspon podataka, br. Kol., FALSE). U Excelu se kaže: "Idite na raspon podataka. Pronađite redak koji ima (vrijednost) u prvom stupcu raspona podataka. Vratite vrijednost (stupca br.) Iz tog retka. Kad to shvatite, vrlo je jednostavan i moćan.
Problem
Jednog dana imate situaciju u kojoj imate ime zaposlenika, ali trebate broj zaposlenika. Na sljedećoj slici imate ime u A10 i trebate pronaći broj zaposlenika u B10.
Kada je ključno polje desno od podataka koje želite preuzeti, VLOOKUP neće raditi. Kad bi samo VLOOKUP prihvatio -1 kao broj stupca, ne bi bilo problema. Ali, nije. Jedno od uobičajenih rješenja je privremeno umetanje novog stupca A, kopiranje stupca s imenima u novi stupac A, popunjavanje VLOOKUP-om, lijepljenje posebnih vrijednosti, a zatim brisanje privremenog stupca A. Excel profesionalci ovaj potez vjerojatno mogu učiniti u snu.
Predložit ću vam da prihvatite izazov i pokušate koristiti ovu metodu u jednom koraku. Da, morat ćete nanijeti formulu na zid nekoliko tjedana, ali i to ste već davno radili s VLOOKUP-om, zar ne?
Mislim da je razlog što je ovo tako teško taj što koristite dvije funkcije koje vjerojatno nikada prije niste koristili. Dakle, dajte da ga rastavim na dva dijela.
Prvo, tu je funkcija INDEX (). Ovo je užasno imenovana funkcija. Kad netko kaže "indeks", to mi ne dočarava ništa slično onome što radi ova funkcija. Index zahtijeva tri argumenta.
=INDEX(data range, row number, column number)
Na engleskom jeziku Excel prelazi na raspon podataka i vraća vam vrijednost na presjeku (retka) th reda i (stupca number) th stupca. Hej, razmisli - ovo je prilično jednostavno, zar ne? =INDEX($A$2:$C$6,4,2)dat će vam vrijednost u B5.
Primjena indeks () za naš problem, možete shvatiti da se vrate broj zaposlenika iz raspona, koju će koristiti ovo: =INDEX($A$2:$A$6,?,1). Zapravo, ovaj njegov dio djeluje toliko trivijalno da se čini beskorisnim. Ali, kada zamijenite upitnik funkcijom MATCH (), imate rješenje.
Evo sintakse:
=MATCH(Value, Single-column data range, FALSE)
Excel kaže: "Pretražite raspon podataka i recite mi relativni broj retka u kojem ćete pronaći podudaranje za (podatke). Dakle, da biste pronašli koji redak ima zaposlenik u A10, trebali biste ga koristiti =MATCH(A10,$B$2:$B$6,FALSE). Da, ovo je složenije od indeksa , ali to bi trebalo biti točno uz ulicu profesionalaca VLOOKUP-a. Ako A10 sadrži "Miller, Bob", tada će ovaj MATCH vratiti da se nalazi u 3. redu raspona B2: B6.
Eto ga - funkcija MATCH () govori funkciji Index u koji red treba pogledati - gotovi ste. Uzmite funkciju Index, zamijenite naš upitnik funkcijom MATCH i sada možete napraviti ekvivalent VLOOKUP-ovima kada polje ključa nije u lijevom stupcu. Evo funkcije koju treba koristiti:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Ljepljiva bilješka na mom zidu zapravo je prikazuje u dvije crte. Prvo sam napisao objašnjenje za MATCH (). Ispod toga napisao sam objašnjenje za INDEX (). Zatim sam nacrtao oblik lijevka između njih dvoje kako bih naznačio da funkcija MATCH () pada na drugi argument funkcije INDEX ().
Prvih nekoliko puta kad sam morao napraviti jedan od ovih, bio sam u iskušenju da samo zalupim novi privremeni stupac A tamo, ali umjesto toga prošao sam bol zbog toga. Brži je i zahtijeva manje manipulacija. Dakle, sljedeći put kada poželite da u funkciju VLOOKUP unesete negativan broj, isprobajte ovu neobičnu kombinaciju INDEKSA i UTAKMICE kako biste riješili svoje probleme.
