Yestok.pl

Opracowanie powstało w oparciu o wersję programu istniejącą w trakcie jego pisania, Libre Office: 5.2.3, Apache OpenOffice: 4.1.3.

Do bogatego sposobu formatowania komórek arkusza ich twórcy dodali dodatkowy – nazywany formatowaniem warunkowym. Terminem tym określono formatowanie, którego ostateczne efekty zależą od spełnienia, bądź nie, różnych warunków zdefiniowanych przez autora zestawienia arkuszowego. Dzięki temu wygląd arkusza może zmieniać się dynamicznie.

• Formatowanie warunkowe poprzez formatowanie standardowe.
• Formatowanie warunkowe poprzez style.
• Formatowanie warunkowe.
• Formatowanie warunkowe w Apache OpenOffice.
• Wartość komórki jest.
• Formuła jest.
• Formatowanie warunkowe w LibreOffice.
• Wartość komórki jest oraz Formuła jest.
• Data.
• Wszystkie komórki.
• Skala koloru (2 wpisy).
• Skala koloru (3 wpisy).
• Pasek danych.
• Zestaw ikon.
• Zarządzanie formatowaniem warunkowym.

Formatowanie warunkowe poprzez formatowanie standardowe.

Pierwszą metodą formatowania warunkowego jest wykorzystanie możliwości zawartych w standardowym formatowaniu komórek. Najprostszą z nich jest możliwość wykorzystania opcji przedstawienia liczb ujemnych czerwoną czcionką.

Na powyższej ilustracji widać, że wybór opcji „Liczby ujemne na czerwono” spowodował wyróżnienie tych liczb w zestawieniu. Jest to klasyczne formatowanie dynamiczne, gdyż gdy wartości liczbowe zmienią znaki – zmieni się kolor, jakim liczba będzie wyświetlona. Jest to oczywiście bardzo skromny zakres warunkowania.

O poszerzonym warunkowaniu napisałem w opracowaniu dotyczącym formatowania komórek, ale powtórzę to teraz ponownie. Użytkownik może utworzyć własny schemat formatowania wykorzystując do tego widoczne na ilustracji pole „Kod formatu”.

Samo ustawienie opcji „Liczby ujemne na czerwono” zmieniło charakter kodu i uznając go za kod zaproponowany przez użytkownika wstawiło do pola „Kod formatu” nowy, złożony sposób formatowania. Kompletny kod składa się z segmentów. Pole „Kod formatowania”może zawierać jeden, dwa albo trzy segmenty, z których każdy określa wygląd liczby. Wystąpienie tylko jednego segmentu oznacza, że wystąpi wspólne formatowanie dla wszystkich liczb, dwa segmenty oznaczają, że pierwszy z nich będzie dotyczył liczb dodatnich i liczby zero a drugi liczb ujemnych. Jeśli określono także trzeci segment, to pierwszy dotyczy tylko liczb dodatnich a trzeci liczby zero. Tak działa ten mechanizm, gdy nie zmienia się go dodatkowymi parametrami zakresu liczb, których dotyczą poszczególne segmenty.

Ilustracja powyżej pokazuje, że wybór tej opcji („Liczby ujemne na czerwono”) wygenerował dwa segmenty: pierwszy „0” oznacza, że liczby dodatnie i zero zostaną pokazane jako liczby całkowite co najmniej jednocyfrowe, drugi: „[RED]-0” oznacza, że liczby ujemne zostaną pokazane czerwoną czcionką jako co najmniej jednocyfrowe liczby całkowite, ze znakiem minus.

Każdemu segmentowi można przypisać własny kolor czcionki, który trzeba wpisać w nawiasach kwadratowych przed segmentem, którego ma dotyczyć. Dozwolonymi kolorami są: [BLACK], [BLUE], [BROWN], [CYAN], [GREEN], [GREY], [MAGENTA], [RED], [WHITE], i [YELLOW] czyli czarny, niebieski, brązowy, seledynowy, zielony, szary, purpurowy, czerwony, biały i żółty. Trzeba użyć angielskich nazw kolorów. Wpisanie koloru z błędem albo koloru „nieobsługiwanego” nie sygnalizuje błędu. Taki zapis jest ignorowany. Kolor czcionki wprowadzony w kodzie formatowania jest nadrzędnym kolorem nad tym wynikającym ze stylu komórki.

Wspomniałem, że segmenty mogą zostać opatrzone dodatkowym parametrem określającym zakres liczb, jakie tym segmentem zostaną pokazane. Ten parametr jest także zapisywany w nawiasach kwadratowych, poprzedza sam kod formatowania i składa się z jednego z dozwolonych znaków relacji oraz liczby. Symbolami relacji mogą być symbole: „<, <=, >, >=, =, <>”. Zapis w postaci [<=7] oznacza, że kod formatowania dotyczy liczb nie większych niż 7.

Poniższa ilustracja pokazuje taki sposób formatowania na przykładzie stanu pewnego magazynu. Zestawienie zawiera maksymalny stan dopuszczalny w magazynie, aktualny stan magazynowy i procentowe wyrażenie tego stanu w stosunku do stanu maksymalnego. Ta ostatnia kolumna została sformatowana własnym trzyczęściowym kodem formatowania takim, że wartości większe niż 0,75 mają zostać pokazane z symbolem buźki w kolorze zielonym, wartości mniejsze od 0,15 kolorem czerwonym z poprzedzającymi liczbę znakami wykrzyknika a pozostałe wartości kolorem niebieskim.

Pod zestawieniem przytoczyłem kod formatowania przypisany do kolumny D. Ostatni segment tego kodu nie może zawierać parametru określającego warunek, gdyż musi określać wygląd wszystkich pozostałych wartości. Gdyby został pominięty, system sam dopisałby kod „Standard”.

