Kolorowanie naprzemienne (np. produktów) – jak poprawić przejrzystość danych?

Z filmu dowiesz się:

• Jak do kolorowania używać formatowania warunkowego?

• Jak formatować liczby, aby wiersze miały ten sam kolor?

• Jak „malować” grupy produktów z wykorzystaniem kolumny pomocniczej i formuły tablicowej?

Naszym celem jest to, aby kategorie z produktami zostały pokolorowane. Sformatujemy dane tak, aby poszczególne grupy z danej kategorii (w naszym przykładzie Kabel, Klawiatura, Monitor, Mysz itd.) zostały pomalowane naprzemiennie.

Zagadnienie to omówimy na podstawie przykładowych danych z rysunku nr 1 w arkuszu Pomocnicza (plik do pobrania).

Wizualizacja celu jaki chcemy osiągnąć została przedstawiona na rysunku nr 2, czyli wiersze z pierwszej kategorii (Kabel) zostały pomalowane, kolejna kategoria (Klawiatura) nie, natomiast trzecia kategoria (Monitor) znowu tak. Naszym celem jest to, aby kategorie z produktami zostały pokolorowane naprzemiennie, czyli co druga kategoria. Ma to na celu poprawienie przejrzystości danych.

Do tego zadania użyjemy funkcjonalności Excela- Formatowania warunkowego. Najpierw jednak musimy przygotować sobie obliczenia pomocnicze w specjalnie do tego celu przygotowanej kolumnie. Będziemy sprawdzać warunek, czy dany wiersz należy już do nowej kategorii.

Drugim sposobem osiągniecia takiego efektu będzie formuła tablicowa.

Najpierw omówimy przykład z użyciem obliczeń pomocniczych. Użyjemy tutaj funkcji JEŻELI. Zapis formuły będzie wyglądał następująco:

=JEŻELI(B3<>B2;SUMA(D2;1);SUMA(D2))

Pierwszym argumentem funkcji JEŻELI jest test_logiczny, w którym sprawdzamy czy wartość w aktualnej komórce jest różna od wartości w komórce powyżej. Kolejny argument funkcji to wartość_logiczna_prawda. Jeśli wartość w aktualnej komórce jest różna od wartości w komórce powyżej, to oznacza to, że zmieniła się kategoria. Ważne tutaj jest, że sortowanie ma wpływ na nasz wynik (dane muszą być posortowane, aby te samy nazwy produktów były pod sobą).

Sprawdzamy warunek z tekstu logicznego i jeśli on jest spełniony otrzymujemy wartość logiczną PRAWDA, czyli chcemy zwiększyć naszą sumę. Nie możemy tutaj zrobić prostego dodawania, ponieważ w komórce powyżej mamy tekst, czyli tytuł kolumny – „Pomocnicza”. Wykorzystamy tutaj funkcję SUMA, w której do wartości w komórce D2 (tytuł kolumny Pomocnicza) dodamy wartość 1. Funkcja SUMA ignoruje wartości tekstowe.

Trzeci argument funkcji to wartość_jeżeli_fałsz. Jeśli wartość w aktualnie sprawdzanej komórce jest równa wartości w komórce powyżej to mamy tą samą kategorię, czyli jeśli warunek z testu logicznego nie jest spełniony otrzymujemy wartość z komórki powyżej. Musimy tutaj znowu zastosować funkcję SUMA, ponieważ funkcja ta ignoruje wartości tekstowe.

Tak przygotowaną formułę zatwierdzamy i kopiujemy na wiersze poniżej - otrzymujemy dane przedstawione na rysunku nr 3.

Otrzymaliśmy odpowiednio narastający numer grup, czyli dla całej kategorii Kabel mamy wartość 1, dla całej kategorii Klawiatura mamy wartość 2 itd.

Zależy nam na takim sformatowaniu liczb, aby wiersze z takim samym numerem miały ten sam kolor. Jest na to kilka sposobów.

