Yestok.pl

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

Wstęp.

Arkusze kalkulacyjne od dość dawna przestały być wyłącznie tabelkami, w których obliczane są różne wartości. Ze względu na strukturę tabelaryczną stały się także wygodnym narzędziem do rejestrowania zdarzeń. Ze zdarzeniem związany jest określony zestaw wartości, który je charakteryzuje. Jeżeli zdarzeniem będzie sprzedaż towaru, to wartościami je opisującymi mogą być: data sprzedaży, nazwa towaru, jednostka miary, w jakiej mierzona jest ilość towaru (sztuka, para, kilogram, metr kwadratowy), cena jednostkowa towaru i sprzedana ilość.

Jeśli zdarzeniem będzie np. wystawienie oceny uczniowi, to zestaw wartości przypisanych do takiego zdarzenia zawierałby: datę wystawienia oceny, nazwisko nauczyciela, nazwę przedmiotu, nazwisko ucznia i wystawiony stopień.

Taki sposób rejestrowania zdarzeń jest łatwy do zrealizowania właśnie w arkuszu kalkulacyjnym. Widać wówczas, że każde zdarzenie ma taką samą strukturę i można je opisać w jednym wierszu w taki sposób, że każda kolumna wiersza zawiera odpowiednią wartość. Zapisane w ten sposób wartości utworzą tabelę a aby zapewnić jej przejrzystość wystarczy w pierwszym jej wierszu umieścić opisową nazwę zawartości poszczególnych kolumn. Ze względu na to, że obecnie arkusz może zawierać ponad milion wierszy, dokładnie 1048576, to taki rejestr może być całkiem pokaźny.

Sposób zorganizowania rejestru, jak widać, zależy od użytkownika, to on decyduje jakie wartości chce zarejestrować w jednostkowym zdarzeniu. Gdyby zakładał, że do analizy sprzedaży będzie potrzebował informacji o producencie towaru – powinien taką wartość także dopisać do zdarzenia jednostkowego. Gdyby w analizie postępów w nauce ważne było rozróżnienie, w jaki sposób została wystawiona ocena (kartkówka, sprawdzian, odpytywanie, aktywność na zajęciach) należałoby i taką wartość umieścić w zapisie zdarzenia.

Takie rejestry dość szybko osiągają znaczne rozmiary. Rozważmy najpierw rejestr sprzedaży. Załóżmy, że problem dotyczy niewielkiego sklepu, otwartego 10 godzin w ciągu dnia, w którym klient pojawia się średnio co 10 minut i kupuje średnio 4 artykuły. To oznacza, że codziennie powstaje 240 zdarzeń sprzedaży towaru a po trzydziestu dniach takich zdarzeń w rejestrze przybywa o 7200.

Nie inaczej może być przy rejestracji ocen. Niech w sześcioklasowej szkole, każda klasa ma cztery oddziały po 20 uczniów. Daje to 480 uczniów, z których każdy w semestrze powinien otrzymać średnio trzy oceny w każdym z (średnio biorąc) siedmiu przedmiotów. To oznacza, że zdarzeń opisujących wystawienie oceny może być 10080 w jednym semestrze.

Tak duża ilość danych znacznie utrudnia wyciąganie na ich podstawie wniosków, nie jesteśmy bowiem w stanie w sposób prosty przeanalizować takiej ich ilości. Dlatego wymyślono tabele przestawne (Pivot table).

Pierwsze podejście do całkiem nowego sposobu analizowania danych zaproponowała firma Lotus, która w 1990 roku wprowadziła na rynek nowy program z kategorii biznesowej o nazwie Improv. Dość skomplikowany sposób obsługi tego programu, zwłaszcza w stosunku do funkcjonujących już programów arkuszy kalkulacyjnych sprawił, że nie zdobył on wielkiej popularności. W Polsce był praktycznie mało znany. Jednak jego idea została przez twórców arkuszy zaadaptowana i istnieje obecnie w każdym z nich pod postacią tabeli przestawnej.

Jak podaje Wikipedia:
Tabela przestawna jest narzędziem analitycznym arkusza kalkulacyjnego pozwalającym filtrować, wybierać, przestawiać kolumny i wiersze z danymi w arkuszu kalkulacyjnym, aby uzyskać wynikowy, odmienny, bardziej przejrzysty i użyteczny od pierwotnego (źródłowego) układ danych, bez naruszania oryginalnej źródłowej tabeli lub tabel. Zazwyczaj tabela przestawna jest generowana przez arkusz kalkulacyjny w odrębnej zakładce, na podstawie wprowadzonych w oknie dialogowym poleceń użytkownika, i może być automatycznie aktualizowana, gdy zmieniane są dane źródłowe.

Technika ta umożliwia uzyskanie informacji niewidocznych na ogół w skomplikowanym i wielowymiarowym, oryginalnym układzie tabeli (tzw. danych źródłowych) i sprawia, że są one bardziej czytelne dla użytkownika.

Podstawą utworzenia tabeli przestawnej jest rejestr zdarzeń, przy czym wymaga się aby pierwszy wiersz rejestru zawierał niepowtarzalne nazwy opisujące poszczególne kolumny kolekcji. Nazwy te są wykorzystywane podczas tworzenia tabeli przestawnej, dlatego warto tworzyć je tak aby określały jednocześnie rodzaj zapisanych wartości, co poprawi czytelność rejestru i ułatwi interpretację tabeli.

Najogólniej rzecz ujmując tabela przestawna składa się z czterech obszarów, z których trzy najważniejsze to: obszar wierszy, obszar kolumn i obszar danych. Czwarty obszar to obszar strony i jego rolę wyjaśnię w dalszej części. W każdym z tych obszarów można ulokować wspomnianą przed chwilą nazwę i to decyduje o ostatecznym wyglądzie tabeli przestawnej.

Aby dokładniej wyjaśnić znaczenie tych obszarów przedstawiam przykładowy rejestr. Wartościami zapisanymi w rejestrze są: nazwy towarów, nazwy producentów tych towarów, ceny jednostkowe, ilości sprzedane w jednej transakcji i wartości tej transakcji.

W tak zorganizowanym zestawieniu wiersze nazywamy rekordami. Poszczególne kolumny rejestru mają nagłówki: „Towar”, „Producent”, „Cena”, „Ilość” i „Wartość”, która w tym przypadku jest wyliczana jako iloczyn ceny i ilości. Nagłówki te stają się nazwami pól w poszczególnych rekordach. Zatem każdy rekord składa się z pól: „Towar”, „Producent”, „Cena”, „Ilość” i „Wartość” a zawartość każdego pola jest charakterystyczna dla konkretnego rekordu. Rejestr złożony z rekordów, z których każdy składa się z pól, nazywamy bazą danych i dlatego w dalszej części opracowania będę się posługiwał terminem baza danych. Jak wspomniałem każda nazwa (a więc pole) może zostać ulokowana w jednym z obszarów tabeli przestawnej. Jak to się robi pokażę w dalszej części, teraz chcę wyjaśnić znaczenie tych obszarów.

Umieszczenie nazwy pola w obszarze wierszy oznacza, że boczkiem tabeli przestawnej będą wartości tego pola, przy czym każda występująca wartość pojawi się tylko jeden raz. Dla przykładu, jeśli polem umieszczonym w obszarze wierszy byłoby „Towar”, to tabela przestawna składałaby się z czterech wierszy, bowiem pole „Towar” zawiera we wszystkich rekordach tylko cztery różniące się wartości: „Baton”, „Bombonierka”, „Czekolada” i „Lizak”. Wartości te stanowiłyby boczek tabeli. Gdyby natomiast wybranym polem było „Producent” to tabela przestawna zawierałaby siedem wierszy, bo pole „Producent” zawiera siedem różniących się wartości: „Bambi”, „Goplana”, „Nestle”, „Olza”, „Solidarność”, „Wawel” i „Wedel”.

