Cum se compară două coloane în Excel (pentru potriviri și diferențe)
Vezi video – Comparați două coloane în Excel pentru potriviri și diferențe
O întrebare pe care o primesc foarte des 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 de date și de ceea ce dorește utilizatorul de la aceasta.
De exemplu, este posibil să doriți să comparați două coloane și să găsiți sau să evidențiați toate punctele de date care se potrivesc (care se află în ambele coloane), sau numai diferențele (unde un punct de date se află într-o coloană și nu în cealaltă), etc.
Din moment ce sunt întrebat atât de mult despre acest lucru, am decis să scriu acest tutorial masiv cu intenția de a acoperi majoritatea (dacă nu toate) scenariilor posibile.
Dacă vi se pare util, transmiteți-l și altor utilizatori Excel.
Acest tutorial acoperă:
Rețineți că tehnicile de comparare a coloanelor prezentate în acest tutorial nu sunt singurele.
În funcție de setul dvs. de date, este posibil să fie nevoie să schimbați sau să ajustați metoda. Cu toate acestea, principiile de bază ar rămâne aceleași.
Dacă credeți că există ceva ce 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: Compararea celulelor din același rând
Acesta 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 ca rezultatul să fie „TRUE”, iar dacă nu se potrivește, atunci am nevoie ca rezultatul să fie „FALSE”.
Formula de mai jos ar face acest lucru:
=A2=B2
Exemplu: Compararea celulelor din același rând (utilizând formula IF)
Dacă doriți să obțineți un rezultat mai descriptiv, puteți utiliza o simplă formulă IF pentru a returna „Potrivire” atunci când numele sunt identice și „Necorespundere” atunci când numele sunt diferite.
=IF(A2=B2,"Match","Mismatch")
Nota: În cazul în care doriți ca comparația să fie sensibilă la majuscule și minuscule, utilizați următoarea formulă IF:
=IF(EXACT(A2,B2),"Match","Mismatch")
Cu formula de mai sus, „IBM” și „ibm” vor fi considerate două nume diferite, iar formula de mai sus va returna „Mismatch”.
Exemplu: Evidențierea rândurilor cu date care se potrivesc
Dacă doriți să evidențiați rândurile care au date care se potrivesc (în loc să obțineți rezultatul într-o coloană separată), puteți face acest lucru utilizând Formatarea condiționată.
Iată care sunt pașii pentru a face acest lucru:
- Selectați întregul set de date.
- Clic pe fila ‘Home’.
- În grupul Styles, faceți clic pe opțiunea ‘Conditional Formatting’.
- Din lista derulantă, faceți clic pe ‘New Rule’.
- În caseta de dialog ‘New Formatting Rule’, faceți clic pe opțiunea ‘Use a formula to determine which cells to format’.
- În câmpul de formulă, introduceți formula: =$A1=$B1
- Click pe butonul Format și specificați formatul pe care doriți să îl aplicați celulelor care se potrivesc.
- Click 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 corespondențele
Dacă doriți să comparați două coloane și să evidențiați datele care se potrivesc, puteți utiliza funcționalitatea de duplicat din formatarea condiționată.
Rețineți că acest lucru este diferit de ceea ce am văzut atunci când am comparat fiecare rând. În acest caz, nu vom face o comparație rând cu rând.
Exemplu: Compararea a două coloane și evidențierea datelor care se potrivesc
De multe ori, veți obține seturi de date în care există potriviri, dar este posibil ca acestea să nu se afle în același rând.
Ca ceva de genul celor prezentate mai jos:
Rețineți că lista din coloana A este mai mare decât cea din B. De asemenea, unele nume sunt prezente î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 de companii care se potrivesc, puteți face acest lucru folosind formatarea condiționată.
Iată pașii pentru a face acest lucru:
- Select the entire data set.
- Clic pe fila Home.
- În grupul Styles, faceți clic pe opțiunea ‘Conditional Formatting’.
- Pasați cursorul pe opțiunea Highlight Cell Rules.
- Clic pe Duplicate Values.
- În caseta de dialog Duplicate Values, asigurați-vă că este selectată opțiunea ‘Duplicate’.
- Specificați formatarea.
- Clic pe OK.
Pasii de mai sus vă vor da rezultatul prezentat mai jos.
Nota: Regula de dublare a formatelor condiționate nu este sensibilă la majuscule și minuscule. Astfel, ‘Apple’ și ‘apple’ sunt considerate la fel și ar fi evidențiate ca fiind duplicate.
Exemplu: Compararea a două coloane și evidențierea datelor necorespunzătoare
Î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ționată și pentru acest lucru.
- Selectați întregul set de date.
- Click pe fila Home.
- În grupul Styles (Stiluri), faceți clic pe opțiunea ‘Conditional Formatting’ (Formatare condiționată).
- Pasați cursorul pe opțiunea Highlight Cell Rules (Reguli de evidențiere a celulelor).
- Click pe Duplicate Values (Valori duplicate).
- În caseta de dialog Duplicate Values (Valori duplicate), asigurați-vă că este selectat ‘Unique’ (Unic).
- Specificați formatarea.
- Clic pe OK.
Aceasta vă va da rezultatul prezentat mai jos. Se evidențiază toate celulele care au un nume care nu este prezent în cealaltă listă.
Comparați două coloane și găsiți punctele de date lipsă
Dacă doriți să identificați dacă un punct de date dintr-o listă este prezent în cealaltă listă, trebuie să folosiț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 și în coloana B,
Pentru a face acest lucru, pot utiliza următoarea formulă VLOOKUP.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Această formulă utilizează funcția VLOOKUP pentru a verifica dacă un nume de companie din A este prezent în coloana B sau nu. Dacă este prezent, 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 din coloana B.
Funcția ISERROR ar returna TRUE dacă există rezultatul VLOOKUP este o eroare și FALSE 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.
Puteți utiliza, de asemenea, funcția MATCH pentru a face același lucru;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Nota: Personal, prefer să folosesc funcția Match (sau combinația INDEX/MATCH) în loc de VLOOKUP. O consider mai flexibilă și mai puternică. Puteți citi diferența dintre Vlookup și Index/Match aici.
Comparați două coloane și extrageți datele care se potrivesc
Dacă aveți două seturi de date și doriți să comparați elementele dintr-o listă cu cele din cealaltă și să extrageți punctul de date care se potrivește, trebuie să folosiți formulele de căutare.
Exemplu: Pull the Matching Data (Exact)
De exemplu, în lista de mai jos, vreau să extrag valoarea de evaluare a pieței pentru coloana 2. Pentru a face acest lucru, trebuie să caut acea valoare în coloana 1 și apoi să extrag valoarea de evaluare a pieței corespunzătoare.
Mai jos este formula care va face acest lucru:
=VLOOKUP(D2,$A:$B,2,0)
sau
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Exemplu: Extragerea datelor care se potrivesc (parțial)
În cazul în care obțineți un set de date în care există o diferență minoră între 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 care nu poate fi utilizată aici.
Să presupunem că aveți setul de date prezentat 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).
În acest caz, puteți utiliza o căutare parțială prin utilizarea caracterelor joker.
Formula următoare va da este rezultatul corect în acest caz:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
sau
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
În exemplul de mai sus, asteriscul (*) este un caracter wildcard care poate reprezenta orice număr de caractere. Atunci când valoarea de căutare este flancată de acesta pe ambele părți, orice valoare din coloana 1 care conține valoarea de căutare din coloana 2 va fi considerată ca fiind o potrivire.
De exemplu, *Exxon* ar fi o potrivire pentru ExxonMobil (deoarece * poate reprezenta orice număr de caractere).
You May Also Like the Following Excel Tips & Tutoriale:
- Cum se compară două foi Excel (pentru diferențe)
- Cum se evidențiază celulele goale în Excel.
- Se evidențiază TOATE celelalte rânduri în Excel.
- Excel Advanced Filter: Un ghid complet cu exemple.
- Highlight Rows Based on a Cell Value in Excel.