VLOOKUP în Excel: Aceasta este ceea ce poate face funcția

Aplicarea și definirea acestei funcții Excel

VLOOKUP este o funcție Excel cu ajutorul căreia utilizatorul poate căuta și evalua conținutul tabelului. Această funcție este disponibilă în versiuni din Excel 2007 pentru Windows și Mac.

Ce este VLOOKUP?

Utilizările posibile ale VLOOKUP trebuie explicate aici folosind un exemplu: în acesta sunteți un mare fan al literaturii și, prin urmare, ați creat propria dvs. foaie de calcul Excel în care puteți sorta cu atenție cărțile pe care le-ați colectat. Fiecare lucrare este introdusă cu informații despre următoarele categorii:

• autor

• titlu

• Numărul paginii

• Anul publicării

Acum doriți să oferiți unui prieten un sfat de carte pe care să îl luați cu dvs. la următoarea întâlnire. Din păcate, nu te poți gândi decât la autor, nu la titlul cărții. Aici intră în joc VLOOKUP, deoarece poate utiliza această valoare de intrare pentru a arunca informațiile pe care le căutați dintr-o singură lovitură.

Cum se utilizează VLOOKUP?

Înainte chiar să vă gândiți la formularea formulelor, ar trebui să se stabilească unde vor fi localizate ulterior câmpul de intrare și diferitele câmpuri de ieșire. Pentru a face acest lucru, este logic să creați un tabel separat care este inițial gol și, astfel, permite spațiul pentru informațiile menționate. Dacă proiectați acest nou tabel pe baza exemplului tabelului existent, veți avea un avantaj de economisire a timpului mai târziu.

Pe această bază, formula VLOOKUP poate fi creată manual sau generată automat de Excel. Pentru începători, merită să folosiți această din urmă abordare pentru a cunoaște treptat structura și efectul formulei. Pentru aceasta, butonul pentru „Insert function” este selectat în fila „Formule”. VLOOKUP este ascuns în fereastra care se deschide. După confirmare, se deschide din nou o fereastră în care pot fi completați cei patru parametri ai formulei. Acestea sunt:

• Criteriul de căutare

• matrice

• Indexul coloanei

• Zona_referință

Prin urmare, schița brută a formulei arată astfel:

= VLOOKUP (criteriu de căutare, matrice, index coloană, interval_link)

și într-o posibilă aplicație ca aceasta:

= VLOOKUP (H3; A3: E40; 5)

Criteriul de căutare

Pentru ca funcția să știe ce valoare trebuie utilizată ca punct de plecare, linia care a fost selectată ca câmp de intrare cu doi pași mai devreme este notată în câmpul „Criteriu de căutare”. În exemplul nostru, numele autorului cărții „Phillip Pulmann” este introdus acolo. Acest lucru face ca formula să fie flexibilă și nu trebuie ajustată din nou de îndată ce valoarea introdusă se modifică.

matrice

Câmpul de intrare „Matrix” descrie tabelul în care pot fi găsite informațiile care urmează să fie transmise. Această matrice specială conține astfel și coloanele pentru titlul cărții, numărul paginii și anul publicării.

Matricea este complet selectată o dată fără titlurile din partea stângă sus la marginea din dreapta jos. În acest fel, Excel știe ce conținut trebuie luat în considerare la evaluare.

Indexul coloanei

Câmpul de intrare pentru „indexul coloanei” solicită utilizatorului să definească coloana matricei în care este listată doar valoarea căutată. Alocarea coloanelor este numerotată cronologic. Aceasta înseamnă că prima coloană a tabelului primește valoarea 1, a doua valoarea 2 etc. În exemplul nostru, aceasta corespunde indexului coloanei 1 pentru autor, indexului coloanei 2 pentru titlu, indexului coloanei 3 pentru numărul paginii și indexul coloanei 4 pentru anul publicării.

Pentru a menține tabelul cât mai flexibil posibil, antetul coloanei poate fi legat în locul numărului. Acest lucru are avantajul că formula poate fi transferată și pe alte rânduri fără probleme, deoarece titlul coloanei poate fi adaptat flexibil de fiecare dată.

Atenţie: VLOOKUP citește matricea de la stânga la dreapta, motiv pentru care indexul coloanei trebuie plasat în dreapta coloanei pentru criteriul de căutare pentru a fi luat în considerare de funcție!

Zona_referință

Parametrul „Range_Lookup” completează formula VLOOKUP specificând acuratețea cu care se evaluează tabelul. Cu toate acestea, diferă de componentele menționate anterior ale formulei, deoarece este opțională. Dacă valoarea 0 este introdusă pentru „incorect”, Excel caută numai valoarea specificată ca criteriu de căutare. Cu valoarea 1 pentru „adevărat”, totuși, căutarea continuă pentru valori evidente dacă valoarea exactă nu a putut fi găsită.

Specificarea acestui parametru este opțional, deoarece valoarea 1 este setată implicit. Această setare va fi utilă mai târziu în VLOOKUP avansat cu mai multe criterii de căutare.

Fuziunea

De îndată ce toți parametrii necesari au fost configurați, VLOOKUP poate fi utilizat. După introducerea criteriului de căutare și confirmarea funcției, valoarea pe care o căutați apare în linia care a fost definită ca un câmp de ieșire.

În exemplul nostru, este acum afișat titlul cărții „Busola de aur”, care corespunde autorului introdus. Pentru a afla rapid numărul paginii și anul publicării, nu mai trebuie făcut nimic decât tragerea formulei VLOOKUP existente în celulele următoare. Acest lucru este atât de ușor, deoarece indexul coloanei din VLOOKUP a fost legat de antetul coloanei din primul tabel, iar al doilea tabel este, de asemenea, structurat în aceeași ordine.

În cazul în care tabelele ar trebui să difere între ele sau să apară o eroare în ciuda tuturor, formula VLOOKUP poate fi, de asemenea, modificată manual. Pentru a face acest lucru, penultima cifră pentru indexul coloanei trebuie să fie potrivită cu coloana noii valori care urmează să fie redată.

VLOOKUP cu mai multe criterii de căutare

De multe ori se întâmplă ca un singur criteriu de căutare să nu fie suficient pentru a evalua cu exactitate un tabel Excel mare. Apoi, este logic să rulați VLOOKUP cu mai multe criterii de căutare. Pentru a face acest lucru, formula existentă trebuie completată cu o funcție IF suplimentară. În acest fel, pot fi luate în considerare până la opt criterii de căutare diferite în timpul aplicației.

VLOOKUP în mai multe foi de calcul Excel

Dacă criteriul de căutare nu poate fi găsit doar într-un tabel, ci și în altul, formula VLOOKUP poate fi ajustată în consecință. Pentru aceasta, atât funcția if, cât și funcția ISERROR trebuie plasate în fața formulei existente. Pentru aceasta sunt necesari cinci parametri:

• Criteriul de căutare

• Matrix1 și Matrix 2

• Indexul coloanei1 și indexul coloanei2

Rezultatul arată astfel:

= IF (ISERROR (VLOOKUP (criteriu de căutare, matrice1, index-coloană1, 0));
VLOOKUP (criteriu de căutare; matrice2; index coloană2,0); VLOOKUP (criteriu de căutare; matrix1; index coloană1;))

și într-o posibilă aplicație ca aceasta:

= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))

Criteriul de căutare este utilizat pentru a insera valoarea care trebuie căutată în cele două tabele. Matrix1 și Matrix2 definesc zonele de celule respective ale celor două tabele. Coloanele index1 și indexul de coloane2 sunt utilizate pentru a defini mai detaliat ce coloane din tabelele respective ar trebui căutate.

Dacă valoarea pe care o căutați apare în ambele tabele, Excel va genera rezultatul din primul tabel. Cu toate acestea, dacă valoarea nu se găsește în niciuna dintre cele două tabele, apare un mesaj de eroare. Avantajul formulei este că cele două liste nu trebuie să aibă aceeași structură sau să aibă aceeași dimensiune.

Atribuiți valori categoriilor utilizând VLOOKUP

O funcție suplimentară a VLOOKUP permite împărțirea automată a valorilor listate în litere și predicate la alegere. În exemplul nostru anterior, ar trebui inserată o coloană de tabel suplimentară pentru tipul de carte. Cărțile cu o lungime de până la 50 de pagini ar trebui să se încadreze în genul nuvelei, în timp ce cărțile de la 51 la 150 de pagini sunt atribuite romanului și de la 151 de pagini romanului. Pentru a face acest lucru posibil, nu este necesară o formulă suplimentară în VLOOKUP, ci doar utilizarea parantezelor „{}”. Formula terminată arată astfel:

= VLOOKUP (B1; {1. „Nuvelă”; 51. „Novella”; 151. „Roman”}; 2)

Conținutul parantezelor crețate indică o matrice care definește aria unui tip de carte respectiv. Prin urmare, atribuirea lungimii laterale genului corespunzător este adăpostită în parantezele cretate. Formula folosește perechi de valori, fiecare separată printr-un punct. Matricea {1. „Nuvelă”; 51. „Novella”; 151. „Roman”} se citește după cum urmează:

„Din 1 arată o nuvelă, din 51 arată o romană, din 151 arată un roman.”

Această matrice poate fi ușor adaptată la diferite sarcini. Aceasta se referă, pe de o parte, la dimensiunea și numărul matricelor, precum și la denumirea lor. Deci, este posibil să ieșiți șiruri sau numere ca rezultat în loc de litere individuale. Tot ce trebuie să faceți este să ajustați literele din formulă.

VLOOKUP pe mai multe foi de lucru

O altă funcție a VLOOKUP permite utilizatorilor să conecteze conținut care se află pe diferite foi de calcul. Pentru exemplul nostru, această opțiune poate fi utilă atunci când informațiile sunt mai întâi sortate în diferite foi de lucru și apoi actualizate într-un tabel rezumat.

Imaginați-vă că, pe lângă cărțile dvs., vă înscrieți și filmele colectate într-o foaie de calcul Excel. Apoi combinați ambele colecții într-un singur tabel mare.

Avantajul acestei proceduri constă nu numai în ordinea crescută, ci și în evitarea erorilor potențiale. Dacă doriți să creați o intrare nouă sau să actualizați una existentă, nu trebuie să căutați în tabelul mare, ci să le puteți accesa pe cele mai mici. Valorile sunt apoi transferate automat în tabelul Excel rezumativ. Acest lucru face ca rescrierea în tabelul mare să fie inutilă, ceea ce în cel mai bun caz evită o mișcare nefericită și o înlănțuire ulterioară a mesajelor de eroare.

Cum arată formula?

Această funcție este posibilă din nou prin inserarea unei alte formule. În timp ce căutarea cu mai multe criterii necesită o formulă IF suplimentară, lucrul cu mai multe foi de lucru necesită o formulă INDIRECTĂ. Aceasta permite specificarea unui interval dintr-o altă foaie de calcul pentru matricea VLOOKUP.

= VLOOKUP (criteriu de căutare; INDIRECT (matrice); index coloană; interval_link)

Atenţie: Această formulă va funcționa numai dacă tabelele individuale din diferitele foi au aceleași nume ca titlurile de coloană ale tabelului general. Tabelele întregi pot fi denumite în „câmpul Nume” din partea stângă sus deasupra grilei de celule. Tabelele care au fost deja denumite pot fi vizualizate cu combinația de taste Ctrl + F3.

Gestionarea mesajelor de eroare emergente

Lucrul cu tabele Excel conectate poate duce la probleme nedorite. Aceasta include în special ieșirea de valori greșite. În cazul în care este afișată valoarea greșită 0, există o mică problemă în setările Excel, care pot fi rezolvate rapid.

Mesajul de eroare comun #NV, pe de altă parte, este o funcție deliberată a VLOOKUP, care indică utilizatorului că valoarea cerută nu este disponibilă. Această notă poate fi concepută diferit cu ajutorul unei formule.

VLOOKUP - o prezentare generală

VLOOKUP este o funcție Excel utilă care poate fi utilizată pentru căutarea și evaluarea tabelelor. Avantajele sale sunt evidente în aplicația sa ușor de utilizat și flexibilă. În acest fel, oricine lucrează în mod regulat cu tabele Excel poate beneficia de această funcție. Fie colecționarul privat care își creează propriile tabele mici, fie marea companie care procesează seturi de date semnificativ mai substanțiale.

Dacă, pe de altă parte, aveți în continuare cereri fără răspuns pe care VLOOKUP nu le-a putut satisface, puteți aștepta cu nerăbdare o opțiune Excel suplimentară: Microsoft le oferă utilizatorilor Excel 365 noul XLOOKUP de la începutul anilor 2022-2023. Acest lucru se bazează pe competențele VLOOKUP și le completează cu funcții suplimentare, uneori chiar mai simple. Prin urmare, o nouă rutină în evaluarea datelor se deschide și în acest moment.