Znajdowanie duplikatów w Excelu: wyróżnianie, zliczanie i zestawianie
By Mirko Peters · · Updated
Znajdź duplikaty w Excelu za pomocą formatowania warunkowego, flag COUNTIF, funkcji UNIQUE, Power Query i sprawdzania między arkuszami, a także wychwyć niemal identyczne wpisy przed ich usunięciem.
Automatically translated with AI from the original post. Read the English original
Aby znaleźć duplikaty w Excelu bez usuwania czegokolwiek, zaznacz dane i wybierz Narzędzia główne > Formatowanie warunkowe > Reguły wyróżniania komórek > Wartości duplikatów. Excel pokoloruje każdą wartość, która występuje więcej niż raz. Jeśli wolisz liczby zamiast kolorów, dodaj pomocniczą kolumnę z formułą LICZ.JEŻELI. Ten artykuł jest przeznaczony dla użytkowników Excela, którzy dostają nieuporządkowane listy i muszą zobaczyć problem, zanim go naprawią. Otrzymasz metodę wyróżniania, formuły zliczające i oznaczające, sposób na wyświetlenie wyłącznie powtarzających się wartości, opcję Power Query, sprawdzanie między arkuszami oraz plan postępowania z niemal identycznymi wpisami.
Najważniejsze wnioski
- Najszybszy sposób na znalezienie duplikatów w Excelu to: Narzędzia główne, Formatowanie warunkowe, Reguły wyróżniania komórek, Wartości duplikatów. Koloruje ono powtarzające się wartości i niczego nie usuwa.
- Pomocnicza kolumna z LICZ.JEŻELI pokazuje, ile razy występuje każda wartość, a dzięki LICZ.WARUNKI można sprawdzić kilka kolumn jednocześnie.
- Funkcja LICZ.JEŻELI nie rozróżnia wielkości liter i traktuje znaki ? oraz * jako symbole wieloznaczne, dlatego wynik może różnić się od oczekiwanego.
- Aby porównać dwa arkusze, zlicz każdą wartość względem drugiej listy za pomocą LICZ.JEŻELI, a jeśli druga lista znajduje się w innym pliku, otwórz oba skoroszyty.
- Niemal identyczne wpisy, takie jak Jon i John, nie zostaną uznane za duplikaty. Najpierw usuń zbędne spacje i ujednolić wielkość liter, a resztę przejrzyj ręcznie lub za pomocą dodatku Fuzzy Lookup.
Najpierw szukanie, dopiero potem usuwanie. Jeśli wiesz już, które wiersze chcesz usunąć, nasz towarzyszący przewodnik o tym, jak usuwać duplikaty w Excelu, omawia ten krok. Tutaj chodzi o to, by najpierw sprawdzić dane w Excelu, tak abyś sam zdecydował, co uznajesz za duplikat, a co zachować.
Wyróżnianie duplikatów za pomocą formatowania warunkowego
To najszybsza kontrola, a Microsoft zaleca ją jako etap przeglądu przed usunięciem czegokolwiek. Opiera się na artykule Microsoft dotyczącym filtrowania unikatowych wartości i usuwania zduplikowanych wartości.
- Zaznacz komórki, kolumnę lub zakres, który chcesz sprawdzić. Zwykle wystarczy jedna kolumna.
- Na karcie Narzędzia główne otwórz Formatowanie warunkowe.
- Wybierz Reguły wyróżniania komórek, a następnie Wartości duplikatów.
- Wybierz format, na przykład jasnoczerwone wypełnienie, i kliknij OK.
Każda komórka, której wartość występuje więcej niż raz, jest teraz pokolorowana. Przewiń arkusz lub przefiltruj kolumnę według koloru, aby zobaczyć tylko wyróżnione komórki. Aby cofnąć zmianę, przejdź do Formatowania warunkowego i wyczyść reguły z zakresu.
Istnieje też drugi sposób, który daje większą kontrolę. Otwórz Formatowanie warunkowe > Zarządzaj regułami, utwórz Nową regułę i wybierz Formatuj tylko unikatowe lub zduplikowane wartości. Na liście Formatuj wszystkie możesz wybrać unikatowe lub zduplikowane, a także ustawić format liczby, czcionkę, obramowanie lub wypełnienie. Jest to przydatne, gdy chcesz wyróżnić wartości występujące tylko raz i pozostawić powtarzające się bez zmian.
Są dwa ograniczenia, o których warto wiedzieć. Microsoft informuje, że Excel nie potrafi wyróżnić duplikatów w obszarze Wartości tabeli przestawnej. Ponadto reguła sprawdza jedną kolumnę naraz, gdy zaznaczysz tylko jedną kolumnę. Wiersz, który powtarza się dopiero wtedy, gdy weźmiesz pod uwagę kilka kolumn łącznie, nie zostanie wyróżniony. Do tego potrzebna jest formuła.
Zliczaj i oznaczaj duplikaty za pomocą funkcji COUNTIF
Kolumna pomocnicza daje liczbę, według której możesz sortować i filtrować. Jest to także właściwe narzędzie, gdy dwa wiersze są duplikatami tylko wtedy, gdy zgadzają się dwie lub trzy kolumny.
Oznacz każde powtórzenie
Załóżmy, że wartości znajdują się w zakresie A2:A100. W komórce B2 wpisz:
=COUNTIF($A$2:$A$100,A2)
Przeciągnij formułę w dół. Wynik 1 oznacza, że wartość jest unikalna. Wynik 2 oznacza, że występuje dwukrotnie, i tak dalej. Każda kopia powtarzającej się wartości otrzymuje tę samą liczbę, łącznie z pierwszą. Aby zamienić liczbę na PRAWDA lub FAŁSZ, dodaj porównanie: =COUNTIF($A$2:$A$100,A2)>1.
Oznacz tylko kolejne kopie
Jeśli planujesz usuwać wiersze, zwykle chcesz zachować pierwszy z nich. Zamiast tego użyj rozszerzającego się zakresu:
=COUNTIF(A$2:A2,A2)>1
Zakres A$2:A2 rośnie w miarę kopiowania formuły w dół. Przy pierwszym wystąpieniu wartości zobaczysz FAŁSZ. Każde kolejne wystąpienie da PRAWDA. Działa to tak samo jak polecenie Usuń duplikaty w Excelu, które również zachowuje pierwsze wystąpienie.
Sprawdzaj kilka kolumn za pomocą COUNTIFS
Dla rekordu, który jest duplikatem tylko wtedy, gdy zgadzają się na przykład imię i nazwisko, użyj funkcji COUNTIFS z jedną parą zakres–kryterium dla każdej kolumny:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)
Wynik większy niż 1 oznacza, że dana kombinacja występuje więcej niż raz. Aby uwzględnić więcej kolumn, dodaj kolejne pary. Nie próbuj budować tego samego sprawdzenia przez łączenie zakresów wewnątrz COUNTIF, jak w COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF oczekuje jako pierwszego argumentu prawdziwego zakresu, więc ta formuła nie działa tak, jak wygląda. Czystym rozwiązaniem jest COUNTIFS.
Zachowania COUNTIF, które zaskakują
Na stronie funkcji COUNTIF firma Microsoft opisuje kilka zachowań istotnych przy szukaniu duplikatów:
- Dopasowywanie tekstu nie uwzględnia wielkości liter. „apples” i „APPLES” są traktowane jako ta sama wartość. Excel uzna je tutaj za duplikaty.
- Znaki
?i*działają jako symbole wieloznaczne. Jeśli Twoje dane je zawierają, na przykład w kodach produktów, COUNTIF może dopasować więcej, niż oczekujesz. Aby wyszukać taki znak dosłownie, poprzedź go znakiem tyldy (~). - COUNTIF zwraca nieprawidłowe wyniki dla ciągów znaków dłuższych niż 255 znaków.
- Spacje na początku lub na końcu, mieszane cudzysłowy proste i typograficzne oraz znaki niedrukowalne mogą zafałszować wynik zliczania. Microsoft sugeruje, aby najpierw oczyścić dane za pomocą funkcji TRIM i CLEAN.
Właśnie ten ostatni punkt stoi za większością skarg w stylu „Excel nie widzi moich duplikatów”. Dwie komórki, które wyglądają identycznie, mogą różnić się spacją na końcu. Najpierw oczyść dane, dopiero potem licz.
Filtrowanie i przegląd
Gdy kolumna pomocnicza jest gotowa, przefiltruj ją pod kątem PRAWDA lub wartości większych niż 1 i przejrzyj wiersze. Możesz posortować według kolumny pomocniczej, aby zgrupować wszystkie powtarzające się wiersze obok siebie. Zanim cokolwiek usuniesz, sprawdź próbkę. Często okazuje się, że „duplikat” to dwa prawdziwe rekordy o tej samej nazwie.
Wyświetl tylko zduplikowane wartości
Czasami potrzebujesz krótkiej listy tego, co się powtarza, a nie flagi przy każdym wierszu.
W programie Excel dla Microsoft 365, 2024 i 2021 zadanie może wykonać funkcja UNIQUE. Według strony funkcji UNIQUE firmy Microsoft =UNIQUE(array,[by_col],[exactly_once]) zwraca unikatowe wartości z zakresu, a ustawienie exactly_once na TRUE zwraca tylko wartości występujące dokładnie raz. Zatem =UNIQUE(A2:A100) podaje każdą wartość jeden raz, a =UNIQUE(A2:A100,,TRUE) zwraca wartości, które w ogóle nie są zduplikowane. Aby uzyskać wyłącznie powtarzające się wartości, połącz UNIQUE z FILTER i COUNTIF:
=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))
Ta formuła zachowuje wiersze, dla których liczba wystąpień jest większa niż 1, i pokazuje każdy z nich raz. Wymaga wersji Excela obsługującej tablice dynamiczne. Zanim zaufasz jej przy dużej liście, przetestuj ją na kilku wierszach.
W starszych wersjach użyj kolumny pomocniczej. Wpisz =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") i przeciągnij w dół. Kolumna pokaże teraz każdą powtarzającą się wartość, a pozostałe komórki pozostaną puste. Odfiltruj puste komórki. Każda powtarzająca się wartość pojawi się tyle razy, ile występuje, co wystarcza do przeglądu.
Zaawansowany filtr pozwala skopiować w inne miejsce tylko unikatowe rekordy. Przejdź do Dane > Zaawansowane, wybierz Kopiuj do innej lokalizacji, wpisz komórkę w polu Kopiuj do i zaznacz Tylko unikatowe rekordy. Porównanie tej listy z oryginałem pokazuje, co się powtarzało.
Użyj Power Query, aby zachować tylko duplikaty
Jeśli dane są duże albo napływają co tydzień, pozwól, aby Power Query znalazł duplikaty. Dokumentacja Microsoft dotycząca Power Query opisuje polecenie Zachowaj duplikaty, czyli przeciwieństwo polecenia Usuń duplikaty. Filtruje ono tabelę do wierszy, których wartości w wybranych kolumnach występują więcej niż raz, a w przeciwieństwie do polecenia Usuń duplikaty zwraca wszystkie pasujące wiersze, a nie po jednym na wartość.
Artykuł Microsoft o zachowywaniu lub usuwaniu zduplikowanych wierszy opisuje kroki dotyczące usuwania. Początek jest taki sam dla obu操作:
- Zaznacz komórkę w danych i wybierz Kwerenda > Edytuj albo załaduj je poleceniem Dane > Z tabeli/zakresu.
- Kliknij nagłówek kolumny, aby ją zaznaczyć. Przytrzymaj klawisz Shift lub Ctrl, aby zaznaczyć więcej kolumn.
- Zastosuj polecenie. Kroki Microsoft dotyczące usuwania używają ścieżki Narzędzia główne > Usuń wiersze > Usuń duplikaty. Polecenie Zachowaj duplikaty znajdziesz obok pozostałych poleceń dotyczących wierszy na karcie Narzędzia główne; sprawdź podgląd.
Microsoft zaznacza też, że Power Query rozróżnia wielkość liter, więc „Apple” i „apple” są tam różnymi wartościami, w przeciwieństwie do funkcji COUNTIF. To rzeczywista różnica między obiema metodami. Jeśli celem jest powtarzalna cotygodniowa kontrola, warto ustalić jedno zachowanie i trzymać się go, na przykład najpierw zamieniając kolumnę na wielkie litery.
Znajdź duplikaty między dwoma arkuszami lub listami
Częstym zadaniem jest sprawdzenie, czy lista z jednego arkusza występuje także w innym: nowa lista klientów ze starą albo zamówienia z tego miesiąca z zamówieniami z poprzedniego.
- Umieść obie listy na osobnych arkuszach, na przykład Arkusz1 i Arkusz2, z wartościami w kolumnie A.
- W Arkuszu1, w komórce B2, wpisz
=COUNTIF(Arkusz2!A:A;A2)>0. - Skopiuj formułę w dół. Wartość PRAWDA oznacza, że wartość występuje również w Arkuszu2.
- Filtruj po PRAWDA, aby zobaczyć wspólne wartości, albo po FAŁSZ, aby zobaczyć nowe.
W przypadku dwóch kolumn, które muszą pasować razem, użyj funkcji COUNTIFS z parą dla każdej kolumny i zakresami z drugiego arkusza.
Jeśli druga lista znajduje się w innym skoroszycie, Microsoft ostrzega, że formuła odwołująca się do zamkniętego skoroszytu może zwrócić błąd #ARG!. Zachowaj otwarte oba skoroszyty podczas pracy albo przenieś drugą listę do pierwszego skoroszytu za pomocą Power Query. Po kontroli pamiętaj, że wynik zależy od danych w danym momencie. Jeśli którakolwiek lista się zmieni, zmienią się także oznaczenia.
Podobne wartości: Jon Smith i John Smith
Wszystko powyżej znajduje wyłącznie dokładne dopasowania, nie licząc wielkości liter. Rzeczywiste listy zawierają też literówki i różną pisownię. Excel nie ma wbudowanej funkcji, która oceniałaby podobieństwo dwóch tekstów, dlatego radzimy sobie z tym w dwóch krokach.
Krok pierwszy: usuń fałszywe różnice. Użyj funkcji USUŃ.ZBĘDNE.ODSTĘPY, aby usunąć nadmiarowe spacje, oraz funkcji NIEDRUKOWANE, aby usunąć znaki niedrukowalne, i oprzyj kontrolę na oczyszczonych wartościach. W kolumnie pomocniczej =USUŃ.ZBĘDNE.ODSTĘPY(A2) pokazuje oczyszczony tekst. Zliczaj duplikaty w tej kolumnie, a nie w oryginale. Ponieważ COUNTIF ignoruje wielkość liter, nie trzeba jej tu zmieniać. W Power Query, gdzie wielkość liter ma znaczenie, zamień kolumnę na wielkie lub małe litery.
Krok drugi: sprawdź, co zostało. Posortuj oczyszczoną listę alfabetycznie i przeglądaj sąsiednie wiersze. Bliskie dopasowania, na przykład „Jon Smith” i „John Smith”, trafiają obok siebie, dzięki czemu człowiek może je zauważyć. Przy większych listach Microsoft Research opublikował dodatek Fuzzy Lookup add-in for Excel. Potrafi znaleźć rozmyte duplikaty wierszy w obrębie jednej tabeli albo dopasować podobne wiersze między dwiema tabelami, a każdemu dopasowaniu przypisuje wynik podobieństwa. Zanim zaplanujesz pracę wokół tego narzędzia, sprawdź wymagania: strona pobierania wskazuje system Windows oraz Excel 2007, 2010, 2013 i 2016, a także wydanie z 2020 roku. Dodatek może nie zainstalować się w aktualnej konfiguracji Microsoft 365 i nie jest dostępny na komputerach Mac.
Niezależnie od użytego narzędzia wyniki dopasowań rozmytych sprawdzaj ręcznie. Dwie osoby o podobnych nazwiskach nie muszą być tą samą osobą.
Częste błędy
- Sprawdzanie tylko jednej kolumny, gdy duplikat jest określony przez dwie. Samo powtarzające się nazwisko nie oznacza zduplikowanego rekordu.
- Ufanie wynikom COUNTIF na danych zawierających niepotrzebne spacje lub nietypowe znaki. Najpierw oczyść dane funkcjami TRIM i CLEAN.
- Zapominanie, że COUNTIF ignoruje wielkość liter, a Power Query ją uwzględnia, przez co obie metody zwracają różne wyniki.
- Używanie
COUNTIF($A$2:$A$100,A2)>1i usuwanie wszystkich wierszy oznaczonych wartością TRUE. Taka formuła oznacza również pierwszą kopię, więc usuniesz wszystkie egzemplarze. Użyj rozszerzającego się zakresu, aby oznaczyć tylko kolejne wystąpienia. - Przeszukiwanie całych kolumn pod kątem ciągów znaków dłuższych niż 255 znaków, przy których COUNTIF zwraca błędne wyniki.
- Usuwanie od razu po podświetleniu. Najpierw skopiuj arkusz, ponieważ według Microsoftu usunięte duplikaty są trwale kasowane.
Powiązane tematy do zgłębienia
- Usuń znalezione duplikaty. Polecenie Usuń duplikaty na karcie Dane zachowuje pierwsze wystąpienie, a resztę usuwa. Najpierw posortuj dane, aby kontrolować, który wiersz pozostanie.
- Zachowaj najlepszy rekord, a nie pierwszy. Posortuj według daty lub wartości, tak aby wiersz, który chcesz zachować, znalazł się na początku, a następnie oznacz kolejne kopie rozszerzającą się funkcją COUNTIF.
- Porównuj listy między plikami. Power Query potrafi połączyć kilka skoroszytów w jedną tabelę, dzięki czemu możesz sprawdzić je razem.
- Zapobiegaj powstawaniu nowych duplikatów. Sprawdzanie poprawności danych z niestandardową formułą może zablokować wartość, która już istnieje. Wypróbuj to na arkuszu testowym.
- Prowadź ślad audytowy. Pozostaw kolumnę pomocniczą w kopii arkusza, abyś później mógł pokazać, co zostało oznaczone i dlaczego.
Następny krok
Wybierz jedną kolumnę w swoim bieżącym pliku, podświetl jej duplikaty za pomocą formatowania warunkowego i policz, ile komórek się wyróżnia. Następnie dodaj kolumnę pomocniczą z funkcją COUNTIF i porównaj wyniki. Jeśli się różnią, właśnie znalazłeś ukryte spacje lub różnice w wielkości liter, a także wiesz już, jak znajdować duplikaty w Excelu, zanim cokolwiek usuniesz.
Źródła
- Filtrowanie unikatowych wartości lub usuwanie zduplikowanych wartości (Microsoft Support)
- Funkcja COUNTIF (Microsoft Support)
- Zachowywanie lub usuwanie zduplikowanych wierszy (Power Query) (Microsoft Support)
- Praca z wartościami zduplikowanymi (Microsoft Learn)
- Dodatek Fuzzy Lookup dla programu Excel (Microsoft Download Center)