Formatowanie warunkowe w MS Excel

Formatowanie warunkowe to jedna z ciekawszych funkcji w Excelu, która polega na wyróżnieniu w wybrany przez użytkownika sposób komórek, spełniające odpowiednie kryteria. Excel 2010 oferuje bardzo duży zestaw możliwości dostosowywania komórek pod względem wyglądu. W artykule przestawimy Państwu możliwe opcje formatowania warunkowego.

Formatowanie warunkowe: charakterystyka

Poniżej przedstawiono krótką charakterystykę poszczególnych opcji formatowania warunkowego.

• reguły wyróżniania komórek – opcja ta umożliwia oznaczenie komórek, które spełniają warunek przy wykorzystaniu operatora porównania np. większe niż, równe itd. Oprócz liczb można porównywać daty (data występująca) oraz tekst (tekst zawierający).
• reguły pierwszych/ostatnich – wykorzystuje najprostsze statystyki do porównania z wartościami komórek. W tym przypadku można wyznaczyć 10% lub 10 elementów spełniających kryteria lub porównać wartości komórek do średniej
• paski danych – zastosowanie tego rodzaju formatowania pozwoli w szybki sposób na zlokalizowanie wartości najmniejszych i największych
• skale kolorów – pozwala zorientować się, które wartości są najmniejsze a które największe
• zestawy ikon – najczęściej stosowany do określenia tempa zmian pomiędzy n-tą wartością a wartością n+1.

Formatowanie warunkowe: case study

Zastosowanie formatowania warunkowego w case study zaprezentujemy państwo na podstawienie przedstawionych na rynku nr 1 danych dotyczących wyników sprzedażowych poszczególnych pracowników.

Rys. 1. Przykładowe dane

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

Jeśli chcemy zaznaczyć zielonym kolorem wypełnienia komórek, które spełniają warunek, że kwota sprzedaży jest wyższa niż 500 000 zł, to najpierw musimy zaznaczyć komórki z kolumny E, a następnie z karty Narzędzia główne z grupy Style wybrać opcję Formatowanie warunkowe Kolejnym krokiem jest wybranie Większe niż z opcji Reguły wyróżniania komórek (rys. 2).

Rys. 2. Lokalizacja reguły Większe niż

Powinno pojawić się okno w którym Excel proponuje kwotę oraz kolor z jasnoczerwonym wypełnieniem oraz ciemnoczerwonym tekstem. Proszę wybrać Format niestandardowy... (rys. 3)

Rys. 3. Wybranie reguły formatowania Większe niż

W oknie Formatowanie komórek wybieramy czerwony kolor (zakładka Wypełnienie) i akceptujemy wybór przyciskiem OK.

Rys. 4. Wybór koloru formatowania

Na koniec zmieniamy kwotę na 500 000 zł (rys. 5) i akceptujemy regułę formatowania.

Rys. 5. Zmiana kwoty

Dzięki tej operacji można zobaczyć, że komórki spełniające warunek formatowania wyróżniono kolorem czerwonym (rys. 6).

Rys. 6. Wyróżnione komórki przy pomocy formatowania warunkowego

Formatowanie warunkowe: usuwanie reguły

Czasami formuła formatowania nie jest już potrzebna. Aby usunąć wszystkie reguły należy wykonać te same czynności jak w przypadku dodawania formatowania warunkowego (Narzędzia główne -> Style -> Formatowanie warunkowe) a następnie wybrać Wyczyść reguły oraz Wyczyść reguły z całego arkusza (rys. 7)

Rys. 7. Czyszczenie reguł formatowania dla całego arkusza

W przypadku, gdy chcemy wyczyścić reguły z zaznaczonych komórek, to należy wybrać Wyczyść reguły z zaznaczonych komórek. Często jednak będziemy chcieli skasować tylko jedną regułę formatowania. W tym celu należy wybrać opcję Zarządzaj regułami (rys. 8).

Rys. 8. Lokalizacja zarządzania regułami

W oknie Menedżer reguł formatowania warunkowego pokazujemy reguły formatowania dla całego arkusza (rys. 9), a następnie po zaznaczeniu reguły formatowania warunkowego klikamy na Usuń regułę, natomiast w kolejnym kroku zatwierdzamy wybrane opcję poprzez naciśnięcie przycisku OK.

Rys. 9. Usuwanie formatowania warunkowego z arkusza

Formatowanie warunkowe: data zakończenia umowy w przyszłym miesiącu

Jeżeli chcemy dowiedzieć się, którym pracownikom, wyszczególnionym w tabeli widocznej na rysunku nr 1, w przyszłym miesiącu zakończy się umowa, to należy zaznaczyć komórki z datami zakończenia umowy, następnie wybrać z karty Narzędzia główne z grupy Style opcję Formatowanie warunkowe, następnie najechać na Reguły wyróżniania komórek i wybrać Data występująca (rys. 10).

Rys. 10. Lokalizacja opcji formatowania daty

Jeżeli zainteresował Cię temat formatowania warunkowego, zapraszamy Cię na kurs Excel Zaawansowany. Dowiesz się wszystkiego o formatowaniu warunkowym.  

Z listy wybieramy W przyszłym miesiącu i zatwierdzamy przyciskiem OK (rys. 11).

Rys. 11. Formatowanie dat spełniających odpowiednie kryterium

Po wybraniu tego rodzaju formatowania z domyślnym kolorem daty występujące w przyszłym miesiącu zostaną w odpowiedni sposób oznaczone (rys. 12 – miesiąc pisania tego artykułu to sierpień, dlatego daty wrześniowe są wyróżnione).

Rys. 12. Wyróżnione daty z przyszłego miesiąca

Formatowanie warunkowe: reguły pierwszych/ostatnich

Jeżeli naszym celem jest oznaczenie trzech pierwszych osób, które miały najlepsze wyniki sprzedażowe to należy z karty Narzędzia główne z grupy Style wybrać opcję Formatowanie warunkowe, następnie najechać na Reguły pierwszych/ostatnich i wybrać polecenie 10 pierwszych elementów (rys. 13).

Rys. 13. Lokalizacja opcji formatowania 10 pierwszych elementów

Formatowanie warunkowe: paski danych

Jeśli chcemy przedstawić swoje dane graficznie w taki sposób, aby zobaczyć które wartości są większe, a które mniejsze, to warto zastanowić się nad zastosowaniem pasków danych w MS Excel. W tym celu należy wybrać Formatowanie warunkowe (Narzędzia główne -> Style -> Formatowanie warunkowe) a następnie wskazać opcję Paski danych oraz Więcej reguł (rys. 14), dzięki temu można dopasować wygląd pasków danych do oczekiwań użytkownika.

Rys. 14. Lokalizacja większej ilości opcji w paskach danych

W oknie nowa reguła formatowania można dostosować pasek danych do naszych potrzeb, poprzez określenie wartości minimalnej oraz maksymalnej. Wartości można określić m.in. liczbowo, przez formułę, procentowo, percentyl lub pozwolić na wybranie automatyczne tych wartości. Mamy również możliwość koloru wypełnienia oraz obramowania, wartości ujemnych oraz kontekstu, czyli kierunku pasków danych (rys. 15).

Rys. 15. Dostępne ustawienia pasków danych

Zaakceptowanie domyślnych ustawień można zobaczyć na rys. 16.

Rys. 16. Paski danych

Formatowanie warunkowe: skale kolorów

Jeśli chcemy mieć rozróżnienie pomiędzy wartościami komórek, a nie chcemy stosować pasków danych, to warto przemyśleć rozwiązanie ze skalą kolorów.

Ab je zastosować z karty Narzędzia główne z grupy Style wybieramy opcję Formatowanie warunkowe, następnie najeżdżamy na opcję Skale kolorów i wybieramy Więcej reguł (rys. 17).

Rys. 17. Lokalizacja większej ilości opcji dostępnych w skali kolorów

W oknie Nowa reguła formatowania można ustawić minimum oraz maksimum, a dla tch wartości zastosować odpowiedni kolor. Ciekawą opcją jest możliwość wybrania skali trójkolorowej dostępnej w opcji Styl formatowania. Ogólny wygląd okna przedstawiono na rys. 18.

Rys. 18. Domyślne ustawienia skali kolorów

Zaakceptowanie przyciskiem OK spowoduje oznaczenie komórek w taki sposób jak przedstawiono na rys. 19

Rys. 19. Efekt zastosowania skali kolorów

Formatowanie warunkowe: zestawy ikon

Wyobraźmy sobie, że chcemy oznaczyć w następujący sposób wartości liczbowe:

• od 0 do 500 000 (włącznie) – ikona czerwona
• od 500 000 do 800 000 (włącznie) – ikona żółta
• pow. 800 000 – ikona zielona

Żeby nasza baza wyglądała w wyżej opisany sposób musimy z karty Narzędzia główne z grupy Style wybrać opcję Formatowanie warunkowe, następnie najechać na opcję Zestawy ikon i wybrać Więcej reguł (rys. 20).

Rys. 20. Dodatkowe ustawienia dla zestawu ikon

W nowym oknie (rys. 21) ustawiamy Typ na liczbę oraz wstawiamy odpowiednie wartości. Nie zapominajmy również o ustawieniu operatora porównania (większości lub większości i równości).

Rys. 21. Dostępne opcje zestawu ikon

Wygląd danych o zastosowaniu jest na rys. 22.

Rys. 22. Wygląd danych po zastosowaniu zestawu ikon

Autorem artykułu jest Grzegorz Plak, trener Cognity.

Zainteresowanych formatowaniem warunkowych w MS Excel zapraszamy na:

• Kurs Excel zaawansowany
• Kurs Excel w analizach danych
• Kurs wizualizacja i prezentacja danych

Polecamy również lekturę artykułów:

• MS Excel: funkcje sumy
• Wykresy w Excelu
• MS Excel: tworzenie baz danych – wprowadzenie
• Filtrowanie danych w Excelu

Formatowanie warunkowe w MS Excel by Cognity is licensed under a Creative Commons Uznanie autorstwa-Użycie niekomercyjne 4.0 Międzynarodowe License.