Wie berechnet man den gewichteten Durchschnitt in Excel?

Angenommen, Sie haben eine Einkaufsliste, wie im folgenden Screenshot gezeigt. Während die MITTELWERT-Funktion in Excel Ihnen den mittleren Preis ohne Berücksichtigung von Gewicht oder Menge liefern würde, ist es in diesen Fällen genauer, den gewichteten Durchschnitt zu berechnen. Dies spiegelt die tatsächlichen Kosten pro Einheit besser wider, indem Elementen mit höherem Gewicht oder häufigeren Vorkommen ein stärkerer Einfluss auf das Endergebnis gegeben wird.

Um den gewichteten Durchschnittspreis zu berechnen, verwenden Sie eine Kombination aus den Funktionen SUMMENPRODUKT und SUMME wie folgt:

Wählen Sie eine leere Zelle aus, z. B. 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 je nach Bedarf für Ihr eigenes Datenlayout an. Die Funktion SUMMENPRODUKT multipliziert jedes Gewicht mit dem entsprechenden Preis und summiert die Ergebnisse, während SUMME die Gewichte addiert – was den korrekten gewichteten Durchschnitt ergibt. Stellen Sie sicher, dass Sie Bereiche gleicher Länge verwenden und dass es keine fehlenden oder leeren Zellen in Ihren Daten gibt, da dies zu Berechnungsfehlern führen könnte.

Wenn der berechnete gewichtete Durchschnitt zu viele oder zu wenige Dezimalstellen für Ihre Präferenz anzeigt, wählen Sie die Zelle aus und klicken Sie dann auf Dezimalstelle erhöhen Schaltfläche oder Dezimalstelle verringern Schaltfläche auf der Registerkarte Start um die angezeigten Dezimalstellen entsprechend anzupassen.

Wenn Sie einen Fehler wie #WERT! erhalten, überprüfen Sie, dass jede referenzierte Zelle einen numerischen Wert enthält und dass die Bereiche konsistent sind. Vermeiden Sie auch, eine Kopfzeile in Ihrem Berechnungsbereich einzubeziehen, um genaue Ergebnisse zu gewährleisten. Bei der Arbeit mit größeren Datensätzen sollten Sie benannte Bereiche zur Klarheit und einfachen Wartung in Betracht ziehen.

Die vorherige Formel berechnet den gewichteten Durchschnittspreis für alle Artikel. In der praktischen Analyse möchten Sie möglicherweise den gewichteten Durchschnitt für bestimmte Kategorien, wie zum Beispiel den gewichteten Durchschnittspreis nur für Äpfel. In solchen Fällen können Sie die Formel erweitern, um eine Bedingung basierend auf Ihren Kriterien einzuschließen.

Um dies zu tun, wählen Sie eine leere Zelle, wie F8, und geben Sie die folgende Formel ein:

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

Drücken Sie dann die Eingabetaste, um den gewichteten Durchschnitt zu berechnen, der Ihren spezifischen Kriterien entspricht. Diese Formel multipliziert jedes Gewicht und Preispaar nur, wenn das Element der Bedingung entspricht (in diesem Fall „Apfel“), summiert sie und teilt durch die Summe der Gewichte für dieses Element.

Hinweis: Hier ist B2:B18 die Fruchtspalte, C2:C18 das Gewicht und D2:D18 der Preis. Ersetzen Sie „Apfel“ bei Bedarf durch ein anderes Element. Diese Methode funktioniert gut für die Filterung nach einem Kriterium; falls Sie nach mehreren Kriterien filtern müssen (z. B. Fruchttyp und Lieferant), kann eine Hilfsspalte oder eine fortgeschrittenere Formel erforderlich sein.

Nachdem Sie die Formel angewendet haben, möchten Sie möglicherweise die Dezimalstellen zur Klarheit anpassen. Wählen Sie die Ergebniszelle aus und verwenden Sie die Dezimalstelle erhöhen oder Dezimalstelle verringern Schaltflächen auf der Registerkarte Start um die angezeigten Dezimalstellen zu ändern.

Sollte die Formel ein unerwartetes Ergebnis zurückgeben, überprüfen Sie, ob die Kriterien Übereinstimmungen innerhalb Ihres Zielbereichs haben und achten Sie auf leere Zellen oder Texteinträge in Spalten, die numerisch sein sollen.

In einigen Situationen müssen Sie möglicherweise häufig gewichtete Durchschnitte über Bereiche berechnen, die in der Größe variieren, fehlende Werte enthalten oder flexible Filterung erfordern, wie das gleichzeitige Anwenden mehrerer Kriterien. Anstatt Formeln oder Bereiche manuell zu aktualisieren, kann die Automatisierung der Berechnung mit einem VBA-Makro Zeit sparen und die Fehlerquote reduzieren – besonders nützlich bei der Arbeit mit großen oder regelmäßig aktualisierten Datensätzen.

Hier erfahren Sie, wie Sie ein VBA-Makro für gewichtete Durchschnitte erstellen und verwenden:

1. Klicken Sie auf Entwicklertools > Visual Basic (oder drücken Sie Alt + F11), um das Microsoft Visual Basic for Applications Editor-Fenster zu öffnen. Klicken Sie als Nächstes 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 Ausführen-Schaltfläche), um das Makro auszuführen.
Sie werden aufgefordert, die Bereiche schrittweise auszuwählen (Kriterienbereich – dies kann übersprungen werden, falls nicht benötigt, Gewichtsbereich und Wertebereich). Sie können auch spezifische Kriterien eingeben, um Ihre Berechnung zu filtern, oder es leer lassen, um alle Daten zu berücksichtigen. Das Makro unterstützt dynamische Datenbereiche, was praktisch ist, wenn sich Ihre Tabelle regelmäßig verändert.

Abschließend erhalten Sie ein Meldungsfenster, das das Ergebnis des gewichteten Durchschnitts auflistet.

Tipps:

• Dieser Ansatz automatisiert repetitive Analysen von gewichteten Durchschnitten und kann weiter ausgebaut werden, um zusätzliche Filter oder Ausgabeoptionen zu handhaben.
• Stellen Sie sicher, dass die ausgewählten Bereiche gleich lang sind und dass die Datentypen konsistent sind.
• Fügen Sie grundlegende Fehlerbehandlung hinzu, wie gezeigt (z. B. in Fällen, in denen keine gültigen Gewichte gefunden werden oder die Summe der Gewichte null ist).
• Wenn Sie nur auf gefilterte/sichtbare Zeilen anwenden möchten, können Sie den Code mit spezieller Zellzählung weiter verbessern.

Wenn 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

Durchschnittliche/verzinsliche jährliche Wachstumsrate in Excel berechnen

Gleitender/Rollender Durchschnitt in Excel

Laufende Summe/Durchschnitt in Excel