Umieszczenie pola „Towar” w obszarze wierszy oraz niewypełnianie pozostałych obszarów spowoduje utworzenie tabeli przestawnej takiej jak pokazuję poniżej.

Tak jak wspomniałem każda wartość jest interpretowana tylko raz. Proszę zwrócić uwagę na to, że w wartościach tekstowych nie są rozróżniane wielkości liter, zatem wartości: „Czekolada”, „CZEKOLADA” oraz „czekolada” występujące w różnych rekordach bazy danych są traktowane jako jedna, taka sama wartość.

Analogicznie do obszaru wierszy zachowuje się obszar kolumn, z tym że określa on nagłówek tabeli przestawnej. Gdyby więc pokazaną powyżej tabelę zmieniono tak, że dodatkowo w obszarze kolumn umieszczono by pole „Producent”, wygląd tabeli zmieniłby się na następujący:

Kolejny ważny obszar to obszar danych. Na rysunku 2. tym obszarem jest widoczna pusta kolumna a na rysunku 3. pusty obszar komórek. Pole umieszczone w obszarze danych spowoduje, że tabela przestawna dokona obliczeń na podstawie wartości występujących w tym wybranym polu bazy. O tym, jakie to będą obliczenia decyduje autor tabeli, przy czym domyślnym obliczeniem jest SUMA. Dla tych dwóch przedstawionych tabel (tych na ilustracjach 2 i 3) w obszarze danych wykorzystałem pole „Wartość”. Domyślnym działaniem jest SUMA więc w tabeli zostały umieszczone sumy odpowiadające poszczególnym wierszom lub wierszom i kolumnom. Odpowiednie wynikowe tabele przestawne przyjmą więc postać jak na ilustracji poniżej.

Tabela przestawna reprezentuje zatem w syntetyczny sposób dane znajdujące się w dużej bazie danych a o tym, jakie to będą dane i jak zorganizowane decyduje autor tabeli.

Wygląd tabeli przestawnej.

Boczek tabeli przestawnej tworzą wartości pola umieszczonego w obszarze wierszy. Ponad wartościami znajduje się przycisk z umieszczoną w nim nazwą pola. Nagłówek tabeli przestawnej tworzą wartości pola umieszczonego w obszarze kolumn. Ponad nagłówkami, po lewej stronie pojawi się analogiczny przycisk. Obliczone dane tabeli przestawnej tworzą wartości tego pola, które zostało umieszczone w obszarze danych.

Aby powstała tabela przestawna wystarczy aby w dowolnym obszarze ulokowano przynajmniej jedno pole. Widać to choćby na rys. 2. na którym zaprezentowana tabela przestawna zawiera wyłącznie nazwę pola „Towar” umieszczoną w obszarze wierszy. Tabela utworzyła jednocześnie kolumnę na dane, lecz nagłówek tej kolumny zawiera napis „(pusty)” co oznacza, że w obszarze danych nie umieszczono żadnego pola.

Jeżeli w tabeli zawierającej wyłącznie boczek określono w obszarze danych nazwę pola to wyliczone wartości pojawią się w kolumnie a w jej nagłówku pojawi się opis wyliczanej wartości. To widoczny napis „Suma – Wartość” określający, że wartości pola „Wartość” zostały zsumowane. W wersji Apache OpenOffice ten typ tabeli przestawnej nie zawiera informacji opisowej. Dla tabeli przestawnej zawierającej także nagłówek informacja opisowa wyliczanej wartości znajduje się w lewym górnym rogu tabeli. I jest tak w obu wersjach programu Calc.

Wyjaśnienia wymaga tutaj widoczny przycisk „Dane”. Pojawił się on w nagłówku tabeli zamiast oczekiwanego przycisku „Producent” wprowadzając pewne zamieszanie. Ten przycisk pojawia się w wydaniu LibreOffice (na skutek specyficznej struktury obszaru kolumn) wówczas gdy tabela przestawna zawiera nagłówek. Ten aspekt objaśnię później, podczas omawiania tworzenia tabeli przestawnej.

Wszystkie obliczone wartości sum znajdujące się w obszarze danych są w rzeczywistości wynikami funkcji arkusza kalkulacyjnego BD.SUMA należącej do kategorii Funkcje Bazy Danych. Argumentami funkcji baz danych są: Baza danych – określający obszar zajmowany przez bazę, Pole Bazy Danych – określający kolumnę bazy danych, jaka będzie brana do obliczeń i Kryteria Wyszukiwania – określający jakie warunki musi spełnić rekord aby pole bazy danych wzięło udział w obliczeniach. To właśnie te parametry są „ustalane” podczas definiowania tabeli przestawnej.

Widoczna w tabeli przestawnej wartość jest wynikiem wykonania stosownych obliczeń, a nie umieszczeniem funkcji bazy danych w komórce tabeli. Obliczanie sumy jest domyślnie przyjętą formułą obliczeniową. Autor może jednak zadysponować inne obliczenia.

Poniżej przedstawiam ich listę. Ponieważ występują nieznaczne różnice w nazewnictwie zastosowanym w LibreOffice i Apache OpenOffice jako nazwę podstawową podaję tę zastosowaną w LibreOffice, w nawiasach kwadratowych nazewnictwo użyte wApache OpenOffice (jeśli jest inne).

• Zlicz [Liczba] – odpowiada obliczeniu funkcji BD.ILE.REKORDÓW.A, która odpowiada za zliczenie liczby wystąpień każdej wartości pola podanego w obszarze danych.
• Zlicz (tylko liczby) [Licznik (Numbers only)] – odpowiada obliczeniu funkcji BD.ILE.REKORDÓW, która odpowiada za zliczenie liczby wystąpień każdej wartości pola podanego w obszarze danych, przy czym zliczane są tylko te wartości pola, które są liczbami.
• Średnia – odpowiada obliczeniu funkcji BD.ŚREDNIA, która oblicza wartość średnią z wartości wskazanego pola.
• Maks – odpowiada obliczeniu funkcji BD.MAKS, która zwraca wartość największą występującą w polu.
• Min – odpowiada obliczeniu funkcji BD.MIN, która zwraca wartość najmniejszą występującą w polu.
• Iloczyn – odpowiada obliczeniu funkcji BD.ILOCZYN, która zwraca iloczyn wartości pola.
• Odch. std. (Próbka) – odpowiada obliczeniu funkcji BD.ODCH.STANDARD, która wylicza odchylenie standardowe dla wartości pola.
• Odch. std. pop (Populacja) [Odch.std (Populacja)] – odpowiada obliczeniu funkcji BD.ODCH.STANDARD.POPUL, która także wylicza odchylenie standardowe wartości pola.
• Wariancja (Próbka) – odpowiada obliczeniu funkcji BD.WARIANCJA, która wylicza wariancję wartości pola.
• Wariancja pop. (Populacja) [Wariancje (Populacja)] – odpowiada obliczeniu funkcji BD.WARIANCJA.POPUL, która wylicza wartość wariancji pola.

Gdy do utworzenia tabeli przestawnej wykorzystano także obszar strony zostanie ona uzupełniona o dodatkowy element, przycisk z nazwą pola umieszczony w obszarze strony wraz z rozwijalną listą informującą o wykorzystanych wartościach tego pola. Przykład takiej tabeli przedstawia rysunek 5.

Dla tabeli z górnej części poprzedniej ilustracji umieściłem w obszarze strony pole „Producent”. Napis „- wszystko -” w rozwijanym polu informuje, że obliczone wartości dotyczą wszystkich producentów.

Tworzenie tabeli przestawnej.

