Hoe berekent u het gewogen gemiddelde in Excel?

Stel dat u een boodschappenlijst hebt zoals weergegeven in de onderstaande schermafbeelding. Terwijl de AVERAGE-functie van Excel het gemiddelde prijsniveau geeft zonder rekening te houden met gewicht of hoeveelheid, biedt het berekenen van het gewogen gemiddelde in dergelijke gevallen een nauwkeuriger beeld. Het weerspiegelt de werkelijke kosten per eenheid beter, doordat items met hogere gewichten of frequenties een grotere invloed krijgen op het eindresultaat.

Gebruik voor het berekenen van de gewogen gemiddelde prijs een combinatie van de SUMPRODUCT- en SUM-functies als volgt:

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

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

en druk op de Enter-toets om het resultaat te verkrijgen.

Opmerking: In deze formule verwijst C2:C18 naar de kolom Gewicht en D2:D18 naar de kolom Prijs. Pas deze bereiken zo nodig aan uw eigen gegevensindeling aan. De SUMPRODUCT-functie vermenigvuldigt elk gewicht met de overeenkomstige prijs en telt de resultaten op, terwijl SUM de gewichten optelt – zodat u het juiste gewogen gemiddelde krijgt. Zorg ervoor dat u bereiken van gelijke lengte gebruikt en dat er geen niet-overeenkomende waarden of lege cellen in uw gegevens voorkomen, aangezien dit tot berekeningsfouten kan leiden.

Als het berekende gewogen gemiddelde te veel of te weinig decimalen weergeeft naar uw voorkeur, selecteert u de cel en klikt u op de knop Meer decimalen of Minder decimalen op het tabblad Start om het aantal weergegeven decimalen naar wens aan te passen.

Als u een foutmelding zoals #WAARDE! tegenkomt, controleert u of elke gerefereerde cel een numerieke waarde bevat en of de bereiken consistent zijn. Vermijd ook het meenemen van een kopregel in uw bereik om nauwkeurige resultaten te garanderen. Overweeg bij het werken met grotere datasets benoemde bereiken te gebruiken voor meer duidelijkheid en eenvoudiger onderhoud.

De vorige formule berekent de gewogen gemiddelde prijs voor alle items. In praktische analyses wilt u mogelijk het gewogen gemiddelde voor specifieke categorieën berekenen, zoals dat van appels. In dergelijke gevallen kunt u de formule uitbreiden met een voorwaarde die aansluit bij uw criteria.

Selecteer hiervoor 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 daarna op de Enter-toets om het gewogen gemiddelde te berekenen dat voldoet aan uw specifieke criteria. Deze formule vermenigvuldigt elk gewicht-prijs-paar alleen als het item voldoet aan de voorwaarde (‘Apple’ in dit geval), telt deze producten op en deelt het resultaat door de som van de bijbehorende gewichten.

Opmerking: Hierbij is B2:B18 de Fruit-kolom, C2:C18 het gewicht en D2:D18 de prijs. Vervang „Apple” door een ander item indien nodig. Deze methode werkt uitstekend bij filteren op één voorwaarde; voor meerdere criteria (bijvoorbeeld fruitsoort en leverancier) is een hulpkolom of een geavanceerdere formule nodig.

Na het toepassen van de formule wilt u de decimalen mogelijk aanpassen voor meer duidelijkheid. Selecteer de resultaatcel en gebruik de knoppen Meer decimalen of Minder decimalen op het tabblad Start om het aantal weergegeven decimalen te wijzigen.

Mocht de formule een onverwacht resultaat opleveren, controleer dan of de criteria overeenkomsten hebben binnen uw doelbereik en let op lege cellen of tekstwaarden in kolommen die numeriek moeten zijn.

In sommige situaties moet u mogelijk regelmatig gewogen gemiddelden berekenen over bereiken die van grootte veranderen, ontbrekende waarden bevatten of flexibele filtering vereisen, zoals het gelijktijdig toepassen van meerdere criteria. In plaats van formules of bereiken handmatig bij te werken, bespaart automatisering van de berekening met een VBA-macro tijd en vermindert het de kans op fouten – vooral handig bij grote of regelmatig bijgewerkte datasets.

Zo maakt en gebruikt u een VBA-macro voor gewogen gemiddelden:

1. Klik op Ontwikkelaar > Visual Basic(of druk op)Alt + F11) om het venster Microsoft Visual Basic for Applications te openen. Klik vervolgens op Invoegen > Module en plak de volgende code 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

2. Druk op F5(of klik op de knop) Uitvoeren) om de macro uit te voeren.
U wordt stapsgewijs gevraagd de bereiken te selecteren (criteria-bereik – dit kunt u overslaan indien niet nodig, gewichtsbereik en waardebereik). U kunt ook specifieke criteria invoeren om uw berekening te filteren of het veld leeg laten om alle gegevens te gebruiken. De macro ondersteunt dynamische bereiken, waardoor deze ideaal is als uw tabel regelmatig groeit of verandert.

Uiteindelijk ontvangt u een berichtvenster met het resultaat van het gewogen gemiddelde.

Tips:

• Deze aanpak automatiseert repetitieve analyses met gewogen gemiddelden en is eenvoudig uit te breiden met extra filter- of uitvoeropties.
• Zorg ervoor dat de geselecteerde bereiken even lang zijn en dat de gegevenstypen consistent blijven.
• Neem basisfoutafhandeling op, zoals weergegeven (bijvoorbeeld voor gevallen waarin geen geldige gewichten worden gevonden of de som van de gewichten nul is).
• Als u de berekening alleen op gefilterde of zichtbare rijen wilt toepassen, kunt u de code verder verbeteren met speciale celenumeratie.

Als u problemen ondervindt met machtigingen of macrobeveiliging, zorg er dan voor dat macro’s zijn ingeschakeld in uw Excel-instellingen voordat u de code uitvoert.

Gemiddelde bereik met afronding in Excel

Gemiddelde veranderingssnelheid in Excel

Bereken het gemiddelde/samengestelde jaarlijkse groeipercentage in Excel

Voortschrijdend/glijdend gemiddelde in Excel

Lopend totaal / lopend gemiddelde in Excel