Czyli paski danych w formatowaniu warunkowym

Paski danych to cudowna opcja formatowania warunkowego, dostępna już od dłuższego czasu w Excelu. Dodanie jej do danych jest bardzo proste: wystarczy zaznaczyć dane, które chcemy sformatować i wybrać styl pasków, jaki nam się najbardziej podoba. Prościzna. Problem jednak pojawia się wtedy, gdy chcemy wyróżnić w ten sposób tylko kilka danych, np. 3 najlepsze. To już nie jest takie oczywiste. I dlatego napisałam ten wpis :).

Oto efekt, który chcę osiągnąć:

Wynik

A dalej napisałam jak to zrobić. Enjoy!

Dodanie pasków danych

To zacznijmy od tej prościzny prościzny, czyli od dodania pasków danych. Chcemy je widzieć obok danych, po to, aby nam nie przesłaniały wartości. Dodam więc kolumnę pomocniczą, na formatce do kolumna F, w której umieszczę prostą formułę, będącą odwołaniem do komórek w kolumnie obok, E. W komórce F4 wpiszę:

=E4

Po skopiowaniu formuły w dół, mam taki efekt:

Kolumna pomocnicza

Tak na dobrą sprawę, to właśnie tę kolumnę będziemy formatować. To do dzieła!

1. Zaznacz dane w tej kolumnie (F4:F14).

2. Z menu Narzędzia główne/Fomratowanie warunkowe wybierz opcję Paski danych i wybierz styl pasków, który Ci się najbardziej podoba, np:

Wybór stylu pasków danych

Efekt jest taki:

Dodanie pasków danych

Gdybyśmy chcieli dodać tylko paski – to już mamy zrobione (obyłoby się nawet bez kolumny pośredniczącej). Natomiast wartości pod paskiem miało nie być widać, więc będziemy edytować tę regułę formatowania warunkowego. Aby to zrobić, wejdź tutaj: Narzędzia główne/Fomratowanie warunkowe/Zarządzaj regułami. Pojawi się Menedżer reguł formatowania warunkowego (koniecznie zwróć uwagę, z jakiego obszaru reguły edytujesz!):

Menedżer reguł formatowania warunkowego

Zaznacz ją na liście reguł i naciśnij przycisk Edytuj. W poniższym okienku zaznacz opcję Pokaz tylko pasek:

Pokaż tylko pasek

Efekt już będzie taki, jak chcieliśmy:

Pasek bez wartości

Do edytowania tej reguły jeszcze wrócimy, ale teraz zajmijmy się wyświetleniem tylko trzech najlepszych wartości.

Wyświetlenie TOP 3 wartości

… czyli kolejna reguła formatowania warunkowego, która będzie określana przez formułę. Jakie zadanie ma ta reguła? Chodzi o to, aby paski danych wyświetliły się tylko przy 3 najwyższych wartościach. Reszta ma zostać ukryta.

I tak właśnie zrobimy – „przykryjemy” niechciane paski danych innym formatowaniem komórki. Takim, którego jedynym działaniem jest niewyświetlanie wartości w komórce. Aby to prawidłowo zadziałało, wykorzystamy 2 właściwości formatowania warunkowego, o czym za chwilę.

Najpierw zdefiniujmy regułę. Ma ona sprawdzać, czy wartość komórki jest mniejsza niż trzecia największa wartość naszego zakresu. Jeśli jest – ma stosować formatowanie, czyli przykryć pasek danych.

Wyzwaniem będzie określenie owych trzech największych wartości. Tutaj z pomocą przyjdzie nam funkcja MAX.K. Zwykła funkcja MAX wyświetla po prostu największą wartość. MAX.K z kolei potrafi podać którąś z kolei największą, czyli np. trzecią. U nas w formatce pierwszą największą, czyli po prostu największą, jest 182, drugą największą jest 180, a trzecią – 170,4. I tak na prawdę ta ostatnia nas interesuje. A raczej interesują nas wszystkie od niej mniejsze, ponieważ właśnie te będziemy formatować. Czyli formuła w formatowaniu warunkowym będzie taka:

=F4<MAX.K($F$4:$F$14;3)

Dodajmy ją więc. Tylko uwaga: podstawowa zasada w formatowaniu w Excelu brzmi: zaznacz dane, które chcesz formatować. I tak w zwykłym formatowaniu nie ma znaczenia, od której komórki zaczniemy zaznaczać dane do sformatowania, to w formatowaniu warunkowym, gdy piszemy formułę, ma to znaczenie. ZAWSZE zaznaczamy od pierwszej komórki, która ma zostać sformatowana. Dlaczego? Ponieważ to tak, jakbyśmy pisali formułę, która odwołuje się do innych komórek. Ważne jest gdzie napiszemy tę formułę, aby jej wynik zobaczyć w określonym miejscu. Dokładnie tak samo jest w formatowaniu warunkowym.