Aby utworzyć tabelę przestawną należy wskazać obszar zajmowany przez bazę danych, określić jakie pola znajdą się w wierszach, a jakie w nagłówku tabeli (jeśli nagłówek będzie potrzebny) oraz wskazać pola obliczeniowe i określić sposób ich obliczania no i oczywiście określić gdzie tabela przestawna ma się pojawić.

Istnieją minimalne różnice w wyglądzie interfejsu tworzenia tabeli w wersjach LibreOffice i Apache OpenOffice, dlatego przedstawię je oddzielnie. Sama zasada tworzenia tabel jest natomiast identyczna. W LibreOffice za wstawienie tabeli przestawnej odpowiada polecenie „Wstaw – Tabela przestawna...”. Spowoduje ono otwarcie pierwszego okna dialogowego „Wybierz źródło” pozwalającego określić bazę dla tworzonej tabeli.

W wersji Apache OpenOffice programu Calc za wstawienie tabeli przestawnej odpowiada polecenie „Dane – Tabela przestawna – Utwórz...”. To polecenie otworzy analogiczne okno dialogowe.

Pierwszym wyborem jest „Bieżące zaznaczenie”. Oznacza on, że obszar bazy danych znajduje się w tym samym arkuszu i jest aktualnie zaznaczony. W obu wersjach programu Calc, gdy nie zaznaczono żadnego obszaru, nastąpi automatyczne rozpoznanie i zaznaczenie obszaru bazy danych na podstawie otoczenia aktualnej komórki aktywnej. Jeśli bazy nie uda się wyznaczyć, programy przyjmą, że bazą jest bieżąca komórka aktywna. Jest to oczywiście niedorzeczne, dlatego w dalszej części procedury można wskazać faktyczny zakres bazy danych.

Drugim wyborem w LibreOffice jest „Nazwany zakres” Rozwijana lista zawiera wszystkie zdefiniowane nazwy, jeśli jakaś nazwa odpowiada bazie danych można ją wskazać. Takiego wyboru nie oferuje Apache OpenOffice.

Trzecim wyborem w LibreOffice jest „Źródło danych zarejestrowane w LibreOffice”. Analogiczny wybór – „Źródło danych zarejestrowane w OpenOffice” – znajduje się na drugiej pozycji Apache OpenOffice. Ta opcja pozwala wskazać bazy danych zarejestrowane w pakiecie (x)Office. O zasadach rejestrowania baz więcej przeczytać można w rozdziale „Źródło danych” opracowania poświęconego korespondencji seryjnej. Dzięki tej możliwości sama baza danych może znajdować się w innym pliku i innej lokalizacji i może być aktualizowana przez różnych użytkowników.

Trzecim wyborem w Apache OpenOffice jest „Zewnętrzne źródło lub zewnętrzny interfejs”. Jest to możliwość skorzystania z zewnętrznych serwerów baz danych wykorzystując np. technologię Online Analytical Processing (OLAP). Takiej możliwości nie oferuje LibreOffice. Ten temat nie będzie poruszany w tym opracowaniu. Nawiasem mówiąc, nawet w systemie pomocy Apache OpenOffice na ten temat znajduje się tylko lakoniczny wpis:

Zwróćmy uwagę na to, że nawet nie przetłumaczono angielskiej wersji pomocy. Przy okazji, użytkownicy Apache OpenOffice powinni wiedzieć, że w systemie pomocy termin „tabela przestawna” nie zawsze został przetłumaczony i czasem pojawia się jako termin: „pivot table”.

Po zatwierdzeniu bazy danych pojawia się kolejne okno dialogowe. To okno finalizuje powstanie tabeli przestawnej.

W wersji dla LibreOffice ma ono wygląd jak na ilustracji poniżej.

W wersji Apache OpenOffice okno dialogowe wygląda jak poniżej:

.

Okna te wywołałem wskazując jako bazę danych tę pokazaną na rys. 1. W obu oknach po prawej stronie znajduje się lista dostępnych pól występujących w bazie danych. W LibreOffice znajduje się ona w obszarze „Pola dostępne”. W Apache OpenOffice pola przedstawione są za pomocą odpowiednio nazwanych przycisków w obszarze „Pola”.

Omawiając znaczenie obszarów pominąłem wyjaśnienie znaczenia obszaru „Pola stron” (LibreOffice) czy „Obszar strony” (Apache OpenOffice). Ten obszar nabiera znaczenia przy bardziej złożonych bazach danych i może jest już pora aby o nim wspomnieć.

Wrócę do zilustrowanej bazy. Każdy zauważy, że żaden sklep nie sprzedawałby tylko czterech artykułów, zazwyczaj jest ich nawet kilkaset. Ponieważ tabela przestawna zawiera wszystkie różniące się wartości wybranego pola, np. wszystkie artykuły, powstałaby długa ich lista. Lista ta ułożona jest alfabetycznie, przez co czytelność tabeli jest kłopotliwa, bowiem kolejność artykułów mogłaby być podobna do takiej: Ananas, Banan, Baton, Bombonierka, Burak, Cebula, Chili, Cukier, Czekolada itd. alfabetycznie wszystkie towary. Nie jest łatwo w takim zestawieniu „zobaczyć globalnie” dane dotyczące np. słodyczy, owoców lub warzyw. Załóżmy, że baza została wzbogacona o jeszcze jedno pole o nazwie „Kategoria” zawierające dla każdego rekordu przypisaną wartość kategorii, w rodzaju np. „Słodycze”, „Wypieki”, „Kawy”, „Przyprawy” itp. Dzięki takiej modyfikacji, umieszczając w obszarze strony nazwę pola „Kategoria” możemy zarządzać tym jaka kategoria towarów zostanie uwzględniona w tabeli przestawnej.

Tworząc tabelę przestawną, w każdym z tych programów, należy „chwycić” i przeciągnąć wybraną nazwę pola do odpowiedniego obszaru. Nazwy pól znajdujące się już w obszarach można przeciągać między nimi modyfikując w ten sposób tabelę przestawną. Jeśli nazwę pola wprowadzono do obszaru omyłkowo, to aby ją usunąć należy kliknąć myszką, a następnie nacisnąć klawisz DELETE. W Apache OpenOffice, zamiast takiego działania można „wyciągnąć” nazwę pola poza obszar, w jakim się znajduje. Ta druga możliwość jakoś nie udała się programistom z LibreOffice i nie można w ten sposób usunąć nazwy pola z obszaru.

Przyglądając się ilustracji okna dialogowego „Układ tabeli przestawnej” zauważamy, że w obszarze „Pola kolumn” już znajduje się pole „Dane”. Jest to właściwie „pseudopole”, gdyż nie występuje ono w bazie danych, a jednak nazwa ta pojawia się w obszarze. Pole to może zostać usunięte opisaną powyżej metodą. Jednak po ponownym otwarciu okna dialogowego jest znowu widoczne. Pole to można jedynie przeciągać między obszarami kolumn i wierszy lub przeciągnąć je na niższą pozycję na liście pól w obszarze. Jeśli znajduje się na najwyższej pozycji swojego obszaru w tabeli przestawnej, jego nazwa pojawi się w postaci przycisku jako nagłówek opisujący kolumny albo wiersze (to zależy, w jakim obszarze się znajduje). Dlatego na rysunku 3. taki właśnie przycisk znajduje się w komórce B1 tej tabeli. Jednocześnie w komórce A2 znajduje się przycisk odpowiadający polu „Towar” w bazie danych. W wydaniu Apache OpenOffice w oknie dialogowym „Tabela przestawna” nigdy nie pojawia się pseudopole „Dane”, chociaż w pewnych okolicznościach, o jakich napiszę później może w tabeli przestawnej pojawić się przycisk „Dane”. Dlatego w odpowiedniku komórki B1 tabeli przestawnej wersji Apache OpenOffice znajdzie się przycisk z nazwą pola „Producent”. Przycisk „Dane”, ale tylko przycisk, pojawi się wtedy, gdy w obszarze „Obszar danych” tabeli przestawnej umieszczono więcej niż jedno pole, będzie o tym mowa niżej.

