Hoe het gemiddelde van elke 5 rijen of kolommen in Excel te berekenen?
Bij het werken met grote datasets in Excel is het gebruikelijk om gemiddelde berekeningen nodig te hebben voor elke groep rijen of kolommen—zoals elke 5 rijen of elke 5 kolommen. Hoewel je handmatig formules kunt invoeren zoals =GEMIDDELDE(A1:A5), =GEMIDDELDE(A6:A10), =GEMIDDELDE(A11:A15), enzovoort, wordt dit al snel onpraktisch als je lijst honderden of duizenden cellen bevat. Het handmatig herhalen van deze bewerkingen is tijdrovend en gevoelig voor fouten. Gelukkig biedt Excel meerdere manieren om deze taak te automatiseren, waardoor data-analyse veel efficiënter en minder vervelend wordt. Dit artikel introduceert verschillende praktische methoden voor het berekenen van het gemiddelde van elke 5 rijen of kolommen, inclusief formule-gebaseerde benaderingen, Excel-add-ins, VBA-automatisering en Pivot Table technieken, om je te helpen de beste oplossing voor jouw scenario te kiezen.
Gemiddelde van elke 5 rijen of kolommen met formules
Gemiddelde van elke 5 rijen met Kutools voor Excel
Gemiddelde van elke 5 rijen of kolommen met VBA-code
Gemiddelde van elke 5 rijen met Pivot Table
Gemiddelde van elke 5 rijen of kolommen met formules
Als je standaard Excel-formules wilt gebruiken, kun je de berekening voor elke 5 rijen of kolommen automatiseren zonder dat je add-ins of scripts nodig hebt. Deze aanpak is vooral geschikt voor statische datasets waarbij je eenvoudigweg sets van gemiddelden moet genereren om je analyse te ondersteunen. Zorg er echter voor dat je correct naar je gegevens verwijst en eventuele lege of onregelmatige intervallen afhandelt.
Het volgende voorbeeld laat zien hoe je het gemiddelde van elke 5 rijen in een kolom kunt berekenen:
1. Voer de volgende formule in de eerste cel in waar je het resultaat wilt (bijvoorbeeld C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Hierbij is A2 de startcel van je gegevenskolom, C2 is de uitvoercel van de formule, en 5 is het interval (aantal rijen om het gemiddelde te berekenen). Pas deze verwijzingen aan volgens je daadwerkelijke dataset.
Nadat je de formule hebt ingevoerd, druk op Enter. Het eerste gemiddelde resultaat wordt weergegeven. Zie screenshot:
2. Selecteer de formulecel, en sleep dan het vulhulpje naar beneden totdat je een foutwaarde tegenkomt (zoals #DIV/0! als er minder dan 5 waarden in de resterende gegevens zijn). Dit zal automatisch gemiddelden ophalen voor elke groep van 5 rijen. Zie screenshot:
Tips en Opmerkingen: Je kunt foutafhandelingsfuncties zoals ALS.FOUT() gebruiken om foutwaarden te onderdrukken als je gegevens niet in perfecte groepen passen, bijvoorbeeld:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Om het gemiddelde elke 5 kolommen over een rij te berekenen, pas de volgende formule toe (plaats in A3 en sleep over):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Hierbij is A1 de startcel, A3 is je formule-uitvoercel, en 5 is het aantal kolommen in elke groep. Pas celverwijzingen aan volgens je gegevensindeling.
Na het invoeren van de formule en drukken op Enter, sleep het vulhulpje naar rechts totdat je een foutwaarde tegenkomt. Zie screenshot:
Deze formule-gebaseerde methode is ideaal voor snelle eenmalige berekeningen of wanneer je geen extra tools wilt gebruiken. Echter, naarmate je gegevens in grootte of vorm veranderen, moet je mogelijk de formules aanpassen of de celbereiken handmatig bijwerken, en het afhandelen van onvolledige groepen kan extra zorg vereisen.
Gemiddelde van elke 5 rijen met Kutools voor Excel
Kutools voor Excel biedt een handige grafische oplossing als je vaak groepen rijen moet middelen zonder complexe formules te beheren. Via de functies Voeg Paginascheidingen In Elke Rij en Gegevenspagina Statistieken, kun je je gegevens snel segmenteren en batchgemiddelden berekenen in slechts een paar klikken. Deze methode is vooral nuttig als je gemiddelden wilt toepassen over herhaalbare intervallen en groepering direct in je werkblad wilt visualiseren.
Na het downloaden en installeren Kutools voor Excel, volg deze stappen:
1. Klik Kutools Plus > Afdrukken > Voeg Paginascheidingen In Elke Rij. Zie screenshot:
2. Specificeer in het dialoogvenster Voeg Paginascheidingen In Elke Rij het interval (bijvoorbeeld 5) om een paginascheiding in te voegen na elke 5 rijen. Dit laat Kutools je gegevens automatisch segmenteren. Zie screenshot:
3. Klik vervolgens op Kutools Plus > Afdrukken > Gegevenspagina Statistieken. Zie screenshot:
4. Selecteer in het dialoogvenster Gegevenspagina Statistieken de kolom(men) die je wilt middelen, en kies Gemiddelde als de berekeningsmethode. Zie screenshot:
5. Klik op OK, en Kutools voegt onmiddellijk subtotalerijen met gemiddelde waarden in bij elk 5-rij interval. Zie screenshot:
Download en probeer Kutools voor Excel Nu Gratis!
Kutools maakt het herhalen van gegevensgroepering en analyse gemakkelijk te voltooien zonder dat je formules hoeft aan te passen of scripts hoeft te schrijven. Let echter op dat ingevoegde paginascheidingen de afdruklayout en weergaven kunnen beïnvloeden, dus je kunt ze na gebruik verwijderen als ze niet nodig zijn voor je rapport.
Gemiddelde van elke 5 rijen of kolommen met VBA-code
Als je herhaaldelijk de gemiddelde waarde voor elk vast aantal rijen of kolommen moet berekenen over grote of voortdurend veranderende datasets, kan het automatiseren van dit proces met VBA een aanzienlijke hoeveelheid handmatige arbeid besparen. VBA stelt je in staat om door je gegevens te lussen, ze indien nodig te groeperen, en het gemiddelde resultaat voor elke batch uit te voeren. Deze methode is vooral geschikt voor gevorderde gebruikers of voor hen die dynamische datablokken behandelen, en voorkomt dat formules je werkblad overladen. Hieronder is een algemene VBA-macro die je gemakkelijk kunt aanpassen.
Automatiseer het middelen van elke 5 rijen:
1. Klik Ontwikkelaar > Visual Basic om het Microsoft Visual Basic for Applications venster te openen. Klik vervolgens op Invoegen > Module en plak de onderstaande code in de module:
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. Om de code uit te voeren, klik de 
knop of druk F5. Selecteer je gegevensbereik (één kolom), kies dan de startcel voor de uitvoer, en specificeer de groepsgrootte (bijvoorbeeld 5). De macro zal het gemiddelde voor elke set van 5 rijen uitvoeren, één onder de andere in de gespecificeerde uitvoerkolom.
Je kunt een vergelijkbare macro gebruiken om elke 5 kolommen over een rij te middelen.
Automatiseer het middelen van elke 5 kolommen::
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
Gemiddelde van elke 5 rijen met Pivot Table
Een andere praktische manier om groepsgemiddelden elke 5 rijen te berekenen is door een Pivot Table te gebruiken, gecombineerd met een volgnummer of indexkolom om de gegevens te groeperen. Deze methode is vooral nuttig voor gebruikers die werken met gestructureerde, tabelgebaseerde gegevens en een snelle, interactieve samenvatting nodig hebben zonder formules te schrijven of add-ins te gebruiken. Een Pivot Table behandelt gegevenswijzigingen dynamisch en ondersteunt flexibele groepering—ideaal voor grote datasets of regelmatige rapportagetaken.
Hier is hoe je deze bewerking kunt uitvoeren met behulp van een hulptabel en een Pivot Table:
1. Voeg een "Index" of "Groep" kolom toe naast je gegevens om elke groep van 5 rijen te markeren. In de eerste gegevensrij (B2), voer in:
=INT((ROW()-ROW($A$2))/5)+1Deze formule labelt elke rij in volgorde, waarbij hetzelfde groepsnummer wordt toegewezen aan elke 5 rijen. Vul deze formule langs je dataset in.
2. Selecteer je gegevens en de nieuwe Index-kolom, en klik vervolgens Invoegen > Draaitabel. Bevestig in het draaitabelcreatievenster je gegevensbereik en kies waar je de draaitabel wilt plaatsen.
3. Sleep in de nieuw gemaakte draaitabelfieldlijst het "Groep"-veld naar het Rijengebied, en je waardeveld (bijvoorbeeld "Verkoop") naar het Waardengebied.
4. Klik op de vervolgkeuzelijst in het Waardengebied, kies Waarde-veldinstellingen, en selecteer Gemiddelde.
Nu toont je draaitabel het gemiddelde voor elke 5 rijen van de originele gegevens, handig gegroepeerd door de hulptabel.
Belangrijkste voordelen van de draaitabelmethode zijn de flexibiliteit en eenvoudige updates wanneer je brongegevens veranderen. Het vereist echter het toevoegen van een hulptabel, en past mogelijk niet bij situaties waarin gegevens precies geformatteerd of ongewijzigd moeten blijven.
Gerelateerde artikelen:
Hoe het gemiddelde van de bovenste of onderste 3 waarden in Excel te berekenen?
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen