Średnia ważona w Excelu: formuła z SUMA.ILOCZYNÓW

=SUMA.ILOCZYNÓW(B2:B5;C2:C5)/SUMA(C2:C5) (po angielsku =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)) oblicza średnią ważoną: każdy wynik z B jest mnożony przez swoją wagę z C, iloczyny są dodawane, a suma jest dzielona przez sumę wag. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.ILOCZYNÓW(B2:B5;C2:C5)/SUMA(C2:C5)

Ocena ważona wynosi 81,2, a zwykła ŚREDNIA (AVERAGE) daje 80,75, bo traktuje prace domowe, warte 20%, tak, jakby liczyły się tyle samo co egzamin końcowy, wart 30%. Zmień wynik egzaminu końcowego, a ocena ważona zmieni się bardziej niż przy takiej samej zmianie wyniku prac domowych.

Excel nie ma funkcji ŚREDNIA.WAŻONA, więc standardową formułą jest SUMA.ILOCZYNÓW podzielona przez SUMA. Arkusze Google mają AVERAGE.WEIGHTED(B2:B5,C2:C5).

Jak działa formuła średniej ważonej

SUMA.ILOCZYNÓW (SUMPRODUCT) mnoży dwa zakresy wiersz po wierszu i dodaje wyniki. Rozpisana w kolumnie pomocniczej to kolumna iloczynów i ich SUMA:

FormułyResetuj

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA(D2:D5)

Każda część wnosi swój wynik razy swoją wagę: 85 × 20% to 17,0, 78 × 30% to 23,4 i tak dalej. Razem dają 81,2. Wagi sumują się do 100%, więc dzielenie przez C6 niczego tu nie zmienia, ale to ono utrzymuje poprawność formuły, gdy wagi się do 100% nie sumują.

Wagi, które nie sumują się do 100%

Wagi nie muszą być procentami. Średnia ocen (GPA) jest ważona liczbą punktów za przedmiot, a średnia cena ilością. Dzielenie przez SUMA wag działa przy każdej sumie.

FormułyResetuj

F2fx

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.ILOCZYNÓW(B2:B6;C2:C6)/SUMA(C2:C6)

Bez dzielenia formuła zwróciłaby sumę ocen razy punkty, tutaj 49,2, a nie średnią. Z dzieleniem F2 pokazuje średnią ważoną 14 punktami. Przedmioty za cztery punkty przyciągają średnią do swoich ocen, a laboratorium za jeden punkt prawie jej nie zmienia: zmień B6 na 2 i zobacz, jak mało zmienia się F2 w porównaniu z F3.

Jeśli wagi to procenty, które sumują się dokładnie do 100%, sama =SUMA.ILOCZYNÓW(B2:B5;C2:C5) daje ten sam wynik. Mimo to zostaw /SUMA(...): w dniu, w którym ktoś zmieni wagę i suma wyniesie 105%, formuła bez dzielenia będzie błędna, a nic w arkuszu tego nie pokaże.

Średnia ważona z warunkiem

Aby ważyć tylko niektóre wiersze, pomnóż przez warunek w SUMA.ILOCZYNÓW, a pasujące wagi zsumuj funkcją SUMA.JEŻELI (SUMIF). Poniżej średnia cena w każdym regionie jest ważona sprzedaną ilością.

FormułyResetuj

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA.ILOCZYNÓW((A2:A7=F2)*C2:C7*D2:D7)/SUMA.JEŻELI(A2:A7;F2;D2:D7)

North sprzedał 100 jabłek, 60 gruszek i 40 śliwek, więc jego średnia cena to $1.45, bliżej ceny jabłek, niż wynikałoby ze zwykłej średniej trzech cen. Warunek (A2:A7=F2) daje 1 w wierszach North i 0 w pozostałych, więc inne wiersze nic nie dodają do licznika, a SUMA.JEŻELI dodaje w mianowniku tylko ilości North.

Ćwiczenie: średnia ważona cena

FormułyResetuj

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: Kupiłeś ten sam towar w pięciu partiach po różnych cenach. Oblicz średnią cenę za sztukę, ważoną ilością w każdej partii. Wpisz formułę w F2.

SprawdźPokaż odpowiedź

Błędy, które dają złą średnią ważoną

• ŚREDNIA z iloczynów. =ŚREDNIA(D2:D5) na kolumnie wynik × waga dzieli przez liczbę wierszy, a nie przez wagi, i daje małą liczbę bez znaczenia. Podziel SUMĘ iloczynów przez SUMĘ wag.
• Dzielenie przez liczbę wierszy zamiast przez wagi. =SUMA.ILOCZYNÓW(B2:B6;C2:C6)/ILE.LICZB(B2:B6) jest poprawne tylko wtedy, gdy każda waga wynosi 1.
• Zakresy, które do siebie nie pasują. =SUMA.ILOCZYNÓW(B2:B6;C3:C7) łączy każdą wartość z wagą z następnego wiersza. Oba zakresy muszą zaczynać się i kończyć w tych samych wierszach; różne rozmiary zwracają #ARG! (po angielsku #VALUE!; tabele pokazują angielskie nazwy błędów).
• Pusta waga. Pusta waga liczy się jako 0, więc ten wiersz zostaje po cichu pominięty. Jeśli brak wagi ma zatrzymać obliczenie, najpierw sprawdź =LICZ.PUSTE(C2:C6).
• Średnia ze średnich. Dwie średnie klas, 70 (10 uczniów) i 90 (30 uczniów), nie dają średnio 80. Zważ je liczebnością klas, a wynik wyniesie 85; strona ŚREDNIA.JEŻELI pokazuje tę samą pułapkę przy warunkach.