Wie berechnet man den gewichteten Durchschnitt in Excel?

Angenommen, Sie haben eine Einkaufsliste wie in der folgenden Abbildung dargestellt. Während die AVERAGE-Funktion von Excel Ihnen den einfachen Durchschnittspreis – ohne Berücksichtigung von Gewicht oder Menge – liefert, bietet sich in solchen Fällen ein präziserer Ansatz an: die Berechnung des gewichteten Durchschnitts. Dieser spiegelt die tatsächlichen Kosten pro Einheit deutlich besser wider, indem Elementen mit höherem Gewicht oder häufigerem Vorkommen ein stärkerer Einfluss auf das Endergebnis eingeräumt wird.

Verwenden Sie zur Berechnung des gewichteten Durchschnittspreises eine Kombination aus den Funktionen SUMMENPRODUKTund SUMMEwie folgt:

Wählen Sie eine leere Zelle – beispielsweise F2 – und geben Sie die folgende Formel ein:

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

und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

Hinweis: In dieser Formel bezieht sich C2:C18 auf die Spalte „Gewicht“ und D2:D18 auf die Spalte „Preis“. Passen Sie diese Bereiche entsprechend Ihrem eigenen Datenlayout an. Die Funktion SUMMENPRODUKT multipliziert jedes Gewicht mit dem zugehörigen Preis und summiert die Ergebnisse, während SUMME die Gewichte insgesamt addiert – so erhalten Sie den korrekten gewichteten Durchschnitt. Achten Sie darauf, Bereiche gleicher Länge zu verwenden, und stellen Sie sicher, dass Ihre Daten keine inkonsistenten Werte oder leeren Zellen enthalten, da dies zu Fehlern bei der Berechnung führen kann.

Wenn der berechnete gewichtete Durchschnitt mehr oder weniger Dezimalstellen anzeigt, als Sie wünschen, wählen Sie die Zelle aus und klicken Sie auf die Schaltfläche Dezimalstellen erhöhenoder Dezimalstellen verringern, um die angezeigten Dezimalstellen entsprechend anzupassen.im Register Start

Wenn ein Fehler wie #WERT! auftritt, stellen Sie sicher, dass jede referenzierte Zelle einen numerischen Wert enthält und dass alle Bereiche konsistent sind. Achten Sie zudem darauf, keine Überschriftenzeile in Ihren Berechnungsbereich einzuschließen, um präzise Ergebnisse zu erhalten. Bei umfangreicheren Datensätzen empfiehlt sich die Verwendung benannter Bereiche – für mehr Übersichtlichkeit und einfachere Wartbarkeit.

Die obige Formel berechnet den gewichteten Durchschnittspreis für alle Artikel. In der Praxis möchten Sie jedoch oft den gewichteten Durchschnitt nur für bestimmte Kategorien ermitteln – beispielsweise ausschließlich für Äpfel. In solchen Fällen können Sie die Formel um eine Bedingung erweitern, die Ihren Kriterien entspricht.

Wählen Sie dazu eine leere Zelle wie F8 aus und geben Sie die folgende Formel ein:

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

Drücken Sie anschließend die Eingabetaste, um den gewichteten Durchschnitt gemäß Ihren spezifischen Kriterien zu berechnen. Diese Formel multipliziert jedes Gewicht-Preis-Paar nur dann, wenn der Artikel der Bedingung entspricht (in diesem Fall „Apfel“), summiert diese Produkte und teilt das Ergebnis durch die Summe der zugehörigen Gewichte.

Hinweis: Dabei ist B2:B18 die Obstspalte, C2:C18 das Gewicht und D2:D18 der Preis. Ersetzen Sie „Apfel“ bei Bedarf durch einen anderen Artikel. Diese Methode eignet sich ideal für die Filterung nach einer einzigen Bedingung; für mehrere Kriterien (z. B. Obstsorte und Lieferant) benötigen Sie eine Hilfsspalte oder eine fortgeschrittenere Formel.

Nachdem Sie die Formel angewendet haben, können Sie die Anzahl der Dezimalstellen zur besseren Lesbarkeit anpassen. Wählen Sie dazu die Ergebniszelle aus und klicken Sie im Register Start auf die Schaltflächen Dezimalstellen erhöhenoder Dezimalstellen verringern, um die angezeigten Dezimalstellen zu ändern.

Sollte die Formel ein unerwartetes Ergebnis liefern, stellen Sie sicher, dass die Kriterien innerhalb Ihres Zielbereichs übereinstimmende Werte enthalten, und prüfen Sie sorgfältig auf leere Zellen oder Texteinträge in Spalten, die numerische Werte enthalten sollen.

In bestimmten Situationen müssen Sie gewichtete Durchschnitte möglicherweise häufig über Bereiche berechnen, deren Größe sich ändert, fehlende Werte enthält oder flexible Filterung – etwa die gleichzeitige Anwendung mehrerer Kriterien – erfordert. Statt Formeln oder Bereiche manuell anzupassen, sparen Sie mit einem VBA-Makro zur Automatisierung der Berechnung nicht nur Zeit, sondern reduzieren auch das Risiko menschlicher Fehler – besonders vorteilhaft bei großen oder regelmäßig aktualisierten Datensätzen.

So erstellen und verwenden Sie ein VBA-Makro für gewichtete Durchschnitte:

1. Klicken Sie auf Entwicklertools > Visual Basic(oder drücken Sie)Alt + F11), um das Editorfenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie anschließend auf Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modulfenster ein:

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. Drücken Sie F5(oder klicken Sie auf die)-Schaltfläche „Ausführen“).
Sie werden aufgefordert, die Bereiche schrittweise auszuwählen (Kriterienbereich – dieser kann übersprungen werden, falls nicht benötigt –, Gewichtsbereich und Wertebereich). Sie können auch spezifische Kriterien eingeben, um Ihre Berechnung zu filtern, oder das Feld leer lassen, um alle Daten zu berücksichtigen. Das Makro unterstützt dynamische Bereiche, was es besonders praktisch macht, wenn sich Ihre Tabelle regelmäßig erweitert oder ändert.

Schließlich erhalten Sie ein Meldungsfeld mit dem Ergebnis des gewichteten Durchschnitts.

Tipps:

• Dieser Ansatz automatisiert die wiederholte gewichtete Durchschnittsanalyse und lässt sich problemlos erweitern, um zusätzliche Filter- oder Ausgabeoptionen zu unterstützen.
• Stellen Sie sicher, dass die ausgewählten Bereiche gleich lang sind und die Datentypen konsistent bleiben.
• Fügen Sie eine grundlegende Fehlerbehandlung ein – beispielsweise für Fälle, in denen keine gültigen Gewichte gefunden werden oder die Summe der Gewichte null ergibt.
• Wenn Sie die Berechnung ausschließlich auf gefilterte bzw. sichtbare Zeilen anwenden möchten, können Sie den Code durch eine spezielle Zellenaufzählung noch weiter optimieren.

Sollten Sie auf Berechtigungs- oder Makrosicherheitsprobleme stoßen, stellen Sie sicher, dass Makros in Ihren Excel-Einstellungen aktiviert sind, bevor Sie den Code ausführen.

Durchschnittsbereich mit Rundung in Excel

Durchschnittliche Änderungsrate in Excel

Berechnung der durchschnittlichen/jährlichen Wachstumsrate in Excel

Gleitender/rollierender Durchschnitt in Excel

Laufende Summe / laufender Durchschnitt in Excel