Uśrednianie i pobieranie danych za pomocą DAVERAGE i DGET

W tej lekcji omówimy dwie ostatnie funkcje bazodanowe:

• DAVERAGE — średnia wartości liczbowych pola dla dopasowanych rekordów.
• DGET — pobiera pojedynczą wartość z jednego wiersza spełniającego kryteria.

Obie funkcje korzystają ze znanego już wzorca (database, field, criteria), dlatego trzeba poznać głównie zwracane przez nie wyniki, a nie nową składnię.

DAVERAGE(database, field, criteria) oblicza średnią wartości w kolumnie pola dla wierszy spełniających kryteria z zakresu kryteriów.

• database — cała tabela wraz z nagłówkami.
• field — kolumna liczbowa, dla której ma zostać obliczona średnia.
• criteria — blok reguł dopasowania.

Funkcja działa podobnie do AVERAGEIFS, ale odczytuje warunki z komórek.

W danych sprzedażowych znajdujących się w zakresie A1:C13, z nagłówkami Region, Rep i Amount, należy umieścić Region w E1, a East w E2.

Formuła zwróci średnią wartości Amount dla wierszy z regionu East. Jeśli zamówienia z regionu East opiewają na 1200, 800 i 1000, średnia wyniesie 1000. Po zmianie wartości E2 na West średnia zaktualizuje się automatycznie.

DAVERAGE obsługuje pełny zestaw kryteriów. Aby obliczyć średnią wyłącznie dla dużych zamówień z regionu East, należy utworzyć dwukolumnowy zakres kryteriów: umieścić nagłówki Region i Amount, a pod nimi wartości East i >1000.

Warunki w tym samym wierszu oznaczają AND, więc funkcja obliczy średnią kwot dla zamówień z regionu East o wartości powyżej 1000. Puste pola są pomijane — na średnią wpływają tylko wartości liczbowe.

W przeciwieństwie do DSUM, które zwraca 0, DAVERAGE zwraca błąd #DIV/0!, gdy żaden wiersz nie spełnia kryteriów — nie można bowiem obliczyć średniej z zera wartości.

Aby temu zapobiec, należy najpierw sprawdzić liczbę dopasowań albo opakować funkcję w IFERROR, aby zamiast kodu błędu wyświetlić czytelny komunikat.

DGET działa inaczej: zwraca pojedynczą wartość z pola jednego wiersza spełniającego kryteria.

Można używać jej jak funkcji wyszukiwania. Jeśli istnieje unikatowy klucz, na przykład identyfikator zamówienia, DGET pobierze jedno pole z dokładnie tego rekordu. Jej zaleta, ale też potencjalne źródło problemów, polega na wymaganiu dokładnie jednego dopasowania.

Załóżmy, że tabela zawiera unikatowe nazwiska przedstawicieli handlowych i chcą Państwo pobrać kwotę dla przedstawicielki o imieniu „Sara”. Należy umieścić Rep w E1, a Sara w E2.

DGET znajdzie jedyny wiersz dotyczący Sary i zwróci jej wartość Amount. Ponieważ funkcja może jednocześnie dopasowywać wartości w kilku kolumnach kryteriów, obsługuje wyszukiwanie na podstawie wielu kluczy, czego nie potrafi zwykła funkcja VLOOKUP.

DGET rygorystycznie wymaga dopasowania dokładnie jednego wiersza:

• Jeśli żaden wiersz nie pasuje, funkcja zwraca #VALUE!.
• Jeśli pasuje więcej niż jeden wiersz, funkcja zwraca #NUM!.

Te błędy są w rzeczywistości przydatne — ostrzegają, że brakuje klucza albo że nie jest on unikatowy. Należy zawężać kryteria, aż dokładnie jeden rekord będzie spełniał warunki.

Aby zagwarantować jedno dopasowanie, należy dodać więcej warunków. Aby pobrać kwotę dla Sary z regionu East, należy użyć dwukolumnowego zakresu kryteriów: umieścić nagłówki Rep i Region, a pod nimi wartości Sara i East.

Logika AND zawęża wyniki do jednego wiersza, a DGET zwraca jego wartość Amount. W ten sposób DGET działa jako przejrzysta funkcja wyszukiwania na podstawie wielu kluczy.

Ponieważ DGET zgłasza błąd przy zerowej liczbie dopasowań lub przy wielu dopasowaniach, warto ją opakować, aby zapewnić płynniejszą obsługę. IFERROR zamienia oba rodzaje błędów na czytelny komunikat.

Jeśli duplikat ma być oznaczany inaczej niż brak dopasowania, można najpierw sprawdzić liczbę wyników za pomocą DCOUNTA i odpowiednio wybrać komunikat.

Oto krótkie wskazówki dotyczące wyboru właściwego narzędzia:

• Potrzebują Państwo sumy dopasowanych wierszy? Należy użyć DSUM.
• Potrzebują Państwo liczby dopasowanych wierszy? Należy użyć DCOUNT lub DCOUNTA.
• Potrzebują Państwo średniej dopasowanych wierszy? Należy użyć DAVERAGE.
• Potrzebują Państwo jednej wartości z pojedynczego dopasowanego wiersza? Należy użyć DGET.

Wszystkie cztery funkcje korzystają z jednego zakresu kryteriów, dzięki czemu można utworzyć raport, który sumuje, zlicza, oblicza średnie i wyszukuje dane — wszystko na podstawie tego samego zestawu komórek warunków.

Kryteria funkcji DGET pasują do trzech wierszy w tabeli. Co zwróci DGET?

Ukończyli Państwo omawianie rodziny funkcji D:

• DAVERAGE oblicza średnią wartości liczbowych pola dla dopasowanych wierszy; brak dopasowań powoduje zwrócenie błędu #DIV/0!.
• DGET zwraca jedną wartość z dokładnie jednego dopasowanego wiersza; brak dopasowań powoduje zwrócenie błędu #VALUE!, a wiele dopasowań — błędu #NUM!.
• Obie funkcje korzystają ze wzorca (database, field, criteria) oraz reguł zakresu kryteriów AND/OR.
• Należy opakowywać je w IFERROR, aby uzyskać przejrzysty i profesjonalny wynik.

W połączeniu z DSUM i DCOUNT pozwalają teraz tworzyć kompletne raporty oparte na kryteriach bezpośrednio ze strukturyzowanej tabeli.