fbpx
Minuty czytania: 6

Formatowanie warunkowe, czyli kolorowe komórki

Opowiem Wam dzisiaj jak dać upust artystycznemu zacięciu w arkuszu kalkulacyjnym. Na wstążce NARZĘDZIA GŁÓWNE w Excelu jest przycisk FORMATOWANIE WARUNKOWE. Wiem, brzmi skomplikowanie, ale znakomicie oddaje to, do czego można go wykorzystać.

  • Formatowanie, czyli zmiana „formatu” komórki np. koloru tła, czcionki, dodanie ikony.
  • Warunkowe, czyli na podstawie określonej reguły/warunku odnoszącego się do zawartości formatowanej komórki.

kolory farb - formatowanie warunkowe w excelu

 

Po co Ci formatowanie warunkowe?

Zanim zaczniecie się bawić tym narzędziem warto odpowiedzieć sobie na pytanie „po co to robimy?” Czy zależy nam na:

  • wyróżnieniu wartości w jakimś przedziale,
  • pierwszych dziesięciu wartościach,
  • pokazaniu różnic pomiędzy wartościami,
  • a może chcemy zwrócić uwagę odbiorcy na przekroczenie założonego planu kosztów?

Jeśli odpowiemy sobie na pytanie „po co?” przed kliknięciem na przycisk FORMATOWANIE WARUNKOWE, będzie nam dużo łatwiej wybrać konkretną opcję formatowania.

 

Możliwe opcje formatowania warunkowego

Sprawdzimy jakie możliwości daje Excel odpowiadając sobie na pytania: co i jak chcemy formatować? Zaczynając od pytania „co?” – mamy do wyboru takie opcje, jak:

  1. Większe niż / mniejsze niż / między / równe – formatuje komórki na podstawie ich wartości. Do wykorzystania, gdy chcemy wyróżnić np. wszystkie komórki, których wartość jest większa / mniejsza od zera (albo innej wartości).
  2. Tekst zawierający – formatuje komórki na podstawie liter występujących w tekście (wielkość liter nie ma znaczenia) Wykorzystaj, gdy chcesz zaznaczyć wszystkie komórki zawierające w tekście konkretną frazę – niezależnie czy występuje ona na początku, czy na końcu wyrazu.
  3. Data występująca – formatuje komórki na podstawie daty. Mamy tutaj do wyboru takie opcje jak: wczoraj, dzisiaj, jutro, w przyszłym / tym / ubiegłym miesiącu, przyszłym / tym / ubiegłym tygodniu, w ciągu ostatnich 7 dni.
  4. Duplikujące się wartości – formatuje komórki, których wartość się dubluje. Mając listę danych możemy wyłapać np. dublujące się numery faktur.
  5. 10 pierwszych / ostatnich elementów – wyróżnia komórki na podstawie ich wartości. Wybierając największe / najmniejsze – możemy wskazać ile pierwszych / ostatnich wartości ma zostać wyróżnionych. Np. zaznacz 5 najlepszych sprzedawców na podstawie ilości podpisanych umów.
  6. Pierwsze / ostatnie 10% – wyróżnia 10% najwyższych / najniższych wyników wyliczonych na podstawie wartości z zaznaczonego zakresu.
  7. Powyżej / poniżej średniej – wyróżnia wartości powyżej / poniżej średniej wyliczonej z zaznaczonego zakresu wartości.

Zastosowanie każdego z powyżej opisanych wariantów wygląda podobnie. Najpierw zaznaczamy zakres danych, następnie wybieramy jedną z opcji formatowania warunkowego. Potem dzieje się magia 🙂

 

Przykład formatowania warunkowego

Przykład będzie bardzo statystyczny – skorzystamy z tabeli, która prezentuje liczbę zameldowanych na stałe osób w poszczególnych dzielnicach Torunia. Za pomocą formatowania warunkowego zaznaczymy pierwsze dziesięć dzielnic z największą liczbą zameldowanych osób. W pierwszym kroku zaznaczamy całą tabelę, a następnie klikamy: FORMATOWANIE WARUNKOWE > 10 PIERWSZYCH ELEMENTÓW.

Formatowanie warunkowe - menu

Excel wyświetli nam następujące okno, w którym możemy wybrać liczbę elementów, które chcemy wyróżnić (domyślnie 10) oraz w jaki sposób chcemy je wyróżnić.

Formatowanie warunkowe - 10 pierwszych

Formatowanie warunkowe - przykład, 10 pierwszych

Zmieńmy regułę tak, aby wyróżnione zostały wszystkie dzielnice powyżej 10 000 zameldowanych mieszkańców. Nie będziemy tworzyć nowej reguły, a zmodyfikujemy już istniejącą. Wchodzimy w FORMATOWANIE WARUNKOWE > ZARZĄDZAJ REGUŁAMI.

Jeśli na wyświetlonym oknie nie widzicie swojej reguły (a wcześniej ją utworzyliście), to w górnym pasku POKAŻ REGUŁY FORMATOWANIA DLA wybierzcie opcję TEN ARKUSZ. Następnie zaznaczamy naszą regułę i klikamy na przycisk EDYTUJ REGUŁĘ.

Formatowanie warunkowe - edytuj regułę

W oknie edycji reguły zmieniamy opcje z FORMATUJ TYLKO OPCJE SKLASYFIKOWANE JAKO PIERWSZE I OSTATNIE na FORMATUJ TYLKO KOMÓRKI ZAWIERAJĄCE. Następnie określamy jaki warunek mają spełniać formatowane wartości (większe niż 10 000):

Formatowanie warunkowe - komórki zawierające...

Zachęcam Was do pobawienia się różnymi opcjami definiowania warunków formatowania. Od określenia wartości, zaznaczając jedną z komórek w arkuszu, po wpisanie formuły, na podstawie której będzie wyliczać się wartość do formatowania. Moim zdaniem wachlarz tych opcji jest bardzo szeroki.

 

Sposób na formatowanie warunkowe

Odpowiedzieliśmy już sobie na pytanie „co?” chcemy formatować (liczbę zameldowanych mieszkańców w poszczególnych dzielnicach). Teraz przyszła pora, aby odpowiedzieć sobie na pytanie „jak?”. Najpierw spójrzmy jakie mamy opcje.

  • Wyróżnienie komórek za pomocą reguły – opisane na przykładzie powyżej – wybieramy jeden z domyślnych sposobów formatowania albo sami ustawiamy formatowanie (w oknie edycji reguły klikamy na przycisk FORMATUJ).
  • Skala 2–kolorowa – tło komórki zostanie sformatowane różnym natężeniem danego koloru na podstawie wartości komórek w zakresie, na naszym przykładzie:

Formatowanie warunkowe - skala dwukolorowa

W okienku typ, określamy rodzaj minimum i maksimum. W tym wypadku najciemniejsze mają być dzielnice, w których mamy najmniej zameldowanych osób, a kolor będzie jaśniał przy większej liczbie. Możemy też wybrać skalę 3-kolorową, w której dodatkowo określamy wartość środkową formatowania.

Dla powyższych ustawień, w skali dwukolorowej, nasza tabela wygląda następująco:

Formatowanie warunkowe - przykład skali dwukolorowej

 

  • Paski kolorów – w każdej komórce z zakresu pojawi się pasek prezentujący poziom danej wartości w zależności od ustawionego minimum (brak paska) – maksimum (pasek w całej komórce). W tej opcji formatowania możemy „ukryć” wartości, a odbiorcy danych zostawić tylko pasek (zaznaczając okienko POKAŻ TYLKO PASEK). Ukrycie danych jest przydatne, jeśli nie chcemy skupić uwagi odbiorcy na poszczególnych wartościach, a na ich natężeniu w porównaniu z pozostałymi danymi.

Formatowanie warunkowe - paski kolorów

Tak sformatowane dane wyglądają następująco:

