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 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

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 z danymi.

Przykład 1

(Arkusz: ‘Formatowanie Warunkowe 1’)

Na poniżej przedstawionej liście płac pracowników zajmujących się analizami w Excelu pewnej firmy chcielibyśmy zaznaczyć tych, którzy zarabiają więcej niż 7000zł. Kwoty powyżej 7 tyś 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ę ‘Regół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ę 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).

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 nieskutecznie 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 2006 formuła pokazuje błędy, co nie wygląda dobrze.

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

Wybieramy polecenie ‘Więcej reguł’.

Z menu rozwijanego wybieramy ‘Zawierające błędy’.

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

Potwierdzamy 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’)

Powyżej opisane polecenia były dostępne także w poprzedniej wersji Excela w Excelu 2007 dodano do formatowania warunkowego kilka ‘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’ wypieramy polecenie ‘Zestawy ikon’ i pierwszy z zestawów strzałki.

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 warunkowe’ à’Paski danych’ i na przykład kolor zielony.

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żyć 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.