Grupowanie wierszy poprzez formatowanie warunkowe w Excelu

Aby osiągnąć odpowiedni efekt, zestawienie musi być stworzone w taki sposób, aby wszystkie komórki w kolumnie, według której będziemy grupować, znajdowały się obok siebie. Najlepiej posortować wcześniej zestawienie według kolumny, zgodnie z która będziemy grupować.

Rys. 1. Zestawienie informacji o pracownikach firmy

Chcemy wyraźnie pogrupować pracowników danych działów, które określone są w kolumnie C w taki sposób, aby wiersze z informacjami o pracownikach były oznaczane na przemian szarym i białym kolorem wypełnienia. Aby to wykonać:

1. Zaznaczamy wszystkie komórki zawierające informacje o pracownikach, począwszy od wiersza 2, czyli komórki A2:E57.

2. Wybieramy polecenie menu Format/Formatowanie warunkowe (w Excelu 2007/2010: na karcie Narzędzia główne w grupie poleceń Style wybieramy Formatowanie warunkowe/Nowa reguła).

3. W oknie Formatowanie warunkowe na liście rozwijalnej po prawej stronie wybieramy Formuła jest (w Excelu 2007/2010: w oknie Nowa reguła formatowania na liście rozwijalnej wybieramy pozycję Użyj formuły do określenia komórek, które należy sformatować).

4. Klikamy na przycisk Formatuj, aby ustawić formatowanie komórek, jakie chcemy zastosować przy spełnieniu warunku.

Rys. 2. Ustawienie szarego koloru wypełnienia

5. W oknie Formatowanie komórek przechodzimy do zakładki Desenie i zaznaczamy kolor szary jak na rysunku 2.

6. Następnie przechodzimy do zakładki Obramowanie, w polu Kolor wybieramy kolor biały i klikamy po kolei cztery zewnętrzne krawędzie w sekcji Obramowanie.

Rys. 3. Ustawienie białego koloru obramowania

7. Naciskamy przycisk OK, aby zatwierdzić ustawienia formatowania.

8. W oknie Formatowanie warunkowe (w Excelu 2007/2010: w oknie Nowa reguła formatowania) w polu tekstowym formuły wpisujemy:

=MOD(ZAOKR(SUMA(1/LICZ.JEŻELI($C$2:$C2;$C$2:$C2));0);2)=0

Jeśli wiersze mają być grupowane według innej kolumny, to należy zmodyfikować zakresy komórek i wpisać inną kolumnę niż kolumna C.

Jeśli chcemy, aby kolorem szarym były oznaczone komórki odpowiadające pierwszemu działowi w kolumnie C, a dla drugiego działu komórki były bez formatowania, to musimy na końcu formuły wpisać wartość 1 zamiast wartości 0. Możemy także wprowadzić dwa różne warunki formatowania warunkowego, wpisując takie same formuły i zmieniając tylko ostatnią liczbę na 1 lub 0 – w takim wypadku można zastosować różne kolory występujące na przemian, a nie tylko kombinacje białego i innego koloru.

Idąc dalej, możemy skorzystać także z trzech kolorów wypełnienia, a nawet więcej. W takim wypadku należy w drugim argumencie funkcji MOD wpisać wartość 3 i dla kolejnych warunków formatowania warunkowego wpisywać odpowiednio wartości 0, 1 lub 2.

Zastosowana funkcja jest funkcją tablicową, jednak w oknie formatowania warunkowego nie trzeba używać kombinacji klawiszy Ctrl + Shift + Enter, aby ją zatwierdzić jako tablicową. Wpisana formuła jest właściwa dla pierwszej zaznaczonej komórki, czyli A2. Ze względu na zastosowane odwołania względne dla pozostałych komórek będzie ona nieco inaczej obliczana. Funkcja SUMA w powyższej formule oblicza, ile jest niepowtarzalnych wartości, począwszy od komórki C2 do komórki w kolumnie C znajdującej się w tym samym wierszu co komórka zawierająca regułę formatowania. Ze względu na operację dzielenia istnieje możliwość, że wyniki nie będą zaokrąglone do liczb całkowitych. Dlatego wynik funkcji SUMA zaokrąglamy do wartości całkowitych. Następnie poprzez funkcję MOD sprawdzamy, jaka jest reszta dzielenia ilości niepowtarzalnych wartości przez liczbę 2. Jeśli reszta dzielenia równa się 0, to wynik jest spełniony i będzie zastosowane ustawione formatowanie komórek.

9. Naciskamy przycisk OK, aby zatwierdzić ustawienia formatowania warunkowego.

Rys. 4. Ustawienie formatowania warunkowego w Excelu 2003

Rys. 5. Ustawienie formatowania warunkowego w Excelu 2007

Teraz dane pracowników dotyczące tych samych działów są oznaczane na przemian białym i szarym kolorem wypełnienia. Arkusz po ustawieniu formatowania warunkowego przedstawiony jest na rysunku 6.

Rys. 6. Arkusz po dodaniu formatowania warunkowego

Rafał Janus