Vizionați video – Cum se folosește funcția VLOOKUP cu criterii multiple

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 Math (așa cum se arată mai jos):

Utilizarea funcției VLOOKUP pentru a obține scorul de matematică pentru fiecare elev 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 asistență.
• Utilizarea funcției ALEGE.

VLOOKUP cu mai multe criterii – Folosirea unei coloane de ajutor

Sunt un fan al coloanelor de ajutor în Excel.

Găsesc două avantaje semnificative ale utilizării coloanelor de ajutor în comparație cu formulele matrice: p>

• 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 de date mari).

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:

Cum funcționează acest lucru?

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 din 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ționale 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ă, în timp ce 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ă niciun 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 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.

Așadar, iată metoda non-coloană de ajutor pentru dvs.

Descărcați fișierul de exemplu

VLOOKUP cu mai multe criterii – Utilizarea funcției CHOOSE

Utilizarea formulelor matrice în loc de helper coloanele vă salvează proprietăți imobiliare ale foii 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ă acest lucru?

Formula folosește și conceptul de coloană de ajutor. Diferența este că, în loc să introduceți coloana de asistență î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 vreau să spun 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 că Alege formula ar da.

Rezultatul este {„Matt | Unit Test”, 91; ”Bob | Unit Test”, 52; ……}

Este o matrice în care o virgulă reprezintă celula următoare 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, acesta caută pur și simplu valoarea din prima coloană (a acestor date virtuale din 2 coloane) și returnează scorul corespunzător.

Descărcați Exemplu de fișier

De asemenea, puteți utiliza alte formule pentru a efectua o căutare cu mai multe criterii (cum ar fi INDEX / MATCH sau SUMPRODUCT).