Jak znaleźć duplikaty wartości w programie Microsoft Excel: 3 metody

Aby znaleźć duplikaty w programie Microsoft Excel, użyj formatowania warunkowego do ich wyróżnienia, formuły LICZ.JEŻELI do oznaczenia ich w kolumnie pomocniczej lub funkcji Usuń duplikaty , aby usunąć powtarzające się rekordy. Zacznij od wyróżnienia lub formuły, gdy chcesz sprawdzić dopasowania; użyj funkcji usuwania dopiero po ustaleniu, co jest duplikatem i utworzeniu kopii oryginalnych danych.

Poniższe przykłady wykorzystują listę w komórkach A2:A100 z nagłówkiem w wierszu 1. Powtarzająca się wartość w jednej kolumnie różni się od zduplikowanego rekordu: dwa wiersze mogą mieć ten sam adres e-mail, ale różne nazwy lub działy. Aby oczyścić tabelę, przed wybraniem metody zdecyduj, które kolumny definiują dopasowanie.

Wybierz właściwą metodę

• Podświetlanie komórek: szybkie sprawdzanie powtarzających się wartości bez zmiany danych.
• Użyj funkcji LICZ.JEŻELI: utwórz etykietę wielokrotnego użytku Duplikat lub Powtórz obok każdej wartości.
• Usuń duplikaty: usuwanie wierszy na podstawie wybranej kolumny lub kombinacji kolumn.

1. Zaznacz duplikaty za pomocą formatowania warunkowego

To najszybsza kontrola wizualna. Aby znaleźć powtarzające się adresy e-mail, zaznacz tylko komórki z adresami e-mail, takie jak A2:A100, a nie nagłówek. Wbudowana reguła porównuje zaznaczone wartości i może oznaczyć każde wystąpienie w zduplikowanej grupie.

1. Wybierz zakres, który chcesz sprawdzić.

   

2. Na karcie Narzędzia główne wybierz Formatowanie warunkowe > Reguły wyróżniania komórek > Duplikujące się wartości .

   

3. W oknie dialogowym pozostaw zaznaczoną opcję Duplikuj , wybierz format i kliknij OK .

   

Excel podświetla pasujące komórki, ale ich nie usuwa. W poniższym przykładzie zaznaczone jest zarówno pierwsze, jak i późniejsze wystąpienie każdego powtórzonego adresu e-mail; unikatowe adresy pozostają niesformatowane.

Aby wyróżnić wartości unikatowe, wybierz opcję Unikatowe w tym samym oknie dialogowym. Wbudowana reguła nie może formatować zduplikowanych wartości w obszarze Wartości raportu tabeli przestawnej; zamiast tego użyj standardowego zakresu lub sprawdzenia opartego na formule. Zapoznaj się z instrukcjami firmy Microsoft dotyczącymi wyszukiwania i usuwania duplikatów .

2. Zaznacz dopasowania za pomocą formuły LICZ.JEŻELI

Kolumna pomocnicza jest przydatna, gdy potrzebujesz etykiety, którą możesz filtrować, sortować lub przeglądać później. W komórce B2 wprowadź następującą formułę, a następnie naciśnij Enter:

=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""))

Zastąp A100 ostatnim wierszem na liście. Znaki dolara utrzymują przeszukiwany zakres w miarę wypełniania formuły, podczas gdy A2 zmienia się w A3, A4 itd. Zewnętrzny warunek JEŻELI pozostawia puste wiersze. Ponieważ formuła zlicza cały zakres, oznacza wszystkie wystąpienia powtarzającej się wartości, w tym jej pierwsze pojawienie się.

1. Po zaznaczeniu komórki B2 przeciągnij jej uchwyt wypełniania w dół przez wiersze, które chcesz sprawdzić, lub skopiuj i wklej formułę do pozostałej części kolumny B.

   

Jeśli chcesz oznaczyć tylko drugie i późniejsze wystąpienia, użyj zamiast tego zakresu rosnącego:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Repeat",""))

W przypadku duplikatu całego wiersza opartego na trzech polach w kolumnach A, B i C, umieść go w kolumnie pomocniczej i wypełnij:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1

Zwraca wartość TRUE dla każdego wiersza, w którym kombinacja A/B/C występuje więcej niż raz. Dodaj lub usuń pary kolumn, aby dopasować tabelę. Funkcja LICZ.JEŻELI obsługuje jedno kryterium; funkcja LICZ.WARUNKI obsługuje wiele kryteriów. W dokumentacji funkcji LICZ.JEŻELI firmy Microsoft zaznaczono również, że dopasowywanie tekstu nie uwzględnia wielkości liter, a dodatkowe spacje lub znaki niedrukowalne mogą wpływać na wyniki.

3. Usuń duplikaty wierszy za pomocą opcji Usuń duplikaty

Użyj tej opcji tylko wtedy, gdy jesteś gotowy do usunięcia rekordów. Najpierw skopiuj oryginalny zakres lub arkusz. Zaznacz całą tabelę, tak aby jej kolumny pozostały razem, a następnie wybierz Dane > Usuń duplikaty .

Jeśli tabela ma nagłówki kolumn, zaznacz opcję „ Moje dane mają nagłówki ”. W sekcji „Kolumny” wybierz pola definiujące duplikat, a następnie kliknij przycisk OK .

• Wybierz opcję „E-mail” , jeśli powtarzający się e-mail ma być liczony jako duplikat. Program Excel usunie cały kolejny wiersz, nawet jeśli nazwa lub dział się różnią.
• Zaznacz wszystkie kolumny, jeśli chcesz usunąć tylko wiersze, do których pasuje cały wybrany rekord.

Excel zachowuje pierwsze wystąpienie i usuwa kolejne wiersze pasujące do wybranego klucza. Usunięcie dotyczy zaznaczonego zakresu lub tabeli, w tym kolumn, które nie zostały zaznaczone w oknie dialogowym. Sprawdź liczbę usuniętych wierszy i natychmiast użyj kombinacji klawiszy Ctrl+Z, jeśli wynik jest niezgodny z oczekiwaniami. Firma Microsoft wyjaśnia to zachowanie w swoim przewodniku po filtrowaniu unikatowych wartości i usuwaniu duplikatów .

Jeśli program Excel pominie duplikat

Podobnie wyglądające wartości mogą zawierać spacje na początku lub na końcu, znaki niedrukowalne lub różne formaty bazowe. Wyczyść kopię danych za pomocą funkcji takich jak PRZYTNIJ/ODSTĘPUJ lub OCZYŚĆ, a następnie sprawdź ponownie. Funkcja LICZ.JEŻELI traktuje tekst pisany wielkimi i małymi literami tak samo, dlatego należy zastosować inne podejście do porównywania, jeśli wielkość liter ma odróżniać rekordy. Aby zapewnić bezpieczeństwo pierwszego podejścia, zaznacz lub oznacz dopasowania, przejrzyj je, a następnie usuń wiersze.