Uśrednianie według wielu warunków za pomocą AVERAGEIFS

Funkcja AVERAGEIFS oblicza średnią wartości spełniających kilka warunków jednocześnie. Zamiast obliczać średnią ze wszystkich transakcji sprzedaży, można zadać pytanie: Jaka była średnia wartość sprzedaży w regionie East w styczniu?

Funkcja ta uzupełnia całą trójkę: SUMIFS sumuje, COUNTIFS zlicza, a AVERAGEIFS oblicza średnią — wszystkie korzystają z tego samego stylu obsługi wielu kryteriów. Jeśli znają Państwo jedną z nich, znają już Państwo prawie wszystkie trzy.

AVERAGEIFS działa dokładnie tak samo jak SUMIFS. Najpierw podaje się zakres wartości do uśrednienia, a następnie pary kryteriów.

• average_range — liczby, które należy uśrednić
• criteria_range1, criteria1
• criteria_range2, criteria2

W tle funkcja sumuje pasujące wartości i dzieli je przez liczbę dopasowanych wartości — w praktyce jest to SUMIFS podzielone przez COUNTIFS, ujęte w jednej przejrzystej funkcji.

Jeśli kolumna A zawiera Region, kolumna B — Miesiąc, a kolumna C — Kwota, oto średnia wartość sprzedaży w regionie East w styczniu.

Wartości z C:C są uśredniane. Dwie pary kryteriów ograniczają wiersze do regionu East i stycznia, zanim zostanie obliczona średnia.

Można filtrować uśredniane wartości według ich wielkości. Załóżmy, że potrzebna jest średnia wyłącznie dla dużych sprzedaży w regionie East — czyli tych powyżej 100.

W tym przypadku kolumna C jest używana zarówno jako average_range, jak i criteria_range. AVERAGEIFS bez problemu wykorzystuje tę samą kolumnę w obu rolach.

Aby utworzyć raport sterowany przez użytkowników, należy odwoływać się do komórek zamiast wpisywać wartości bezpośrednio. W komórce F1 należy umieścić region, a w F2 — minimalną kwotę.

Operator porównania należy połączyć z odwołaniem do komórki: ">"&F2 oznacza większe niż wartość znajdująca się w F2. W przypadku prostego dopasowania tekstu, na przykład regionu, operator nie jest potrzebny — wystarczy odwołanie do komórki.

Najważniejsza pułapka związana z AVERAGEIFS: jeśli żaden wiersz nie spełnia wszystkich kryteriów, nie ma czego uśredniać, więc funkcja zwraca błąd #DIV/0!.

Różni się to od działania SUMIFS (która zwraca 0) i COUNTIFS (która również zwraca 0). Średnia dla zera elementów jest niezdefiniowana, dlatego Excel zwraca błąd zamiast zgadywać.

Należy otoczyć formułę funkcją IFERROR, aby wyświetlić przyjazną wartość, gdy nic nie pasuje. Zamiast nieestetycznego błędu #DIV/0! użytkownik zobaczy myślnik lub komunikat.

Dzięki temu pulpity nawigacyjne zachowują przejrzysty wygląd, nawet gdy dana kombinacja filtrów nie zwraca danych. Przy uśrednianiu przefiltrowanych danych zawsze należy uwzględnić przypadek pustego wyniku.

Ważny szczegół: AVERAGEIFS pomija puste komórki w average_range — nie uwzględnia ich ani w sumie, ani w dzielniku. Komórka zawierająca 0 zawiera jednak rzeczywistą liczbę i jest uwzględniana.

Jeśli zera są wartościami zastępczymi oznaczającymi brak danych, obniżą średnią. W razie potrzeby można dodać kryterium takie jak C:C, "<>0", aby je wykluczyć.

Można dodawać tyle kryteriów, ile potrzeba. Jeśli kolumna D zawiera Sprzedawcę, należy znaleźć średnią wartość sprzedaży z regionu East, w styczniu, zrealizowanej przez Marię.

Każda dodana para dodatkowo zawęża filtr. Ponieważ AVERAGEIFS korzysta z logiki AND, do średniej trafiają tylko wiersze spełniające wszystkie trzy kryteria.

Należy zachować właściwą kolejność argumentów w obu funkcjach:

• AVERAGEIF: range, criteria, [average_range] — najpierw zakres testowany, a opcjonalny average_range na końcu.
• AVERAGEIFS: average_range, range1, crit1, ... — average_range zawsze występuje jako pierwszy.

Podobnie jak w przypadku pozostałych funkcji, wybieranie domyślnie wersji mnogiej ...IFS zapewnia spójny schemat dla funkcji SUM, COUNT i AVERAGE.

AVERAGEIFS umożliwia tworzenie wielu zestawień porównawczych. Należy wypisać nazwy regionów w kolumnie F, a następnie obliczyć średnią sprzedaży dla każdego regionu za pomocą jednej formuły, którą można skopiować, blokując kolumnę kryterium.

Przy użyciu $C:$C jako average_range, $A:$A jako criteria_range oraz $F2 jako względnego odwołania do komórki regionu skopiowanie formuły w dół natychmiast wyznaczy jedną średnią dla każdego regionu.

Przypomnijmy sobie, co sprawia, że AVERAGEIFS działa inaczej niż funkcje pokrewne.

Można teraz uśredniać wartości na podstawie wielu kryteriów:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range występuje jako pierwszy.
• Brak dopasowań powoduje zwrócenie błędu #DIV/0! — należy obsłużyć go za pomocą IFERROR.
• Puste komórki są pomijane, ale zera są uwzględniane; w razie potrzeby można je wykluczyć za pomocą "<>0".
• Do tworzenia dynamicznych limitów liczbowych służy ">"&F1.

Następnie: obsługa przedziałów dat w tych funkcjach kryterialnych.