Wczoraj wieczorem w programie radiowym Craiga Crossmana Computer America Joe z Bostonu miał pytanie:
Mam kolumnę z numerami faktur. Jak mogę używać programu Excel do oznaczania duplikatów?
Zaproponowałem użycie formatów warunkowych i wzoru LICZ.JEŻELI. Oto szczegóły, jak to działa.
Chcemy ustawić formatowanie warunkowe dla całego zakresu, ale łatwiej jest ustawić format warunkowy dla pierwszej komórki w zakresie, a następnie skopiować ten format warunkowy. W naszym przypadku komórka A1 ma nagłówek numeru faktury, więc wybiorę komórkę A2 iz menu wybierz Format> Formatowanie warunkowe. Okno dialogowe Formatowanie warunkowe zaczyna się od początkowego menu rozwijanego z napisem „Wartość komórki to”. Jeśli dotkniesz strzałki obok tego, możesz wybrać „Formuła to”.
Po wybraniu opcji „Formuła jest” okno dialogowe zmienia wygląd. Zamiast pól „Między x a y” jest teraz jedno pole formuły. Ta formuła jest niesamowicie potężna. Możesz wpisać dowolną formułę, o której możesz marzyć, o ile ta formuła zostanie oceniona jako PRAWDA lub FAŁSZ.
W naszym przypadku musimy użyć formuły LICZ.JEŻELI. Formuła do wpisania w polu to
=COUNTIF(A:A,A2)>1
W języku angielskim oznacza to „przejrzyj cały zakres kolumny A. Policz, ile komórek w tym zakresie ma tę samą wartość, co komórka A2. (Jest naprawdę ważne, aby„ A2 ”we wzorze wskazywał na bieżąca komórka - komórka, w której ustawiasz formatowanie warunkowe. Więc - jeśli twoje dane znajdują się w kolumnie E i ustawiasz pierwsze formatowanie warunkowe w E5, formuła będzie wyglądać tak =COUNTIF(E:E,E5)>0). Następnie porównujemy, aby sprawdzić, czy to się liczy jest> 1. W idealnym przypadku, bez duplikatów, liczba zawsze będzie wynosić 1 - ponieważ komórka A2 jest w zakresie - powinniśmy znaleźć dokładnie jedną komórkę w kolumnie A, która zawiera tę samą wartość co A2.
Kliknij przycisk Format…
Nadszedł czas, aby wybrać okropny format. U góry tego okna dialogowego Formatowanie komórek znajdują się trzy zakładki. Zakładka Czcionka jest zwykle pierwsza, więc możesz wybrać pogrubioną, czerwoną czcionkę, ale podoba mi się coś bardziej nieprzyjemnego. Zwykle klikam kartę Wzory i wybieram jasnoczerwony lub jasnożółty. Wybierz kolor, a następnie kliknij przycisk OK, aby zamknąć okno dialogowe Formatowanie komórek.
Wybrany format zostanie wyświetlony w oknie „Podgląd formatu do użycia”. Kliknij OK, aby zamknąć okno dialogowe Formatowanie warunkowe…
… I nic się nie dzieje. Łał. Jeśli po raz pierwszy konfigurujesz formatowanie warunkowe, byłoby naprawdę miło uzyskać tutaj opinię, że zadziałało. Ale jeśli nie masz szczęścia, że 1098 w komórce A2 jest duplikatem innej komórki, warunek nie jest prawdziwy i wygląda na to, że nic się nie stało.
Musisz skopiować formatowanie warunkowe z A2 do innych komórek w swoim zakresie. Po ustawieniu kursora w pozycji A2 wykonaj polecenie Edycja> Kopiuj. Naciśnij Ctrl + Spacja, aby zaznaczyć całą kolumnę. Zrób Edycja> Wklej specjalnie. W oknie dialogowym Wklej specjalnie kliknij opcję Formaty. Kliknij OK.
Spowoduje to skopiowanie formatowania warunkowego do wszystkich komórek w kolumnie. Teraz - w końcu - widzisz komórki z czerwonym formatowaniem, co oznacza, że masz duplikat.
Dobrze jest przejść do komórki A3 i przyjrzeć się formatowi warunkowemu po skopiowaniu. Wybierz A3, naciśnij od, aby wywołać formatowanie warunkowe. Formuła w polu Formuła jest tak zmieniona, aby policzyć, ile razy A3 pojawia się w kolumnie A: A.
Uwagi
W pytaniu Joe miał tylko 1700 faktur w zakresie. Skonfigurowałem 65536 komórek z formatowaniem warunkowym, a każda komórka porównuje bieżącą komórkę z 65536 innymi komórkami. W Excelu 2005 - z większą liczbą wierszy - problem będzie jeszcze gorszy. Formuła w pierwszym kroku mogłaby wyglądać następująco:=COUNTIF($A$2:$A$1751,A2)>1
Ponadto podczas kopiowania formatu warunkowego do całej kolumny można było wybrać tylko wiersze z danymi przed wykonaniem funkcji Wklej formaty specjalne.
Jeszcze
Inną kwestią, którą opisałem po pytaniu, jest to, że naprawdę nie można sortować kolumny na podstawie formatu warunkowego. Jeśli chcesz posortować te dane, aby duplikaty znalazły się w jednym obszarze, wykonaj następujące kroki. Najpierw dodaj nagłówek do B1 o nazwie „Duplikat?”. Wpisz tę formułę w B2: =COUNTIF(A:A,A2)>1.
Za pomocą wskaźnika komórki w B2 kliknij uchwyt autouzupełniania (mały kwadrat w prawym dolnym rogu komórki), aby skopiować formułę do końca zakresu.
Możesz teraz sortować według kolumny B malejąco i A rosnąco, aby faktury, których dotyczą problemy, znalazły się na górze zakresu.
To rozwiązanie zakłada, że chcesz podświetlić OBIE zduplikowane faktury, aby móc ręcznie określić, które usunąć lub poprawić. Jeśli nie chcesz, aby zaznaczyć pierwsze wystąpienie w dwóch egzemplarzach, można dostosować formułę być: =COUNTIF($A$2:$A2,A2)>1. Ważne jest, aby wprowadzić znaki dolara dokładnie tak, jak pokazano. Spowoduje to przeglądanie wszystkich komórek tylko od bieżącej komórki w górę, szukając zduplikowanych wpisów.
Dzięki Joe z Bostonu za pytanie!
