Yestok.pl

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

Zestawienia tabelaryczne tworzone w arkuszu kalkulacyjnym bardzo często mają budowę charakterystyczną dla struktury bazy danych i równie często – choć użytkownik nawet o tym nie wie – są tak wykorzystywane.

Baza danych składa się z kolejno zapisanych informacji o jednostkowym obiekcie. Te informacje nazywane są rekordami bazy danych. Każdy rekord zawiera grupę powiązanych elementów danych jednostkowych tworzącą kompletny opis obiektu. Jeśli obiektem jest np. OSOBA, to grupę powiązanych elementów mogą tworzyć: nazwisko, imię, nr PESEL, nr telefonu oraz wiele innych związanych z osobą informacji. Wszystko zależy od zakresu gromadzonych informacji o obiekcie.

Każdy jednostkowy element danych w rekordzie nazywany jest polem, a każdy rekord zawiera takie same pola ułożone w takiej samej kolejności. W efekcie zapisane kolejno rekordy tworzą tabelę, w której rekord jest jej wierszem a pola – kolumnami tej tabeli. Do obsługi baz danych wykorzystywane są specjalizowane programy i języki programowania, gdyż złożone bazy danych obejmować mogą wiele powiązanych ze sobą plików.

W najprostszej bazie, będącej jednym plikiem danych, mają one układ tabeli arkusza kalkulacyjnego. Wiersze arkusza kalkulacyjnego odpowiadają rekordom bazy danych a poszczególne komórki wiersza polom rekordu. Dla wielu użytkowników arkusz kalkulacyjny zawiera wystarczającą funkcjonalność obsługi bazy danych i – aby otrzymać pożądane rezultaty – nie potrzebują oni żadnego innego programu ani funkcjonalności bazodanowych. Dla polepszenia czytelności takiej tabeli, nad pierwszym rekordem bazy danych znajdującej się w arkuszu Calc, umieszcza się zazwyczaj odpowiednie nagłówki określające jakie dane zawierają poszczególne kolumny. Te nagłówki to w istocie nazwy pól w rekordzie.

Tutaj potrzebne jest dodatkowe wyjaśnienie. Autorzy tworzą bardzo często wielopoziomowy układ nagłówka, np. taki jak na poniższej ilustracji, obrazującej hipotetyczny fragment zestawienia o osobach.

Ponieważ nazwy pól muszą znajdować się w wierszu bezpośrednio nad pierwszym rekordem (wierszem) danych, należy zapewnić to, aby te nazwy znajdowały się w pojedynczych (niescalonych) komórkach . W przypadku tego przedstawionego przykładu należałoby zorganizować nagłówek tabeli następująco:

W powyższym przykładzie nazwami pól bazy są: Imię, Nazwisko, Komórka, Praca, Dom, Mail, Facebook, Instagram. Bazy danych na poziomie rekordu odwołują się do danych jednostkowych właśnie poprzez nazwę pola. By uniknąć nieporozumień, nazwy te muszą być unikatowe w obrębie rekordu.

Przedstawione w tym opracowaniu zagadnienia są związane z poleceniem „Dane”. Ilustracja poniżej prezentuje rozwiniętą postać tego polecenia w programie Apache OpenOffice (po lewej) i LibreOffice (po prawej).

Opracowanie wyjaśni jak wykorzystywać możliwości arkusza kalkulacyjnego Calc przy analizowaniu dużej ilości zgromadzonych rekordów. Z tego miejsca opracowania można od razu wybrać temat interesujący czytelnika.

• Nazwa zakresu bazy danych.
• Sortowanie.
• Tabela przestawna.
• Filtrowanie.
• Autofiltr.
• Filtr standardowy.
• Filtr zaawansowany.
• Obliczenia w odfiltrowanych rekordach.
• Grupowanie.
• Sumy częściowe.
• Autokonspekt.
• Konsolidowanie.
• Funkcje baz danych.

Nazwa zakresu bazy danych.

Obszarowi bazy danych można nadać specyficzną nazwę. Nie we wszystkich poleceniach jest ona niezbędna, lecz ułatwia pracę przy odwoływaniu się do bazy. Ta nazwa ma inny charakter niż ta przypisana do zakresu arkusza, omawiana już wcześniej w opracowaniu „Nazwy w programie Calc” (zob. nazwy w programie Calc.). Nie pojawia się np. w wykazie nazw w rozwiniętym polu „Obszar arkusza” znajdującym się na pasku formuł ani w oknie dialogowym polecenia „Arkusz → Nazwane zakresy lub wyrażenia → Zarządzaj...” a w wersji Apache OpenOffice w oknie dialogowym polecenia „Wstaw → Nazwy → Definiuj...” (w obu wersjach Calca skrótem klawiaturowym tego polecenia jest „CTRL+F3”).

Oba rodzaje nazw można natomiast zobaczyć po wywołaniu Nawigatora (F5), który ukaże oddzielną pozycję dla „Nazwanych zakresów” i oddzielną dla „Zakresów baz danych”. Przykład takiego okna Nawigatora przedstawia rys. 4. Arkusz zawierał jedną zdefiniowaną nazwę bazy danych („Nowy”) i dwie nazwy zakresów („danetestowe” i „kryteria”).

Nazwę obszarowi bazy danych w arkuszach Calc obu pakietów nadaje się poleceniem „Dane → Określ zakres”. Zostanie otwarte okno dialogowe „Definiuj zakres bazy danych”. Przykład takiego okna (z programu Calc LibreOffice) pokazuje rys. 5.

Ilustracja prezentuje to okno z już rozwiniętymi ustawieniami opcji. Zaznaczanie opcji jest możliwe dopiero po podaniu nazwy zakresu. Analogicznie dotyczy to dwóch przycisków: „Dodaj/Modyfikuj” oraz „Usuń”. Jeżeli nazwa odpowiada jakiemuś zdefiniowanemu już obszarowi, oba przyciski są aktywne i mają nazwy „Modyfikuj” oraz „Usuń”. Gdy wpisana nazwa nie jest jeszcze zdefiniowana, aktywny jest jedynie przycisk „Dodaj”. Można zaznaczyć dowolne opcje, ale o tym, które z nich mają rzeczywisty wpływ na dane, decyduje to, skąd pochodzi obszar bazy danych. Okienko to pozwala też na modyfikowanie parametrów już istniejącego zakresu – stąd przycisk „Modyfikuj”.

Najprostszym zakresem jest tabela znajdująca się bezpośrednio w arkuszu. W tym przypadku jedyną opcją wpływającą na dane jest informacja czy pierwszy wiersz tabeli zawiera nagłówki (nazwy) pól. W takiej sytuacji należy także samodzielnie nadać nazwę zakresowi bazy. Jeżeli polecenie zostanie wywołane bez uprzedniego zaznaczenia zakresu bazy danych, Calc spróbuje rozpoznać w sposób automatyczny ten zakres, analizując zawartość komórek znajdujących się wokół komórki aktywnej. Po nadaniu nazwy i ustaleniu opcji nazwa zakresu jest dostępna w arkuszu.

Innym pochodzeniem danych mogą być zarejestrowane w pakiecie źródła danych. Tego rodzaju dane wstawia się do arkusza poprzez przeciągnięcie do niego nazw odpowiednich tabel lub kwerend. Takie działanie, tak utworzonym obszarom, nadaje nazwy automatycznie. Nadawana nazwa to „Importujn”, w której „n” jest liczbą określającą kolejny obszar pobrany ze źródła. Nazwę tę użytkownik może zmienić na inną, wykorzystując wspomniane powyżej polecenie „Dane → Określ zakres...”. Sama zmiana nazwy polega w rzeczywistości na nadaniu obszarowi „Importujn” nowej nazwy, a następnie usunięciu tej niepotrzebnej. Problem źródeł danych poruszyłem i omówiłem w opracowaniu poświęconym korespondencji seryjnej w programie Writer.

Zarejestrowanym źródłem może być arkusz zapisany w innym pliku, także pochodzący z innego systemu, np. z Excela albo dane umieszczone w pliku tekstowym. W takim przypadku znaczenie mają także opcje „Zachowaj formatowanie” oraz „Nie zapisuj importowanych danych”. Pierwsza z nich decyduje o formatowaniu wszystkich komórek w kolumnach arkusza na podstawie pierwszego wiersza danych. Druga powoduje, że plik arkusza po zapisaniu na dysku nie będzie zawierał danych pochodzących z zewnętrznego źródła. Dane te znajdują się bowiem we wskazanym źródle danych, a użytkownik przy ponownym otwieraniu dokumentu zostanie zapytany o to, czy odtworzyć kwerendę danych. Dzięki temu przy otwieraniu pliku zostanie pobrana zawsze aktualna zawartość bazy.

