Wie kann man alle 5 Zeilen oder Spalten in Excel mitteln?
Beim Arbeiten mit großen Datensätzen in Excel ist es üblich, Mittelwertberechnungen für jede Gruppe von Zeilen oder Spalten durchzuführen – wie zum Beispiel alle 5 Zeilen oder alle 5 Spalten. Während man Formeln wie =MITTELWERT(A1:A5), =MITTELWERT(A6:A10), =MITTELWERT(A11:A15) usw. manuell einfügen könnte, wird dies bei Hunderten oder Tausenden von Zellen schnell unpraktisch. Das wiederholte manuelle Einfügen dieser Operationen ist sowohl zeitaufwendig als auch fehleranfällig. Glücklicherweise bietet Excel mehrere Möglichkeiten, diese Aufgabe zu automatisieren und macht die Datenanalyse viel effizienter und weniger mühsam. Dieser Artikel stellt mehrere praktische Methoden zur Berechnung des Durchschnitts aller 5 Zeilen oder Spalten vor, darunter formelbasierte Ansätze, Excel-Add-ins, VBA-Automatisierung und PivotTable-Techniken, um Ihnen zu helfen, die beste Lösung für Ihr Szenario zu finden.
Mittelwert für jede 5 Zeilen oder Spalten mit Formeln
Durchschnitt für jede 5 Zeilen mit Kutools für Excel
Durchschnitt für jede 5 Zeilen oder Spalten mit VBA-Code
Durchschnitt für jede 5 Zeilen mit Pivot-Tabelle
Durchschnitt für jede 5 Zeilen oder Spalten mit Formeln
Wenn Sie bevorzugen, Standard-Excel-Formeln zu verwenden, können Sie die Berechnung für jede 5 Zeilen oder Spalten automatisieren, ohne Add-ins oder Skripte zu benötigen. Dieser Ansatz ist besonders geeignet für statische Datensätze, bei denen Sie einfach Mengen von Durchschnittswerten generieren müssen, um Ihre Analyse zu unterstützen. Allerdings muss darauf geachtet werden, die Daten korrekt zu referenzieren und Leerzeichen oder unregelmäßige Intervalle zu verarbeiten.
Das folgende Beispiel zeigt, wie der Durchschnitt jeder 5 Zeilen in einer Spalte berechnet wird:
1. Geben Sie die folgende Formel in die erste Zelle ein, in der Sie das Ergebnis wünschen (zum Beispiel C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Hier ist A2 die Startzelle Ihrer Datenspalte, C2 ist die Ausgabezelle der Formel, und 5 ist das Intervall (Anzahl der zu mittelnden Zeilen). Passen Sie diese Referenzen entsprechend Ihrem tatsächlichen Datensatz an.
Nachdem Sie die Formel eingegeben haben, drücken Sie die Eingabetaste. Das erste gemittelte Ergebnis wird angezeigt. Siehe Screenshot:
2. Wählen Sie die Formelzelle aus, dann ziehen Sie den Ausfüllkästchen nach unten, bis Sie einen Fehlerwert erhalten (wie #DIV/0!, falls weniger als 5 Werte in den verbleibenden Daten vorhanden sind). Dadurch werden automatisch Durchschnitte für jede Gruppe von 5 Zeilen abgerufen. Siehe Screenshot:
Tipps und Hinweise: Sie können Fehlerbehandlungsfunktionen wie WENNFEHLER() verwenden, um Fehlerwerte zu unterdrücken, wenn Ihre Daten nicht in perfekt dimensionierte Gruppen passen, zum Beispiel:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Um den Durchschnitt über 5 Spalten pro Reihe zu berechnen, wenden Sie die folgende Formel an (platzieren Sie sie in A3 und ziehen Sie sie nach rechts):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Hier ist A1 die Startzelle, A3 ist Ihre Formelausgabeszelle, und 5 ist die Anzahl der Spalten in jeder Gruppe. Passen Sie die Zellreferenzen je nach Bedarf für Ihre Datenanordnung an.
Nach dem Eingeben der Formel und Drücken der Eingabetaste, ziehen Sie den Ausfüllkästchen nach rechts, bis Sie einen Fehlerwert erreichen. Siehe Screenshot:
Diese formelbasierte Methode ist ideal für schnelle einmalige Berechnungen oder wenn Sie keine zusätzlichen Werkzeuge verwenden möchten. Wenn sich Ihre Daten jedoch in Größe oder Form ändern, müssen Sie möglicherweise die Formeln anpassen oder die Zellbereiche manuell aktualisieren, und das Verarbeiten unvollständiger Gruppen erfordert möglicherweise zusätzliche Sorgfalt.
Durchschnitt für jede 5 Zeilen mit Kutools für Excel
Kutools für Excel bietet eine praktische grafische Lösung, wenn Sie häufig Gruppen von Zeilen mitteln müssen, ohne komplexe Formeln zu verwalten. Über die Funktionen 'Seitenumbruch in jeder Zeile einfügen' und 'Datenseiten-Summen' können Sie Ihre Daten schnell segmentieren und Stapel-Durchschnittswerte in nur wenigen Klicks berechnen. Diese Methode ist besonders nützlich, wenn Sie Durchschnitte in wiederholbaren Intervallen anwenden und Gruppierungen direkt in Ihrem Arbeitsblatt visualisieren möchten.
Nach dem Herunterladen und Installieren von Kutools für Excel folgen Sie bitte diesen Schritten:
1. Klicken Sie auf Kutools Plus > Drucken > Seitenumbruch in jeder Zeile einfügen. Siehe Screenshot:
2. Geben Sie im Dialogfeld 'Seitenumbruch in jeder Zeile einfügen' das Intervall an (z. B. 5), um nach jeder 5. Zeile einen Seitenumbruch einzufügen. Dadurch segmentiert Kutools Ihre Daten automatisch. Siehe Screenshot:
3. Klicken Sie als Nächstes auf Kutools Plus > Drucken > Datenseiten-Summen. Siehe Screenshot:
4. Wählen Sie im Dialogfeld 'Datenseiten-Summen' die Spalte(n) aus, die Sie mitteln möchten, und wählen Sie 'Mittelwert' als Berechnungsmethode. Siehe Screenshot:
5. Klicken Sie auf OK, und Kutools fügt sofort Teilergebniszeilen mit Durchschnittswerten in jedem 5-Zeilen-Intervall ein. Siehe Screenshot:
Jetzt Kutools für Excel herunterladen und kostenlos testen!
Kutools macht repetitive Datengruppierung und -analyse leicht zu absolvieren, ohne dass Formeln angepasst oder Skripte geschrieben werden müssen. Beachten Sie jedoch, dass eingefügte Seitenumbrüche das Drucklayout und die Ansichten beeinträchtigen könnten, daher sollten Sie diese nach der Nutzung entfernen, falls sie für Ihren Bericht nicht benötigt werden.
Durchschnitt für jede 5 Zeilen oder Spalten mit VBA-Code
Wenn Sie den Durchschnittswert für eine feste Anzahl von Zeilen oder Spalten in großen oder sich ständig ändernden Datensätzen wiederholt berechnen müssen, kann die Automatisierung dieses Prozesses mit VBA einen erheblichen Teil der manuellen Arbeit sparen. VBA ermöglicht es Ihnen, Ihre Daten zu durchlaufen, sie nach Bedarf zu gruppieren und das Durchschnittsergebnis für jeden Satz auszugeben. Diese Methode ist besonders geeignet für fortgeschrittene Benutzer oder solche, die dynamische Datenblöcke verarbeiten, und vermeidet Formeln, die Ihr Arbeitsblatt überladen. Im Folgenden finden Sie ein allgemein verwendbares VBA-Makro, das Sie leicht anpassen können.
Automatisches Mitteln jeder 5 Zeilen:
1. Klicken Sie auf Entwicklertools > Visual Basic, um das Microsoft Visual Basic for Applications-Fenster 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, dann wählen Sie die Startzelle für die Ausgabe und geben Sie die Gruppengröße an (z. B. 5). Das Makro gibt den Durchschnitt für jede Menge von 5 Zeilen aus, jeweils untereinander in der angegebenen Ausgabespalte.
Sie können ein ähnliches Makro verwenden, um den Durchschnitt für jede 5 Spalten in einer Zeile zu berechnen.
Automatisches Mitteln jeder 5 Spalten:
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 für jede 5 Zeilen mit Pivot-Tabelle
Eine weitere praktische Möglichkeit, Gruppendurchschnitte für jede 5 Zeilen zu berechnen, ist die Verwendung einer Pivot-Tabelle, kombiniert mit einer Sequenznummer- oder Indexspalte, um die Daten zu gruppieren. Diese Methode ist besonders nützlich für Benutzer, die mit strukturierten tabellenbasierten Daten arbeiten und eine schnelle, interaktive Zusammenfassung benötigen, ohne Formeln oder Add-ins zu verwenden. Eine Pivot-Tabelle behandelt Datenänderungen dynamisch und unterstützt flexible Gruppierungen – ideal für große Datensätze oder regelmäßige Berichtsaufgaben.
So führen Sie diese Operation mit einer Hilfsspalte und einer Pivot-Tabelle durch:
1. Fügen Sie neben Ihren Daten eine „Index“- oder „Gruppe“-Spalte hinzu, um jede Gruppe von 5 Zeilen zu markieren. In der ersten Datenzeile (B2) geben Sie ein:
=INT((ROW()-ROW($A$2))/5)+1Diese Formel kennzeichnet jede Zeile in der Sequenz und weist denselben Gruppennummern jeder 5 Zeilen zu. Füllen Sie diese Formel entlang Ihres Datensatzes nach unten aus.
2. Wählen Sie Ihre Daten und die neue Indexspalte aus, dann klicken Sie auf Einfügen > Pivot-Tabelle. Bestätigen Sie im Pivot-Tabellen-Erstellungsdialog Ihren Datenbereich und wählen Sie aus, wo die Pivot-Tabelle platziert werden soll.
3. Ziehen Sie im neu erstellten Feldlistenbereich der Pivot-Tabelle das „Gruppe“-Feld in den Zeilenbereich und Ihr Wertefeld (z. B. „Umsatz“) in den Werte-Bereich.
4. Klicken Sie auf den Dropdown-Pfeil im Werte-Bereich, wählen Sie „Wertfeldeinstellungen“ und dann „Mittelwert“ aus.
Nun zeigt Ihre Pivot-Tabelle den Durchschnitt für jede 5 Zeilen der Originaldaten, bequem gruppiert durch die Hilfsspalte.
Die wichtigsten Vorteile der Pivot-Tabellenmethode sind ihre Flexibilität und einfache Aktualisierung, wenn sich Ihre Quelldaten ändern. Es ist jedoch eine Hilfsspalte erforderlich, und sie ist möglicherweise nicht für Situationen geeignet, in denen die Daten genau formatiert oder unverändert bleiben müssen.
Verwandte Artikel:
Wie berechnet man den Durchschnitt der obersten oder untersten 3 Werte in Excel?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins