Analiza długich list - funkcje bazy danych

Analizowanie długich list danych rozciągających się na kilkanaście tysięcy wierszy to nic przyjemnego. Jak odszukać w takim gąszczu informacji konkretny rekord? Czy konieczne jest pogrupowanie danych i ukrycie tych niepotrzebnych? Korzystanie z filtrowania czy projektowanie tabeli przestawnej trwa, a Ty potrzebujesz znać pewną wartość natychmiast. Z pomocą przyjdą mało popularne funkcje z kategorii Bazy danych. Po określeniu kryterium przeszukają zakres komórek, który obejmuje lista, i błyskawicznie wyświetlą wartość, której w danej chwili potrzebujesz.

Przykładową długą listę zawierającą szczegóły dostaw przedstawia rysunek 1.

Rys. 1. Dane dotyczące dostaw

Załóżmy, że właśnie dzwoni Twój szef i prosi Cię, abyś podał mu przez telefon numer dokumentu, który dotyczył towaru dostarczonego do firmy o nazwie Poga. Możesz tę wartość wyszukać za pomocą funkcji bazy danych o nazwie BD.POLE. Najpierw jednak konieczne jest stworzenie wiersza z kryteriami wyszukiwania.

Możesz to zrobić bardzo szybko:

1. Skopiuj nagłówki listy (Ctrl + C) i wklej je (Ctrl + V) w dowolnym miejscu arkusza, np. pod zestawieniem. To będą Twoje kryteria. Widok arkusza przedstawia rysunek 2.

Rys. 2. Nagłówki listy posłużą jako kryteria wyszukiwania

Wskazówka
Wiersz z kryteriami dla funkcji bazodanowych powinien być identyczny jak wiersz z nagłówkami listy. Zalecamy zatem kopiowanie i wklejanie zamiast ręcznego wpisywania z klawiatury.

Teraz możesz już zastosować funkcję w celu wyszukania numeru dokumentu:

2. W komórce C23 wpisz nazwę firmy (w przykładzie Poga).

3. Do komórki A24 wpisz natomiast taką formułę:

=BD.POLE(A3:E20;1;A22:E23)

i zatwierdź, wciskając Enter.

Rys. 3. Błyskawicznie odnaleziona wartość

W pierwszym argumencie funkcji powinieneś podać zakres komórek, który ma być przeszukiwany. Pamiętaj, aby uwzględnić także nagłówki listy (w przykładzie A3:E20 – porównaj z rysunkiem 1), w przeciwnym razie funkcja zwróci błąd. Drugi argument to numer kolumny, w której znajduje się poszukiwana wartość (kolumny liczysz względem zakresu w pierwszym argumencie, a nie arkusza – szerzej na ten temat przy opisie funkcji BD.ŚREDNIA). Równie dobrze zamiast numeru kolumny możesz podać nazwę jej nagłówka, ale musi być ona zawarta w cudzysłowie (w przykładzie: "Nr dokumentu"). Trzeci argument to zakres komórek obejmujący wiersz z powtórzonymi nagłówkami listy oraz wprowadzoną poniżej szukaną wartością.

Uwaga
Powinieneś bardzo precyzyjnie określić kryteria wyszukiwania. Jeśli w przeszukiwanym zakresie nazwa Poga wystąpi więcej niż jeden raz, wtedy funkcja zwróci błąd (#LICZBA!). W tej sytuacji powinieneś uściślić kryterium wyszukiwania np. o wartość dostawy. Wpisz ją do komórki E23.

Przyjmijmy, że Twoim zadaniem jest teraz zliczenie wszystkich dostaw, których wartość przekracza 6000 zł. Do wykonania tego zadania posłużymy się funkcją BD.ILE.REKORDÓW.

W tym celu:

1. Wpisz do komórki E23 (odnoszącej się w przykładzie do kolumny Wartość dostawy) kryterium wyszukiwania, w przykładzie: >6000

2. Do komórki A24 wpisz teraz taką formułę:

=BD.ILE.REKORDÓW(A3:E20;;A22:E23)

i zatwierdź, wciskając Enter.

Powinna zostać zwrócona liczba 2 - porównaj z rysunkiem 1. Uwzględnione zostały komórki E8 i E14.

Pierwszym argumentem – jak w przypadku funkcji BD.POLE – jest przeszukiwany zakres razem z nagłówkami kolumn. Trzeci argument to wiersze zawierające kryteria. Drugi argument został pominięty, gdyż nie jest konieczny do poprawnego działania formuły.

Rys. 4. Zliczone dostawy o wartości przekraczającej 6000

A co, jeśli chciałbyś zliczyć rekordy, które spełniają jednocześnie dwa warunki (większe od 2000 i mniejsze od 4000)? Nic prostszego!

Aby to zrobić:

1. Wyczyść kryterium wprowadzone do komórki E23 i formułę z komórki A24.

2. Nagłówek ID klienta z komórki D22 zastąp wpisem z komórki E22 - Wartość dostawy.

3. Do komórki D23 wpisz teraz pierwsze kryterium: >2000, a do komórki po prawej (tzn. E23) drugie: <4000

Wynik otrzymasz automatycznie bez konieczności modyfikacji formuły.

Rys. 5. Dwa kryteria dla funkcji zliczającej rekordy

Może także zajść potrzeba wykonania szybkiego obliczenia na wyszukanych wartościach. Przyjmijmy, że potrzebujesz znać średnią wartość dostaw zrealizowanych przez dostawcę o identyfikatorze TT12. W tym przypadku zastosujemy inną funkcję bazodanową BD.ŚREDNIA.

Aby wykonać obliczenie:

1. Wiersz z kryteriami przywróć do postaci z rysunku 2 (nagłówki powinny być zgodne z listą).

2. Do komórki B23 wpisz identyfikator dostawcy: TT12

3. Do komórki A24 wpisz taką formułę:

=BD.ŚREDNIA(A3:E20;5;A22:E23)

i zatwierdź, wciskając Enter.

Zostanie wyświetlona liczba z wieloma miejscami po przecinku, ale wystarczy, że nadasz walutowe formatowanie komórki z paska narzędziowego i uzyskasz efekt widoczny na rysunku 6.

Rys. 6. Średnia wartość dostaw zrealizowanych przez dostawcę o określonym identyfikatorze

Pierwszy i trzeci argument mają takie same znaczenie jak w przypadku wcześniej przedstawionych formuł. Natomiast drugi argument (o nazwie pole) oznacza numer kolejnej kolumny od lewej, w której znajdują się wartości brane do obliczeń. Numer ten nie jest liczony względem arkusza, tylko względem zakresu komórek określonym w pierwszym argumencie funkcji. Jeśli np. wpiszesz B3:F20, to drugi argument się nie zmieni. Pierwsza kolumną nadal jest B, a piątą – F.

Wskazówka
W analogiczny sposób możesz zsumować wartości (za pomocą funkcji BD.SUMA), a także wyznaczyć wartość maksymalną (BD.MAX) i minimalną (BD.MIN) dostaw. Podstaw nowe nazwy funkcji, natomiast argumenty pozostaw bez zmian.