Poniżej na ilustracji pokazuję okno dialogowe układu tabeli przestawnej odpowiadające tej pokazanej w dolnej części rys. 4. Ilustracja ma jednocześnie wyjaśnić, w jaki sposób definiuje się sposób obliczeń danych.

Ilustracja zawiera w istocie dwa okna dialogowe. Podstawowe, „Układ tabeli przestawnej”, ukazujące rozmieszczenie nazw pól w poszczególnych obszarach i „Pole danych”, w którym można określić sposób obliczania i wyświetlania wartości. Strzałki wskazują jak przeciągnięto nazwy pól.

Dwukrotne kliknięcie myszką nazwy pola znajdującej się w obszarze „Pola danych” otworzy dodatkowe okno dialogowe „Pole danych”, to jest to drugie okno dialogowe widoczne na ilustracji. Za pomocą tego okna można wybrać funkcję, jaka ma zostać zastosowana do wyznaczenia wartości w tabeli przestawnej oraz ustalić zasady wyświetlania wartości. W górnej części tego okna wskazuje się wybraną funkcję. Znaczenie tych funkcji wyjaśniłem już wcześniej. O zasadach wyświetlania wartości napiszę w dalszej części.

Nazwa pola znajdująca się w obszarze „Pola danych” zawsze poprzedzona jest nazwą funkcji, jaka zostanie użyta do obliczenia wyświetlanej wartości. W wersji LibreOffice nie przetłumaczono nazw tych funkcji, dlatego na ilustracji widać tekst „Sum – Wartość”, a nie „Suma – Wartość”. Odpowiednio np. funkcji „Zlicz” odpowiadać będzie napis „Count” a funkcji „Średnia” napis „Mean”. W wydaniu Apache OpenOffice występują polskie odpowiedniki nazw.

W tabelach przestawnych w polach obszarów można umieszczać więcej nazw pól. Tabela jest wówczas zorganizowana w ten sposób, że pole umieszczone najwyżej jest polem nadrzędnym względem pola leżącego niżej. Ten sposób hierarchizowania wartości dotyczy jednak tylko pól umieszczanych w obszarach wierszy, kolumn i strony. Najlepiej widać to na kolejnej ilustracji, na której w obszarze „Pola wierszy” umieściłem pola „Producent” na pierwszym miejscu i „Towar” na drugim.

Jak widać na ilustracji, dla każdego producenta – wymienionego w kolejności alfabetycznej – wystąpią wiersze z jego produktami – także wykazane w kolejności alfabetycznej.

Wprowadzenie kilku nazw pól do obszaru danych nie ustawia ich hierarchicznie, lecz przypisuje obliczone wartości grupami do poszczególnych pozycji „wiersz-kolumna”. Przykład tego ukazuje rys. 12. Tutaj w obszarze „Pola danych” wprowadziłem trzy pola: „Wartość”, obliczające sumę tego pola, „Towar” zliczające liczbę wystąpień tego pola oraz „Ilość” obliczające sumę tego pola. W efekcie można przeanalizować, jakie uzyskano dochody ze sprzedaży poszczególnych towarów, ilu klientów kupiło dany towar i ile go sprzedano.

Przy okazji tej ilustracji proszę zwrócić uwagę na to, że w obszarze „Pola danych” umieściłem pole „Towar”, pole, które nie zawiera wartości liczbowych, lecz nazwę artykułu. Do takiego pola można zastosować jedynie regułę zliczenia wystąpień pola a w tym konkretnym przypadku poda ono ile zrealizowano transakcji sprzedaży tego towaru.

Gdy w obszarze danych jest więcej niż jedno pole, w tabeli przestawnej Apache OpenOffice pojawi się przycisk „Dane”, nie ma on jednak odwzorowania w obszarach okna dialogowego „Tabela przestawna”.

Modyfikowanie tabeli przestawnych.

Czasami ustalone parametry tabeli przestawnej należy zmienić. Może to być spowodowane koniecznością zmiany sposobu obliczania pól, zmiany układu pól czy choćby zmiany obszaru źródłowego. Aby przywołać potrzebne do ego okno dialogowe należy aktywować dowolną komórkę znajdującą się wewnątrz tabeli przestawnej i ponownie wywołać akcję tworzenia tabeli. Ponieważ aktywną komórką jest ta wewnątrz tabeli przestawnej, aparat tworzenia tabeli przestawnej nie będzie analizował otoczenia pod kątem bazy danych, lecz zostanie otwarte okno dialogowe z aktualnymi parametrami tej tabeli.

To samo okno dialogowe można też wywołać klikając prawym przyciskiem myszki w obszarze tabeli i wybierając z menu kontekstowego widoczną pozycję „Edycja układu...” dla LibreOffice albo „Edytuj układ” dla Apache OpenOffice.

Oba menu widoczne są na rys. 13. poniżej.

Pozostałe pozycje tego menu są takie same i oznaczają: „Usuń” – usuń tabelę przestawną, „Filtr...” – otwórz okno dialogowe do ustalenia filtru. Ten element nie był jeszcze omawiany, lecz dzięki filtrowi można zdefiniować warunki, jakie muszą spełnić rekordy bazy danych aby zostały uwzględnione w tabeli przestawnej. Zdefiniowanie i zaakceptowanie filtrów wpływa od razu na wygląd tabeli przestawnej. „Odśwież” – to działanie ponownie przeanalizuje obszar bazy danych i zmodyfikuje tabelę przestawną. Jest to bardzo istotna opcja, gdyż tabela przestawna nie podlega automatycznemu przeliczaniu, gdy zmieniane są zawartości pól bazy danych albo został zmieniony obszar źródłowy.

Polecenia „Odśwież” i „Usuń” dostępne są także w obu wersjach programu Calc po wywołaniu sekwencji poleceń „Dane – Tabela przestawna – ...”. Polecenia te będą niedostępne (nieaktywne), jeśli aktywna komórka znajduje się poza tabelą przestawną.

Modyfikowanie tabel przestawnych jest także możliwe bez wywoływania wspomnianego okna dialogowego, jednak zmiany ograniczone są tylko do wyglądu (struktury) tabeli.

Na ilustracjach tabel przestawnych widać, że nagłówkami boczku tabeli są przyciski zawierające nazwę pola, którego wartości stanowią treść boczku. Podobnie jest z nagłówkiem tabeli. Tam także znajduje się przycisk z nazwą pola, którego wartości są treścią nagłówka (w LibreOffice zamieszanie wprowadza jednak przycisk „Dane”). Otóż układ tabeli przestawnej można zmienić przeciągając w niej przyciski reprezentujące pole w nowe miejsce w boczku, nagłówku, albo obszarze strony.

Ten mechanizm jest dość trudny do opisania więc najlepszą metodą jego poznania będzie poeksperymentowanie na jakiejś przykładowej tabeli przestawnej.

Przyciski nazw pól zawierają na prawej krawędzi wydzielony obszar przedstawiający czarny trójkąt skierowany wierzchołkiem do dołu. Kliknięcie tego obszaru otworzy dodatkowe okno, pozwalające zmienić sposób wyświetlania pola lub ograniczyć analizowane wartości. Właściwość ta nie odnosi się do przycisku „Dane”, nawet jeśli obszar z czarnym trójkątem na nim występuje.

Na rys. 14 poniżej widoczne jest to okno otwarte po kliknięciu przycisku „Producent”.

Okno umożliwia zmianę sortowania pola „Producent”. Domyślnym sortowaniem jest sortowanie rosnące. Widoczna w oknie opcja „Sortowanie niestandardowe” pozwala wybrać jeden z niestandardowych sposobów sortowania zdefiniowany w opcjach programu Calc.