W pierwszym przypadku możemy użyć funkcji MOD, ponieważ możemy zauważyć, że nasze liczby nieparzyste występujące w kolumnie Pomocnicza mają zawsze resztę z dzielenia przez 2 równą 1. Zapis tej funkcji będzie wyglądał następująco:

=MOD(JEŻELI(B3<>B2;SUMA(D2;1);SUMA(D2));2)

Pierwszym argumentem funkcji jest Liczba, czyli wartość z kolumny Pomocnicza (wynik działania funkcji JEŻELI). Drugim argumentem funkcji jest Dzielnik, czyli w naszym przykładzie liczba 2.

Tak przygotowana formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku nr 4.

W wyniku działania funkcji MOD otrzymamy naprzemienne numerowanie grup wartościami 1 i 0. Jest to nasza kolumna pomocnicza i zakładamy, że tam gdzie jest liczba 1, dane mają być pomalowane, a tam gdzie jest liczba 0 – nie.

Aby pomalować odpowiednie wiersze, należy napisać prostą regułę formatowania warunkowego, którą będzie sprawdzać, jaka wartość jest na końcu każdego wiersza.

Istotne jest, że z punktu widzenia formatowania warunkowego, jeśli wartość w kolumnie sprawdzającej Pomocnicza będzie różna od zera to otrzymamy wartość logiczną PRAWDA, czyli Excel nałoży formatowanie warunkowe. Jeśli to będzie wartość 0, otrzymamy wartość logiczną FAŁSZ, czyli Excel nic nie zrobi.

Zaznaczamy zakres danych z kolumn A, B i C, następnie wybieramy polecenie Formatowanie warunkowe (punkt nr 2 na rysunku nr 5) z karty Narzędzia główne i potem Nowa reguła (punkt nr 3 na rysunku nr 5).

Otworzy nam się okno Nowej formuły formatowania, gdzie musimy określić jej parametry. W polu Wybierz typ reguły (punkt nr 1 na rysunku nr 6) wybieramy Użyj formuły do określenia komórek, które należy sformatować.

W polu Formatuj wartości, dla których następująca formuła jest prawdziwa (punkt nr 2 na rysunku nr 6) robimy odwołanie do komórki $D$3. Ważne jest, abyśmy za pomocą klawisza F4 zablokowali nasze dane po kolumnie ale po wierszu już nie, czyli musimy usunąć znak dolara przed numerem wiersza w odwołaniu do komórki. Zapis powinien wyglądać następująco: $D3.

W polu Podgląd musimy ustawić parametry formatowania, czyli klikamy przycisk Formatuj (punkt nr 3 na rysunku nr 6).

Po kliknięciu w przycisk Formatuj otworzy nam się okno Formatowania komórek, w którym wybieramy kartę Wypełnienie i ustawiamy kolor wypełnienia. Wybrany kolor zatwierdzamy przyciskiem OK (rys. 7).

Otrzymamy prawidłowo sformatowane dane przedstawione na rysunku nr 8.

Jeśli chcielibyśmy uzyskać odwrotne wyniki, czyli pierwsza kategoria miałaby być niepomalowana to najprostszym sposobem jest zmiana tytułu kolumny Pomocniczej na wartość 1. Otrzymamy wtedy odwrotne wyniki przedstawione na rysunku nr 9.

Kolumna D jest kolumną pomocniczą, więc chcielibyśmy ją ukryć. Klikamy na nazwę kolumny D, prawym przyciskiem myszy z podręcznego menu wybieramy polecenie Ukryj (rys. 10).

W naszym rozwiązaniu wykorzystaliśmy formułę pomocniczą. Gdybyśmy skasowali tą formułę z kolumny D stracilibyśmy wyniki formatowania warunkowego.

Nasz przykład bazował na posortowanych danych w kolumnie kategoria. Gdybyśmy posortowali dane w kolumnie Cena otrzymalibyśmy całkiem inne wyniki formatowania warunkowego, ponieważ zmieniłaby się kolejność produktów w kolumnie Kategoria ze względu na ich cenę (rys. 11).

