Formatowanie warunkowe

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Formatowanie Warunkowe.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel: ExcelSzkolenie.pl Cwiczenia Excel.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Pracując z arkuszami z dużą ilością danych bardzo czasochłonne staje się wyszukanie i zaznaczenie pewnych danych, lub znalezienie błędnych danych.

Dużym ułatwieniem w takich przypadkach jest formatowanie warunkowe.

Użyteczność formatowania warunkowego nie będzie aż tak widoczna na poniższych prostych przykładach, formatowanie warunkowe staje się na prawdę użyteczne dopiero w przypadku dużych tabel.

Przykład 1

(Arkusz: ‘Formatowanie Warunkowe 1’)

Na poniżej przedstawionej liście płac pracowników zajmujących się analizami w Excelu w pewnej firmie chcielibyśmy zaznaczyć tych, którzy zarabiają więcej niż 7000zł. Kwoty powyżej 7 tys. zł oznaczymy czerwonym tłem. Natomiast dla kwot poniżej 6500zł zmienimy kolor czcionki na zielony.

Przed włączeniem formatowania warunkowego należy zaznaczyć obszar, którego ma ono dotyczyć, pomijamy przy tym nagłówki wierszy i kolumn.

Formatowanie warunkowe znajdziemy na karcie ‘NARZĘDZIA GŁÓWNE’, wybieramy opcję ‘Reguły wyróżniania komórek’ a potem klikamy ‘Większe niż…’.

Excel sam zaproponuje kwotę i formatowanie. Kwotę zmieniamy na 7000zł.

A w menu formatowania wybieramy ‘Format niestandardowy…’.

W oknie ‘Formatowanie komórek’, które zostanie wyświetlone, wybieramy kolor czerwony na karcie ‘Wypełnienie’.

Inne parametry formatu, które pozwala zmienić to okno to: format liczb, czcionka (wielkość, kolor, dodatkowe efekty) oraz obramowanie komórki.

Po zaakceptowaniu, kolory komórek zostaną zmienione. Dodamy teraz 2 warunek. Wybieramy ‘Mniejsze niż…’.

Zmieniamy kwotę na 6500 i ponownie wybieramy Format niestandardowy.

Na karcie ‘Czcionka’ zmieniamy kolor na zielony i klikamy OK.

Kolor czcionki został odpowiednio zmieniony.

Efekt naszego formatowania powinien wyglądać tak jak poniżej.

Spróbujmy teraz zmienić tło na żółte dla komórki C8 (klikamy ją prawym klawiszem i wybieramy ikonę wypełnienia, następnie kolor żółty).

Komórki zmienione poprzez formatowanie warunkowe są ‘odporne’ na próby zwykłego formatowania. Można powiedzieć, że formatowanie warunkowe jest nadrzędne wobec zwykłego formatowania.

Jeśli chcemy usunąć formatowanie warunkowe, zaznaczamy obszar, na którym formatowanie ma być usunięte, wybieramy polecenie ‘Wyczyść reguły’ i klikamy ‘Wyczyść z zaznaczonych komórek’.

Polecenie znajdujące się poniżej pozwala usunąć formatowanie warunkowe z całego arkusza.

Żółte tło które próbowaliśmy dodać do komórki C8, stało się widoczne po usunięciu formatowania warunkowego.

Formatowanie to było więc cały czas ‘przykryte’ formatowaniem warunkowym.

Przykład 2

(Arkusz: ‘Formatowanie Warunkowe 2’)

Formatowanie warunkowe może być użyteczne do ukrywania błędów.

W poniższym przykładzie w kolumnie wzrost jest formuła obliczająca wzrost płacy. Dla pracowników, którzy nie pracowali w 2013 formuła pokazuje błędy, co nie wygląda dobrze.

Przed wybraniem formatowania warunkowego najlepiej jest zaznaczyć cały arkusz (klikając w miejsce oznaczone na powyższym rysunku czerwonym kwadratem).

Wybieramy polecenie ‘Nowa reguła…’.

Wybieramy ‘Formatuj tylko komórki zawierające’.

A następnie z menu rozwijanego ‘Zawierające błędy’.

Po kliknięciu przycisku ‘Formatuj...’ zmieniamy kolor czcionki na biały.

Potwierdzamy dwukrotnie klikając OK.

Dzięki użyciu powyższego formatowania warunkowego wszystkie komunikaty o błędach zostały ukryte (nadal istnieją, ale ponieważ czcionka ma kolor tła komunikaty są niewidoczne).

Przykład 3

(Arkusz: ‘Formatowanie Warunkowe 3’)

W tym przykładzie pokażę kilka tzw. ‘bajerów’, w profesjonalnych dokumentach biznesowych sugeruje używać je z umiarem.

Poniższa tabela prezentuje dane sprzedaży na rynku pewnego produktu. Aby była bardziej czytelna dodamy do niej formatowanie warunkowe.

Zaznaczamy dane w kolumnie ‘Zmiana’.

Z ‘Formatowania warunkowego’ wybieramy polecenie ‘Zestawy ikon’ i pierwszy z zestawów strzałek.

Przesuwając kursorem po zestawach ikon możemy obserwować jak wyglądały by one w naszym przykładzie.

Zaznaczamy teraz dane w kolumnie ‘Udział Rynkowy’ i wybieramy ‘Formatowanie warunk.’ à ’Paski danych’ i na przykład kolor zielony.

(Kolor zielony ponoć uspokaja, co może być użyteczne).

Tabela stała się bardziej czytelna, od razu widać którzy konkurenci rosną i jaka jest pozycja rynkowa każdego z nich.

Dzięki paskom danych nie musimy przygotowywać dodatkowego wykresu.

Trzecia z możliwych opcji formatowania warunkowego to skale kolorów. Działa analogicznie do przedstawionych powyżej, zainteresowanym sugeruje poeksperymentować z nią na własną rękę.

Ustawienia wszystkich reguł formatowania możemy zmieniać, to od nas zależy od jakiej kwoty strzałka będzie skierowana w dół i jak długi powinien być pasek danych.

Dostęp do tych ustawień uzyskamy po kliknięciu polecenia ‘Więcej reguł’ dostępnego w każdej z wyżej wymienionych kategorii.

Standardowe ustawienie w przypadku 3 ikon to podział na równe części czyli granice przedziałów na poziomie 33% i 67%.

Przykład 4

(Arkusz: ‘Formatowanie Warunkowe 4’)

W formatowaniu warunkowym można także używać adresu komórki i formuł.

W poniższym przykładzie chcielibyśmy wyróżnić czerwonym tłem zmiany sprzedaży dla tych produktów, które były poniżej planu o więcej niż 1%p.

W formule wprowadzamy adres właściwy dla pierwszej z zaznaczonych komórek, adres musi być bez znaków $.

Excel automatycznie wprowadzi odpowiednie formuły do formatowania warunkowego dla każdej z komórek obszaru.

Gdybyśmy pozostawili znaki $ wszystkie wyniki odnosiłyby się do targetu dla produktu 1, zamiast odnosić się do targetu ze ‘swojej’ linijki.

Przykład 5

(Arkusz: ‘Ikony formatowania warunkowego’)

W arkuszu ‘Ikony formatowania warunkowego’ pokazuję większość z dostępnych zbiorów ikon, wraz z moimi sugestiami odnośnie ich czytelności dla użytkowników.

Poniżej można znaleźć przykład w którym użytko strzałek dla dużej tabeli, niestety nie jest to tak czytelne jak byśmy sobie tego życzyli.

Użycie ikon zalecałbym raczej tylko dla kolumny podsumowującej, ponieważ z powyższej tabeli trudno na ich podstawie wyciągnąć jakieś wnioski.

Umieszczenie sumy przy nazwiskach a nie na końcu tabeli pozwala na szybszą orientację.

Można też pozostawić tylko ikonę bez wartości będącej w komórce.

Formatowanie warunkowe z użyciem zestawów ikon umożliwia wyświetlanie tylko jednej lub kilku ikon i pozostawienie pozostałych komórek bez żadnych obrazków co w bardzo czytelny sposób oznacza niektóre komórki.

Wybieramy ‘Formatowanie warunkowe’ - ‘Zarządzaj regułami’ - ‘Edytuj regułę’ a następnie we wszystkich polach za wyjątkiem jednego wybieramy ‘Brak ikony komórki’.

Jedna pozostawiona przez nas ikona będzie się wyświetlała przy komórkach spełniających zadany warunek.

Od razu widać że kwiecień był trudnym miesiącem dla prawie wszystkich handlowców. Antoni Grzybowski i Adam Kosowski mieli problemy na początku roku ale później ogarnęli temat, natomiast Antoni Sosnowski ma problemy z osiągnięciem targetu przez cały rok.

To było czerwone światło teraz zajmijmy się gwiazdami. Przekaz obrazkowy równie intuicyjny.

Wrzesień był wyjątkowo udanym miesiącem, a tytuł gwiazdy przypadł w tym roku Wiesławowi Grzybowskiemu.

Formatowanie z użyciem skali kolorów będzie czytelne tylko dla tabel gdzie wartości rosną lub maleją w kolejnych komórkach, jeśli zmiany są chaotyczne takie formatowanie raczej jeszcze zaciemni obraz.

Poniżej sprzedawcy posortowani od najgorszych do najlepszych.

Oraz nie posortowani w 2 różnych kolorystykach.

Więcej przykładów wykorzystania formatowania warunkowego można znaleźć w lekcjach ‘Użyteczne Triki’ i ‘Triki dla Ekspertów’.