Sfat: Încercați să utilizați noile funcții XLOOKUP și XMATCH , versiuni îmbunătățite ale funcțiilor descrise în acest articol. Aceste funcții noi funcționează în orice direcție și returnează potriviri exacte în mod implicit, făcându-le mai ușor și mai convenabil de utilizat decât predecesorii lor.
Să presupunem că aveți o listă de numere de locație de birou și trebuie să știți ce angajați se află în fiecare birou. Foaia de calcul este imensă, deci poate considerați că este o sarcină dificilă. Este de fapt destul de ușor de făcut cu o funcție de căutare.
Funcțiile VLOOKUP și HLOOKUP , împreună cu INDEX și MATCH, sunt unele dintre cele mai utile funcții din Excel.
Notă: Caracteristica Expert căutare nu mai este disponibilă în Excel.
Iată un exemplu de utilizare a funcției VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
În acest exemplu, B2 este primul argument - un element de date de care funcția trebuie să funcționeze. Pentru VLOOKUP, primul argument este valoarea pe care doriți să o găsiți. Acest argument poate fi o referință de celulă sau o valoare fixă, cum ar fi "smith" sau 21.000. Al doilea argument este zona de celule, C2-:E7, în care se caută valoarea pe care doriți să o găsiți. Al treilea argument este coloana din acea zonă de celule care conține valoarea pe care o căutați.
Al patrulea argument este opțional. Introduceți TRUE sau FALSE. Dacă introduceți True sau lăsați argumentul necompletat, funcția returnează o potrivire aproximativă a valorii pe care o specificați în primul argument. Dacă introduceți FALSE, funcția va corespunde valorii furnizate de primul argument. Cu alte cuvinte, dacă lăsați al patrulea argument necompletat sau dacă introduceți TRUE, aveți mai multă flexibilitate.
Acest exemplu vă arată cum funcționează funcția. Atunci când introduceți o valoare în celula B2 (primul argument), VLOOKUP caută în celulele din zona C2:E7 (al doilea argument) și returnează cea mai apropiată potrivire aproximativă din a treia coloană din zonă, coloana E (al treilea argument).
Al patrulea argument este gol, deci funcția returnează o potrivire aproximativă. Dacă nu ar returna nimic, ar trebui să introduceți una din valorile din coloanele C și D pentru a obține un rezultat.
Atunci când vă obișnuiți cu VLOOKUP, funcția HLOOKUP este la fel de ușor de utilizat. Introduceți aceleași argumente, dar aceasta caută în rânduri în loc de coloane.
Utilizarea funcțiilor INDEX și MATCH în loc de VLOOKUP
Există anumite limitări în utilizarea funcției VLOOKUP: funcția VLOOKUP poate căuta doar o valoare de la stânga la dreapta. Acest lucru înseamnă că coloana care conține valoarea pe care o căutați trebuie să se afle întotdeauna în partea stângă a coloanei care conține valoarea returnată. Acum, dacă foaia de calcul nu este construită astfel, nu utilizați VLOOKUP. Utilizați în schimb combinația de funcții INDEX și MATCH.
Acest exemplu ilustrează o listă mică în care valoarea după care dorim să căutăm, Chicago, nu se află în coloana cea mai din stânga. Așadar, nu putem utiliza VLOOKUP. În schimb, vom utiliza funcția MATCH pentru a găsi Chicago în zona B1:B11. Acesta se află în rândul 4. Apoi, INDEX utilizează acea valoare ca argument de căutare și găsește populația pentru Chicago în coloana 4 (coloana D). Formula utilizată este afișată în celula A14.
Pentru mai multe exemple de utilizare a funcțiilor INDEX și MATCH în loc de VLOOKUP, consultați articolul https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ de Bill Jelen, Microsoft MVP.
Încercați
Dacă doriți să experimentați cu funcții de căutare înainte de a le încerca cu propriile date, iată câteva date eșantion.
VLOOKUP Exemplu la locul de muncă
Copiați următoarele date într-o foaie de calcul necompletată.
Sfat: Înainte de a lipi datele în Excel, setați lățimile coloanelor de la A la C la 250 pixeli și faceți clic pe Încadrare text (fila Pornire , grupul Aliniere ).
|
Densitate |
Vâscozitate |
Temperatură |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Formulă |
Descriere |
Rezultat |
|
=VLOOKUP(1;A2:C10;2) |
Utilizând o potrivire aproximativă, caută valoarea 1 în coloana A, găsește cea mia mare valoare mai mică sau egală cu 1 în coloana A, care este 0,946, apoi returnează valoarea din coloana B în același rând. |
2,17 |
|
=VLOOKUP(1;A2:C10;3;TRUE) |
Utilizând o potrivire aproximativă, caută valoarea 1 în coloana A, găsește cea mai mare valoare mai mică sau egală cu 1 în coloana A, care este 0,946, apoi returnează valoarea din coloana C din același rând. |
100 |
|
=VLOOKUP(0,7;A2:C10;3;FALSE) |
Utilizând o potrivire exactă, caută valoarea 0,7 în coloana A. Deoarece nu există nicio potrivire exactă în coloana A, se returnează o eroare. |
#N/A |
|
=VLOOKUP(0,1;A2:C10;2;TRUE) |
Utilizând o potrivire aproximativă, caută valoarea 0,1 în coloana A. Deoarece 0,1 este o valoare mai mică decât cea mai mică valoare din coloana A, se returnează o eroare. |
#N/A |
|
=VLOOKUP(2;A2:C10;2;TRUE) |
Utilizând o potrivire aproximativă, caută valoarea 2 în coloana A, găsește cea mai mare valoare mai mică sau egală cu 2 din coloana A, care este 1,29, apoi returnează valoarea din coloana B din același rând. |
1,71 |
Exemplu HLOOKUP
Copiați toate celulele din acest tabel și lipiți-le în celula A1 într-o foaie de lucru necompletată în Excel.
Sfat: Înainte de a lipi datele în Excel, setați lățimile coloanelor de la A la C la 250 pixeli și faceți clic pe Încadrare text (fila Pornire , grupul Aliniere ).
|
Osii |
Rulmenți |
Șuruburi |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Formulă |
Descriere |
Rezultat |
|
=HLOOKUP("Osii"; A1:C4; 2; TRUE) |
Caută „Osii” în rândul 1 și returnează valoarea din rândul 2 ce este în aceeași coloană (coloana A). |
4 |
|
=HLOOKUP("Rulmenți"; A1:C4; 3; FALSE) |
Caută „Rulmenți” în rândul 1 și returnează valoarea din rândul 3 ce este în aceeași coloană (coloana B). |
7 |
|
=HLOOKUP("B"; A1:C4; 3; TRUE) |
Caută „R” în rândul 1 și returnează valoarea din rândul 3 ce este în aceeași coloană. Pentru că nu se găsește o potrivire exactă pentru „B”, se utilizează cea mai mare valoare din rândul 1 care este mai mică decât „B”: „Osii”, din coloana A. |
5 |
|
=HLOOKUP("Șuruburi"; A1:C4; 4) |
Caută „Șuruburi” în rândul 1 și returnează valoarea din rândul 4 ce este în aceeași coloană (coloana C). |
11 |
|
=HLOOKUP(3; {1;2;3\"a";"b";"c"\"d";"e";"f"}; 2; TRUE) |
Caută numărul 3 în constanta matrice de trei rânduri și returnează valoarea din rândul 2 aflată în aceeași coloană (în acest caz cea de-a treia). Sunt trei rânduri de valori în constanta matrice, fiecare rând fiind separat de punct și virgulă (;). Deoarece "c" se găsește în rândul 2 și în aceeași coloană ca 3, se returnează „c”. |
c |
Exemple INDEX și MATCH
Acest ultim exemplu utilizează funcțiile INDEX și MATCH împreună pentru a returna numărul celei mai vechi facturi și data corespunzătoare pentru fiecare dintre cele cinci orașe. Deoarece data este returnată ca număr, utilizăm funcția TEXT pentru a o formata ca dată. Funcția INDEX utilizează, de fapt, rezultatul funcției MATCH drept argument. Combinația funcțiilor INDEX și MATCH se utilizează de două ori în fiecare formulă - mai întâi, pentru a returna numărul facturii, apoi pentru a returna data.
Copiați toate celulele din acest tabel și lipiți-le în celula A1 într-o foaie de lucru necompletată în Excel.
Sfat: Înainte de a lipi datele în Excel, setați lățimile coloanelor de la A la D la 250 pixeli și faceți clic pe Încadrare text (fila Pornire , grupul Aliniere ).
|
Factură |
Localitate |
Data facturării |
Cea mai veche factură în funcție de localitate, cu data |
|
3115 |
Arad |
07.04.12 |
="Arad = "&INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);3);"dd.mm.yy") |
|
3137 |
Arad |
09.04.12 |
="Arad = "&INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);3);"dd.mm.yy") |
|
3154 |
Arad |
11.04.12 |
="Domnești = "&INDEX($A$2:$C$33;MATCH("Domnești";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Domnești";$B$2:$B$33;0);3);"dd.mm.yy") |
|
3191 |
Arad |
21.04.12 |
="Cluj-Napoca = "&INDEX($A$2:$C$33;MATCH("Cluj-Napoca";$B$2:$B$33;0);1)& "; Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Cluj-Napoca";$B$2:$B$33;0);3);"dd.mm.yy") |
|
3293 |
Arad |
25.04.12 |
="București = "&INDEX($A$2:$C$33;MATCH("București";$B$2:$B$33;0);1)& "; Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("București";$B$2:$B$33;0);3);"dd.mm.yy") |
|
3331 |
Arad |
27.04.12 |
|
|
3350 |
Arad |
28.04.12 |
|
|
3390 |
Arad |
01.05.12 |
|
|
3441 |
Arad |
02.05.12 |
|
|
3517 |
Arad |
08.05.12 |
|
|
3124 |
Arad |
09.04.12 |
|
|
3155 |
Arad |
11.04.12 |
|
|
3177 |
Arad |
19.04.12 |
|
|
3357 |
Arad |
28.04.12 |
|
|
3492 |
Arad |
06.05.12 |
|
|
3316 |
Domnești |
25.04.12 |
|
|
3346 |
Domnești |
28.04.12 |
|
|
3372 |
Domnești |
01.05.12 |
|
|
3414 |
Domnești |
01.05.12 |
|
|
3451 |
Domnești |
02.05.12 |
|
|
3467 |
Domnești |
02.05.12 |
|
|
3474 |
Domnești |
04.05.12 |
|
|
3490 |
Domnești |
05.05.12 |
|
|
3503 |
Domnești |
08.05.12 |
|
|
3151 |
Cluj-Napoca |
09.04.12 |
|
|
3438 |
Cluj-Napoca |
02.05.12 |
|
|
3471 |
Cluj-Napoca |
04.05.12 |
|
|
3160 |
București |
18.04.12 |
|
|
3328 |
București |
26.04.12 |
|
|
3368 |
București |
29.04.12 |
|
|
3420 |
București |
01.05.12 |
|
|
3501 |
București |
06.05.12 |
