Hoe berekent u het gemiddelde van een dynamisch bereik in Excel?

Formules zijn een veelzijdige aanpak voor het berekenen van het gemiddelde van een dynamisch bereik wanneer het begin- of eindpunt van uw bereik vaak verandert, zoals vaak het geval is bij maandelijkse verkopen of lopende totalen. Door een invoercel de grens van het dynamische bereik te laten bepalen, kunt u snel aanpassen aan bijgewerkte gegevens zonder uw formule opnieuw te schrijven.

Selecteer hiervoor een lege cel, zoals cel C4, en voer de volgende formule in:

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

Druk vervolgens op de Enter-toets om het resulterende gemiddelde te zien.

Deze formule past het bereik automatisch aan, zodat alle cellen vanaf A2 tot en met de rij die in C2 is opgegeven, worden meegenomen. Wanneer de waarde in C2 verandert, past het gemiddelde zich direct aan. Zo blijft het bereik flexibel: ideaal voor dynamisch uitbreiden of inkrimpen bij nieuwe gegevens of wanneer u een specifieke subset wilt analyseren.

Opmerkingen:

(1) In deze formule =IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2))): A2 vertegenwoordigt de eerste cel van het te middelen bereik, en C2verwijst naar de cel die het rijnummer bevat van de laatste cel in het doelbereik. Pas deze verwijzingen aan op basis van uw eigen gegevensstructuur indien nodig. Zorg ervoor dat cel C2 naar een geldige rij verwijst, anders krijgt u onverwachte resultaten of „#N/B".

(2) Als alternatief kunt u het volgende gebruiken:

=AVERAGE(INDIRECT("A2:A"&C2))

Deze methode is net zo effectief, omdat ze een tekstverwijzing naar het bereik maakt die INDIRECT vervolgens dynamisch interpreteert. Wees echter voorzichtig bij het gebruik van INDIRECT met gesloten werkmappen of grote datasets, aangezien dit de berekeningssnelheid kan vertragen en minder efficiënt is dan INDEX voor vluchtige gegevens.

Praktische tip: Wanneer uw gegevens continu groeien (bijvoorbeeld door dagelijks nieuwe rijen toe te voegen), kunt u een AANTALARG- of AANTAL-functie gebruiken om automatisch de bovengrens van de celverwijzing in te stellen. Zo blijft uw dynamische bereik altijd actueel.

Toepasselijke scenario’s: dagelijkse gegevenslogs, tijdreeksinvoer of elke analyse waarbij het begin of einde van het bereik wordt bepaald door gebruikersinvoer of een samenvattende cel. Voordelen: direct en zonder extra hulpmiddelen. Beperking: vereist handmatige aanpassing van de formule wanneer rijlocaties aanzienlijk veranderen.

In situaties waarin uw dynamische bereik niet op basis van positie, maar op basis van specifieke criteria (zoals een regio, categorie of een door de gebruiker gedefinieerd label) wordt bepaald, kunt u dynamische benoemde bereiken slim combineren met functies zoals INDIRECT om uw berekeningen automatisch aan te passen. Dit is bijzonder handig voor interactieve dashboards waar gebruikers uit een keuzelijst selecteren en direct de bijbehorende gemiddelden zien.

Groepeer eerst uw dataset op basis van koprijen of -kolommen. Zo doet u dat:

1. Selecteer het volledige gebied (bijvoorbeeld A1:D11) en klik op de Maken vanuit Selectie-knop in het Naambeheerder-deelvenster. Schakel in het pop-updialoogvenster zowel Bovenste rij als Meest linkse kolom in en klik op OK. Deze stap wijst automatisch benoemde bereiken toe aan gegevens in rijen en kolommen, waardoor verwijzingen in formules een stuk eenvoudiger worden.

2. Voer in uw gekozen lege cel de volgende formule in:

=AVERAGE(INDIRECT(G2))

Hierbij is G2 de criteriacel waarin gebruikers de naam van de rij- of kolomkop invoeren of selecteren. Zodra G2 verandert (bijvoorbeeld van „Regio1” naar „Regio2”), berekent de formule dynamisch het gemiddelde voor het bijbehorende bereik. Zorg er altijd voor dat de invoer in G2 exact overeenkomt met de gedefinieerde namen — inclusief hoofdletters — om #VERW!-fouten te voorkomen.

Ideaal voor: rapportagedashboards en criteria-gestuurde analyses. Voordelen: stelt u in staat om via gebruikersinteractie zeer flexibele, dynamische rapportage of analyse op één cel uit te voeren. Beperking: vereist correct naambeheer en consistente invoerwaarden.

Tel/sumeer/bereken automatisch het gemiddelde van cellen op basis van Vulkleur in Excel

Soms markeert u cellen op basis van hun vulkleur en telt, sommeert of berekent u later het gemiddelde van deze cellen. De Tellen op kleur-functie van Kutools voor Excel helpt u hierbij eenvoudig en snel.

Voor geavanceerd dynamisch gedrag – zoals het middelen van de laatste N rijen, het berekenen van een gemiddelde op basis van meerdere dynamische criteria of zelfs het combineren van gegevens uit meerdere werkbladen – kunt u een aangepaste VBA-macro ontwikkelen. Deze aanpak is vooral geschikt wanneer ingebouwde formules te complex worden voor uw situatie of wanneer u automatisering nodig hebt die meebeweegt met frequent veranderende gegevensstructuren.

U wilt bijvoorbeeld het gemiddelde berekenen van de laatste N rijen in kolom A, waarbij N door de gebruiker wordt opgegeven, of het gemiddelde nemen van waarden uit niet-aaneengesloten cellen binnen een door de gebruiker geselecteerd beperkt bereik.

1. Ga naar Ontwikkelaarshulpmiddelen > Visual Basic om de Microsoft Visual Basic for Applications-editor te openen. Selecteer vervolgens Invoegen > Module en plak de volgende VBA-code:

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

2. Klik op de -knop om de macro uit te voeren. Voer in het pop-updialoogvenster het aantal laatste rijen in dat u wilt middelen (bijvoorbeeld 5, 10, enzovoort) en klik op OK. Het resultaat verschijnt in een berichtvenster.

Voor het berekenen van een gemiddelde met complexere voorwaarden—bijvoorbeeld op basis van specifieke criteria of gegevens uit meerdere werkbladen—kunt u de VBA-code eenvoudig aanpassen. Voeg bijvoorbeeld InputBoxen toe om een criteriumwaarde op te geven, of laat de code meerdere werkbladen doorlopen om het samenvoegbereik samen te stellen voordat het gemiddelde wordt berekend.

Deze aanpak biedt maximale flexibiliteit en kan complexe of herhalende dynamische gemiddelde-berekeningen automatiseren. Zorg er echter voor dat u macro’s inschakelt en deze methode alleen gebruikt in een vertrouwde werkmap om beveiligingsrisico’s te voorkomen. Sla uw werk op voordat u nieuwe macro’s uitvoert en overweeg back-ups te maken bij het automatiseren van wijzigingen.

Voordelen: Stelt automatisering mogelijk, ondersteunt complexe of grootschalige gegevensscenario’s en is nauwkeurig af te stemmen op zeer specifieke bedrijfslogica. Nadelen: Vereist basiskennis van VBA en procedures moeten worden bijgewerkt bij structurele wijzigingen.