Jeśli źródłem danych jest „prawdziwa” baza danych, opcja „Zachowaj formatowanie” nie ma znaczenia, gdyż w systemie baz danych każde pole jest zdefiniowane już podczas tworzenia bazy. Znaczenie ma natomiast opcja „Wstaw lub usuń komórki”. Powoduje ona, że obszar bazy danych zostanie automatycznie zaktualizowany, gdy w źródle danych zostaną dodane albo usunięte rekordy.

Niezależnie od metody pozyskania danych mogą one być przetwarzane w taki sam sposób.

Dostępne są jeszcze dwa polecenia. „Dane → Wybierz zakres” pozwalające wskazać i wybrać jeden ze zdefiniowanych zakresów oraz „Dane → Odśwież zakres”. To drugie jest nieaktywne, wtedy gdy jeszcze nie użyto polecenia, które potrafi korzystać z odświeżania, a jeśli takie polecenie zostało już użyte to wtedy, gdy aktywna komórka znajduje się poza nazwanym zakresem bazy danych. Poleceniami, które mogą korzystać z odświeżania, są filtry: standardowe i zaawansowane. Przy wywołaniu tego polecenia Calc „wie”, w jakiej bazie danych znajduje się aktywna komórka i dla tej bazy wykona odpowiednią akcję odświeżania.

Sortowanie.

Sortowanie jest jednym z najczęściej wykonywanych działań na danych. I chociaż dotyczy nie tylko zestawień tabelarycznych, to na nich w większości przypadków jest wykonywane.

Calc w poleceniu „Dane” ma trzy podporządkowane polecenia dotyczące sortowania. Polecenie „Sortuj...”, które otwiera dodatkowe okno dialogowe pozwalające określić zasady i opcje sortowania, oraz polecenia „Sortuj rosnąco” i „Sortuj malejąco”, które to polecenia służą do wykonania prostego sortowania danych w porządku rosnącym albo malejącym.

Każde wywołanie jednego z tych poleceń sprawdza, czy w momencie ich wywołania komórka aktywna arkusza znajduje się wewnątrz jakiegoś zdefiniowanego zakresu bazy danych, czy nie. Jeśli się znajduje, to jest znany rozmiar zakresu oraz to, czy pierwszy wiersz zawiera etykiety kolumn (nazwy pól). Gdy komórka aktywna znajduje się wewnątrz nienazwanego zakresu, program sam stara się rozpoznać jakie dane mają zostać posortowane. Ta analiza jest dość złożona, ale najczęściej zakres danych zostaje prawidłowo określony, chyba że aktywna komórka nie styka się żadną krawędzią ani narożem z inną wypełniona komórką. Na pewno zakres rozpoznawanego obszaru jest ograniczany przez cały pusty wiersz albo kolumnę. Ponadto program usiłuje zinterpretować czy pierwszy wiersz wyznaczonego obszaru może być traktowany jako wiersz etykiet kolumn. Jeżeli komórki w pierwszym wierszu zawierają wyłącznie dane tekstowe, zostanie on potraktowany jak wiersz z etykietami (nazwami pól).

W przypadku wywołania jednego z poleceń „Sortuj rosnąco” albo „Sortuj malejąco” rozpoznany zakres zostanie od razu posortowany według wartości danych, znajdujących się w kolumnie komórki aktywnej. Jeśli procedura rozpozna wiersz etykiet, nie weźmie on udziału w procesie sortowania.

Wydaje się, że takie sortowanie to sposób prymitywny, gdyż sortować można tylko według jednego kryterium (jednej kolumny). Tak jednak nie jest i prawdopodobnie niewielu użytkowników arkuszy o tym wie, że tym poleceniem można posortować dane według wielu kluczy (pól). Zakładając, że pewien zakres danych ma zostać posortowany według trzech kluczy (pól), pierwszym kluczem ma być kategoria towaru, drugim jego nazwa a trzecim – gatunek należy te trzy proste sortowania wykonać w kolejności odwrotnej do wagi klucza. A więc najpierw wykonać sortowanie wg gatunku, następnie ten sam zakres wg nazwy i na końcu wg kategorii.

Inaczej działa polecenie „Dane → Sortuj...”. Nie wykonuje od razu sortowania, lecz otwiera nowe, zawierające dwie zakładki, okno dialogowe o nazwie „Sortowanie”, pozwalające ustalić zasady sortowania i dodatkowe opcje.

Pierwsza zakładka „Kryteria sortowania” zawiera trzy rozwijalne pola nazwane kluczami sortowania. Zawartością pola może być etykieta kolumny – jeśli Calc rozpoznał zakres danych zawierający etykiety kolumn – albo nazwa kolumny w postaci „Kolumna x”, w której „x” jest literową nazwą kolumny w arkuszu. Po wywołaniu polecenia automatycznie rozpoznawany jest zakres danych i o pierwszym kluczu sortowania decyduje komórka aktywna. Dopóki nie ustawi się klucza sortowania, dopóty nie można ustawić właściwości następnego klucza. Obok każdego klucza sortowania znajdują się dwa pola wyboru do ustalenia porządku sortowania.

Jeżeli obszar danych do sortowania nie może zostać określony, należy go zaznaczyć jeszcze przed wywołaniem polecenia „Dane → Sortuj...”, gdyż okno dialogowe „Sortowanie” nie ma możliwości zaznaczenia właściwego zakresu danych.

W programie Calc Apache OpenOffice dostępne są tylko trzy klucze sortowania, w LibreOffice po określeniu trzeciego klucza pojawi się kolejne pole, do ustalenia klucza czwartego i ewentualnie następnych.

Druga zakładka tego okna dialogowego to „Opcje”. Poniżej na ilustracji wygląd tej zakładki w programie Calc LibreOffice

Czerwony prostokąt otacza te opcje, których nie ma w analogicznej zakładce w wersji Apache OpenOffice.

Opcje oznaczają odpowiednio:

Uwzględnij wielkość liter. Podczas sortowania, z dwóch takich samych liter, mniejszą wartość ma mała litera a większą wielka. Przy sortowaniu rosnącym prawidłową kolejnością będzie zatem: „abc, abC, aBc, aBC”.

Zakres zawiera etykiety kolumn. Jeżeli jest zaznaczona, pierwszy wiersz zakresu nie weźmie udziału w sortowaniu.

Dołącz formaty. Jeżeli tę opcję zaznaczono, komórki będą przenoszone łącznie z własnym formatowaniem. Gdy opcja jest niezaznaczona, formatowania pozostaną na swoich pierwotnych miejscach.

Włącz sortowanie naturalne. Ten sposób sortowania traktuje cyfry wchodzące w skład sortowanego elementu jak liczby, a nie ciąg znaków. Dzięki temu symbole np. „ab1, ab10, ab2, ab3, ab21” zostaną ułożone w kolejności: „ab1, ab2, ab3, ab10, ab21”, a nie: „ab1, ab10, ab2, ab21, ab3”.

Widoczne na ilustracji dwie następne opcje nie są nigdzie opisane. System pomocy opisujący zakładkę „Opcje” nawet ich nie wymienia. Eksperymentując z różnymi danymi, nie udało mi się znaleźć wyjaśnienia, czego tak naprawdę dotyczą.

Kopiuj wyniki sortowania do: Opcja pozwala albo wskazać nazwę obszaru (rozwinięcie pola z widocznym napisem „- niezdefiniowane -” wyświetli wspólną listę nazw zakresów i zakresów baz danych) albo wpisać do pola obok adresu jednej komórki. Do podanego miejsca zostaną przeniesione wyniki sortowania. Przenoszony jest cały zakres. Ta opcja pozwala uzyskać posortowane dane i jednocześnie zachować oryginalny porządek danych w zakresie.

Niestandardowy kierunek sortowania. Można wybrać jeden z zadeklarowanych w opcjach programu Calc porządek sortowania. Po instalacji pakietu znajdują się tam listy sortowania nazw dni tygodnia i nazw miesięcy. Użytkownik może zdefiniować własny porządek sortowania.

Język. Opcja ma umożliwić wybór opcji charakterystycznych dla języka sortowanych danych. W rzeczywistości udostępnia tę możliwość jedynie dla języka niemieckiego, tak jak to opisano w systemie pomocy.

