Średnia ważona w Excelu: wartości, wagi i średnie grup

Dwie grupy klientów oceniły usługę. Pierwsza dała średnio 5, druga 3. Czy wspólna średnia wynosi 4? Zanim odpowiem, pytam o liczebność grup. Dwie opinie i dwieście opinii to zupełnie inne udziały w wyniku.

Średnia ważona pozwala zapisać tę różnicę. Pokażę ją na małych danych syntetycznych, które można policzyć na kartce, a potem przeniosę rachunek do Excela. Najwięcej uwagi poświęcę wyborowi wag. Sama formuła jest krótka. Znacznie łatwiej pomylić się przy ustalaniu, co właściwie ma reprezentować jej wynik.

Co oznacza waga przy średniej

W zwykłej średniej arytmetycznej każda obserwacja ma ten sam udział. Dodaję wartości i dzielę przez liczbę obserwacji. W średniej ważonej przypisuję wartościom wagi: mnożę każdą wartość przez jej wagę, dodaję iloczyny i dzielę przez sumę wag.

W tym poradniku używam wag nieujemnych, z co najmniej jedną dodatnią. Waga zero wyłącza daną wartość z udziału w średniej. Waga dwa oznacza dwukrotnie większy udział niż waga jeden. Nie musi oznaczać dwóch osób: znaczenie zależy od tego, co analizuję.

Przy średniej cenie zakupu wagą może być liczba kupionych sztuk. Przy łączeniu średnich ocen grup wagą jest liczba ocen w grupie. W szkolnej ocenie ważonej nauczyciel może umownie ustalić, że sprawdzian ma większą wagę niż zadanie domowe. Każdy z tych rachunków ma tę samą konstrukcję, ale odpowiada na inne pytanie.

Zapisuję definicję wprost w raporcie. „Średnia ocena ważona liczbą odpowiedzi” mówi czytelnikowi więcej niż samo „średnia”. Szczególnie gdy obok znajduje się drugi wskaźnik, oparty na wartości sprzedaży lub strategicznym znaczeniu klienta.

Pięć ocen, jeden rachunek

Zostawmy na chwilę firmę i weźmy syntetyczne oceny. Sprawdziany dostają wagę 2, zadania wagę 1. To umowne założenie przykładu, bez sugestii, że tak powinien oceniać każdy nauczyciel.

Licznik to 8 + 6 + 5 + 5 + 4, czyli 28. Mianownik to 2 + 2 + 1 + 1 + 1, czyli 7. Średnia ważona wynosi 28 / 7 = 4.

Zwykła średnia z pięciu ocen wynosi 21 / 5 = 4,2. Niższy wynik ważony bierze się stąd, że oceny 4 i 3 otrzymały większy udział. Nie jest to dodatkowa kara wymyślona przez Excela. Tak działa przyjęty wcześniej system wag.

Zwróć uwagę na mianownik. Dzielę przez 7, a nie przez pięć wierszy. Gdybym pomnożył oceny przez wagi, a potem podzielił przez liczbę ocen, dostałbym inną wielkość. To jeden z częstszych błędów przy ręcznym składaniu tego wzoru.

Formuła średniej ważonej w Excelu

Wpisz nagłówki „Ocena” i „Waga” do A1:B1, a pięć par z tabeli do A2:B6. Obie kolumny mają zawierać liczby. W polskim Excelu używam:

=SUMA.ILOCZYNÓW(A2:A6;B2:B6)/SUMA(B2:B6)

Angielskie odpowiedniki to SUMPRODUCT i SUM. Separatory argumentów zależą od ustawień regionalnych. Tutaj polskie formuły zapisuję ze średnikami, a angielskie z przecinkami.

Microsoft pokazuje tę konstrukcję w poradniku obliczania średniej ważonej. Najpierw powstaje suma iloczynów odpowiadających sobie wartości i wag, następnie dzielę ją przez sumę wag. Wynik naszego arkusza to 4.

