Jak obliczyć średnią ważoną w Excelu?

Średnia ważona to średnia, która uwzględnia wagę lub ważność przypisaną do każdej wartości. W tym artykule dowiesz się, w jaki sposób obliczyć w Excelu średnią ważoną przy użyciu formuł SUMA.ILOCZYNÓW oraz SUMA.

Funkcja SUMA.ILOCZYNÓW

W jednym z poprzednich artykułów omówiłam podstawowe sposoby obliczania średniej arytmetycznej, jednak Excel niestety nie udostępnia specjalnej funkcji służącej do kalkulacji średniej ważonej. W takiej sytuacji przydatne w obliczeniach są pozostałe funkcje, z których najważniejszą jest SUMA.ILOCZYNÓW.

Funkcja SUMA.ILOCZYNÓW służy do obliczenia iloczynu wartości zapisanych w określonych zakresach danych, np. kolumnach. Składnia funkcji wygląda następująco:

=SUMA.ILOCZYNÓW(tablica1; [tablica2]; [tablica3];…)

Tablica 1,2,3…. – argumenty funkcji, które określają tablice/zakresy danych, które zostaną pomnożone, a następnie zsumowane.

Jak działa funkcja SUMA.ILOCZYNÓW?

średnia ważona

Na powyższym przykładzie argumentami funkcji zostały zakresy danych B2 do B7 oraz C2 do C7. Funkcja SUMA.ILOCZYNÓW obliczyła iloczyny wartości z komórek B2-B7 przez wartości z komórek C2-C7, a następnie zsumowała otrzymane wyniki (B2*C2 + B3*C3 + B4*C4, itd.).

Ważna informacja:
Argumenty tablicowe używane w funkcji SUMA.ILOCZYNÓW muszą mieć takie same wymiary. W przypadku, gdy jedna z użytych tablic ma inne wymiary niż pozostałe, funkcja zwróci błąd #ARG!, oznaczający problem z zapisem formuły:

błąd #ARG!

Przykłady obliczania średniej ważonej

Poniżej przedstawię dwa praktyczne przykłady, w których można użyć funkcji SUMA.ILOCZYNÓW i SUMA do obliczenia średniej ważonej w Excelu.

Przykład 1 – obliczanie średniej ważonej w przypadku wag sumujących się do 100%

Pewien uczeń w trakcie semestru otrzymał cząstkowe oceny za poszczególne zadania, do których jest przypisana określona waga procentowa (suma wag wynosi 100%):

średnia ważona

Naszym zadaniem jest obliczenie końcowej oceny, ale z powodu różnej wagi dla poszczególnych ocen nie możemy zastosować zwykłej średniej arytmetycznej. Przy użyciu funkcji SUMA.ILOCZYNÓW możemy obliczyć sumę iloczynów poszczególnych ocen i ich wag, a zatem otrzymamy średnią ważoną:

średnia ważona

Każda ocena wpisana do kolumny B została pomnożona przez odpowiednią wagę z kolumny C, a następnie iloczyny zostały sumowane. Otrzymany wynik (3,6) jest średnią ważoną, a zatem ocena ucznia powinna wynosić 4 (po zaokrągleniu do liczby całkowitej).

Poniższa ilustracja pokazuje, że identyczny wynik mógłby także zostać uzyskany po ręcznym obliczeniu i zsumowaniu iloczynów każdej oceny i odpowiedniej wagi (wyniki w kolumnie D, użyte zostały formuły zapisane w kolumnie E):

średnia ważona

Przykład 2 – obliczanie średniej ważonej dla wag wyrażonych liczbowo

Wyżej opisany przykład był dość uproszczony, gdyż wagi były wyrażone procentowo i ich suma wynosiła 100%. Zatem, co powinniśmy zrobić w przypadku, gdy przypisane wagi nie sumują się do 100% lub do jedności? Aby obliczyć średnią ważoną w takich sytuacjach należy użyć kombinacji dwóch funkcji: SUMA.ILOCZYNÓW oraz SUMA.

W poniższym przykładzie również chcemy obliczyć końcową ocenę dla ucznia, jednak wagi zostały zapisane w postaci liczbowej i ich suma wynosi 15:

średnia ważona

W takiej sytuacji należy w pierwszej kolejności zsumować iloczyny ocen i przypisanych ich wag za pomocą SUMA.ILOCZYNÓW, a następnie podzielić przez sumę wag policzoną funkcją SUMA:

=SUMA.ILOCZYNÓW(liczby(oceny); wagi)/SUMA(wagi)

Zatem, w danym przykładzie poprawna formuła powinna wyglądać następująco:

=SUMA.ILOCZYNÓW(B2:B7; C2:C7)/SUMA(B2:B7)

średnia ważona

Otrzymana w ten sposób średnia ważona wskazuje, że końcowa ocena ucznia powinna wynosić 4 (3,5667 zaokrąglone do liczby całkowitej).

W przypadku obliczania średniej dla kilku uczniów można przeciągnąć formułę w boki, jednak należy pamiętać o odpowiednim adresowaniu komórek z użyciem $:

średnia ważona

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.