Pogledajte video - Kako koristiti funkciju VLOOKUP s više kriterija
Excel VLOOKUP funkcija u svom osnovnom obliku može tražiti jednu vrijednost pretraživanja i vratiti odgovarajuću vrijednost iz navedenog retka.
No često postoji potreba za korištenjem Excel VLOOKUP -a s više kriterija.
Kako koristiti VLOOKUP s više kriterija
Pretpostavimo da imate podatke s imenom učenika, vrstom ispita i rezultatom iz matematike (kao što je prikazano u nastavku):
Korištenje funkcije VLOOKUP za dobivanje ocjene iz matematike za svakog učenika za odgovarajuće ispitne razine moglo bi biti izazov.
Može se tvrditi da bi bolja opcija bila restrukturiranje skupa podataka ili uporaba zaokretne tablice. Ako vam to uspije, ništa slično. No, u mnogim slučajevima imate problema s podacima koje imate i zaokretna tablica možda nije opcija.
U takvim slučajevima ovaj je vodič za vas.
Sada postoje dva načina na koje možete dobiti vrijednost pretraživanja pomoću VLOOKUP -a s više kriterija.
- Korištenje stupca pomoćnika.
- Korištenje funkcije IZABERI.
VLOOKUP s više kriterija - pomoću stupca pomoćnika
Ljubitelj sam pomoćnih stupaca u Excelu.
Nalazim dvije značajne prednosti korištenja pomoćnih stupaca u odnosu na formule niza:
- Olakšava razumijevanje onoga što se događa na radnom listu.
- To ga čini bržim u usporedbi s funkcijama niza (uočljivo u velikim skupovima podataka).
Nemojte me krivo shvatiti. Nisam protiv formula niza. Volim nevjerojatne stvari koje se mogu učiniti s formulama polja. Samo ih čuvam za posebne prigode kada sve ostale mogućnosti nisu od pomoći.
Vraćajući se na pitanje, pomoćni stupac potreban je za stvaranje jedinstvenog kvalifikatora. Ovaj jedinstveni kvalifikator tada se može koristiti za traženje ispravne vrijednosti. Na primjer, u podacima postoje tri Matta, ali postoji samo jedna kombinacija Matt i Unit Test ili Matt i Mid-Term.
Evo koraka:
- Umetnite pomoćni stupac između stupaca B i C.
- U pomoćnom stupcu upotrijebite sljedeću formulu: = A2 & ”|” & B2
- Time bi se stvorili jedinstveni kvalifikatori za svaku instancu kao što je prikazano u nastavku.
- Time bi se stvorili jedinstveni kvalifikatori za svaku instancu kao što je prikazano u nastavku.
- Koristite sljedeću formulu u G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopija za sve ćelije.
Kako ovo radi?
Izrađujemo jedinstvene kvalifikatore za svaku instancu imena i ispita. U ovdje korištenoj funkciji VLOOKUP vrijednost pretraživanja je izmijenjena u $ F3 & ”|” & G $ 2, tako da su oba kriterija pretraživanja kombinirana i koriste se kao jedna vrijednost pretraživanja. Na primjer, vrijednost pretraživanja za funkciju VLOOKUP u G2 je Matt | Unit Test. Sada se ova vrijednost pretraživanja koristi za dobivanje rezultata iz C2: D19.
Pojašnjenja:
Postoji nekoliko pitanja koja će vam vjerojatno pasti na pamet pa sam mislio da ću pokušati odgovoriti ovdje:
- Zašto sam koristio | simbol pridružujući dva kriterija? - U nekim iznimno rijetkim (ali mogućim) uvjetima možete imati dva različita kriterija, ali na kraju daju isti rezultat kada se kombiniraju. Evo vrlo jednostavnog primjera (oprostite mi na nedostatku kreativnosti ovdje):
Imajte na umu da, iako su A2 i A3 različiti, a B2 i B3 različiti, kombinacije na kraju ostaju iste. Ali ako koristite separator, čak bi i kombinacija bila drugačija (D2 i D3).
- Zašto sam pomoćni stupac umetnuo između stupaca B i C, a ne krajnje lijevo? - Nema štete ako umetnete pomoćni stupac krajnje lijevo. Zapravo, ako se ne želite pomiriti s izvornim podacima, to bi trebao biti pravi način. Učinio sam to jer me tjera da koristim manji broj ćelija u funkciji VLOOKUP. Umjesto da imam 4 stupca u nizu tablica, mogao bih upravljati sa samo 2 stupca. Ali to sam samo ja.
Sada ne postoji jedna veličina koja odgovara svima. Neki ljudi mogu radije ne koristiti stupac pomoćnika dok koriste VLOOKUP s više kriterija.
Dakle, evo metode stupca koji ne pomažu.
Preuzmite datoteku primjera
VLOOKUP s više kriterija - Korištenje funkcije IZABERI
Korištenje formula polja umjesto pomoćnih stupaca štedi nekretnine na radnom listu, a izvedba može biti jednako dobra ako se koristi manje puta u radnoj knjizi.
S obzirom na isti skup podataka koji je korišten gore, evo formule koja će vam dati rezultat:
= VLOOKUP ($ E3 & ”|” & F $ 2, ODABERITE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Budući da je ovo formula niza, koristite je sa Control + Shift + Enter, umjesto samo Enter.
Kako ovo radi?
Formula također koristi koncept pomoćnog stupca. Razlika je u tome što umjesto stavljanja stupca pomoćnika na radni list, smatrajte to virtualnim podacima pomoćnika koji su dio formule.
Dopustite mi da vam pokažem što mislim pod virtualnim podacima pomoćnika.
Na gornjoj ilustraciji, dok odabirem dio IZBOR formule i pritisnem F9, ona prikazuje rezultat koji bi dala formula IZABERI.
Rezultat je {"Matt | Unit Test", 91; "Bob | Unit Test", 52;…}
To je niz u kojem zarez predstavlja sljedeću ćeliju u istom retku, a točka -zarez predstavlja da su sljedeći podaci u sljedećem stupcu. Stoga ova formula stvara 2 stupca podataka - jedan stupac ima jedinstveni identifikator, a jedan rezultat.
Sada, kada koristite funkciju VLOOKUP, ona jednostavno traži vrijednost u prvom stupcu (ovih podataka iz virtualnog 2 stupca) i vraća odgovarajuću ocjenu.
Preuzmite datoteku primjera
Također možete koristiti druge formule za pretraživanje s više kriterija (poput INDEX/MATCH ili SUMPRODUCT).
Postoji li neki drugi način da to učinite? Ako da, podijelite sa mnom u odjeljku komentara.
Možda će vam se svidjeti i sljedeći LOOKUP vodiči:
- VLOOKUP vs. INDEKS/UTAKMICA
- Dobijte više vrijednosti pretraživanja bez ponavljanja u jednoj ćeliji.
- Kako VLOOKUP osjetiti velika i mala slova
- Upotrijebite IFERROR s VLOOKUP -om da biste se riješili #N/A grešaka.
