Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția Excel VLOOKUP în Excel pentru a căuta o valoare.
Prezentare generală a funcției VLOOKUP
Funcția VLOOKUP Vlookup înseamnă căutare verticală. Se caută o valoare în coloana din stânga a unui tabel. Apoi returnează o valoare un număr specificat de coloane la dreapta din valoarea găsită. Este la fel ca un hlookup, cu excepția faptului că privește valorile pe verticală în loc de orizontală.
(Observați cum apar datele introduse de formulă)
Sintaxa și argumentele funcției VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
valoare_cercare - Valoarea pe care doriți să o căutați.
table_array - Intervalul de date care conține atât valoarea pe care doriți să o căutați, cât și valoarea pe care doriți să o returneze vlookup. Coloana care conține valorile căutării trebuie să fie cea mai stângă coloană.
col_index_num - Numărul coloanei din intervalul de date, din care doriți să returnați o valoare.
range_lookup - Adevărat sau fals. FALSE caută o potrivire exactă. TRUE caută cea mai apropiată potrivire care este mai mică sau egală cu valoarea de căutare. Dacă se alege TRUE, coloana din stânga (coloana de căutare) trebuie să fie sortată ascendent (de la cea mai mică la cea mai mare).
Ce este funcția VLOOKUP?
Fiind una dintre funcțiile mai vechi din lumea foilor de calcul, funcția VLOOKUP este folosită Vertical Căutări. Are câteva limitări care sunt adesea depășite cu alte funcții, cum ar fi INDEX / MATCH. Cu toate acestea, are avantajul de a fi o singură funcție care poate face o căutare de potrivire exactă de la sine. De asemenea, tinde să fie una dintre primele funcții despre care oamenii învață.
Exemplu de bază
Să analizăm un eșantion de date dintr-o carte de note. Vom aborda câteva exemple de extragere a informațiilor pentru anumiți studenți.
Dacă vrem să aflăm în ce clasă este Bob, am scrie formula:
| 1 | = VLOOKUP („Bob”, A2: C5, 2, FALSE) |
Lucrurile importante de reținut sunt că elementul pe care îl căutăm (Bob) trebuie să se afle în prima coloană a intervalului nostru de căutare (A2: C5). Am spus funcției că dorim să returnăm o valoare din 2nd coloana, care în acest caz este coloana B. În cele din urmă, am indicat că dorim să facem un potrivire perfecta plasând Fals ca ultim argument. Aici, răspunsul va fi „Lectură”.
Sfat lateral: De asemenea, puteți utiliza numărul 0 în loc de Fals ca argument final, deoarece acestea au aceeași valoare. Unii oameni preferă acest lucru, deoarece este mai rapid să scrieți. Știți doar că ambele sunt acceptabile.
Date schimbate
Pentru a adăuga câteva clarificări primului nostru exemplu, elementul de căutare nu trebuie să se afle în coloana A a foii de calcul, ci doar în prima coloană a intervalului de căutare. Să folosim același set de date:
Acum, să găsim nota pentru clasa Știință. Formula noastră ar fi
| 1 | = VLOOKUP („Știință”, B2: C5, 2, FALS) |
Aceasta este încă o formulă validă, întrucât prima coloană din intervalul nostru de căutare este coloana B, unde se va găsi termenul nostru de căutare „Știință”. Întoarcem o valoare din 2nd coloana intervalului de căutare, care în acest caz este coloana C. Răspunsul este „A-“.
Utilizarea wildcard
Funcția VLOOKUP acceptă utilizarea comodinelor „*” și „?” când faci căutări. De exemplu, să spunem că am uitat cum să scriem numele lui Frank și am vrut doar să căutăm un nume care începe cu „F”. Am putea scrie formula
| 1 | = VLOOKUP ("F *", A2: C5, 2, FALSE) |
Aceasta ar putea găsi numele Frank în rândul 5 și apoi va returna valoarea de la 2nd coloana relativă. În acest caz, răspunsul va fi „Știință”.
Potrivire neexactă
De cele mai multe ori, va trebui să vă asigurați că ultimul argument din VLOOKUP este fals (sau 0), astfel încât să obțineți o potrivire exactă. Cu toate acestea, există de câteva ori când este posibil să căutați o potrivire neexactă. Dacă aveți o listă de date sortate, puteți utiliza și VLOOKUP pentru a returna rezultatul pentru elementul care este fie același, fie următorul cel mai mic. Acest lucru este adesea utilizat atunci când se ocupă cu creșterea gamelor de numere, cum ar fi într-un tabel de impozite sau bonusuri de comisioane.
Să presupunem că doriți să găsiți rata de impozitare pentru un venit introdus în celula D2. Formula din D4 poate fi:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Diferența în această formulă este că ultimul nostru argument este „Adevărat”. În exemplul nostru specific, putem vedea că atunci când individul nostru introduce un venit de 45.000 USD, va avea o rată de impozitare de 15%.
Notă: Deși, de obicei, dorim o potrivire exactă cu False ca argument, uitați să specificați 4a argument într-un VLOOKUP, implicit este True. Acest lucru vă poate determina să obțineți rezultate neașteptate, mai ales atunci când vă ocupați de valorile textului.
Coloană dinamică
VLOOKUP îți cere să dai un argument care să spună din ce coloană vrei să returnezi o valoare, dar poate apărea ocazia când nu știi unde va fi coloana sau dacă vrei să permiți utilizatorului să schimbe din ce coloană să revină. În aceste cazuri, poate fi util să utilizați funcția MATCH pentru a determina numărul coloanei.
Să luăm din nou exemplul cărții de note, cu câteva date în E2 și E4. Pentru a obține numărul coloanei, am putea scrie o formulă de
| 1 | = MATCH (E2, A1: C1, 0) |
Aceasta va încerca să găsească poziția exactă a „Gradului” în intervalul A1: C1. Răspunsul va fi 3. Știind acest lucru, îl putem conecta la o funcție VLOOKUP și putem scrie o formulă în E6 astfel:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Deci, funcția MATCH se va evalua la 3, iar asta îi spune VLOOKUP să returneze un rezultat din 3rd coloană din intervalul A2: C5. În general, obținem rezultatul dorit de „C”. Formula noastră este dinamică acum prin faptul că putem schimba fie coloana pe care trebuie să o privim, fie numele pe care să îl căutăm.
Limitări VLOOKUP
După cum sa menționat la începutul articolului, cea mai mare cădere a VLOOKUP este că necesită ca termenul de căutare să fie găsit în coloana din stânga cea mai mare a intervalului de căutare. Deși există câteva trucuri fanteziste pe care le puteți face pentru a depăși acest lucru, alternativa obișnuită este să utilizați INDEX și MATCH. Acest combo vă oferă mai multă flexibilitate și uneori poate fi chiar un calcul mai rapid.
VLOOKUP în Foi de calcul Google
Funcția VLOOKUP funcționează exact la fel în Foi de calcul Google ca în Excel:
Note Aditionale
Utilizați funcția VLOOKUP pentru a efectua o căutare VERTICALĂ. VLOOKUP caută o potrivire exactă (range_lookup = FALS) sau cea mai apropiată potrivire care este egală sau mai mică decât valoarea de căutare (range_lookup = ADEVĂRAT, numai valori numerice) în primul rând al table_array. Apoi returnează o valoare corespunzătoare, n număr de rânduri sub potrivire.
Când utilizați un VLOOKUP pentru a găsi o potrivire exactă, mai întâi definiți o valoare de identificare pe care doriți să o căutați ca fiind valoare_cercare. Această valoare de identificare poate fi un SSN, un ID de angajat, un nume sau un alt identificator unic.
Apoi definiți intervalul (numit table_array) care conține identificatorii din rândul de sus și orice valori pe care doriți să le căutați în ultimul rând. IMPORTANT: identificatorii unici trebuie să fie în rândul de sus. Dacă nu sunt, trebuie fie să mutați rândul în partea de sus, fie să utilizați MATCH / INDEX în loc de VLOOKUP.
În al treilea rând, definiți numărul rândului (rând_index) din table_array că vrei să te întorci. Rețineți că primul rând, care conține identificatorii unici, este rândul 1. Al doilea rând este rândul 2 etc.
În cele din urmă, trebuie să indicați dacă să căutați o potrivire exactă (FALSĂ) sau cea mai apropiată potrivire (ADEVĂRAT) în range_lookup. Dacă este selectată opțiunea de potrivire exactă și nu se găsește o potrivire exactă, se returnează o eroare (# N / A). Pentru ca formula să revină necompletată sau „nu a fost găsită” sau orice altă valoare în locul valorii de eroare (# N / A) utilizați funcția IFERROR cu VLOOKUP.
Pentru a utiliza funcția VLOOKUP pentru a returna un set de potrivire aproximativ: range_lookup = ADEVĂRAT. Această opțiune este disponibilă numai pentru valorile numerice. Valorile trebuie sortate în ordine crescătoare.
Pentru a efectua o căutare orizontală, utilizați funcția HLOOKUP.
VLOOKUP Exemple în VBA
De asemenea, puteți utiliza funcția VLOOKUP în VBA. Tip:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Pentru argumentele funcției (valoare_consultare etc.), le puteți introduce direct în funcție sau puteți defini variabile de utilizat.
Reveniți la lista tuturor funcțiilor din Excel