Kierunek. To ważna opcja, o której nie wie wielu użytkowników ale też nie często stosowana. Umożliwia ona zmianę sposobu sortowania danych z wierszowego na kolumnowy. Przy takim sortowaniu, od lewej do prawej, przestawiane są całe kolumny. Pierwsza kolumna może zostać potraktowana jako kolumna z etykietami wierszy i wówczas nie weźmie ona udziału w sortowaniu. Jeśli etykiety nie zostaną zinterpretowane, to w polu klucza sortowania pojawi się wpis „Wiersz n”, w którym „n” jest numerem wiersza zakresu sortowania.

System pomocy LibreOffice zawiera informację o występowaniu jeszcze jednej informacji w tej zakładce. Ma to być pozycja opisana jako "Obszar danych" zawierająca zakres komórek, który ma zostać posortowany. W rzeczywistości ta pozycja występuje tylko w wersji Apache OpenOffice.

Tabela przestawna.

Jest to bardziej zaawansowana metoda analizowania danych. Temat poświęcony temu zagadnieniu zamieściłem w serwisie już wcześniej, w opracowaniu znajdującym się pod tym adresem: . https://yestok.pl/ooo/y52.php.

Filtrowanie.

Filtrowanie to wyodrębnienie spośród wielu rekordów tych, które spełniają wymagane kryteria wyboru. Takie dane są najczęściej niezbędne w sytuacjach, kiedy analizie podlegać ma pewna wybrana grupa rekordów, np. sprzedaż warzyw albo produkty pochodzące od tego samego dostawcy.

Kluczowym jest w tym zagadnieniu pojęcie kryterium. Kryterium jest utworzoną w stosunku do pola lub pól w rekordzie relacją, której wynik PRAWDA (TRUE) oznacza, że rekord z taką wartością relacji ma zostać zaakceptowany i odfiltrowany.

Calc oferuje dwa sposoby prezentacji takich danych. Pierwszy z nich polega na wyświetlaniu wierszy z zaakceptowanymi rekordami i ukryciu wierszy z rekordami, które nie spełniają kryteriów. Drugi, na skopiowaniu rekordów spełniających kryteria w inne miejsce skoroszytu.

Z filtrowaniem poprzez ukrywanie wierszy związana jest zasada, o której należy i warto wiedzieć. Dotyczy ona tych wierszy, które spełniły kryteria i pozostały widoczne w arkuszu. Skopiowanie ich, przeniesienie, usunięcie lub sformatowanie będzie dotyczyło tylko ich. Wiersze ukryte w wyniku filtrowania nie wezmą udziału w tych operacjach.

Filtrowanie dostępne jest w trzech wariantach. Są to: „Autofiltr”, „Filtr standardowy” i „Filtr zaawansowany”.

Autofiltr.

To najprostszy rodzaj filtrowania danych, co nie oznacza jednak, że nie może zostać rozbudowany. W Calc LibreOffice aktywuje go polecenie „Dane → Autofiltr” w Apache OpenOffice „Dane → Filtr → Autofiltr”. Użycie tego typu filtra wymaga najpierw umieszczenia komórki aktywnej wewnątrz zakresu danych. Wywołanie tego polecenia natychmiast zmienia wygląd pierwszego wiersza zakresu, przekształcając każdą komórkę w rozwijalne pole, pozwalające określić kryterium wyboru. Jeśli zakres danych nie ma wiersza etykiet, pojawi się najpierw pytanie o to, czy pierwszy wiersz ma być traktowany tak, jak wiersz opisu.

Jeżeli ten pierwszy wiersz nie będzie traktowany jako wiersz opisu, będzie stanowił rekord danych, taki sam jak każdy inny w tym zakresie i podlegał badaniu pod kątem kryterium wyboru.

Autofiltry w LibreOffice i Apache OpenOffice nieco się różnią, jednak ich działanie zawsze ukryje te wiersze zakresu danych, które nie spełniają wybranego kryterium. Użytkownik rozpozna ten efekt po tym, że numeracja kolejnych wierszy na lewej krawędzi arkusza nie będzie ciągła. Włączenie autofiltra w Calc Apache OpenOffice zmienia wygląd zakresu na taki jak poniżej.

Przy każdej etykiecie kolumny, w pierwszym wierszu, pojawił się przycisk rozwijający pole i pozwalający wskazać kryterium wyboru. Na rozwijanej liście znajdują się wszystkie jednostkowe wartości występujące w kolumnie. Powyżej, rozwinięte zostało pole „Dostawa” z możliwością wyboru jednej z występujących w tym polu wartości. Lista wartości jest automatycznie posortowana, umożliwiając wybór jednej z nich. Klikając wartość, spowodujemy wybór wszystkich tych rekordów, które w wybranym polu zawierają tę wartość.

Można też wybrać pozycję „Wszystkie”, która w rzeczywistości kasuje to kryterium wyboru.

Pozycja „- 10 pierwszych -” oznacza, że wyświetlone zostaną rekordy o pierwszych dziesięciu największych wartościach. Nie należy oczekiwać, że filtr wyodrębni tylko 10 rekordów. Wszystko zależy od układu wartości. Każda z wartości powtarzająca się jest traktowana jako kolejna w sekwencji dziesięciu największych. Jeśli wartość jedenasta i następne będą takie same jak wartość dziesiąta, zostaną zaliczone do zestawu dziesięciu największych wartości. Wówczas te dalsze rekordy także zostaną uznane za spełniające kryterium.

Ostatni wybór, „- Standardowe -” to w istocie wywołanie filtra należącego do wariantu „Filtr standardowy”, zostanie on omówiony w dalszej części przy opisie tego wariantu filtrowania.

W oprogramowaniu LibreOffice wygląd pierwszego wiersza zakresu danych jest analogiczny jak wersji Apache OpenOffice. Zmienił się natomiast wygląd rozwijalnego pola wyboru kryteriów. Przedstawia to poniższy rysunek.

Listę wartości występujących w polu zastąpiła lista z możliwością wyboru wielokrotnego. Można więc zaznaczyć kilka wartości, które zadziałają jak warunki połączone operatorem LUB.

Trzy opcje znajdujące się powyżej tej listy pozwalają odpowiednio na:

„Wszystko” – zaznaczenie wszystkich wartości. Ponowne kliknięcie kasuje wszystkie zaznaczenia i tak na przemian.

Okienko ze znakiem krokiewki – jego kliknięcie powoduje zaznaczenie tylko wskazanej pozycji. Inne pozycje zostaną zdezaktywowane.

Okienko ze znakiem X – jego kliknięcie powoduje zaznaczenie wszystkich wartości z wyjątkiem zaznaczonej pozycji.

Pole tekstowe zawierające wpis „Szukaj pozycji...” służy do wpisywania kolejnych znaków ciągu, jaki ma zostać znaleziony wewnątrz pola w rekordzie. Już wpisanie pierwszego znaku ograniczy liczbę widocznych pozycji wyboru w dolnej części rozwiniętego pola dialogu.

Pozycja „Filtr standardowy... ” ma takie samo znaczenie jak wspomniana przy omawianiu wersji Apache OpenOffice pozycja „- Standardowe -”. Zostanie omówiona w dalszej części tego opracowania.

Autorzy Calca LibreOffice dodali dwa nowe kryteria wyboru „Pusty” oraz „Niepusty”, pozwalające od razu wyodrębnić tylko te rekordy, w których analizowane pole jest puste albo niepuste.

„- 10 pierwszych -” ma takie samo znaczenie jak wspomniane wcześniej. Jednak w przypadku LibreOffice, przynajmniej wersji, jaką tu omawiam, powrót do wyświetlania wszystkich rekordów nie działa tak, jakbyśmy oczekiwali. Wybór znacznika „Wszystkie”, z jakichś powodów nie przywraca wyświetlania wszystkich rekordów. Żeby ponownie zobaczyć wszystkie rekordy, trzeba wykonać jakieś wyszukiwanie, choćby jednego rekordu a dopiero po nim ponownie wyszukać rekordy zaznaczając „Wszystkie”.

Ostatnią nowością w wersji LibreOffice jest dodanie możliwości posortowania rekordów rosnąco albo malejąco wg wybranego pola.

Autofiltr może zostać zastosowany do kilku pól w zakresie danych. Kolejne kryteria wyboru są łączone ze sobą działaniem logicznym I.