W dolnej części tego okna znajduje się lista wszystkich zinterpretowanych wartości pola. Odhaczając konkretne pozycje można wybrać jakie dane będą reprezentowane w tabeli przestawnej. Jeśli zaznaczono jedną pozycję na tej liście to dwa przyciski znajdujące się obok pola „Wszystkie” pozwalają: albo wybrać do zestawienia to jedno pole, albo wybrać do tego zestawienia wszystkie pola z wyłączeniem tego zaznaczonego.

Tylko w wersji LibreOffice dostępne jest widoczne na ilustracji pole z napisem „Szukaj pozycji...”. Wystarczy w tym polu zacząć wpisywać tekst, a na liście pozostaną tylko pola dopasowane do tego tekstu.

Tabelę przestawną można też zmodyfikować zmieniając ręcznie wpisy znajdujące się w boczku lub nagłówku tabeli. Na przykład można zamiast słowa „Czekolada” wpisać treść „Tabliczka”. Po takiej zmianie wszystkie rekordy bazy danych zawierające wpis „Czekolada” będą przypisane do nowej nazwy.

Można zmienić także nagłówek kolumny podsumowującej co spowoduje także automatyczną zmianę opisu w wierszu podsumowującym i vice-versa. W wersji LibreOffice po otwarciu okna dialogowego „Układ tabeli przestawnej” i jego zamknięciu nawet bez dokonania jakichkolwiek zmian, opisy kolumn (wierszy) podsumowujących wrócą do standardowej postaci.

Nie można natomiast zmienić wyników znajdujących się w tabeli. Każda próba wprowadzenia do tabeli „własnej wartości” kończy się komunikatem „Nie można zmieniać tej części tabeli przestawnej”.

Opcje i możliwości tabel przestawnych.

Podczas tworzenia tabel przestawnych można ustalić dodatkowe opcje wpływające na wizualizację i prezentację danych. Zazwyczaj są one ukryte a procedura tworzenia tabel wykorzystując ich domyślne wartości tworzy tabele spełniające oczekiwania. Warto jednak poznać możliwości i sposoby ich zmiany. W oknie dialogowym „Układ tabeli przestawnej” wydania LibreOffice widocznym na rys. 8. znajdują się one pod pozycjami „Opcje” i „Źródło i cel”. Kliknięcie symbolu plusa ujawni zawartość tych pozycji. Pokazuję je poniżej.

W wersji Apache OpenOffice opcje ukryte są pod przyciskiem „Więcej” widocznym na rys. 9. Naciśnięcie tego przycisku ukaże dostępne możliwości w sekcji „Wynik”.

Na początku opracowania napisałem, że procedura tworzenia tabeli przestawnej próbuje ustalić bazę danych. Jeśli się to nie powiedzie albo wykryta baza nie jest tą, o jaką chodziło można wskazać wybraną bazę. W LibreOffice wykonać to można w sekcji „Źródło” wybierając nazwę obszaru (jeśli bazie przypisano nazwę) albo wskazując zakres komórek zawierający bazę. W Apache OpenOffice w polu „Wybór od” można wpisać albo wskazać obszar komórek. Wybierając obszar komórek należy pamiętać o tym, że pierwszy wiersz zaznaczanego obszaru musi zawierać nazwy pól.

Domyślne lokowanie tabeli przestawnej zależy od użytego programu. W LibreOffice domyślnym celem, miejscem tabeli przestawnej, jest nowy arkusz. Arkusz ten otrzymuje nazwę „Tabela przestawna_nazwa arkusza_numer”. Nazwa arkusza to nazwa tego arkusza, w którym znajduje się baza danych a numer to kolejny numer tabeli przestawnej. Jako cel można jednak wskazać nazwę wybranego obszaru albo inne miejsce.

W Apache OpenOffice cel jest definiowany przez parametr „Wyniki do”. Standardowo pierwsze rozwijane pole zawiera pozycję „-niezdefiniowane-” a obszar tabeli przestawnej jest lokowany pod obszarem bazy danych o dwa wiersze niżej. Można to zmienić poprzez zmianę adresu w drugim polu albo poprzez wybór w pierwszym polu opcji „-nowy arkusz-”. Tabela przestawna powstanie wówczas w nowym arkuszu, którego nazwa zostanie utworzona w taki sam sposób jak w LibreOffice.

Opcje tabeli przestawnej są w obu wersjach programu takie same i decydują o wyglądzie i dodatkowych możliwościach oglądu tabel.

Ignoruj puste wiersze.

• Ta opcja oznacza, że aparat tworzenia tabeli przestawnej zignoruje wszystkie puste wiersze, jakie znajdą się w obszarze bazy danych. Jeśli opcja będzie wyłączona to puste wiersze zostaną przedstawione jako oddzielna pozycja nazwana „(pusty)”. W LibreOffice będzie to ostatnia pozycja listy w Apache OpenOffice – pierwsza. Opcja dotyczy pustych wierszy, a nie pól. Jeśli choćby jedno pole w wierszu będzie wypełnione, to tabela przestawna uwzględni ten wiersz, choćby nawet wypełnione pole nie brało udziału w tworzeniu tabeli.

Kolumny podsumowania albo Suma kolumn.

• Ta opcja spowoduje, że na prawej krawędzi tabeli przestawnej znajdą się kolumny sumujące wartości w wierszu. Należy zwrócić uwagę na to, że opis kolumny zwierać będzie napis „Suma”, mimo że wynikiem jest liczba zależna od zastosowanego sposobu obliczania wartości pola. Jeśli więc obliczaną wartością była np. średnia, to w kolumnie suma znajdować się będzie także średnia obliczona ze wszystkich rekordów, a nie z liczb widocznych w danym wierszu. Jeżeli w obszarze danych znajduje się kilka pól bazy danych, to kolumny „sum” powstają oddzielnie dla każdego pola.

Wiersze podsumowania albo Łącznie wierszy.

• Ta opcja jest analogiczna do tej opisanej powyżej, z tym że odpowiednie „sumy” stanowić będą ostatni wiersz tabeli przestawnej i zawierać będą wyniki dotyczące kolumn.

Obie powyższe opcje są domyślnie włączone dlatego tabele przestawne powstające bez zmian opcji takie sumy mają. Przypomnę w tym miejscu, że autor może zmienić napisy tytułowe kolumn lub wierszy podsumowujących.

Dodaj filtr.

• Opcja decyduje o tym, czy ponad tabelą przestawną a w przypadku występowania obszaru strony ponad tym obszarem pojawi się dodatkowy przycisk „Filtr”. Użycie przycisku otworzy okno dialogowe filtru, takie samo jak to, które zostałoby otwarte po kliknięciu opcji „Filtr...” w menu kontekstowym widocznym na rys. 13.

Identyfikuj kategorię.

• Ta opcja decyduje, do jakiej grupy danych zostaną przypisane rekordy niezawierające wpisów w polach grupujących. Gdy np. w bazie danych znajdą się rekordy, które nie mają wypełnionego pola „Towar”, to przy wyłączonej opcji zostaną potraktowane jako grupa towarów „(pusty)” i utworzą odpowiedni wiersz lub kolumnę. Jeżeli opcja będzie włączona, to rekordy te zostaną przypisane do tych nazwanych towarów, pod którymi bezpośrednio wystąpiły w bazie danych.

Włącz schodzenie do szczegółów albo Wyświetlanie detali.

