Wie berechnet man den Durchschnitt jeder fünften Zeile oder Spalte in Excel?
Beim Arbeiten mit großen Datensätzen in Excel ist es üblich, Durchschnittswerte für jeweils eine Gruppe von Zeilen oder Spalten zu berechnen – beispielsweise für jeweils 5 Zeilen oder 5 Spalten. Zwar könnten Sie Formeln wie =MITTELWERT(A1:A5), =MITTELWERT(A6:A10), =MITTELWERT(A11:A15) usw. manuell einfügen, doch wird dies schnell unpraktisch, sobald Ihre Liste Hunderte oder gar Tausende von Zellen umfasst. Das wiederholte, manuelle Einfügen solcher Formeln ist nicht nur zeitaufwendig, sondern auch fehleranfällig. Zum Glück bietet Excel mehrere intelligente Möglichkeiten, diese Aufgabe zu automatisieren – und so Ihre Datenanalyse deutlich effizienter und weniger mühsam zu gestalten. In diesem Artikel stellen wir Ihnen verschiedene praktische Methoden vor, um den Durchschnitt jeder 5. Zeile oder Spalte zu berechnen: von formelbasierten Lösungen über Excel-Add-Ins und VBA-Automatisierung bis hin zu PivotTable-Techniken – damit Sie die perfekte Strategie für Ihr Szenario finden.
Durchschnittswert jeder 5. Zeile oder Spalte mithilfe von Formeln ermitteln
Den Durchschnitt jeder 5. Zeile mit Kutools für Excel berechnen
Durchschnittswert jeder 5. Zeile oder Spalte mithilfe von VBA-Code berechnen
Durchschnitt jeder fünften Zeile mit einer PivotTable
Durchschnittswert jeder 5. Zeile oder Spalte mithilfe von Formeln berechnen
Wenn Sie Standard-Excel-Formeln bevorzugen, können Sie die Berechnung für jeweils fünf Zeilen oder Spalten ganz ohne Add-Ins oder Skripterstellung automatisieren. Dieser Ansatz eignet sich ideal für statische Datensätze, bei denen Sie lediglich Durchschnittswerte zur Unterstützung Ihrer Analyse generieren möchten. Achten Sie jedoch darauf, Ihre Daten korrekt zu referenzieren und leere oder unregelmäßige Intervalle angemessen zu berücksichtigen.
Das folgende Beispiel zeigt, wie der Durchschnitt jeder fünften Zeile einer Spalte berechnet wird:
1. Geben Sie die folgende Formel in die erste Zelle ein, in der das Ergebnis erscheinen soll (z. B.)C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Dabei ist A2 die Startzelle Ihrer Datenspalte, C2 die Zelle für die Formelausgabe und 5 das Intervall (Anzahl der zu mittelnden Zeilen). Passen Sie diese Bezüge entsprechend Ihrem tatsächlichen Datensatz an.
Nachdem Sie die Formel eingegeben haben, drücken Sie Enter. Das erste Durchschnittsergebnis wird angezeigt. Siehe Screenshot:
2.Wählen Sie die Formelzelle aus und ziehen Sie den Ausfüllknauf nach unten, bis ein Fehlerwert erscheint (z. B.)#DIV/0!, falls weniger als 5 Werte in den verbleibenden Daten vorhanden sind). So erhalten Sie automatisch die Durchschnitte für jede Gruppe von 5 Zeilen. Siehe Screenshot:
Tipps und Hinweise:Sie können Fehlerbehandlungsfunktionen wie WENNFEHLER()verwenden, um Fehlerwerte zu unterdrücken, falls Ihre Daten nicht perfekt in gleich große Gruppen passen, zum Beispiel:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Um den Durchschnitt jeder 5. Spalte entlang einer Zeile zu berechnen, wenden Sie die folgende Formel an (geben Sie sie in)A3 ein und ziehen Sie nach rechts):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Dabei ist A1 die Startzelle, A3 die Zelle, in der die Formel ausgegeben wird, und 5 die Anzahl der Spalten pro Gruppe. Passen Sie die Zellbezüge entsprechend Ihrem Datenlayout an.
Geben Sie nach dem Eingeben der Formel Enter ein und ziehen Sie den Ausfüllknauf nach rechts, bis ein Fehlerwert erscheint. Siehe Screenshot:
Diese formelbasierte Methode eignet sich ideal für einmalige, schnelle Berechnungen oder wenn Sie auf zusätzliche Tools verzichten möchten. Beachten Sie jedoch: Bei Änderungen der Größe oder Struktur Ihrer Daten müssen Sie Formeln oder Zellbereiche möglicherweise manuell anpassen – und unvollständige Gruppen erfordern besondere Sorgfalt.
Durchschnitt jeder 5. Zeile mit Kutools für Excel
Kutools für Excel bietet eine komfortable grafische Lösung, wenn Sie regelmäßig Gruppen von Zeilen mitteln möchten – ohne sich mit komplexen Formeln herumschlagen zu müssen. Mithilfe der Funktionen Fügen Sie Seitenumbrüche in jeder zweiten Zeile ein und Datenblattstatistik können Sie Ihre Daten im Handumdrehen segmentieren und Batch-Durchschnitte mit nur wenigen Klicks berechnen. Diese Methode ist besonders nützlich, wenn Sie Durchschnitte über wiederkehrende Intervalle anwenden und die Gruppierung direkt in Ihrem Arbeitsblatt visualisieren möchten.
Nachdem Sie heruntergeladen und installiert haben Kutools für Excel, führen Sie bitte die folgenden Schritte aus:
1. Klicken Sie auf KUTOOLS PLUS > Drucken > Fügen Sie Seitenumbrüche in jeder zweiten Zeile ein. Siehe Screenshot:
2. Geben Sie im Dialogfeld Fügen Sie Seitenumbrüche in jeder zweiten Zeile eindas Intervall an (z. B.)5), um nach jeweils 5 Zeilen einen Seitenumbruch einzufügen. So segmentiert Kutools Ihre Daten automatisch. Siehe Screenshot:
3. Klicken Sie anschließend auf KUTOOLS PLUS > Drucken > Datenblattstatistik. Siehe Screenshot:
4. Wählen Sie im Dialog Datenblattstatistik die Spalte(n) aus, deren Durchschnitt Sie berechnen möchten, und wählen Sie Durchschnitt als Berechnungsmethode. Siehe Screenshot:
5. Klicken Sie auf OK, und Kutools fügt sofort Zwischensummenzeilen mit Durchschnittswerten in jedem 5-Zeilen-Intervall ein. Siehe Screenshot:
Laden Sie Kutools für Excel jetzt herunter und testen Sie es kostenlos!
Kutools vereinfacht wiederkehrende Datengruppierungen und -analysen – ganz ohne Anpassung von Formeln oder das Schreiben von Skripten. Beachten Sie jedoch, dass eingefügte Seitenumbrüche das Drucklayout und die Ansicht beeinträchtigen können. Entfernen Sie sie daher nach Gebrauch, sofern sie für Ihren Bericht nicht erforderlich sind.
Durchschnittswert jeder 5. Zeile oder Spalte mithilfe von VBA-Code ermitteln
Wenn Sie regelmäßig den Durchschnittswert für eine feste Anzahl von Zeilen oder Spalten über große oder ständig wechselnde Datensätze hinweg berechnen müssen, spart Ihnen die Automatisierung dieses Prozesses mit VBA erheblichen manuellen Aufwand. Mithilfe von VBA können Sie Ihre Daten effizient durchlaufen, flexibel gruppieren und das Durchschnittsergebnis für jeden Batch direkt ausgeben. Diese Methode ist ideal für fortgeschrittene Anwender oder alle, die mit dynamischen Datenblöcken arbeiten – und verhindert gleichzeitig, dass Ihr Arbeitsblatt mit Formeln überladen wird. Im Folgenden finden Sie ein universell einsetzbares VBA-Makro, das sich mühelos an Ihre Anforderungen anpassen lässt.
Automatische Durchschnittsberechnung jeder 5. Zeile:
1. Klicken Sie auf Entwicklertools > Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie dann auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
Sub AverageEvery5Rows()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Integer, i As Integer, j As Integer
Dim LastRow As Long, StartRow As Long
Dim SumValue As Double, CountValue As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
LastRow = DataRange.Rows.Count
StartRow = 1
i = 0
Do While StartRow <= LastRow
SumValue = 0
CountValue = 0
For j = 0 To GroupSize - 1
If (StartRow + j) <= LastRow Then
SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
CountValue = CountValue + 1
End If
Next j
If CountValue > 0 Then
OutputCell.Offset(i, 0).Value = SumValue / CountValue
Else
OutputCell.Offset(i, 0).Value = ""
End If
StartRow = StartRow + GroupSize
i = i + 1
Loop
End Sub2. Um den Code auszuführen, klicken Sie auf die Schaltfläche 
oder drücken Sie F5. Wählen Sie Ihren Datenbereich (eine Spalte) aus, legen Sie die Startzelle für die Ausgabe fest und geben Sie die Gruppengröße an (z. B. 5). Das Makro berechnet dann nacheinander den Durchschnitt jeder Gruppe von 5 Zeilen und gibt ihn in der angegebenen Ausgabespalte aus.
Sie können ein ähnliches Makro nutzen, um den Durchschnitt jeder fünften Spalte entlang einer Zeile zu berechnen.
Automatisierte Durchschnittsberechnung jeder 5. Spalte:
Sub AverageEveryNColumns()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Long
Dim totalCols As Long, totalRows As Long
Dim startCol As Long, endCol As Long, outCol As Long
Dim v As Variant
Dim r As Long, c As Long
Dim sumVal As Double, cntVal As Long
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set DataRange = Application.InputBox("Select the data range (single rows)", _
xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
If GroupSize < 1 Then
MsgBox "Group size must be >= 1.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prevCalc As XlCalculation
prevCalc = Application.Calculation
Application.Calculation = xlCalculationManual
totalCols = DataRange.Columns.Count
totalRows = DataRange.Rows.Count
v = DataRange.Value
outCol = 0
For startCol = 1 To totalCols Step GroupSize
endCol = startCol + GroupSize - 1
If endCol > totalCols Then endCol = totalCols
sumVal = 0
cntVal = 0
For r = 1 To totalRows
For c = startCol To endCol
If Not IsEmpty(v(r, c)) Then
If IsNumeric(v(r, c)) Then
sumVal = sumVal + CDbl(v(r, c))
cntVal = cntVal + 1
End If
End If
Next c
Next r
If cntVal > 0 Then
OutputCell.Offset(0, outCol).Value = sumVal / cntVal
Else
OutputCell.Offset(0, outCol).Value = ""
End If
outCol = outCol + 1
Next startCol
CleanExit:
Application.Calculation = prevCalc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Durchschnitt jeder 5. Zeile mit PivotTable
Eine weitere praktische Möglichkeit, Gruppendurchschnitte alle 5 Zeilen zu berechnen, bietet die Verwendung einer PivotTable in Kombination mit einer Sequenznummer oder einer Indexspalte zur Datengruppierung. Diese Methode eignet sich besonders gut für Anwender, die mit strukturierten, tabellenbasierten Daten arbeiten und schnell eine interaktive Zusammenfassung benötigen – ganz ohne Formeln oder Add-Ins. PivotTables passen sich dynamisch an Änderungen in den Daten an und ermöglichen flexible Gruppierungen, was sie ideal für große Datensätze oder regelmäßige Berichtsaufgaben macht.
So führen Sie diesen Vorgang mithilfe einer Hilfsspalte und einer PivotTable durch:
1.Fügen Sie neben Ihren Daten eine Spalte „Index“ oder „Gruppe“ hinzu, um jede Gruppe von 5 Zeilen zu kennzeichnen. Geben Sie in die erste Datenzeile ()B2) Folgendes ein:
=INT((ROW()-ROW($A$2))/5)+1Diese Formel nummeriert jede Zeile fortlaufend und weist dabei jeder Gruppe von 5 Zeilen dieselbe Gruppennummer zu. Füllen Sie diese Formel neben Ihrem Datensatz nach unten aus.
2. Markieren Sie Ihre Daten sowie die neue Indexspalte und klicken Sie anschließend auf Einfügen > PivotTable. Bestätigen Sie im Dialogfeld zur Erstellung der PivotTable Ihren Datenbereich und wählen Sie aus, wo die PivotTable platziert werden soll.
3. Ziehen Sie im neu erstellten Feldbereich der PivotTable das Feld „Group“ (Gruppe) in den Bereich Zeilen und Ihr Wertefeld (z. B. „Sales“/Umsatz) in den Bereich Werte.
4. Klicken Sie im Bereich „Werte“ auf das Dropdown-Menü, wählen Sie Wert Feld-Einstellungen und anschließend Durchschnitt.
Ihre PivotTable zeigt nun den Durchschnittswert für jeweils 5 Zeilen der Originaldaten an, übersichtlich nach der Hilfsspalte gruppiert.
Wesentliche Vorteile der PivotTable-Methode sind ihre Flexibilität und die einfache Aktualisierung bei Änderungen Ihrer Quelldaten. Allerdings erfordert sie das Hinzufügen einer Hilfsspalte und ist möglicherweise ungeeignet, wenn Ihre Daten exakt formatiert bleiben oder unverändert bleiben müssen.
Verwandte Artikel:
Wie berechnet man den Durchschnitt der drei höchsten oder niedrigsten Werte in 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