ŚREDNIA w Excelu: liczby, braki i średnia ważona

Zanim wpiszę ŚREDNIA, pytam, co ma reprezentować jedna obserwacja. Dzień, zamówienie, osoba, a może gotowe podsumowanie całego zespołu? Sama funkcja nie zna tej odpowiedzi. Obliczy wynik zgodnie z zawartością zakresu, nawet jeśli połączyłeś dane o różnym znaczeniu.

Polska nazwa to ŚREDNIA, angielska AVERAGE. Poniższe dane są syntetyczne, a przykłady odnoszą się do zakresów komórek w Excelu Microsoft 365, 2024, 2021, 2019 i 2016. Używam średników w zapisach PL oraz przecinków w EN; rzeczywisty separator zależy od ustawień regionalnych.

Co jest mianownikiem średniej

W A1 wpisz nagłówek wynik. A2 ma zawierać 10, A3 liczbę 20, A4 pozostaw naprawdę pustą, w A5 wpisz tekst oczekuje, a w A6 liczbę 0. Obliczenie umieść poza źródłem, na przykład w C2.

=ŚREDNIA(A2:A6)

W zakresie są trzy liczby: 10, 20 i 0. Suma wynosi 30, więc średnia to 30 podzielone przez 3, czyli 10. Dzielnikiem nie jest liczba wszystkich komórek ani wszystkich komórek niepustych. Tekst w A5 pozostaje tekstem, mimo że zajmuje miejsce w arkuszu.

Dokumentacja Microsoft rozróżnia liczby od tekstu, wartości logicznych i pustych komórek w odwołaniach. To hasło skupia się na zakresach. Nie przenoś bez sprawdzenia reguł odwołań na każdy możliwy argument wpisany bezpośrednio w formule.

Brak i zero zmieniają pytanie

Jeżeli pustą A4 zastąpisz liczbą 0, suma nadal wynosi 30, ale masz już cztery liczby. Wynik spada do 7,5. Excel działa zgodnie z regułą. To autor danych zdecydował, że brak obserwacji zamieni się w obserwację o wartości zero.

W raporcie dziennym brak może oznaczać niedostarczony plik, a zero poprawnie zmierzony dzień bez zdarzeń. Ja nie łączę tych stanów tylko dla ujednolicenia kolumny. Obok średniej pokazuję liczbę dostępnych obserwacji i liczbę braków. Wtedy odbiorca widzi, czy porównuje podobnie kompletne okresy.

Liczba zapisana jako tekst w zakresie również może zostać pominięta. Zmiana wyglądu komórki nie powinna zastępować kontroli typu danych. Jeżeli średnia z importu wygląda podejrzanie, sprawdź kilka znanych wartości i sposób importowania, zamiast od razu poprawiać wynik ręcznie.

Warunek dla liczb i dla innej kolumny

Dla pierwotnego zakresu z liczbami 10, 20 i 0 formuła poniżej wybierze wyłącznie wartości dodatnie. Średnia 10 i 20 wynosi 15.

=ŚREDNIA.JEŻELI(A2:A6;">0")

Warunek ">0" usuwa również liczby ujemne. Jeśli potrzebujesz wszystkich liczb różnych od zera, kryterium "<>0" odpowiada innemu pytaniu. Przy wartościach -10, 0 i 20 dodatnie dają średnią 20, a niezerowe 5. Nie opisuję więc filtra dodatnich jako uniwersalnego „pomijania zer”.

Pełna składnia to ŚREDNIA.JEŻELI(zakres;kryteria;[średnia_zakres]). W osobnym arkuszu umieść w A2:A4 statusy gotowe, gotowe, oczekuje, a w B2:B4 liczby 10, 20, 90. Wtedy:

=ŚREDNIA.JEŻELI(A2:A4;"gotowe";B2:B4)

Wynik to 15. Warunek dotyczy statusu, a uśredniane są liczby z drugiej kolumny. Rolę opcjonalnego trzeciego argumentu opisuje Microsoft. Przy braku wartości spełniających kryterium oczekuj błędu dzielenia przez zero, zamiast średniej równej zero.

Średnia ważona z poprawną nazwą funkcji

W nowym arkuszu wpisz nagłówki wartosc w A1 i waga w B1. W A2:A4 umieść 10, 20, 30, a w B2:B4 wagi 1, 2, 3. W tym ćwiczeniu wagi są dodatnimi liczbami określającymi względny udział obserwacji.

=SUMA.ILOCZYNÓW(A2:A4;B2:B4)/SUMA(B2:B4)

Polska nazwa funkcji to SUMA.ILOCZYNÓW, a angielska SUMPRODUCT. Nazwa SUMA.PRODUKT z dawnej wersji tekstu była błędna. W liczniku masz 10 razy 1, 20 razy 2 i 30 razy 3, razem 140. Suma wag to 6, więc wynik wynosi około 23,33.

Zakresy wartości i wag muszą odpowiadać sobie pozycjami i mieć zgodne wymiary. Sprawdzam też kompletność obu kolumn oraz dodatnią sumę wag. Brak wartości z zachowaną wagą może zmienić znaczenie ilorazu. Nie traktuj pomijania tekstu przez funkcje jako automatycznej procedury czyszczenia danych.

Błędy, filtry i ukryte wiersze

Jeśli w zakresie nie ma liczb, ŚREDNIA zgłosi #DZIEL/0!, angielskie #DIV/0!. Błędy zapisane w komórkach źródłowych także wymagają wyjaśnienia. Samo JEŻELI.BŁĄD potrafi ukryć różne przyczyny pod jednym komunikatem, więc najpierw rozpoznaj, co właściwie poszło nie tak.

Zwykła ŚREDNIA odwołuje się do podanego zakresu, a ukrycie wiersza nie tworzy nowej definicji tego zakresu. Jeśli chcesz średnią widocznych wierszy, zapoznaj się z SUMY.CZĘŚCIOWE / SUBTOTAL i różnicą między wierszami filtrowanymi a ręcznie ukrytymi. To osobna decyzja raportowa.

Sprawdź znaczenie wyniku

Przećwicz zamianę pustej komórki na zero i wyjaśnij zmianę mianownika. Następnie porównaj zwykłą średnią 10, 20, 30 ze średnią ważoną powyżej. W obu przypadkach potrafisz policzyć wynik bez dużego zbioru i zauważyć, które obserwacje mają wpływ na liczbę.

Jeżeli dane są już podsumowaniami grup, nie uśredniaj ich automatycznie z jednakową wagą. Grupa dwóch obserwacji i grupa dwustu nie muszą mieć takiego samego znaczenia. Najpierw nazwij jednostkę analizy. Do dalszej pracy nad liczeniem warunkowym przyda się SUMA.WARUNKÓW.