När vi beräknar ett enkelt genomsnitt av en given uppsättning värden är antagandet att alla värden har lika stor vikt eller vikt.
Om du till exempel dyker upp för tentor och alla tentor har en liknande vikt, så skulle genomsnittet av dina totala betyg också vara det vägda genomsnittet av dina poäng.
Men i verkligheten är detta knappast fallet.
Vissa uppgifter är alltid viktigare än andra. Vissa tentor är viktigare än andra.
Och det är där Vägt genomsnitt kommer in i bilden.
Här är läroboksdefinitionen av Vägt genomsnitt:
Låt oss nu se hur vi beräknar det vägda genomsnittet i Excel.
Beräkning av vägat genomsnitt i Excel
I den här självstudien lär du dig hur du beräknar det vägda genomsnittet i Excel:
- Använda SUMPRODUCT -funktionen.
- Använda SUM -funktionen.
Så låt oss börja.
Beräkning av vägat genomsnitt i Excel - SUMPRODUCT -funktion
Det kan finnas olika scenarier där du måste beräkna det vägda genomsnittet. Nedan finns tre olika situationer där du kan använda SUMPRODUCT -funktionen för att beräkna viktat genomsnitt i Excel
Nedan finns tre olika situationer där du kan använda SUMPRODUCT -funktionen för att beräkna viktat genomsnitt i Excel
Exempel 1 - När vikterna läggs upp till 100%
Anta att du har en datamängd med betyg som en student har gjort i olika tentor tillsammans med vikterna i procent (som visas nedan):
I ovanstående data får en elev betyg i olika utvärderingar, men i slutändan måste det ges ett slutbetyg eller betyg. Ett enkelt genomsnitt kan inte beräknas här eftersom betydelsen av olika utvärderingar varierar.
Till exempel, en frågesport, med en vikt på 10% bär två gånger vikten jämfört med en uppgift, men en fjärdedel av vikten jämfört med tentamen.
I ett sådant fall kan du använda SUMPRODUCT -funktionen för att få det vägda genomsnittet av poängen.
Här är formeln som ger dig det vägda genomsnittet i Excel:
= SUMPRODUKT (B2: B8, C2: C8)
Så här fungerar den här formeln: Excel SUMPRODUCT -funktionen multiplicerar det första elementet i den första matrisen med det första elementet i den andra matrisen. Sedan multiplicerar det det andra elementet i den första matrisen med det andra elementet i den andra matrisen. Och så vidare…
Och slutligen lägger det till alla dessa värden.
Här är en illustration för att göra det tydligt.
Exempel 2 - När vikterna inte läggs upp till 100%
I ovanstående fall tilldelades vikterna på ett sådant sätt att totalen uppgick till 100%. Men i verkliga scenarier är det kanske inte alltid så.
Låt oss titta på samma exempel med olika vikter.
I ovanstående fall lägger vikterna upp till 200%.
Om jag använder samma SUMPRODUCT -formel ger det mig fel resultat.
I ovanstående resultat har jag fördubblat alla vikter och det returnerar det vägda genomsnittliga värdet till 153,2. Nu vet vi att en elev inte kan få mer än 100 av 100, oavsett hur briljant han/hon är.
Anledningen till detta är att vikterna inte går upp till 100%.
Här är formeln som kommer att få detta sorterat:
= SUMPRODUCT (B2: B8, C2: C8)/SUM (C2: C8)
I formeln ovan divideras SUMPRODUCT -resultatet med summan av alla vikter. Oavsett vad skulle vikterna alltid lägga upp till 100%.
Exempel 3 - När vikterna behöver beräknas
I exemplet som täcks hittills specificerades vikterna. Det kan dock finnas fall där vikterna inte är direkt tillgängliga, och du måste beräkna vikterna först och sedan beräkna det vägda genomsnittet.
Antag att du säljer tre olika typer av produkter enligt nedan:
Du kan beräkna det vägda genomsnittliga priset per produkt med hjälp av funktionen SUMPRODUCT. Här är formeln du kan använda:
= SUMPRODUCT (B2: B4, C2: C4)/SUM (B2: B4)
Genom att dividera SUMPRODUCT -resultatet med SUMMAN för mängder ser du till att vikterna (i detta fall mängder) uppgår till 100%.
Beräkning av vägat genomsnitt i Excel - SUM -funktion
Även om SUMPRODUCT -funktionen är det bästa sättet att beräkna det vägda genomsnittet i Excel, kan du också använda SUM -funktionen.
För att beräkna det vägda genomsnittet med SUM -funktionen måste du multiplicera varje element med dess tilldelade vikt i procent.
Använda samma dataset:
Här är formeln som ger dig rätt resultat:
= SUMMA (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Denna metod är okej att använda när du har ett par objekt. Men när du har många föremål och vikter kan den här metoden vara besvärlig och felaktig. Det finns ett kortare och bättre sätt att göra detta med SUM -funktionen.
Fortsätter med samma datauppsättning, här är den korta formeln som ger dig det vägda genomsnittet med hjälp av SUM -funktionen:
= SUMMA (B2: B8*C2: C8)
Tricket när du använder denna formel är att använda Control + Shift + Enter, istället för att bara använda Enter. Eftersom SUM -funktionen inte kan hantera matriser måste du använda Ctrl + Skift + Enter.
När du trycker på Control + Shift + Enter ser du att lockiga parenteser visas automatiskt i början och slutet av formeln (se formelfältet i bilden ovan).
Återigen, se till att vikterna blir upp till 100%. Om det inte gör det måste du dividera resultatet med summan av vikterna (som visas nedan, med produktexemplet):
