Funkcje baz danych

LibreOffice Calc oferuje wiele możliwości wykonania obliczeń na komórkach wyodrębnionych z dużego zbioru informacji. Jednym z nich jest zestaw funkcji bazodanowych (funkcji baz danych). Przyjrzymy im się w niniejszym artykule. Zaczniemy od przygotowania zakresu danych i opisu składni funkcji, aby — po kilku przykładach — zobaczyć różnice pomiędzy nimi a funkcjami macierzowymi.

Przygotowanie bazy danych

Aby omawiane funkcje zadziałały, konieczne jest odpowiednie przygotowanie zbioru danych.

Przede wszystkim, jego pierwszy wiersz musi zawierać unikatowe (niepowtarzalne) etykiety kolumn. Mogą to być dowolne ciągi znaków, ale warto, aby trafnie opisywały one zawartość. Co prawda dla arkusza nie ma to żadnego znaczenia, ale człowiekowi znacznie ułatwi pracę.

Wszystkie dane w obrębie każdej z kolumn powinny być tego samego typu. Arkusz kalkulacyjny, w przeciwieństwie do prawdziwej bazy danych, nie kontroluje tego ani nie wymusza. Jednak gdy warunek ten nie zostanie spełniony, możemy otrzymać dość nieoczekiwane wyniki.

Postulaty te spełnia wykaz opłat za korzystanie z sieci telefonii komórkowej, baza stanowiąca podstawę poniższych przykładów.

Tworzenie funkcji

LibreOffice posiada dwanaście funkcji bazodanowych, dzięki którym możemy dokonać szeregu obliczeń na danych spełniających określone kryteria.

Nazwy wszystkich z nich zaczynają się od BD (jak Baza Danych) i kropki. Po niej następuje nazwa funkcji arkusza kalkulacyjnego odpowiadającej za wykonane obliczenie. Dla przykładu, funkcja BD.SUMA jest bazodanowym odpowiednikiem funkcji SUMA.

Omawiane funkcje przyjmują trzy argumenty. Pierwszym jest zakres komórek stanowiących bazę danych. Drugim — kolumna, w obrębie której mają zostać wykonane obliczenia. Trzecim zaś — kryteria wyodrębniania komórek.

Kolumnę można identyfikować zarówno za pomocą liczby (zaczynając od 1, licząc od lewej strony), jak i etykiety (koniecznie ujętej w cudzysłowy). Poniższe dwa zapisy są równoważne:

=BD.MAKS(baza.A1:H201;8;A1:A2)
=BD.MAKS(baza.A1:H201;"Koszt";A1:A2)

Kryteriami filtrowania jest specjalnie przygotowany zakres komórek. Zawiera on przynajmniej jedną etykietę kolumny z bazy oraz — poniżej — poszukiwaną w niej wartość.

Gdy wiersze wyodrębniamy na podstawie wartości z kilku kolumn, należy umieścić je w jednym wierszu kryteriów. Lub, innymi słowy, w obrębie wiersza komórki łączy koniunkcja.

Jeżeli interesują nas wiersze zawierające jedną lub drugą wartość, powinniśmy umieścić je pod sobą. Ujmując rzecz inaczej: kolejne wiersze w obrębie zakresu łączy alternatywa.

Kilka przykładów

Najprostszy przypadek

Spróbujmy odpowiedzieć na pytanie: ile kosztowało najdroższe nieprzerwane połączenie z Internetem?

Aby poznać odpowiedź, wyodrębnimy z bazy danych wszystkie wpisy dotyczące połączeń internetowych. Następnie wybierzemy z nich ten, który w kolumnie „Koszt” ma największą liczbę. Jednocześnie jest to liczba, która nas interesuje.

Do stworzenia kryteriów wyodrębniania funkcji bazodanowej będą nam potrzebne dwie komórki. W jednej wpiszemy „Rodzaj aktywności”, poniżej zaś — „Dane”.

Następnie wprowadzamy formułę:

=BD.MAKS(baza.A1:H201;8;A1:A2)

Okno programu Calc pokazujące najprostszy przypadek formuły bazy danych. A1: Rodzaj aktywności; A2: Dane.

Spójnik I (koniunkcja)

Drugie zadanie jest odrobinę bardziej skomplikowane: ile pieniędzy wydano na wiadomości tekstowe (SMS) wysłane 13 lipca?

