Duplikaty w danych można znajdować za pomocą formatowania warunkowego i funkcji LICZ.JEŻELI. Poniżej przedstawiono szczegółowe informacje na ten temat.

Budowanie pierwszej formuły formatowania warunkowego

Zaczniemy od utworzenia formatu warunkowego dla pierwszej komórki danych. Później skopiujemy ten format do całego zakresu komórek.

W naszym przykładzie komórka A1 zawiera nagłówek kolumny (Faktura), więc zaznacz komórkę A2 i kliknij polecenie Formatowanie warunkowe w menu Format. Zostanie wyświetlone okno dialogowe Formatowanie warunkowe. Pierwsze pole tego okna zawiera tekst Wartość komórki jest. Klikając strzałkę obok tego pola, możesz wybrać pozycję Formuła jest.

Po kliknięciu pozycji Formuła jest okno dialogowe zmieni wygląd. Zamiast pól dla wyrażenia międzyx I y zostanie wyświetlone jedno pole przeznaczone na jedną formułę. Daje ono ogromne możliwości. Można w nim wpisać dowolną formułę, jaką można tylko sobie wymyślić, o ile tylko formuła ta będzie dawała w wyniku wartości PRAWDA lub FAŁSZ.

W tym przypadku musimy użyć funkcji LICZ.JEŻELI. Formuła przeznaczona do wpisania w tym polu jest następująca:

 =LICZ.JEŻELI(A:A;A2)>1 

Formułę tę można streścić tak: przejrzyj cały zakres komórek kolumny A. Policz, ile komórek z tego zakresu ma taką samą wartość co komórka A2. Następnie sprawdź, czy obliczony wynik jest większy niż 1.

Gdy nie ma duplikatów, wynik wynosi zawsze 1; ponieważ komórka A2 znajduje się wewnątrz zakresu, w całej kolumnie powinna znajdować się dokładnie jedna komórka równa co do wartości komórce A2.

 Uwaga   W tej formule A2 oznacza bieżącą komórkę, czyli komórkę, dla której opracowujesz format warunkowy. Jeśli więc dane znajdują się w kolumnie E i zaczniesz tworzenie formatu warunkowego od komórki E5, formuła będzie mieć postać: =LICZ.JEŻELI(E:E;E5)>1.

Wybór koloru wyróżniającego zduplikowane pozycje

Teraz czas wybrać jakiś okropny (nietypowy, rzucający się w oczy) format, który będzie identyfikował znalezione duplikaty. W oknie dialogowym Formatowanie warunkowe kliknij przycisk Formatuj.

Kliknij kartę Desenie i kliknij próbkę jakiegoś jaskrawego koloru, na przykład czerwonego czy żółtego. Następnie kliknij przycisk OK, aby zamknąć okno dialogowe Formatowanie komórek.

Zobaczysz wybrany kolor w polu podglądu. Kliknij przycisk OK, aby zamknąć okno dialogowe Formatowanie warunkowe i…

Nic się nie dzieje. Ojej. Jeśli pierwszy raz konstruujesz formatowanie warunkowe, przydałby się jakiś sposób potwierdzenia, że ono działa. Ale jeśli nie masz tyle szczęścia, że wartość w komórce A2 będzie powieleniem jakiejś innej w kolumnie, warunek da w wyniku wartość FAŁSZ i formatowanie wyróżniające nie zostanie zastosowane.

Kopiowanie formatowania warunkowego do pozostałych komórek

Należy skopiować formatowanie warunkowe z komórki A2 w dół do pozostałych komórek zakresu. Mając kursor wciąż w komórce A2, kliknij polecenie Kopiuj w menu Edycja. Naciśnij kombinację klawiszy CTRL+Spacja, aby zaznaczyć całą kolumnę. Następnie kliknij polecenie Wklej specjalnie w menu Edycja. W oknie dialogowym Wklej specjalnie kliknij przycisk radiowy Formaty, a następnie kliknij przycisk OK.

Spowoduje to skopiowanie formatowania warunkowego do wszystkich komórek w kolumnie. Teraz możemy w końcu zobaczyć kilka pokolorowanych komórek wskazujących duplikaty.

Pouczające będzie sprawdzenie komórki A3 i zajrzenie do jej formuły warunkowej (po skopiowaniu z komórki A2). Zaznacz komórkę A3 i kliknij polecenie Formatowanie warunkowe w menu Format. Formuła w polu Formuła jest została zmodyfikowana tak, aby zliczać wystąpienia danych z komórki A3 w kolumnie A.

Można używać do 65536 komórek z formatowaniem warunkowym, z których każda będzie porównywać komórkę bieżącą z 65535 innymi. Technicznie rzecz biorąc, formuła w pierwszym kroku mogłaby mieć postać =LICZ.JEŻELI($A$2:$A$1751;A2)>1.

Dodatkowo podczas kopiowania formatowania warunkowego do całej kolumny można przed użyciem polecenia Wklej specjalnie zaznaczyć tylko komórki zawierające dane.

Wyróżnianie tylko drugiego wystąpienia duplikatu

W poprzednim rozwiązaniu założono, że wyróżniane są oba wystąpienia powtarzających się numerów faktur, aby można było ręcznie sprawdzić, które z nich należy usunąć lub poprawić. Aby nie wyróżniać pierwszego wystąpienia duplikatów, wystarczy poprawić formułę na następującą:

 =LICZ.JEŻELI($A$2:$A2;A2)>1 

 Uwaga    Ważne jest, aby znaki dolara były rozmieszczone dokładnie tak, jak pokazano.

W pierwszym argumencie tej formuły zmienia się tylko drugie odwołanie do zakresu danych, gdy formuła jest kopiowana w dół kolumny. Oznacza to, że w ramach wyszukiwania duplikatów porównywane będą tylko komórki leżące między bieżącą komórką a pierwszą komórką zakresu.

Sortowanie danych

Nie można wprost sortować kolumny na postawie formatowania warunkowego. Jeśli chcesz posortować dane w taki sposób, aby duplikaty znalazły się w jednym miejscu, wykonaj następujące czynności:

Najpierw wpisz w komórce B1 nagłówek Duplikat. Następnie wpisz w komórce B2 następującą formułę:

 =LICZ.JEŻELI(A:A;A2)>1 

Mając kursor w komórce B2, kliknij dwukrotnie uchwyt Autowypełniania (mały kwadrat w prawym dolnym rogu komórki), aby skopiować formułę w dół do końca kolumny.

Teraz możesz posortować dane według kolumny B (malejąco), a następnie według kolumny A (rosnąco), aby wyświetlić zduplikowane numery faktur na samej górze.

Źródło : Microsoft