GWARANTOWANE TERMINY
Jesteś tutaj: strona głównastrefa wiedzyFormatowanie warunkowe w MS Excel
MENU

Formatowanie warunkowe w MS Excel


Kategoria: Kurs Excel, Samouczek Excel , Analiza danych
14Aug

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

Rys. 4. Wybór koloru formatowania

 

 

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

 

rys 5 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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

 

 

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

 

rys 16 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

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 formatowanie warunkowe

Rys. 21. Dostępne opcje zestawu ikon

 

 

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

 

rys 22 formatowanie warunkowe

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:

 

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

 

 

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

Wybrani Klienci:

Ten serwis używa plików cookies. Brak zmiany ustawień przeglądarki oznacza zgodę na ich używanie.
Więcej informacji o plikach cookies znajdziesz tutaj
Kliknij ZGADZAM SIĘ, aby ta informacja nie wyświetlała się ponownie
Cognity.pl