Minuty czytania: 4

Formatowanie warunkowe w zależności od zawartości innej komórki

Formatowanie warunkowe to jedna z podstawowych dróg, aby uatrakcyjnić Twoje arkusze. Jeśli to dla Ciebie nowy temat zachęcam do lektury poprzedniego artykułu. Przeczytasz w nim o najważniejszych możliwościach jakie daje to narzędzie.

W tym artykule przeczytasz o nieco bardziej zaawansowanych trickach, takich jak formatowanie warunkowe w zależności od komórki.

Proste formatowanie warunkowe

Formatowanie warunkowe umożliwia formatowanie komórek w zależności od zawartości danej komórki. Co to w praktyce oznacza? Wyobraź sobie, że chciałbyś żeby komórka zawierająca wartość sprzedaży zmieniała tło na zielone, w sytuacji kiedy wprowadzisz tam wartość powyżej 100 000 zł. Na takie operacje pozwala właśnie formatowanie warunkowe.

Nie jest to formatowanie statyczne tylko dynamiczne. To znaczy, że jest wykonywane „w locie”. Excel na bieżąco sprawdza zawartość komórki i w zależności od ustawionych warunków – włącza, bądź wyłącza formatowanie.

Co musisz zrobić aby skorzystać z tej funkcjonalności?

Po pierwsze, wskaż obszar na którym ma działać formatowanie warunkowe. Załóżmy, że mamy  rejestr sprzedaży w firmie. Chcielibyśmy teraz w naszej bazie sprzedaży, w grupie towaru wyróżnić jakąś konkretną grupę np. AGD.

W standardowy sposób musielibyśmy je najpierw posortować albo przefiltrować aby osiągnąć cel. Przy formatowaniu warunkowym nie musimy wykonywać żadnych dodatkowych czynności, aby wyróżnić komórki spełniające nasze warunki. Excel zrobi to za nas. 

Przejdźmy do praktycznego przykładu. Zaznacz zakres komórek, na którym ma działać formatowanie. Przejdź na NARZĘDZIA GŁÓWNE i z sekcji STYLE wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.

Formatowanie warunkowe - reguły wyróżniania komórek

Nasze formatowanie ma zadziałać wtedy, gdy w komórce pojawi się wyraz „AGD”. Zatem wybieramy opcję RÓWNE i wpisujemy warunek, czyli słowo AGD. Po prawej stronie mamy style formatowania. Możesz wybrać z kilku opcji stylów zdefiniowanych. Jeżeli żaden z nich nie spełnia Twoich oczekiwań, możesz wybrać FORMAT NIESTANDARDOWY i samodzielnie określić sposób formatowania.

Formatowanie warunkowe w zależności od komórki

Jeżeli chciałbyś teraz zmienić reguły powyższego formatowania i zamiast słowa „AGD” wybrać na przykład „Meble” musiałbyś przejść do menu formatowania warunkowego i edytować reguły.

Oczywiście jest lepszy sposób. Załóżmy, że chcesz teraz wyróżniać „kraj odbiorcy”.  Chcemy uniknąć „sztywnego” określania konkretnego kraju. Dlatego zrobimy parametr w wybranej komórce. Parametr oznacza, że będziesz mógł zmieniać zawartość tej komórki, a formatowanie warunkowe będzie uzależnione od wartości z tej komórki.

Jeśli np. wpiszesz „Polska” to „zapalą się” tylko te komórki, które zawierają to słowo. Zmieniając zawartość komórki na „Anglia” sformatujesz komórki które zawierają ten kraj.

Jak widzisz to dużo ciekawsze rozwiązanie i łatwiejsze w obsłudze rozwiązanie. Jak to zrobić?

  1. Zaznacz obszar, który chcesz sformatować.
  2. Ze wstążki NARZĘDZIA GŁÓWNE, wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.
  3. Wybierz opcję RÓWNE…
  4. Teraz nie wpisujesz statycznej nazwy kraju, tylko odwołujesz się do komórki, w której został umieszczony wspomniany wcześniej parametr.
  5. Pozostaw domyślny lub zmień styl formatowania.
  6. Zatwierdź wybór.

Teraz, gdy zmienisz wartość komórki – parametru, automatycznie spowoduje to zmianę formatowania na właściwych komórkach. Prawda, że bardzo elastyczne rozwiązanie?

Połącz formatowanie warunkowe z listą rozwijaną

W poprzednim przykładzie, w komórce która była parametrem wartości zmieniałeś ręcznie. Byłoby dobrze to nieco zautomatyzować.

Załóżmy, że masz tabelkę w której chciałbyś wyróżniać wybranych klientów. Nie wiesz ilu ich dokładnie jest, a tym bardziej nie pamiętasz ich wszystkich. Pomysł polega na tym, że utworzysz listę rozwijaną z nazwami klientów. Komórka, w której znajdzie się ta lista – będzie Twoim parametrem do formatowania warunkowego.

W pierwszej kolejności – przygotuj listę unikatowych klientów. Skopiuj wszystkich klientów w oddzielne miejsce. Teraz, korzystając z narzędzia USUŃ DUPLIKATY na wstążce DANE, przygotujesz listę unikatowych wartości.

Na podstawie tych wartości stwórz listę rozwijaną. O tym jak to zrobić przeczytasz w poprzednim artykule.

Teraz, gdy powiążesz komórkę z listą rozwijaną z formatowaniem warunkowym, będziesz mógł wybierać klientów z listy, a formatowania będą automatycznie się zmieniały.

Formatowanie warunkowe z warunkiem na liście rozwijanej

Formatowanie warunkowe, które podświetla cały wiersz

Byłoby jeszcze ciekawiej, gdyby po wybraniu parametru, w tabeli z danymi podświetlały się całe wiersze, które zawierają wybrany parametr. Zatem do dzieła.

  1. Zaznacz cały obszar z danymi. Robisz tak dlatego, że formatowanie ma dotyczyć całych wierszy.
  2. Przejdź do menu FORMATOWANIA WARUNKOWEGO i wybierz opcję NOWA REGUŁA. W standardowym menu nie ma opcji pozwalającej na podświetlenie całych wierszy. Musisz stworzyć własną regułę.
  3. W menu nowej reguły formatowania  wybierz opcję UŻYJ FORMUŁY DO OKREŚLANIA KOMÓREK, KTÓRE NALEŻY SFORMATOWAĆ.
  4. Teraz stwórz taką formułę, która gdy warunki formatowania zostaną spełnione to wynik formuły da PRAWDĘ, a w przeciwnym razie da FAŁSZ.

Załóżmy, że chcesz aby podświetlił Ci się cały wiersz wtedy, kiedy wpis w kolumnie klient, będzie równy temu co jest wpisane w komórce F1. Wpisz:

=$B7=$F$1

Taka formuła oznacza, że porównujesz zawartość komórki B7 (tam masz nazwę klienta) z Twoim parametrem w komorce F1. Zauważ, że wybierając komórkę B7, Excel domyślnie wstawił wszędzie dolary (czyli zmienił adresowanie komórki na bezwzględne) W tym momencie, to nie jest działanie prawidłowe dla naszych potrzeb. 

Chcemy stworzyć taką formułę, która dla kolejnych komórek w kolumnie B będzie sprawdzała czy wpis równa się zawartości komórki F1. Czyli dla całego obszaru zawsze będziemy odnosić się do komórki F1, ale porównywana komórka  będzie się zmieniać. To oznacza, że adres komórki w sprawdzanej kolumnie B musi mieć postać adresu mieszanego, czyli blokujemy kolumnę B znakiem $, ale numery wierszy pozostawiamy niezablokowane (bez znaku dolara)

Excel będzie przynosił sprawdzanie warunku na kolejne kolumny i gdybyśmy nie zablokowali kolumny B w naszym warunku porównywanie nie miałoby sensu. Dlaczego? Ponieważ kazalibyśmy Excelowi porównywać np. nazwę klienta z grupą towaru.

Dzięki temu, że zablokowaliśmy kolumnę B, w każdej kolejnej kolumnie sprawdzany jest właśnie ten warunek – właściwy dla tej kolumny.

Kolejne kroki są już analogiczne do poprzedniego postępowania. Wybierz FORMATUJ i ustaw preferowany przez Ciebie styl formatowania. Teraz, gdy wybierzesz z listy rozwijanej kolejnych klientów, na Twojej liście danych będą podświetlały się całe wiersze, właściwe dla wybranego klienta.


Podsumowanie

Formatowanie warunkowe daje całą masę możliwości na ciekawą wizualizację Twoich danych. Natomiast formatowanie w zależności od zawartości innej komórki, daje również opcję na interaktywność w arkuszu.

Jestem ciekawy jak Ty wykorzystujesz formatowanie warunkowe w swoich arkuszach. Podziel się Twoimi sposobami w komentarzu. A jeżeli artykuł pomógł Ci ulepszyć Twoje arkusze – podziel się nim proszę w Twoich mediach społecznościowych.


1 Comment

Tomek · 16 czerwca 2020 at 05:58

Fajny pomysł wykorzystania formatowania warunkowego do wyróżniania całych wierszy.

Dodaj komentarz

Avatar placeholder

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *