Cum să compari două foi Excel pentru duplicate: 5 moduri rapide

Când lucrați cu seturi de date mari în Excel, este obișnuit să aveți mai multe foi de lucru care conțin date similare. Compararea acestor foi pentru înregistrări duplicate poate fi o provocare, mai ales atunci când datele sunt împrăștiate pe diferite coloane și rânduri.

Cele mai comune metode de a găsi duplicate în două foi Excel sunt să utilizați:

1. Funcțiile CĂUTARE V, COUNTIF sau EXACT

2. Formatarea condițională

3. Power Query

4. Instrumente externe și suplimente

5. Verificări vizuale pentru duplicate

Acest articol parcurge pas cu pas aceste cinci metode pentru a identifica și gestiona duplicatele în mai multe foi de lucru. Până la sfârșit, veți vedea cât de ușor este să gestionați valorile duplicate.

Să începem!

Cuprins

• Exemple de fișe de lucru și date
• 1. Folosind funcțiile VLOOKUP, COUNTIF sau EXACT pentru a găsi duplicate
  • A. Cum folosiți funcția VLOOKUP pentru a găsi duplicate în două foi?
    • Ce se întâmplă dacă manipulați diferite registre de lucru?
  • B. Cum utilizați funcția COUNTIF pentru a găsi duplicate în foile de lucru?
  • C. Cum utilizați funcția EXACT pentru a găsi duplicate în foile de lucru?
• 2. Cum să utilizați formatarea condiționată pentru rândurile duplicate
  • Cum să utilizați Managerul de reguli de formatare condiționată
• 3. Cum să utilizați Power Query pentru a găsi duplicate în foile de lucru
• 4. Instrumente și suplimente pentru a identifica duplicatele în foile de lucru
• 5. Cum să verificați vizual dacă există duplicate în două foi
• 3 sfaturi pentru pregătirea foilor de lucru Excel
• Cum să gestionați erorile și inconsecvențele
• Gânduri finale

Exemple de fișe de lucru și date

Pentru a urma exemplele din acest articol, creați un nou registru de lucru Excel cu aceste date în coloana A a primei foi:

• Măr

• Portocale

• Pară

• Căpșună

Pune aceste date în coloana A a celei de-a doua foi:

• Pară

• Căpșună

• Măr

• Ananas

Fișele dvs. de lucru arată astfel:

Odată ce ați configurat foile de lucru, acum putem trece peste diferitele modalități de a găsi duplicate în două foi, începând cu funcțiile CĂUTARE V, COUNTIF și EXACT.

Să mergem!

1. Folosind funcțiile VLOOKUP, COUNTIF sau EXACT pentru a găsi duplicate

Excel are trei funcții încorporate care pot facilita găsirea duplicatelor: CĂUTARE V , COUNTIF și EXACT .

Aceste funcții sunt concepute pentru a vă ajuta să găsiți, să numărați și să comparați datele din foile de calcul, făcându-le instrumente ideale pentru a găsi intrări duplicate.

Fiecare funcție are un scop unic, iar înțelegerea modului de utilizare a acestora vă poate simplifica în mod semnificativ procesul de analiză a datelor.

În secțiunile următoare, vom explora modul de utilizare a funcțiilor CĂUTARE V, COUNTIF și EXACT pentru a identifica efectiv duplicatele în foile de lucru Excel.

A. Cum folosiți funcția VLOOKUP pentru a găsi duplicate în două foi?

VLOOKUP înseamnă Căutare verticală. Utilizați această funcție pentru a găsi valori duplicate între două coloane. Aceasta este sintaxa:

=CĂUTAREV(valoare_căutare, matrice_tabelă, număr_index_col, [căutare_gamă])

• lookup_value : valoarea pe care doriți să o căutați în prima coloană a table_array.

• table_array : intervalul de celule care conțin datele în care doriți să căutați.

• col_index_num : numărul coloanei din table_array din care doriți să returnați valoarea.

• range_lookup : Opțional. Este fie TRUE (potrivire aproximativă) fie FALSE (potrivire exactă). Implicit este TRUE.

Pentru a utiliza funcția pe două foi de lucru dintr-un fișier Excel, trebuie să știți cum să faceți referire la o foaie separată într-o formulă. Pentru a face acest lucru, tastați numele foii urmat de semnul exclamării (!) și celula sau intervalul de celule.

De exemplu, aceasta face referire la celulele A2 la A5 din foaia 2 a aceluiași registru de lucru: Sheet 2!$A$2:$A$5.

Pentru a utiliza funcția CĂUTARE V din foaia de calcul eșantion, urmați acești pași:

1. Selectați celula B2 pentru a afișa primul rezultat al comparației.

