Wie berechnet man den Durchschnitt einer Spalte in Excel anhand von Kriterien aus einer anderen Spalte?
In vielen praktischen Excel-Szenarien müssen Sie häufig den Durchschnittswert einer Spalte berechnen – gruppiert oder gefiltert nach übereinstimmenden Einträgen in einer anderen Spalte. So ermitteln Sie beispielsweise ganz einfach den durchschnittlichen Umsatz pro Verkäufer oder pro Region, wie in der folgenden Abbildung veranschaulicht. Diese Art der Berechnung kommt regelmäßig in Zusammenfassungsberichten, Leistungsanalysen und Datenüberprüfungen zum Einsatz. In diesem Artikel stellen wir Ihnen mehrere effektive Methoden vor, mit denen Sie dieses Ziel erreichen – damit Sie stets den Ansatz wählen können, der perfekt zu Ihren Anforderungen und Ihrem Kenntnisstand passt.
Berechnen des Durchschnitts in einer Spalte basierend auf dem gleichen Wert in einer anderen Spalte mithilfe von Formeln
Berechnen des Durchschnitts in einer Spalte basierend auf dem gleichen Wert in einer anderen Spalte mit Kutools für Excel
Berechnen des Durchschnitts nach Gruppe mithilfe von PivotTable
Automatisierte Berechnung des gruppenweisen Durchschnitts mit VBA-Makro
Berechnen des Durchschnitts in einer Spalte basierend auf dem gleichen Wert in einer anderen Spalte mithilfe von Formeln
Eine der einfachsten Methoden, um den Durchschnitt einer Gruppe basierend auf einer anderen Spalte in Excel zu berechnen, ist die Verwendung bedingter Formeln wie AVERAGEIF oder AVERAGEIFS. Dieser Ansatz eignet sich besonders gut, wenn Sie gezielte Ergebnisse für bestimmte Kriterien benötigen – etwa den durchschnittlichen Umsatz einer einzelnen Stadt oder eines bestimmten Verkäufers.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis angezeigt werden soll, geben Sie dann die folgende Formel ein und drücken Sie Enter:
=AVERAGEIF(B2:B13,E2,C2:C13)
Erläuterung der Parameter: In der obigen Formel ist B2:B13 der Bereich mit den zu prüfenden Kriterien (z. B. Stadt oder Verkäufer), E2 der konkrete Wert, mit dem verglichen werden soll (z. B. „Owenton“), und C2:C13 der Bereich mit den numerischen Werten, deren Durchschnitt berechnet werden soll.
Nachdem Sie die Eingabetaste gedrückt haben, erhalten Sie sofort den Durchschnitt für die in Zelle E2 angegebene Gruppe – beispielsweise den durchschnittlichen Umsatz für „Owenton“.
Wenn Sie den Durchschnitt für jeden eindeutigen Wert in der Kriterienspalte berechnen möchten, passen Sie einfach den Wert in der Kriterienzelle (E2) entsprechend an – oder kopieren Sie die Formel nach unten, falls Sie bereits eine Liste eindeutiger Einträge haben.
Praktischer Tipp: Bei größeren Datensätzen oder zahlreichen eindeutigen Gruppen beschleunigt die Kombination dieser Formel mit einer Liste eindeutiger Werte – erstellt mithilfe von Tools wie „Duplikate entfernen“ oder der Excel-Funktion UNIQUEin Office 365 und Excel 2021 – die gleichzeitige Berechnung aller Gruppendurchschnitte. Achten Sie dabei sorgfältig darauf, dass die Bereiche in Ihrer Formel sämtliche gewünschten Daten abdecken und beim Kopieren korrekt ausgerichtet bleiben.
Häufige Fehler und Problembehebung:
- Wenn Sie einen #DIV/0!-Fehler erhalten, prüfen Sie, ob Ihr Kriterienwert tatsächlich im ausgewählten Bereich vorkommt.
- Stellen Sie sicher, dass Ihr Zahlenbereich ausschließlich gültige Zahlen enthält – Text oder leere Zellen können die Berechnung stören.
Berechnen des Durchschnitts in einer Spalte basierend auf dem gleichen Wert in einer anderen Spalte mit Kutools für Excel
Wenn Sie den Durchschnitt für alle eindeutigen Werte einer Spalte automatisch berechnen möchten, ohne wiederholt Formeln einzugeben oder manuell zu filtern, bietet Kutools für Excel eine effiziente Möglichkeit zur Durchführung dieser Aufgabe. Dies ist besonders vorteilhaft, wenn Sie große Listen oder komplexe Datensätze verarbeiten.
1. Wählen Sie den gesamten Datenbereich aus, der sowohl die Gruppenspalte als auch die numerische Spalte enthält, deren Durchschnittswert Sie berechnen möchten. Navigieren Sie anschließend zu Kutools > Vereinigen/Aufteilen > Erweiterte Zeilen zusammenführen.
2. Führen Sie im Dialogfeld Zeilen kombinieren basierend auf Spalte die folgenden Schritte aus:
- Wählen Sie die Spalte aus, nach der Sie gruppieren möchten (z. B. Stadt oder Verkäufer), und klicken Sie auf die Schaltfläche Primärschlüssel, um sie als Gruppierungsfeld festzulegen.
- Wählen Sie die numerische Spalte aus, deren Durchschnittswert Sie berechnen möchten, und klicken Sie anschließend auf Berechnen > Durchschnitt.
Tipp: Für alle anderen Spalten (z. B. Datumsangaben) können Sie festlegen, wie deren Werte kombiniert werden sollen – beispielsweise durch Kommas getrennt. - Klicken Sie auf OK, um den Vorgang auszuführen.
Kutools gruppiert Ihre Daten sofort anhand des gewählten Schlüssels und liefert für jede Gruppe den Durchschnittswert der numerischen Spalte.
Die Batch-Verarbeitung von Kutools ist ideal für Anwender, die regelmäßig gruppierte Statistiken analysieren – etwa Monatsberichte, Abteilungszusammenfassungen oder andere Mehrgruppenberechnungen. Als Add-In verändert Kutools nicht die ursprüngliche Datenstruktur, und mit der Vorschau-Funktion können Sie Ihre Gruppierungen vor dem Anwenden prüfen.
Hinweise und Tipps:
- Stellen Sie sicher, dass sich in Ihrem ausgewählten Bereich keine leeren Zeilen befinden, bevor Sie das Tool ausführen.
- Sollten Sie die gruppierten Durchschnittswerte an anderer Stelle benötigen, übertragen Sie die Ergebnisse nach der Berechnung einfach per Kopieren und Einfügen.
- Stellen Sie bei sehr großen Datensätzen sicher, dass Gruppierungs- und numerische Spalten korrekt zugewiesen sind – so vermeiden Sie Verwirrung.
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
Berechnen des Durchschnitts nach Gruppe mithilfe von PivotTable
PivotTable bieten eine leistungsstarke, integrierte Möglichkeit, Daten – einschließlich der Berechnung von Durchschnitten nach Gruppe – zusammenzufassen, zu gruppieren und zu analysieren, ohne Formeln oder externe Add-Ins zu benötigen. Diese Technik ist ideal, wenn Sie eine interaktive Ansicht von Durchschnitten und Summen nach verschiedenen Kategorien wünschen, und eignet sich sowohl für kleine als auch für sehr große Datensätze.
So konfigurieren Sie eine PivotTable, um Gruppendurchschnitte zu berechnen:
- Wählen Sie eine beliebige Zelle in Ihrem Datensatz aus und navigieren Sie dann zu Einfügen > PivotTable. Wählen Sie im Dialogfeld aus, wo die PivotTable erscheinen soll (neues Arbeitsblatt oder vorhandenes Arbeitsblatt), und klicken Sie auf OK.
- Ziehen Sie im Bereich PivotTable-Felder die Spalte, nach der Sie gruppieren möchten, in den Bereich Zeilen (z. B. „Stadt“ oder „Verkäufer“).
- Ziehen Sie die numerische Spalte, deren Durchschnittswert Sie berechnen möchten (z. B. „Umsatz“), in den Bereich Werte. Standardmäßig berechnet Excel möglicherweise die Summe. Um dies zu ändern, klicken Sie auf das Wertefeld, wählen Sie Wert-Feld-Einstellungen und anschließend Durchschnitt.
Ihre PivotTable zeigt sofort den Durchschnittswert je Gruppe an. Sie können den Bericht bei Bedarf schnell filtern, sortieren und formatieren. Diese Methode ist benutzerfreundlich und vermeidet das Risiko von Formelfehlern.
Vorteile: Interaktiv, verarbeitet große Datenmengen elegant und fasst mehrere Statistiken gleichzeitig präzise zusammen.
Nachteile: Das Ergebnis wird im PivotTable-Format und nicht als einfache Liste angezeigt; bei Änderungen an den Quelldaten ist gelegentlich eine Aktualisierung erforderlich.
Tipp: Durch Doppelklicken auf eine beliebige Zusammenfassungszelle in der PivotTable wird ein neues Arbeitsblatt mit den zugrunde liegenden Daten dieser Gruppe geöffnet – so prüfen Sie Details oder analysieren Fehler im Handumdrehen.
Häufige Probleme:
- Wenn Sie den Durchschnitt nicht angezeigt bekommen, stellen Sie sicher, dass im Wertefeld unter „Feld-Einstellungen“ die Option „Durchschnitt“ ausgewählt ist.
- Überprüfen Sie Ihre Quelldaten auf zusätzliche leere Zeilen oder Spalten, die Layoutprobleme in der PivotTable verursachen können.
Automatisierte Berechnung des gruppenweisen Durchschnitts mit VBA-Makro
Für Benutzer, die häufig Durchschnitte für viele Gruppen berechnen oder automatisch eine Zusammenfassung erstellen müssen, kann das Schreiben eines VBA-Makros erheblichen manuellen Aufwand sparen. VBA ist besonders hilfreich, wenn Sie davon ausgehen, dass sich Ihre Datenstruktur nicht ändert, oder wenn Sie wiederholt Zusammenfassungsberichte per Mausklick generieren möchten.
Bevor Sie beginnen, stellen Sie sicher, dass Sie Ihre Arbeitsmappe speichern und Makros aktivieren. So geht’s los:
1. Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im Editor auf Einfügen > Modul, um ein neues Code-Modul zu erstellen. Fügen Sie den folgenden Code in das Modul ein:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Schließen Sie nach dem Einfügen des Codes den VBA-Editor. Wechseln Sie zurück zu Excel und drücken Sie Alt+F8, wählen Sie GroupAverageSummary in der Liste aus und klicken Sie auf Ausführen. Das Makro fordert Sie auf, Ihre Gruppenspalte (Kriterium) und die Werte-Spalte (numerisch) auszuwählen. Sobald Sie Ihre Auswahl getroffen haben, generiert es automatisch ein neues Arbeitsblatt mit dem Namen „Group Average Summary“, das jede eindeutige Gruppe und die zugehörigen Durchschnittswerte anzeigt.
Hinweise zu Parametern und Vorgehensweise:
- Stellen Sie sicher, dass Ihre Gruppen- und Wertespalten gleich lang sind und gültige Daten enthalten (d. h., vermeiden Sie teilweise ausgewählte Bereiche).
- Dieses Makro lässt sich bei Bedarf für fortgeschrittenere Gruppierungen oder zusätzliche Zusammenfassungsstatistiken anpassen.
- Wenn Ihr Arbeitsblatt bereits eine benannte „Gruppendurchschnittszusammenfassung“ (Arbeitsblattnamen) enthält, erstellt das Makro ein Arbeitsblatt mit dem Standardnamen „Arbeitsblattname“.
Fehlerbehebung:
- Wenn Sie eine Fehlermeldung wie „Index außerhalb des gültigen Bereichs“ oder eine ähnliche Meldung erhalten, stellen Sie sicher, dass Ihre Auswahlbereiche korrekt ausgerichtet sind und sich auf demselben Arbeitsblatt befinden.
- Für optimale Ergebnisse stellen Sie sicher, dass die Wertespalte ausschließlich numerische Daten enthält – Text oder leere Zellen im Zahlenbereich werden vom Makro übersprungen.
Die Verwendung dieses Makros ist ideal für die Stapelverarbeitung, automatisierte Berichte oder Situationen, in denen Sie regelmäßig neue oder aktualisierte Datensätze zusammenfassen müssen.
Demo: Berechnen Sie den Durchschnitt einer Spalte basierend auf identischen Werten in einer anderen Spalte mit Kutools für Excel
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins