Cum se compară două coloane în Excel folosind VLOOKUP

Când lucrați cu seturi de date mari în Excel, poate fi necesar să comparați două coloane pentru a găsi asemănări sau diferențe.

VLOOKUP este o funcție puternică care vă permite să căutați date potrivite între două coloane. Funcția vă permite să căutați valori într-o coloană care apar în alta.

Acest articol vă arată cum să comparați două coloane folosind VLOOKUP, astfel încât să vă puteți analiza eficient datele.

Cuprins

• Elementele fundamentale ale funcției Vlookup
  • Funcția Vlookup
• Pregătirea datelor dvs. pentru VLOOKUP
  • Formatarea datelor
• Cum să utilizați VLOOKUP pentru a compara două coloane
  • Gestionarea altor erori
• Alternative la VLOOKUP pentru compararea coloanelor
  • 1. Utilizarea funcțiilor de indexare și potrivire
  • 2. Utilizarea XLOOKUP în Excel
• Cinci sfaturi pentru a îmbunătăți performanța VLOOKUP
  • 1. Limitați intervalul de căutare
  • 2. Folosiți referințe absolute
  • 3. Sortați-vă datele
  • 4. Folosiți o dublă CĂUTARE V
  • 5. Folosiți formatarea condiționată
• Utilizări avansate pentru VLOOKUP
• Cuvântul nostru final

Elementele fundamentale ale funcției Vlookup

Să presupunem că aveți o foaie de calcul cu două liste de articole în coloana A și coloana B. Doriți să găsiți elementele din Lista 1 care apar și în Lista 2.

Vă puteți imagina că lucrul manual prin liste de mii de articole ar consuma enorm de mult timp. Din fericire, Excel aduce VLOOKUP în ajutor!

Termenul VLOOKUP înseamnă Căutare verticală. Funcția va compara două coloane, va găsi potriviri între ele și va returna valorile asociate.

Funcția Vlookup

Iată o sintaxă de bază a funcției LOOKUP:

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

Aceasta este o defalcare a elementelor:

• lookup_value : valoarea pe care doriți să o căutați în prima coloană a table_array.

• table_array : intervalul de celule care conțin datele în care doriți să căutați.

• col_index_num : numărul coloanei din table_array din care doriți să returnați valoarea.

• range_lookup : Opțional. Este fie TRUE (potrivire aproximativă) fie FALSE (potrivire exactă). Valoarea implicită este TRUE.

Pregătirea datelor dvs. pentru VLOOKUP

Înainte de a utiliza VLOOKUP pentru a compara două coloane în Excel , trebuie să vă pregătiți datele.

Creați două coloane separate în foaia de lucru în care doriți să comparați valorile. Acest articol folosește coloana A și coloana B pentru exemplele noastre.

Iată exemplele de date utilizate în acest articol:

Acesta este exemplul de tabel cu antetul care urmează să fie adăugat la secțiunea intitulată „Pregătirea datelor pentru CĂUTARE V”.

Lista 1 Lista 2
Articol 1 Articol 3
Articol 2
Articol 4 Articol 3 Articol 1
Articol 4 Articol 5

Formatarea datelor

Este important să vă asigurați că datele din ambele coloane sunt formatate în mod similar. CĂUTARE V este sensibilă la majuscule, ceea ce înseamnă că majusculele și literele mici contează.

Pot apărea erori de potrivire în rezultatul final dacă formatarea este inconsecventă.

De asemenea, este o idee bună să eliminați orice valori duplicate sau celule goale pentru a minimiza riscul de erori.

Pentru a elimina valorile duplicate:

1. Selectați coloana.

2. Accesați fila Date din panglica de sus.

3. Faceți clic pe Eliminare duplicate în secțiunea Instrumente de date.

Butonul poate fi puțin greu de văzut. Această imagine vă va ajuta:

Pentru a elimina celulele goale:

1. Selectați coloana.

2. Accesați fila Acasă din panglica de sus.

3. Extindeți meniul „Găsiți și selectați”.

4. Alegeți „Mergeți la Special”.

5. Selectați „Blancs” din opțiuni.

6. Faceți clic pe „Ștergeți” în secțiunea Celule.

Cum să utilizați VLOOKUP pentru a compara două coloane

Odată ce datele sunt pregătite, puteți scrie formula VLOOKUP pentru a compara două coloane în Excel și pentru a identifica potriviri. Urmați acești pași:

1. Selectați o celulă într-o coloană nouă în care doriți să afișați rezultatele comparației (de exemplu, celula C2).

2. Introdu următoarea formulă: =CĂUTAREV(A2, B:B, 1, FALSE))

3. Apăsați Enter pentru a aplica formula.

4. În cazul unei valori care se potrivesc, valoarea va fi afișată pe același rând al coloanei rezultat (de exemplu, coloana C).