2. Tastați această formulă: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Apăsați Enter pentru a afișa rezultatul comparației.

4. Completați formula pentru a compara valorile pentru restul rândurilor din prima foaie.

Rezultatele vor arăta ca în imaginea de mai jos:

Puteți aranja afișajul afișând un mesaj ușor de utilizat în loc de o eroare atunci când un duplicat nu este găsit.

De exemplu, această formulă va afișa „Da” sau „Nu” pentru valorile găsite și, respectiv, negăsit:

=IF(ISNA(CĂUTAREV(A2, Foaia 2!$A$2:$A$5, 1, FALSE)), „Nu”, „Da”)

Există mai multe exemple ale acestei funcții în articolul nostru despre utilizarea VLOOKUP pentru a compara două coloane .

Ce se întâmplă dacă manipulați diferite registre de lucru?

Dacă foile de lucru sunt în registre de lucru separate, utilizarea funcției este aceeași cu două fișiere Excel. Cu toate acestea, referirea celei de-a doua foaie de lucru este puțin mai complexă.

Trebuie să:

• Introduceți numele registrului de lucru Excel între paranteze

• Urmează cu numele foii de lucru

• Puneți ghilimelele registrul de lucru și foaia de lucru.

De exemplu, dacă celulele sunt într-o foaie numită Sheet2 într-un registru de lucru numit „WB 2.xlsx”, formatul va arăta astfel:

„[WB 2.xlsx]Sheet2”!$A$2:$A$5

Înainte de a introduce formula, închideți al doilea registru de lucru. În caz contrar, veți primi o eroare.

B. Cum utilizați funcția COUNTIF pentru a găsi duplicate în foile de lucru?

Funcția COUNTIF din Excel este utilizată pentru a număra numărul de celule dintr-un interval specificat care îndeplinesc un anumit criteriu.

Pentru a compara mai multe foi, puteți număra numărul de celule din a doua foaie de lucru care se potrivește cu o celulă din prima foaie de lucru.

Aceasta este sintaxa funcției:

=COUNTIF(interval, criterii)

• Interval : intervalul de celule pe care doriți să le numărați pe baza criteriilor specificate.

• Criterii : condiția care trebuie îndeplinită pentru ca o celulă să fie numărată.

Pentru a utiliza funcția cu datele eșantion, urmați acești pași:

1. Selectați celula B2 pentru a afișa primul rezultat al comparației.

2. Introdu această formulă: =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. Apăsați Enter pentru a afișa rezultatul comparației.

4. Completați formula pentru a compara valorile pentru restul rândurilor din prima foaie.

Funcția va găsi o potrivire pentru unele celule și niciuna pentru altele. Celula de comparație afișează numărul. Iată rezultatul din eșantionul de date:

Funcția COUNTIF poate fi utilizată pentru alte sarcini utile, cum ar fi numărarea celulelor care nu sunt goale în Excel .

C. Cum utilizați funcția EXACT pentru a găsi duplicate în foile de lucru?

Funcția EXACT din Excel poate fi folosită și pentru a căuta duplicate în aceleași celule în două foi de lucru Excel diferite. Sintaxa este:

=EXACT(text1, text2)

• text1 este primul șir de text pe care doriți să îl comparați.

• text2 este al doilea șir de text pe care doriți să îl comparați.

Urmați acești pași:

1. Selectați celula B2.

2. Introduceți formula =EXACT(A2, Sheet2!A2)

3. Apăsați Enter pentru a afișa rezultatul comparației. Formula va returna TRUE dacă ambele valori sunt identice sau FALSE în caz contrar.

4. Completați formula pentru a compara valorile pentru restul rândurilor din prima foaie.

Rețineți că această metodă nu caută duplicate într-un interval de celule. În schimb, căutați doar potriviri bazate pe aceeași celulă într-o foaie diferită.

Acest lucru poate fi util cu datele ordonate unde vă așteptați doar la câteva excepții.

Funcțiile LOOKUP, COUNTIF și EXACT sunt utile pentru a găsi duplicate, dar Excel este un program versatil și există și alte opțiuni. În secțiunea următoare, ne uităm la modul în care puteți utiliza formatarea condiționată pentru a identifica duplicatele în două foi.

2. Cum să utilizați formatarea condiționată pentru rândurile duplicate

În această secțiune, veți învăța cum să utilizați formatarea condiționată pentru a găsi și a evidenția rândurile duplicate în două foi de lucru Excel.

Pentru a crea o regulă de formatare condiționată, urmați acești pași:

1. Selectați intervalul de celule care conține datele (A2:A5 în acest caz).

2. Faceți clic pe fila „Acasă” din panglica Excel.

