Jak usunąć duplikaty w Excelu: polecenie, formuły, Power Query
By Mirko Peters · · Updated
Jak usunąć duplikaty w Excelu: użyj karty Dane, polecenia Usuń duplikaty albo zachowaj oryginalne dane dzięki funkcji UNIQUE, formułom i Power Query. Kroki, ograniczenia i typowe błędy.
Automatically translated with AI from the original post. Read the English original
Aby usunąć duplikaty w Excelu, zaznacz dane, przejdź do Dane > Usuń duplikaty, zaznacz kolumny, które decydują o tym, co uznajemy za duplikat, i wybierz OK. Excel usuwa każdy wiersz, który powtarza wartości w tych kolumnach, i zachowuje pierwszy z nich. Ten artykuł jest przeznaczony dla użytkowników Excela, którzy oczyszczają listy, eksporty i raporty i chcą wybrać odpowiednią metodę. Na początku znajdziesz samo polecenie, następnie sposoby sprawdzania duplikatów przed ich usunięciem, potem opcje zachowujące dane źródłowe, a na końcu kilka rozwiązań automatyzujących powtarzalne zadania.
Najważniejsze wnioski
- Zaznacz dane, otwórz kartę Dane, następnie wybierz Usuń duplikaty, zaznacz kolumny definiujące duplikat i wybierz OK. Excel usuwa cały wiersz i zachowuje pierwsze dopasowanie.
- Usunięcie jest trwałe, dlatego najpierw skopiuj dane do innego arkusza. Ctrl+Z działa tylko do momentu wykonania kolejnej czynności.
- Excel porównuje to, co jest wyświetlane w komórce, więc 3/8/2006 i 8 mar 2006 traktowane są jako różne wartości. Najpierw uporządkuj formaty.
- Jeśli chcesz przejrzeć duplikaty przed ich usunięciem, wyróżnij je formatowaniem warunkowym lub oznacz je za pomocą pomocniczej kolumny z funkcją COUNTIF.
- Funkcje UNIQUE i Power Query dają czystą listę bez ingerencji w dane źródłowe, a Power Query powtarza oczyszczanie przy każdym odświeżeniu.
Excel oferuje więcej niż jedno narzędzie do tego zadania, ponieważ „duplikat” może oznaczać różne rzeczy. Czasem jest to całkowicie identyczny wiersz. Innym razem jest to ten sam identyfikator klienta z innym numerem telefonu. Zanim cokolwiek klikniesz, zdecyduj, o który przypadek chodzi.
Usuwanie duplikatów za pomocą polecenia na karcie Dane
To najszybsza metoda i ta, której większość osób potrzebuje. Opiera się na artykule Microsoftu o filtrowaniu unikatowych wartości lub usuwaniu zduplikowanych wartości.
- Wykonaj kopię zapasową danych. Skopiuj zakres do innego arkusza lub skoroszytu. Microsoft mówi wprost: duplikaty są trwale usuwane.
- Zaznacz zakres. Kliknij dowolną komórkę w danych lub w tabeli Excela albo zaznacz zakres samodzielnie. Zmieniony zostanie wyłącznie zaznaczony zakres lub tabela. Komórki poza nim pozostaną na swoim miejscu.
- Otwórz polecenie. Przejdź do karty Dane i wybierz Usuń duplikaty w grupie Narzędzia danych.
- Wybierz kolumny. W oknie dialogowym zaznacz kolumny, które Excel ma porównywać. Możesz użyć przycisków Zaznacz wszystko lub Odznacz wszystko. Jeśli pierwszy wiersz zawiera nazwy kolumn, pozostaw zaznaczoną opcję Moje dane mają nagłówki.
- Wybierz OK. Excel wyświetli komunikat z liczbą usuniętych zduplikowanych wartości i liczbą pozostałych wartości unikatowych.
Zwróć uwagę na krok 4, ponieważ często sprawia kłopot. Wiersz jest duplikatem tylko wtedy, gdy zgadzają się wszystkie zaznaczone kolumny. Weźmy listę z kolumnami ID, Nazwa i Cena. Jeśli zaznaczysz ID i Nazwa, ale nie Cena, dwa wiersze o tym samym ID i nazwie zostaną uznane za duplikaty, nawet jeśli ich ceny się różnią. Excel usunie wtedy cały drugi wiersz, łącznie z ceną. Jeśli chcesz zachować każdy wiersz, w którym cokolwiek się różni, zaznacz wszystkie kolumny. Jeśli sam identyfikator ID jednoznacznie określa rekord, zaznacz tylko ID, ale pamiętaj, co w ten sposób usuwasz.
Jeśli wynik wygląda źle, naciśnij Ctrl+Z zaraz po wykonaniu polecenia. Nie licz na to później.
Który wiersz zostanie: najpierw sortowanie
Firma Microsoft podaje, że pierwsze wystąpienie zostaje, a kolejne identyczne wiersze są usuwane. Oznacza to, że o wyniku decyduje kolejność wierszy. Jeśli masz listę kontaktów z kilkoma wpisami dla jednej osoby i chcesz zachować najnowszy, posortuj ją według daty, od najnowszej, a następnie uruchom polecenie Usuń duplikaty. Jeśli chcesz zachować wiersz z wypełnionym adresem e-mail, posortuj dane tak, aby te wiersze były na górze. Sortowanie jest tu zwykłym elementem porządkowania danych, a nie miłym dodatkiem.
Co uznaje się za identyczne
Program Excel porównuje to, co widać w komórce, a nie to, co jest w niej zapisane. Przykład firmy Microsoft: 3/8/2006 i Mar 8, 2006 są traktowane jako różne wartości, mimo że oznaczają tę samą datę. Tak samo jest z liczbami sformatowanymi inaczej lub tekstem z końcową spacją. Artykuł o wyszukiwaniu i usuwaniu duplikatów wspomina również, że liczby, które Excel podaje po wykonaniu operacji, mogą obejmować puste komórki lub spacje.
Zanim cokolwiek usuniesz, ujednolic wygląd komórek. Zastosuj jeden format daty, usuń zbędne spacje (robi to funkcja TRIM) i sprawdź, czy liczby nie są zapisane jako tekst. Strony firmy Microsoft nie mówią, czy polecenie ignoruje wielkość liter, więc jeśli ma to dla Ciebie znaczenie, sprawdź to na małym zakresie.
Kiedy polecenie odmawia wykonania
Nie można usunąć duplikatów z danych zawierających konspekt lub sumy częściowe. Najpierw usuń konspekt i sumy częściowe, a dopiero potem uruchom polecenie.
Przejrzyj duplikaty, zanim je usuniesz
Firma Microsoft radzi najpierw filtrować lub wyróżnić duplikaty, a dopiero potem je usunąć. Zajmuje to minutę i pozwala uniknąć większości żalu.
Wyróżnianie duplikatów za pomocą formatowania warunkowego
- Zaznacz komórki, które chcesz sprawdzić.
- Na karcie Narzędzia główne otwórz Formatowanie warunkowe, następnie wybierz Reguły wyróżniania komórek, a potem Duplikuj wartości.
- Wybierz styl wyróżnienia i kliknij OK.
Każda powtarzająca się wartość jest teraz pokolorowana. Nie można tego zastosować do obszaru Wartości tabeli przestawnej. Ta metoda sprawdza jedną kolumnę naraz, więc wiersz powtarzający się tylko w kilku kolumnach nie zostanie wyróżniony. Do tego potrzebna jest kolumna pomocnicza.
Oznaczanie zduplikowanych wierszy za pomocą kolumny pomocniczej
Kolumna pomocnicza wyświetla PRAWDA lub FAŁSZ obok każdego wiersza. Dzięki temu możesz filtrować i przeglądać dane, a następnie je usunąć.
- Wstaw kolumnę obok danych i dodaj nagłówek, np. „Duplikat?”.
- Jeśli sprawdzasz kolumnę A, wpisz w komórce B2:
=COUNTIF(A$2:A2,A2)>1 - Przeciągnij formułę w dół. Za pierwszym razem, gdy wartość się pojawi, zobaczysz FAŁSZ. Każde kolejne wystąpienie da PRAWDA.
- Przefiltruj kolumnę pomocniczą według wartości PRAWDA. Przejrzyj te wiersze. Usuń je, jeśli rzeczywiście są duplikatami.
Ponieważ zakres A$2:A2 rozszerza się podczas kopiowania formuły w dół, oznaczone są tylko późniejsze wpisy, a pierwszy pozostaje bez zmian. Odpowiada to działaniu polecenia Usuń duplikaty.
Aby sprawdzić kilka kolumn naraz, utwórz kolumnę klucza, która je połączy: =A2&"|"&B2&"|"&C2. Uruchom funkcję COUNTIF na tym kluczu. Między polami umieść separator, np. kreskę pionową. Bez niego „12” i „34” oraz „1” i „234” dadzą wynik „1234” i zostaną potraktowane jak ten sam rekord.
Filtrowanie ma też wadę. Jeśli usuwasz przefiltrowane wiersze, zaznaczając je, sprawdź, czy wybrane są tylko widoczne, oznaczone wiersze, i wykonaj to na kopii. W razie wątpliwości posortuj kolumnę pomocniczą i usuń cały blok wierszy z wartością PRAWDA za jednym razem.
Uzyskaj listę bez duplikatów i zachowaj źródło
Czasami nie chcesz niczego usuwać. Potrzebujesz tylko czystej listy do listy rozwijanej, podsumowania lub importu. Tutaj sprawdzają się trzy opcje.
Funkcja UNIQUE
W Excelu dla Microsoft 365, Excelu 2024 i Excelu 2021 funkcja =UNIQUE(A2:A100) zwraca każdą unikatową wartość z zakresu, a wynik rozlewa się na komórki poniżej. Microsoft podaje, że funkcja jest dostępna także w Excelu dla sieci Web i w aplikacjach mobilnych. Składnia to =UNIQUE(array,[by_col],[exactly_once]):
arrayto zakres do sprawdzenia. Jeśli obejmuje kilka kolumn, UNIQUE porównuje całe wiersze.by_colma wartość TRUE, jeśli chcesz porównywać kolumny zamiast wierszy. Pomiń ten argument, aby uzyskać standardowe porównanie wierszy.exactly_oncema wartość TRUE, jeśli chcesz otrzymać tylko te wiersze, które w danych występują dokładnie raz. Jeśli go pominiesz, dostaniesz każdy unikatowy wiersz.
Wynik aktualizuje się wraz ze zmianami w źródle. Jeśli źródłem jest tabela programu Excel, a w formułach używasz odwołań strukturalnych, zakres rozlania zmienia rozmiar podczas dodawania lub usuwania wierszy. Aby uzyskać posortowaną listę, owiń funkcję w ten sposób: =SORT(UNIQUE(A2:A100)).
Para drugiego i trzeciego argumentu ma większe znaczenie, niż się wydaje. =UNIQUE(A2:A100,,TRUE) zwraca wartości, które występują dokładnie raz, i to jest zupełnie inne pytanie niż „usuń nadmiarowe kopie”. Zastanów się, którego wyniku potrzebujesz.
Jedno ograniczenie: starsze wersje Excela (2019 i wcześniejsze) nie mają funkcji UNIQUE. Strona Microsoftu wymienia wyłącznie wersje wspomniane powyżej.
Zaawansowane filtrowanie
Ta metoda działa we wszystkich wersjach desktopowych i kopiuje wynik w inne miejsce.
- Zaznacz dane.
- Przejdź do Dane > Zaawansowane w grupie Sortowanie i filtrowanie.
- Wybierz Kopiuj do innej lokalizacji.
- Wpisz komórkę w polu Kopiuj do i zaznacz Tylko unikatowe rekordy.
- Wybierz OK.
Oryginalne dane pozostają na swoim miejscu, a unikatowe wiersze pojawiają się w nowej lokalizacji.
Starszy wzorzec formuły
Jeśli nie możesz użyć funkcji UNIQUE, wpisz w nowej kolumnie =IF(COUNTIF($A$2:A2,A2)=1,A2,"") i przeciągnij formułę w dół. Każda wartość pojawi się raz, a powtórzenia zamienią się w puste ciągi. Skopiuj kolumnę, wklej ją jako wartości, a następnie odfiltruj puste komórki. To mało eleganckie, ale działa wszędzie.
Obsługa częściowych dopasowań i różnic w wielkości liter
Rzeczywiste dane rzadko są czystą kopią. Trzy sytuacje pojawiają się wielokrotnie.
Dokładne duplikaty. Każda zaznaczona kolumna się zgadza. Polecenie radzi sobie z tym bez problemu.
Częściowe dopasowania. Kolumna klucza się powtarza, ale pozostałe pola się różnią, na przykład ten sam identyfikator klienta z dwoma różnymi adresami. Tutaj musisz zdecydować, która wersja jest poprawna. Posortuj dane tak, aby właściwy rekord był pierwszy, a następnie usuń duplikaty tylko na podstawie kolumny klucza. Możesz też oznaczyć powtórzenia pomocniczą kolumną i sprawdzić je ręcznie. Żadne narzędzie nie powie Ci, który adres jest prawidłowy.
Różnice w wielkości liter. Według dokumentacji Microsoftu Power Query rozróżnia wielkość liter, więc „Apple” i „apple” nie są tam duplikatami. Sugerowane obejście polega na zmianie wielkości liter w kolumnie na wielkie lub małe za pomocą przekształcenia, zanim usuniesz duplikaty. Zmienia to zapisane wartości, dlatego zachowaj oryginalną kolumnę, jeśli nadal potrzebujesz dokładnej pisowni.
Automatyzacja: Power Query i VBA
Do jednorazowego czyszczenia wystarczy polecenie. W przypadku pliku, który czyścisz co tydzień, skonfiguruj rozwiązanie, które będzie się powtarzać.
Power Query
Jeśli dane pochodzą z pliku CSV, bazy danych lub innego skoroszytu, usuń duplikaty już podczas importu. Power Query zapisuje ten krok i wykonuje go ponownie przy każdym odświeżeniu.
- Wczytaj dane do Edytora Power Query. Jeśli dane są już w arkuszu, zaznacz komórkę w danych i wybierz Zapytanie > Edytuj, albo użyj Dane > Pobierz dane
- Kliknij nagłówek kolumny, aby ją zaznaczyć. Przytrzymaj Shift lub Ctrl, aby zaznaczyć więcej kolumn. Kolumny nie muszą sąsiadować ze sobą.
- Przejdź do Narzędzia główne > Usuń wiersze > Usuń duplikaty.
- Zamknij i załaduj wynik.
Microsoft na stronie dotyczącej zachowywania lub usuwania zduplikowanych wierszy w Power Query opisuje te kroki. Warto znać jego wskazówki dotyczące duplikatów: o tym, co uznajemy za duplikat, decydują wybrane kolumny, a Power Query nie gwarantuje, że zachowa pierwszy wiersz z grupy. Jeśli ma znaczenie, który wiersz zostanie, nie polegaj wyłącznie na sortowaniu. Microsoft odsyła do wskazówek „Preserve sort” w artykule o typowych problemach z Power Query. Powiązana opcja, Keep Duplicates, działa odwrotnie: zachowuje tylko te wiersze, których wartości występują więcej niż raz, co jest szybkim sposobem na audyt listy.
Jeśli łączysz dwie tabele, zastanów się, gdzie powinno nastąpić usuwanie duplikatów. Usunięcie duplikatów z każdego źródła przed scaleniem utrzymuje scalenie w małej skali i przewidywalne. Usunięcie ich po scaleniu porządkuje to, co powstało w wyniku scalenia. Wybierz w zależności od tego, skąd pochodzą duplikaty, i sprawdź liczbę wierszy w podglądzie przed załadowaniem.
Makro VBA
Jeśli powtarzasz to samo czyszczenie w skoroszytach, które otwierasz codziennie, możesz rozważyć makro. Obiekt Range ma metodę RemoveDuplicates, udokumentowaną w dokumentacji VBA firmy Microsoft. Przyjmuje ona kolumny do porównania i określa, czy pierwszy wiersz zawiera nagłówki. Przykład podany przez Microsoft to:
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes
Ten wiersz porównuje dwie pierwsze kolumny w zakresie A1:C100 i traktuje pierwszy wiersz jako nagłówek. Aby go użyć, naciśnij Alt+F11, wstaw moduł, wklej wiersz wewnątrz Sub i zapisz plik jako skoroszyt z obsługą makr (.xlsm). Najpierw uruchom go na kopii. Makro usuwa dane równie nieodwracalnie jak polecenie, a Ctrl+Z nie przywróci wierszy. Dział IT może też blokować makra, więc sprawdź to, zanim zbudujesz coś, czego nie uruchomią Twoi współpracownicy. Jeśli chcesz dowiedzieć się więcej o tym języku, na stronie VBA zebrano powiązane materiały.
Excel w sieci Web i na urządzeniach mobilnych
Artykuły Microsoft o usuwaniu duplikatów wymieniają wyłącznie wersje Excela na komputery (Microsoft 365, 2024, 2021, 2019 i 2016) i nie wspominają o sieci Web ani aplikacjach mobilnych. Microsoft nie opisuje polecenia Usuń duplikaty dla tych wersji, więc nie buduj na nim procesu. Microsoft udokumentował natomiast dla sieci Web i urządzeń mobilnych funkcję UNIQUE, więc użyj jej, aby uzyskać czystą listę na tych urządzeniach, a trwałe usunięcie wykonaj na komputerze.
Częste błędy
- Uruchomienie polecenia bez kopii zapasowej. Po zamknięciu pliku wiersze znikają bezpowrotnie.
- Zaznaczenie niewłaściwych kolumn. Pominięcie kolumny zmienia to, które wiersze uznawane są za duplikaty, a usuwany jest cały wiersz, a nie tylko powtórzona komórka.
- Zapomnienie o wcześniejszym sortowaniu, a potem zdziwienie, dlaczego zachował się stary rekord zamiast nowego.
- Porównywanie komórek, które wyglądają tak samo, ale mają inne formatowanie, na przykład daty zapisane na dwa sposoby lub tekst z końcowymi spacjami.
- Próba uruchomienia polecenia na danych z konturem lub sumami częściowymi. Najpierw usuń te elementy.
- Traktowanie
UNIQUEz ustawieniemexactly_oncena TRUE jako sposobu na usunięcie nadmiarowych wpisów. Funkcja zwraca tylko wartości występujące jeden raz, więc wartości powtarzające się znikają z wyniku całkowicie.
Powiązane tematy do zgłębienia
- Wyróżnij duplikaty formatowaniem warunkowym i oznacz powtarzające się wiersze pomocniczą kolumną z funkcją LICZ.JEŻELI, zanim cokolwiek usuniesz. Oba sposoby opisano powyżej.
- Czyść dane podczas importu. Krok w Power Query nie dopuszcza duplikatów do każdego odświeżenia i dobrze sprawdza się w plikach CSV oraz danych z innych systemów.
- Zapobiegaj duplikatom przy wprowadzaniu. Walidację danych z niestandardową formułą można ustawić tak, aby uniemożliwić użytkownikom wpisanie wartości, która już istnieje. Najpierw przetestuj ją na małym arkuszu.
- Automatyzuj powtarzalne zadania za pomocą makra i naucz się uruchamiać je bezpiecznie, na kopii.
- Scalanie danych z kilku źródeł, skąd często biorą się duplikaty. Zaplanuj kolejność czyszczenia i scalania.
Następny krok
Przed kolejnym czyszczeniem skopiuj dane, wyróżnij duplikaty formatowaniem warunkowym i sprawdź, ile ich jest. Następnie uruchom Usuń duplikaty na kopii i porównaj liczby z tymi, które podaje Excel.