Cum se compară două coloane în Excel ( folosind VLOOKUP & IF)

când lucrați cu date în Excel, mai devreme sau mai târziu va trebui să comparați datele. Aceasta ar putea compara două coloane sau chiar date în foi/registre de lucru diferite.

în acest tutorial Excel, vă voi arăta diferite metode pentru a compara două coloane în Excel și a căuta potriviri sau diferențe.

există mai multe moduri de a face acest lucru în Excel și în acest tutorial vă voi arăta unele dintre acestea (cum ar fi compararea folosind formula VLOOKUP sau dacă formula sau formatarea condiționată).

deci, să începem!

comparați două coloane (una lângă alta)

acesta este cel mai de bază tip de comparație în care trebuie să comparați o celulă dintr-o coloană cu celula din același rând dintr-o altă coloană.

să presupunem că aveți un set de date așa cum se arată mai jos și pur și simplu doriți să verificați dacă valoarea din coloana A dintr-o anumită celulă este aceeași (sau diferită) în comparație cu valoarea din celula adiacentă.

desigur, puteți face acest lucru atunci când aveți un set de date mic când aveți unul mare, puteți utiliza o formulă simplă de comparație pentru a face acest lucru. Și amintiți-vă, există întotdeauna o șansă de eroare umană atunci când faceți acest lucru manual.

deci, permiteți-mi să vă arăt câteva modalități ușoare de a face acest lucru.

comparați cot la cot folosind operatorul egal cu semnul

să presupunem că aveți setul de date de mai jos și doriți să știți ce rânduri au datele potrivite și ce rânduri au date diferite.

mai jos este o formulă simplă pentru a compara două coloane (una lângă alta):

=A2=B2

formula de mai sus vă va oferi un adevărat dacă ambele valori sunt aceleași și FALSE în cazul în care nu sunt.

acum, dacă trebuie să cunoașteți toate valorile care se potrivesc, pur și simplu aplicați un filtru și afișați doar toate valorile adevărate. Și dacă doriți să cunoașteți toate valorile diferite, filtrați toate valorile FALSE (așa cum se arată mai jos):

comparați una lângă alta folosind funcția IF

o altă metodă pe care o puteți utiliza pentru a compara două coloane poate fi utilizând funcția IF.

acest lucru este similar cu metoda de mai sus, unde am folosit operatorul egal cu ( = ), cu un avantaj suplimentar. Când utilizați funcția IF, puteți alege valoarea pe care doriți să o obțineți atunci când există potriviri sau diferențe.

de exemplu, dacă există o potrivire, puteți obține textul „potrivire” sau puteți obține o valoare precum 1. În mod similar, atunci când există o nepotrivire, puteți programa formula pentru a vă oferi textul „nepotrivire” sau pentru a vă oferi o celulă 0 sau goală.

mai jos este formula IF care returnează ‘Match’ atunci când cele două celule au valoarea celulei și ‘Not A Match’ atunci când valoarea este diferită.

=IF(A2=B2,"Match","Not a Match")

formula de mai sus folosește aceeași condiție pentru a verifica dacă cele două celule (în același rând) au date potrivite sau nu (a2=B2). Dar, deoarece folosim funcția IF, îi putem cere să returneze un text specific în cazul în care condiția este adevărată sau falsă.

după ce formula rezultă într-o coloană separată, puteți filtra rapid datele și puteți obține rânduri care au datele potrivite sau rânduri cu date nepotrivite.

evidențiați rândurile cu date potrivite (sau date diferite)

o altă modalitate excelentă de a verifica rapid rândurile care au date potrivite (sau au date diferite), este să evidențiați aceste rânduri folosind formatarea condiționată.

puteți face ambele – evidențiați rândurile care au aceeași valoare într-un rând, precum și cazul în care valoarea este diferită.

să presupunem că aveți un set de date așa cum se arată mai jos și doriți să evidențiați toate rândurile în care numele este același.

mai jos sunt pașii pentru a utiliza formatarea condiționată pentru a evidenția rândurile cu date potrivite:

1. selectați întregul set de date (cu excepția anteturilor)
2. Faceți clic pe fila Pornire
3. în grupul Stiluri, faceți clic pe Formatare condiționată
4. în opțiunile care apar, Faceți clic pe ‘Regulă nouă’
5. în caseta de dialog „noua regulă de Formatare”, faceți clic pe opțiunea – „Utilizați o formulă pentru a determina ce celule să formatați’
6. în câmpul’ formatați valorile în care această formulă este adevărată’, introduceți formula: =$A2=$B2
7. Faceți clic pe butonul Format
8. Faceți clic pe fila ‘completați’ și selectați culoarea în care doriți să evidențiați rândurile cu aceeași valoare în ambele coloane
9. Faceți clic pe OK