Jak widać, przy pomocy zwykłego formatowaniu komórek także można zrealizować formatowanie warunkowe. Pozwala ono na wykorzystanie go tylko do wyglądu wartości liczbowej i tylko w odniesieniu do własnej wartości w komórce.

Formatowanie warunkowe poprzez style.

Styl komórki to kompletny zestaw atrybutów przypisanych komórce z nadaną takiemu zestawowi nazwą. Definiując wiele stylów można łatwo nadawać wygląd komórkom poprzez przypisanie im wybranego stylu. Dysponując wieloma stylami można także nadawać różny wygląd tym samym komórkom uzależniając to od spełnienia bądź nie, wybranego warunku lub warunków.

Tytuł tego fragmentu opracowania nie dotyczy jednak metodologii tworzenia stylów. O tym napisałem w opracowaniu „Formatowanie komórek” lecz wykorzystania funkcji STYL.

W arkuszu dostępna jest funkcja STYL(nazwa stylu1;czas;nazwa stylu2), której argumentami są: nazwa stylu dostępnego w arkuszu, jaki ma zostać przypisany do komórki, czas w sekundach, przez jaki styl będzie obowiązywał w komórce, nazwa stylu, który zostanie przypisany komórce po upływie podanego czasu. Aby mechanizm przypisania stylu zadziałał, funkcja STYL musi stać się częścią formuły obliczeniowej. Dlatego tą metodą nie można nadawać wyglądu komórkom przeznaczonym na wprowadzanie danych. Wartością arytmetyczną funkcji jest liczba 0 (zero), można więc taką funkcję dopisać po znaku „+” na końcu każdej formuły matematycznej. Użycie tej funkcji nie wpłynie na merytoryczne obliczenia zadekretowane w formule natomiast nada komórce wymagany styl formatowania. Na przykład, jeżeli w arkuszu zostały zdefiniowane style o nazwach „zielony” i „żółty” to formuła w postaci „=A1+A2+STYL("zielony";10;"żółty")” wyświetli sumę wartości z komórek A1 i A2 nadając komórce wynikowej styl „zielony” a po 10 sekundach zmieni go na styl „żółty”.

Nieco inaczej należy to rozwiązać w przypadku formuł tekstowych, w których jedynym operatorem jest znak „&”. Dopisanie na końcu formuły tekstowej wyrażenia „&STYL(argumenty)” nada rezultatowi styl wynikający z zadziałania funkcji, ale dodatkowo dostawi na końcu wynikowego ciągu cyfrę zero traktując ją jako element wyniku. Aby tego uniknąć koniecznym jest skorzystanie dodatkowo z funkcji o jednoliterowej nazwie T(argument). Funkcja ta zwraca albo wartość tekstową, albo pusty ciąg, wtedy kiedy jej argumentem nie będzie wartość tekstowa. Zatem zapis „&T(STYL(argumenty))” doda do formuły tekstowej ciąg pusty, gdyż funkcja STYL nie zwraca wartości tekstowej, i nie zmieni jej wyniku, .

Funkcja STYL, jak napisałem ma trzy argumenty, z których dwa ostatnie nie muszą wystąpić. Jeśli wystąpi tylko pierwszy i drugi argument (czyli czas), to po jego upływie zostanie do komórki zastosowany styl „Domyślnie” (brak trzeciego argumentu). Jeśli czas zostanie pominięty albo podany jako 0, to traktowane jest to jako czas nieskończony, czyli tak jakby funkcja była napisana tylko z pierwszym argumentem. Zmiana stylów w komórce będzie następowała po każdorazowym przeliczeniu danych wpływających na formułę. Nawiązując do przytoczonego powyżej przykładu, wtedy gdy zmieni się wartość w komórce A1 albo A2.

Łatwo zauważyć, że sama funkcja STYL nie wnosi nadzwyczajnych możliwości, po prostu sformatuje komórkę wybranym stylem lub stylami. Jeśli jednak argumenty funkcji STYL zostaną wyznaczone za pomocą formuły warunkowej, pozwoli to na dynamiczne określanie stylu komórki. Argumentem funkcji mogą być np. funkcje: JEŻELI, WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO, INDEKS, lub wyrażenia obliczeniowe, które dopiero wyznaczą nazwę konkretnego stylu.

Na poniższej ilustracji przedstawiam przykład wykorzystania funkcji STYL.

Tym razem w zestawieniu postanowiłem sformatować w urozmaicony sposób kolumnę zawierającą nazwy towarów. Zasada formatowania jest analogiczna, jeśli procentowy stan aktualny jest większy niż 75% to komórce ma być nadany styl „czerwony”, jeśli będzie mniejszy niż 15% – nadany powinien być styl „żółty” w pozostałych przypadkach – styl „zielony”.

Odpowiednie style zostały zdefiniowane. W kolumnie F umieściłem nazwy towarów po to aby w kolumnie A móc zbudować formuły łączące te nazwy z funkcją STYL, bo tylko w formule mogę użyć tej funkcji. Formuła wpisana do komórki A2 ma postać:
„=F2&T(STYL(JEŻELI(D2>0,75;"czerwony";JEŻELI(D2<0,15;"żółty";"zielony"))))”.

Jak z niej widać pierwszy i jedyny argument funkcji STYL jest wyznaczony za pomocą wyrażenia logicznego zbudowanego z funkcji JEŻELI. Przykład nie jest zapewne najszczęśliwszy. Wymaga utrzymywania pomocniczej kolumny z nazwami towarów po to aby móc na jej podstawie wytworzyć kolumnę zawierającą te same nazwy, ale już powiązane z funkcją STYL. Oddaje jednak intencje stosowania takiego rozwiązania.