5. Trageți formula în jos din C2 pentru a o copia peste câte celule aveți nevoie.

Dacă utilizați datele noastre eșantion, puneți rezultatele comparației în a treia coloană. Formula va găsi trei potriviri din cele patru elemente din Lista 1.

Observați că articolul 2 este afișat ca #N/A. Excel ne spune că potrivirea nu este aplicabilă, adică nu a putut fi găsită.

Este corect și reprezintă informații utile despre valorile lipsă. Cu toate acestea, unii utilizatori Excel ar putea crede că există o problemă de date sau o eroare de funcție.

Este o practică bună să afișați o altă indicație că nu a fost găsită nicio potrivire. Ar putea fi pur și simplu un gol.

Pentru a face acest lucru, combinați funcția VLOOKUP cu funcția IFNA astfel:

=IFNA(CĂUTAREV(A1, B:B, 1, FALSE), „”)

Funcția IFNA detectează eroarea #N/A și înlocuiește ieșirea cu un spațiu gol (“”). De asemenea, puteți utiliza funcția ISNA sau aveți o logică suplimentară cu funcția IF.

Gestionarea altor erori

VLOOKUP poate genera uneori și alte erori în rezultatele comparației. #REF ! Eroare în Excel este o problemă reală cu datele dvs.

De obicei, apare atunci când intervalul specificat este incorect. Exemplul nostru a făcut referire la întreaga coloană, dar puteți utiliza și intervale de celule verticale.

Asigurați-vă că intervalul de căutare la care vă referiți acoperă toate valorile pe care doriți să le comparați.

Alternative la VLOOKUP pentru compararea coloanelor

Există două funcții alternative principale de căutare atunci când comparăm două coloane în Excel pentru a găsi potriviri.

1. Utilizarea funcțiilor de indexare și potrivire

În loc să utilizați CĂUTARE V, puteți compara două coloane în Excel combinând funcțiile INDEX și MATCH.

Această metodă oferă o modalitate mai flexibilă de a căuta date și este deosebit de utilă atunci când lucrați cu coloane neadiacente sau când indexul coloanei se poate modifica.

Aceasta este sintaxa de pus în coloana rezultat:

=INDEX(interval_return, MATCH(valoare_căutare, interval_căutare, 0))

• Return_range : intervalul de celule care conțin datele pe care doriți să le returnați.

• Lookup_value : valoarea pe care doriți să o căutați în intervalul de căutare.

• Lookup_range : intervalul de celule în care doriți să găsiți valoarea de căutare.

Folosind aceleași date din exemplele anterioare, înlocuim formula VLOOKUP după cum urmează:

=(INDEX(B:B, MATCH(A2; B:B, 0)))

Funcția MATCH returnează poziția relativă a valorii de căutare în intervalul de căutare, iar funcția INDEX returnează valoarea corespunzătoare din intervalul de returnare.

Rezultatele pe aceleași date vor fi aceleași cu VLOOKUP pe care am folosit-o mai devreme. Această imagine arată funcțiile utilizate:

De asemenea, puteți înlocui eroarea #N/A cu un mesaj personalizat. Iată un exemplu de utilizare a funcției IFERROR.

=DACA EROARE(INDEX(B:B, POTRIVIRE(A2, B:B, 0)), „Negăsit”)

2. Utilizarea XLOOKUP în Excel

Pentru utilizatorii cu Excel 365 sau Excel 2019, XLOOKUP este un test logic alternativ la VLOOKUP pentru găsirea valorilor comune în două coloane.

XLOOKUP oferă mai multe avantaje. Îl puteți folosi pentru a căuta date atât pe orizontală, cât și pe verticală, pentru a lucra cu coloane neadiacente și pentru a specifica valori personalizate pentru erori.

Sintaxa XLOOKUP este:

=XLOOKUP(valoare_căutare, interval_căutare, interval_return, [dacă_nu_găsit], [mod_potrivire], [mod_căutare])

• lookup_value : valoarea pe care doriți să o căutați în intervalul de căutare.

• lookup_range : intervalul de celule în care doriți să găsiți valoarea de căutare.

• return_range : intervalul de celule care conțin datele pe care doriți să le returnați.

• Adăugați valori de eroare personalizate, modul de potrivire și parametrii modului de căutare, după cum este necesar.

XLOOKUP va găsi valoarea de căutare în intervalul de căutare și va returna valoarea corespunzătoare din intervalul de returnare.

Formula este deosebit de utilă atunci când aveți două liste încorporate în seturi de date de mai multe coloane. În exemplele noastre anterioare, listele au fost în prima coloană și a doua coloană, dar foaia dvs. poate avea mai multe date decât atât.

Iată un exemplu cu două liste în prima și a treia coloană a unei foi de calcul:

Formula XLOOKUP arată astfel:

=CĂUTARE XL(A2,C:C,C:C,””)

Această imagine arată rezultatul cu comparația din coloana D. Prima valoare este prezentă, dar lipsește a doua.

Rețineți că nu am formule de eroare suplimentare, dar valoarea lipsă este afișată ca un gol. Asta pentru că folosesc al patrulea parametru, care este o valoare de eroare personalizată. În acest caz, este un șir gol.

Ca bonus, vă voi arăta cum să utilizați XLOOKUP pentru a compara pe rânduri. Dacă aveți două liste care sunt în rândul 1 și pe rândul 2, cea mai simplă utilizare a formulei arată astfel:

=CĂUTARE XL(B1, 2:2, 2:2, „”)

Această imagine arată rezultatele pe cele două rânduri.

Cu funcționalitatea și flexibilitatea sa îmbunătățite, XLOOKUP este o alternativă excelentă la VLOOKUP pentru compararea listelor în Excel pentru a determina dacă sunt prezente potriviri.

Cinci sfaturi pentru a îmbunătăți performanța VLOOKUP

Optimizarea funcției VLOOKUP vă poate ajuta să evitați timpii lungi de așteptare și să îmbunătățiți capacitatea de răspuns generală a foii de lucru Excel.

Iată șase sfaturi și trucuri pe care le puteți aplica pentru a îmbunătăți performanța VLOOKUP în Excel.

1. Limitați intervalul de căutare

Am folosit coloane întregi în exemple pentru a le simplifica. Dacă lucrați cu o cantitate mare de date, ar trebui să evitați acest lucru. Utilizarea coloanelor complete poate încetini procesul de calcul al Excel.

În schimb, încercați să utilizați intervalul exact necesar pentru datele dvs. (de exemplu, A1:A100). Acest lucru reduce numărul de celule pe care funcția ta VLOOKUP trebuie să le evalueze.

2. Folosiți referințe absolute

Când specificați un interval de date (de exemplu, celulele de la B2 la B5), utilizați referințe absolute. Acest lucru vă asigură că formula este consecventă și că există date atunci când o copiați în mai multe celule.

Iată un exemplu:

=CĂUTAREV(A2, $B$2:$B$5, 1, FALSE)

3. Sortați-vă datele

Dacă știți că datele din coloana de căutare sunt sortate în ordine crescătoare, puteți utiliza VLOOKUP cu un TRUE sau 1 pentru argumentul „range_lookup”.

Acest lucru va determina Excel să efectueze o potrivire aproximativă, care este mai rapidă decât o potrivire exactă în toate celulele. Cu toate acestea, fiți precaut când utilizați această opțiune, deoarece o sortare incorectă poate duce la rezultate incorecte.

4. Folosiți o dublă CĂUTARE V

Puteți utiliza două funcții VLOOKUP pentru a accelera procesul de căutare în Excel.

Prima VLOOKUP va determina dacă valoarea de căutare există setând „col_index_num” la 1 și „range_lookup” la TRUE.

Dacă returnează TRUE, o a doua VLOOKUP preia valoarea dorită cu „range_lookup” setat la TRUE.

5. Folosiți formatarea condiționată

Puteți utiliza formatarea condiționată în foaia de lucru Excel pentru a evidenția valorile care se potrivesc sau lipsesc în coloana specificată. De asemenea, puteți aplica culori la valori unice. Astfel, datele dvs. sunt mai ușor de citit.

Veți găsi meniul de formatare condiționată în grupul de stiluri din fila Acasă.

Utilizări avansate pentru VLOOKUP

Testele T pentru probe perechi sunt utilizate pentru a compara mediile a două eșantioane înrudite. Acest videoclip arată utilizarea lor în Excel.

Dacă aveți date sau variabile suplimentare pe care trebuie să le faceți referire în timpul analizei, puteți utiliza VLOOKUP pentru a prelua valorile necesare dintr-un alt tabel sau foaie de lucru.

De exemplu, puteți utiliza VLOOKUP pentru a prelua informații demografice sau condiții de tratament pentru fiecare observație pereche din setul dvs. de date.

Cuvântul nostru final

Până acum, aveți o înțelegere solidă despre utilizarea VLOOKUP. Această funcție puternică vă permite să identificați rapid diferențele și potrivirea valorilor între două liste, făcând analiza datelor mai eficientă și mai precisă.

Ați văzut, de asemenea, exemple de alte funcții de căutare și referință, cum ar fi XLOOKUP, INDEX și MATCH. Adăugarea acestor elemente la abilitățile dumneavoastră vă va consolida și mai mult capacitățile de analiză a datelor.

Continuați să exersați și să vă perfecționați abilitățile VLOOKUP și veți deveni în curând un expert în compararea coloanelor în Excel, economisind timp și sporind productivitatea.