Wanneer we een eenvoudig gemiddelde van een gegeven reeks waarden berekenen, is de veronderstelling dat alle waarden even zwaar of even belangrijk zijn.
Als u bijvoorbeeld voor examens verschijnt en alle examens een vergelijkbaar gewicht hebben, dan is het gemiddelde van uw totale cijfers ook het gewogen gemiddelde van uw scores.
In het echte leven is dit echter nauwelijks het geval.
Sommige taken zijn altijd belangrijker dan andere. Sommige examens zijn belangrijker dan andere.
En dat is waar Gewogen gemiddelde komt in beeld.
Hier is de tekstboekdefinitie van gewogen gemiddelde:
Laten we nu eens kijken hoe we het gewogen gemiddelde in Excel kunnen berekenen.
Gewogen gemiddelde berekenen in Excel
In deze zelfstudie leert u hoe u het gewogen gemiddelde in Excel kunt berekenen:
- De SOMPRODUCT-functie gebruiken.
- De SOM-functie gebruiken.
Dus laten we beginnen.
Gewogen gemiddelde berekenen in Excel - SOMPRODUCT-functie
Er kunnen verschillende scenario's zijn waarin u het gewogen gemiddelde moet berekenen. Hieronder staan drie verschillende situaties waarin u de SOMPRODUCT-functie kunt gebruiken om het gewogen gemiddelde in Excel te berekenen
Hieronder staan drie verschillende situaties waarin u de SOMPRODUCT-functie kunt gebruiken om het gewogen gemiddelde in Excel te berekenen
Voorbeeld 1 - Wanneer de gewichten optellen tot 100%
Stel dat je een dataset hebt met cijfers die een student in verschillende examens heeft gescoord, samen met de gewichten in percentages (zoals hieronder weergegeven):
In de bovenstaande gegevens krijgt een student punten in verschillende evaluaties, maar moet hij uiteindelijk een eindscore of cijfer krijgen. Een eenvoudig gemiddelde kan hier niet worden berekend, omdat het belang van verschillende evaluaties varieert.
Zo weegt een quiz met een gewicht van 10% twee keer zo zwaar als een opdracht, maar een vierde van het gewicht in vergelijking met het examen.
In een dergelijk geval kunt u de SOMPRODUCT-functie gebruiken om het gewogen gemiddelde van de score te krijgen.
Hier is de formule die u het gewogen gemiddelde in Excel geeft:
=SOMPRODUCT(B2:B8,C2:C8)
Hier is hoe deze formule werkt: Excel SOMPRODUCT-functie vermenigvuldigt het eerste element van de eerste array met het eerste element van de tweede array. Vervolgens vermenigvuldigt het het tweede element van de eerste array met het tweede element van de tweede array. Enzovoort…
En tot slot voegt het al deze waarden toe.
Hier is een illustratie om het duidelijk te maken.
Voorbeeld 2 - Wanneer gewichten niet optellen tot 100%
In bovenstaand geval zijn de gewichten zo toegekend dat het totaal opgeteld 100% uitkwam. Maar in real-life scenario's is dit misschien niet altijd het geval.
Laten we eens kijken naar hetzelfde voorbeeld met verschillende gewichten.
In het bovenstaande geval tellen de gewichten op tot 200%.
Als ik dezelfde SOMPRODUCT-formule gebruik, krijg ik het verkeerde resultaat.
In het bovenstaande resultaat heb ik alle gewichten verdubbeld en wordt de gewogen gemiddelde waarde geretourneerd als 153,2. Nu weten we dat een student niet meer dan 100 van de 100 kan halen, hoe briljant hij/zij ook is.
De reden hiervoor is dat de gewichten niet optellen tot 100%.
Hier is de formule waarmee dit wordt gesorteerd:
=SOMPRODUCT(B2:B8,C2:C8)/SOM(C2:C8)
In de bovenstaande formule wordt het SOMPRODUCT-resultaat gedeeld door de som van alle gewichten. Vandaar dat, wat er ook gebeurt, de gewichten altijd optellen tot 100%.
Voorbeeld 3 - Wanneer de gewichten moeten worden berekend
In het tot nu toe behandelde voorbeeld zijn de gewichten gespecificeerd. Er kunnen echter gevallen zijn waarin de gewichten niet direct beschikbaar zijn en u eerst de gewichten moet berekenen en vervolgens het gewogen gemiddelde.
Stel dat u drie verschillende soorten producten verkoopt, zoals hieronder vermeld:
Met de functie SOMPRODUCT kunt u de gewogen gemiddelde prijs per product berekenen. Hier is de formule die u kunt gebruiken:
=SOMPRODUCT(B2:B4,C2:C4)/SOM(B2:B4)
Het SOMPRODUCT resultaat delen door de SOM van hoeveelheden zorgt ervoor dat de gewichten (in dit geval hoeveelheden) optellen tot 100%.
Gewogen gemiddelde berekenen in Excel - SOM-functie
Hoewel de functie SOMPRODUCT de beste manier is om het gewogen gemiddelde in Excel te berekenen, kunt u ook de functie SOM gebruiken.
Om het gewogen gemiddelde te berekenen met behulp van de SOM-functie, moet u elk element vermenigvuldigen met het toegewezen belang in percentage.
Met dezelfde dataset:
Hier de formule die u het juiste resultaat geeft:
=SOM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)
Deze methode is goed te gebruiken als je een paar items hebt. Maar als u veel items en gewichten heeft, kan deze methode omslachtig en foutgevoelig zijn. Er is een kortere en betere manier om dit te doen met behulp van de SOM-functie.
Doorgaan met dezelfde dataset, hier is de korte formule die u het gewogen gemiddelde geeft met behulp van de SOM-functie:
=SOM(B2:B8*C2:C8)
De truc bij het gebruik van deze formule is om Control + Shift + Enter te gebruiken in plaats van alleen Enter te gebruiken. Aangezien de SOM-functie geen arrays aankan, moet u Control + Shift + Enter gebruiken.
Wanneer u op Control + Shift + Enter drukt, ziet u automatisch accolades verschijnen aan het begin en het einde van de formule (zie de formulebalk in de bovenstaande afbeelding).
Nogmaals, zorg ervoor dat de gewichten optellen tot 100%. Als dit niet het geval is, moet u het resultaat delen door de som van de gewichten (zoals hieronder weergegeven, aan de hand van het productvoorbeeld):
