CĂUTARE VOR Între două cărți de lucru: Ghid ușor pas cu pas

Dacă lucrați cu mai multe registre de lucru în Excel, poate fi necesar să utilizați funcția CĂUTARE VL pentru a prelua date dintr-un registru de lucru în altul.

Funcția VLOOKUP caută o anumită valoare într-un tabel dintr-un registru de lucru separat și returnează o valoare corespunzătoare din același rând. Sintaxa trebuie să includă o referință complet calificată la cel de-al doilea registru de lucru și numele foii de lucru.

Obținerea corectă a referințelor poate fi o provocare atunci când utilizați funcția pentru prima dată. Acest articol folosește exemple de foi de calcul pentru a vă arăta exact cum să introduceți formulele.

Veți învăța, de asemenea, câteva îmbunătățiri la CĂUTARE V și câteva alternative la funcție.

Să începem!

Cuprins

• Ce este VLOOKUP?
• Cum să utilizați VLOOKUP între două registre de lucru
  • Eșantion de date
  • Pasul 1: Păstrați ambele registre de lucru deschise
  • Pasul 2: Începeți să introduceți formula VLOOKUP
  • Pasul 3: Cum să faceți referire la cel de-al doilea registru de lucru
• 3 erori frecvente de VLOOKUP și cum să le remediați
  • 1. #N/A Eroare
  • 2. #Ref! Eroare numărul coloanei
  • 3. Eroare ghilimele și parantezele pătrate
• 4 sfaturi pentru gestionarea diferitelor registre de lucru
• Cum se actualizează automat datele
• Utilizări avansate ale funcției VLOOKUP
• 2 alternative principale la VLOOKUP
  • 1. POTRIVIRE INDEX
  • 2. CĂUTARE XL
• Cum să utilizați VLOOKUP pe mai multe foi
  • Pasul 1: Identificați componentele
  • Pasul 2: Configurați datele dvs
  • Pasul 3: Utilizați funcția VLOOKUP
  • (Opțional) Pasul 4: Depanați erorile #N/A
• Gânduri finale

Ce este VLOOKUP?

Să presupunem că aveți un registru de lucru cu vânzările de produse și un registru de lucru separat cu prețurile fiecărui produs. Doriți să accesați prețurile din al doilea caiet de lucru și să le utilizați în calcule din primul caiet de lucru.

VLOOKUP este o soluție pentru această sarcină. Termenul înseamnă „Vertical Lookup” . „

Este o funcție încorporată care vă permite să căutați o anumită valoare într-o coloană și să returnați o valoare corespunzătoare într-o altă coloană în funcție de locație.

Poate fi folosit cu:

• o singură foaie de calcul

• mai multe foi în același registru de lucru

• două registre de lucru Excel

Dacă nu aveți complexitatea suplimentară a mai multor registre de lucru, consultați acest tutorial despre compararea a două coloane în Excel folosind CĂUTARE V.

Cum să utilizați VLOOKUP între două registre de lucru

Funcția VLOOKUP este utilă în special atunci când trebuie să căutați date în mai multe foi de lucru sau registre de lucru.

Cei trei pași principali pentru a face acest lucru sunt:

1. Deschideți ambele registre de lucru.

2. Introduceți formula VLOOKUP în primul registru de lucru Excel.

3. Furnizați o referință complet calificată la cel de-al doilea registru de lucru.

Eșantion de date

Acest tutorial simplu funcționează prin exemple bazate pe date eșantion. Pentru a continua, creați un nou registru de lucru numit „Vânzări” și introduceți aceste date în coloanele A și B:

• Mere, 40

• Portocale, 30

• Pere, 70

• Banane, 20

Adăugați o a treia coloană pentru a păstra informațiile despre preț:

Creați un al doilea registru de lucru Excel numit „Produs” și redenumiți Sheet1 ca „Prețuri”. Introduceți aceste date în coloana A și coloana B:

• Mere, 1,50

• Portocale, 2.30

• Pere, 3,60

• Banane, 2.10

Datele arată astfel:

Pentru a calcula veniturile din vânzări din prima foaie de calcul, trebuie să căutăm prețul în a doua foaie de calcul. VLOOKUP pentru salvare!

Pasul 1: Păstrați ambele registre de lucru deschise

Ambele registre de lucru trebuie să fie deschise când se utilizează formula VLOOKUP între două registre de lucru. Dacă al doilea registru de lucru este închis, funcția va returna o eroare.

Puteți fie să le deschideți separat, fie să utilizați comanda „Deschidere” din Excel pentru a le deschide pe amândouă simultan. Există două moduri de a deschide fiecare registru de lucru:

• Accesați fila Fișier și selectați „Deschidere”.

• Folosiți comanda rapidă de la tastatură „Ctrl + O” .

După ce aveți ambele registre de lucru deschise, puteți comuta între ele făcând clic pe fila corespunzătoare din partea de jos a ferestrei Excel.

Pasul 2: Începeți să introduceți formula VLOOKUP

Iată sintaxa de bază a funcției LOOKUP:

=CĂUTAREV(valoare_căutare, matrice_tabelă, număr_index_col, [căutare_gamă])

• lookup_value : valoarea pe care doriți să o căutați în celălalt registru de lucru.

• table_array : intervalul de celule din celălalt registru de lucru care conține datele pe care doriți să le preluați.

• col_index_num : numărul coloanei din intervalul care conține datele pe care doriți să le preluați.

• range_lookup : Acest ultim parametru este opțional. Dacă îl setați la TRUE sau îl omiteți, VLOOKUP va căuta o potrivire aproximativă. Dacă îl setați la FALSE, VLOOKUP va căuta doar o potrivire exactă.

Să presupunem că doriți să căutați prețul merelor în cel de-al doilea registru de lucru al datelor noastre eșantion. Iată cum se va descompune formula:

• Lookup_value se află în celula A2 a primului registru de lucru.

• Tabelul se află în intervalul de celule A1:B5 al celui de-al doilea registru de lucru.

• Col_index_num este a doua coloană care deține prețul.

Cea mai complicată parte a formulei VLOOKUP este referirea corectă la cel de-al doilea registru de lucru.

Pasul 3: Cum să faceți referire la cel de-al doilea registru de lucru

Pentru a finaliza formula VLOOKUP pentru două registre de lucru, va trebui să utilizați o referință complet calificată care include aceste detalii din cel de-al doilea registru de lucru:

• numele registrului de lucru

• numele foii

• gama de celule

Acest lucru asigură că Excel știe exact unde să găsească datele pe care le căutați. Referința complet calificată este utilizată pentru argumentul table_array al funcției VLOOKUP.

Acestea sunt valorile pentru datele eșantionului:

• numele registrului de lucru: Product.xlsx

• denumirea foii: Preturi

• interval de celule: $A1:$B5

Pentru a căuta prețul pentru primul articol, introduceți următoarea formulă în celula C2 a primului registru de lucru:

=CĂUTAREV(A2, [Product.xlsx]Prețuri! $A$1:$B$5, 2, FALSE)

Această imagine arată funcția în acțiune în bara de formule pentru prima celulă:

Rețineți că numele registrului de lucru este cuprins între paranteze drepte, iar numele foii este urmat de un semn de exclamare.

Dacă numele fișierului Excel conține un spațiu sau caractere speciale, atunci trebuie să includeți întregul registru de lucru și foaia între ghilimele simple.

Formula folosește valori absolute pentru intervalul de celule (prefixul $). Acest lucru vă permite să copiați formula în restul rândurilor făcând clic pe mânerul de umplere.

Iată rezultatele VLOOKUP:

Si asta e! Acum știi cum să folosești VLOOKUP eficient. Pentru a vă ajuta în cazul în care întâmpinați erori, am compilat trei dintre cele mai frecvente erori VLOOKUP în secțiunea următoare.

3 erori frecvente de VLOOKUP și cum să le remediați

Acum că ați învățat cum să utilizați VLOOKUP dintr-un alt registru de lucru, este posibil să întâlniți câteva erori comune.

Iată cele trei erori cele mai frecvente și cum să le remediați.

1. #N/A Eroare

Eroarea #N/A apare atunci când valoarea de căutare nu este găsită în matricea tabelului. Pentru a remedia această eroare, puteți încerca următoarele:

• Verificați ortografia valorii de căutare.

• Asigurați-vă că valoarea de căutare este în prima coloană a matricei tabelului.

• Verificați dacă valoarea de căutare nu este formatată ca text în cele două foi.

• Asigurați-vă că argumentul range_lookup este setat la FALSE pentru a găsi o potrivire exactă.

Exemplul de mai sus nu a reușit să găsească o valoare potrivită pentru Cireșe în al doilea registru de lucru.

2. #Ref! Eroare numărul coloanei

# Ref! Eroarea se întâmplă atunci când argumentul col_index_num este mai mare decât numărul de coloane din matricea tabelului.

Pentru a remedia această eroare, verificați dacă argumentul col_index_num este mai mic sau egal cu numărul de coloane din tabelul sursă.

În imaginea de mai jos, col_index_num a fost setat la 3, dar există doar două coloane în intervalul de referință.

3. Eroare ghilimele și parantezele pătrate

Eroare de ghilimele și paranteze pătrate apare atunci când efectuați CĂUTARE V cu o valoare care conține ghilimele sau paranteze pătrate.

Pentru a remedia această eroare, puteți încerca următoarele:

• Utilizați ghilimele duble în jurul valorii de căutare.

• Utilizați funcția CHAR(34) pentru a introduce ghilimele duble.

• Utilizați funcția IFERROR pentru a returna o celulă goală dacă valoarea de căutare conține ghilimele sau paranteze pătrate.

Odată ce vă familiarizați cu aceste erori, remediați-le este simplă! Pentru a vă ajuta și mai mult să deveniți un profesionist VLOOKUP, am oferit patru sfaturi pentru gestionarea diferitelor registre de lucru în secțiunea următoare.

4 sfaturi pentru gestionarea diferitelor registre de lucru

Când lucrați cu mai multe registre de lucru, este important să urmăriți registrul de lucru care conține datele.

Iată cele mai bune patru sfaturi ale noastre pentru a vă păstra sănătatea mintală:

1. Utilizați convențiile de denumire pentru a vă ajuta să vă amintiți ce foi de calcul Excel conțin și ce date.

2. Utilizați referințe absolute în formulele dvs. pentru a vă asigura că intervalul de căutare face referire la celulele corecte, chiar dacă mutați sau copiați formula într-o altă locație sau foaie Excel.

3. Utilizați funcția IFERROR pentru a gestiona orice erori #N/A care pot apărea dacă valorile de căutare nu sunt găsite în datele sursă.

4. Luați în considerare blocarea celulelor din registrul de lucru de referință, astfel încât să nu poată fi suprascrise.

Cum se actualizează automat datele

Când lucrați cu mai multe fișiere Excel, actualizarea manuală a datelor poate fi consumatoare de timp .

Dacă doriți ca formula dvs. VLOOKUP să se actualizeze automat atunci când adăugați sau eliminați date din celelalte registre de lucru, puteți utiliza funcția INDIRECT.

Iată cum:

• În formula VLOOKUP, înlocuiți argumentul matricei tabelului cu funcția INDIRECT.

• În funcția INDIRECT, utilizați o referință la celula care conține matricea tabelului din celălalt registru de lucru.

Pentru a realiza acest lucru cu exemplele noastre de registre de lucru, urmați acești pași.

1. Utilizați o celulă din coloana D pentru a introduce referința la foaia „Prețuri” din registrul de lucru „Produs” și intervalul de celule A1:B5 astfel: [ Product.xlsx]Prețuri!A:B

2. În celula C2 a registrului de lucru „Vânzări”, utilizați această formulă: =CĂUTAREV(A2, INDIRECT($D$1), 2, FALSE)

3. Copiați formula în jos (referințele absolute asigură păstrarea celulei cu foaia de calcul).

Aceste acțiuni asigură că modificările din foaia originală sunt actualizate automat în celulele conectate.

Această imagine arată funcția INDIRECT combinată cu CĂUTARE V:

Rețineți că acest exemplu folosește intervalul „A:B” pentru a face referire la cele două coloane. Aceasta înseamnă că atunci când adăugați mai multe produse și prețuri, formula va continua să funcționeze în registrul de lucru curent.

Acum că ați primit un gust al puterii VLOOKUP, să aruncăm o privire la câteva utilizări avansate ale funcției din secțiune!

Utilizări avansate ale funcției VLOOKUP

Exemplele din acest articol returnează o singură potrivire pentru fiecare valoare de căutare. Dacă doriți să lucrați cu mai multe potriviri dintr-un registru de lucru diferit, puteți utiliza în schimb o formulă matrice.

De asemenea, puteți combina VLOOKUP cu alte funcții. De exemplu, îl puteți încorpora într-o funcție IF pentru o analiză complexă suplimentară.

Analiza complexă include tehnici precum testele chi-pătrat și testele T pentru mostre pereche. Iată un exemplu de utilizare a testului chi-pătrat în Excel:

Utilizatorii avansați profită de VLOOKUP pentru a compara seturile de date și pentru a identifica nepotrivirile. Veți găsi alte modalități în acest articol despre găsirea discrepanțelor în Excel .

Dar ce se întâmplă dacă nu doriți să utilizați VLOOKUP din orice motiv? Ai noroc! Există câteva alternative, care este ceea ce vom trata în secțiunea următoare.

2 alternative principale la VLOOKUP

Când vine vorba de căutarea valorilor în diferite foi, CĂUTARE V este adesea funcția de bază. Cu toate acestea, există câteva alternative care pot fi utile în anumite situații.

Cele mai frecvente două sunt:

• POTRIVIRE INDEX

• CĂUTARE XL

1. POTRIVIRE INDEX

INDEX și MATCH este o combinație puternică de două funcții care pot fi folosite pentru a căuta valori într-un tabel. Spre deosebire de VLOOKUP, care necesită ca valoarea de căutare să fie în coloana cea mai din stânga a tabelului, INDEX MATCH poate căuta valori în orice coloană.

Iată cum funcționează:

1. Funcția INDEX returnează o valoare dintr-un interval specificat pe baza unui număr de rând și coloană.

2. Funcția MATCH returnează poziția unei valori de căutare într-un interval specificat.

Pentru a utiliza INDEX MATCH, trebuie să specificați intervalul în care doriți să căutați, rândul în care doriți să găsiți valoarea de căutare și coloana în care doriți să returnați rezultatul.

Iată un exemplu de formulă:

=INDEX(IntervalTabel,Potrivire(ValoareCăutare,Interval Căutare,0),ColumnIndex)

Pentru a utiliza acest lucru cu exemplele de foi de calcul, aceasta este formula:

=INDEX([Product.xlsx]Prețuri!$B$1:$B$5, MATCH(A2, [Product.xlsx]Prețuri!$A$1:$A$5, 0))

Aceasta se descompune astfel:

• Funcția MATCH caută valoarea din celula A2 (numele produsului) din intervalul A1:A5 din foaia „Prețuri” din registrul de lucru „Produs”.

• Funcția INDEX returnează o valoare din intervalul B1:B5 în foaia „Prețuri” din registrul de lucru „Produs”, pe baza numărului rândului furnizat de funcția MATCH.

Această imagine arată formula în acțiune:

2. CĂUTARE XL

XLOOKUP este o funcție mai nouă care a fost introdusă în Excel 365 . Este similar cu VLOOKUP, dar cu câteva caracteristici suplimentare.

Unul dintre cele mai mari avantaje ale XLOOKUP este că poate căuta valori în stânga coloanei de căutare.

Iată cum funcționează:

1. Funcția XLOOKUP returnează o valoare dintr-un interval specificat pe baza unei valori de căutare.

