Jak zaznaczyć zduplikowane komórki i wiersze w Excelu

W tym poradniku dowiesz się, jak pokazać duplikaty w Excelu. Przyjrzymy się z bliska różnym metodom cieniowania zduplikowanych komórek, całych wierszy lub kolejnych duplikatów przy użyciu formatowania warunkowego. Zobaczysz również, jak wyróżnić duplikaty różnymi kolorami za pomocą specjalistycznego narzędzia.

W zeszłym tygodniu badaliśmy różne sposoby identyfikacji duplikatów w Excelu. Niewątpliwie formuły duplikatów są bardzo przydatne, ale wyróżnianie zduplikowanych wpisów zdefiniowanym kolorem może jeszcze bardziej ułatwić analizę danych.

Najszybszym sposobem na znalezienie i wyróżnienie duplikatów w Excelu jest użycie formatowania warunkowego. Największą zaletą tej metody jest to, że nie tylko pokazuje ona istniejące duplikaty, ale wykrywa i koloruje nowe duplikaty podczas wprowadzania, edytowania lub nadpisywania danych.

W dalszej części tego poradnika znajdziesz wiele sposobów podświetlania zduplikowanych rekordów w zależności od konkretnego zadania. Te techniki działają we wszystkich wersjach programu Excel 2016, Excel 2013, Excel 2010 i niższych.

• Podświetlanie duplikatów w Excelu z 1. wystąpieniem (wbudowana reguła)w regule)
• Jak podświetlić duplikaty w Excelu z wyjątkiem pierwszego wystąpienia
• Jak podświetlić duplikaty w zakresie (wiele kolumn)
• Jak podświetlić N-ty i kolejne duplikaty rekordy
• Zacieniowanie całych wierszy na podstawie zduplikowanych wartości w jednej kolumnie
• Jak podświetlić zduplikowane wiersze w Excelu
• Jak wyświetlić kolejne zduplikowane komórki
• Duplicate Remover – najszybszy sposób na zaznaczanie duplikatów w Excelu

Jak zaznaczyć duplikaty w Excelu za pomocą wbudowanej reguły (z 1. wystąpieniem)

Na początek, we wszystkich wersjach programu Excel istnieje predefiniowana reguła do podświetlania zduplikowanych komórek. Aby użyć tej reguły w swoich arkuszach, wykonaj następujące czynności:

1. Wybierz dane, które chcesz sprawdzić pod kątem duplikatów. Może to być kolumna, wiersz lub zakres komórek.
2. Na karcie Strona główna, w grupie Style, kliknij polecenie Formatowanie warunkowe > Reguły wyróżniania komórek > Duplikaty wartości…
   
3. Okno dialogowe Duplikaty wartości zostanie otwarte z domyślnie wybranym formatem Jasnoczerwone wypełnienie i Ciemnoczerwony tekst. Aby zastosować domyślny format, wystarczy kliknąć przycisk OK.

Oprócz czerwonego wypełnienia i formatowania tekstu na liście rozwijanej dostępnych jest kilka innych predefiniowanych formatów. Aby podświetlić duplikaty przy użyciu innego koloru, kliknij przycisk Format niestandardowy… (ostatnia pozycja na liście rozwijanej) i wybierz kolor wypełnienia i/lub czcionki według własnych upodobań.

Używając wbudowanej reguły, można podświetlić duplikaty w jednej kolumnie lub w kilku kolumnach, jak pokazano na poniższym zrzucie ekranu:

Przy korzystaniu z wbudowanej reguły Excela do podświetlania zduplikowanych wartości należy pamiętać o następujących dwóch rzeczach:

• To działa tylko dla pojedynczych komórek. Aby podświetlić zduplikowane wiersze, należałoby utworzyć własne reguły oparte na wartościach w określonej kolumnie lub porównujące wartości w kilku kolumnach.
• Ocienia zduplikowane komórki, w tym ich pierwsze wystąpienie. Aby podświetlić wszystkie duplikaty z wyjątkiem pierwszych wystąpień, utwórz regułę formatowania warunkowego opartą na tej formule.

Jak podświetlić duplikaty bez pierwszego wystąpienia