Pola, do których zastosowano kryterium wyboru, mają zmienioną postać przycisku w pierwszym wierszu zakresu. Przycisk zmienia kolor, a poniżej w prawym dolnym rogu pojawia się dodatkowy kwadratowy znacznik.

Kiedy autofiltr jest już niepotrzebny, można go ukryć albo usunąć. Usunięcie autofiltru polega na ustawieniu komórki aktywnej wewnątrz zakresu, który jest filtrowany i ponownym wywołaniu autofiltru.

Filtrowane obszary, które mają wiersz opisu, mogą zostać ukryte. Ukrycie spowoduje jedynie to, że w pierwszym wierszu znikną charakterystyczne przyciski, a w samym obszarze pozostaną takie rekordy, jakie były widoczne w momencie ukrywania. Do ukrycia autofiltru stosuje się polecenia „Dane → Więcej filtrów → Ukryj autofiltr” (LibreOffice) albo „Dane → Filtr → Ukryj autofiltr” (Apache OpenOffice).

Jeśli po ukryciu autofiltru chcemy widzieć wszystkie rekordy, należy dodatkowo wywołać polecenie „Dane → Więcej filtrów → Resetuj filtr” (w LibreOffice) albo „Dane → Filtr → Usuń filtr” (w Apache OpenOffice).

Filtr standardowy.

Filtr standardowy pozwala utworzyć złożone kryterium wyboru wiążące ze sobą kilka warunków wyboru. Po ustawieniu aktywnej komórki w zakresie danych lub po wskazaniu zakresu danych należy wywołać polecenie filtra standardowego. Otwarte Zostanie okno dialogowe takie jak na ilustracji.

Okno dialogowe w programie Apache OpenOffice ma analogiczna postać z takimi samymi parametrami.

Prezentowane okno dialogowe ma już rozwiniętą sekcję zatytułowaną „Opcje”. Główną część tego okna stanowi sekcja: „Kryteria filtrowania”.

Pole „Nazwa pola” pozwala wybrać to pole zakresu danych, które będzie podstawą filtrowania. Na rozwijalnej liście znajdą się wszystkie nazwy pól występujące w zakresie. Jeśli zakres danych nie zawiera nazw pól, to zastępczymi nazwami pól będą nazwy kolumn, stanowiących zakres danych (np. Kolumna B).

Pole „Warunek” służy do określenia relacji, jaka ma zajść między zawartością pola a wartością znajdującą się w polu „Wartość”. Prezentowany na ilustracji przykład żąda, aby wybrane rekordy zawierały w polu „Kategoria” wartość „warzywa”. Pole „Warunek” można rozwinąć i wybrać jeden z dostępnych warunków znajdujących się na liście.

Jak widać, lista zawiera możliwość badania relacji matematycznych, wielkości statystycznych lub zależności tekstowych. Relacje w rodzaju: „mniejszy niż”, „nierówny” czy „większy lub równy”, nie wymagają dodatkowych wyjaśnień.

Warunek „Największe” i „Najmniejsze” wymaga wpisania do pola „Wartość” liczby, ile rekordów z największymi lub najmniejszymi wartościami ma zostać odfiltrowanych. Jest to podobny wariant jak ten wspomniany przy autofiltrze z wyborem „10 pierwszych”, pozwalający jednak wybrać dowolną liczbę rekordów, w tym także ostatnich (najmniejsze). Podobnie jest z warunkami „Największe %” i „Najmniejsze %”. Należy podać, ile procent największych lub najmniejszych wartości ma decydować o odfiltrowaniu rekordów. W obu tych przypadkach w polu „Wartość” musi zostać wpisana wartość liczbowa.

Kolejne warunki, od „Zawiera” do „Nie kończy się na”, dotyczą pól tekstowych i pozwalają dokładniej określić warunki wyboru.

Ostatnie pole pojedynczego kryterium wyboru, „Wartość”, wymaga wprowadzenia wartości wymaganej w warunku. Pole to jest także rozwijalne. Rozwijana lista zawiera wszystkie jednostkowe wartości występujące w analizowanym polu oraz dwie wartości dodatkowe: „niepusty” i „pusty”. Przy pomocy tych ostatnich można selekcjonować rekordy, w których analizowane pole jest puste albo ma cokolwiek wpisane. Do tego pola można także wprowadzić dowolne wartości, nie zważając na dostępną listę.

Jeśli wywołanie filtru standardowego nastąpiło po wstępnym zastosowaniu autofiltru, pierwszy wiersz sekcji „Kryteria filtrowania” będzie już wypełniony tym zastosowanym kryterium.

Kolejne wiersze kryteriów pozwalają na wskazanie kolejnego pola i określenia następnego warunku wyboru, przy czym zawsze należy określić, jak ten kolejny warunek ma się do warunków poprzedzających. Konstruując całą regułę, należy pamiętać o tym, że działania logiczne mają – tak jak arytmetyczne – także ustaloną kolejność ich wykonywania. Mnożenie logiczne (czyli operator I) ma pierwszeństwo przed sumą logiczną (czyli operatorem LUB). Sekcja „Kryteria filtrowania” może zawierać wiele wierszy. Pamiętając o tym, że budowana w ten sposób reguła wyboru nie będzie zawierała nawiasów decydujących o pierwszeństwie wykonywanych działań, należy przemyśleć jej konstrukcję.

W dolnej części okna dialogowego znajduje się sekcja „Opcje”. Sekcja ta, w momencie wywołania okna ukryta, zawiera dodatkowe parametry określające sposób filtrowania. Jeśli rozpoznany zakres danych jest nazwany, opcja „Zakres zawiera etykiety kolumn” jest ustawiona zgodnie z parametrami podanymi przy nadawaniu nazwy zakresu i nieaktywna. Opcja „Zachowaj reguły filtru” jest zaznaczona i także nieaktywna. Obie te opcje są aktywne (czyli można je zmieniać) wówczas, gdy zakres danych nie ma przypisanej żadnej nazwy.

Opcje mają następujące znaczenia:

Uwzględnij wielkość liter. Decyduje o tym, czy podczas porównywania danych tekstowych ma być uwzględniana wielkość liter.

Zakres zawiera etykiety kolumn. Informuje, czy pierwszy wiersz zakresu zawiera nazwy pól.

Wyrażenia regularne. Informuje o tym, że pola wartość mogą zawierać wyrażenia regularne. Wyrażenia regularne pozwalają na dopasowanie do kryterium danych z częściowo zgodnym szablonem. Np. wyrażenie regularne „.*8” oznacza wyszukanie dowolnego ciągu znaków zakończonego cyfrą 8, czyli takich wartości jak 128, 18, abc8 czy tylko liczby 8.

Kopiuj wyniki do: Zaznaczenie tej opcji oznacza, że odfiltrowane rekordy zostaną przekopiowane do innego miejsca w skoroszycie. Miejsce to można wskazać, rozwijając pole z widocznym napisem „- niezdefiniowane -”. Lista będzie zawierała nazwy obszarów zadeklarowanych jako nazwy zakresów. Jeśli takich nazw nie ma, pola nie można rozwinąć. Obok znajduje się pole pozwalające podać albo wskazać konkretny adres, od którego rozpocznie się kopia odfiltrowanych rekordów. Wystarczy podać adres pojedynczej komórki. Przekopiowane zostaną wszystkie pola odfiltrowanych rekordów.

Zachowaj reguły filtru. Ta opcja oznacza, że reguła filtrowania zostanie stowarzyszona z zakresem bazy danych, a to z kolei spowoduje, że aby ponownie wykonać to samo filtrowanie (np. po zmianach zaszłych w zakresie bazy danych), wystarczy ustawić aktywną komórkę w dowolnym miejscu zakresu bazy i wywołać polecenie „Dane → Odśwież zakres”. To właśnie ta opcja decyduje o dostępności polecenia odświeżania. Jeśli reguła filtrowania kopiowała rekordy do nowego miejsca, to po odświeżeniu kopia będzie zawierała tylko prawidłowe nowe rekordy. Ten sposób odświeżanie z użyciem filtru standardowego najlepiej sprawdza się wtedy, gdy ustaloną regułę filtrowania będziemy stosować do zmieniającego się zakresu danych. Zmiany mogą polegać na usuwaniu, dodawaniu lub modyfikowaniu rekordów. Dopóki nie zmienimy reguły filtrowania, dopóty możemy korzystać z polecenia „Dane → Odśwież zakres”.

Z jednym zakresem bazy może być stowarzyszona tylko jedna reguła. Gdy sposób filtrowanie jest już zbędny, można go usunąć poleceniem „Dane → Więcej filtrów → Resetuj filtr” (LibreOffice) albo „Dane → Filtr → Usuń filtr” (Apache OpenOffice).

Filtr zaawansowany.

Trzeci sposób filtrowania jest przydatny, gdy do zakresu bazy danych chcemy stosować zmieniające się reguły filtrowania. Ten sposób wykorzystuje dodatkowy obszar, w którym należy utworzyć te reguły.

Dodatkowy obszar nazywany jest obszarem kryteriów. Pierwszy wiersz tego obszaru musi zawierać nazwy pól rekordów, które będą brały udział w ustalaniu reguł. Następne wiersze, a przynajmniej jeden, zawierają kryteria wyboru zapisane w kolumnach pod właściwą dla tego kryterium nazwą pola. Podsumowując, jeżeli rekordy bazy danych składają się z dziesięciu pól, możemy w obszarze kryteriów umieścić tylko cztery z nich a kryteria zapisać tylko pod dwoma wybranymi nazwami pól. Pola, dla których nie utworzono kryterium, mimo tego, że są umieszczone w obszarze kryteriów, nie będą brały udziału w filtrowaniu.

Istnieje zasadnicze ograniczenie dotyczące obszaru kryteriów w wersji programu pakietu Apache OpenOffice. Obszar ten może mieć co najwyżej 8 wierszy zawierających kryteria. Jeśli zaznaczy się ich więcej pojawi się komunikat: „Ten zakres nie zawiera prawidłowej kwerendy.”. Takiego ograniczenia nie ma w LibreOffice. Testowałem w nim użycie obszaru kryteriów zawierającego ponad 1000 wierszy.

Wszystkie kryteria wyboru wpisane w jednym wierszu łączone są operatorem I natomiast wiersze łączone są operatorem LUB. Taki sposób interpretacji kryteriów powoduje, że czasem dla uzyskania niezbędnego filtrowania konieczne będzie powtórzenie nazwy pola. Dotyczy to sytuacji, gdy odfiltrowane mają być rekordy z pewnego zakresu wartości, typu „od – do”. Tylko powtórzenie nazwy pola pozwoli połączyć te kryteria wyrażeniem logicznym I.

Poniżej przedstawiam prezentację pokazującą, w jaki sposób można wykorzystać filtr zaawansowany.

Prezentacja ukazała sposób konstruowania kryteriów. Jak widać, w przypadku odfiltrowywania należy wpisać znak relacji i porównywaną wartość jako zwykły tekst. Jeśli porównanie ma nastąpić ze znakiem relacji „=”, to ten znak można opuścić, wpisując tylko wartość. Nie oznacza to jednak tego, że nie można utworzyć kryterium zależnego od czynnika zmiennego. By to osiągnąć, należy jako kryterium wstawić formułę tekstową, zwracającą wynikową postać zgodną z zasadami tworzenia kryterium. Czyli np. gdy kryterium ma odfiltrować rekordy o wartości mniejszej niż podana w komórce F1 (niech to będzie np. 100) arkusza „Arkusz2”, należy wprowadzić formułę: „="<"&Arkusz2.F1”. Wynikowa postać formuły „<100” będzie już zgodna z budową kryterium.

Obliczenia w odfiltrowanych rekordach.

Po odfiltrowaniu interesujących nas rekordów zachodzi zazwyczaj potrzeba dokonania na nich stosownych obliczeń. Jednym z problemów obliczeniowych dotyczących odfiltrowanych rekordów jest niewiedza, w jaki sposób otrzymać wyniki dotyczące takich rekordów. Dotyczy to takich funkcji jak SUMA, ILE.LICZB czy np. MAKS. Zastosowanie tych funkcji ze wskazaniem zakresu komórek, zawsze uwzględnia wszystkie komórki w podanym zakresie, bez względu na to, czy są ukryte, czy nie. Funkcje te zatem nie nadają się do wykorzystania.

Zanim będę kontynuował ten temat, wspomnę o tym, że filtrowanie jest tylko jednym ze sposobów ukrycia komórek. Poza tym, komórki można ukryć, wykorzystując ich grupowanie albo konspekt (o tym będzie mowa w dalszej części) albo po prostu wykonując polecenie „Format → Wiersze → Ukryj”. Skrócona forma tego ostatniego sposobu to kliknięcie numeru komórki prawym przyciskiem myszki i wybór z wywołanego menu kontekstowego pozycji „Ukryj wiersze”. Ta wiedza jest potrzebna, aby odróżniać wiersze ukryte w wyniku filtrowania od wierszy ukrytych w inny sposób.

Arkusz kalkulacyjny Calc ma dedykowaną funkcję, która pozwala wykonać obliczenia tylko na rekordach odfiltrowanych, czyli na tych, jakie nie zostały ukryte w wyniku filtrowania. W programie wersji LibreOffice funkcja ma nazwę SUMY.CZĘŚCIOWE (SUBTOTAL) natomiast w wersji Apache OpenOffice: SUMY.POŚREDNIE. Funkcje te nadal jednak uwzględnią wiersze ukryte w inny sposób. Sama funkcja zawiera co prawda słowo SUMY, ale pozwala obliczyć różne wartości zależne od podanego parametru „rodzaj funkcji”.

Składnia tej funkcji jest następująca:

SUMY.CZĘŚCIOWE(rodzaj funkcji; zakres)

SUMY.POŚREDNIE(rodzaj funkcji; zakres)

Zakres jest standardowo rozumianym zakresem komórek i w tym przypadku zawiera zazwyczaj cały zakres analizowanego obszaru. Funkcja, wykonując obliczenia, nigdy nie uwzględnia wierszy ukrytych w wyniku filtrowania. Parametr „rodzaj funkcji” decyduje o tym, jakie obliczenia zostaną wykonane i jest liczbą od 1 do 11. W LibreOffice parametr ten może dodatkowo przyjąć wartość od 101 do 111 i tylko w tej wersji programu oznacza to, że wiersze ukryte w inny sposób niż filtrowanie także mają być ignorowane w obliczeniach.

Wartości liczbowe parametru „rodzaj funkcji” przypisane są do następujących funkcji:

• 1 lub 101 ŚREDNIA
• 2 lub 102 ILE.LICZB
• 3 lub 103 ILE.NIEPUSTYCH
• 4 lub 104 MAKS
• 5 lub 105 MIN
• 6 lub 106 ILOCZYN
• 7 lub 107 ODCH.STANDARDOWE
• 8 lub 108 ODCH.STANDARD.POPUL
• 9 lub 109 SUMA
• 10 lub 110 WARIANCJA
• 11 lub 111 WARIANCJA.POPUL

Jeżeli swoją bazę danych podsumujemy funkcją SUMY.CZĘŚCIOWE(109;B2:B2000) otrzymamy sumę liczb tylko z wierszy widocznych. Taki parametr (109) jest niedopuszczalny w funkcji SUMY.POŚREDNIE.

I jeszcze ważna informacja. Jeżeli w obszarze działania funkcji SUMY.CZĘŚCIOWE (SUMY.POŚREDNIE) znajdą się jakieś inne funkcje tych sum, to ich wartości nie będą uwzględniane w obliczeniach.

Grupowanie.

Grupowanie jest operacją pozwalającą na skomasowanie wybranych komórek w oddzielny zestaw danych. Takie skomasowanie można zdefiniować zarówno w zakresie wierszy, jak i kolumn. Grupować można tylko komórki przylegające do siebie. Wyłącznie od użytkownika zależy jakie komórki będą tworzyły oddzielne grupy. Zdefiniowanie grup pozwala w wygodny sposób manipulować wizualizacją zgromadzonych danych. Poszczególne grupy można włączać lub wyłączać pojedynczym kliknięciem myszki.

Zasady grupowania przedstawię na przykładzie poniższego zestawienia.

Chcę zdefiniować grupy miast należące do tego samego województwa, będą to grupy łączące wybrane wiersze oraz grupy zawierające kolumny z danymi jednego kwartału i te grupy łączą kolumny. Komórki już zgrupowane mogą zostać ponownie zgrupowane, tworząc grupę nadrzędną. W ten sposób może zostać utworzonych kilka poziomów grupowania. W tym aspekcie możemy mówić o tworzeniu konspektu analogicznego do pojęcia konspektu w dokumentach tekstowych.