Bardzo często komórki formatowane warunkowo są tymi, które zawierają wartości podlegające kontroli. Tak będzie, gdy w ten sam sposób zechcemy wyróżnić komórki zawierające procentową wartość stanu aktualnego. Pierwsza komórka pokazująca tę wartość – D2 – zawiera formułę „=ZAOKR(C2/B2;4)”. Aby uzależnić wygląd tej komórki od własnej zawartości, funkcja STYL musiałaby zawierać odwołanie do komórki D2. Formuła zapisana w komórce nie może zawierać odwołania, nawet pośredniego, do siebie samej. Generuje to w komórce błąd o numerze 522 oraz komunikat na pasku statusowym: „Błąd: Odwołanie cykliczne”.

W tym miejscu przedstawiam jeszcze jedną pomocną funkcję: BIEŻĄCY(). Jest to funkcja bezargumentowa o bardzo interesującym działaniu. Wystąpienie tej funkcji w formule spowoduje, że formuła zostanie policzona do miejsca, w którym wystąpiła funkcja BIEŻĄCY() i obliczona wartość zostanie przypisana tej właśnie funkcji. Np. zapis formuły w postaci „=1+2+BIEŻĄCY()+10” wyliczy liczbę 16, gdyż 1+2 jest 3 i taką wartość przyjmie w tym momencie funkcja BIEŻĄCY() co ostatecznie prowadzi do wspomnianego wyniku.

Wykorzystując tę funkcję przedstawiam zastosowanie funkcji STYL do kolumny D.

Tym razem aby pokazać inne możliwości zamiast użycia struktury z funkcją JEŻELI wykorzystałem funkcję WYSZUKAJ.PIONOWO i funkcję BIEŻĄCY(). Formułą wpisaną do komórki D2 jest:
„=ZAOKR(C2/B2;4)+STYL(WYSZUKAJ.PIONOWO(BIEŻĄCY();$A$22:$B$24;2))”

W tej formule funkcja STYL otrzymuje nazwę stylu poprzez wybór jej z tabeli widocznej w dolnej części ilustracji. Wyszukiwaną wartością jest wartość funkcji BIEŻĄCY(), która w tym przypadku odpowiada wartości pierwszej części formuły.

Trzeba pamiętać, że funkcja STYL nadaje komórce styl i pozostanie on jej przypisany, nawet gdy formuła zostanie usunięta. Aby przypisać takiej komórce inny styl należy skorzystać z menadżera stylów (F11) i wskazać w nim potrzebny styl.

Formatowanie warunkowe.

Poprzednie rozdziały pokazały dwie możliwości wykorzystania formatowania warunkowego. Ten rozdział nosi tytuł taki sam jak całe opracowanie tylko dlatego, że omówię w nim polecenie programu Calc nazywające się „Formatowanie warunkowe”. Jest ono dostępne w wykazie poleceń polecenia „Format”. Przedstawia to rys. 5.

Tak samo nazwane polecenie w Apache OpenOffice nie zawiera dodatkowych opcji widocznych na ilustracji i przechodzi do realizacji odpowiadającej wyborowi „Warunek…” w programie Calc LibreOffice. Oba programy różnią się widokiem okien dialogowych dlatego omówię je oddzielnie.

Wspólną cechą tego typu formatowania w obu programach jest to, że mimo iż odwołują się do nazw stylów, to style te nie są przypisywane do komórki. Właściwości tych stylów nadpisują jedynie atrybuty podstawowego stylu komórki. Dlatego, tak jak napisałem to w opracowaniu dotyczącym stylów, zastosowanie skrótu klawiaturowego CTRL+M w stosunku do obszaru formatowania warunkowego usunie te nadpisane właściwości, przywracając komórce oryginalny styl.

Ten sposób działania można zauważyć obserwując zachowanie menadżera stylów. Menadżer wyróżnia nazwę tego stylu, który jest przypisany komórce aktywnej. Można zobaczyć, że jeśli komórka zostanie wyróżniona formatowaniem warunkowym przypisującym jakiś styl to menadżer i tak wyróżnia na liście stylów pierwotną nazwę stylu tej komórki. Menadżer stylów nie wyróżnia żadnej nazwy stylu, jeżeli został zaznaczony obszar komórek, w którym zastosowano różne style. Funkcjonowanie formatowania warunkowego wymaga aby włączona była opcja „Oblicz automatycznie”. W Apache OpenOffice opcja ta znajduje się w sekwencji poleceń: „Narzędzia – Zawartość komórki – Oblicz automatycznie”. W LibreOffice w „Dane – Oblicz – Oblicz automatycznie”.

Formatowanie warunkowe można zdefiniować dla pojedynczej komórki, a następnie skopiować ten sposób formatowania. Można to zrobić malarzem formatów albo sekwencją „Kopiuj” (CTRL+C), a następnie po zaznaczeniu obszaru docelowego: „Wklej specjalnie” (CTRL+SHIFT+V) zaznaczając wyłącznie opcję „Formaty”. Można także zaznaczyć od razu obszar, który będzie podlegał formatowaniu warunkowemu.

Formatowanie warunkowe w Apache OpenOffice.

Wywołanie formatowania warunkowego otwiera następujące okno dialogowe:

Okno umożliwia zdefiniowanie do trzech warunków. Funkcjonują one następująco. Jeśli pierwszy warunek zostanie spełniony, komórka przyjmuje atrybuty wskazanego stylu i dalsze warunki nie będą już sprawdzane. Jeśli pierwszy warunek nie jest spełniony, analizowany jest warunek 2. na tych samych zasadach, a jeśli to potrzebne – także trzeci.