Formatowanie warunkowe - przykład pasków kolorów

 

  • Zestawy ikon – przy danych pojawią się ikony, w zależności od wartości komórki (ustalamy przedziały). Dla przykładu przyjęłam, aby Excel oznaczył czerwonym krzyżykiem dzielnice posiadające poniżej 50 zameldowanych mieszkańców, a żółtym wykrzyknikiem w przedziale 50 – 1000. Wartości powyżej 1000 mają być oznaczone zielonym ptaszkiem.

Formatowanie warunkowe - zestaw ikon

Jeśli chciałabym zwrócić uwagę odbiorcy na dzielnice, w których mieszka mniej niż 50 osób, to wystarczy szybkie rzucenie oka na tabelę, aby te dzielnice wyłapać.

Formatowanie warunkowe - przykład zestawu ikon

 

Ułatwiaj odbiór danych

Wybór jednej z opcji formatowania komórek zależy od tego co chcemy oflagować, czyli od tego na jakie pytanie szukamy odpowiedzi w prezentacji danych. Nie mam na myśli, że warunki mają być tak skonstruowane, aby dać gotowy wniosek. Wręcz przeciwnie – mają skupiać uwagę na danych z których chcemy wyciągnąć wnioski.

Moim zdaniem jest to zasadnicza różnica: nie mamy manipulować odbiorcą, tylko ułatwić mu czytanie danych i dojście do własnych przemyśleń. Z tego powodu koniecznie trzeba dodać legendę do zastosowanych ikon / pasków danych albo w inny sposób przekazać odbiorcy, co oznacza zastosowany sposób formatowania.

Innym podejściem jest umożliwienie szybkiej edycji formatowania. Ustawiamy formatowanie na podstawie wartości komórek w zakresie, ale warunki odnoszą się do poszczególnych komórek, w które można wpisać samodzielnie dowolne wartości.

Druga bardzo ważna kwestia przy stosowaniu formatowania warunkowego – czasem mniej, znaczy lepiej. Arkusz, który będzie przeładowany różnego rodzaju formatowaniem będzie mniej czytelny, niż gdyby nie miał go wcale.

Ostatnia, ale dla mnie bardzo ważna, zasada przy korzystaniu z tej funkcjonalności – jeśli stosujecie formatowanie warunkowe dla kilku danych w jednym arkuszu / skoroszycie, zdecydujcie się albo na jeden rodzaj skali, ikon, pasków, albo na spójną kolorystkę. Zwłaszcza jeśli dotyczy to tych samych danych, ale w różnych kategoriach czasowych czy obszarowych.

Gdybym teraz robiła tabelę z danymi dotyczących dzielnic innego miasta i prezentowała te tabele obok siebie, to zastosowałabym ten sam rodzaj formatowania warunkowego w obydwu tabelach.

 

Zmień reguły formatowania warunkowego na własne

A co jeśli otrzymamy arkusz zawierające już reguły formatowania, które zakłócają nam odbiór danych? Można wyczyścić reguły z zaznaczonego zakresu, całego arkusza, tabeli albo tabeli przestawnej. Sposób to skorzystanie ze ścieżki: FORMATOWANIE WARUNKOWE > WYCZYŚĆ REGUŁY i wybieramy interesującą nas opcję:

Formatowanie warunkowe - wyczyść reguły

Potem zostaje już tylko nałożenie własnego formatowania warunkowego. Takiego, które będzie nam ułatwiać analizę, a nie powodować oczopląsu. Po przeczytaniu tego artykułu – mam nadzieję – będzie to czysta przyjemność : )

Agnieszka Kmieć


9 Komentarzy

Wojcian · 11 marca 2019 o 08:47

a jak skopiować formatowanie warunkowe z jednego arkusza na ….240 w moim przypadku,
próbowałem zaznaczyć sformatowaną kolumne potem wszystkie arkusze i skopiować formułe albo za pomocą malarza formatów ale nie działa.Moge to zrobić tylko na piechote czyli arkusz po arkuszu ale za jednym zamachem nie umiem

    Łukasz Pietrzak · 11 marca 2019 o 08:53

    Cześć. Kopiujesz komórki, które mają formatowanie. Zaznaczasz komórki w arkuszach, w których chcesz wkleić formatowanie (wiele arkuszy zaznaczasz z wciśniętym klawiszem SHIFT) Potem prawy przycisk myszy i z menu WKLEJ SPECJALNIE wybierasz ZACHOWAJ FORMATOWANIE ŹRÓDŁOWE (więcej o wklejaniu specjalnym, w tym artykule: https://excelness.com/blog/wklej-specjalnie/

Wojcian · 11 marca 2019 o 09:18

robiłem to już dziesiatki razy bo troche poczytałem i nic.problem pojawia sie jak zaznaczam wszystkie arkusze wtedy tracę mozliwość wklejania – te pola podwietlają sie na szaro i nie sa aktywne.Jak robie to arkusz po arkuszu jest ok.

Łukasz Pietrzak · 14 marca 2019 o 19:48

Wojcian, właśnie wrzuciłem na mój facebookowy profil z filmem jak to zrobić. Zapraszam 😉
https://www.facebook.com/excelness/

Lucyna · 30 października 2019 o 11:55

Dzień Dobry,
mam problem z formatowaniem warunkowym, a dokładnie z ” reguły wyróżniania komórek między”. Chcę wyróżnić zakres między 10 lat 10 miesięcy a 60 lat 10 miesięcy.
Excel wyróżnia mi zakres 10 lat 1 miesięcy, 10 lat 2 miesięcy…
Czy jest jakiś sposób, aby wyróżniał tylko zakres z zapytania?

    Łukasz Pietrzak · 30 października 2019 o 20:08

    Cześć Lucyna. Na pewno jest ale ciężko mi będzie pomóc nie widząc tego przykładu w Excelu. Wyślij to proszę na kontakt@excelness.com
    Zachęcam Cię także do korzystania z naszej grupy na Facebooku, gdzie pomagamy sobie przy tego typu wyzwaniach 😉
    bit.ly/KeepCalmAndUseExcel

Marian023 · 20 listopada 2019 o 09:42

Witam, mam problem ze zminą kolorów w formatowaniu warunkowym. Chodzi o dynamiczny kalendarz miesięczny, gdzie sobota i niedziela zaznaczone są w tabeli na czerwono. W zależności od wyboru miesiąca i roku dynamicznie zmieniają się weekendy na czerwono i to działa. Ale gdy stworzyłem nową procedurę sumę kolorów to nie działa. Cała tabela nie ma wypełnienia, kolor weekendów dalej jest czerwony ale rozpoznaje jako brak wypełnienia. Proszę o pomoc. Pozdrawiam

    Łukasz Pietrzak · 20 listopada 2019 o 09:53

    Ciężko mi odpowiedzieć na ten problem, nie widząc szczegółów. Zachęcam Cię do dołączenia do grupy na Facebooku i wrzucenia tam tego problemu, najlepiej z plikiem przykładowym bit.ly/KeepCalmAndUseExcel

      Marian023 · 28 listopada 2019 o 12:58

      Witam.
      Mam problem z przeliczaniem formuły. Czy jest jakaś funkcja w vba , aby aktualizowała obliczenia w arkuszu?W arkuszu 1 mam listę rozwijaną z miesiącami i przycisk do przeniesienia do arkusza 2.
      W arkuszu 2 kalendarz na 31 dni, który dynamicznie zmienia kolory (dni tygodnia żółte, weekendy czerowne). Stworzyłem funkcje sumuj kolory i to to fajnie działa. Ale jak zmienie miesiąc to sumy w kolorach się nie zmieniają. Dopiero po najechaniu na komórkę z formułą rozwinięcie jej i zatwierdzenie poprawnie przelicza. Proszę o pomoc w tym temacie. Wszystkie opcje w ustawieniach są na obliczanie automatyczne . Pozdrawiam.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *