Czym jest średnia ważona i jak obliczyć ją w Excel

Czym jest średnia ważona?

Zanim zaczniemy obliczać średnią ważoną w Excel przypomnijmy, czym jest średnia ważona. Najłatwiej to zrobić na przykładzie porównując ją do średniej arytmetycznej.

• Średnia arytmetyczna – liczby wchodzące w skład średniej arytmetycznej mają taki sam wpływ na obliczanie średniej
• Średnia ważona– liczby wchodzące w skład średniej arytmetycznej mogą mieć różny wpływ na liczenie średniej

Rozważny na szybko następujący przypadek: Kupiłeś:

• na jednej stacji paliw 50 litrów benzyny po cenie 4,5zł/litr
• na drugiej stacji paliw 30 litrów benzyny po cenie 5zł/litr
• na trzeciej stacji paliw 20 litrów benzyny po cenie 4,75zł/litr

Możesz oczywyście uznać, że średnia cena zakupionego paliwa to 4,75. Jednak, gdy przyjrzysz się ilości swoich zakupów zauważysz, że powinienneś skorygować te obliczenie właśnie o zakupione ilości. By to zrobić sprawdź poniższy przykład.

Przykład: Obliczanie średniej ważonej

Z powyższego przykładu wynika, że łącznie kupiłeś 100 litrów paliwa w różnych cenach za litr. Średnią cenę wg. średniej arytmetycznej obliczysz bardzo prosto:

= (4,5 + 5 + 4,75 ) / 3 = 4,75

Średnią cenę wg. średniej ważonej obliczysz uwzględniając zakupione ilości paliwa w litrach, a więc kolejno 50, 30, 20. Suma litrów paliwa wynosi 100. Wagi w takim razie będą wyglądać nastpująco:

• 50/100 = 0,5
• 30/100 = 0,3
• 20/100 = 0,2

Następnie, by obliczyć średnią ważoną mnożysz kolejne ceny z ich wagami, a całość dodajesz:

(4,5 * 0,5) + (5 * 0,3) + (4,75 * 0,2) = 4,7 (tak, to średnia ważona cena zakupionego paliwa 🙂

Przykład: Średnia ważona w Excel – sposób liczenia

Jak w takim razie obliczyć średnią ważoną w Excel? Excel nawet w wersji 2019 nie posiada specjalnej funkcji do obliczania śreniej ważonej. Napiszmy więc prostą formułę, za pomocą której ją obliczymy.

Wprowadźmy liczby z poprzedniego przykładu do arkusza. Następnie dodajmy kolumnę o nazwie „Waga” i w niej obliczmy wagi. Wzór na wagi to w naszym przypadku ilość dla danego produktu dzielona przez sumę wszystkich ilości, a więc np 50 litrów / 100 litrów = 0,5. Wynoszą one kolejno: 0,5 ; 0,3 ; 0,2.

Następnie w odrębnej komórce mnożymy ceny i wagi. Całość na koniec dodajemy i otrzymujemy w Excel średnią ważoną.

To oczywiście sposób rozbity na najprostsze elementy. Jeśli chcemy, możemy wszystko obliczyć za pomocą jednej formuły. Plik Excel do pobrania zamieszczam na końcu publikacji.

Średnia ważona Excel – wyznaczanie wag

Wyżej opisany przykład był bardzo uproszczony. Co w przypadku, gdy mamy do czynienia z bardziej skomplikowanymi obliczniami? Co zrobić, gdy wagi nie sumują się do 100 lub do jedności? Jak w takim wypadku będzie wyglądało ustalanie wag do średniej?

Rozważmy następujący przypadek: Wykonałeś kilkanaście przejazdów samochodem. Samochód podczas każdej trasy wykonał inną trasę. Inne też było spalanie. Twoim celem jest zmierzenie średniego spalania samochodu.

Dla celów wykonania ćwiczenia, skopiuj do Excel poniższą tabelę:

Jedyne, co powinieneś zrobić, by obliczyć wagi to zsumować je ze sobą, a następnie podzielić wartości (np ilości) przez tę sumę. Następnie, tak jak w poprzednim przykładzie mnożysz spalanie i wagi. Całosć dodajesz uzyskując wartość średniej ważonej.

Plik Excel z przykładem zamieszczony jest na końcu artykułu.

Średnia ważona w tabeli przestawnej Excel

Czy możliwe jest obliczenie średniej ważonej w tabeli przestawnej? Jak najbardziej. Należy do tego celu wykorzystać pola obliczeniowe.

Plik z przykładem do pobrania:

Ten i podobne przykłady obliczeń w Excel zamieszczam w tym dziale: Excel. Zachęcam także do zapoznania się z moim kursem VBA w Excel: VBA