Czyli trochę o logice w formatowaniu warunkowym

Pisałam już o tym, jak wykryć duplikaty na podstawie 2 kolumn – możesz o tym przeczytać tutaj. I własnie pod tym wpisem pojawiło się ciekawe pytanie:

Jak zaznaczyć tylko duplikaty powyżej, a ostatnie wystąpienie zostawić bez formatowania?

Przyszły mi do głowy 2 rozwiązania, jedno z nich idealnie odpowiadające na to pytanie, a drugie… w sumie na inne :). O pierwszym będzie dzisiaj, a o drugim kolejnym razem.

Czyli będziemy osiągać taki efekt:

Formatka

Polecam całe rozwiązanie umieścić w obiekcie Tabela, aby było ono dynamiczne. W końcu chcemy dopisywać nowe dane i na nich też ma działać. Umożliwi nam to właśnie obiekt tabela. Jego tworzenie omawiałam np. tutaj.

Logika rozwiązania

Do rozwiązania tego problemu zdecydowanie będziemy pisać formułę. Bardziej rozbudowaną niż ostatnim razem, ale wykorzystującą jej elementy. Tak, jak poprzednio – chcemy wychwycić wszystkie powtarzające się projekty dla tego samego klienta. Duplikatem będzie więc powtarzająca się kombinacja projektu i klienta (2 kolumn).

Należy więc ustalić:

1. Ile w ogóle jest powtarzających się wpisów (duplikatów). Mają bowiem zostać zaznaczone wszystkie dotychczasowe, niezależnie od ich ilości.
2. Z którym wystąpieniem danego wpisu mamy do czynienia w konkretnym wierszu?

Jeśli numer wystąpienia w wierszu jest mniejszy niż wszystkie wystąpienia tego duplikatu – powinniśmy ten wiersza zaznaczać. I to cała logika zadania :).

Formuła

Nasza formuła będzie się składać z 2 części – jedną z nich (które wystąpienie) opisywałam wcześniej, jednak wszystko tutaj przypomnę.

1. Zacznijmy od policzenia ile w ogóle rekordów się powtarza. Policzy to następująca funkcja, którą roboczo wpiszę do komórki I4 na mojej formatce::

=LICZ.WARUNKI($B$4:$B$10;$B4;$C$4:$C$10;$C4)

Sprawdza ona, ile razy w kolumnie z projektami ($B$4:$B$10) wystąpił dany projekt (wpis z B4) przy równoczesnym wystąpieniu takiego samego klienta (wpis z C4) w kolumnie z klientami ($C$4:$C$10). Funkcja ich po prostu policzy. Jeśli takich wpisów będzie więcej niż 1 – dla nas oznacza to duplikat.

2. Ok, teraz możemy policzyć, z którym wystąpieniem mamy do czynienia w konkretnym wierszu. Zrobi to  taka funkcja (omówiona szczegółowo tutaj), roboczo wpiszę ją do komórki J4:

=LICZ.WARUNKI($B$4:$B4;$B4;$C$4:$C4;$C4)

Będzie to możliwe dzięki sprytnie zablokowanym zakresom: bez dolara obok dolnego wiersza zakresu, co umożliwia powiększanie.

Formuły robocze

3. Teraz zostaje kwestia porównania i to zrobi dla nas funkcja JEŻELI. Chciałabym, aby najpierw sprawdziła ona czy w ogóle duplikat jest. Jeśli tak – nastąpi sprawdzenie, czy występuje on jako ostatni. Jeśli tak – zostanie zwrócona wartość PRAWDA, jeśli nie – FAŁSZ.

Trzeci argument JEŻELI zostawimy pusty – jeśli więc będzie konieczne jego użycie – funkcja zwróci wartość FAŁSZ, co dla formatowania warunkowego będzie sygnałem, aby nie formatować komórki.

Formuła robocza będzie więc następująca (roboczo w K4):

=JEŻELI(I4>1;J4<I4)

4. Jest bardzo krótka w tej postaci. Ponieważ jednak odwołuje się ona do kolumn pośredniczących, których nie chcemy w docelowym rozwiązaniu, podstawimy wcześniej napisane formuły do komórek, które biorą w niej udział (więcej o sposobach zagnieżdżania funkcji omawiałam podczas tego webinaru). Docelowa formuła wygląda tak (wpisałam ją roboczo do komórki L4):

=JEŻELI(LICZ.WARUNKI($B$4:$B$9;$B4;$C$4:$C$9;$C4)>1;LICZ.WARUNKI($B$4:$B4;$B4;$C$4:$C4;$C4)<LICZ.WARUNKI($B$4:$B$9;$B4;$C$4:$C$9;$C4))

Wszystkie kolumny robocze

5. Teraz pozostaje już tylko kwestia formatowania warunkowego.

Przed pracą z formatowaniem warunkowym warto sobie skopiować ostatnią formułę, którą napisaliśmy w komórce L4. W tym celu koniecznie trzeba wejść do edycji tej komórki (np. do paska formuły) i skopiować treść formuły.

1. Chcemy, aby podświetlane były projekty i klienci. Zanim uruchomimy kreator formatowania warunkowego – zaznaczamy zakres, który należy sformatować, czyli B4:C9.
2. Idziemy do Narzędzia główne/Formatowanie warunkowe/Nowa reguła…/Użyj formuły do określenia komórek, które należy sformatować
3. I wklejamy tam skopiowaną wcześniej formułę:
   

   Tworzenie reguły formatowania warunkowego

4. Koniecznie ustawiamy też format wyróżnianych komórek (u mnie to fioletowy kolor wypełnienia)
5. I zatwierdzamy wszystko po kolei.

Aby zobaczyć działanie naszego cuda – wystarczy podmienić lub dopisać dane tak, aby stworzyć duplikaty:

I gotowe! Mam nadzieję, że to rozwiązanie Ci się przyda. Jeśli tak – miło mi będzie, jak podasz dalej ten wpis. Może być w formie maila, wpisu na Facebooku czy jakikolwiek inny sposób. Pomóż mi szerzyć przydatną excelową wiedzę! 🙂

Tutaj znajdziesz wersję wideo wpisu (dobrze widać pracę na kolumnach pośredniczących i kopiowanie formuły do formatowania warunkowego):

A tutaj plik do pobrania:

MalinowyExcel Duplikaty na podstawie 2 kolumn poprzedni dw.xlsx

Zobacz w sklepie powiązane produkty:

• WEBINAR: Formatowanie warunkowe
• WEBINAR: Zagnieżdżanie funkcji