Jak znaleźć i usunąć duplikaty w programie Excel

Zduplikowane wartości w programie Excel mogą być denerwujące, ale na szczęście istnieje kilka metod ich wyszukiwania i usuwania. Zalecamy wykonanie kopii zapasowej arkusza Excel przed usunięciem duplikatów. Przyjrzyjmy się, jak liczyć, znajdować i usuwać zduplikowane wartości w programie Excel.

1. Użyj przycisku Usuń duplikaty

Najszybszą metodą znalezienia i usunięcia duplikatów w programie Excel jest użycie przycisku „Usuń duplikaty” w programie Excel. Ta metoda umożliwia wyszukiwanie duplikatów na podstawie danych w jednej lub większej liczbie kolumn. Usuwa całe wiersze w przypadku znalezienia duplikatów.

Kliknij dowolną komórkę zawierającą dane w arkuszu kalkulacyjnym. Nie musi to być komórka, kolumna ani wiersz, z którego chcesz usunąć duplikaty.

Wybierz zakładkę „Dane” i kliknij przycisk „Usuń duplikaty” na pasku narzędzi.

Zaznacz kolumny, z których chcesz usunąć duplikaty. Domyślnie zaznaczone są wszystkie kolumny. Ponadto, jeśli Twoje kolumny nie mają nagłówków, odznacz pole „Moje dane mają nagłówki”, aby uwzględnić pierwszy wiersz. Kliknij „OK” po wybraniu żądanych kolumn.

Excel wyświetli komunikat informujący, ile wierszy zostało usuniętych i ile wierszy pozostało. Ta metoda usuwa cały wiersz, a nie tylko wartość. Naciśnij „OK”, aby zaakceptować wyniki i wrócić do arkusza kalkulacyjnego.

Jeśli nie podobają Ci się wyniki, naciśnij Ctrl+, Zaby przywrócić usunięte wartości do arkusza.

2. Znajdź zduplikowane dane przy użyciu formatowania warunkowego

Jeśli wolisz przeglądać duplikaty w programie Excel przed usunięciem czegokolwiek, spróbuj użyć formatowania warunkowego . Spowoduje to znalezienie duplikatów w programie Excel, ale ich nie usunie.

Wybierz kolumny lub cały arkusz, w których chcesz wyszukać zduplikowane wartości.

Otwórz kartę „Strona główna” i kliknij przycisk „Formatowanie warunkowe”.

Wybierz „Reguły podświetlania komórek” i wybierz „Zduplikowane wartości”.

Wybierz kolor, w jakim mają być wyświetlane zduplikowane wartości. Możesz także zmienić „Typ reguły” z „Zduplikowane wartości” na „Unikalne wartości”, jeśli wolisz, aby unikalne wartości były podświetlone. Kiedy skończysz, kliknij „Gotowe”.

Przejrzyj podświetlone komórki i usuń niepotrzebne duplikaty. W moim przykładzie chcę tylko usunąć wiersze, w których każda wartość jest zduplikowana.

3. Usuń duplikaty za pomocą filtra warunkowego

Jeśli chcesz ułatwić usuwanie duplikatów w programie Excel po użyciu formatowania warunkowego, użyj filtru programu Excel opartego na kolorze komórki, aby wyświetlić tylko duplikaty lub unikalne wartości. Dzięki tej opcji ręcznie usuniesz całe wiersze lub tylko zduplikowane komórki.

Skonfiguruj formatowanie warunkowe w oparciu o poprzednią sekcję.

Otwórz zakładkę „Strona główna”, wybierz przycisk „Sortuj i filtruj” i wybierz „Filtruj”.

W nagłówku każdej kolumny pojawią się strzałki rozwijania. Kliknij strzałkę i wybierz „Filtruj według koloru”. Wybierz kolor zduplikowanych komórek, aby tylko je wyświetlić. Możesz też wybrać opcję „Bez wypełnienia”, aby wybrać tylko unikalne wartości.

Pamiętaj, że po wybraniu opcji „Bez wypełnienia” nie zostaną wyświetlone żadne duplikaty (w tym wartość oryginalna). Jeśli chcesz nadal widzieć wszystkie wartości, wybierz zamiast tego opcję „Filtruj według koloru”. Spowoduje to umieszczenie kolorowych komórek lub komórek bez wypełnienia na górze listy.

W moim przykładzie sortuję według koloru zduplikowanych komórek. Dzięki temu możesz przejrzeć duplikaty i usunąć wszystkie wiersze lub wartości, których nie chcesz zachować. Jeśli zdecydujesz się wyświetlać tylko unikalne wartości, możesz skopiować lub przenieść wszystkie widoczne dane do nowego arkusza. Spowoduje to pozostawienie jedynie duplikatów.

Po zakończeniu edycji danych kliknij ponownie strzałkę menu i wybierz „Wyczyść filtr”.

Jeśli chcesz usunąć strzałki rozwijane, wybierz „Sortuj i filtruj” z paska narzędzi, a następnie wybierz „Filtruj”. Spowoduje to wyczyszczenie wszystkich filtrów.

4. Znajdź duplikaty w Excelu za pomocą formuły

Formuły programu Excel umożliwiają wykonanie większości zadań w arkuszu kalkulacyjnym, łącznie ze znalezieniem duplikatów. Istnieje kilka różnych formuł, których możesz użyć w zależności od tego, co chcesz zobaczyć. Wszystkie opierają się na funkcjach COUNT . Te formuły nie usuwają duplikatów. Po ich znalezieniu musisz to zrobić ręcznie. Bez względu na to, jakiej formuły użyjesz, proces jest taki sam:

Utwórz nową kolumnę na tym samym arkuszu. Oznaczyłem swoje „Duplikaty”.

Wybierz pierwszą pustą komórkę w nowej kolumnie. Wprowadź żądaną formułę (zobacz różne formuły poniżej kroków) na pasku funkcji/formuły lub w samej komórce.

Jeśli chcesz rozszerzyć formułę na inne komórki w kolumnie, kliknij i przytrzymaj mały kwadrat w prawym dolnym rogu komórki zawierającej formułę. Pociągnij w dół, aby wypełnić dowolną liczbę komórek.

Teraz, gdy wiesz, jak wstawić formułę, najlepiej skorzystać z poniższych opcji:

• =COUNTIF(Range, Criteria) >1 – Zakres to kolumna, a kryterium to komórka znajdująca się najwyżej. Działa to dobrze w przypadku wyszukiwania duplikatów w pojedynczej kolumnie. Na przykład, aby zobaczyć duplikaty tylko na podstawie kolumny D, użyj =COUNTIF(D:D, D2) >1. Wynik „True” oznacza duplikat, a „False” oznacza unikalny.

• Jeśli nie chcesz, aby odwołanie do komórki zmieniało się podczas przeciągania, użyj zamiast tego odwołań bezwzględnych do komórek. Na przykład sprawdziłbym =COUNTIF($D$2:$D$105, $D2) >1całą kolumnę D.
• Jeśli chcesz pokazać coś innego niż Prawda lub Fałsz, umieść LICZBĘ JEŚLI w funkcji JEŻELI: =IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", ""). Spowoduje to wyświetlenie opcji „Duplikuj” dla duplikatów i pustej komórki dla unikalnych wartości. Możesz z łatwością użyć dowolnych słów, takich jak „Duplikat” i „Unikalny”.
• Jeśli chcesz porównać wiele kolumn na raz, musisz użyć bezwzględnych odwołań do komórek i instrukcji JEŻELI, takiej jak: =IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique")Ta formuła wyświetli komunikat „Duplikuj” tylko wtedy, gdy wartości w D, E i F są takie same. Jeśli którykolwiek z trzech jest unikalny w rzędzie, zamiast tego wyświetli się „Unikalny”.
• Jeśli chcesz wyświetlić tylko zduplikowane wartości, a nie pierwsze wystąpienie wartości, spróbuj zamiast tego: =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique")Opcja „Duplikuj” wyświetla się tylko w przypadku drugiego lub większej liczby wystąpień.

5. Policz liczbę duplikatów, korzystając ze wzoru

Możesz użyć powyższej formuły bez > 1tekstu, aby policzyć liczbę duplikatów w kolumnie. Te dwie formuły staną się =COUNTIF($D$2:$D$105, $D2)i =COUNTIF(D:D, D2). Wprowadź nową kolumnę, aby wyświetlić liczbę wystąpień każdego elementu w danych.

Wartość „1” oznacza, że ​​jest to wartość unikalna. Wszystko inne jest równoznaczne z duplikatami.

6. Usuń zduplikowane wartości za pomocą formuły

Po znalezieniu zduplikowanych wartości (sekcja 4) lub liczby duplikatów (sekcja 5) użyj metody filtrowania, aby usunąć duplikaty i zachować unikalne wartości.

Kliknij dowolne miejsce w kolumnie zawierającej zduplikowane wartości lub liczbę. Przejdź do „Karta Strona główna -> Sortuj i filtruj -> Filtruj”, aby włączyć menu rozwijane filtra w nagłówku kolumny.

Kliknij strzałkę menu rozwijanego w zduplikowanej kolumnie.

Zaznacz wartości, które chcesz zachować, usuń zaznaczenie tych, których nie chcesz, i kliknij „Zastosuj”. Ukryj wszystkie duplikaty, wybierając tylko „Unikalne”, „Fałsz” lub jakąkolwiek inną etykietę unikalnych wartości dla duplikatu wartości i „1” w przypadku zliczania duplikatów. Wyświetl tylko duplikaty, odznaczając unikalne identyfikatory.

Wybierz widoczne wiersze, naciskając Ctrl+ C. Jeśli z jakiegoś powodu spowoduje to zaznaczenie wszystkich wierszy, w tym wierszy ukrytych, użyj zamiast tego skrótu Alt+ .;

Skopiuj unikalne wartości lub duplikaty (w zależności od tego, które chcesz wyświetlić) do innego arkusza. Usuń te wartości z oryginalnego arkusza.

Przejdź do „Sortuj i filtruj -> Filtruj”. Kliknięcie „Filtruj” spowoduje usunięcie filtra i wyświetlenie wszystkich pozostałych duplikatów lub unikalnych wartości.

Stąd przejdź przez duplikaty i usuń komórki lub wiersze, których już nie potrzebujesz. Pamiętaj, że oryginalne wystąpienie jest wymienione ze zduplikowanymi wartościami.

7. Usuń zduplikowane dane za pomocą filtrów zaawansowanych

Do tej pory korzystaliśmy tylko z filtrów podstawowych, ale duplikaty można znaleźć i usunąć w programie Excel także za pomocą filtrów zaawansowanych.

Wybierz kolumnę, którą chcesz filtrować. Otwórz zakładkę „Dane” i kliknij „Zaawansowane”.

• W polu „Filtr zaawansowany” wybierz „Filtruj listę lokalnie”. Spowoduje to ukrycie duplikatów w tym samym zestawie danych. Później możesz ręcznie skopiować i wkleić unikalne wartości w inne miejsce tego samego arkusza lub do innego arkusza.

Jeśli nie wybrałeś jeszcze kolumn, wybierz je. Pojawią się automatycznie w polu „Zakres listy”. Pozostaw pole „Zakres kryteriów” puste.

Zaznacz pole obok „Tylko unikalne rekordy” i naciśnij „OK”.

To pokaże unikalne wartości w twoich danych. Użyj skrótu Alt+ ;, aby wybrać widoczne wiersze, tylko jeśli chcesz wykonać na nich jakąkolwiek akcję, na przykład skopiować do innej lokalizacji.

Przeciwnie, wybierz opcję „Kopiuj do innej lokalizacji” w polu Filtr zaawansowany, jeśli chcesz, aby program Excel automatycznie kopiował unikalne wartości w inne miejsce w tym samym arkuszu.

Najpierw wybierz „Zakres listy”. Możesz pozostawić pole „Zakres kryteriów” puste.

Kliknij raz pole „Kopiuj do” i wybierz wiersze w arkuszu, do których chcesz skopiować unikalne dane.

Upewnij się, że pole obok „Tylko unikalne rekordy” jest zaznaczone.

Ta metoda ukrywa cały zduplikowany wiersz, a nie tylko wartości. Ukrywa również oryginalne wystąpienie zduplikowanej wartości, a nie tylko zduplikowane wersje. Będziesz chciał wyświetlić duplikaty, aby wyodrębnić oryginalne wartości.