• Włączenie tej opcji wpływa na możliwości przeglądania tabeli. Jest to szczególne działanie, pokażę je zatem na przykładzie górnej tabeli widocznej na rys. 4. Jeśli dwukrotnie klikniemy dowolną pozycję boczku tabeli, np. w napis „Czekolada” pojawi się dodatkowe pytanie o szczegóły, jakie mają zostać pokazane dla tej wybranej pozycji. Ilustracja poniżej obrazuje tę sytuację i wygenerowane okno dialogowe „Pokaż szczegóły”.

  

  W oknie należy podać jakie pole bazy danych ma „uszczegółowić” tę pozycję. Tutaj wskazałem pole „Producent” i po jego wybraniu tabela przestawna przyjmie taki oto wygląd.

  

  Teraz po ponownym kliknięciu nazwy „Czekolada” pozycje (wiersze) szczegółowe zostaną zwinięte i tabela powróci do poprzedniej postaci. Od tej pory każde dwukrotne kliknięcie nazwy jakiegoś towaru w tej tabeli pokaże wszystkie pozycje szczegółowe dla tego towaru.

  To działanie można powtórzyć dla pozycji szczegółowych i np. w tabeli na rys. 18 kliknąć dwukrotnie nazwę wybranego producenta. Pokaże się ponownie okno dialogowe „Pokaż szczegóły”, w którym można wskazać pole uszczegóławiające ten wiersz.

  Wybrane pola stają się na stałe polami szczegółowymi, czyli po ponownym dwukrotnym kliknięciu nie pojawia się już okienko „Pokaż szczegóły” tylko od razu te szczegóły są rozwijane. Aby to zmienić należy zmodyfikować parametry tabeli przestawnej ustalane w oknie dialogowym „Układ tabeli przestawnej” albo „Tabela przestawna” zależnie od używanej wersji programu. O tym, jak wywołać te okna napisałem wyżej.

  W przypadku aktywowania szczegółów, w odpowiadającym obszarze okien dialogowych definiujących tabelę przestawną znajdują się nazwy pól ułożone we właściwej kolejności uszczegóławiania pozycji. Pola te można usunąć i wstawić w ich miejsce inne.

  Inaczej zareaguje system, gdy dwukrotnie zostanie kliknięta jakaś wartość występująca w tabeli przestawnej. W takim przypadku utworzony zostanie nowy arkusz, otrzyma on nazwę systemową, a więc najczęściej „Arkusz n”, w której n jest kolejnym numerem arkusza, a w arkuszu umieszczany jest ekstrakt rekordów bazy danych, na podstawie jakich kliknięta wartość została wyliczona.

  Poniższa ilustracja ukazuje utworzony nowy arkusz po dwukrotnym kliknięciu wartości 25,20 w komórce odpowiadającej producentowi „Goplana” i towarowi „Czekolada”.

  

Dane przedstawione w tym wygenerowanym arkuszu są z jego punktu widzenia całkowicie autonomiczne, traktowane tak jakby wprowadził je sam użytkownik. Można je więc dowolnie modyfikować. Jeżeli źródło danych zawierało pola obliczane, tak jak np. w powyższym przykładzie pole „Wartość”, które zawiera formułę obliczeniową „Ilość × Cena”, to w ekstrakcie danych pole to zawiera tylko liczbę.

Prezentacja danych.

Liczby prezentowane w tabeli przestawnej w obszarze danych to wartości wyznaczone przez odpowiednie funkcje bazy danych. Nie jest to jednak jedyna możliwa do wyświetlenia wartość. Tabela przestawna umożliwia bowiem wyświetlenie kilku różnych interpretacji tych obliczeń. Odpowiada za to druga, domyślnie ukryta, część okna dialogowego „Pole danych”.

Przypomnę, że okno to zostanie otwarte po dwukrotnym kliknięciu nazwy pola umieszczonego w obszarze danych. Okno to widać na ilustracji rys. 10. Poniżej przedstawię je ponownie, tym razem z widocznymi wszystkimi ustawieniami parametrów. Dodatkowe parametry zostaną pokazane po kliknięciu symbolu „plus” przy opisie „Wartość wyświetlana”. W wersji Apache OpenOffice w dolnej części okna dialogowego znajduje się przycisk „Więcej” otwierający ten obszar parametrów

Ten ujawniony obszar parametrów zawiera trzy nowe możliwości: „Typ”, „Pole bazowe” i „Element bazowy”.

Domyślnie typem wyświetlanej wartości jest „Normalna”, oznacza to, że w tabeli przestawnej znajdują się po prostu wyniki liczbowe wykorzystanych funkcji bazodanowych. W tej sytuacji pozostałe opcje są niedostępne.

W oknie „Typ” można rozwinąć listę dopuszczalnych typów. Wybrany – decyduje o ewentualnym dostępie do dalszych opcji. Typ „Normalna” został już wyjaśniony. Inne typy to:

Różnica z.

• Ten typ spowoduje, że należy także ustalić „Pole bazowe” i Element bazowy”. Pole bazowe określa jakie pole jest podstawą obliczeń a dokładną wartość uwzględnianą w obliczeniach definiuje element bazowy. Od każdego wyniku odejmowana jest wartość wskazana jako element bazowy i w komórce tabeli przestawnej umieszczana jest obliczona różnica tych wartości. Jako przykład przedstawiam rysunek poniżej. Ukazuje on dwie wersje tej samej tabeli przestawnej. Po lewej stronie typem wyświetlanych wartości jest „Normalna”, czyli w tym przypadku suma pola „Wartość”, po prawej – „Różnica z”, przy czym polem bazowym jest pole „Towar” a elementem bazowym wartość obliczona dla pozycji „Baton”.



Elementem bazowym wybranego pola bazowego może być wartość przypisana do wybranego elementu pola bazowego. W przypadku tego prezentowanego przykładu mógłby nim być dowolny z towarów znajdujący się w bazie. Istnieją dwa dodatkowe elementy bazowe nazywające się odpowiednio „- poprzedni element -” oraz „- następny element -”. Oznaczają one, że obliczenie każdej wartości nastąpi względem poprzedniego albo następnego elementu. Pole z elementem bazowym pozostanie w tabeli przestawnej puste. Gdy jako element bazowy wskazano „- poprzedni element -” puste jest pierwsze pole zestawienia, gdy „- następny element -” puste jest ostatnie pole.

% przez.

• Ten typ także wymaga ustalenia pola bazowego i elementu bazowego. Oblicza jednak iloraz obliczonej wartości i wartości elementu bazowego wyświetlając go jako wartość procentową.

% róznica z.

• Ten typ wyświetlanej wartości to iloraz różnicy obliczonej tak jak w typie „Rożnica z” i wartości elementu bazowego, wyrażona w procentach. Z racji sposobu obliczeń tutaj także należy podać pole bazowe i element bazowy.

Suma narastająca.

• W tym typie wyświetlanych wartości podaje się tylko pole bazowe. Pierwszy element zawiera swą własną wartość, każdy następny sumę swojej wartości i elementu poprzedzającego.

% przez kolumnę.

• Wartość każdej pozycji w kolumnie jest podzielona przez sumę tej kolumny i wyrażona w procentach. Zatem suma kolumny przedstawiona jest jako 100%. W tym typie wyświetlanych wartości nie wskazuje się pola bazowego.

% przez wiersz.

• Wartość obliczana jest względem zsumowanych wartości w wierszu. W tym typie suma w wierszu stanowi 100%.

% przez całość.

• Jako 100% traktowana jest suma całkowita wierszy i kolumn. Poszczególne wartości stanowią odpowiednią część procentową.

Indeks.

• Ten typ wyświetlanych wartości jest na tyle złożony, że nie daje się intuicyjnie zinterpretować jakiego rodzaju informację niesie. Nie wyjaśnia tego także system pomocy, który informuje, że ten typ dokonuje następującego wyliczenia (wartość danej pozycji × suma całkowita) / (suma wiersza tej pozycji × suma kolumny tej pozycji). Taki sposób opisu tego typu występuje w LibreOffice, Apache OpenOffice, Microsoft Office niczego więcej nie wyjaśniając.

Na stronie https://skuteczneraporty.pl/blog/pokaz-wartosci-jako-alternatywny-sposob-prezentowania-danych-w-tabeli-i-na-wykresie-przestawnym/ znalazłem taką interpretację tego typu wyświetlanej wartości:

Jak interpretować indeks? Indeksy o wartości zbliżonej do 1 oznaczają niewyróżniający się wpływ na wiersze i kolumny. Indeksy poniżej 1 oznaczają bardzo mały wpływ na wiersze i/lub kolumny, powyżej 1 – bardzo duży. Mój komentarz jest taki, że opcja ta nie jest intuicyjna.

Jednak eksperymenty przeprowadzone przeze mnie na testowej tabeli danych nie dają podstaw do takiej interpretacji. Pozostawiam to zagadnienie specjalistom, którzy zapewne wiedzą o co chodzi.

Jeżeli podczas definiowania wyświetlanych wartości zostanie wskazane niewłaściwe pole bazowe, zamiast wyników pojawią się wpisy „#VALUE!”. Pole bazowe musi być jednym z pól wymienionych w obszarze wierszy lub kolumn.

Prezentacja tabeli.

Odrębne zasady kierują rozbudowaną prezentacją boczku i (lub) nagłówka tabeli. Rozbudowane możliwości wyglądu tych elementów można definiować klikając dwukrotnie w wybrane pole w obszarze wierszy lub kolumn okna dialogowego „Układ tabeli przestawnej” lub odpowiednio „Tabela przestawna” jednak dotyczą one sytuacji, gdy elementy te tworzą pewną hierarchiczną strukturę. Pokażę to na przykładzie następujących tabel przestawnych.

W pierwszej tabeli głównym elementem boczku jest nazwa producenta a podrzędnym nazwa towaru. Pozwala to przeanalizować sprzedaż towarów względem producentów. W drugiej tabeli głównym elementem jest nazwa towaru a podrzędnym nazwa producenta. To z kolei pozwala przeanalizować jacy producenci biorą udział w sprzedaży towarów. Z zasady, jeżeli następny element boczku jest taki sam jak poprzedzający, to element ten pozostaje pusty i widać to wyraźnie w obu przykładach.

Poniżej pokazuję okno dialogowe „Pole danych”, jakie zostanie otwarte po dwukrotnym kliknięciu nazwy pola w obszarze wierszy. Ilustracja przedstawia układ pól z prawej tabeli przestawnej. Nazwa wybranego pola widnieje w sekcji opisanej jako „Nazwa”.

Główna część tego okna definiuje sumy częściowe. Jeśli wybrana zostanie opcja „Brak” tabela przestawna nie będzie zawierała sum częściowych. Wybór opcji „Automatycznie” utworzy sumy częściowe dla nadrzędnych elementów grupujących, a więc dla producentów – w tabeli lewej – i towarów – w tabeli prawej – i będą to sumy wartości występujących w tabeli. Wybór opcji „Użytkownika” aktywuje możliwość wskazania innego wyliczenia wartości podawanej jako suma pośrednia. Ilustracja pokazuje wybór funkcji „Zlicz”.

Kolejna prezentacja dwóch przykładowych tabel przestawnych przedstawia je po włączeniu sum częściowych.

Tabela po lewej stronie zawiera sumy częściowe wynikające z zastosowania opcji „Automatycznie” są to więc zsumowane poszczególne wartości agregujące sprzedaż poszczególnych producentów. Tabela po prawej stronie zawiera sumy częściowe wynikające z parametrów widocznych na rys. 23. Pod każdą grupą towarową znajduje się suma podająca ilość wystąpień pola wartość dla każdego towaru, czyli de facto podające ile transakcji nastąpiło z danym towarem.

Okno dialogowe „Pole danych” zawiera dodatkowy przycisk „Opcje...”, który otwiera kolejne okno dialogowe zatytułowane „Opcje pola danych” z dalszymi możliwościami. Okno to wygląda tak:

Okno zawiera cztery sekcje ustawień. Sekcja „Sortuj według” pozwala wskazać porządek sortowania oraz wybrać klucz sortowania. Po rozwinięciu pola z widocznym wpisem „Towar”, co oznacza sortowanie wg nazwy towaru, pojawi się inna możliwość, w tym konkretnym przypadku będzie to pozycja „Sum – Wartość” oznaczająca możliwość posortowania według sum wynikających z pola „Wartość”.

Sekcja „Opcje wyświetlania” pozwala zdefiniować układ wyświetlania oraz określenie dodatkowych ustawień. „Pusta linia po każdym elemencie” doda puste wiersze rozdzielające układ wierszy w tabeli przestawnej. „Powtórz etykiety elementu” spowoduje, że w tabeli zawsze pojawi się nazwa elementu. Rozwijana lista pozycji „Układ” obejmuje trzy opcje: „Układ tabelaryczny”, „Układ konspektu z sumami częściowymi na górze” oraz „Układ konspektu z sumami częściowymi na dole”.

Na kolejnej ilustracji pokazuję tabelę przestawną z ustawionymi pierwszymi opisanymi opcjami oraz wyborem opcji „Układ konspektu z sumami częściowymi na górze”.

Po każdej grupie towarów występuje pusty wiersz a nazwa towaru jest powtórzona w każdym wierszu. Pierwszą pozycją grupy jest suma częściowa, którą w tym przykładzie jest zliczenie wystąpień pola wartość.

Sekcja „Pokaż automatycznie” przydatna jest dla bardzo dużych grup towarów. Jeśli zostanie aktywizowana opcją „Pokaż” pozwala wskazać ile elementów wybranego pola ma zostać pokazanych i czy mają to być elementy o największych wartościach pola wymienionego w pozycji „Używane pole” – to wybór „Góra” w pozycji „Od” czy o najmniejszych wartościach – to wybór „Dół” w pozycji „Od”.

Poniżej przykład zastosowania parametrów „Dół” oraz wprowadzenia liczby 2. Zachowano włączoną opcję „Pusta linia po każdym elemencie”.

Ostatnia sekcja „Ukryj elementy” pozwala wyeliminować z zestawienia wybrane rekordy zawierające konkretne wartości.

W tej sekcji znajduje się też pole „Hierarchia”, staje się ono aktywne, wtedy gdy tabela przestawna oparta jest o zewnętrzne źródło danych zawierającym taką hierarchię.

Selekcja rekordów.

Może się zdarzyć, że tworząc tabelę przestawną chcemy ograniczyć zestaw rekordów z bazy danych. Jedną z możliwości jest wybór wartości, jakie mają być wybrane albo pominięte. Ta możliwość jest dostępna bezpośrednio w tabeli przestawnej i widoczna np. na rysunku 14.

Można określić pola występujące w obszarze strony i za pomocą tych pól wpływać na wygląd tabeli przestawnej. Poniżej ponownie pokazuję tabelę przestawną, w której do obszaru strony wprowadziłem pole „Producent”.

Napis „- wielokrotność -” sygnalizuje, że wybrano kilka pól. Jeśli oznaczono tylko jedną wartość pola, to pojawi się ona w polu obok przycisku „Producent”. System sygnalizuje zmianą wyglądu przycisku rozwijania pola fakt, że tabela przestawna prezentuje wyselekcjonowane rekordy.

Można także wykluczyć pewne rekordy wykorzystując wspomnianą wcześniej sekcję „Ukryj elementy”.

Każda z tych możliwości pozwala wskazać tylko te pola, które występują w obszarach strony, wierszy albo kolumn i dotyczy rekordów o określonej wartości pola. Może pojawić się jednak potrzeba selekcji warunkowej i to niekoniecznie związanej z polami uwzględnianymi w tabeli przestawnej. Na przykład tabela przestawna miałaby dotyczyć tylko tych rekordów, w których wartość pola „Ilość” jest większa niż 2 i mniejsza niż 7. Pole to nie bierze udziału w powstawaniu tabeli przestawnej.

Taką możliwość zapewni wykorzystanie filtru. Jeśli tabela przestawna powstała z ustawioną opcją „Dodaj filtr”, to ponad całą tabelą znajdzie się przycisk „Filtr”. Jego naciśnięcie wywoła okno dialogowe pozwalające ustalić warunki filtrowania rekordów. Okno to zostanie także otwarte, gdy wybrana zostanie opcja „Filtr...” w menu kontekstowym dostępnym po kliknięciu prawym przyciskiem myszki na tabeli przestawnej.

Powyższa ilustracja ukazuje okno dialogowe filtru z ustawionymi warunkami odpowiadającymi przytoczonemu przykładowi. Okno dialogowe ukazane jest z rozwiniętą sekcją „Opcje”.

Kryteria filtrowania pozwalają określić do trzech warunków, jakie musi spełnić rekord aby wziął udział w tworzeniu tabeli przestawnej. Warunki te mogą być połączone operatorem „I”, czyli iloczynem logicznym albo operatorem „LUB” szyli sumą logiczną. Jak widać filtr w odróżnieniu od wcześniej pokazanych sposobów pozwala wybrać rekordy według właściwie dowolnych kryteriów.

Na uwagę zasługuje pole „Wartość”, ma ono na prawej krawędzi przycisk pozwalający je rozwinąć. Na rozwiniętej liście wystąpią wszystkie różniące się wartości, jakie wystąpiły w bazie, w polu wskazanym w „Nazwa pola”. Jeśli do utworzenia relacji potrzebna jest któraś z tych wartości wystarczy ją wskazać.

W przytoczonym przykładzie kryteria dotyczyły wartości liczbowych i w zasadzie w takich sytuacjach nie określa się dodatkowych opcji. Jeśli jednak zastosowano by wyszukiwanie rekordów według pól zawierających teksty, przydatnymi mogą być opcje „Uwzględnij wielkość liter” i „Wyrażenia regularne”. Pierwsza z nich oznacza, że podczas porównywania musi nastąpić zgodność wielkości liter, tym sposobem budując kryterium „Towar = Czekolada” i zaznaczając opcję uwzględniania wielkości liter spowodujemy, że wyselekcjonowane zostaną tylko rekordy z nazwą towaru „Czekolada” zapisaną w ten właśnie sposób.

Druga opcja „Wyrażenia regularne” spowoduje, że w polu „Wartość” można wprowadzić wyrażenie regularne (o tych wyrażeniach więcej napisałem w opracowaniu pt. ”Poprawianie prac”). Wówczas zapis kryterium w postaci np. „Towar = .*o.*” wyselekcjonuje rekordy, które w nazwie wpisanej do pola „Towar” zawierają w dowolnym miejscu literę „o”.

Ostatnia opcja „Bez duplikatów” oznacza, że na liście filtrowanych danych duplikaty zostaną wyłączone. Tak to jest przynajmniej napisane w systemie pomocy. Podczas pracy nad tym artykułem stwierdziłem, że ta opcja nie wpływa na wyniki tabeli przestawnej ani w wersji LibreOffice, ani w Apache OpenOffice. Poprawnie natomiast funkcjonuje, gdy zastosuje się ją do filtrowania rekordów poleceniem filtrowania znajdującym się w grupie poleceń „Dane”.

Na zakończenie informacja, która może przydać się podczas pracy. Jeśli dla danej tabeli przestawnej wybierze się polecenie edycji jej układu i w oknie dialogowym wskaże się nowe położenie tabeli to spowodujemy jej skopiowanie w nowe miejsce, pozwoli to na łatwe przedstawienie wyników w różny sposób. Tabele przestawne, które powstały na podstawie tej samej bazy danych zostaną odświeżone, gdy polecenie odświeżenia zostanie zastosowane do dowolnej z nich.

Tabele przestawne mimo tego, że są efektem zadziałania procedury realizowanej przez program Calc są nadal zwyczajnymi tabelami. System nie pozwala co prawda zmienić wpisanych wartości i rozpoznaje sytuację kliknięcia wnętrza tabeli przestawnej, ale można wobec tabeli zastosować opcje wykorzystywane dla tradycyjnie przygotowanych zestawień, np. w przypadku dużych tabel przytwierdzić wiersze i kolumny (polecenie „Widok – Przytwierdź wiersze i kolumny” w LibreOffice lub „Okno – Przytwierdź” w Apache OpenOffice). Można także sformatować komórki tabeli, tutaj jednak większość zmian formatowania zostanie cofnięta po wykonaniu operacji „Odśwież”.

Można też „obudować” tabelę własnymi kolumnami wprowadzając niezbędne formuły wykorzystujące komórki takiej tabeli. Wszystko zależy od potrzeb.

Opracowanie to powstało z powodu fatalnego funkcjonowania usługi Neostrada polegającego na tym, że bardzo często następowało rozłączenie i ponowne połączenie z Internetem. Stało się to na tyle uciążliwe, że oprócz składania kolejnych reklamacji zacząłem archiwizować rejestrowane przez router neostrady komunikaty o błędach (tylko te świadczące o rozłączeniu i ponownym połączeniu). Zapisałem je w arkuszu kalkulacyjnym i na podstawie takiej bazy zbudowałem tabelę przestawną obrazującą ile razy w poszczególnych godzinach doby każdego dnia następowało ponowne nawiązywanie połączenia z Internetem. Ponieważ taką bazę mam to udostępnię ją tym czytelnikom, którzy chcieliby poćwiczyć tworzenie tabeli przestawnej.

Najpierw jednak objaśnienie rekordów tej bazy. Rekordy składają się z czterech pól o nazwach: „Data/Czas”, „Facility” (przyjmującym wartość „user” albo „daemon”), „Bezwzględność” (przyjmującym wartość „crit”) i „Komunikat” (zawierającym treść komunikatu). Nazwy tych pól pozostawiłem takie, jakie nadane zostały przez oprogramowanie routera. W bazie występują dwa rodzaje sekwencji rekordów. Pierwsza sekwencja wygląda tak:

Informuje ona o rozłączeniu Internetu, dokonanych analizach kanału, wynegocjowanej szybkości połączenia od serwera do komputera (ds) i od komputera do serwera (us), oraz o nawiązaniu połączenia po odebraniu poprawnego adresu IP (to ostatni komunikat w tej sekwencji).

Druga sekwencja składa się z czterech rekordów:

Ta sekwencja oznacza, że serwer rozłączył połączenie, a następnie po przydzieleniu nowego adresu IP router ponownie połączył się z Internetem

Jak by na to nie patrzeć to ostatni z komunikatów w każdym przypadku oznacza nawiązanie połączenia internetowego. Oznacza to, że wystarczy zliczyć ile razy wystąpił ten komunikat w każdej godzinie doby. Docelowa tabela przestawna powinna mieć mniej więcej taki wygląd:

Aby to zrealizować trzeba rozbudować bazę danych o dwie kolumny. W jednej powinna znaleźć się data utworzona na podstawie pierwszego pola rekordu. Zwróćmy uwagę na to, że pole to o postaci Aug 18 10:19:45 nie zawiera informacji o roku, miesiąc jest określony przez trzy pierwsze litery angielskiej nazwy miesiąca a treścią pola jest numer dnia i dokładny czas wystąpienia komunikatu. W drugiej musi znaleźć się godzina, o której komunikat wystąpił, także wyekstrahowana z pierwszego pola. Należy zatem utworzyć odpowiednie formuły, które te wartości wyznaczą. W tak uzupełnionej bazie do analizy wymagany jest rekord o konkretnej zawartości, trzeba zatem wykorzystać także filtr.

Baza danych zawiera ponad 10000 rekordów i można ją pobrać klikając na łączu obok tp.ods