Szukanie duplikatów w Excelu

Często zdarza się, że w naszych plikach Excela pewne dane się powtarzają. Czy to źle? To zależy. Przydaje się jednak je czasem znaleźć, żeby wiedzieć dokładnie co i ile razy się powtarza.

SPIS TREŚCI

Co jest duplikatem w Excelu

Chce teraz jasno określić, co rozumiem w Excelu przez słowo „duplikat”. Jest to komórka, która zawiera dokładnie taki sam tekst/liczbę/datę (jakąkolwiek wartość), co inna komórka.

Powyższy opis jest bardzo szeroki i traktując go dosłownie, można powiedzieć, że liczba „50” określająca wiek pewnej osoby i liczba „50” określająca kwotę na fakturze, to są duplikaty. Technicznie są nimi, ale w praktyce tak ich nie traktujemy bo nie duplikują żadnej informacji.

Zazwyczaj to, co opisujemy jako duplikaty, można opisać tak:

• • Zazwyczaj, jako duplikat, traktujemy powtarzające się wartości w ramach jednej tabeli – no bo co z tego, że nazwisko pewnej osoby powtarza się ale w zupełnie dwóch innych tabelach – występuje wtedy w zupełnie innym kontekście.
  • Zazwyczaj duplikaty występują w tej samej kolumnie (tej samej tabeli).
  • Wystarczy, że 2 komórki mają taką samą zawartość, ale może być ich dowolnie więcej – wszystkie wtedy nazwę duplikatem.
  • Są to wartości, które raczej nie powinny się powtarzać. Jeśli mamy tabelę z listą kupionych produktów i produkt o nazwie „ABCD” powtarza się tam 10 razy, to technicznie jest duplikatem, ale jego powtórzenie nie zwraca naszej uwagi – bo to przecież normalne że ten sam produkt mogło kupić 10 różnych klientów. Jeśli natomiast mamy listę pracowników i powtórzy nam się numer PESEL, to jest to „podejrzane” i warto przyjrzeć się takiemu duplikatowi.

Rysunek 1. Przykład duplikatów – powtarzające się numery PESEL na liście pracowników.

Skoro już wiesz, co mam na myśli, pisząc o duplikatach w Excelu, to w kolejnej sekcjach zobacz jak je wyróżnić i znaleźć.

Formatowanie warunkowe duplikatów w Excelu

Gdy podejrzewamy, że mamy w naszej tabeli duplikaty, to możemy użyć jednej z dwóch głównych opcji Excela, związanych z duplikatami:

1. 1. Usuwanie duplikatów – kliknij żeby przeczytać więcej na ten temat w osobnym wpisie naszego poradnika.
   2. Wyróżnienie duplikatów za pomocą formatowania warunkowego.

Opiszę teraz drugą z tych opcji. Jeśli masz wątpliwości czym jest formatowanie warunkowe lub chcesz poznać to narzędzie od podstaw to zapraszam Cię do naszego głównego artykułu na temat formatowania warunkowego w Excelu.

Wykonaj tych kilka prostych kroków:

1. 1. Zaznacz kolumnę (lub inna obszar komórek) gdzie szukasz duplikatów.
   2. Przejdź w górnym menu Excela do zakładki Narzędzia główne, a potem Formatowanie warunkowe. Następnie Reguły wyróżniania komórek i wybierz Duplikujące się wartości.
   3. W oknie, po prawej stronie, wybierz kolor, którym chcesz sformatować komórki. Możesz tam wybrać również formatowanie niestandardowe (swoje własne). Naciśnij OK. Gotowe.

Rysunek 2. Formatowanie warunkowe duplikatów w menu Excela.

Obojętnie jaki będzie efekt (czy znajdzie duplikaty, czy nie), nie zobaczysz żadnego komunikatu. Musisz własnoręcznie sprawdzić, czy Excel pokolorował jakieś komórki, które komórki, ile ich jest. W kolejnej sekcji przeczytasz jak to zrobić.

Znalezienie zduplikowanych wartości

Gdy masz już, za pomocą formatowania warunkowego, pokolorowane duplikaty, to najłatwiej je znaleźć za pomocą filtru. Wstaw filtr w swojej tabeli (zaznacz ją i przejdź w menu Excela do zakładki Dane – przycisk Filtruj). Następnie przejdź do kolumny, gdzie mogą być duplikaty, otwórz filtr i wybierz opcję Filtruj według koloru.

Rysunek 3. Filtrowanie zduplikowanych wartości.

Jeśli jest ona wyszarzona, czyli niedostępna, to znaczy, że w kolumnie nie ma żadnych kolorów, czyli nie ma żadnych duplikatów. W przeciwnym razie, wybierz jeden z kolorów, które się tam pojawią. Wówczas, zobaczysz w tabeli wszystkie komórki, które zostały pokolorowane – czyli wszystkie duplikaty.

Szukanie unikatowych wartości w Excelu

Skoro wiemy jak szukać powtórzonych wartości, to warto też powiedzieć jak wyróżnić te wartości, które występują w kolumnie Excela tylko jeden raz – czyli unikaty.

Postępuj bardzo podobnie, jak w przypadku kolorowania duplikatów, czyli:

1. 1. Zaznacz kolumnę (lub inna obszar komórek) gdzie szukasz unikatów.
   2. Przejdź w górnym menu Excela do zakładki Narzędzia główne, a potem Formatowanie warunkowe. Następnie Reguły wyróżniania komórek i wybierz Duplikujące się wartości.
   3. Tym razem, w oknie, po lewej stronie, wybierz Unikatowe zamiast Zduplikowane.
   4. W oknie, po prawej stronie, wybierz kolor, którym chcesz sformatować komórki. Możesz tam wybrać również formatowanie niestandardowe (swoje własne). Naciśnij OK.

Rysunek 4. Wybór unikatowych wartości w formatowaniu warunkowym.

Formatowanie zduplikowanych wierszy

Do tej pory mówiłem szukaniu duplikatów, które rozumiałem jako pojedyncze, powtarzające się komórki. A co jeśli duplikatem dla Ciebie jest cały wiersz. Czyli jedna komórka, która powtarza się w kolumnie, nie jest warta Twojej uwagi, ale jeśli wszystkie komórki w pewnym wierszu są dokładnie takie samej jak komórki z innego wiersza – to wtedy chcesz je wyróżnić.

Rysunek 5. Zduplikowane całe wiersze tabeli Excela.

Nie można tego zrobić za pomocą standardowego formatowania warunkowego. Może jednak użycia formatowania warunkowego z formułami. Aby tego dokonać, posłużę się kolumną pomocniczą.

Kolumna pomocnicza

Aby ułatwić sobie pracę, stworzę obok mojej tabeli kolumnę, która będzie łączyła w sobie wartości ze wszystkich kolumn. Zakładając, że moja tabela ma 4 kolumny, mogę zapisać w kolejnej kolumnie taką formułę:

=POŁĄCZ.TEKSTY(",";FAŁSZ;A2:D2)

Rysunek 6. Formuła, która tworzy kolumnę pomocniczą.

Pierwszy argument, czyli przecinek, wskazuje, że wartości z kolejnych kolumn mają być rozdzielone przecinkiem. Drugi argument jest mało istotny. Trzeci argument, to komórki kolejnych kolumn, które mają być połączone. W efekcie, ostatnia kolumna zawiera wartości wszystkich poprzednich kolumn.

Przeciągnij tę formułę do końca tabeli. Co w ten sposób osiągnęliśmy? Doprowadziliśmy do tego, że możemy całą tabelę przeanalizować, analizując pojedynczą kolumnę – czyli tak, jak robiliśmy to wcześniej! Więc jesteśmy w domu.

Gdy znajdziesz duplikaty w tej nowej kolumnie, to możesz, albo pokolorować komórkę tylko w tej nowej kolumnie, albo cały wiersz tabeli. Obydwa rozwiązania opisuję poniżej.

Formatowanie tylko nowej kolumny

Postępuj tak jak wcześniej, czyli:

1. 1. Zaznacz nową kolumnę.
   2. Przejdź w górnym menu Excela do zakładki Narzędzia główne, a potem Formatowanie warunkowe. Następnie Reguły wyróżniania komórek i wybierz Duplikujące się wartości.
   3. W oknie będzie zaznaczona opcja duplikatów, więc tylko wybierz formatowanie. Naciśnij OK.

Teraz, patrząc na nową kolumnę, widzisz które z wierszy całkowicie się duplikują.

Rysunek 7. Formatowanie warunkowe wyróżniło komórki tylko w kolumnie pomocniczej.

Formatowanie całego zduplikowanego wiersza

W tym przypadku, nie dość, że użyliśmy formuły do stworzenia dodatkowej kolumny w tabeli Excela, to dodatkowo, użyjemy formuły w samym formatowaniu warunkowym:

• • Zaznacz całą tabelę, wraz z nową kolumną. Najlepiej zacznij zaznaczać od lewego, górnego rogu tabeli. Jest to najbardziej naturalny kierunek zaznaczania, a w przypadku tego formatowania warunkowego, ten kierunek ma duże znaczenie.
  • Przejdź w górnym menu Excela do zakładki Narzędzia główne, a potem Formatowanie warunkowe. Następnie Nowa reguła.
  • W oknie, wybierz opcję Użyj formuły do określenia komórek, które należy sformatować.
  • Ja zaznaczyłem moje komórki od lewego, górnego rogu, czyli od komórki A2. W związku z tym, kluczowy w mojej formule będzie wiersz nr 2. Wpisz formułę:

=LICZ.JEŻELI($E$2:$E$15;$E2)>1

• • Kilka słów na temat formuły:
    • Użyłem LICZ.JEŻELI, która liczy ilość wystąpień. Jeśli pewna wartość występuje więcej niż 1 raz, to jest duplikatem.
    • Zliczam to co znajduje się w kolumnie E, bo tam mam moją nową kolumnę.
  • Nie zapomnij wybrać jakiegoś formatowania w tym oknie. Naciśnij OK.

Rysunek 8. Formuła formatowania warunkowego, która wyróżnia zduplikowane wiersze.

Rysunek 9. Efekt formatowania warunkowego całych wierszy.