Cum să găsiți aceleași valori într-o coloană Excel. Găsiți și eliminați duplicatele în Microsoft Excel

Sa luam in considerare La Cum să găsiți și să evidențiați valori identice în Excel. Formatarea condiționată ne va ajuta. Ce este formatarea condiționată și cum să lucrați cu aceasta, consultați articolul „Formatarea condiționată în Excel”.Puteți selecta valori duplicate în Excel atât în întregul tabel, cât și într-un anumit interval (rând, coloană). O functie " Filtrați în Excel " va ajuta la ascunderea lor dacă este necesar. Să luăm în considerare mai multe moduri.
Prima cale.
Cum să găsiți aceleași valori în Excel.
De exemplu, numărul, numele de familie etc. Cum să faceți acest lucru, consultați articolul „Cum să selectați celulele în Excel”.
A doua cale.
Cum să evidențiați valorile duplicate în Excel. În acest tabel trebuie să evidențiem anul nașterii 1960. Selectați coloana „Anul nașterii”.În fila „Acasă”, în secțiunea „Stiluri”, faceți clic pe butonul „Formatare condiționată”. Apoi, în secțiunea „Reguli pentru celulele selectate”, selectați „Valori duplicate”.
În caseta de dialog care apare, selectați ceea ce trebuie să evidențiem: valori duplicate sau unice. Selectați culoarea de umplere a celulei sau culoarea fontului.
Pentru mai multe detalii, consultați articolul „Evidențiați data, ziua săptămânii furnizate în Excel”.
Faceți clic pe „OK”. În coloana D sunt evidențiați toți anii - 1960.

În formatarea condiționată, puteți selecta și funcția „Conține text” din secțiunea „Reguli pentru celulele selectate”. Scrieți acest text (de exemplu, nume, număr etc.), iar toate celulele cu acest text vor fi evidențiate în culoare. Am scris numele de familie „Ivanov”. Există multe modalități de a găsi valori identice în Excel și de a le evidenția nu numai cu culoare, ci și cu cuvinte, numere și semne. Puteți configura tabelul astfel încât duplicatele să fie nu numai evidențiate, ci și numărate. Puteți selecta semnificații repetate de la primul cuvânt sau puteți selecta duplicate începând cu al doilea. Citiți despre toate acestea și multe altele în articolul „

Să profităm de formatarea condiționată. Am discutat deja acest subiect în articol, iar acum îl vom aplica pentru a rezolva o altă problemă.

Se caută înregistrări duplicate în Excel 2007

Să selectăm coloana în care vom căuta duplicate (în exemplul nostru, aceasta este coloana cu numere de catalog), iar în fila principală căutăm butonul „Formatizare condiționată”. Mai departe, punct cu punct, ca în figură.

În noua fereastră, tot ce trebuie să facem este să fim de acord cu schema de culori propusă (sau să alegem alta) și să facem clic pe „OK”.

Acum, valorile noastre care se repetă sunt colorate în roșu. Dar sunt împrăștiate pe toată masa și acest lucru este incomod. Trebuie să sortați liniile pentru a le aduna. Vă rugăm să rețineți că în tabelul de mai sus există o coloană „Nr. articol” care conține numere de rând. Dacă nu aveți unul, ar trebui să creați unul, astfel încât să putem restabili ulterior ordinea inițială a datelor din tabel.
Selectați întregul tabel, accesați fila „Date” și faceți clic pe butonul „Sortați”. În fereastra nouă trebuie să setăm ordinea de sortare. Setăm valorile de care avem nevoie și adăugăm următorul nivel. Trebuie să sortăm rândurile mai întâi după culoarea celulei și apoi după valoarea celulei, astfel încât duplicatele să fie unul lângă celălalt.

Ne ocupăm de duplicatele găsite. În acest caz, liniile duplicate pot fi pur și simplu șterse.

Observați că, pe măsură ce duplicatele sunt eliminate, celulele roșii revin la alb.
După ce ați scăpat de celulele colorate, selectați din nou întregul tabel și sortați-l după coloana „Nr./n”. După aceasta, nu mai rămâne decât să corectăm numerotarea care s-a rătăcit din cauza liniilor șterse.

Cum se face acest lucru în Excel 2003

Aici va fi puțin mai complicat - va trebui să utilizați funcția logică „COUNTIF()”.
Introduceți celula cu prima valoare, printre care veți căuta duplicate.

Format.
Formatarea condițională.

În primul câmp, selectați „Formulă” și introduceți formula „=COUNTIF(C;RC)>1”. Doar nu uitați să schimbați aspectul la timp - „COUNTIF” este introdus în aspectul rus, iar „(C;RC)>1” în cel englez.

Selectați o culoare făcând clic pe butonul „Format” din fila „Vizualizare”.
Acum trebuie să copiem acest format în întreaga coloană.

Editați | ×.
Copie.

Selectați întreaga coloană cu datele verificate.

Editați | ×.
Inserție specială.

Selectați „Formate”, „OK” și formatarea condiționată este copiată în întreaga coloană.
Cucerește Excel și ne vedem în curând!

Găsirea duplicatelor în Excel este una dintre cele mai comune sarcini pentru orice angajat de birou. Există mai multe moduri diferite de a rezolva acest lucru. Dar cât de repede puteți găsi duplicate în Excel și le puteți evidenția cu culoare? Pentru a răspunde la această întrebare frecventă, să ne uităm la un exemplu specific.

Cum să găsiți valori duplicate în Excel?

Să presupunem că înregistrăm comenzile primite de companie prin fax și e-mail. Poate apărea o situație în care aceeași comandă a fost primită prin două canale de informații primite. Dacă înregistrați aceeași comandă de două ori, pot apărea anumite probleme pentru companie. Mai jos vom lua în considerare o soluție care utilizează formatarea condiționată.

Pentru a evita comenzile duplicate, puteți utiliza formatarea condiționată pentru a găsi rapid valori duplicate într-o coloană Excel.

Exemplu de jurnal zilnic de comenzi pentru mărfuri:

Pentru a verifica dacă jurnalul de comenzi conține posibile duplicate, vom analiza după numele clienților - coloana B:

După cum puteți vedea în imaginea cu formatare condiționată, am reușit să implementăm ușor și rapid căutarea duplicată în Excel și să detectăm datele celulelor duplicate pentru tabelul cu istoricul comenzilor.

Exemplu de funcție COUNTIF și evidențierea valorilor duplicate

Principiul formulei pentru găsirea duplicatelor folosind formatarea condiționată este simplu. Formula conține funcția =COUNTIF(). Această funcție poate fi folosită și atunci când se caută valori identice într-un interval de celule. Primul argument din funcție este intervalul de date care trebuie vizualizat. În al doilea argument indicăm ceea ce căutăm. Primul nostru argument are referințe absolute, deoarece trebuie să fie imuabil. Iar al doilea argument, dimpotrivă, trebuie să se schimbe la adresa fiecărei celule din intervalul vizualizat, prin urmare are o legătură relativă.

Cele mai rapide și mai ușoare modalități: găsiți duplicate în celule.

După funcție, există un operator care compară numărul de valori găsite într-un interval cu numărul 1. Adică, dacă există mai multe valori, atunci formula returnează valoarea TRUE și se aplică formatarea condiționată celulei curente. .

Dacă lucrați cu cantități mari de informații în Excel și le adăugați în mod regulat, de exemplu, date despre elevii școlii sau angajații companiei, atunci pot apărea valori duplicate în astfel de tabele, cu alte cuvinte, duplicate.

În acest articol ne vom uita la cum să găsiți, selectați, ștergeți și numărați numărul de valori duplicate în Excel.

Cum să găsiți și să evidențiați

Puteți găsi și evidenția duplicatele într-un document utilizând formatarea condiționată în Excel. Selectați întregul interval de date din tabelul dorit. În fila „Acasă”, faceți clic pe butonul "Formatarea condițională", selectați din meniu „Reguli pentru evidențierea celulelor” – „Valori duplicate”.

În fereastra următoare, selectați din lista verticală "repetitiv"și culoarea celulei și a textului în care să picteze duplicatele găsite. Apoi faceți clic pe „OK” și programul va căuta duplicate.

În exemplu, Excel a evidențiat toate aceleași informații în roz. După cum puteți vedea, datele nu sunt comparate rând cu rând, dar sunt selectate celule identice din coloane. Prin urmare, celula „Sasha V.” este evidențiată. . Pot exista mai mulți astfel de studenți, dar cu nume de familie diferite.

Cum să numere

Dacă trebuie să găsiți și să numărați numărul de valori care se repetă în Excel, vom crea un tabel pivot Excel pentru aceasta. Adăugați coloana „Cod” la cea originală și completați-o cu „1”: puneți 1, 1 în primele două celule, selectați-le și trageți-le în jos. Când se găsesc duplicate pentru rânduri, valoarea din coloana Cod va fi incrementată cu câte unul de fiecare dată.

Selectați totul împreună cu titlurile, accesați fila „Inserare” și apăsați butonul "Masă rotativă".

Pentru a afla mai multe despre cum să lucrați cu tabele pivot în Excel, citiți articolul urmând linkul.

În fereastra următoare, celulele intervalului sunt deja indicate; utilizați un marcator pentru a marca „La o foaie nouă” și faceți clic pe „OK”.

În partea dreaptă, trageți primele trei titluri în zonă „Titluri de rând”, și trageți câmpul „Cod” în zona „Valori”.

Ca urmare, vom obține un tabel pivot fără duplicate, iar în câmpul „Cod” vor exista numere corespunzătoare valorilor repetate din tabelul sursă - de câte ori a fost repetat acest rând în el.

Pentru comoditate, să evidențiem toate valorile din coloană „Suma după codul câmpului”și sortați-le în ordine descrescătoare.

Cred că acum puteți găsi, selecta, șterge și chiar număra numărul de duplicate în Excel pentru toate rândurile din tabel sau numai pentru coloanele selectate.

Evaluează acest articol:

Poate că toți cei care lucrează cu date în Excel se confruntă cu întrebarea cum să compare două coloane în Excel pentru asemănări și diferențe. Există mai multe moduri de a face acest lucru. Să aruncăm o privire mai atentă la fiecare dintre ele.

Cum se compară două coloane în Excel rând cu rând

Când comparați două coloane de date, este adesea necesar să comparați datele din fiecare rând individual pentru potriviri sau diferențe. Putem face o astfel de analiză folosind funcția. Să vedem cum funcționează acest lucru folosind exemplele de mai jos.

Exemplul 1: Cum să comparați două coloane pentru potriviri și diferențe pe același rând

Pentru a compara datele din fiecare rând de două coloane din Excel, să scriem o formulă simplă. Formula trebuie inserată în fiecare rând din coloana alăturată, lângă tabelul în care se află datele principale. După ce am creat o formulă pentru primul rând al tabelului, o putem întinde/copia în rândurile rămase.

Pentru a verifica dacă două coloane din același rând conțin aceleași date, avem nevoie de formula:

=IF(A2=B2; „Potrivire”; „”)

Formula care determină diferențele dintre datele a două coloane dintr-un rând va arăta astfel:

=DACA(A2<>B2; "Nu se potriveste"; „”)

Putem potrivi testul de potrivire și diferență între două coloane pe o linie într-o singură formulă:

=IF(A2=B2; „Potriviți”; „Nu se potrivesc”)

=DACA(A2<>B2; "Nu se potriveste"; „Potriviți”)

Un exemplu de rezultat de calcul ar putea arăta astfel:

Pentru a compara datele din două coloane ale aceluiași rând, ținând cont de majuscule și minuscule, utilizați formula:

=DACA(POTRIVIT(A2,B2), „Potrivire”, „Unic”)

Cum să comparați mai multe coloane pentru potriviri într-un singur rând Excel

Excel vă permite să comparați datele din mai multe coloane ale aceluiași rând folosind următoarele criterii:

Găsiți rânduri cu aceleași valori în toate coloanele tabelului;
Găsiți rânduri cu aceleași valori în oricare două coloane ale tabelului;

Exemplul 1. Cum să găsiți potriviri pe un rând în mai multe coloane ale unui tabel

Să ne imaginăm că tabelul nostru este format din mai multe coloane de date. Sarcina noastră este să găsim rânduri în care valorile se potrivesc în toate coloanele. Excel și funcțiile ne vor ajuta în acest sens. Formula de determinare a potrivirilor va fi următoarea:

=DACĂ(ȘI(A2=B2;A2=C2); „Coincide”; „“)

Dacă tabelul nostru are o mulțime de coloane, atunci va fi mai ușor să utilizați funcția în combinație cu:

=IF(COUNTIF($A2:$C2,$A2)=3;”Potrivire”;” “)

În formulă, „5” indică numărul de coloane ale tabelului pentru care am creat formula. Dacă tabelul are mai multe sau mai puține coloane, atunci această valoare ar trebui să fie egală cu numărul de coloane.

Exemplul 2: Cum să găsiți potriviri de pe același rând în oricare două coloane ale unui tabel

Să ne imaginăm că sarcina noastră este să identificăm dintr-un tabel cu date în mai multe coloane acele rânduri în care datele sunt aceleași sau se repetă în cel puțin două coloane. Funcțiile și ne vor ajuta în acest sens. Să scriem o formulă pentru un tabel format din trei coloane de date:

=IF(SAU(A2=B2;B2=C2,A2=C2);”Potrivire”;” “)

În cazurile în care există prea multe coloane în tabelul nostru, formula noastră cu funcția va fi foarte mare, deoarece în parametrii săi trebuie să specificăm criteriile de potrivire între fiecare coloană a tabelului. O modalitate mai ușoară, în acest caz, este să utilizați .

=DACA(NUMĂRĂDAC(B2:D2,A2)+NUMĂRĂDAC(C2:D2,B2)+(C2=D2)=0; „Șir unic”; „Șir nu unic”)

=IF(COUNTIF($B:$B,$A5)=0; „Nu există potriviri în coloana B”; „Sunt potriviri în coloana B”)

Această formulă verifică valorile din coloana B pentru a vedea dacă se potrivesc cu celulele din coloana A.

Dacă tabelul este format dintr-un număr fix de rânduri, puteți specifica un interval clar în formulă (de exemplu, $B2:$B10). Acest lucru va accelera formula.

Cum să compari două coloane în Excel pentru potriviri și să le evidențiezi în culoare

Când căutăm potriviri între două coloane în Excel, este posibil să fie nevoie să vizualizăm potrivirile găsite sau diferențele în date, de exemplu folosind evidențierea culorilor. Cel mai simplu mod de a evidenția potrivirile și diferențele de culoare este să utilizați formatarea condiționată în Excel. Să vedem cum să faceți acest lucru folosind exemplele de mai jos.

Găsirea și evidențierea potrivirilor în culori în mai multe coloane în Excel

În cazurile în care trebuie să găsim potriviri în mai multe coloane, atunci pentru aceasta avem nevoie de:

Selectați coloanele cu date în care doriți să calculați potrivirile;
În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli pentru evidențierea celulelor” -> „Valori repetate”;
În caseta de dialog pop-up, selectați „Repetare” în lista derulantă din stânga, iar în lista verticală din dreapta, selectați în ce valori de repetare a culorii vor fi evidențiate. Faceți clic pe butonul „OK”:
După aceasta, potrivirile vor fi evidențiate în coloana selectată:

Găsiți și evidențiați rândurile care se potrivesc în Excel

Găsirea celulelor care se potrivesc cu date din două sau mai multe coloane și căutarea potrivirilor de rânduri întregi cu date sunt concepte diferite. Vă rugăm să rețineți cele două tabele de mai jos:

Tabelele de mai sus conțin aceleași date. Diferența lor este că în exemplul din stânga am căutat celule potrivite, iar în dreapta am găsit linii întregi care se repetă cu date.

Să vedem cum să găsim rânduri potrivite într-un tabel:

În dreapta tabelului cu datele, vom crea o coloană auxiliară în care, vizavi de fiecare rând cu datele, vom pune o formulă care combină toate valorile rândului tabelului într-o singură celulă:

=A2&B2&C2&D2

În coloana auxiliară veți vedea datele combinate din tabel:

Acum, pentru a determina rândurile care se potrivesc din tabel, parcurgeți următorii pași:

Selectați zona cu date din coloana auxiliară (în exemplul nostru, acesta este un interval de celule E2:E15 );
În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli pentru evidențierea celulelor” -> „Valori repetate”;
În caseta de dialog pop-up, selectați „Repetare” în lista derulantă din stânga, iar în lista verticală din dreapta, selectați în ce culoare vor fi evidențiate valorile care se repetă. Faceți clic pe butonul „OK”:
După aceasta, liniile duplicate vor fi evidențiate în coloana selectată: