Cum se evidențiază celulele și rândurile duplicate în Excel

În acest tutorial, veți învăța cum să arătați duplicatele în Excel. Vom examina îndeaproape diferite metode pentru a umbri celulele duplicate, rândurile întregi sau dublurile consecutive folosind formatarea condiționată. De asemenea, veți vedea cum să evidențiați duplicatele cu diferite culori folosind un instrument specializat.

Săptămâna trecută, am explorat diferite modalități de a identifica duplicatele în Excel. Fără îndoială, formulele de duplicat sunt foarte utile, dar evidențierea intrărilor duplicate cu o culoare definită ar putea facilita și mai mult analiza datelor.

Cea mai rapidă modalitate de a găsi și evidenția duplicatele în Excel este utilizarea formatării condiționate. Cel mai mare avantaj al acestei metode este că nu numai că vă arată dublurile existente, dar detectează și colorează noile dubluri pe măsură ce introduceți, modificați sau suprascrieți datele.

În continuare, în acest tutorial, veți găsi mai multe modalități de evidențiere a înregistrărilor duplicate, în funcție de sarcina dvs. specifică. Aceste tehnici funcționează în toate versiunile de Excel 2016, Excel 2013, Excel 2010 și inferioare.

• Sublinierea dublurilor în Excel cu primele apariții (construite-în regulă încorporată)
• Cum se evidențiază duplicatele în Excel cu excepția primelor ocurențe
• Cum se evidențiază duplicatele într-un interval (mai multe coloane)
• Cum se evidențiază al N-lea duplicat și cele ulterioare înregistrări
• Ascundeți rânduri întregi pe baza valorilor duplicate dintr-o coloană
• Cum se evidențiază rândurile duplicate în Excel
• Cum se vizualizează celulele duplicate consecutive
• Duplicate Remover – cel mai rapid mod de a evidenția dublurile în Excel

Cum se evidențiază dublurile în Excel folosind regula încorporată (cu primele apariții)

Pentru început, în toate versiunile Excel, există o regulă predefinită pentru evidențierea celulelor duplicate. Pentru a utiliza această regulă în foile dvs. de lucru, efectuați următorii pași:

1. Selectați datele pe care doriți să le verificați pentru duplicate. Aceasta poate fi o coloană, un rând sau un interval de celule.
2. În fila Home, în grupul Styles, faceți clic pe Conditional Formatting > Highlight Cells Rules > Duplicate Values…
   
3. Fereastra de dialog Duplicate Values se va deschide cu formatul Light Red Fill și Dark Red Text selectat în mod implicit. Pentru a aplica formatul implicit, faceți pur și simplu clic pe OK.

În afară de formatarea de umplere roșie și de formatare a textului, o mână de alte formate predefinite sunt disponibile în lista derulantă. Pentru a evidenția dublurile folosind o altă culoare, faceți clic pe Custom Format… (ultimul element din lista derulantă) și selectați culoarea de umplere și/sau de font pe care o doriți.

Utilizând regula încorporată, puteți evidenția duplicatele într-o coloană sau în mai multe coloane, așa cum se arată în următoarea captură de ecran:

Când utilizați regula încorporată în Excel pentru evidențierea valorilor duplicate, vă rugăm să țineți cont de următoarele două lucruri:

• Funcționează numai pentru celule individuale. Pentru a evidenția rândurile duplicate, ar trebui să vă creați propriile reguli, fie pe baza valorilor dintr-o anumită coloană, fie prin compararea valorilor din mai multe coloane.
• Ea nuanțează celulele duplicate, inclusiv primele lor apariții. Pentru a evidenția toate duplicatele, cu excepția primelor apariții, creați o regulă de formatare condiționată bazată pe această formulă.

Cum se evidențiază duplicatele fără primele ocurențe

Pentru a evidenția a doua și toate ocurențele duplicate ulterioare, selectați celulele pe care doriți să le colorați și creați o regulă bazată pe o formulă în acest mod:

1. În fila Home, în grupul Styles, faceți clic pe Conditional Formatting > New rule > Folosiți o formulă pentru a determina ce celule să formatați.
2. În caseta Format values where this formula is true, introduceți o formulă similară cu aceasta:
   =COUNTIF($A:$A2,$A2)>1

   Unde A2 este celula cea mai de sus din intervalul selectat.

3. Click pe butonul Format… și selectați culoarea de umplere și/sau de font pe care o doriți.
4. În cele din urmă, faceți clic pe OK pentru a salva și aplica regula.

Dacă nu aveți prea multă experiență cu formatarea condiționată în Excel, veți găsi pașii detaliați pentru a crea o regulă bazată pe formule în următorul tutorial: Formatarea condiționată Excel bazată pe valoarea unei alte celule.

Ca rezultat, celulele duplicate, cu excepția primelor instanțe, vor fi evidențiate cu culoarea aleasă de dumneavoastră:

Cum se afișează a 3-a, a 4-a și toate înregistrările duplicate ulterioare

Pentru a vizualiza duplicatele începând cu a N-a apariție, creați o regulă de formatare condiționată bazată pe formula ca în exemplul anterior, cu singura diferență că înlocuiți >1 la sfârșitul formulei cu numărul necesar. De exemplu:

Pentru a evidenția a 3-a și toate aparițiile duplicate ulterioare, creați o regulă de formatare condiționată bazată pe această formulă:

=COUNTIF($A:$A2,$A2)>=3

Pentru a evidenția a 4-a și toate înregistrările duplicate ulterioare, utilizați această formulă:

=COUNTIF($A:$A2,$A2)>=4

Pentru a evidenția numai anumite apariții, utilizați operatorul egal cu (=). De exemplu, pentru a evidenția numai a 2-a ocurență, veți folosi această formulă:

=COUNTIF($A:$A2,$A2)=2

Cum se evidențiază duplicatele într-un interval (mai multe coloane)

Când doriți să verificați duplicatele pe mai multe coloane, nu prin compararea coloanelor între ele, ci să găsiți toate ocurențele aceluiași element în toate coloanele, utilizați una dintre următoarele soluții.

Subliniați duplicatele în mai multe coloane, inclusiv primele apariții

Dacă prima instanță a unui element care apare în setul de date de mai multe ori este considerată un duplicat, cea mai simplă cale de urmat este utilizarea regulii încorporate de Excel pentru duplicate.

Sau, creați o regulă de formatare condiționată cu această formulă:

De exemplu, pentru a evidenția dublurile din intervalul A2:C8, formula este următoarea:

=COUNTIF($A:$C, A2)>1

Rețineți utilizarea referințelor absolute la celule pentru interval ($A$2:$C$8) și a referințelor relative pentru celula de sus (A2).

Evidențiați duplicatele din mai multe coloane fără primele ocurențe

Soluția pentru acest scenariu este mult mai complicată, nu e de mirare că Excel nu are o regulă încorporată pentru aceasta 🙂

Pentru a evidenția intrările duplicate în mai multe coloane ignorând primele apariții, va trebui să creați 2 reguli cu următoarele formule:

Regula 1. Se aplică la prima coloană

Aici folosiți exact aceeași formulă pe care am folosit-o pentru a evidenția intrările duplicate fără primele ocurențe într-o singură coloană (pașii detaliați pot fi găsiți aici).

În acest exemplu, creăm o regulă pentru A2:A8 cu această formulă:

=COUNTIF($A:$A2,$A2)>1

Ca rezultat, elementele duplicate fără prima apariție sunt evidențiate în coloana cea mai din stânga a intervalului (în cazul nostru există un singur astfel de element):

Regula 2. Se aplică la toate coloanele ulterioare

Pentru a evidenția duplicatele în coloanele rămase (B2:C8), utilizați această formulă:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

În formula de mai sus, prima funcție COUNTIF numără aparițiile unui anumit element în prima coloană, iar a doua funcție COUNTIF face același lucru pentru toate coloanele ulterioare. Apoi, se adună aceste numere și se verifică dacă suma este mai mare decât 1.

Ca rezultat, sunt găsite și evidențiate toate elementele duplicate, cu excepția primei lor apariții:

Subliniind duplicatele din toate coloanele cu o singură regulă

O altă soluție posibilă este să adăugați o coloană goală în stânga setului de date și să combinați formulele de mai sus într-o singură formulă ca aceasta:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Unde B2 este celula de sus cu date din a 2-a coloană a intervalului țintă.

Pentru a înțelege mai bine formula, să o împărțim în 2 părți principale:

• Pentru prima coloană (B), condiția IF nu este niciodată îndeplinită, astfel încât se aplică doar a doua funcție COUNTIF, care este formula obișnuită pe care am folosit-o deja pentru a găsi duplicatele fără prima apariție într-o singură coloană.
• Pentru toate coloanele următoare (C2:D8), punctul cheie este utilizarea inteligentă a referințelor absolute și relative în cele două funcții COUNTIF. Pentru a face lucrurile mai ușor de înțeles, am copiat-o în coloana G, astfel încât să puteți vedea cum se schimbă formula atunci când este aplicată la alte celule:
  

Pentru că condiția IF este întotdeauna TRUE pentru toate coloanele, altele decât prima (numărul de coloane este mai mare decât 1), formula procedează astfel:

• Contează numărul de apariții ale unui anumit element (D5 în captura de ecran de mai sus) în toate coloanele din stânga coloanei date: COUNTIF(B:$C,D5)
• Contează numărul de apariții ale unui anumit element în coloana elementului, până la celula elementului: COUNTIF(D:D5,D5)
• În cele din urmă, formula însumează rezultatele ambelor funcții COUNTIF. Dacă numărul total este mai mare decât 1, adică dacă există mai mult de o apariție a elementului, se aplică regula și elementul este evidențiat.

Evidențierea unor rânduri întregi pe baza valorilor duplicate dintr-o coloană

Dacă tabelul dvs. conține mai multe coloane, este posibil să doriți să evidențiați rânduri întregi pe baza înregistrărilor duplicate dintr-o anumită coloană.

După cum știți deja, regula încorporată de Excel pentru duplicate funcționează numai la nivel de celulă. Dar o regulă personalizată bazată pe o formulă nu are nicio problemă cu umbrirea rândurilor. Punctul cheie este să selectați rândurile întregi și apoi să creați o regulă cu una dintre următoarele formule:

• Pentru a evidenția rândurile duplicate, excluzând primele apariții:
  =COUNTIF($A:$A2, $A2)>1
• Pentru a evidenția rândurile duplicate, inclusiv primele apariții:
  =COUNTIF($A:$A, $A2)>1

Unde A2 este prima celulă și A15 este ultima celulă utilizată din coloana pe care doriți să o verificați pentru duplicate. După cum vedeți, utilizarea inteligentă a referințelor absolute și relative ale celulelor este ceea ce face diferența.
Captura de ecran următoare demonstrează ambele reguli în acțiune:

Cum să evidențiați rândurile duplicate în Excel

Exemplul anterior a demonstrat cum să colorați rânduri întregi pe baza valorilor duplicate dintr-o anumită coloană. Dar ce se întâmplă dacă doriți să vizualizați rândurile care au valori identice în mai multe coloane? Sau, cum evidențiați rândurile duplicate absolute, cele care au valori complet egale în toate coloanele?

Pentru aceasta, utilizați funcția COUNTIFS care permite compararea celulelor după mai multe criterii. De exemplu, pentru a evidenția rândurile duplicate care au valori identice în coloanele A și B, utilizați una dintre următoarele formule:

• Pentru a evidenția rândurile duplicate cu excepția primei apariții:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Pentru a evidenția rândurile duplicate cu prima apariție:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

Captura de ecran următoare demonstrează rezultatul:

După cum înțelegeți, exemplul de mai sus este doar în scop demonstrativ. Atunci când evidențiați rânduri duplicate în foile dvs. din viața reală, în mod firesc nu sunteți limitat la compararea valorilor doar în 2 coloane, funcția COUNTIFS poate procesa până la 127 de perechi interval/criteriu.

Evidențierea celulelor duplicate consecutive în Excel

Câteodată, este posibil să nu aveți nevoie să evidențiați toate dublurile dintr-o coloană, ci mai degrabă să afișați doar celulele duplicate consecutive, adică cele care se află una lângă cealaltă. Pentru a face acest lucru, selectați celulele cu date (fără a include antetul coloanei) și creați o regulă de formatare condiționată cu una dintre următoarele formule:

• Pentru a evidenția duplicatele consecutive fără prima apariție:
  =$A1=$A2
• Pentru a evidenția duplicatele consecutive cu primele apariții:
  =OR($A1=$A2, $A2=$A3)

Captura de ecran următoare demonstrează evidențierea textelor consecutive duplicate, dar aceste reguli vor funcționa și pentru numere și date consecutive duplicate:

Dacă foaia Excel poate avea rânduri goale și nu doriți ca celulele goale consecutive să fie evidențiate, faceți următoarele îmbunătățiri la formule:

• Pentru a evidenția celulele duplicate consecutive fără prima ocurență și a ignora celulele goale:
  =AND($A2<>"", $A1=$A2)
• Pentru a evidenția celulele duplicate consecutive cu prima ocurență și a ignora celulele goale:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Cum vedeți, nu este mare lucru să evidențiați celulele duplicate în Excel folosind formatarea condiționată. Cu toate acestea, există chiar o modalitate mai rapidă și mai ușoară. Pentru a-l afla, citiți următoarea secțiune a acestui tutorial.

Cum să evidențiați duplicatele în Excel cu Duplicate Remover

Complementul Duplicate Remover este soluția all-in-one pentru a rezolva problema înregistrărilor duplicate în Excel. Acesta poate găsi, evidenția, selecta, copia sau muta celule duplicate sau întregi rânduri duplicate.

În ciuda numelui său, add-in-ul poate evidenția rapid duplicatele în culori diferite, fără a le șterge.

Duplicate Remover adaugă 3 caracteristici noi la panglica Excel:

• Dedupe Table – pentru a găsi și evidenția imediat duplicatele într-un tabel.
• Duplicate Remover – asistent pas cu pas cu opțiuni avansate pentru a identifica și evidenția duplicatele sau valorile unice într-un tabel.
• Compare 2 Tables – pentru a găsi și evidenția duplicatele prin compararea a două coloane sau două tabele separate.

După instalarea Ultimate Suite for Excel, veți găsi aceste instrumente în fila Ablebits Data în grupul Dedupe:

Evidențierea duplicatelor în Excel în câteva clicuri

Pentru acest exemplu, am creat următorul tabel cu câteva sute de rânduri. Iar scopul nostru este de a evidenția rândurile duplicate care au valori egale în toate cele trei coloane:

Credeți sau nu, puteți obține rezultatul dorit cu doar 2 clicuri de mouse 🙂

1. Cu orice celulă din tabel selectată, faceți clic pe butonul Dedupe Table, iar ingeniosul add-in va prelua întregul tabel.
2. Fereastra de dialog Dedupe Table se va deschide cu toate coloanele selectate automat, iar opțiunea Color duplicates aleasă în mod implicit. Așadar, tot ce trebuie să faceți este să faceți clic pe OK 🙂 Gata!

Sfat. Dacă doriți să detectați rândurile duplicate în funcție de una sau mai multe coloane, debifați toate coloanele irelevante și lăsați selectate doar coloana (coloanele) cheie.

Și rezultatul ar arăta asemănător cu acesta:

Așa cum vedeți în imaginea de mai sus, instrumentul Dupe Table a evidențiat rândurile duplicate fără primele ocurențe.

Dacă doriți să evidențiați dublurile, inclusiv primele ocurențe, sau dacă doriți să colorați înregistrările unice mai degrabă decât dublurile, sau dacă nu vă place culoarea roșie implicită, atunci utilizați expertul Duplicate Remover care are toate aceste caracteristici și multe altele.

Subliniați duplicatele în Excel folosind un asistent avansat pas cu pas

În comparație cu instrumentul rapid Dedupe Table, asistentul Duplicate Remover necesită câteva clicuri în plus, dar compensează acest lucru cu o serie de opțiuni suplimentare. Permiteți-mi să vi-l arăt în acțiune:

1. Selectați orice celulă din tabelul dvs. în care doriți să evidențiați duplicatele și faceți clic pe butonul Duplicate Remover de pe panglică. Expertul va rula și întregul tabel va fi selectat. Suplimentul va sugera, de asemenea, crearea unei copii de rezervă a tabelului dumneavoastră, pentru orice eventualitate. Dacă nu aveți nevoie de ea, debifați această casetă.

   Verificați că tabelul a fost selectat corect și faceți clic pe Next.

   

2. Selectați unul dintre următoarele tipuri de date pe care doriți să le găsiți:
   • Duplicates exceptând primele apariții
   • Duplicates with 1st occurrences
   • Unique values
   • Unique values and 1st duplicate occurrences

   Pentru acest exemplu, să găsim Duplicates + 1st occurrences:
   

3. Acum, selectați coloanele pentru a verifica dacă există duplicate. Deoarece dorim să evidențiem rândurile duplicate complete, am selectat toate cele 3 coloane.
   

   În plus, add-in-ul vă permite să specificați dacă tabelul dvs. are anteturi și dacă doriți să săriți peste celulele goale. Ambele opțiuni sunt selectate în mod implicit.

4. În cele din urmă, alegeți acțiunea pe care să o efectuați asupra duplicatelor. Aveți o serie de opțiuni, cum ar fi selectarea, ștergerea, copierea, mutarea duplicatelor sau adăugarea unei coloane de stare pentru a identifica dublurile.

Din moment ce astăzi explorăm diferite moduri de a evidenția duplicatele în Excel, alegerea noastră este evidentă 🙂 Deci, selectați Fill with color (Umplere cu culoare) și alegeți una dintre culorile standard ale temei sau faceți clic pe More Colors… și alegeți orice culoare RGB sau HSL personalizată.

Click pe butonul Finish (Finalizare) și bucurați-vă de rezultat 🙂

Acesta este modul în care evidențiați duplicatele în Excel folosind add-in-ul nostru Duplicate Remover. Dacă sunteți curios să încercați acest instrument pe propriile foi de calcul, sunteți binevenit să descărcați o versiune de încercare complet funcțională a suitei Ultimate Suite care include toate instrumentele noastre de economisire a timpului pentru Excel. Iar feedback-ul dvs. în comentarii va fi foarte apreciat!

Dacă sunteți mulțumit de performanța Duplicate Remover, suntem bucuroși să vă oferim această oportunitate foarte specială:

S-ar putea să vă intereseze și

• Cum să identificați duplicatele în Excel (în 1 tabel)
• Cum să comparați 2 coloane sau tabele pentru potriviri și diferențe
• Cum să eliminați rândurile duplicate în Excel

.