Czyli jak zaznaczyć dane, jeśli jest spełnione kilka warunków jednocześnie?

Na ostatnim webinarze, o formatowaniu warunkowym, zapytaliście czy można zrobić część wspólną warunków. Czyli przykładowo, jak mamy dwa warunki i jeden koloruje na różowo, a drugi na szaro, to żeby ich część wspólna, czyli komórki spełniające oba te warunki, była przenikającym się kolorem różowo-szarym. Czyli chodzi o coś takiego:

Wbudowanej funkcjonalności, która robi dokładnie coś takiego, nie znalazłam. Natomiast znalazłam sposób, żeby sobie z tym poradzić :). O tym dalej we wpisie!

Formatka wygląda tak, jak na obrazku wcześniej. Potrzebne nam są dane ID wysyłki, nazwa towaru i inne dane, nie ma znaczenia ile ich jest. W ID wysyłki zakodowany jest rok i miesiąc wysyłki oraz jakiś jej numer, który nas tutaj nie interesuje. Czekolady mają w swojej nazwie słowo „czekolada” i po tym je rozpoznamy (idealnie byłoby mieć kolumnę Rodzaj towaru, ale jej nie mamy, więc radzimy sobie inaczej).

Zadanie polega na tym, aby wszystkie id wysyłki z marca zaznaczyć na różowo, i wszystkie towary, będące czekoladą – na szaro. Natomiast jeśli towar jest czekoladą i był wysłany w marcu – chcemy mieć przenikające się oba kolory i to całego wiersza.

Wysyłki z marca

Najpierw nadajmy 2 pierwsze reguły. Te łatwiejsze ;). Aby zaznaczyć na różowo wszystkie wysyłki z marca w kolumnie ID wysyłki  należy:

1. Zaznaczyć tę kolumnę, żeby Excel wiedział, że właśnie ją ma formatować.

2. Następnie wybrać Narzędzia główne/ Formatowanie warunkowe/ Reguły wyróżniania komórek/ Tekst zawierający

3. W okienku, które się pojawi, wpisujemy, że tekst ma zawierać /03/, natomiast format wybieramy Format niestandardowy, ponieważ umożliwi nam to określenie swojego koloru:

Określanie reguły dla marca

A tutaj określamy ten kolor:

Wybieranie koloru

4. Po zatwierdzeniu wszystkich okienek efekt mamy taki:

Transakcje z marca – wynik

Teraz podobnie dla czekolad….

Towary: czekolada

Tutaj logika będzie identyczna, czyli szukamy tym razem produktów, które będą zawierały słowo: czekolada. Czyli znowu:

1. Zaznaczamy kolumnę Towar,

2. Następnie  Narzędzia główne/ Formatowanie warunkowe/ Reguły wyróżniania komórek/ Tekst zawierający

3. W okienku, które się pojawi, wpisujemy, że tekst ma zawierać czekolada (wielkość liter nie ma znaczenia), i znów Format niestandardowy, żeby znaleźć nasz kolor. Ja wybrałam szary.

Po zatwierdzeniu wszystkiego mamy już 2 różne warunki:

2 różne warunki

To teraz najtrudniejsza część…

Część wspólna: czekolady z marca

Czyli deseń. Tak na prawdę ten deseń to oddzielna, trzecia, reguła formatowania warunkowego. Reguła, która sprawdza czy dwa wcześniejsze warunki na raz są spełnione. Natomiast nie da się już jej „wyklikać” tak, jak robiliśmy to wcześniej. Trzeba teraz napisać formułę, która będzie nie dość, że sprawdzała oba wcześniejsze warunki, to jeszcze formatowała cały wiersz.

Żeby formuła sprawdzała 2 warunki jednocześnie, musimy użyć funkcji ORAZ. Natomiast w niej po kolei sprawdzimy wcześniejsze warunki:

Pierwszy z nich sprawdzi, czy transakcja była w marcu. Można to bardzo prosto sprawdzić poprzez funkcję FRAGMENT.TEKSTU. Nasz miesiąc zawsze jest na 8 pozycji w Id wysyłki i zawsze ma 2 znaki. Tak więc sprawdzimy, czy ten wycinek ID to marzec:

=FRAGMENT.TEKSTU($B6;8;2)="03"

A teraz sprawdzimy, czy w nazwie towaru znajduje się czekolada. To już będzie ciut trudniejsze, ponieważ to słowo nie zawsze występuje na identycznej pozycji… Na szczęście istniej funkcja SZUKAJ.TEKST, która umie określić pozycję, na której występuje dany tekst. Jeśli występuje. Jeśli nie występuje – zwróci błąd, co dla nas jest równoznaczne z tym, że warunek jest fałszywy. Funkcja ta potrzebuje od nas jedynie tekstu, który szukamy i tekstu, w którym szukamy. Warunek sprawdzający czekoladę będzie więc taki:

=SZUKAJ.TEKST("czekolada";$C6)>0

Tak na prawdę można byłoby pominąć porównanie „>0”, ponieważ Excel potraktuje jakąkolwiek dodatnią liczbę jako wartość logiczną PRAWDA, a tylko to jest potrzebne funkcji ORAZ do działania (na co zwrócił mi uwagę Adam Kopeć, za co mu bardzo dziękuję!). Cyzli może też być tak:

=SZUKAJ.TEKST("czekolada";$C6)

Całość, wrzucone do funkcji ORAZ, czyli to, co powinno się znaleźć w formatowaniu warunkowym, wygląda tak:

=ORAZ(FRAGMENT.TEKSTU($B6;8;2)="03";SZUKAJ.TEKST("czekolada";$C6))

Teraz trzeba to tylko wpisać do okienka formatowania warunkowego, czyli: Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła/ Użyj formuły do określenia komórek, które należy sformatować:

Określanie reguły dla części wspólnej warunków

i oczywiście wybrać formatowanie:

Żeby osiągnąć deseń…

Wybieranie deseniu:

Deseń

Efekt wszystkiego otrzymujemy taki:

Wynik: 3 reguły

Warto jeszcze sprawdzić, czy kolejność warunków jest dobra. W tym celu należy wejść do menedżera reguł, czyli Narzędzia główne/ Formatowanie warunkowe/ |Zarządzaj regułami. Kolejność powinna być taka, że warunek sprawdzający część wspólną warunków powinien być pierwszy:

Prawidłowa kolejność reguł

Jeśli robisz w takiej kolejności jak ja, to kolejność będzie dobra. Natomiast jeśli coś by nie zadziałało – najpierw sprawdź właśnie kolejność :).

Ta dam! I wszystko. Mam nadzieję, że było pomocne. Zachęcam Cię też do obejrzenia poniższego filmu, ponieważ tam krok po kroku pokazuję jak to wszystko zrobić. A trochę tego jest… 😉