Aby podświetlić drugie i wszystkie kolejne wystąpienia duplikatów, zaznacz komórki, które chcesz pokolorować, i utwórz regułę opartą na formule w ten sposób:

1. Na karcie Strona główna, w grupie Style kliknij polecenie Formatowanie warunkowe > Nowa reguła > Użyj formuły, aby określić, które komórki należy sformatować.
2. W polu Formatuj wartości, gdzie ta formuła jest prawdziwa wprowadź formułę podobną do tej:
   =COUNTIF($A:$A2,$A2)>1

   Gdzie A2 jest najwyższą komórką wybranego zakresu.

3. Kliknij przycisk Formatuj… i wybierz żądany kolor wypełnienia i/lub czcionki.
4. Na koniec kliknij przycisk OK, aby zapisać i zastosować regułę.

Jeśli nie masz dużego doświadczenia z formatowaniem warunkowym w programie Excel, szczegółowe kroki tworzenia reguły opartej na formule znajdziesz w poniższym samouczku: Formatowanie warunkowe w Excelu na podstawie wartości innej komórki.

W rezultacie zduplikowane komórki z wyłączeniem pierwszych instancji zostaną wyróżnione wybranym przez Ciebie kolorem:

Jak wyświetlić 3, 4 i wszystkie kolejne zduplikowane rekordy

Aby wyświetlić duplikaty zaczynające się od N-tego wystąpienia, utwórz regułę formatowania warunkowego na podstawie formuły jak w poprzednim przykładzie, z tą różnicą, że zastąpisz >1 na końcu formuły wymaganą liczbą. Na przykład:

Aby podświetlić 3. i wszystkie kolejne duplikaty, utwórz regułę formatowania warunkowego na podstawie formuły:

=COUNTIF($A:$A2,$A2)>=3

Aby podświetlić 4. i wszystkie kolejne duplikaty, użyj formuły:

=COUNTIF($A:$A2,$A2)>=4

Aby podświetlić tylko określone wystąpienia, użyj operatora równości (=). Na przykład, aby podświetlić tylko 2. wystąpienia, użyłbyś tej formuły:

=COUNTIF($A:$A2,$A2)=2

Jak podświetlić duplikaty w zakresie (wiele kolumn)

Gdy chcesz sprawdzić duplikaty w wielu kolumnach, nie porównując kolumn ze sobą, ale znaleźć wszystkie wystąpienia tego samego elementu we wszystkich kolumnach, użyj jednego z następujących rozwiązań.

Podświetl duplikaty w wielu kolumnach, w tym pierwsze wystąpienie

Jeśli pierwsze wystąpienie elementu, który pojawia się w zbiorze danych więcej niż raz, jest uważane za duplikat, najprostszym sposobem jest użycie wbudowanej reguły Excela dla duplikatów.

Albo utwórz regułę formatowania warunkowego za pomocą tej formuły:

Na przykład, aby zaznaczyć duplikaty w zakresie A2:C8, formuła wygląda następująco:

=COUNTIF($A:$C, A2)>1

Zauważ użycie bezwzględnych odniesień do komórek dla zakresu ($A$2:$C$8) i względnych odniesień dla górnej komórki (A2).

Podświetl duplikaty w wielu kolumnach bez pierwszego wystąpienia

Rozwiązanie dla tego scenariusza jest o wiele bardziej skomplikowane, nic dziwnego, że Excel nie ma wbudowanej reguły dla niego 🙂

Aby podświetlić zduplikowane wpisy w kilku kolumnach, ignorując pierwsze wystąpienie, musisz utworzyć 2 reguły z następującymi formułami:

Reguła 1. Stosuje się do pierwszej kolumny

W tym przypadku używamy dokładnie tej samej formuły, której użyliśmy do podświetlenia duplikatów bez pierwszego wystąpienia w jednej kolumnie (szczegółowe kroki można znaleźć tutaj).

W tym przykładzie tworzymy regułę dla A2:A8 z następującą formułą:

=COUNTIF($A:$A2,$A2)>1

W efekcie duplikaty bez pierwszego wystąpienia są wyróżniane w lewej kolumnie zakresu (w naszym przypadku jest tylko jeden taki element):

Reguła 2. Dotyczy wszystkich kolejnych kolumn

