Vizionați videoclipuri - Cum să utilizați funcția VLOOKUP cu mai multe criterii
Funcția Excel VLOOKUP, în forma sa de bază, poate căuta o valoare de căutare și poate returna valoarea corespunzătoare din rândul specificat.
Dar de multe ori este nevoie să utilizați Excel VLOOKUP cu mai multe criterii.
Cum se folosește VLOOKUP cu mai multe criterii
Să presupunem că aveți date cu numele studenților, tipul examenului și scorul la matematică (așa cum se arată mai jos):
Utilizarea funcției VLOOKUP pentru a obține scorul de matematică pentru fiecare student pentru nivelurile de examen respective ar putea fi o provocare.
Se poate argumenta că o opțiune mai bună ar fi restructurarea setului de date sau utilizarea unui tabel pivot. Dacă asta funcționează pentru tine, nimic de genul asta. Dar, în multe cazuri, sunteți blocat cu datele pe care le aveți și este posibil ca tabelul pivot să nu fie o opțiune.
În astfel de cazuri, acest tutorial este pentru dvs.
Acum există două moduri în care puteți obține valoarea de căutare utilizând VLOOKUP cu mai multe criterii.
- Utilizarea unei coloane de ajutor.
- Folosind funcția ALEGE.
VLOOKUP cu mai multe criterii - Utilizarea unei coloane de ajutor
Sunt un fan al coloanelor de ajutor din Excel.
Găsesc două avantaje semnificative ale utilizării coloanelor de ajutor față de formulele matrice:
- Este ușor să înțelegeți ce se întâmplă în foaia de lucru.
- Îl face mai rapid în comparație cu funcțiile matrice (vizibile în seturi mari de date).
Acum, nu mă înțelege greșit. Nu sunt împotriva formulelor matrice. Îmi place lucrurile uimitoare care se pot face cu formule matrice. Doar că le păstrez pentru ocazii speciale în care toate celelalte opțiuni nu sunt de niciun ajutor.
Revenind la întrebarea în cauză, coloana de ajutor este necesară pentru a crea un calificativ unic. Acest calificativ unic poate fi apoi utilizat pentru a căuta valoarea corectă. De exemplu, există trei Matt în date, dar există o singură combinație de Matt și Unit Test sau Matt și Mid-Term.
Iată pașii:
- Introduceți o coloană de ajutor între coloana B și C.
- Utilizați următoarea formulă în coloana de ajutor: = A2 & ”|” & B2
- Acest lucru ar crea calificative unice pentru fiecare instanță, așa cum se arată mai jos.
- Acest lucru ar crea calificative unice pentru fiecare instanță, așa cum se arată mai jos.
- Utilizați următoarea formulă în G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Copiați pentru toate celulele.
Cum funcționează asta?
Creăm calificative unice pentru fiecare instanță a unui nume și a examenului. În funcția VLOOKUP utilizată aici, valoarea de căutare a fost modificată la $ F3 & ”|” & G $ 2, astfel încât ambele criterii de căutare sunt combinate și sunt utilizate ca o singură valoare de căutare. De exemplu, valoarea de căutare pentru funcția VLOOKUP în G2 este Matt | Unit Test. Acum această valoare de căutare este utilizată pentru a obține scorul de la C2: D19.
Clarificări:
Există câteva întrebări care vă vor veni în minte, așa că m-am gândit că voi încerca să răspund aici:
- De ce am folosit | simbol în timp ce alătură cele două criterii? - În unele condiții excepțional de rare (dar posibile), este posibil să aveți două criterii care sunt diferite, dar ajunge să dea același rezultat atunci când este combinat. Iată un exemplu foarte simplu (iartă-mă pentru lipsa mea de creativitate aici):
Rețineți că, deși A2 și A3 sunt diferite, iar B2 și B3 sunt diferite, combinațiile ajung să fie aceleași. Dar dacă utilizați un separator, atunci chiar și combinația ar fi diferită (D2 și D3).
- De ce am inserat coloana de ajutor între coloana B și C și nu în extrema stângă? - Nu există nici un rău în introducerea coloanei de ajutor în partea stângă extremă. De fapt, dacă nu doriți să vă temperați cu datele originale, acesta ar trebui să fie calea de urmat. Am făcut-o deoarece mă face să folosesc un număr mai mic de celule în funcția VLOOKUP. În loc să am 4 coloane în matricea de tabele, m-aș putea descurca doar cu 2 coloane. Dar eu sunt doar eu.
Acum nu există o dimensiune care să se potrivească tuturor. Unele persoane pot prefera să nu folosească nicio coloană de ajutor în timp ce utilizează VLOOKUP cu mai multe criterii.
Iată deci metoda coloanei non-helper pentru dvs.
Descărcați fișierul de exemplu
VLOOKUP cu mai multe criterii - Utilizarea funcției ALEGE
Utilizarea formulelor matrice în locul coloanelor de ajutor vă salvează proprietăți imobiliare din foaia de lucru, iar performanța poate fi la fel de bună dacă este utilizată de mai puține ori într-un registru de lucru.
Având în vedere același set de date ca cel folosit mai sus, iată formula care vă va oferi rezultatul:
= VLOOKUP ($ E3 & ”|” & F $ 2, ALEGE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Deoarece aceasta este o formulă matrice, utilizați-o cu Control + Shift + Enter, în loc de doar Enter.
Cum funcționează asta?
Formula folosește și conceptul de coloană de ajutor. Diferența este că, în loc să introduceți coloana de ajutor în foaia de lucru, considerați-o ca date de asistență virtuală care fac parte din formulă.
Permiteți-mi să vă arăt ce înțeleg prin date de asistență virtuală.
În ilustrația de mai sus, în timp ce selectez partea ALEGE din formulă și apăs F9, arată rezultatul pe care l-ar da formula ALEGE.
Rezultatul este {"Matt | Unit Test", 91; "Bob | Unit Test", 52; …}
Este o matrice în care o virgulă reprezintă următoarea celulă din același rând și punct și virgulă reprezintă următoarele date în coloana următoare. Prin urmare, această formulă creează 2 coloane de date - o coloană are identificatorul unic și una are scorul.
Acum, când utilizați funcția VLOOKUP, aceasta caută pur și simplu valoarea din prima coloană (a acestor date de 2 coloane virtuale) și returnează scorul corespunzător.
Descărcați fișierul de exemplu
De asemenea, puteți utiliza alte formule pentru a efectua o căutare cu mai multe criterii (cum ar fi INDEX / MATCH sau SUMPRODUCT).
Există vreun alt mod în care știi să faci asta? Dacă da, împărtășiți cu mine în secțiunea de comentarii.
Vă pot plăcea, de asemenea, următoarele tutoriale de căutare:
- VLOOKUP vs. INDICE / MATCH
- Obțineți valori multiple de căutare fără repetare într-o singură celulă.
- Cum se face VLOOKUP Case Sensitive.
- Utilizați IFERROR cu VLOOKUP pentru a scăpa de erori # N / A.