Oznacza to, że interesują nas wszystkie rekordy z 13 lipca dotyczące wiadomości SMS. Gdy je wyodrębnimy, wystarczy obliczyć sumę liczb z kolumny „Koszt”.

Tym razem będą nam potrzebne dwa nagłówki. Pod „Rodzaj aktywności” wpisujemy „SMS”. Obok zaś wprowadzamy „Data”, a pod nią — „13.07.2013”. Odpowiednio modyfikujemy również naszą formułę:

=BD.SUMA(baza.A1:H201;8;A1:B2)

Okno programu Calc pokazujące formułę bazy danych wykorzystującą koniunkcję. A1: Rodzaj aktywności; B1: Data; A2: SMS; B2: 13.07.2013.

Przy okazji warto zobaczyć, jak będzie się zmieniał wynik po usunięciu jednej z komórek w drugim wierszu.

Spójnik LUB (alternatywa)

Załóżmy kolejne pytanie: ile sumarycznie kosztował transfer danych?

Ponieważ na transfer składają się zarówno połączenia internetowe, jak i wiadomości multimedialne (MMS), z naszej bazy musimy wyodrębnić wiersze dotyczące bądź jednego, bądź drugiego rodzaju aktywności.

W tym celu pozostawiamy kolumnę „Rodzaj aktywności”, pod nią zaś wpisujemy „MMS” oraz (jeszcze niżej) „Dane”. Ponownie modyfikujemy trzeci argument funkcji.

=BD.SUMA(baza.A1:H201;8;A1:B3)

Okno programu Calc pokazujące formułę bazy danych wykorzystującą alternatywę. A1: Rodzaj aktywności; A2: MMS; A3: Dane.

Koniunkcja oraz alternatywa

Omówione powyżej spójniki I oraz LUB można łączyć w ramach jednego zbioru kryteriów. Przyjrzymy się temu zabiegowi poszukując odpowiedzi na pytanie: ile łącznie wysłano wiadomości tekstowych i multimedialnych?

Ponownie będziemy potrzebowali dwóch kolumn. Pod „Rodzaj aktywności” wpisujemy „SMS” oraz „MMS”. W drugiej, oznaczonej „Kierunek”, umieszczamy dwukrotnie „Wychodzące”. Ponadto zmieniamy drugi argument funkcji.

=BD.SUMA(baza.A1:H201;7;A1:B3)

Okno programu Calc pokazujące formułę bazy danych wykorzystującą zarówno koniunkcję, jak i alternatywę. A1: Rodzaj aktywności; B1: Kierunek; A2: SMS; B2: Wychodzące; A3: MMS; B3: Wychodzące.

Ze zwykłej ciekawości warto usunąć którąkolwiek z komórek w kryteriach. Spowoduje to zwiększenie otrzymanego wyniku.

Dzieje się tak, ponieważ spójnik I ma pierwszeństwo przed spójnikiem LUB. Można sobie wyobrazić, że program wybiera z bazy danych rekordy spełniające wszystkie kryteria określone w pierwszym wierszu. Operację tę powtarza dla następnych wierszy, a na końcu scala ze sobą otrzymane wyniki.

Ujmując rzecz bardziej symbolicznie, możliwa jest operacja (a I b) LUB (c I d); nie jest jednak możliwe działanie (a LUB b) I (c LUB d).

Spójniki WIĘKSZY NIŻ…, MNIEJSZY NIŻ…

Podczas określania kryteriów wyodrębniania danych możemy również skorzystać z operatorów porównywania. Sprawdźmy to na przykładzie pytania: ile wysłanych wiadomości tekstowych składało się z kilku jednostek?

Będziemy potrzebowali dwóch kolumn. W pierwszej, zatytułowanej „Rodzaj aktywności”, wpisujemy „SMS”. Druga nosi etykietę „min./kB/szt.”; w niej wpisujemy „>1”.

Ponadto modyfikujemy formułę:

=BD.ILE.REKORDÓW(baza.A1:H201;8;A1:B2)

Okno programu Calc pokazujące formułę bazy danych wyodrębniającą rekordy przy pomocy operatora porównywania liczb. A1: Rodzaj aktywności; B1: min./kB/szt.; A2: SMS; B2: >1.

Ponieważ daty i godziny są przechowywane w arkuszu jako liczby, podczas pracy z czasem możemy wykorzystywać te same operatory. Wystarczy zamieścić je przed zapisem w formacie jednoznacznie zrozumiałym dla programu.

Dla przykładu, poniższa konfiguracja dostarczy odpowiedź na pytanie, ile wiadomości tekstowych wysłano w drugiej połowie miesiąca:

=BD.ILE.REKORDÓW(baza.A1:H201;8;A1:C2)

Okno programu Calc pokazujące formułę bazy danych wyodrębniającą rekordy przy pomocy operatora porównywania liczb; operator został użyty na komórce zawierającej datę. A1: Rodzaj aktywności; B1: Kierunek; C1: Data; A2: SMS; B2: Wychodzące; C2: >2013-07-15.

Zakresy

Podczas wyodrębniania wierszy na podstawie danych liczbowych często mamy do czynienia z zakresami. Jednym z takich przypadków jest delikatna modyfikacja poprzedniego pytania: ile wiadomości tekstowych wysłano między 11 a 20 lipca?

Naturalnie problem ten możemy rozwiązać za pomocą alternatywy, ale tworzenie zakresu liczącego aż dziesięć wierszy będzie żmudne. O ileż lepiej byłoby skorzystać z koniunkcji oraz spójników MNIEJSZY LUB RÓWNY… i WIĘKSZY LUB RÓWNY… Jest to możliwe po powtórzeniu nagłówka.

W tym celu w zakresie kryteriów umieszczamy dwukrotnie etykietę „Data”. Pod pierwszą z nich wpisujemy „>=2013-07-11”, pod drugą zaś — „<=2013-07-20”. Ponadto modyfikujemy trzeci argument funkcji.

=BD.ILE.REKORDÓW(baza.A1:H201;8;A1:D2)

Okno programu Calc pokazujące formułę bazy danych wyodrębniającą rekordy przy pomocy operatorów porównywania liczb. Powtórzenie etykiety kolumny z datą pozwala wyodrębnić rekordy utworzone później niż, ale wcześniej niż. A1: Rodzaj aktywności; B1: Kierunek; C1: Data; D1: Data; A2: SMS; B2: Wychodzące; C2: >=2013-07-10;

Funkcje bazodanowe a formuły macierzowe

Uważni czytelnicy z pewnością zauważą, że funkcje baz danych pomagają rozwiązać wiele z tych problemów, które dotychczas rozwiązywaliśmy przy pomocy formuł macierzowych. Dlatego warto zaznaczyć różnice pomiędzy nimi.

  • Funkcje bazodanowe są przynajmniej tak samo szybkie jak formuły macierzowe, a w przypadku dużych zbiorów danych — szybsze od nich.
  • Funkcje bazodanowe zajmują więcej komórek w arkuszu — konieczne jest przygotowanie zakresu kryteriów. Formuła macierzowa często mieści się tylko w jednej komórce.
  • Tym samym funkcje bazodanowe są często czytelniejsze niż formuły macierzowe. Ich modyfikacja nie wymaga również dodatkowej wiedzy i umiejętności.
  • LibreOffice oferuje tylko dwanaście funkcji bazodanowych. Do wykonania niektórych obliczeń konieczne jest wykorzystanie formuł macierzowych.
  • Wynikiem działania funkcji bazodanowej jest zawsze tylko i wyłącznie jedna komórka. Wynik formuł macierzowych może zajmować wiele komórek.

Podsumowanie

  • Funkcje bazodanowe do poprawnego działania wymagają odpowiedniego przygotowania zbioru danych.
  • Wszystkie z nich przyjmują trzy argumenty: zakres zbioru danych, adres kolumny z obliczanymi wartościami oraz zakres kryteriów wyodrębniania rekordów.
  • Zakres kryteriów musi być skonstruowany w określony sposób. Pierwszym wierszem muszą być etykiety kolumn. Następne wiersza zawierają poszukiwane dane. Komórki w ramach wiersza łączone są spójnikiem I; wiersze w ramach zakresu łączone są spójnikiem LUB.
  • Do tworzenia kryteriów można używać operatorów porównania.
  • Funkcje baz danych są szybkie i proste w obsłudze, ale okupują to mniejszą wszechstronnością niż formuły macierzowe.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

Komentarzy: 1

  • Leon Miklosik napisał(a):

    Gratuluję, Panie Mirosławie, regularnych i wartościowych publikacji. Pozdrawiam Pana.