Przed dodaniem następnego warunku należy zaznaczyć pole wyboru i wprowadzić nowy warunek. Jeżeli dla istniejącego warunku pole wyboru zostanie wyłączone, to jest to równoważne z usunięciem tego warunku a nie jego tymczasowym wyłączeniu.

Generalna zasada przypisania formatowania warunkowego określa, że zostanie ono przypisane pod warunkiem, że wynikiem badanej relacji jest PRAWDA.

Rodzaj relacji wynika z wyboru pierwszego pola. Na ilustracji znajduje się w nim wpis „Wartość komórki jest”, jednak wybrać można także inną możliwość – „Formuła jest”.

Wartość komórki jest.

Ten wybór pozwala określić formatowanie warunkowe na podstawie zawartości komórek w zaznaczonym obszarze poprzez badanie odpowiadających relacji. Dlatego kolejne rozwijane pole, w którym widać wpis „równa” (pole relacji) pojawia się tylko wtedy gdy w pierwszym polu wybrano „Wartość komórki jest”. Relacjami, jakie można wybrać są: widoczny wpis „równa” oraz: „mniejsza niż”, „większa niż”, „mniejsza lub równa”, „większa lub równa”, „nierówna”, „między i „nie między”. Ostatnia część budowanego warunku, po prawej stronie, zawiera pole lub pola (gdy wybrano jedną z dwóch ostatnich relacji) pozwalające wprowadzić wymaganą wartość. Tą wartością może być bezpośrednio wpisana liczba, adres komórki zawierający wymaganą wartość lub formułę ją wyliczającą albo formuła, która wyznaczy porównywaną wartość. Jeśli wartością podawaną bezpośrednio jest tekst, należy go wpisać w cudzysłowie.

Poniżej układu opisującego relację znajduje się rozwijane pole „Styl komórki”, w którym należy wskazać nazwę stylu, jaki zostanie zastosowany do zmodyfikowania stylu komórki, gdy relacja będzie spełniona. Jeśli takiego stylu jeszcze nie ma, można go utworzyć wykorzystując przycisk „Nowy styl…”.

Utworzenie formatowania warunkowego przedstawię na przykładzie, którego ilustracją jest poniższy rysunek.

Ponad oknem dialogowym „Formatowanie warunkowe” znajdują się dwie kolumny liczb. Komórki z liczbami w lewej kolumnie mają otrzymać formatowanie warunkowe takie, że powinny przyjąć styl o nazwie „żółty”, wtedy gdy wartość w komórce znajduje się między dwoma wartościami, pierwszą, taką, jaka znajduje się w odpowiadającej komórce po prawej stronie i drugą, większą od tej wartości o 3.

Obszar formatowania został zaznaczony, przez co kolory są przytłumione, ale pokazują zastosowane formatowanie. W oknie dialogowym poniżej tego obszaru widać ustaloną relację pierwszego i jedynego w tym przypadku warunku. Jak widać pierwszą, niższą wartość wprowadziłem jako adres komórki D6, natomiast wartość wyższa została pokazana jako formuła obliczeniowa D6+3. W przypadku formuł wpisywanych do tych pól nie poprzedza się ich znakiem „=”.

Jeżeli do pola ma zostać wprowadzona wartość znajdująca się w arkuszu to można adres tej komórki wprowadzić poprzez kliknięcie myszką. Adres komórki zostanie wprowadzony w postaci adresowania bezwzględnego, podobnej do takiego: „$Arkusz1.$D$6”.

Może warto w tym momencie poruszyć problem adresowania bezwzględnego i względnego. Zastosowanie adresu bezwzględnego oznacza, że ten adres zostanie wykorzystany we wszystkich pozostałych komórkach obszaru, w którym ma obowiązywać formatowanie warunkowe. Gdybym w przytoczonym przykładzie zastosował ten zapis, czyli pierwszym adresem byłby „$D$6” a drugim „$D$6+3” to we wszystkich komórkach formatowania warunkowego ich wartości byłyby porównywane z tymi liczbami, a nie liczbami położonymi odpowiednio po prawej stronie.

Budując relację tworzy się ją dla aktywnej komórki obszaru. W Apache OpenOffice tą komórką jest końcowa komórka zaznaczonego obszaru (C6), widać to na powyższej ilustracji. Taki stan wynika z naturalnego odruchu zaznaczania obszaru od górnego lewego do prawego dolnego rogu. Przy takim działaniu ostatnią zaznaczaną komórką jest prawa dolna i to ona jest w tym przypadku komórką aktywną. Autorzy konstruujący relację mogą odruchowo tworzyć ją odwołując się do lewej górnej komórki, a nie do komórki aktywnej. Stąd często wpisanym adresem byłby D2, gdyż przyglądając się zaznaczonemu obszarowi uznaliby, że pierwszą komórką do sformatowania jest C2. Taki zapis oznaczałby jednak, że wygląd komórki C6 zależałby od relacji zachodzącej w stosunku do liczb z komórki D2. Formalnie nie byłby oczywiście błąd. Jednak ponieważ podczas definiowania formatowania warunkowego był zaznaczony pewien obszar, do komórek tego obszaru zostaną przeniesione zasady formatowania. Wobec tego formatowanie komórki C5 odwoła się do adresu D1, a następne komórki C4, C3 i C2 nie mają się do czego odwołać, bo nie ma wierszy, do których należało się odwołać. W tych komórkach adresy przyjmą postać „D#ODWOŁANIE”. Dlatego tak ważnym jest aby budowana relacja dotyczyła właściwej komórki. Jest to dość istotna uwaga zwłaszcza dla tych, którzy pracują także na wersji LibreOffice. W tej wersji w zaznaczonym obszarze aktywną jest komórka, od której rozpoczęto zaznaczanie, a więc przy wspomnianym już nawyku jest to komórka w lewym górnym rogu obszaru.

Formuła jest.

Wybór tego sposobu pozwala nadać formatowanie komórkowe obszarom niezawierającym danych decydujących o formatowaniu. Okno dialogowe zmieni się w ten sposób, że pojawi się jedno pole, w którym należy wpisać odpowiadającą relację, albo podać adres komórki (komórek), które taką relację zawierają. Pozostała część warunku jest taka sama jak poprzednio.

Pokażę to działanie na zmodyfikowanym zestawieniu z poprzedniego przykładu. Tutaj zestawienie zostało uzupełnione o poprzedzającą je kolumnę zawierająca nazwy, i to właśnie nazwy mają zostać sformatowane warunkowo przy spełnieniu tych samych relacji jak poprzednio.

W polu formuły wpisana została relacja „I(C6>=D6;C6<=(D6+3))”. Jest to formuła logiczna zatem jej wynikową wartością jest albo PRAWDA, albo FAŁSZ. Jeśli będzie to PRAWDA to komórka przyjmie format zgodny z wybranym stylem. Zastosowanie adresów względnych zapewnia, że wybór formatowania komórek B5, B4, B3 i B2 będzie zależał od wartości w odpowiadających wierszach.

W kontekście odwoływania się do adresów komórek w oknie „Formatowanie warunkowe” trzeba także wiedzieć, że adresy te nie podlegają modyfikacjom podczas zmiany położenia tych komórek. Jeśli komórki zostaną przeniesione albo zmienią adres na skutek np. wstawienia, albo usunięcia pewnych kolumn lub wierszy, to formuła zapisana w formatowaniu warunkowym nadal odwoływać się będzie do wpisanych adresów. W edycji tych formuł nie działa także klawisz funkcyjny SHIFT+F4 (w LibreOffice F4), który w normalnym trybie budowania formuł pozwala przekształcić adres do każdej postaci między adresem względnym a bezwzględnym.

W wydaniu Apache OpenOffice niedogodnością jest to, że o istnieniu obszarów formatowanych warunkowo dowiemy się, dopiero gdy to formatowanie „zadziała”. Nie dysponujemy żadnym narzędziem pozwalającym określić czy i gdzie takie obszary są. Dlatego warto aby autorzy tworzyli jakąś dokumentację. Żeby nie pisać oddzielnych opracowań, które mogą zaginąć, szczególnie gdy minie wiele czasu, można takie informacje zamieścić bezpośrednio w pliku. Wybierając polecenie „Plik – Właściwości…” można w zakładce „Opis” okna dialogowego „Właściwości” w polu „Komentarz” umieścić stosowne opisy. Ułatwi to prace nad ewentualnymi poprawkami.

Formatowanie warunkowe w LibreOffice.

Autorzy tej wersji znacznie rozbudowali możliwości formatowania warunkowego. Najważniejszą zmianą jest dodanie menadżera formatowania warunkowego. Jest on ukryty pod pozycją „Zarządzanie…” widoczną na rys. 5. Dzięki niemu można zobaczyć, w jakich miejscach arkusza zastosowane zostały style warunkowe oraz łatwo przejść do ich edycji albo modyfikacji. Drugą istotną zmianą jest możliwość zdefiniowania dowolnej liczby warunków.

Tak samo jak w wersji Apache OpenOffice formatowanie warunkowe można zdefiniować dla jednej komórki albo dla obszaru komórek. W tej wersji podczas tworzenia zasad formatowania warunkowego będzie dostępne pole, w którym w każdej chwili można określić zakres komórek objętych takim formatowaniem. Nie ma więc potrzeby wcześniejszego zaznaczania takiego obszaru, chociaż można to oczywiście zrobić. Samo formatowanie warunkowe można rozpocząć wybierając jeden z rodzajów formatowania, czyli „Warunek”, „Skala koloru”, „Pasek danych” „Zestaw ikon” albo „Data”. Można też przejść do opcji „Zarządzanie” i w niej dopiero wybrać nowy sposób formatowania.

LibreOffice ma cztery typy formatowania warunkowego. Są nimi „Wartość komórki jest”, „Formuła jest”, „Data” i „Wszystkie komórki”. Widoczne w menu opcje „Skala koloru”, „Pasek danych” i „Zestaw ikon” to w rzeczywistości warianty typu „Wszystkie komórki”.

Wartość komórki jest oraz Formuła jest.

Ten typ formatowania jest taki sam jak w wersji Apache OpenOffice dlatego nie będę tu ponownie omawiał zasad działania. Opcja „Warunki” otwiera okno dialogowe przygotowane do utworzenia warunków zgodnie z typem „Wartość komórki jest”. Okno to przedstawiam poniżej.

W rozwijanym polu typów warunków widać pozycje: „Wszystkie komórki”, „Wartość komórki jest”, „Formuła jest” oraz „Data”, czyli wszystkie typy formatowania. To oznacza, że niezależnie od wybranej opcji można wskazać inny typ formatowania. Od wyboru typu formatowania zależy widok okna dialogowego. Dla „Wartość komórki jest” jest on analogiczny do okna w Apache OpenOffice. Tutaj także z rozwijanego pola rodzaju relacji można wybrać jedną z takich samych, jakie omawiałem powyżej. W odróżnieniu od Apache OpenOffice początkowo dostępny jest jeden warunek, lecz przyciskiem „Dodaj” można dodać tyle nowych warunków ile będzie potrzebnych. Dodawany warunek zawsze będzie umieszczany na końcu listy i nie ma możliwości przeniesienia go. Jest to o tyle ważne, że sprawdzanie warunków odbywa się od pierwszego z nich i kończy na pierwszym, który spełnia warunki. Warunki zbędne można usunąć przyciskiem „Usuń”.

