Uśrednianie według kryteriów za pomocą AVERAGEIF

Funkcje SUMIF i COUNTIF służą odpowiednio do sumowania i zliczania. Trzecim członkiem tej rodziny jest AVERAGEIF, która oblicza średnią tylko z wartości spełniających określony warunek.

Zamiast uśredniać każdą sprzedaż, można uśrednić wyłącznie sprzedaż w regionie East albo tylko wyniki powyżej 50. Funkcja łączy filtrowanie i obliczanie średniej w jednym przejrzystym kroku.

Funkcja AVERAGEIF ma taki sam trzyczęściowy układ jak SUMIF:

• range komórki do sprawdzenia
• criteria warunek
• average_range komórki, dla których ma zostać obliczona średnia

Funkcja przegląda range, znajduje dopasowania, a następnie oblicza średnią z odpowiadających im komórek w average_range.

Gdy regiony znajdują się w A2:A10, a kwoty w B2:B10, aby obliczyć średnią sprzedaży dla regionu East, należy sprawdzić kolumnę A pod kątem wartości East i obliczyć średnią z odpowiadających jej wartości w kolumnie B.

W praktyce funkcja wykonuje działanie SUMIF podzielone przez COUNTIF, ale AVERAGEIF łączy je w jedną czytelną formułę.

Jeśli kolumna używana do sprawdzania jest jednocześnie kolumną, z której ma zostać obliczona średnia, można pominąć trzeci argument. Aby uśrednić tylko wyniki w B2:B10 większe niż 70:

Kolumna B jest tu jednocześnie sprawdzana i uśredniana. Po pominięciu average_range arkusz używa argumentu range w obu rolach.

Podobnie jak w przypadku innych funkcji IF, warto przechowywać warunek w komórce, aby zapewnić elastyczność. Jeśli D1 zawiera nazwę regionu, wskaż D1 jako kryterium.

Teraz jedna komórka steruje obliczaniem średniej. Zmiana D1 z East na West natychmiast wywołuje ponowne obliczenie, co doskonale sprawdza się w tabeli podsumowującej lub selektorze pulpitu nawigacyjnego.

AVERAGEIF akceptuje operatory ujęte w cudzysłowy, podobnie jak powiązane z nią funkcje. Aby uśrednić tylko sprzedaż o wartości co najmniej 100:

• \">=100\" 100 lub więcej
• \"<50\" poniżej 50
• \"<>0\" z pominięciem zer

Ta ostatnia opcja jest szczególnie przydatna, ponieważ pozwala obliczyć średnią z pominięciem zer, które w przeciwnym razie zaniżałyby wynik.

Aby użyć progu przechowywanego w komórce, połącz operator z tą komórką za pomocą ampersandu. Jeśli D1 zawiera wartość progową, oblicz średnią ze wszystkich wartości większych od niej.

Operator & tworzy tekst warunku na podstawie \">\" i wartości w D1. To ten sam sposób łączenia, który został użyty w funkcjach SUMIF i COUNTIF.

AVERAGEIF ma jedną pułapkę, której nie mają pozostałe funkcje. Jeśli żaden wiersz nie pasuje, nie ma czego dzielić i pojawia się błąd #DIV/0!.

Na przykład obliczenie średniej dla regionu, który nie występuje w danych, zwróci ten błąd. Arkusz informuje w ten sposób, że filtr nie znalazł żadnej komórki, a nie że formuła jest uszkodzona.

Owiń formułę w IFERROR, aby w przypadku braku dopasowań wyświetlić przyjazny komunikat zamiast błędu #DIV/0!.

Teraz dla nieistniejącego regionu wyświetli się tekst Brak danych zamiast niepokojącego błędu. Dzięki temu raporty zachowują estetyczny wygląd nawet wtedy, gdy brakuje niektórych kategorii.

AVERAGEIF uśrednia tylko te spośród dopasowanych komórek, które zawierają liczby. Naprawdę puste komórki w average_range są pomijane, więc nie są traktowane jak zero.

To ważne: brakująca wartość nie obniży średniej w kierunku zera tak jak rzeczywiste 0. Aby wykluczyć także zera, dodaj kryterium \"<>0\" w kolumnie z wartościami.

Utwórz przejrzyste podsumowanie: wpisz każdą kategorię w komórkach D2, D3 i D4, a następnie napisz jedną funkcję AVERAGEIF odwołującą się do komórki z etykietą i skopiuj ją w dół.

Każdy wiersz pokazuje teraz średnią wartość dla własnej kategorii, obok sumy z SUMIF i liczby z COUNTIF z wcześniejszych lekcji. Razem tworzą zwięzły raport w pełni sterowany formułami.

Sprawdź swoją wiedzę na temat działania funkcji AVERAGEIF.

Można już obliczać średnią warunkowo za pomocą funkcji AVERAGEIF. Najważniejsze informacje:

• Kolejność to range, criteria, average_range; ostatni argument można pominąć, aby uśrednić sam zakres testowany.
• Funkcja obsługuje tekst, liczby i operatory, takie jak \">=100\".
• Brak dopasowań powoduje błąd #DIV/0!, dlatego warto zabezpieczyć formułę za pomocą IFERROR.
• Puste komórki są pomijane, a nie traktowane jak zero.

W następnej części zostanie dopasowany tekst częściowy za pomocą symboli wieloznacznych.