Więc: zaznaczamy zakres F4:F14 i dodajemy regułę: Narzędzia główne/Fomratowanie warunkowe/Nowa reguła/Użyj formuły…:

Dodawanie drugiej reguły

W polu z formułą wklej wspomnianą wcześniej formułę (pkt. 2 na obrazku), a następnie kliknij przycisk Formatuj, aby określić formatowanie komórek, które ma być zastosowane. Co do zasady nie będzie tutaj żadnego formatowania komórki jako takiej (czyli np. koloru wypełnienia), tylko będziemy tutaj formatować wartość komórki tak, aby żadna wartość nie została wyświetlona. W tym celu, w okinku formatowania należy wejść do zakładki Liczby i wybrać kategorię Niestandardowe. Następnie, w polu Typ, wpisać takie formatowanie: ;;; Prezentuje to rysunek:

Formatowanie niestandardowe – ukrywanie wartości komórki

Po dodaniu tej reguły, efekt jest… uwaga… taki:

Nie widać różnicy!

No właśnie: nie widać różnicy! Dlaczego? Zerknijmy do menedżera reguł:

Kolejność reguł

Pierwszą regułą, czyli ważniejszą, jest reguła, którą przed chwilą dodaliśmy – ta o braku formatowania. I ona ma pierwszeństwo w przypadku konfliktu reguł. A tutaj konflikt zdecydowanie mamy. Dotyczy on komórek z wartością mniejszą niż 172,4 (MAX.K). Zgodnie z kolejnością, wartości są formatowane najpierw tak, jak mówi pierwsza, ważniejsza, reguła. Dopiero potem, jeśli jeszcze jest taka możliwość – stosowane są kolejne reguły, przy czym brane są pod uwagę tylko te elementy formatowania, których nie określiła ważniejsza reguła. A ponieważ, w naszym przypadku, wcześniejsza reguła określiła tylko sposób wyświetlania wartości, to wszystkie inne elementy są do wykorzystania. Stąd paski danych się wyświetlają, ponieważ pierwsza reguła nic o nich nie mówiła.

Dlatego nie widzimy różnicy.

Żeby jednak ją zobaczyć, chcemy powiedzieć Excelowi, że dla komórek, w których działa pierwsza reguła ma już nie stosować innych reguł formatowania. Do tego służy checkbox Zatrzymaj, gdy warunek jest prawdziwy. Należy więc zaznaczyć tę opcję przy pierwszej regule:

Zatrzymaj, gdy warunek jest prawdziwy

I wtedy efekt jest taki:

Efekt zatrzymania pozostałych warunków

Tak na prawdę można by to już zostawić, ponieważ efekt został osiągnięty. Mnie jednak razi to, że prawie nie widać różnicy w długości pasków. Pobawię się więc skalą pasków (coś niedopuszczalnego w prawdziwych wykresach, ale tutaj sobie na to pozwolę, ponieważ paski danych mają tylko zwrócić uwagę na hierarchię wartości, a nie ich wartości). Zacznę od wartości, która jest czwartą największą, czyli taką, którą wskaże mi funkcja:

=MAX.K($E$4:$E$14;4)

Wskaże ona wartość 160 dla naszych danych i to jest dla mnie OK. Żeby skrócić oś pasków danych, należy edytować warunek formatowania warunkowego, zgodnie z obrazkiem:

Skrócenie osi pasków danych

Jako minimum wybrać formułę i wkleić tę powyższą do okienka wskazanego przez pkt. 2. na obrazku. BTW: tylko ten zabieg wystarczyłby do pokazania 3 największych wartości ;). Ale jak zwykle wszystko zależy od kontekstu, bo nie zawsze chcielibyśmy skracać skalę… Efekt jest taki:

Wynik

Voila! Wszystko!

Oczywiście zaprezentowana metoda jest jednym z wielu pomysłów na rozwiązanie tego problemu. Do głowy przychodzi mi jeszcze co najmniej dwa. Jednak ten opisany bardzo chciałam Wam pokazać, ponieważ wykorzystuje on tę mało znaną opcję Zatrzymaj, gdy warunek jest prawdziwy. Mam nadzieję, że się przyda 🙂

PS Rety, to chyba wpis z największą ilością obrazków ;).

A tutaj wersja wideo wpisu: