Sfat
Încercați să utilizați funcțiile noi 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 convenabile de utilizat decât predecesoarele lor.
Să presupunem că aveți o listă cu numere de birouri și trebuie să știți ce angajat se află în fiecare birou. Foaia de calcul este imensă, așa că ați putea crede că este o sarcină dificilă. De fapt, este destul de ușor de făcut cu o funcție de căutare.
Funcțiile VLOOKUP și HLOOKUP , împreună cu funcțiile 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 are nevoie pentru a utiliza. Pentru VLOOKUP, acest prim 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 "Vlad" 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 zona de celule care conține valoarea pe care o căutați.
Al patrulea argument este opțional. Introduceți fie TRUE, fie 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 potrivi valoarea furnizată de primul argument. Cu alte cuvinte, dacă lăsați al patrulea argument necompletat sau dacă introduceți True beneficiați de mai multă flexibilitate.
Acest exemplu vă arată cum funcționează funcția. 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.
Când vă obișnuiți cu VLOOKUP, funcția HLOOKUP este la fel de simplu de utilizat. Introduceți aceleași argumente, dar caută în rânduri în loc de coloane.
Utilizarea funcțiilor INDEX și MATCH în locul funcției VLOOKUP
Există anumite limitări la utilizarea funcției VLOOKUP: funcția VLOOKUP poate căuta o valoare doar de la stânga la dreapta. Aceasta înseamnă că coloana care conține valoarea pe care o căutați ar trebui să fie î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 funcțiilor 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.
Încercați
Dacă doriți să experimentați cu funcțiile de căutare înainte să le încercați cu datele proprii, iată câteva date exemplu.
VLOOKUP - exemplu la locul de muncă
Copiați următoarele date într-o foaie de calcul necompletată.
Sfat
Înainte să lipiți datele în Excel, setați lățimea 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 să lipiți datele în Excel, setați lățimea 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 să lipiți datele în Excel, setați lățimea 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 |
Consultați și
Cartelă de referințe rapide: informații despre VLOOKUP
Funcții de căutare și de referință (referință)
Utilizarea argumentului matrice_tabel într-o funcție VLOOKUP