Hoe een gewogen gemiddelde berekenen in Excel?

Stel dat je een boodschappenlijst hebt zoals in de onderstaande schermafbeelding. Terwijl de functie GEMIDDELDE in Excel je de gemiddelde prijs zou geven zonder rekening te houden met gewicht of hoeveelheid, is een nauwkeurigere benadering in deze gevallen om het gewogen gemiddelde te berekenen. Dit weerspiegelt de werkelijke kosten per eenheid beter door items met hogere gewichten of frequenties een sterkere invloed te geven op het eindresultaat.

Om het gewogen gemiddelde te berekenen, gebruik je een combinatie van de SOMPRODUCT en SOM functies als volgt:

Selecteer een lege cel, zoals F2, en voer de volgende formule in:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

Druk vervolgens op de Enter-toets om het resultaat te krijgen.

Opmerking: In deze formule verwijst C2:C18 naar de kolom Gewicht en D2:D18 naar de kolom Prijs. Pas deze bereiken aan indien nodig voor jouw eigen gegevensindeling. De functie SOMPRODUCT vermenigvuldigt elk gewicht met de bijbehorende prijs en somt de resultaten op, terwijl SOM de gewichten optelt – wat het juiste gewogen gemiddelde oplevert. Zorg ervoor dat je bereiken van gelijke lengte gebruikt en controleer op niet-overeenkomende of lege cellen in je gegevens, omdat dit kan leiden tot rekenfouten.

Als het berekende gewogen gemiddelde te veel of te weinig decimalen toont naar jouw voorkeur, selecteer dan de cel en klik op Decimaal verhogen knop of Decimaal verlagen knop op het Start tabblad om het aantal weergegeven decimalen naar wens aan te passen.

Als je een foutmelding zoals #WAARDE! tegenkomt, controleer dan of elke gerefereerde cel een numerieke waarde bevat en of de bereiken consistent zijn. Vermijd ook het opnemen van een koprij in je bereik om nauwkeurige resultaten te garanderen. Wanneer je werkt met grotere datasets, overweeg dan het gebruik van benoemde bereiken voor duidelijkheid en gemakkelijke onderhoud.

De vorige formule berekent het gewogen gemiddelde voor alle items. In praktische analyses wil je mogelijk het gewogen gemiddelde voor specifieke categorieën, zoals het gewogen gemiddelde van alleen appels. In dergelijke gevallen kun je de formule verbeteren door een voorwaarde toe te voegen op basis van je criteria.

Om dit te doen, selecteer een lege cel, zoals F8, en voer de volgende formule in:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Druk vervolgens op de Enter-toets om het gewogen gemiddelde te berekenen dat voldoet aan je specifieke criteria. Deze formule vermenigvuldigt elk gewicht en prijspaar alleen als het item aan de voorwaarde voldoet (“Appel” in dit geval), somt ze op en deelt door de som van de gewichten voor dat item.

Opmerking: Hier is B2:B18 de kolom Fruit, C2:C18 is het Gewicht en D2:D18 is de Prijs. Vervang “Appel” door een ander item indien nodig. Deze methode werkt goed voor filteren op één voorwaarde; als je op meerdere criteria moet filteren (bijvoorbeeld fruitsoort en leverancier), is een hulptabel of een meer geavanceerde formule mogelijk nodig.

Na het toepassen van de formule wil je mogelijk de decimalen aanpassen voor duidelijkheid. Selecteer de resultaatcel en gebruik de Decimaal verhogen of Decimaal verlagen knoppen op het Start tabblad om het aantal weergegeven decimalen te wijzigen.

Als de formule een onverwacht resultaat oplevert, controleer dan of aan de criteria voldaan wordt binnen je doelbereik, en let op lege cellen of tekstwaarden in kolommen die numeriek moeten zijn.

In sommige situaties moet je mogelijk vaak gewogen gemiddelden berekenen over bereiken die in grootte variëren, ontbrekende waarden bevatten of flexibele filtering vereisen, zoals het tegelijkertijd toepassen van meerdere criteria. In plaats van handmatig formules of bereiken bij te werken, kan het automatiseren van de berekening met een VBA-macro tijd besparen en de kans op fouten verminderen – vooral handig bij het omgaan met grote of regelmatig bijgewerkte datasets.

Hier is hoe je een VBA-macro maakt en gebruikt voor gewogen gemiddelden:

1. Klik op Ontwikkelaar > Visual Basic (of druk op Alt + F11) om het venster Microsoft Visual Basic for Applications-editor te openen. Klik vervolgens op Invoegen > Module en plak de volgende code hieronder in het nieuwe modulevenster:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2Druk op F5 (of klik op de Uitvoeren-knop) om uit te voeren.
Je wordt stap voor stap gevraagd om de bereiken te selecteren (criteriabereik – dit kan worden overgeslagen als het niet nodig is, gewichtsbereik en waardebereik). Je kunt ook specifieke criteria invoeren om je berekening te filteren of het leeg laten om alle gegevens te beschouwen. De macro ondersteunt dynamische databereiken, wat praktisch is als je tabel regelmatig groeit of verandert.

Tenslotte krijg je een berichtvenster met het resultaat van het gewogen gemiddelde.

Tips:

• Deze aanpak automatiseert herhalende analyses van gewogen gemiddelden en kan verder worden uitgebreid om extra filtering of uitvoeropties af te handelen.
• Zorg ervoor dat de geselecteerde bereiken van gelijke lengte zijn en dat de gegevenstypen consistent zijn.
• Voeg basisfoutafhandeling toe zoals getoond (bijvoorbeeld in gevallen waar geen geldige gewichten worden gevonden of de gewichtssom nul is).
• Als je alleen wilt toepassen op gefilterde/zichtbare rijen, kun je de code verder verbeteren met speciale celopsomming.

Als je problemen tegenkomt met machtigingen of macrobeveiliging, zorg er dan voor dat macros zijn ingeschakeld in je Excel-instellingen voordat je de code uitvoert.

Gemiddelde bereik afronden in Excel

Gemiddelde veranderingssnelheid in Excel

Gemiddelde/jaarlijkse samengestelde groeisnelheid berekenen in Excel

Voortschrijdend/rollend gemiddelde in Excel

Lopende totaal/gemiddelde in Excel