2. Puteți specifica dacă doriți o potrivire exactă sau o potrivire aproximativă.

3. De asemenea, puteți specifica ce să returnați dacă nu se găsește nicio potrivire.

Pentru a utiliza XLOOKUP, trebuie să specificați valoarea de căutare, intervalul în care doriți să căutați și coloana în care doriți să returnați rezultatul.

Iată un exemplu de formulă:

=XLOOKUP(Valoare de căutare,Interval de căutare,Interval de returnare,”Negăsit”, 0)

Pentru a utiliza acest lucru cu exemplele de foi de calcul, aceasta este formula:

=XLOOKUP(A2, [Product.xlsx]Prețuri!$A$1:$A$5, [Product.xlsx]Prețuri!$B$1:$B$5)

Aceasta se descompune astfel:

• A2: Valoarea pe care doriți să o căutați (numele produsului).

• [Product.xlsx]Prețuri!$A$1:$A$5: intervalul în care doriți să căutați valoarea de căutare (numele produselor din foaia „Prețuri” din registrul de lucru „Produs”).

• [Product.xlsx]Prețuri!$B$1:$B$5: intervalul care conține valorile returnate (prețurile din foaia „Prețuri” din registrul de lucru „Produs”).

Aceasta este formula în acțiune:

Cum să utilizați VLOOKUP pe mai multe foi

Dacă trebuie să efectuați o CĂUTARE VĂ între două registre de lucru, este posibil să fie necesar să utilizați și CĂUTARE VL pe mai multe foi.

Din fericire, procesul pentru a face acest lucru este foarte asemănător cu efectuarea unei CĂUTARE V între două registre de lucru.

Sunt patru pași.

Pasul 1: Identificați componentele

În loc să includeți matricea tabelului așa cum ați face pentru o singură foaie, va trebui să includeți și numele foii.

De exemplu, dacă doriți să căutați o valoare în intervalul A2:B6 pe foaia Jan din registrul de lucru Sales_reports.xlsx, formula dvs. ar arăta astfel:

=CĂUTAREV(A2,[Sales_reports.xlsx]Ian!$A$2:$B$6,2,FALSE)

Pasul 2: Configurați datele dvs

Asigurați-vă că datele dvs. sunt organizate astfel încât să fie ușor de referit pe mai multe foi.

Poate doriți să luați în considerare crearea de file diferite pentru date de referință specifice.

Pasul 3: Utilizați funcția VLOOKUP

Odată ce datele dvs. sunt configurate, puteți utiliza funcția CĂUTARE V pentru a căuta și a prelua date din alte foi.

Doar asigurați-vă că includeți numele foii în formula dvs., așa cum se arată mai sus.

(Opțional) Pasul 4: Depanați erorile #N/A

Dacă întâmpinați erori #N/A când utilizați CĂUTARE V pe mai multe foi, verificați de două ori formula pentru a vă asigura că ați referit corect toate foile și intervalele necesare.

De asemenea, poate doriți să verificați dacă efectuați o potrivire exactă, mai degrabă decât o căutare în interval.

Gânduri finale

Ați învățat cum să utilizați VLOOKUP pentru a căuta și a prelua date pe două foi de calcul. Această funcție versatilă face ușoară căutarea valorilor într-un registru de lucru pe baza valorilor corespunzătoare din altul.

Puterea funcției VLOOKUP se extinde cu mult dincolo de foile de lucru individuale, ajungând în diferite registre de lucru pentru a lega și analiza datele.

Stăpânirea acestei funcții este un pas semnificativ spre a deveni competent în Excel, deoarece deschide noi posibilități de gestionare și interpretare a datelor.

Încorporând funcții precum INDIRECT, INDEX-MATCH sau XLOOKUP, puteți îmbunătăți și mai mult flexibilitatea și eficacitatea căutărilor dvs. de date.

Continuați să exersați și să explorați vastele capabilități ale Excel; acesta este doar vârful aisbergului. Vânătoare de date fericită!