Przepis na

Formatowanie warunkowe

• Autor: Mirosław Zalewski
• 11 grudnia 2012
• komentarze 2

Formatowanie warunkowe jest funkcją ułatwiającą analizę dużych ilości danych. W tym artykule przyjrzymy się najbardziej podstawowym zagadnieniom z nim związanym — tworzeniu nowych reguł oraz zmianie zakresu ich obowiązywania.

Z formatowania warunkowego powinniśmy korzystać zawsze wtedy, gdy chcemy w jakiś sposób oznaczyć komórki zawierające określoną treść. Zakres możliwości ich wykorzystania, w gruncie rzeczy, ogranicza tylko nasza wyobraźnia. Dla przykładu, ja posiadam arkusz kalkulacyjny, w którym umieszczam tytuły artykułów do napisania na blog i informację o stopniu ich ukończenia. Czerwonym kolorem oznaczam te z nich, które powinny zostać opublikowane w ciągu najbliższego tygodnia, a jeszcze nie zostały ukończone. Ponieważ korzystam z formatowania warunkowego, kolor ten sam znika, gdy w odpowiedniej komórce zadeklaruję ukończenie artykułu.

Warunki wstępne

Aby w ogóle rozpocząć korzystanie z formatowania warunkowego, musimy spełnić trzy warunki wstępne.

Po pierwsze należy upewnić się, że w menu zaznaczona jest opcja Narzędzia → Zawartość komórki → Oblicz automatycznie. Opcja ta jest zaznaczona w domyślnej konfiguracji, ale sprawdzenie na pewno nie zaszkodzi.

Po wtóre, musimy przygotować zestaw stylów formatujących komórki w pożądany sposób. W tym artykule zastosuję odmienny kolor tła, gdyż jest to zmiana bardzo skutecznie przyciągająca wzrok. Równie dobrze można jednak zastosować zmianę kroju lub odmiany pisma czy odmienny kolor tekstu.

Oczywiście nic nie zrobimy bez pliku arkusza kalkulacyjnego, na którym będziemy pracować. Ja na potrzeby tego wpisu przygotowałem niewielką bazę danych zawierającą wybrane informacje o kilku modelach samochodów. Jest ona wystarczająco mała, aby jej analiza nie nastręczała żadnych problemów i bez wykorzystania formatowania warunkowego, ale bez trudu można sobie wyobrazić plik zawierający więcej kolumn i kilka tysięcy rekordów.

Tworzenie nowych reguł

Po przygotowaniu środowiska pracy, możemy przystąpić do tworzenia nowych reguł. Z menu wybieramy Format → Formatowanie warunkowe → Formatowanie warunkowe.... Pojawi się nowe okno, zawierające listę wszystkich reguł formatowania warunkowego dla danej komórki. Póki co jest ono puste. Klikamy więc przycisk Dodaj.

Przede wszystkim musimy, za pomocą listy rozwijanej po lewej, określić rodzaj formatowania warunkowego. W LibreOffice 4.0 dostępne są cztery opcje; wcześniejsze wersje mają ich mniej, ale sama zasada działania pozostaje bez zmian:

• Wszystkie komórki — pozwala określić schemat definiujący wygląd wielu komórek jednocześnie. Na przykład najwyższej wartości z zakresu może zostać przypisany kolor zielony, a najniższej — czerwony. Wartościom pomiędzy nimi będą przypisywane kolory i odcienie pośrednie, w zależności od tego, czy są bliżej wartości najwyższej, czy najniższej.
• Wartość komórki jest — służy do porównywania wartość komórki z arbitralnie określoną wartością lub inną komórką. Typ porównania określamy za pomocą drugiej listy rozwijanej. Jest to najprostszy — i zazwyczaj odpowiedni — z rodzajów formatowania warunkowego.
• Formuła jest — pozwala wprowadzić formułę, której wynik zostanie przekonwertowany na wartość logiczną. Wartość logiczna PRAWDA (liczba inna niż 0, niepusty ciąg znaków) spowoduje zaaplikowanie stylu; wartość logiczna FAŁSZ (0) spowoduje, że styl nie zostanie zaaplikowany.
• Data jest — ułatwia pracę z czasem dzięki wybraniu z listy daty wyrażonej językiem naturalnym („Dzisiaj”, „Następny tydzień”, „Bieżący rok”). Identyczne efekty można uzyskać dzięki formułom, chociaż zazwyczaj jest to dość skomplikowane.

Następnie, w polu po prawej, określamy wartość lub formułę, do której będziemy się odnosić.

Na końcu z listy na dole wybieramy styl, który ma zostać zaaplikowany w przypadku, gdy warunek zostanie spełniony. Po prawej stronie dostępny jest jego podgląd.

Jeżeli korzystamy z rodzaju Formuła jest, powinniśmy zwrócić szczególną uwagę na sposób adresowania komórek. Domyślnie w formułach wykorzystywane są adresy względne. Tak więc adres A1 w komórce A1 oznacza po prostu „aktualna komórka”. Ma to znaczenie zwłaszcza wtedy, gdy jedną regułę formatowania warunkowego aplikujemy do wielu komórek jednocześnie. Adres komórki wyświetlany w formule zmieni się, jeżeli okno zarządzania formatowaniem warunkowym otworzymy w innej komórce.