Gdy buduję taki arkusz pierwszy raz, zostawiam obok kolumnę pomocniczą. W C2 wpisuję =A2*B2 i kopiuję do C6. Widzę wtedy 8, 6, 5, 5 i 4. Formuła jest mniej tajemnicza, a błędnie wpisaną wagę łatwiej zauważyć. Dopiero gdy rozumiem rachunek, skracam go do jednej komórki.

Zakresy muszą mieć te same wymiary i odpowiadać sobie wierszami. Dokumentacja SUMPRODUCT opisuje zarówno wymóg zgodnych wymiarów, jak i traktowanie nieliczbowych elementów tablic jak zer. Dlatego przed liczeniem sprawdzam import. Tekst wyglądający jak liczba może dać wynik, który wygląda wiarygodnie, choć część danych nie weszła do licznika tak, jak planowałem.

Kiedy wagi mogą być procentami

W poprzednim przykładzie suma wag wynosiła 7. Nie musi wynosić ani 1, ani 100. Dzielenie przez sumę wag normalizuje ich udział.

Mogę zmienić wagi 2, 2, 1, 1, 1 na 20, 20, 10, 10, 10. Każda rośnie dziesięciokrotnie. Licznik wzrośnie tak samo jak mianownik, więc wynik pozostanie równy 4. Zmieniła się skala wag, a ich proporcje zostały te same.

Jeżeli ustalone wagi są udziałami procentowymi i sumują się do 100%, suma ważonych wartości już daje średnią. Mimo to często zostawiam dzielenie przez sumę wag: czytelnik od razu widzi pełną definicję, a drobna zmiana skali nie wymaga przebudowy formuły.

Nie oznacza to, że brakujące 10% można zignorować. Jeśli model zakłada konkretne udziały, a ich suma wynosi 90%, najpierw wyjaśniam brak. Automatyczne przeskalowanie może zamaskować pominiętą kategorię. Poprawny rachunek na niepełnych danych wciąż opisuje niepełne dane.

Dlaczego średnia ze średnich potrafi oszukać

Wróćmy do dwóch grup. W danych syntetycznych grupa A ma 2 oceny i średnią 5. Grupa B ma 8 ocen i średnią 3. Zwykła średnia z dwóch średnich wynosi 4, ale odpowiada na pytanie o dwie grupy z równym udziałem każdej z nich.

Jeśli chcę średnią ze wszystkich dziesięciu ocen, liczę inaczej. Dwie oceny ze średnią 5 mają łączną sumę 10. Osiem ocen ze średnią 3 ma sumę 24. Razem daje to 34, a 34 / 10 = 3,4.

W Excelu mogę użyć tego samego wzoru, podstawiając do pierwszego zakresu średnie grup, a do drugiego ich liczebności. Warunek jest istotny: grupy powinny obejmować odpowiednie, rozłączne obserwacje tego samego rodzaju. Podwójnie policzona osoba da podwójny udział niezależnie od poprawności wzoru.

Jeżeli mam tylko zaokrąglone średnie grup, odzyskuję przybliżony wynik. Zapis 3,4 mógł wcześniej powstać z wielu różnych sum. Do dokładnego raportu wolę zachować sumę ocen i ich liczbę. Wtedy nie próbuję odtwarzać szczegółów z już zaokrąglonego wskaźnika.

Wybór wagi zmienia pytanie biznesowe

Załóżmy, że rozdzielam klientów na standardowych i VIP. Mogę uznać, że ocena VIP ma wagę 2. Otrzymam wskaźnik ważony według przyjętej polityki. Nie powinienem opisywać go potem jako przeciętnej opinii wszystkich klientów z równym udziałem każdej osoby.

Jeśli chcę zbadać satysfakcję respondentów, naturalnym punktem wyjścia jest jedna odpowiedź z taką samą wagą jak druga. Jeśli chcę nadać priorytet określonej grupie, uzasadniam to osobno. Sama wyższa płatność klienta nie ustanawia uniwersalnej reguły statystycznej.