Po zaznaczeniu komórek należy wywołać polecenie „Dane → Grupa i Konspekt → Grupuj...”. Skrótem klawiaturowym dla tej czynności jest klawisz F12. Pojawi się okno dialogowe z prośbą o wybór łączenia grupowanych komórek. Komórki wcześniej zgrupowane można rozgrupować. Należy umieścić komórkę aktywną w grupie, która ma zostać rozgrupowana i wywołać polecenie „Dane → Grupa i konspekt → Rozgrupuj...”. Skrótem klawiaturowym dla tej czynności jest CTRL+F12. Jeśli komórka aktywna należała do grupy komórek wierszowych i równocześnie kolumnowych, pojawi się dodatkowe okno dialogowe, w którym należy wybrać, jaka z tych grup ma zostać rozgrupowana.

Gdy arkusz zawiera zgrupowane komórki, na marginesie – na lewo od numerów wierszy (dla grupowania wierszy) oraz na marginesie ponad symbolami oznaczeń kolumn (dla grupowania kolumn) – pojawiają się dodatkowe oznaczenia obrazujące zakresy poszczególnych grup. Ponadto pojawiają się niewielkie ponumerowane przyciski. Jest ich o jeden więcej niż zdefiniowanych poziomów grupowania. Kliknięcie przycisku z numerem „1” ukryje wszystkie wiersze i/lub kolumny, które mają przypisany ten poziom grupowania. W arkuszu wyświetlone zostaną tylko dane nienależące do żadnej grupy. Kliknięcie przycisku o najwyższej liczbie spowoduje wyświetlenie wszystkich danych, bez ukrywania grup. Niezależnie od tego użytkownik może zwijać lub rozwijać pojedyncze grupy wykorzystując do tego, znajdujący się na granicy ogranicznika grupy, znak minus (zwijający grupę) lub plus (rozwijający grupę).

Poniżej na prezentacji można zobaczyć mechanizm tworzenia grup i zarządzania nimi.

Sumy częściowe.

Polecenie „Sumy częściowe” nie bez kozery nawiązuje do nazwy funkcji. Pozwala ono w łatwy i szybki sposób pokazać wyniki w różnych ujęciach, uzyskane z wielu zgromadzonych rekordów, wykorzystując do tego funkcję SUMY.CZĘŚCIOWE a w przypadku Apache OpenOffice SUMY.POŚREDNIE. Na potrzebę omówienia tego polecenia przygotowałem niewielką bazę danych. Jej strukturę pokazuje rys. 13.

Baza zawiera na bieżąco wprowadzane dane o zakupach realizowanych przez pracowników hipotetycznej firmy. Rejestrowane dane, to data zakupu, nazwisko kupującego, dział przedsiębiorstwa, w którym zatrudniona jest ta osoba, nazwa kupowanej rzeczy, jej cena ilość i wartość. Dane w takim rejestrze mogą być gromadzone przez długi czas, zawierając wieleset rekordów.

Dysponując taką bazą danych, można odpowiedzieć sobie na różne pytania, np.

1. Ile na zakupy wydały poszczególne działy przedsiębiorstwa?
2. Jakie przedmioty i w jakich ilościach zakupiły poszczególne działy?
3. Ile razy kupowano konkretne rzeczy?
4. Jakie kwoty wydali pracownicy firmy?

Takich zagadnień można sobie wyobrazić znacznie więcej a na wszystkie odpowiedzią może być polecenie „Sumy częściowe”.

Zrealizowanie polecenia wymaga określenia, na jakie grupy mają zostać podzielone rekordy i jakie wartości należy dla tych grup obliczyć. Użyte słowo grupy ma tutaj potoczne znaczenie i nie jest związane z omawianym wczesnej grupowaniem wierszy, czy kolumn. Nawiązując do zadanych powyżej czterech pytań: w przypadku odpowiedzi na pytanie pierwsze, rekordy należy pogrupować wg działów i obliczyć sumę kosztów w tych działach. W przypadku odpowiedzi na pytanie drugie, rekordy należy podzielić na grupy wg nazw zakupionych przedmiotów a wewnątrz tych grup dodatkowo na działy. Natomiast jako wyniki należy obliczyć ilość każdego zakupionego przedmiotu w każdym dziale. Odpowiedź na trzecie pytanie wymaga, aby pierwszym i jedynym podziałem na grupy były nazwy przedmiotów. Policzyć należy, ile razy każdy z przedmiotów był kupowany. Przy ostatnim zadanym pytaniu należy pogrupować pracowników wg nazwisk i obliczyć łączną wydaną przez nich kwotę.

Sumy częściowe pozwalają na określenie do trzech poziomów grupowania. Na każdym poziomie można wykonać obliczenia dla każdego pola rekordu, ale dla każdego pola na jednym poziomie można wykonać tylko jedno obliczenie. Polecenie nie wymaga tego, aby jego zakresem działania była nazwana baza danych, wystarczy zaznaczyć wymagany obszar rekordów albo tylko ustawić komórkę aktywną arkusza wewnątrz obszaru, który miałby być analizowany. Wywołanie polecenia otwiera okno dialogowe pokazane na rys. 14.

Okno przedstawia definicję pierwszej grupy. Rozwijana lista pola „Grupuj według” ukazuje nazwy wszystkich pól występujących w zaznaczonym obszarze bazy danych. Ilustracja przedstawia ustalenia grupowania zakupów wg pola „Rzecz”, czyli według zakupionych produktów.

Obszar oznaczony nazwą „Oblicz sumy pośrednie” pozwala zaznaczyć te pola, których wartości wezmą udział w obliczeniach.

Obszar oznaczony nazwą „Użyj funkcji”, pozwala przypisać do zaznaczonego pola zastosowaną funkcję obliczeniową. Funkcje obliczeniowe odpowiadają tym wymienionym w opisie funkcji SUMY.CZĘŚCIOWE. A mówiąc precyzyjnie do wybranych pól, w podsumowaniu grupy, zostanie wykorzystana funkcja SUMY.CZĘŚCIOWE, w której parametr „rodzaj funkcji” jest z zakresu od 1 do 11, odpowiedni do wybranej funkcji. Wstawione funkcje uwzględniają, jak widać, wiersze ukryte, z wyłączeniem tych, które zostały ukryte filtrowaniem.

Ostatnią zakładką okna dialogowego „Sumy częściowe” jest zakładka „Opcje”. Wygląda ona tak:

W stosunku do grup można zażądać, aby każda z nich zaczynała się na nowej stronie, aby podczas rozpoznawania grup uwzględnić wielkość liter oraz to, że przed wykonaniem obliczeń rekordy muszą zostać posortowane według tych grup.

Druga część tego okna określa kierunek sortowania grup i ewentualnie konieczność wykorzystania niestandardowej listy sortowania, jednej z tych zarejestrowanych w aplikacji w „Narzędzia → Opcje → LibreOffice Calc → Listy sortowania”.

Przycisk „OK” służy do przekształcenia bazy danych w zestawienie z odpowiednimi sumami pośrednimi. Przycisk „Usuń” przywraca zwykłą postać bazy danych bez przywrócenia porządku rekordów sprzed zestawienia.

Sumy częściowe oprócz samego utworzenia wyników tworzą także, związane z tym procesem, grupy, czyli konspekt. Fakt ten można wykorzystać do prezentacji danych. O samych grupach traktuje poprzedni rozdział tego opracowania.

Kolejna prezentacja pokaże jak utworzyć sumy częściowe danych znajdujących się w bazie danych.

Zamykając temat sum częściowych, dodam, że utworzone sumy są zawsze prezentowane z formatowaniem „Standard”. Oznacza to, że nie ma znaczenia dla tych sum, jakie formatowanie mają liczby, na podstawie których te wyniki są obliczane. Użytkownik w wynikowym zestawieniu musi to poprawić samodzielnie.

W programie Calc Apache OpenOffice sumy pośrednie otrzymują styl „Wynik” i są wyświetlane zgodnie z ustawieniami tego stylu. Łatwo wobec tego zmienić sposób wyświetlania wszystkich wyników, zmieniając właściwości stylu. W LibreOffice także jest przypisany jakiś styl, jednak jego nazwa nie jest nigdzie ujawniona.

Autokonspekt.

To polecenie wywoływane jest sekwencją „Dane → Grupa i konspekt → Autokonspekt” i automatycznie aktywuje grupy w obszarze danych. Grupy zostaną aktywowane tylko wtedy gdy w obszarze danych zostaną wykryte formuły odwołujące się do zakresów danych, czyli gdy wstawione zostały funkcje należące do zestawu jedenastu odpowiedników funkcji SUMY.CZĘŚCIOWE.

Jeśli to polecenie zostanie wywołane po umieszczeniu komórki aktywnej w obszarze danych pokazanych na rys. 12, to zostaną aktywowane grupy takie, jakie widać poniżej bez konieczności ręcznego zaznaczania poszczególnych obszarów.

Jeśli przy wywołaniu tego polecenia nie zostanie zaznaczony obszar komórek, Calc przeanalizuje wszystkie dane w tym arkuszu i utworzy stosowne grupy we wszystkich obszarach, jakie uda mu się zinterpretować.

Usuń konspekt.

To polecenie usuwa wszystkie utworzone konspekty (grupy) niezależnie od sposobu ich powstania.

Konsolidowanie.

Jak wskazuje nazwa tej czynności, scala ona dane z wybranych zakresów w jedno zestawienie. Procesowi konsolidacji należy podać jaka funkcja będzie towarzyszyć scalaniu. Tutaj także, jak przy sumach częściowych, dostępnych jest 11 funkcji. Są to: SUMA, ILE.NIEPUSTYCH, ŚREDNIA, MAKS, MIN, ILOCZYN, ILE.LICZB, ODCHYLENIE.STANDARDOWE, ODCHYLENIE.STANDARD.POPUL, WARIANCJA i WARIANCJA.POPUL.

Podczas konsolidacji można użyć tylko jednej wybranej funkcji do wszystkich danych. Cały proces jest realizowany po wywołaniu polecenia „Dane → Konsoliduj...”, w którego wyniku otwarte zostanie poniższe okno dialogowe:

W oknie występuje pięć obszarów:

Funkcja. W rozwijalnym polu należy wybrać funkcję, jaka ma być zastosowana podczas konsolidacji danych.

Obszary konsolidacji. W tym polu widoczne są wszystkie zadeklarowane obszary tych danych. To z tych obszarów nastąpi konsolidacja. Jeżeli jakiś z obszarów jest już niepotrzebny, można go wskazać, a następnie usunąć przyciskiem „Usuń”.

Obszar danych źródłowych. W tym obszarze określa się dane źródłowe. Można to zrobić poprzez zaznaczenie ich w wybranym arkuszu albo poprzez wybór nazwy obszaru (jeśli obszar ma nazwę). Obszar danych źródłowych nie ma żadnego wpływu na konsolidowane dane, jeżeli nie zostanie dodany do obszaru konsolidacji. Po określeniu obszaru źródłowego należy go zatem dodać (przycisk „Dodaj”) do obszarów konsolidacji.

Kopiuj wyniki do: Tu wskazuje się miejsce, wystarczy adres pojedynczej komórki, w którym rozpocznie się wynikowy obszar skonsolidowanych wyników.

Opcje. Ten obszar jest zazwyczaj ukryty przy otwieraniu okna dialogowego. Należy w nim zaznaczyć opcje konsolidowania. Używa się ich tylko wówczas gdy konsolidowane obszary zawierają nagłówki wierszy i/lub kolumn pozwalające na właściwe pogrupowanie danych. Ponadto opcja „Połącz z danymi źródłowymi” pozwala na automatyczne modyfikowanie danych skonsolidowanych wtedy, gdy nastąpią zmiany w danych w obszarach konsolidacji.

Wykonanie konsolidacji.

Aby wykonać konsolidację musi wystąpić przynajmniej jeden obszar konsolidacji. Obszary źródłowe a w konsekwencji obszary konsolidacji muszą znajdować się w tym samym skoroszycie. Wybrane obszary źródłowe są dodawane do obszarów konsolidacji z adresowaniem bezwzględnym, co oznacza, że w przypadku zmiany rozmiarów obszaru źródłowego lub zmiany położenia takiego obszaru, zmiany te nie zostaną uwzględnione w definicjach obszarów konsolidacji.

Obszary źródłowe mogą mieć różne rozmiary. Skonsolidowane zostaną tylko takie dane, których położenie w obszarach konsolidacji jest takie samo względem lewego górnego rogu tych obszarów. Sposób konsolidacji może zostać zmieniony, gdy obszary konsolidacji zawierają nazwy wierszy i kolumn i zaznaczono odpowiednie opcje.

Z punktu widzenia procesu konsolidacji zawartość obszarów konsolidacji nie ma znaczenie. Jednak sensem konsolidacji jest łączenie danych o podobnym charakterze.

Poniżej na ilustracji przedstawiam dwa obszary danych, które będą podlegały konsolidacji.

Obszary te zawierają dane o podobnym charakterze. Obszar drugi zawiera jednak kolumny ułożone w innym porządku niż kolumny obszaru pierwszego. Jest to zabieg celowy, mający pokazać możliwości konsolidowania.

Proces konsolidacji opisałem następująco:

Konsolidowane wartości mają być sumowane (funkcja „Suma”). Gdy w powyższym oknie dialogowym wyłączone są wszystkie opcje, wynikiem będzie następująca prezentacja.

Obszar skonsolidowanych danych zaczyna się w komórce A17. Obszary konsolidacji nie muszą mieć takich samych rozmiarów. Wynikowy obszar skonsolidowanych danych będzie miał tyle wierszy, ile ma ich największy obszar konsolidacji i tyle kolumn ile ma najszerszy z obszarów.

Największym obszarem konsolidacji jest obszar 1, zawiera 5 wierszy i 4 kolumny. Dlatego obszar skonsolidowany też będzie miał taki rozmiar. Obszar ma zawierać sumy z tych dwóch obszarów. Pierwszy wiersz i pierwsza kolumna obu obszarów nie zawiera liczb, więc odpowiednie miejsca w obszarze wynikowym nie zawierają żadnych wpisów. Pierwsze dwa wiersze z liczbami w obszarze 1 mają swoje odpowiedniki w obszarze 2., dlatego pierwsze dwa wiersze wynikowe zawierają sumę odpowiednich komórek.

Po włączeniu opcji „Etykiety wierszy” wynikowy skonsolidowany obszar wygląda tak:

Tylko pierwsza kolumna obszarów konsolidacji jest traktowana jako ta, która zawiera etykiety wierszy. Wynikowy obszar skonsolidowanych danych będzie miał tyle wierszy, ile wystąpi różnych etykiet we wszystkich obszarach konsolidacji. Wiersze o takich samych etykietach będą konsolidowane.

W obszarze wynikowym pojawi się tyle kolumn, ile ich ma najszerszy z obszarów. Konsolidacji podlegają wartości z kolejnych, odpowiadających sobie komórek w wierszu. W pokazanym przykładzie kolumny w obszarze 2. zostały umieszczone w innej kolejności niż odpowiadające im kolumny w obszarze 1. W tym wypadku sumowanie dotyczy nieodpowiadających sobie wartości.

Analogiczna sytuacja zachodzi, gdy włączona zostanie tylko opcja „Etykiety kolumn”. Pierwszy wiersz obszarów konsolidacji będzie traktowany jak wiersz zawierający nagłówki kolumn.

Wynikowy obszar skonsolidowanych danych będzie miał tyle kolumn, ile jest różnych nagłówków w obszarach konsolidacji. Proszę zwrócić uwagę na to, że rozróżnione zostały nagłówki „koszt3” i „Koszt3”. Proces konsolidacji uznał, że są to różne nagłówki. Oznacza to, że wielkość liter w etykietach ma znaczenie. Natomiast ilość wierszy wynika z obszaru konsolidacji o największej ich ilości.

Następne zestawienie dotyczy sytuacji, gdy zaznaczono obie opcje etykiet. Przed otrzymaniem zestawienia wynikowego poprawiłem nagłówek w obszarze 1. Przy takich opcjach wynikowy obszar skonsolidowanych danych ma tyle wierszy, ile jest różnych etykiet we wszystkich obszarach i tyle kolumn ile jest różnych nagłówków.

Proces konsolidacji odnajduje wartości należące do tych samych etykiet. To powoduje, że jeżeli nawet dane nie są zestawione w takiej samej kolejności to i tak zostaną prawidłowo skonsolidowane.

We wszystkich powyżej pokazanych przykładach wartości pokazane w wynikowym obszarze skonsolidowanych danych są obliczane w momencie konsolidowania. To znaczy tyle, że gdy nastąpią jakieś zmiany w obszarach konsolidacji, to te zmiany nie wpłyną na wynikowy obszar skonsolidowanych danych.