Aby wyróżnić duplikaty w pozostałych kolumnach (B2:C8), należy skorzystać z następującego wzoru:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

W powyższym wzorze pierwsza funkcja COUNTIF zlicza wystąpienia danego elementu w pierwszej kolumnie, a druga COUNTIF robi to samo dla wszystkich kolejnych kolumn. Następnie sumujemy te liczby i sprawdzamy, czy suma jest większa od 1.

Jako wynik, wszystkie zduplikowane elementy z wyłączeniem ich pierwszego wystąpienia są znajdowane i podświetlane:

Podświetlanie duplikatów we wszystkich kolumnach za pomocą jednej reguły

Innym możliwym rozwiązaniem jest dodanie pustej kolumny po lewej stronie zbioru danych i połączenie powyższych formuł w jedną formułę, jak poniżej:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Gdzie B2 jest górną komórką z danymi w 2. kolumnie zakresu docelowego.

Aby lepiej zrozumieć formułę, podzielmy ją na 2 główne części:

• W przypadku pierwszej kolumny (B) warunek JEŻELI nigdy nie jest spełniony, więc stosowana jest tylko druga funkcja COUNTIF, czyli zwykła formuła, której już użyliśmy do znalezienia duplikatów bez pierwszego wystąpienia w jednej kolumnie.
• W przypadku wszystkich kolejnych kolumn (C2:D8) kluczowym punktem jest sprytne użycie odwołań bezwzględnych i względnych w dwóch funkcjach COUNTIF. Aby ułatwić zrozumienie, skopiowałem ją do kolumny G, abyś mógł zobaczyć, jak zmienia się formuła po zastosowaniu do innych komórek:
  

Ponieważ warunek JEŻELI jest zawsze TRUE dla wszystkich kolumn poza pierwszą (liczba kolumn jest większa niż 1), formuła postępuje w ten sposób:

• Zlicza liczbę wystąpień danego elementu (D5 na zrzucie ekranu powyżej) we wszystkich kolumnach na lewo od danej kolumny: COUNTIF(B:$C,D5)
• Zlicza liczbę wystąpień danego elementu w kolumnie elementu, aż do komórki elementu: COUNTIF(D:D5,D5)
• Na koniec formuła dodaje wyniki obu funkcji COUNTIF. Jeśli całkowita liczba jest większa niż 1, tzn. jeśli występuje więcej niż jedno wystąpienie elementu, reguła jest stosowana i element jest podświetlany.

Podświetlanie całych wierszy na podstawie zduplikowanych wartości w jednej kolumnie

Jeśli Twoja tabela zawiera kilka kolumn, możesz chcieć podświetlić całe wiersze na podstawie zduplikowanych rekordów w określonej kolumnie.

Jak już wiesz, wbudowana reguła Excela dotycząca duplikatów działa tylko na poziomie komórki. Ale niestandardowa reguła oparta na formule nie ma problemu z cieniowaniem wierszy. Kluczową kwestią jest zaznaczenie całych wierszy, a następnie utworzenie reguły z jedną z następujących formuł:

• Aby podświetlić zduplikowane wiersze z wyłączeniem pierwszego wystąpienia:
  =COUNTIF($A:$A2, $A2)>1
• Aby podświetlić zduplikowane wiersze z uwzględnieniem pierwszego wystąpienia:
  =COUNTIF($A:$A, $A2)>1

Gdzie A2 jest pierwszą komórką, a A15 jest ostatnią używaną komórką w kolumnie, którą chcesz sprawdzić pod kątem duplikatów. Jak widać, sprytne użycie bezwzględnych i względnych odwołań do komórek jest tym, co robi różnicę.
Następujący zrzut ekranu demonstruje obie reguły w działaniu:

Jak podświetlić zduplikowane wiersze w Excelu

W poprzednim przykładzie pokazano, jak kolorować całe wiersze na podstawie zduplikowanych wartości w określonej kolumnie. Ale co zrobić, jeśli chcesz wyświetlić wiersze, które mają identyczne wartości w kilku kolumnach? Albo jak podświetlić wiersze absolutnie zduplikowane, czyli takie, które mają całkowicie jednakowe wartości we wszystkich kolumnach?

W tym celu należy użyć funkcji COUNTIFS, która umożliwia porównywanie komórek według wielu kryteriów. Na przykład, aby wyróżnić zduplikowane wiersze, które mają identyczne wartości w kolumnach A i B, należy użyć jednej z następujących formuł:

• Aby wyróżnić zduplikowane wiersze z wyjątkiem pierwszego wystąpienia:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Aby wyróżnić zduplikowane wiersze z pierwszym wystąpieniem:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

Następujący zrzut ekranu przedstawia wynik:

Jak rozumiesz, powyższy przykład służy wyłącznie do celów demonstracyjnych. Podczas podświetlania zduplikowanych wierszy w rzeczywistych arkuszach nie jesteś oczywiście ograniczony do porównywania wartości tylko w 2 kolumnach, funkcja COUNTIFS może przetwarzać do 127 par zakres/kryterium.

Podświetlanie kolejnych zduplikowanych komórek w Excelu

Czasami może się zdarzyć, że nie będziesz potrzebował podświetlać wszystkich duplikatów w kolumnie, ale raczej pokażesz tylko kolejne zduplikowane komórki, czyli te, które są obok siebie. W tym celu należy zaznaczyć komórki z danymi (bez nagłówka kolumny) i utworzyć regułę formatowania warunkowego z jedną z poniższych formuł:

• Aby wyróżnić kolejne duplikaty bez pierwszego wystąpienia:
  =$A1=$A2
• Aby podświetlić kolejne duplikaty z pierwszym wystąpieniem:
  =OR($A1=$A2, $A2=$A3)

Następujący zrzut ekranu demonstruje podświetlanie kolejnych duplikatów tekstu, ale te reguły będą również działać w przypadku kolejnych duplikatów liczb i dat:

Jeśli Twój arkusz programu Excel może mieć puste wiersze i nie chcesz, aby kolejne puste komórki były podświetlane, wprowadź następujące poprawki do formuł:

• Aby podświetlić kolejne zduplikowane komórki bez pierwszego wystąpienia i zignorować puste komórki:
  =AND($A2<>"", $A1=$A2)
• Aby podświetlić kolejne zduplikowane komórki z pierwszym wystąpieniem i zignorować puste komórki:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Jak widzisz, podświetlanie duplikatów w Excelu za pomocą formatowania warunkowego to nic trudnego. Jest jednak jeszcze szybszy i łatwiejszy sposób. Aby się o tym przekonać, przeczytaj następną część tego poradnika.

Jak zaznaczyć duplikaty w Excelu za pomocą Duplicate Remover

Dodatek Duplicate Remover jest rozwiązaniem typu „wszystko w jednym” do radzenia sobie z duplikatami rekordów w Excelu. Może znaleźć, podświetlić, wybrać, skopiować lub przenieść zduplikowane komórki lub całe zduplikowane wiersze.

Pomimo swojej nazwy, dodatek może szybko podświetlić duplikaty w różnych kolorach bez ich usuwania.

Dodatek Duplicate Remover dodaje 3 nowe funkcje do wstążki Excela:

• Dedupe Table – aby natychmiast znaleźć i podświetlić duplikaty w jednej tabeli.
• Duplicate Remover – kreator krok po kroku z zaawansowanymi opcjami do identyfikacji i podświetlania duplikatów lub unikalnych wartości w 1 tabeli.
• Compare 2 Tables – znajdź i podświetl duplikaty, porównując dwie kolumny lub dwie oddzielne tabele.

Po zainstalowaniu Ultimate Suite for Excel znajdziesz te narzędzia na karcie Ablebits Data w grupie Dedupe:

Podświetlanie duplikatów w Excelu za pomocą kilku kliknięć

Dla tego przykładu utworzyłem następującą tabelę z kilkuset wierszami. A naszym celem jest podświetlenie zduplikowanych wierszy, które mają równe wartości we wszystkich trzech kolumnach:

Uwierz lub nie, ale możesz uzyskać pożądany rezultat za pomocą zaledwie 2 kliknięć myszą 🙂

1. Z zaznaczoną dowolną komórką w tabeli kliknij przycisk Dedupe Table, a sprytny dodatek odbierze całą tabelę.
2. Okno dialogowe Dedupe Table otworzy się z automatycznie zaznaczonymi wszystkimi kolumnami i domyślnie wybraną opcją Color duplicates. Wystarczy więc tylko kliknąć OK 🙂 Gotowe!