Korzystając z przycisku Dodaj można tworzyć kolejne reguły formatowania warunkowego. Jeżeli warunki są tak skonstruowane, że jedna wartość spełnia więcej niż jeden z nich, na komórkę zaaplikowany zostanie pierwszy spełniony. Kolejność warunków ma więc znaczenie.

Przećwiczmy przedstawioną wyżej teorię na przykładzie.

Załóżmy, że chcę z mojej skromnej bazy danych wyciągnąć informacje o samochodach produkowanych przez Forda. Ponieważ ich cechy będę chciał odnieść do cech samochodów innych producentów, nie skorzystam z filtrowania, ale wykorzystam formatowanie warunkowe.

Ponieważ chcę zmienić kolor całym rekordom, muszę zaznaczyć całą bazę danych. Z tego samego powodu, po wejściu do okna tworzenia nowej reguły formatowania warunkowego, wykorzystam rodzaj Formuła jest, a nie Wartość komórki jest. Następnie wprowadzam formułę:

$A1="Ford"

Na koniec z listy rozwijanej wybieram wcześniej utworzony styl. Dwukrotnie klikam OK.

Zmiana zakresu formatowania warunkowego

Czasem zdarza się, że plik ulegnie zmianie już po wprowadzeniu reguł formatowania warunkowego. W takich wypadkach chcielibyśmy objąć nimi dodatkowe kolumny lub wiersze. Istnieją cztery sposoby, aby to zrobić.

Pierwszy jest najprostszy i najbardziej oczywisty, ale — niestety — dostępny jedynie w LibreOffice 4.0 i nowszych. Otóż w oknie zarządzania formatowaniem warunkowym znajduje się pole, w którym można określić zakres obowiązywania danego zestawu reguł. Użytkownicy starszych wersji muszą korzystać z innych sposobów.

Jednym z nich jest Malarz formatów. Aby z niego skorzystać, należy zaznaczyć komórkę, z której formatowanie chcemy skopiować, a następnie kliknąć ikonkę Malarza formatów znajdującą się na domyślnym pasku narzędziowym. Kursor myszki zmieni się. Teraz możemy wskazać komórkę (lub ich zakres), dla których ma zostać zaaplikowane formatowanie komórki źródłowej. Gdybyśmy ikonkę Malarza formatów kliknęli dwukrotnie, program byłby w tym trybie dopóty, dopóki nie wciśniemy Esc.

Innym jest kopiowanie formatowania. W tym celu kopiujemy komórkę znajdującą się w zakresie formatowania warunkowego, a następnie zaznaczamy komórki, które chcemy do niego włączyć. Wciskamy skrót klawiaturowy Ctrl + Shift + V (lub wybieramy z menu Edycja → Wklej specjalnie). Pojawi się nowe okno, w którym odznaczamy wszystko oprócz Formaty. Potwierdzamy operację.

Malarz formatów i kopiowanie formatowania tak naprawdę nie rozszerzają dotychczasowego zakresu formatowania warunkowego, a jedynie tworzą nowe zakresy z identycznymi regułami. Ma to jedną poważną wadę — jeżeli kiedyś przyjdzie nam zmienić warunki formatowania lub dodać nowe reguły, będziemy musieli te poprawki wprowadzić w całym szeregu zakresów (lub ponownie skorzystać z pomocy Malarza formatów czy kopiowania). Powoduje to także niezbyt estetyczny efekt w oknie zarządzania formatowaniem warunkowym dokumentu. Jedynym znanym mi sposobem na obejście tego problemu w LibreOffice wcześniejszych niż 4.0 jest ręczna edycja pliku arkusza kalkulacyjnego. Interesują nas wtedy elementy <calcext:conditional-format>, a konkretniej zawartość ich atrybutów calcext:target-range-address, znajdujące się przy końcu pliku content.xml.

Jak przenosić komórki, aby nie zniszczyć zakresu formatowania warunkowego?

Niestety, nie tylko Malarz formatów i kopiowanie formatowania potrafią zepsuć zakresy istniejących reguł formatowania warunkowego — zdarza się to także podczas zwykłego przenoszenia komórek! Warto więc wiedzieć, w jaki sposób przenosić komórki w obrębie formatowania warunkowego, aby nie popsuć zdefiniowanych zakresów.

Dla zakresu formatowania warunkowego destrukcyjne są trzy operacje: wycinanie komórek, wklejanie ich za pomocą skrótu klawiaturowego Ctrl + V oraz ich usuwanie — ale tylko wtedy, gdy zaznaczymy dwie lub więcej komórek. Dozwolone jest usuwanie komórek pojedynczo.

Tak więc jeżeli chcemy przenieść jedną komórkę zawierającą formatowanie warunkowe w inne miejsce, musimy ją skopiować, usunąć, a następnie wkleić specjalnie (Ctrl + Shift + V). W oknie wklejania należy zaznaczyć wszystko oprócz Formaty i Wklej wszystko.

W ten, niewątpliwie uciążliwy, sposób muszą sobie radzić użytkownicy LibreOffice w wersji 3.6 oraz starszych. W LibreOffice 4.0 możliwe jest zwykłe przeniesienie komórek, usunięcie automatycznie utworzonych nowych zakresów formatowania warunkowego i rozszerzenie zakresu obowiązywania wcześniej istniejących reguł.