3. Faceți clic pe „Formatare condiționată” în grupul „Stiluri”.

4. Alegeți „Regulă nouă” din meniul derulant.

Următoarea sarcină este să oferiți o formulă pe care să o utilizați regula. Urmați acești pași:

1. Alegeți „Utilizați o formulă pentru a determina ce celule să formatați” în caseta de dialog

2. Introduceți următoarea formulă: =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

În cele din urmă, aplicați formatarea pe care o preferați pentru celulele duplicate.

1. Faceți clic pe butonul Format pentru a deschide caseta de dialog Formatare celule.

2. Alegeți un format, de exemplu, umpleți duplicatele cu o culoare de fundal galbenă.

3. Faceți clic pe OK.

Datele dvs. duplicate sunt acum evidențiate cu galben.

Cum să utilizați Managerul de reguli de formatare condiționată

După ce ați creat regula de formatare condiționată, o puteți gestiona folosind Managerul de reguli de formatare condiționată.

Pentru a accesa managerul:

1. Accesați fila Acasă.

2. Faceți clic pe Formatare condiționată.

3. Alegeți „Gestionați regulile”.

Veți vedea o listă cu toate regulile de formatare condiționată aplicate foii selectate. Puteți edita, șterge sau modifica ordinea regulilor selectând regula și făcând clic pe butoanele corespunzătoare.

Pentru a aplica aceeași regulă pe cealaltă foaie, urmați acești pași:

1. Selectați intervalul pe care doriți să îl comparați în a doua foaie.

2. Accesați Managerul regulilor de formatare condiționată.

3. Selectați regula, faceți clic pe Duplicate Rule și apoi apăsați Edit Rule.

4. Înlocuiți „Sheet2” cu numele primei foi de comparat.

Acum că ați aplicat regula de formatare condiționată ambelor foi, duplicatele vor fi evidențiate în funcție de formatarea pe care ați ales-o.

Asigurați-vă că ajustați intervalul și referințele de celule din formule după cum este necesar pentru a acoperi toate datele pe care doriți să le comparați.

Formatarea condiționată ar putea părea puțin primitivă. Dacă doriți un control mai fin, atunci Power Query poate fi răspunsul! În secțiunea următoare, vom acoperi cum puteți utiliza Power Query pentru a găsi duplicate.

3. Cum să utilizați Power Query pentru a găsi duplicate în foile de lucru

Power Query este un instrument de transformare și pregătire a datelor în Microsoft Excel. Identificarea acelorași valori este doar una dintre numeroasele sarcini de analiză pe care le puteți efectua cu instrumentul.

Pentru a face acest lucru, ar trebui mai întâi să importați datele din cele două foi de lucru în tabele separate. Urmați acești pași în fiecare foaie:

1. Faceți clic dreapta pe intervalul de celule.

2. Alegeți „Obține date din tabel/gamă”.

3. Modificați numele tabelului cu ceva adecvat.

După importarea ambelor foi, prima sarcină este să îmbinați datele:

1. Accesați fila Date.

2. Faceți clic pe „Obțineți date”.

3. Selectați „Combinați interogări”.

4. Alegeți „Merge” și selectați cele două tabele.

5. Faceți clic pe cele două coloane cheie.

6. Alegeți „Inner” ca „Join Kind” și faceți clic pe OK.

Editorul Power Query se va deschide cu datele combinate din ambele tabele din foaia dvs. Excel. Veți vedea două coloane, câte una din fiecare tabel. Deoarece sunteți interesat doar de valorile duplicate, puteți elimina a doua coloană.

Puteți face clic pe „Închidere și încărcare” în Editorul Power Query pentru a încărca duplicatele într-o nouă foaie de lucru.

Pentru a explora mai multe aspecte ale acestei funcții puternice, urmați exemplele din articolul nostru despre cum să utilizați Power Query în Excel .

Excel are, de asemenea, instrumente și programe de completare terță parte care adaugă capacitatea de a găsi fără probleme duplicate, așa că haideți să aruncăm o privire la unele dintre aceste instrumente în secțiunea următoare.

4. Instrumente și suplimente pentru a identifica duplicatele în foile de lucru

Instrumentele externe și suplimentele pot oferi funcționalități avansate care pot să nu fie disponibile în funcțiile native Excel. Aceste instrumente pot simplifica și mai mult procesul de comparare a foilor pentru duplicate.

Spreadsheet Compare este un instrument Microsoft care vă permite să comparați două registre de lucru unul lângă altul, evidențiind diferențele și identificând cu ușurință duplicatele. Îl puteți descărca de pe site-ul Microsoft.

Există mai multe programe de completare pe care le puteți instala pentru a automatiza procesul de găsire a duplicaturilor. Un exemplu este „ Duplicate Remover ”. Pentru a instala un add-in:

1. Accesați fila Inserare.

2. Faceți clic pe „Obțineți add-in”.

3. Căutați „Duplicat”.

4. Faceți clic pe „Adăugați” pe instrumentul dorit.

5. Cum să verificați vizual dacă există duplicate în două foi

Dacă totul eșuează, folosește-ți ochii! Caseta de dialog Aranjare Windows din Excel vă permite să vizualizați mai multe foi de lucru sau registre de lucru una lângă alta.

Deși nu găsește în mod direct duplicatele, vă poate ajuta să comparați vizual datele din foi de lucru sau registre de lucru pentru a identifica duplicatele. Urmați acești pași:

1. Faceți clic pe fila „Vizualizare” din panglica Excel.

2. Faceți clic pe „Aranjați toate” în grupul „Fereastră”.

3. Alegeți o opțiune de aranjare, de exemplu „Vertical” sau „Orizontal”.

Aceasta va afișa ambele foi una lângă alta sau una deasupra celeilalte. Acum puteți compara manual datele din fiecare foaie pentru a identifica duplicatele.

Trebuie să parcurgeți datele și să inspectați vizual fiecare valoare pentru a găsi potriviri.

Rețineți că această metodă nu este eficientă pentru seturi mari de date, deoarece necesită o comparație manuală. Utilizarea celorlalte metode din acest articol va fi mai eficientă pentru a găsi duplicate în seturi de date mai mari.

Și aceasta este ultima dintre metodele noastre comune pentru a găsi valori duplicate în foile Excel! În secțiunea următoare, vă vom oferi câteva sfaturi pentru pregătirea foilor de lucru.

3 sfaturi pentru pregătirea foilor de lucru Excel

Înainte de a începe să comparați mai multe foi, asigurați-vă că aveți coloanele și rândurile setului de date aliniate corect.

Asigurați-vă că ambele foi Excel au aceeași structură și aceleași nume de antet. Dacă este necesar, puteți rearanja coloanele din ambele foi pentru a se potrivi.

Iată trei sugestii pentru a asigura comparații precise:

1. Aranjați datele în aceeași ordine în ambele foi. Acest lucru facilitează funcționarea eficientă a funcțiilor Excel.

2. Normalizați-vă datele utilizând formatarea, scrierea cu majuscule și tipuri de date consecvente. Acest lucru va preveni intrările nepotrivite din cauza diferențelor minore.

3. Eliminați rândurile sau coloanele goale inutile, deoarece acestea pot interfera cu procesul de comparare.

Puteți evalua cât de multă duplicare aveți într-un set de date numărând valorile unice. Acest videoclip prezintă mai multe metode:

Cum să gestionați erorile și inconsecvențele

Inconsecvențele în datele dvs. pot afecta procesul de comparare. Iată patru sfaturi pentru a rezolva neconcordanțe:

1. Verificați dacă există discrepanțe în tipurile de date, cum ar fi amestecarea textului și a valorilor numerice în aceeași coloană.

2. Asigurați-vă că este utilizată formatare consecventă pentru date, numere și alte tipuri de date.

3. Examinați-vă datele pentru intrări lipsă sau incorecte și actualizați dacă este necesar.

4. Standardizați abrevierile sau convențiile de denumire inconsecvente în seturile dvs. de date.

Gânduri finale

Găsirea duplicatelor în două foi de lucru Excel este o sarcină esențială pentru gestionarea și analiza datelor, asigurând integritatea și acuratețea datelor. Excel oferă mai multe tehnici de identificare a duplicatelor, fiecare cu propriile avantaje și limitări.

Alegerea metodei depinde de nevoile utilizatorului, de dimensiunea și complexitatea setului de date și de rezultatul dorit. Pentru seturi de date mai mici și comparații simple, folosirea CĂUTARE V , COUNTIF sau formatare condiționată poate fi suficientă.

Pentru seturi de date mai mari sau transformări de date mai complexe, Power Query este un instrument puternic și flexibil care poate gestiona o gamă largă de sarcini de pregătire a datelor, inclusiv găsirea de duplicate.

Pentru a finaliza, compararea foilor Excel pentru duplicate este o abilitate foarte utilă pe care o aveți în cutia de instrumente. Cu trucurile din acest articol, puteți identifica acele duplicate plictisitoare și vă puteți păstra datele curate.

Aveți încredere în noi, pe măsură ce vă îmbunătățiți acest lucru, veți trece rapid prin sarcinile de date și veți impresiona pe toți cei din jurul vostru!