Formuła ogólna
=SUMPRODUCT(weights,values)/SUM(weights)
Podsumowanie
Aby obliczyć średnią ważoną, można użyć funkcji SUMPRODUCT razem z funkcją SUMA. W pokazanym przykładzie wzór w G5 skopiowany na dół to:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
gdzie wagi to nazwany zakres I5: K5.
Wyjaśnienie
Średnia ważona, zwana także średnią ważoną, to średnia, w przypadku której niektóre wartości liczą się bardziej niż inne. Innymi słowy, niektóre wartości mają większą „wagę”. Średnią ważoną możemy obliczyć, mnożąc wartości przez odpowiednie wagi, a następnie dzieląc sumę wyników przez sumę wag. W programie Excel można to przedstawić za pomocą poniższej formuły ogólnej, w której wagi i wartości są zakresami komórek:
=SUMPRODUCT(weights,values)/SUM(weights)
W pokazanym arkuszu wyniki dla 3 testów są wyświetlane w kolumnach od C do E, a wagi znajdują się w nazwanych zakresach wag (I5: K5). Formuła w komórce G5 to:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Pracując od wewnątrz, najpierw używamy funkcji SUMPRODUCT, aby pomnożyć wagi przez odpowiadające im wyniki i zsumować wynik:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT najpierw mnoży odpowiadające sobie elementy dwóch tablic, a następnie zwraca sumę iloczynu:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Wynik jest następnie dzielony przez sumę wag:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Ponieważ formuła jest kopiowana do kolumny G, nazwane wagi zakresu I5: K5 nie zmieniają się, ponieważ zachowuje się jak odniesienie bezwzględne. Jednak wyniki w C5: E5, wprowadzone jako odniesienie względne, są aktualizowane w każdym nowym wierszu. Wynik jest średnią ważoną dla każdej nazwy na liście, jak pokazano. Średnia w kolumnie F jest obliczana tylko w celach informacyjnych przy użyciu funkcji ŚREDNIA:
=AVERAGE(C5:E5)
Wagi, które nie sumują się do 1
W tym przykładzie wagi są skonfigurowane tak, aby sumowały się do 1, więc dzielnik zawsze wynosi 1, a wynikiem jest wartość zwrócona przez SUMPRODUCT. Jednak fajną cechą tego wzoru jest to, że wagi nie muszą sumować się do 1.
Na przykład możemy użyć wagi 1 dla pierwszych dwóch testów i wagi 2 dla finału (ponieważ finał jest dwa razy ważniejszy), a średnia ważona będzie taka sama:
W komórce G5 formuła jest rozwiązywana w następujący sposób:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Uwaga: wartości w nawiasach klamrowych () powyżej to zakresy wyrażone w tablicach.
Transpozycja wag
Funkcja SUMPRODUCT wymaga, aby wymiary tablicy były zgodne. Jeśli wymiary są niezgodne, SUMPRODUCT zwróci błąd #ARG. W poniższym przykładzie wagi są takie same jak w oryginalnym przykładzie, ale są wymienione w zakresie pionowym:
Aby obliczyć średnią ważoną za pomocą tego samego wzoru, musimy „odwrócić” wagi do poziomej tablicy za pomocą funkcji TRANSPOSE w następujący sposób:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Po uruchomieniu TRANSPOSE tablica pionowa:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
staje się:
=(0.25,0.25,0.5) // horizontal array
Od tego momentu formuła zachowuje się jak poprzednio.
Czytaj więcej: tablice pionowe i poziome.
