Kiedy obliczamy prostą średnią danego zestawu wartości, zakładamy, że wszystkie wartości mają jednakową wagę lub wagę.
Na przykład, jeśli pojawiasz się na egzaminach, a wszystkie egzaminy mają podobną wagę, średnia twoich wszystkich ocen będzie również średnią ważoną twoich wyników.
Jednak w rzeczywistości tak nie jest.
Niektóre zadania są zawsze ważniejsze niż inne. Niektóre egzaminy są ważniejsze niż inne.
I właśnie tam Średnia ważona pojawia się na obrazie.
Oto podręcznikowa definicja średniej ważonej:
Zobaczmy teraz, jak obliczyć średnią ważoną w programie Excel.
Obliczanie średniej ważonej w Excelu
W tym samouczku dowiesz się, jak obliczyć średnią ważoną w programie Excel:
- Korzystanie z funkcji SUMA PRODUKT.
- Korzystanie z funkcji SUMA.
Więc zacznijmy.
Obliczanie średniej ważonej w programie Excel - funkcja SUMPRODUCT
Mogą istnieć różne scenariusze, w których trzeba obliczyć średnią ważoną. Poniżej znajdują się trzy różne sytuacje, w których można użyć funkcji SUMPRODUCT do obliczenia średniej ważonej w programie Excel
Poniżej znajdują się trzy różne sytuacje, w których można użyć funkcji SUMPRODUCT do obliczenia średniej ważonej w programie Excel
Przykład 1 - Gdy wagi sumują się do 100%
Załóżmy, że masz zestaw danych z ocenami uzyskanymi przez ucznia z różnych egzaminów wraz z wagami w procentach (jak pokazano poniżej):
W powyższych danych uczeń otrzymuje oceny z różnych ocen, ale ostatecznie musi otrzymać ocenę końcową lub ocenę końcową. Nie można tutaj obliczyć prostej średniej, ponieważ ważność różnych ocen jest różna.
Na przykład quiz o wadze 10% ma dwukrotnie większą wagę w porównaniu z zadaniem, ale jedną czwartą wagę w porównaniu z egzaminem.
W takim przypadku możesz użyć funkcji SUMPRODUCT, aby uzyskać średnią ważoną wyniku.
Oto wzór, który da ci średnią ważoną w Excelu:
=PROD.SUMA(B2:B8;C2:C8)
Oto jak działa ta formuła: Funkcja Excel SUMPRODUCT mnoży pierwszy element pierwszej tablicy przez pierwszy element drugiej tablicy. Następnie mnoży drugi element pierwszej tablicy przez drugi element drugiej tablicy. I tak dalej…
I wreszcie dodaje wszystkie te wartości.
Oto ilustracja, aby to wyjaśnić.
Przykład 2 - Kiedy wagi nie sumują się do 100%
W powyższym przypadku wagi zostały przypisane w taki sposób, aby suma sumowała się do 100%. Ale w rzeczywistych scenariuszach nie zawsze tak jest.
Spójrzmy na ten sam przykład z różnymi wagami.
W powyższym przypadku wagi sumują się do 200%.
Jeśli użyję tej samej formuły SUMPRODUCT, otrzymam zły wynik.
W powyższym wyniku podwoiłem wszystkie wagi i zwraca średnią ważoną wartość 153,2. Teraz wiemy, że uczeń nie może uzyskać więcej niż 100 na 100, bez względu na to, jak jest genialny.
Powodem tego jest to, że wagi nie sumują się do 100%.
Oto formuła, która to posortuje:
=PROD.SUMA(B2:B8;C2:C8)/SUMA(C2:C8)
W powyższym wzorze wynik SUMPRODUCT dzieli się przez sumę wszystkich wag. Dlatego bez względu na wszystko, wagi zawsze sumują się do 100%.
Przykład 3 - Kiedy należy obliczyć wagi
W omówionym do tej pory przykładzie wagi zostały określone. Mogą jednak wystąpić przypadki, w których wagi nie są bezpośrednio dostępne i należy najpierw obliczyć wagi, a następnie obliczyć średnią ważoną.
Załóżmy, że sprzedajesz trzy różne rodzaje produktów, jak wspomniano poniżej:
Możesz obliczyć średnią ważoną cenę za produkt za pomocą funkcji SUMA PRODUKT. Oto formuła, której możesz użyć:
=PROD.SUMA(B2:B4,C2:C4)/SUMA(B2:B4)
Podzielenie wyniku SUMPRODUCT przez SUMA ilości zapewnia, że wagi (w tym przypadku ilości) sumują się do 100%.
Obliczanie średniej ważonej w programie Excel - funkcja SUMA
Chociaż funkcja SUMPRODUCT jest najlepszym sposobem obliczania średniej ważonej w programie Excel, możesz również użyć funkcji SUMA.
Aby obliczyć średnią ważoną za pomocą funkcji SUMA, należy pomnożyć każdy element z przypisaną mu wagą w procentach.
Używając tego samego zbioru danych:
Oto formuła, która da Ci właściwy wynik:
=SUMA(B2*C2,B3*C3;B4*C4,B5*C5;B6*C6;B7*C7;B8*C8)
Ta metoda jest w porządku, gdy masz kilka przedmiotów. Ale gdy masz wiele przedmiotów i ciężarów, ta metoda może być kłopotliwa i podatna na błędy. Jest krótszy i lepszy sposób na zrobienie tego za pomocą funkcji SUMA.
Kontynuując ten sam zestaw danych, oto krótka formuła, która poda średnią ważoną za pomocą funkcji SUMA:
=SUMA(B2:B8*C2:C8)
Sztuczka podczas korzystania z tej formuły polega na użyciu Control + Shift + Enter, a nie tylko Enter. Ponieważ funkcja SUMA nie obsługuje tablic, musisz użyć Control + Shift + Enter.
Kiedy naciśniesz Control + Shift + Enter, zobaczysz, że nawiasy klamrowe pojawiają się automatycznie na początku i na końcu formuły (patrz pasek formuły na powyższym obrazku).
Ponownie upewnij się, że wagi sumują się do 100%. Jeśli tak nie jest, wynik należy podzielić przez sumę wag (jak pokazano poniżej na przykładzie produktu):
