Calc - tabele przestawne.

Uwaga: przeglądasz tę stronę na urządzeniu o niewielkim ekranie (szerokość < 640px). Niektóre zamieszczone w artykule ilustracje i animacje mogą stać się nieczytelne po dopasowaniu ich do rozdzielczości tego ekranu.

Yestok.pl

Jerzy Moruś

© Wszystkie prawa zastrzeżone. Wykorzystanie całości serwisu lub jego fragmentów bez pisemnej zgody autora zabronione.

Calc. Tabele przestawne



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.

Przykładowy rejestr sprzedaży.
Rys. 1: Przykładowy rejestr sprzedaży.

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.

Obszar wiersdzy tabeli przestawnej.
Rys. 2: Tabela przestawna utworzona na podstawie wskazania jedynie obszaru wierszy.

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:

Obszar wierszy i obszar kolumn tabeli przestawnej.
Rys. 3: Tabela przestawna z podanymi polami w obszarach wierszy i kolumn.

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.

Kompletne tabele przestawne.
Rys. 4: Kompletne tabele przestawne.

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).

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.

Tabela przestawna z polem w obszarze strony.
Rys. 5: Tabela przestawna zawierające pole w obszarze strony.

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.

Wybór źródła dla tabeli przestawnej (LibreOffice).
Rys. 6: Okno dialogowe określające źródło danych (LibreOffice).

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

Wybór źródła dla tabeli przestawnej (Apache OpenOffice).
Rys. 7: Okno dialogowe określające źródło danych (Apache OpenOffice).

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:

Zewnętrzne źródło lub zewnętrzny interfejs

Opens the External Source dialog where you can select the OLAP data source for the pivot table.

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.

Okno dialogowe ustawień tabeli przestawnej (LibreOffice).
Rys. 8: Okno dialogowe „Układ tabeli przestawnej” (LibreOffice).

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

Okno dialogowe ustawień tabeli przestawnej (Apache OpenOffice).
Rys. 9: Okno dialogowe „Tabela przestawna” (Apache OpenOffice)
.

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.

Wypełnione okno dialogowe "Układ tabeli przestawnej (LibreOffice).
Rys. 10: Wypełnione okno dialogowe układu tabeli przestawnej (LibreOffice).

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.

Tabela z dwupoziomową hierarchią wierszy.
Rys. 11: Tabela przestawna z wielopoziomowym obszarem wierszy.

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.

Wielopolowy obszar danych.
Rys. 12: Tabela przestawna z kilkoma polami w obszarze „Pola danych”.

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.

Wykorzystanie menu kontekstowego.
Rys. 13: Menu kontekstowe tabeli przestawnej, LibreOffice po lewej i Apache OpenOffice po prawej.

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”.

Rozwinięte okno wyboru pola "Producent".
Rys. 14: Okno przycisku pola.

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.

Opcje tabeli przestawnej (LibreOffice).
Rys. 15: Opcje tabeli przestawnej LibreOffice.

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”.

Opcje tabeli przestawnej (Apache OpenOffice).
Rys. 16: Opcje tabeli przestawnej Apache OpenOffice.

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.

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.

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

Opcje wyświetlania.
Rys. 20: W pełni rozwinięto okno dialogowe „Pole danych”.

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:

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.

Hierarchiczna struktura boczku tabeli przestawnej.
Rys. 22: Tabele przestawne o hierarchicznej strukturze boczku.

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”.

Okno dialogowe "Pole danych" dla obszaru wierszy i kolumn.
Rys. 23: Okno dialogowe „Pole danych” dla obszaru wierszy.

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 przestawna z sumami częściowymi.
Rys. 24: Tabele przestawne z sumami częściowymi.

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:

Dodatkowe opcje pola danych.
Rys. 25: Okno dialogowe „Opcje pola danych”.

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”.

Tabela przestawna z dodatkowymi opcjami .
Rys. 26: Rozwinięte opcje boczku tabeli przestawnej.

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”.

Przykład zastoswania opcji krańcowych elementów.
#Rys. 27: Tabela przestawna dwóch najgorszych producentów.

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”.

Wybór zakresu danych prezentowanych w tabeli przestawnej poprzez selekcję w pola strony.
Rys. 28: Rozwinięta lista wyboru pola obszaru strony.

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.

Okno dialogowe definiowania filtru.
Rys. 29: Okno dialogowe definiowania filtru.

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:

Tabela przestawna rozłączeń  routera.
Rys. 30: Tabela przestawna rozłączeń routera .

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

Jeżeli chcecie Państwo pobrać plik, pamiętacie, że za taką możliwość odpowiada kliknięcie odnośnika (łącza) prawym przyciskiem myszki i wybranie z wyświetlonego menu pozycji w rodzaju „Zapisz element docelowy jako...” lub „Zapisz link jako...”. Kliknięcie lewym przyciskiem może spowodować inne działania niż pobieranie, gdyż przeglądarka może próbować bezpośredniego odczytania pliku. W szczególności, większość przeglądarek ma obecnie włączoną możliwość bezpośredniego odczytywania plików PDF.