Wskazówka. Jeśli chcesz wykryć zduplikowane wiersze według jednej lub więcej kolumn, usuń zaznaczenie wszystkich nieistotnych kolumn i pozostaw wybraną tylko kluczową kolumnę (lub kolumny).

A wynik wyglądałby podobnie do tego:

Jak widać na powyższym obrazie, narzędzie Dupe Table podświetliło zduplikowane wiersze bez pierwszych wystąpień.

Jeśli chcesz podświetlić duplikaty, w tym pierwsze wystąpienia, lub jeśli chcesz pokolorować unikalne rekordy, a nie duplikaty, lub jeśli nie podoba Ci się domyślny czerwony kolor, a następnie użyj Kreatora usuwania duplikatów, który ma wszystkie te funkcje i wiele więcej.

Wyświetl duplikaty w Excelu za pomocą zaawansowanego kreatora krok po kroku

W porównaniu z szybkim narzędziem Dedupe Table kreator Duplicate Remover wymaga kilku kliknięć więcej, ale nadrabia to wieloma dodatkowymi opcjami. Pokażę Ci go w działaniu:

1. Zaznacz dowolną komórkę w tabeli, w której chcesz zaznaczyć duplikaty, i kliknij przycisk Duplicate Remover (Usuwanie duplikatów) na wstążce. Zostanie uruchomiony kreator, a cała tabela zostanie zaznaczona. Dodatek zasugeruje również utworzenie kopii zapasowej tabeli, tak na wszelki wypadek. Jeśli nie jest to potrzebne, usuń zaznaczenie tego pola.

   Sprawdź, czy tabela została wybrana poprawnie i kliknij przycisk Dalej.

   

2. Wybierz jeden z następujących typów danych, które chcesz znaleźć:
   • Duplikaty z wyjątkiem pierwszego wystąpienia
   • Duplikaty z pierwszym wystąpieniem
   • Wartości unikalne
   • Wartości unikalne i pierwsze wystąpienie duplikatu

   Dla tego przykładu znajdźmy Duplikaty + pierwsze wystąpienie:
   

3. Teraz wybierz kolumny do sprawdzenia pod kątem duplikatów. Ponieważ chcemy zaznaczyć całe zduplikowane wiersze, wybrałem wszystkie 3 kolumny.
   

   Dodatkowo dodatek pozwala określić, czy tabela ma nagłówki i czy chcesz pominąć puste komórki. Obie opcje są domyślnie zaznaczone.

4. Na koniec wybierz akcję, która ma zostać wykonana na duplikatach. Masz kilka opcji, takich jak zaznaczanie, usuwanie, kopiowanie, przenoszenie duplikatów lub dodawanie kolumny statusu w celu identyfikacji duplikatów.

Ponieważ dzisiaj badamy różne sposoby zaznaczania duplikatów w Excelu, nasz wybór jest oczywisty 🙂 Tak więc wybierz opcję Wypełnij kolorem i wybierz jeden ze standardowych kolorów motywu lub kliknij przycisk Więcej kolorów… i wybierz dowolny niestandardowy kolor RGB lub HSL.

Kliknij przycisk Zakończ i ciesz się wynikiem 🙂

Tak właśnie podświetlasz duplikaty w Excelu za pomocą naszego dodatku Duplicate Remover. Jeśli jesteś ciekaw wypróbowania tego narzędzia na własnych arkuszach, zapraszamy do pobrania w pełni funkcjonalnej wersji próbnej pakietu Ultimate Suite, który zawiera wszystkie nasze narzędzia do oszczędzania czasu dla Excela. A twoja opinia w komentarzach będzie bardzo cenna!

Jeśli jesteś zadowolony z działania Duplicate Remover, cieszymy się, że możemy dać ci tę wyjątkową okazję:

Możesz być również zainteresowany

• Jak zidentyfikować duplikaty w Excelu (w 1 tabeli)
• Jak porównać 2 kolumny lub tabele w poszukiwaniu dopasowań i różnic
• Jak usunąć duplikaty wierszy w Excelu

.