Cum să comparați două coloane în Excel și să eliminați duplicatele (evidențiați, colorați, mutați)

Acest articol vă va lua aproximativ 10 minute pentru a le citi. În următoarele 5 minute, puteți compara cu ușurință două coloane în Excel și puteți afla dacă există duplicate în ele, să le ștergeți sau să le evidențiați color. Deci, a venit momentul!

Excel este o aplicație foarte puternică și foarte cool pentru crearea și procesarea unor cantități mari de date. Dacă aveți mai multe registre de lucru cu date (sau doar un tabel imens), atunci probabil că doriți să comparați 2 coloane, să găsiți valori duplicate și apoi să faceți ceva cu ele, de exemplu, ștergeți, evidențiați sau ștergeți conținutul . Coloanele pot fi în același tabel, pot fi adiacente sau nu, pot fi amplasate pe 2 foi diferite sau chiar în cărți diferite.

Imaginează-ți că avem 2 coloane cu numele oamenilor – 5 nume pe coloană A și 3 nume într-o coloană B. Trebuie să comparați numele din aceste două coloane și să găsiți duplicate. După cum înțelegeți, acestea sunt date fictive, luate doar de exemplu. În tabelele reale, avem de-a face cu mii sau chiar zeci de mii de înregistrări.

Opțiunea A: ambele coloane sunt pe aceeași foaie. De exemplu, o coloană A și coloană B.

Opțiunea B: Coloanele sunt pe foi diferite. De exemplu, o coloană A pe foaie Sheet2 și coloană A pe foaie Sheet3.

Excel 2013, 2010 și 2007 au un instrument încorporat Eliminați duplicatele (Eliminați duplicatele) dar este neputincios în această situație, deoarece nu poate compara datele din 2 coloane. În plus, poate elimina doar duplicatele. Nu există alte opțiuni, cum ar fi evidențierea sau schimbarea culorilor. Și punct!

În continuare, vă voi arăta modalitățile posibile de a compara două coloane în Excel, ceea ce vă va permite să găsiți și să eliminați înregistrările duplicate.

• Comparați 2 coloane și căutați duplicate folosind formule
  
  • Opțiunea A: ambele coloane sunt pe aceeași foaie
  • Opțiunea B: coloanele sunt pe foi diferite sau în registre de lucru diferite
• Procesarea duplicatelor găsite
  
  • Afișați numai rândurile duplicate în coloana A
  • Schimbați culoarea sau evidențiați duplicatele găsite
  • Eliminați duplicatele din prima coloană

Comparați 2 coloane în Excel și găsiți intrări duplicate folosind formule

Opțiunea A: ambele coloane sunt pe aceeași foaie

1. În prima celulă goală (în exemplul nostru, aceasta este celula C1), scriem următoarea formulă:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   În formula noastră A1 aceasta este prima celulă a primei coloane pe care o vom compara. 1 $ B$ и 10000 $ B$ acestea sunt adresele primei și ultimei celule din a doua coloană, cu care vom efectua comparația. Rețineți referințele absolute – literele coloanelor și numerele rândurilor sunt precedate de un semn dolar ($). Folosesc referințe absolute, astfel încât adresele celulelor să rămână aceleași atunci când copiez formule.

   Dacă doriți să găsiți duplicate într-o coloană B, schimbați referințele astfel încât formula să arate astfel:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   In schimb "Numai"Și"Duplicat» Puteți scrie propriile etichete, de exemplu, «Nu a fost găsit"Și"Găsite„, sau lasă numai”Duplicat' și introduceți un caracter spațiu în loc de a doua valoare. În acest din urmă caz, celulele pentru care nu se găsesc duplicate vor rămâne goale și, cred, această reprezentare a datelor este cea mai convenabilă pentru o analiză ulterioară.

2. Acum să copiem formula noastră în toate celulele din coloană C, până la rândul de jos, care conține datele din coloană A. Pentru a face acest lucru, mutați cursorul mouse-ului în colțul din dreapta jos al celulei C1, indicatorul va lua forma unei reticule negre, așa cum se arată în imaginea de mai jos:Faceți clic și țineți apăsat butonul stâng al mouse-ului și trageți în jos chenarul cadrului, evidențiind toate celulele în care doriți să introduceți formula. Când toate celulele necesare sunt selectate, eliberați butonul mouse-ului:

   

Sfat: În tabelele mari, copierea formulei va fi mai rapidă dacă utilizați comenzi rapide de la tastatură. Evidențiați o celulă C1 și apăsați Ctrl + C (pentru a copia formula în clipboard), apoi faceți clic Ctrl + Shift + End (pentru a selecta toate celulele care nu sunt goale din coloana C) și în final apăsați Ctrl + V (pentru a introduce formula în toate celulele selectate).

3. Grozav, acum toate valorile duplicate sunt marcate ca „Duplicat„:

Opțiunea B: două coloane sunt pe foi diferite (în registre de lucru diferite)

1. În prima celulă a primei coloane goale din foaia de lucru Sheet2 (în cazul nostru este coloana B) introduceți următoarea formulă:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Aici Sheet3 este numele foii pe care se află coloana a 2-a și $ A $ 1: $ A $ 10000 sunt adrese de celule de la prima până la ultima în această a doua coloană.

2. Copiați formula în toate celulele dintr-o coloană B (la fel ca și opțiunea A).
3. Obtinem acest rezultat:

Procesarea duplicatelor găsite

Grozav, am găsit intrări în prima coloană care sunt prezente și în a doua coloană. Acum trebuie să facem ceva cu ei. Parcurgerea manuală a tuturor înregistrărilor duplicate dintr-un tabel este destul de ineficientă și necesită prea mult timp. Există modalități mai bune.

Afișați numai rândurile duplicate în coloana A

Dacă coloanele dvs. nu au antete, atunci trebuie să le adăugați. Pentru a face acest lucru, plasați cursorul pe numărul care reprezintă prima linie și acesta se va transforma într-o săgeată neagră, așa cum se arată în figura de mai jos:

Faceți clic dreapta și selectați din meniul contextual inserare (Introduce):

Dați nume coloanelor, de exemplu, „Nume si Prenume"Și"Duplicat?» Apoi deschideți fila Date (Date) și apăsați Filtru (Filtru):

După aceea, faceți clic pe săgeata gri de lângă „Duplicat?« pentru a deschide meniul de filtrare; debifați toate elementele din această listă, cu excepția Duplicat, și apăsați OK.

Asta e tot, acum vezi doar acele elemente ale coloanei А, care sunt duplicate în coloană В. Există doar două astfel de celule în tabelul nostru de antrenament, dar, după cum înțelegeți, în practică vor fi mult mai multe.

Pentru a afișa din nou toate rândurile unei coloane А, faceți clic pe simbolul filtrului din coloană В, care acum arată ca o pâlnie cu o săgeată mică și selectați selectați toate (Selectează tot). Sau puteți face același lucru prin Panglică făcând clic Date (Date) > Selectați și filtrați (Sortare și filtrare) > clar (Clar) așa cum se arată în captura de ecran de mai jos:

Schimbați culoarea sau evidențiați duplicatele găsite

Dacă notele „Duplicat” nu este suficient pentru scopurile dvs. și doriți să marcați celulele duplicate cu o culoare diferită a fontului, culoarea de umplere sau o altă metodă...

În acest caz, filtrați duplicatele așa cum se arată mai sus, selectați toate celulele filtrate și faceți clic Ctrl + 1pentru a deschide dialogul Celule de format (format de celule). De exemplu, să schimbăm culoarea de umplere a celulelor din rândurile cu duplicate în galben strălucitor. Desigur, puteți schimba culoarea de umplere cu instrumentul Umple fila (Culoare de umplere). Acasă (Acasă) dar avantajul casetei de dialog Celule de format (Cell Format) prin faptul că puteți configura toate opțiunile de formatare în același timp.

Acum cu siguranță nu veți rata nicio celulă cu duplicate:

Eliminarea valorilor duplicate din prima coloană

Filtrați tabelul astfel încât să fie afișate numai celulele cu valori duplicate și selectați acele celule.

Dacă cele 2 coloane pe care le comparați sunt pe foi diferite, adică în diferite tabele, faceți clic dreapta pe intervalul selectat și selectați Ștergeți rândul (Eliminați linia):

Anunturi OKatunci când Excel vă cere să confirmați că doriți cu adevărat să ștergeți întregul rând al foii și apoi să ștergeți filtrul. După cum puteți vedea, rămân doar rândurile cu valori unice:

Dacă 2 coloane sunt pe aceeași foaie, aproape unul de celălalt (adiacent) sau nu aproape unul de celălalt (nu alăturat), atunci procesul de eliminare a duplicatelor va fi puțin mai complicat. Nu putem elimina întregul rând cu valori duplicate, deoarece acest lucru va elimina și celulele din a doua coloană. Deci pentru a lăsa doar intrări unice într-o coloană А, fa asta:

1. Filtrați tabelul pentru a afișa numai valorile duplicate și selectați acele celule. Faceți clic dreapta pe ele și selectați din meniul contextual Conținutul clar (conținut clar).
2. Curățați filtrul.
3. Selectați toate celulele dintr-o coloană А, începând de la celulă A1 până în partea de jos care conține datele.
4. Apasă pe Date (Date) și apăsați Sortați de la A la Z (Sortați de la A la Z). În caseta de dialog care se deschide, selectați Continuați cu selecția curentă (Sortați în cadrul selecției specificate) și faceți clic pe butonul Negru (Triere):
5. Șterge coloana cu formula, nu vei mai avea nevoie de ea, de acum încolo ai doar valori unice.
6. Asta e, acum coloana А conține numai date unice care nu sunt în coloană В:

După cum puteți vedea, eliminarea duplicatelor din două coloane în Excel folosind formule nu este atât de dificilă.