Podobnie przy cenach. Kupuję 2 sztuki po 10 oraz 8 sztuk po 5. Łączny koszt wynosi 20 + 40 = 60, a liczba sztuk 10. Średnia cena sztuki to 6. Zwykła średnia z cen 10 i 5 wyniosłaby 7,5 i opisywała dwa zakupy z równym udziałem, bez uwzględnienia ilości.

Sprawdzam też jednostki. Jeżeli jeden zakup jest podany w sztukach, a drugi w opakowaniach po dziesięć sztuk, najpierw sprowadzam je do wspólnej jednostki. Excel pomnoży obie liczby bez protestu. O zgodność znaczenia muszę zadbać ja.

Braki danych i zerowy mianownik

Wartość zero i brak wartości mają różne znaczenia. Ocena 0 może być poprawną odpowiedzią na przyjętej skali. Pusta komórka może oznaczać brak odpowiedzi. Zanim przypiszę im ten sam udział w średniej, ustalam zasady zbioru.

Szczególnie zdradliwy jest wiersz z pustą oceną i dodatnią wagą. Jeśli licznik nie dostanie wkładu tej oceny, a mianownik nadal uwzględni wagę, średnia się obniży. Przyjmuję więc prostą zasadę przygotowania danych: do obu zakresów wpuszczam ten sam zestaw kompletnych, zatwierdzonych par.

Jeśli suma wag wynosi zero, średnia w tej konstrukcji jest nieokreślona. Dzielenie przez zero nie jest dowodem, że średnia wynosi zero. W raporcie opisuję brak podstaw do obliczenia wyniku i sprawdzam, skąd wzięły się zerowe wagi.

Nie zakrywam od razu wszystkich problemów funkcją zwracającą zero przy błędzie. Taki zabieg potrafi zamienić zepsuty import w elegancki wskaźnik. Najpierw ustalam przyczynę, potem sposób prezentacji braku wyniku. Więcej o przygotowaniu danych pokazuję w poradniku czyszczenia danych.

Jak sprawdzić sens otrzymanej średniej

Przy nieujemnych wagach i dodatniej ich sumie średnia powinna mieścić się między najmniejszą a największą uwzględnioną wartością. Wynik 8 dla ocen od 3 do 5 każe mi wrócić do mianownika i zakresów. To szybka kontrola sensu, nie pełny audyt danych.

Sprawdzam również kierunek zmiany. Jeżeli zwiększam wyłącznie udział niskiej oceny, wspólna średnia powinna przesuwać się w jej stronę. Gdy rośnie, prawdopodobnie pomyliłem kolumny, zakres lub definicję wagi.

W danych grupowych patrzę na liczebności obok średnich. Wynik 5 z jednej opinii ma inną podstawę niż wynik 4,8 z dużej liczby odpowiedzi. Średnia ważona potrafi połączyć grupy, lecz sama nie mówi o rozrzucie ocen, reprezentatywności próby ani niepewności oszacowania. Przy nierównych lub nietypowych rozkładach przydaje się też mediana.

Małe ćwiczenie na koniec

Weź dwie syntetyczne grupy: średnia 2 z jedną odpowiedzią i średnia 4 z trzema odpowiedziami. Najpierw odtwórz sumy: 2 oraz 12. Po połączeniu masz 14 punktów z czterech odpowiedzi, więc średnia wynosi 3,5.

Potem zmień liczebność pierwszej grupy z 1 na 3, zostawiając obie średnie bez zmian. Obie grupy mają teraz równy udział. Wynik powinien znaleźć się dokładnie w połowie między 2 i 4, czyli wynieść 3.

Na własnym raporcie zapisz obok formuły trzy informacje: co jest wartością, co jest wagą i jakie wiersze zostały wyłączone. Jeśli druga osoba rozumie te trzy zdania, ma szansę poprawnie odczytać również samą średnią.