8. Usuń duplikaty programu Excel za pomocą dodatku Power Query

Power Query może usunąć zduplikowane wartości w programie Excel, jak pokazano poniżej. Ta metoda usuwa cały zduplikowany wiersz.

Otwórz zakładkę „Dane” i wybierz „Z tabeli/zakresu”.

Wszystkie dane powinny zostać wybrane automatycznie. Jeśli nie, wpisz cały zakres arkusza w polu „Utwórz tabelę”. Kiedy skończysz, kliknij „OK”.

Otworzy się edytor „Power Query”. Wybierz kolumny i kliknij prawym przyciskiem myszy wybrany nagłówek kolumny. Z menu wybierz opcję „Usuń duplikaty”. Spowoduje to usunięcie wszystkich duplikatów w edytorze dodatku Power Query. W tym narzędziu oryginalne wystąpienie pozostaje na liście.

Jeśli chcesz usunąć duplikaty z całej tabeli, kliknij przycisk „Tabela” w lewym górnym rogu i wybierz „Zachowaj duplikaty”, aby wyświetlić tylko zduplikowane wpisy i usunąć resztę.

Wyświetla wszystkie duplikaty, łącznie z oryginalnymi wpisami. Aby usunąć dodatki i pozostawić tylko oryginalne wpisy, kliknij ponownie „Tabela” i wybierz „Usuń duplikaty”.

Kliknij „Zamknij i załaduj” u góry, aby otworzyć tabelę w nowej karcie tego samego skoroszytu. Załadują się tylko wyniki Twojego zapytania. Nie powoduje to usunięcia niczego z oryginalnego arkusza.

9. Korzystanie z tabeli przestawnej

Użyj tabel przestawnych, aby wyświetlić tylko unikalne wartości w danych, usuwając w ten sposób zduplikowane wpisy. W rzeczywistości nie powoduje to usunięcia żadnych wierszy ani wartości z oryginalnych danych; pokazuje po prostu unikalne wartości.

Otwórz kartę „Wstaw” i wybierz „Tabela przestawna”. Wybierz „Z tabeli/zakresu”.

Wybierz tabelę lub zakres, w którym chcesz ukryć zduplikowane wartości. Domyślnie powinien być wybrany cały zestaw wartości. Jeśli nie, naciśnij Ctrl+, Aaby zaznaczyć wszystko. Możesz też użyć myszy, aby wybrać zakres niestandardowy.

Wybierz, czy tabela przestawna ma zostać umieszczona w tym samym arkuszu, czy w nowym arkuszu. Jeśli jest to istniejący arkusz, wprowadź lub wybierz komórkę, której chcesz użyć w polu „Lokalizacja”. Kliknij OK.”

Na pasku bocznym tabeli przestawnej zaznacz lub przeciągnij kolumny, z których chcesz wyodrębnić unikalne wartości, do sekcji „Wiersze”.

Należy sformatować tabelę przestawną, aby wyświetlić ją w formie tabelarycznej. W tym celu przejdź do zakładki „Projekt” i wykonaj następujące kroki:

Wybierz „Układ raportu -> Pokaż w formie tabelarycznej”.

Wybierz „Podsumy -> Nie pokazuj sum częściowych”.

Wybierz „Układ raportu -> Powtórz wszystkie etykiety pozycji”.

Wybierz „Sumy całkowite -> Wyłączone dla wierszy i kolumn”.

Otrzymasz tabelę przestawną z unikalnymi wartościami w formie tabelarycznej.

Przy tak wielu sposobach wyszukiwania i usuwania duplikatów w programie Excel nie ma powodu robić tego ponownie ręcznie. Wybierz swoją ulubioną metodę i biegnij z nią. Oszczędzając czas w przypadku duplikatów problemów, wypróbuj porady i wskazówki dotyczące programu Microsoft Excel, aby zaoszczędzić jeszcze więcej czasu. Dowiedz się także, jak dokładniej wyczyścić dane w programie Excel .

Źródło obrazu: Pixabay . Wszystkie zrzuty ekranu wykonał Crystal Crowder.