W dolnej części okna dialogowego znajduje się pole określające zakres komórek, którym zostanie przypisane formatowanie warunkowe. To tutaj w każdym momencie można ten zakres zmodyfikować. Można adresy wpisać ręcznie albo zaznaczyć je w arkuszu.

Mimo że przy polu wprowadzania wartości nie ma charakterystycznej ikonki zaznaczania obszaru w arkuszu, takiej jaką widać przy polu zakresu, można po ustawieniu kursora wewnątrz pola kliknąć komórkę a jej adres w postaci pełnego adresu bezwzględnego zostanie wstawiony do pola.

Podobnie jest po wybraniu typu „Formuła jest”. Wszystkie zasady omówione dla tego typu w rozdziale „Formatowanie warunkowe w Apache OpenOffice” powyżej – obowiązują także w tym przypadku.

Użytkownik porównujący oba programy arkuszowe zauważy brak przycisku „Nowy styl...” pozwalającego zdefiniować – niejako w biegu – nowy styl potrzebny w formatowaniu warunkowym. Rolę tego przycisku spełnia pierwsza pozycja z rozwijanej listy pola wyboru stylu.

Data.

Ten typ formatowania warunkowego dotyczy komórek zawierających datę. Do porównywania pobierana jest data systemowa i z nią jest porównywana analizowana relacja. Przykład takiego formatowania poniżej.

Pokazany na ilustracji obszar zawiera daty od 18 stycznia 2017 do 4 lutego 2017 i został sformatowany warunkowo przy pomocy czterech warunków, z których ostatni pokazany w oknie dialogowym to „Dzisiaj” z przypisanym stylem „czerwony”. Pierwszy warunek to: – „Jutro” ze stylem „zielony”, drugi – „Ostatnie 7 dni” a trzeci – „Przyszły miesiąc”, oba z przypisanym stylem „żółty”. Nie należy kierować się informacjami widocznymi na górnym marginesie „zamkniętych” warunków. Tam zawsze, w przypadku formatowania „Data” pojawia się napis „Data jest dzisiaj”.

Oprócz wymienionych dostępne są jeszcze relacje: „Wczoraj”, „Bieżący tydzień”, „Zeszły tydzień”, „Przyszły tydzień”, „Bieżący miesiąc”, „Przyszły miesiąc”,”Bieżący rok”, „Zeszły rok” i „Przyszły rok”. Przy korzystaniu z warunków związanych z tygodniem trzeba pamiętać, że Calc traktuje jako tydzień dni od niedzieli do soboty.

Widoczny efekt formatowania dotyczy dnia 29 stycznia 2017 roku. Widać na nim wpływ kolejności zdefiniowanych warunków. Pierwszy warunek określał „jutro” i ponieważ taka data wystąpiła w obszarze, komórka została objęta kolorem zielonym. Drugi warunek miał przypisać kolor żółty do dat z ostatnich siedmiu dni i to zrobił. Do tych dni wliczany jest także dzień „dzisiaj”. Trzeci warunek miał także przypisać kolor żółty, lecz do dat z przyszłego miesiąca. To także widać, bo data 31. stycznia nie jest zaznaczona. W końcu czwarty warunek miał przypisać kolor czerwony do „dzisiaj”. Dzień ten (29.01.2017) występuje w obszarze, ale został już sformatowany zasadą warunku drugiego, więc warunek czwarty nie zostanie wykorzystany.

Wszystkie komórki.

To ostatni typ formatowania warunkowego, który może wystąpić w wielu bardzo interesujących wariantach. Najpierw jednak zasada jego działania. Ten typ wymaga zaznaczenia obszaru zawierającego interesujące nas dane. Dane te stanowią pewną wspólną przestrzeń wartości, które zechcemy ze sobą w jakiś sposób porównać. Porównanie opiera się o wyznaczone albo wskazane przynajmniej dwie wartości odniesienia. Zawartość każdej komórki obszaru jest porównywana z wartościami odniesienia i dopiero wtedy do tej wartości przypisywana jest jej wizualizacja.

Wartościami odniesienia mogą być:

Min i Maks.
Wskazanie jednej z tych wartości to wyznaczenie wartości odniesienia będącej najmniejszą wartością w zaznaczonym obszarze albo wartością największą. Te wartości odniesienia są wyznaczane automatycznie na podstawie zaznaczonego obszaru.

Percentyl.
Jest to miara statystyczna dotycząca zbioru wartości określająca położenie elementów tego zbioru w zakresie wszystkich jego wartości. Wymaga podania dodatkowej informacji liczbowej – rzędu – określającego grupę elementów, dla której zostanie wyliczona wartość. Rząd podawany jest w procentach i dotyczy liczebności elementów, a nie ich wartości. Obliczony percentyl danego rzędu jest liczbą mówiącą o maksymalnej wartości osiąganej przez procent elementów wskazany przez rząd.. Wartość jest wyliczana automatycznie na podstawie zaznaczonego obszaru. Np. obliczenie percentyla 15%-owego mówi, że tę wartość lub mniejszą osiąga 15% elementów zbioru a percentyla 50% (znany on jest także jako mediana), że wartość tę lub mniejszą osiąga połowa elementów. Miara ta nie należy do powszechnie stosowanych więc wyjaśnię ją na przykładzie mediany. Jeżeli zbiorem wartości są liczby 6, 12, 57, 1, 6, 6, 18 to środkowym elementem tego siedmioelementowego zbioru jest element czwarty. Po ułożeniu wartości w kolejności narastającej 1, 6, 6, 6, 12, 18, 57 czwartym elementem jest liczba 6. Mediana, czyli percentyl 50% wynosi 6 i mówi, że 50% wyników osiąga co najwyżej wartość 6. Jeśli konkretnej wartości nie ma w zbiorze, np. dla przykładu z medianą zbiór miałby parzystą ilość elementów, wartość percentyla jest aproksymowana.

Wartość.
Jest wartością wpisaną bezpośrednio.

Procent.
Procent jest wartością obliczoną automatycznie dla każdego elementu zbioru. Wartość ta obliczana jest następująco: (x_n – min)/(maks – min) gdzie x_n jest n-tym elementem zbioru, min – najmniejszą wartością w tym zbiorze a maks – największą wartością. Dlatego najmniejszy element w zbiorze ma przypisaną wartość procentową 0% a największy 100%.

Formuła.
To ostatnia wartość odniesienia. W najprostszym przypadku jest to adres komórki zawierającej odpowiednią wartość albo formułę ją wyliczającą. Niestety w wersji programu Calc, którą opisuję, ten mechanizm nie funkcjonuje prawidłowo. Mam tu na myśli tylko wybór typu „Wszystkie komórki”. Zmiana wyniku formuły nie wpływa we właściwy sposób na wygląd danych, dopóty dopóki nie wywoła się ponownie edycji formatu warunkowego w tym obszarze. Dopiero ta akcja powoduje aktualizację wartości odniesienia. Nie trzeba nic więcej robić, można natychmiast anulować edycję. Okazuje się, że efekt naprawienia kolorowania pojawi się także wtedy, gdy rozmiar okna programu zostanie choćby minimalnie zmieniony. To jak na razie jest najlepsza metoda skorygowania kolorów, minimalizuj okno a następnie maksymalizuj. Jest to ewidentny błąd funkcjonowania tego mechanizmu. Miałem okazję sprawdzić to w wersji 5.0.2 i tam funkcjonuje on prawidłowo. Przetestowałem też to działanie w już udostępnionej wersji 5.3.0.3 ale i tu nie działa dobrze a ponadto pojawiły się dodatkowe błędy.

Po wybraniu typu formatowania „Wszystkie komórki” pojawi się nowe pole wyboru rodzaju formatowania. Jak na rysunku.

Dostępne rodzaje to: „Skala koloru (2 wpisy)”, Skala koloru (3 wpisy), „Pasek danych” i „Zestaw ikon”. Każdy z tych rodzajów otwiera własne okno warunku.

Skala koloru (2 wpisy).

Pole warunku dla tego rodzaju formatowania wymaga podania dwóch wartości odniesienia i przyjmuje następujący wygląd.

W rozwijanym polu wartości odniesienia należy wskazać rodzaj wartość. Na ilustracji widać listę wszystkich omówionych powyżej rodzajów. Kolejne pole znajdujące się pod wybranym rodzajem wartości odniesienia nie jest wypełniane, jeśli wybranym rodzajem jest Min albo Maks. W pozostałych przypadkach należy tam wpisać odpowiednią wartość bezpośrednią (gdy wybrano Wartość) rząd (gdy wybrano Pertencyl) liczbę procent (gdy wybrano Procent) albo formułę np. w postaci „=B33” (gdy wybrano Formuła). W polu położonym najniżej wybiera się kolor, jaki zostanie przypisany tej wartości. Wszystkie te pola są widoczne także po prawej stronie. Tutaj należy określić drugi punkt odniesienia.

Zasada skali koloru jest następująca: wszystkie wartości w obszarze, które są poniżej albo odpowiadają pierwszej wartości odniesienia zostaną pokolorowane pierwszym kolorem. Wszystkie wartości odpowiadające drugiej wartości albo większe – będą pokolorowane drugim kolorem. Pozostałe wartości będą miały przypisany kolor pośredni między kolorem pierwszym a drugim, przy czym kolor pośredni zostanie wyznaczony na podstawie wartości elementu.

Przykład takiego formatowania przedstawia rysunek poniżej.

W tym zestawieniu celowo wpisałem narastającą sekwencję wartości aby pokazać jak zmienia się skala koloru. Wartościami odniesienia jest procent. Wartości odpowiadające co najwyżej 25% mają kolor „Light green”, wartości odpowiadające co najmniej 85% maja kolor „Light red” a pozostałym wartościom został przypisany gradient wynikający z odpowiedniej wartości procentowej.

Użytkownik w rzeczywistości nie jest zainteresowany tym jak te gradienty są wyznaczane, bo i tak nie ma na to wpływu. Dla zainteresowanych podam jednak wyjaśnienie. Każdy kolor określony jest trzema składowymi kolorów podstawowych: R (czerwonego), G (zielonego) i B (niebieskiego). Składowe przyjmują wartości z zakresu od 0 do 255. Gradient pośredni obliczany jest dla każdej składowej w wg następującego wzoru:

S jest składową koloru R, G lub B.

S_max, S_min są odpowiednio większą i mniejszą z wartości tej składowej w obu kolorach granicznych.

x_max, x_min są odpowiednio największym i najmniejszym elementem zbioru wartości.

x jest wartością elementu, dla którego obliczana jest odpowiednia składowa.

Skala koloru (3 wpisy).

Pole warunku dla tego rodzaju formatowania zawiera jeszcze jedną wartość odniesienia. Występują w związku z tym trzy kolory. Zasada formatowania jest analogiczna jak ta omówiona powyżej. Przykład takiego formatowania przedstawia kolejna ilustracja.

W tym przykładzie zastosowałem różne wartości odniesienia. Pierwszą jest wartość minimalna w zbiorze, drugą jest konkretnie wskazana wartość 410, która powinna przyjąć kolor żółty, trzecią wartością odniesienia jest 85%.

Pasek danych.

Ten rodzaj formatowania warunkowego przypomina utworzenie wykresu słupkowego. W obszarze komórek każda jest wypełniona kolorem stosownie do wartości, jaką zawiera. Okno dialogowe określające wartości odniesienia, które w tym rodzaju formatowania są także dwie, wygląda tak.

Rozwinięte pole wyboru wartości odniesienia widoczne jest po prawej stronie. Nową pozycją jest tu „Automatyczny”, która pozwala na dobór wartości przez oprogramowanie. Dodatkowe opcje formatowania można określić po naciśnięciu przycisku „Więcej opcji…”. Następna ilustracja pokazuje przykład zastosowania tego rodzaju formatowania oraz znaczenie tych dodatkowych opcji.

Dla zademonstrowania wszystkich możliwości jedna z wartości jest ujemna. Wszystkie wartości w komórkach zostały wyrównane do lewej krawędzi. Widoczne na ilustracji okno „Pasek danych” obrazuje zastosowane ustawienia do zebranych danych. Pierwsze dwie wartości to widoczne także na rys. 16. wartości odniesienia.

W sekcji „Pasek kolorów” można wybrać kolor, jakim zostaną przedstawione wartości dodatnie i kolor dla wartości ujemnych. Dodatkowo można wybrać w polu „Wypełnienie” to, czy słupek będzie jednolity, czy gradientowy.

W sekcji „Oś” można określić położenie osi pionowej. Wybrać można jedną z trzech możliwości: „Automatyczny”, „Środek” albo „Brak”. Brak osi oznacza, że wartości ujemne nie będą formatowane. „Środek” oznacza, że w środku komórki pojawi się oś i wartości dodatnie będą prezentowane po prawej stronie tej osi a ujemne, po lewej. „Automatyczny” wystawi oś w zależności od wartości dodatnich i ujemnych. Można w tej sekcji dodatkowo określić kolor osi. Trzeba pamiętać, że oś nie dzieli komórki na dwa odrębne obiekty, jest tylko elementem graficznym wewnątrz komórki.

Sekcja „Długość paska” umożliwia określenie, jaką część komórki może zająć pasek. Parametrów tych nie można zmienić, jeżeli w sekcji „Oś” wybrana jest opcja „Automatyczne”. W tej sekcji można także zaznaczyć chęć wyświetlenia tylko paska, bez wartości.

Formatowanie warunkowe „Pasek danych” tworzy wyłącznie pasek poziomy. Orientacja danych wpisanych do komórki nie ma na to wpływu.

Zestaw ikon.

To ostatni rodzaj formatowania obszaru komórek. W jego wyniku na lewej krawędzi komórki zostanie umieszczona ikona, czyli obrazek przypisany do wartości. W przypadku tego typu formatowania podaje się wartości progowe, od których wartość w komórce będzie oznaczoną stosowną ikonką. Calc przewiduje tu wiele symboli i użytkownik sam może wybrać, z jakich zechce skorzystać.

Poniżej okno dialogowe dostępne po wybraniu zestawu symboli.

Ilustracja przedstawia rozwinięte pole wyboru ikon, po to by pokazać ich listę, zaś sam warunek jest przygotowany do zestawu nazwanego „4 oceny”. Widać, że należy określić trzy progi, które zadecydują jaka z czterech ikon zostanie przypisana do wartości.

Przykład zastosowania różnych ikon – do tych samych danych, jakie przedstawiłem na rys. 17. – zamieszczam poniżej.

Na ilustracji przedstawiłem sześć kolumn tych samych wartości sformatowanych warunkowo z wykorzystaniem różnych ikon. Progi nie są jednakowe.

Zarządzanie formatowaniem warunkowym.

W programie Calc LibreOffice autorzy dodali bardzo przydatną możliwość zarządzania zdefiniowanymi formatowaniami. Zapewnia ją pozycja „Zarządzanie…” w rozwijanym menu polecenia „Format – Formatowanie warunkowe”. Otwierane jest okno dialogowe takie jakie pokazuję poniżej.

W górnej części okna umieszczone są wszystkie obszary, w których formatowanie warunkowe zostało zdefiniowane wraz z opisem pierwszego warunku. Poniżej znajdują się trzy przyciski: „Dodaj” – pozwalający dodać nowy zakres i typ formatowania warunkowego, „Edycja…” – pozwalający przywołać wskazany obszar w celu jego modyfikacji oraz „Usuń” – usuwający formatowanie warunkowe wybranego obszaru.

Ilustracja przedstawia to okno wywołane w związku z ostatnim przykładem wykorzystania zestawu ikon. Trzeba bowiem nadmienić, że okno zarządzania zawierać będzie zdefiniowane obszary występujące tylko w tym arkuszu, w jakim okno zostało przywołane. Jeśli arkusz nie zawiera żadnych obszarów zawierających formatowanie warunkowe górna część tego okna będzie pusta. Przyciskiem „Dodaj” można w arkuszu utworzyć nowy obszar z formatowaniem warunkowym.

Jak już napisałem wcześniej formatowanie warunkowe jest także usuwane przy skorzystaniu ze skrótu klawiaturowego CTRL+M odpowiadającego poleceniu „Format – Wyczyść formatowanie bezpośrednie”.