Masz w arkuszu kilka ocen, kwot albo wyników i potrzebujesz jednej liczby, która je podsumuje? W Excelu najczęściej wystarczy funkcja ŚREDNIA, ale wynik zależy od tego, czy puste komórki, zera i ukryte wiersze mają wejść do obliczeń.
Jedna funkcja wystarczy, jeśli dobrze dobierzesz zakres
- Standardowa średnia: wpisz
=ŚREDNIA(B2:B20). - Puste komórki: funkcja je pomija, ale uwzględnia komórki z wartością zero.
- Warunek: użyj
ŚREDNIA.JEŻELIdla jednego kryterium lubŚREDNIA.WARUNKÓWdla kilku. - Filtrowana lista: skorzystaj z
SUMY.CZĘŚCIOWE, aby uwzględnić tylko widoczne wiersze. - Różne liczby obserwacji: oblicz średnią ważoną, zamiast uśredniać same średnie.
Wpisz ŚREDNIA i wskaż komórki z liczbami
Najprostszy wariant działa w pustej komórce, w której ma pojawić się wynik. Wpisz =ŚREDNIA(B2:B20) i naciśnij Enter. Zakres w formule zastąp adresem komórek z własnymi danymi.
Możesz też wskazać pojedyncze, nieprzylegające komórki, na przykład =ŚREDNIA(B2;B5;B9). W polskiej wersji Excela argumenty rozdziela zwykle średnik; nazwy funkcji i separator mogą się różnić w zależności od ustawień językowych. ŚREDNIA bierze pod uwagę wartości liczbowe, a tekst i puste komórki w zakresie pomija, zgodnie z dokumentacją funkcji ŚREDNIA w Microsoft Support.
Przy ocenie programu, gdzie wyniki w zakresie B2:B6 wynoszą 3, 4, 4, 5 i 5, formuła zwróci 4,2. Jeśli w zakresie znajduje się również nagłówek tekstowy, nie wpłynie na wynik. Liczbowy nagłówek lub liczba wpisana omyłkowo do zakresu już może go zmienić, więc zaznaczaj komórki z danymi, a nie całą kolumnę bez namysłu.
Zero może obniżyć średnią, choć puste pole jej nie zmieni
Tu łatwo o wynik, który wygląda poprawnie, ale odpowiada na inne pytanie. W zakresie z liczbami 8, 0 i 10 Excel uwzględni zero, więc średnia wyniesie 6; jeśli środkowa komórka będzie pusta, średnia z dwóch pozostałych liczb wyniesie 9.
Rozstrzygnij więc, co oznacza zero w Twoich danych. Jeśli oznacza rzeczywisty brak sprzedaży lub wynik równy zero, pozostaw je w obliczeniu. Jeśli zero jest tylko technicznym wpisem oznaczającym brak pomiaru, zwykła funkcja ŚREDNIA zaniży rezultat i trzeba wykluczyć te wartości.
| Sytuacja w arkuszu | Skutek dla wyniku | Co zrobić |
|---|---|---|
| Komórka jest pusta | Nie jest liczona jako zero | Użyj ŚREDNIA, jeśli brak wpisu ma pozostać pominięty |
| Komórka zawiera 0 | Zero wchodzi do średniej i może ją obniżyć | Pozostaw zero, jeśli to faktyczny wynik; w przeciwnym razie zastosuj warunek wykluczający zera |
| Liczby są zapisane jako tekst | Funkcja może je pominąć | Sprawdź format komórek i w razie potrzeby zamień tekst na liczby |
Przy wielu wartościach możesz użyć warunku, który pominie zera: =ŚREDNIA.JEŻELI(B2:B20;">0"). To rozwiązanie pasuje do dodatnich wyników, ale odrzuci też liczby ujemne, więc przy danych, w których mogą występować, dobierz kryterium do znaczenia pomiaru.
Średnia dla wybranego kryterium wymaga innej funkcji
Gdy interesują Cię na przykład wyłącznie zgłoszenia z jednego statusu, nie musisz ręcznie filtrować zakresu ani kopiować wyników do nowego arkusza. Funkcja ŚREDNIA.JEŻELI oblicza średnią dla jednego kryterium, a ŚREDNIA.WARUNKÓW pozwala uwzględnić kilka.
Załóżmy, że w kolumnie A masz status zgłoszenia, a w kolumnie B czas obsługi. Średnią dla statusu „Zamknięte” obliczy formuła =ŚREDNIA.JEŻELI(A2:A100;"Zamknięte";B2:B100). Pierwszy zakres zawiera sprawdzane statusy, a ostatni wartości, które mają zostać uśrednione.
Jeżeli wynik ma dotyczyć zamkniętych zgłoszeń z konkretnego zespołu, zastosuj ŚREDNIA.WARUNKÓW i dodaj zakresy oraz kryteria dla statusu i zespołu. Każdy zakres kryteriów musi odpowiadać położeniem zakresowi uśrednianych wartości, inaczej formuła może nie porównywać tych samych wierszy.
Po filtrze użyj SUMY.CZĘŚCIOWE zamiast zwykłej średniej
Filtrujesz tabelę do jednego miesiąca, a wynik nadal obejmuje wszystkie rekordy? Sama formuła =ŚREDNIA(B2:B100) nie jest najlepszym wyborem, gdy chcesz policzyć wyłącznie widoczne wiersze. Do tego służy SUMY.CZĘŚCIOWE, która pomija wiersze odrzucone przez filtr.
Wpisz =SUMY.CZĘŚCIOWE(1;B2:B100), aby policzyć średnią w widocznych wierszach zakresu. Jeśli ręcznie ukryte wiersze również mają zostać wykluczone, wybierz wariant =SUMY.CZĘŚCIOWE(101;B2:B100). Microsoft wyjaśnia, że argument 1 uwzględnia wiersze ukryte ręcznie, a 101 je pomija; wiersze odfiltrowane są pomijane w obu przypadkach. Szczegóły zawiera opis funkcji SUMY.CZĘŚCIOWE.
Prawdę mówiąc, przy raportach filtrowanych to właśnie wybór funkcji częściej prowadzi do zaskakującego wyniku niż sama formuła średniej. Gdy chcesz policzyć wyłącznie to, co widać po filtrze, wybór pada na SUMY.CZĘŚCIOWE; przy średniej z całej kolumny wystarczy ŚREDNIA.
Średnich z grup nie uśredniaj bez sprawdzenia ich liczebności
Załóżmy, że dwa zespoły mają średni czas obsługi 4,8 i 4,2 minuty. Pierwsza średnia opiera się na 20 zgłoszeniach, druga na 80. Zwykłe uśrednienie tych dwóch wyników da 4,5 minuty, ale taki rezultat traktuje małą i dużą grupę jak równoważne próbki.
Jeśli chcesz poznać średnią dla wszystkich 100 zgłoszeń, uwzględnij liczbę obserwacji. Przy średnich w B2:B3 i liczebnościach w C2:C3 użyj formuły =SUMA.ILOCZYNÓW(B2:B3;C2:C3)/SUMA(C2:C3). Dla podanych danych wynik wynosi 4,32 minuty, bo rezultat jest ważony liczbą zgłoszeń w każdym zespole.
To podejście ma jeden warunek: liczebności muszą odpowiadać średnim, a średnie grupowe muszą być policzone z porównywalnych danych. Jeśli masz dostęp do wszystkich pojedynczych pomiarów, prościej i bezpieczniej obliczyć =ŚREDNIA(zakres_z_wynikami) bezpośrednio na tych obserwacjach. Średnia ważona przydaje się wtedy, gdy dostępne są tylko wyniki zbiorcze.
Dobierz formułę do pytania, na które ma odpowiadać wynik
Jeśli liczysz wszystkie wpisane wyniki, zacznij od ŚREDNIA. Gdy część wierszy ma zostać wybrana według kryterium, użyj funkcji warunkowej; gdy znaczenie mają wyłącznie rekordy widoczne po filtrze, wybierz SUMY.CZĘŚCIOWE.
Przed zaakceptowaniem wyniku sprawdź trzy rzeczy: czy zera oznaczają prawdziwe pomiary, czy wszystkie liczby należą do właściwego zakresu i czy grupy mają podobną liczebność. Uważam, że taki krótki przegląd jest lepszy niż bezrefleksyjne poleganie na jednej formule, bo Excel policzy dokładnie to, co wskaże zakres, nawet gdy zakres nie pasuje do pytania.