Ostatnia opcja: „Połącz z danymi źródłowymi” powoduje, że skonsolidowane dane „reagują” na zmiany dokonane w obszarach konsolidacji. Mowa tu jednak jest wyłącznie o zmianach wartości liczbowych w tych obszarach. Włączenie tej opcji powoduję zmianę organizacji wynikowego obszaru skonsolidowanych danych.

Wynikowy obszar skonsolidowanych danych będzie zawierał wszystkie wiersze i kolumny obszarów konsolidacji z taką zmianą, że dane kolumnowe zostana przestawione do zgodnej kolejności. Wiersze o jednakowej etykiecie są łączone w grupy i dla każdej grupy powstaje wiersz danych skonsolidowanych zawierający formulę konsolidacji. Pokazuje to poniższa ilustracja:

Jak widać na ilustracji, „koszt2” dla pozycji „Ula” jest wyliczany jako SUMA(C20:C21). W tych właśnie komórkach znajdują się przeniesione dane z obszaru źródłowego. Dane te są jednocześnie zgrupowane i ukryte.

Ten sam obszar po „odsłonięciu” danych grupy „1” wygląda następująco:

Obszar zawiera najpierw wiersze źródłowe z wartościami „dopasowanymi” do właściwych kolumn oraz z informacją, z jakiego arkusza pochodzą. Każda komórka tego wiersza zawierająca liczbę ma wpisane odwołanie do swojego położenia w obszarze źródłowym, w postaci adresu bezwzględnego. Wiersze o takiej samej etykiecie stanowią grupę. Dla każdej takiej grupy tworzony jest wiersz wynikowy, zawierający funkcję określoną w parametrach konsolidacji. Ten wynikowy wiersz nie należy do żadnej grupy, gdy więc zostaną ukryte wiersze grupy „1”, pozostanie widoczny tylko obszar skonsolidowanych danych taki jak na rys. 24.

Konsolidacja pozwala szybko otrzymać zbiorcze dane, zgromadzone w różnych zestawieniach. Nie ma jednak mechanizmu automatycznego dopasowania się do zmian (poza omówionymi przed chwilą zmianami w obszarach źródłowych). Każda zmiana obszaru źródłowego, czy to dotycząca rozmiaru, czy położenia, wymaga definiowania od nowa obszarów konsolidacji. Najbezpieczniej jest w takiej sytuacji usunąć wynikowy obszar skonsolidowanych danych i utworzyć go ponownie.

Funkcje bazy danych.

Bazodanowy aspekt wykorzystywania arkusza Calc został od początku zauważony przez twórców oprogramowania. Arkusz ma dołączoną specjalną grupę funkcji przeznaczoną do takich właśnie obliczeń. Funkcje te to znane już z innej postaci funkcje statystyczne, adaptowane do zastosowań przy bazach danych.

Wszystkie te funkcje wykorzystują obszar kryteriów, taki jaki został omówiony w części poświęconej filtrom zaawansowanym.

Ogólna postać wywołania tych funkcji jest następująca:

NAZWA.FUNKCJI(baza danych;pole obliczeniowe;kryteria)

Nazwa funkcji określa rodzaj wykonanych obliczeń na danych, które spełniają kryteria wyboru. Wszystkie funkcje bazy danych rozpoczynają się od znaków „BD.”

Baza danych to obszar danych zorganizowanych w postaci bazy, zawierający nagłówki kolumn. Obszar ten można podać jako zakres komórek, nazwę zakresu lub nazwę zakresu bazy danych. O różnicach w znaczeniu tych pojęć wspomniałem w rozdziale „Nazwa zakresu bazy danych”.

Pole obliczeniowe to wskazanie, które z pól bazy danych ma wziąć udział w obliczeniach. Pole można wskazać poprzez podanie adresu komórki, w której znajduje się jego nazwa, poprzez podanie numeru tego pola w rekordzie bazy, przy czym pola w rekordzie liczone są od 1, lub poprzez wpisanie nazwy pola znajdującej się w nagłówku bazy danych. Nazwę pola należy zawsze wprowadzać w cudzysłowie. Ten parametr może zostać pominięty albo podany jako 0 tylko dla funkcji BD.ILE.REKORDÓW i BD.ILE.REKORDÓW.A. funkcje te wykonają wówczas obliczenia na wszystkich rekordach spełniających kryteria wyboru. Pozostałe funkcje baz danych, w przypadku braku tego parametru zwrócą: błąd #VALUE (w LibreOffice) albo wartość 0 (w Apache OpenOffice).

Kryteria, to obszar zawierający reguły wyboru rekordów. Może zostać wskazany jako obszar komórek albo poprzez przypisaną mu nazwę.

Wszystkie funkcje bazy danych dopuszczają w obszarze kryteriów wyrażenia regularne. Jeżeli wyrażenia regularne mają być wykorzystane, należy upewnić się, że zaznaczona została opcja „Włącz wyrażenia regularne w formułach” w parametrach programu Calc, „Narzędzia → Opcje → (x)Office Calc → Oblicz”.

Oto dostępne funkcje kategorii „bazy danych”, w nawiasie ich angielska nazwa.

BD.ILE.REKORDÓW (DCOUNT)

Funkcja podaje ile rekordów (wierszy), w zadeklarowanym obszarze, spełnia kryteria wyboru i zawiera wartości numeryczne we wskazanym polu obliczeniowym. Oznacza to, że rekordy, które mają pole obliczeniowe puste albo zapisaną w nim informację tekstową nie zostaną zaliczone do wyniku, nawet jeśli spełniają kryteria. Jeśli pole obliczeniowe zostanie pominięte, to zliczone zostaną wszystkie rekordy spełniające kryteria.

BD.ILE.REKORDÓW.A (DCOUNTA)

Funkcja podaje ile rekordów (wierszy), w zadeklarowanym obszarze, spełnia kryteria wyboru. W odróżnieniu od poprzedniej funkcji nie zostaną wliczone tylko rekordy zawierające "pustą" wartość w polu obliczeniowym.

BD.ILOCZYN (DPRODUCT)

Funkcja mnoży wszystkie komórki pola obliczeniowego tych danych, które spełniają warunki wyboru.

BD.MAKS (DMAX) i BD.MIN (DMIN)

Funkcje zwracają wartość maksymalną albo minimalną występującą w polu obliczeniowym rekordów spełniających kryteria wyboru.

BD.ODCH.STANDARD (DSTDEV)

Funkcja oblicza odchylenie standardowe populacji na podstawie próbki, wykorzystując liczby w kolumnie bazy danych spełniające warunki wyboru. Dane obliczeniowe są traktowane jako próbka z populacji.

BD.ODCH.STANDARD.POPUL (DSTDEVP)

Funkcja oblicza odchylenie standardowe populacji na podstawie wszystkich komórek zakresu danych spełniających warunki wyboru. Dane obliczeniowe są traktowana jako cała populacja.

BD.POLE (DGET)

Funkcja zwraca zawartość komórki pola obliczeniowego spośród rekordów spełniających warunki wyboru. Funkcja zwraca zawartość pola tylko wtedy, gdy wystąpi tylko jeden dopasowany rekord. Gdy warunki wyboru wykluczą wszystkie rekordy, wynikiem funkcji jest wartość #ARG! (Apache OpenOffice) lub #VALUE! w (LibreOffice). Gdy warunki wyboru spełnia więcej niż jeden rekord, zwracany jest wynik „Błąd:502”.

BD.SUMA (DSUM)

Funkcja zwraca sumę wartości wszystkich komórek w polu obliczeniowym tych wszystkich wierszy (rekordów), które odpowiadają warunkom wyboru.

BD.WARIANCJA (DVAR)

Funkcja zwraca wariancję wszystkich wartości pola obliczeniowego, we wszystkich rekordach spełniających warunki wyboru. Dane obliczeniowe są traktowane jako próbka z populacji.

BD.WARIANCJA.POPUL (DVARP)

Funkcja oblicza wariancję wartości wszystkich komórek pola bazy danych we wszystkich rekordach spełniających określone kryteria wyszukiwania. Dane obliczeniowe są traktowana jako cała populacja.

BD.ŚREDNIA (DAVERAGE)

Funkcja zwraca średnią wartość pola obliczeniowego we wszystkich wierszach (rekordach bazy danych) odpowiadających warunkom wyboru.

Przyjmując, że zadeklarowany obszar bazy znajduje się w zakresie B23:F230 i ma przypisaną nazwę „baza”, trzecia kolumna tej bazy ma nagłówek „Wartość towaru”, a obszar kryteriów nazwano „kryteria”, obliczenie sumy wartości towaru może zostać zapisane w następujący sposób: