Vizionați video - Comparați două coloane în Excel pentru potriviri și diferențe
Singura întrebare pe care o primesc foarte mult este „cum să compar două coloane în Excel?”.
Acest lucru se poate face în mai multe moduri diferite, iar metoda de utilizat va depinde de structura datelor și de ceea ce dorește utilizatorul de la aceasta.
De exemplu, vă recomandăm să comparați două coloane și să găsiți sau să evidențiați toate punctele de date potrivite (care se află în ambele coloane) sau doar diferențele (unde un punct de date se află într-o coloană și nu în cealaltă) etc.
Deoarece am fost întrebat despre acest lucru atât de mult, am decis să scriu acest tutorial masiv cu intenția de a acoperi majoritatea (dacă nu toate) scenariile posibile.
Dacă vi se pare util acest lucru, transmiteți-l și altor utilizatori Excel.
Rețineți că tehnicile de comparare a coloanelor prezentate în acest tutorial nu sunt singurele.
Pe baza setului de date, poate fi necesar să modificați sau să ajustați metoda. Cu toate acestea, principiile de bază ar rămâne aceleași.
Dacă credeți că există ceva care poate fi adăugat la acest tutorial, anunțați-mă în secțiunea de comentarii
Comparați două coloane pentru potrivirea exactă a rândurilor
Aceasta este cea mai simplă formă de comparație. În acest caz, trebuie să faceți o comparație rând cu rând și să identificați care rânduri au aceleași date și care nu.
Exemplu: Comparați celulele în același rând
Mai jos este un set de date în care trebuie să verific dacă numele din coloana A este același în coloana B sau nu.
Dacă există o potrivire, am nevoie de rezultat ca „ADEVĂRAT”, iar dacă nu se potrivește, atunci am nevoie de rezultat ca „FALS”.
Formula de mai jos ar face acest lucru:
= A2 = B2
Exemplu: Comparați celulele în același rând (folosind formula IF)
Dacă doriți să obțineți un rezultat mai descriptiv, puteți utiliza o formulă IF simplă pentru a returna „Match” atunci când numele sunt aceleași și „Mismatch” atunci când numele sunt diferite.
= IF (A2 = B2, „Potrivire”, „Nepotrivire”)
Notă: În cazul în care doriți să faceți compararea între majuscule și minuscule, utilizați următoarea formulă IF:
= IF (EXACT (A2, B2), „Potrivire”, „Nepotrivire”)
Cu formula de mai sus, „IBM” și „ibm” ar fi considerate două nume diferite, iar formula de mai sus ar returna „Mismatch”.
Exemplu: Evidențiați rândurile cu date de potrivire
Dacă doriți să evidențiați rândurile care au date potrivite (în loc să obțineți rezultatul într-o coloană separată), puteți face acest lucru folosind Formatarea condiționată.
Iată pașii pentru a face acest lucru:
- Selectați întregul set de date.
- Faceți clic pe fila „Acasă”.
- În grupul Stiluri, faceți clic pe opțiunea „Formatare condiționată”.
- Din meniul derulant, faceți clic pe „Regulă nouă”.
- În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
- În câmpul pentru formulă, introduceți formula: = $ A1 = $ B1
- Faceți clic pe butonul Format și specificați formatul pe care doriți să îl aplicați celulelor potrivite.
- Faceți clic pe OK.
Aceasta va evidenția toate celulele în care numele sunt aceleași în fiecare rând.
Comparați două coloane și evidențiați potrivirile
Dacă doriți să comparați două coloane și să evidențiați datele potrivite, puteți utiliza funcționalitatea duplicată în formatarea condiționată.
Rețineți că acest lucru este diferit de ceea ce am văzut când am comparat fiecare rând. În acest caz, nu vom face o comparație rând cu rând.
Exemplu: comparați două coloane și evidențiați datele de potrivire
Adesea, veți primi seturi de date în care există potriviri, dar este posibil ca acestea să nu fie în același rând.
Ceva așa cum se arată mai jos:
Rețineți că lista din coloana A este mai mare decât cea din B. De asemenea, unele nume se găsesc în ambele liste, dar nu în același rând (cum ar fi IBM, Adobe, Walmart).
Dacă doriți să evidențiați toate numele companiilor potrivite, puteți face acest lucru folosind formatarea condiționată.
Iată pașii pentru a face acest lucru:
- Selectați întregul set de date.
- Faceți clic pe fila Acasă.
- În grupul Stiluri, faceți clic pe opțiunea „Formatare condiționată”.
- Plasați cursorul pe opțiunea Evidențiați regulile celulei.
- Faceți clic pe Duplicate Values.
- În caseta de dialog Duplicare valori, asigurați-vă că este selectat „Duplicare”.
- Specificați formatarea.
- Faceți clic pe OK.
Pașii de mai sus vă vor oferi rezultatul așa cum se arată mai jos.
Notă: regula de copiere a formatării condiționate nu este sensibilă la majuscule și minuscule. Deci, „Apple” și „măr” sunt considerate la fel și ar fi evidențiate ca duplicate.
Exemplu: comparați două coloane și evidențiați datele nepotrivite
În cazul în care doriți să evidențiați numele care sunt prezente într-o listă și nu în cealaltă, puteți utiliza formatarea condițională și pentru aceasta.
- Selectați întregul set de date.
- Faceți clic pe fila Acasă.
- În grupul Stiluri, faceți clic pe opțiunea „Formatare condiționată”.
- Plasați cursorul pe opțiunea Evidențiați regulile celulei.
- Faceți clic pe Duplicare valori.
- În caseta de dialog Duplicare valori, asigurați-vă că este selectat „Unic”.
- Specificați formatarea.
- Faceți clic pe OK.
Acest lucru vă va oferi rezultatul așa cum se arată mai jos. Se evidențiază toate celulele care au un nume care nu este prezent pe cealaltă listă.
Comparați două coloane și găsiți puncte de date lipsă
Dacă doriți să identificați dacă un punct de date dintr-o listă este prezent în cealaltă listă, trebuie să utilizați formulele de căutare.
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să identificați companiile care sunt prezente în coloana A, dar nu în coloana B,
Pentru a face acest lucru, pot folosi următoarea formulă VLOOKUP.
= ISERROR (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Această formulă utilizează funcția VLOOKUP pentru a verifica dacă un nume de companie din A este prezent sau nu în coloana B. Dacă este prezent, acesta va returna acel nume din coloana B, altfel va returna o eroare # N / A.
Aceste nume care returnează eroarea # N / A sunt cele care lipsesc în coloana B.
Funcția ISERROR ar reveni ADEVĂRAT dacă există rezultatul VLOOKUP este o eroare și FALS dacă nu este o eroare.
Dacă doriți să obțineți o listă cu toate numele în care nu există nicio potrivire, puteți filtra coloana de rezultate pentru a obține toate celulele cu TRUE.
De asemenea, puteți utiliza funcția MATCH pentru a face același lucru;
= NU (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Notă: Personal, prefer să folosesc funcția Match (sau combinația INDEX / MATCH) în loc de VLOOKUP. Mi se pare mai flexibil și mai puternic. Aici puteți citi diferența dintre Vlookup și Index / Match.
Comparați două coloane și extrageți datele potrivite
Dacă aveți două seturi de date și doriți să comparați elementele dintr-o listă cu cealaltă și să preluați punctul de date potrivite, trebuie să utilizați formulele de căutare.
Exemplu: Trageți datele potrivite (Exact)
De exemplu, în lista de mai jos, doresc să aduc valoarea de evaluare de piață pentru coloana 2. Pentru a face acest lucru, trebuie să caut această valoare în coloana 1 și apoi să aduc valoarea de evaluare de piață corespunzătoare.
Mai jos este formula care va face acest lucru:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
sau
= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Exemplu: Trageți datele de potrivire (parțiale)
În cazul în care obțineți un set de date în care există o diferență minoră în numele din cele două coloane, utilizarea formulelor de căutare prezentate mai sus nu va funcționa.
Aceste formule de căutare au nevoie de o potrivire exactă pentru a da rezultatul corect. Există o opțiune de potrivire aproximativă în funcția VLOOKUP sau MATCH, dar aceasta nu poate fi utilizată aici.
Să presupunem că aveți setul de date așa cum se arată mai jos. Rețineți că există nume care nu sunt complete în coloana 2 (cum ar fi JPMorgan în loc de JPMorgan Chase și Exxon în loc de ExxonMobil).
Într-un astfel de caz, puteți utiliza o căutare parțială utilizând caractere wildcard.
Următoarea formulă va da rezultatul corect în acest caz:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
sau
= INDEX ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
În exemplul de mai sus, asteriscul (*) este un caracter wildcard care poate reprezenta orice număr de caractere. Când valoarea de căutare este flancată cu ea pe ambele părți, orice valoare din Coloana 1 care conține valoarea de căutare din Coloana 2 ar fi considerată ca o potrivire.
De exemplu, * Exxon * ar fi o potrivire pentru ExxonMobil (deoarece * poate reprezenta orice număr de caractere).
Vă pot plăcea, de asemenea, următoarele sfaturi și tutoriale Excel:
- Cum se compară două foi Excel (pentru diferențe)
- Cum să evidențiați celulele goale în Excel.
- Evidențiați ORICE alt rând în Excel.
- Filtru avansat Excel: un ghid complet cu exemple.
- Evidențiați rândurile pe baza unei valori a celulei în Excel.