Drugi sposób pomalowania naprzemiennie grup produktów omówimy na podstawie przykładowych danych z arkusza BezPomocniczej z pliku do pobrania (rys. 12).

Napiszemy tutaj formułę, która zapewni nam poprawne działania formatowania warunkowego ale bez dodatkowych obliczeń pomocniczych. W tym rozwiązaniu musimy mieć informację w każdym kolejnym wierszu - ile razy zmieniły się nasze grupy kategorii. Zapis formuły będzie wyglądał następująco:

=$B$2:B2<>$B$3:B3

Zaczynając od początku musimy sprawdzić zakres danych od komórki B2 do komórki B2. Pierwszą komórkę blokujemy bezwzględnie za pomocą klawisza F4, ponieważ od tej komórki zaczynamy sprawdzać dane (jest stała). Drugiej komórki nie blokujemy ponieważ ma się przesuwać w dół.

Musimy sprawdzić ile razy dane z komórek z zakresu $B$2:B2 były różne w stosunku do zakresu zaczynającego się komórkę niżej, czyli $B$3:B3. Analogicznie musimy zablokować bezwzględnie pierwsza komórkę $B$3, żeby się nie przesuwała razem z formułą.

Zapis tej formuły zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku 13.

Z każdym kolejnym wierszem mamy sprawdzane większe zakresy. Możemy sobie podejrzeć formułę w trybie edycji komórki za pomocą klawisza F9 dla komórki np. D5 (rys. 14).

Widzimy, że w kolejnych wierszach mamy kilka operacji porównania. Za każdym razem porównywana jest para komórek. Jeśli są różne to Excel zwraca wartość logiczną PRAWDA, jeśli są sobie równe to otrzymujemy wartość logiczną FAŁSZ.

Z podglądu komórki w trybie edycji cofamy się za pomocą skrótu klawiszowego Ctrl+Z. Naszym zadaniem jest zliczenie ilości wyników FAŁSZ. Wstawiamy naszą operację porównania do funkcji SUMA. Musimy również zamienić nasze wartości logiczne PRAWDA i FAŁSZ na wartości 1 i 0. Najszybszym sposobem zamiany jest użycie podwójnej negacji, czy wstawienie do formuły dwóch znaków minus. Zapis formuły będzie wyglądał następująco:

=SUMA(--($B$2:B2<>$B$3:B3))

Należy pamiętać że operacje porównania są w Excelu wykonywane na samym końcu, więc musimy naszą operację porównania wstawić w nawias aby została wykonana przed podwójnym minusem.

Zatwierdzamy naszą formułę i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku nr 15.

Musimy pamiętać o zatwierdzeniu tej formuły skrótem klawiszowym Ctrl+Shift+Enter, ponieważ jest to formuła tablicowa. Otrzymaliśmy numery poszczególnych grup. Teraz wystarczy dołożyć do zapisu formuły powyżej funkcję MOD (tak jak w pierwszym omawianym rozwiązaniu), która podzieli wyniki funkcji SUMA przez liczbę 2. Zapis funkcji powinien wyglądać następująco:

=MOD(SUMA(--($B$2:B2<>$B$3:B3));2)

Pamiętamy o zatwierdzeniu formuły tablicowej skrótem klawiszowym Ctrl+Shift+Enter. Otrzymamy wyniki przedstawione na rysunku nr 16.

Jeśli wartość wyniku funkcji wynosi 1 to malujemy komórki, jeśli wynosi 0 pozostawiamy bez zmian.

Problem pojawia się jeśli pierwszej kategorii nie chcielibyśmy malować. Jeśli mamy możliwość poszerzenia zakresu z kolumny B na komórki powyżej można to zmienić (rys. 17).

Wtedy nasze dane będą się rozpoczynać od wartości 0 i formatowanie warunkowe ich nie pomaluje. Dane po zwiększeniu zakresu przedstawia rysunek nr 18.