pașii de mai sus ar evidenția instantaneu rândurile în care numele este la fel în ambele coloane a și B (în același rând). Și în cazul în care numele este diferit, aceste rânduri nu vor fi evidențiate.

în cazul în care doriți să comparați două coloane și să evidențiați rândurile în care numele sunt diferite, utilizați formula de mai jos în caseta de dialog Formatare condiționată (la Pasul 6).

=$A2<>$B2

cum funcționează acest lucru?

când folosim formatarea condiționată cu o formulă, aceasta evidențiază doar acele celule în care formula este adevărată.

când folosim $A2=$B2, va verifica fiecare celulă (în ambele coloane) și va vedea dacă valoarea dintr-un rând din coloana A este egală cu cea din coloana B sau nu.

în cazul în care este o potrivire exactă, o va evidenția în culoarea specificată, iar în cazul în care nu se potrivește, nu o va face.

cea mai bună parte a formatării condiționale este că nu necesită utilizarea unei formule într-o coloană separată. De asemenea, atunci când aplicați regula pe un set de date, aceasta rămâne dinamică. Aceasta înseamnă că, dacă schimbați orice nume din setul de date, formatarea condiționată se va ajusta în consecință.

comparați două coloane folosind VLOOKUP (găsiți potriviri/date diferite)

în exemplele de mai sus, v-am arătat cum să comparați două coloane (sau liste) atunci când comparăm doar celulele laterale.

în realitate, acest lucru este rareori va fi cazul.

în majoritatea cazurilor, veți avea două coloane cu date și va trebui să aflați dacă un punct de date dintr-o coloană există sau nu în cealaltă coloană.

în astfel de cazuri, nu puteți utiliza un simplu semn egal sau chiar o funcție IF.

ai nevoie de ceva mai puternic…

… ceva care este chiar pe aleea lui VLOOKUP!

permiteți-mi să vă arăt două exemple în care comparăm două coloane în Excel folosind funcția VLOOKUP pentru a găsi potriviri și diferențe.

comparați două coloane folosind VLOOKUP și găsiți potriviri

să presupunem că avem un set de date așa cum se arată mai jos, unde avem câteva nume în coloanele A și B.

dacă trebuie să aflați care sunt numele din coloana B care sunt și în coloana A, puteți utiliza formula VLOOKUP de mai jos:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

formula de mai sus compară cele două coloane (A și B) și vă oferă numele în cazul în care numele este și în coloana B A și returnează „no Match” în cazul în care numele este în coloana B și nu în coloana A.

în mod implicit, funcția VLOOKUP va returna o eroare #N/A în cazul în care nu găsește o potrivire exactă. Deci, pentru a evita obținerea erorii, am înfășurat funcția VLOOKUP în funcția IFERROR, astfel încât să ofere „nicio potrivire” atunci când numele nu este disponibil în coloana A.

puteți face și invers comparație – pentru a verifica dacă numele este în coloana A, precum și în coloana B. formula de mai jos ar face acest lucru:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

comparați două coloane folosind VLOOKUP și găsiți diferențe (puncte de date lipsă)

în timp ce în exemplul de mai sus, am verificat dacă datele dintr-o coloană erau acolo într-o altă coloană sau nu.

de asemenea, puteți utiliza același concept pentru a compara două coloane folosind funcția VLOOKUP și pentru a găsi date lipsă.

să presupunem că avem un set de date așa cum se arată mai jos, unde avem câteva nume în coloanele A și B.

dacă trebuie să aflați care sunt numele din coloana B care nu există în coloana A, puteți utiliza formula VLOOKUP de mai jos:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

formula de mai sus verifică numele din coloana B împotriva tuturor numelor din coloana A. În cazul în care găsește o potrivire exactă, ar returna acel nume și, în cazul în care nu găsește și se potrivește exact, va returna eroarea #N/A.

deoarece sunt interesat să găsesc numele lipsă care există coloana B și nu în coloana A, trebuie să știu Numele care returnează eroarea #N/A.

acesta este motivul pentru care am înfășurat funcția VLOOKUP în funcțiile IF și ISERROR. Această formulă întreagă Dă Valoarea – ” nu este disponibil „atunci când numele lipsește în coloana a și” disponibil ” atunci când este prezent.

pentru a cunoaște toate Numele care lipsesc, puteți filtra coloana de rezultate pe baza valorii „nu este disponibil”.

de asemenea, puteți utiliza funcția de potrivire de mai jos pentru a obține același rezultat:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

interogări comune atunci când se compară două coloane

mai jos sunt câteva interogări comune pe care le primesc de obicei atunci când oamenii încearcă să compare date în două coloane în Excel.

Q1. Cum se compară mai multe coloane în Excel în același rând pentru meciuri? Numărați și duplicatele totale.

Ans. Am dat procedura de a compara două coloane în excel pentru același rând de mai sus. Dar dacă doriți să comparați mai multe coloane în excel pentru același rând, consultați exemplul

=IF(AND(A2=B2, A2=C2),"Full Match", "")

aici am comparat datele din coloana a, coloana B și coloana C. După aceasta, am aplicat formula de mai sus în coloana D și am obținut rezultatul.

acum, pentru a număra duplicatele, trebuie să utilizați funcția Countif.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

Q2. Ce operator folosiți pentru meciuri și diferențe?

Ans. Mai jos sunt operatorii de utilizat:

• pentru a găsi potriviri, utilizați semnul egal cu semnul (=)
• pentru a găsi diferențe (nepotriviri), utilizați semnul egal cu semnul (<>)

Q3. Cum să comparați două tabele diferite și să trageți date potrivite?

Ans. Pentru aceasta, puteți utiliza funcția VLOOKUP sau funcția index & MATCH. Pentru a înțelege acest lucru într-un mod mai bun, vom lua un exemplu.

aici vom lua două tabele și acum doresc să facă trage de date de potrivire. În primul tabel, aveți un set de date și în al doilea tabel, să ia lista de fructe și apoi utilizați trage de potrivire a datelor într-o altă coloană. Pentru potrivirea tragerii, utilizați formula

=INDEX($B:$B,MATCH($D2,$A:$A,0))

Q4. Cum se elimină duplicatele în Excel?

Ans. Pentru a elimina datele duplicate, trebuie să găsiți mai întâi valorile duplicate.

pentru a găsi duplicatul, puteți utiliza diverse metode, cum ar fi formatarea condiționată, Vlookup, dacă declarație, și multe altele. Excel are, de asemenea, un instrument încorporat în care puteți selecta doar datele și puteți elimina duplicatele dintr-o coloană sau chiar mai multe coloane

Q5. Pot vedea că există o valoare de potrivire în ambele coloane. Cu toate acestea, formulele pe care le-ați împărtășit mai sus nu le consideră ca fiind potriviri exacte. De ce?

Ans: Excel consideră ceva o potrivire exactă atunci când fiecare caracter al unei celule este egal cu celălalt. Există șanse mari ca în setul dvs. de date să existe spații de conducere sau de urmărire.

deși aceste spații pot face ca valorile să pară egale cu ochiul liber, pentru Excel acestea sunt diferite. Dacă aveți un astfel de set de date, cel mai bine este să scăpați de aceste spații (puteți utiliza funcții Excel, cum ar fi TRIM pentru aceasta).

Q7. Cum se compară două coloane care dau rezultatul ca fiind adevărat atunci când toate valorile întregi ale primelor coloane nu sunt mai mici decât valorile întregi ale celei de-a doua coloane. Pentru a rezolva această problemă, nu am nevoie de formatare condiționată, funcția Vlookup, dacă Declarație, precum și orice alte formule. Am nevoie de formula pentru a rezolva această problemă.

Ans. Puteți utiliza formula matrice pentru rezolvarea acestei probleme.

sintaxa este {=și(H6:H12 >I6:I12)}. Acest lucru vă va oferi „adevărat” ca rezultat ori de câte ori valoarea coloanei H este mai mare decât valoarea din coloana i altfel „fals” va fi rezultatul.

s-ar putea să vă placă și următoarele tutoriale Excel:

• comparați două coloane în Excel (pentru potriviri și diferențe)
• cum să ascundeți coloanele pe baza valorii celulei în Excel
• cum să împărțiți o coloană în mai multe coloane în Excel
• cum să selectați coloane alternative în Excel (sau fiecare A N-A coloană)
• cum să lipiți într-o coloană filtrată sărind celulele ascunse
• cele mai bune cărți Excel (care vă vor face un Excel Pro în 2020)

Raportați acest anunț