Jeśli nie mielibyśmy wierszy poniżej, które moglibyśmy wykorzystać do zwiększenia naszego zakresu to najprostszą operacją byłoby porównanie całej formuły do wartości 0. Zapis formuły powinien wyglądać następująco:

=MOD(SUMA(--($B$2:B2<>$B$3:B3));2)=0

W wyniku takiego porównania otrzymalibyśmy wartości logiczne PRAWDA i FAŁSZ przedstawione na rysunku 19.

Formatowanie warunkowe pomaluje nam wiersze z wartościami logicznymi PRAWDA.

Gdybyśmy chcieli aby pierwsza grupa produktów została pomalowana musielibyśmy porównać formułę do wartości 1. Zapis wyglądałby następująco:

=MOD(SUMA(--($B$2:B2<>$B$3:B3));2)=1

My będziemy korzystać z zapisu formuły z porównaniem do wartości 0. Dla pewności prawidłowego działania powinniśmy zablokować drugą komórkę po wierszach w wartościach które porównujemy, aby nam się nie przesuwała, ponieważ będziemy ją kopiować na cały zakres danych. Formuła powinna wyglądać następująco:

=MOD(SUMA(--($B$2:$B2<>$B$3:$B3));2)=0

Kopiujemy całą formułę w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C, a następnie zaznaczamy zakres danych A3:C26. Wybieramy polecenie Formatowanie warunkowe (punkt nr 2 na rysunku nr 20) z karty Narzędzia główne, a następnie Nowa reguła (punkt nr 3 na rysunku 20).

Otworzy nam się okno Nowej formuły formatowania, gdzie musimy określić jej parametry. W polu Wybierz typ reguły (punkt nr 1 na rysunku 21) wybieramy polecenie Użyj formuły do określenia komórek, które należy sformatować.

W polu Formatuj wartości, dla których następująca formuła jest prawdziwa (punkt nr 2 na rysunku 21) wklejamy naszą wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V.

W polu Podgląd musimy ustawić parametry formatowania, czyli klikamy przycisk Formatuj (punkt nr 3 na rysunku 21).

Po kliknięciu w przycisk Formatuj otworzy nam się okno Formatowania komórek, w którym wybieramy kartę Wypełnienie i ustawiamy kolor wypełnienia. Wybrany kolor zatwierdzamy przyciskiem OK (rys. 22).

Otrzymamy prawidłowo sformatowane dane przedstawione na rysunku 23.

Pisaliśmy formułę w kolumnie D tylko po to, aby sprawdzić jej działanie zanim stworzymy na jej podstawie nową zasadę formatowania warunkowego. Na tym etapie możemy ją usunąć.

Formuła, którą napisaliśmy była formułą tablicową, którą w arkuszu Excela normalnie musimy zatwierdzać kombinacją klawiszy Ctrl+Shift+Enter, ale gdy tę formułę wpisujemy do formatowania warunkowego nie jest to konieczne.

Nasze końcowe dane powinny wyglądać tak jak na rysunku nr 24.

Na koniec możemy sprawdzić czy formuła ta będzie działać prawidłowo, gdy inaczej posortujemy nasze dane. Polecenie Sortuj od Z do A znajduje się na karcie Dane (rys. 25).

Otrzymamy sformatowane dane, gdzie za każdym razem kiedy zmieni się kategoria przedmiotu (wartość w kolumnie B), zmieni się sposób malowania danych (rys. 26).

Nasza formuła tablicowa jest dynamiczna i zmienia się w zależności od sposobu sortowania danych.

Podsumowując poznaliśmy dwa sposoby na malowanie grup produktów naprzemiennie za pomocą formatowania warunkowego. Jeden z wykorzystaniem kolumny pomocniczej, drugi natomiast z wykorzystaniem formuły tablicowej.

Polecamy także:

• Funkcje Tablicowe – jak wykorzystać je w Twojej firmie?
• Funkcja WARUNKI zamiast JEŻELI - jak szybciej przyporządkować dane?