Hoe het gemiddelde van een dynamisch bereik in Excel te berekenen?

Formules zijn een veelzijdige aanpak om het gemiddelde van een dynamisch bereik te berekenen wanneer het begin- of eindpunt van je bereik vaak verandert, zoals vaak gebeurt bij maandelijkse verkopen of lopende totalen. Door een invoercel te laten bepalen wat de grens van het dynamische bereik is, kun je snel aanpassen aan bijgewerkte gegevens zonder je formule te herschrijven.

Om dit in te stellen, selecteer 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 om alle cellen van A2 tot de rij aan te geven die wordt aangegeven door C2, dus wanneer de waarde in C2 verandert, verandert ook het gemiddelde bereik. Dit maakt het flexibel om het gemiddelde bereik dynamisch uit te breiden of in te korten naarmate nieuwe gegevens binnenkomen of wanneer je 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 bereik om te middelen, en C2 verwijst naar de cel die het rijnummer van de laatste cel van het doelbereik bevat. Pas deze referenties aan op basis van je eigen gegevensstructuur indien nodig. Zorg ervoor dat de cel C2 verwijst naar een geldige rij, anders krijg je onverwachte resultaten of "NA".

(2) Als alternatief kun je gebruiken:

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

Deze methode is net zo effectief omdat het een tekstreferentie maakt voor het bereik, dat INDIRECT dan dynamisch interpreteert. Wees echter voorzichtig bij het gebruik van INDIRECT met gesloten werkboeken of grote datasets, omdat dit de berekeningssnelheid kan beïnvloeden en minder efficiënt is dan INDEX voor vluchtige gegevens.

Praktische tip: Wanneer je gegevens continu groeien (zoals het toevoegen van nieuwe rijen elke dag), kun je een COUNTA- of COUNT-functie gebruiken om automatisch de bovengrens van de celreferentie in te stellen - dit zorgt ervoor dat je dynamische bereik altijd up-to-date gegevens dekt.

Toepasselijke scenario's: Dagelijkse datalogboeken, tijdreeksen of elke analyse waarbij het begin of einde van het bereik wordt bepaald door gebruikersinvoer of een samenvattingscel. Voordelen: Direct, vereist geen extra tools. Beperking: Vereist handmatige formuleaanpassing als rijlocaties drastisch veranderen.

Voor situaties waarin je dynamische bereik niet wordt gedefinieerd door positie, maar door specifieke criteria (zoals regio, categorie of door de gebruiker gedefinieerd label), kun je dynamische genoemde bereiken en functies zoals INDIRECT combineren om je berekeningen aan te passen. Dit is vooral handig voor dashboards waar gebruikers kiezen uit een dropdown en direct gerelateerde gemiddelden zien.

Groeper eerst je dataset per koprijen of kolommen. Zo doe je dat:

1. Selecteer het hele gebied (zoals A1:D11) en klik op de Maken vanuit Selectie knop in het Namen paneel. In het pop-up dialoogvenster vink je beide opties Bovenste rij en Meest linkse kolom aan, klik vervolgens op OK. Deze stap wijst automatisch namen toe aan gegevens in rijen en kolommen, wat het refereren in formules vereenvoudigt.

2. Voer in je gekozen lege cel deze formule in:

=AVERAGE(INDIRECT(G2))

Hier is G2 de criteriacel waar gebruikers de naam van de rij- of kolomkop typen of selecteren. Wanneer 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 hoofdlettergevoeligheid) om #VERW!-fouten te voorkomen.

Beste voor: Rapportagedashboards, criteria-gedreven analyses. Voordelen: Maakt zeer flexibele dynamische rapportage of single-celanalyse mogelijk door gebruikersinteractie. Beperking: Is afhankelijk van goede naambeheer en consistente invoerwaarden.

Cellen automatisch tellen/optellen/gemiddelde berekenen op basis van vulkleur in Excel

Soms markeer je cellen met een vulkleur en tel je deze cellen later op of bereken je het gemiddelde. Kutools voor Excel’s Tellen op kleur hulpmiddel kan je hierbij helpen met gemak.

Voor geavanceerd dynamisch gedrag, zoals het gemiddelde berekenen van de laatste N rijen, het gemiddelde berekenen op basis van meerdere dynamische criteria, of zelfs het combineren van gegevens uit meerdere werkbladen, kun je een aangepaste VBA-macro maken. Deze methode is vooral nuttig wanneer ingebouwde formules te complex worden voor jouw scenario, of wanneer je automatisering nodig hebt die zich aanpast aan frequent veranderende structuren.

Je kunt bijvoorbeeld het gemiddelde willen berekenen van de laatste N rijen in kolom A, waarbij N door de gebruiker wordt ingevoerd, of waarden middelen van niet-aaneengesloten, door de gebruiker gespecificeerde bereiken.

1. Ga naar Ontwikkelhulpprogramma's > 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-up dialoogvenster het aantal laatste rijen in dat je wilt middelen (zoals 5, 10, etc.) en druk op OK. Het resultaat verschijnt in een berichtenvenster.

Om te middelen met meer complexe voorwaarden (bijvoorbeeld op basis van criteria of vanuit meerdere werkbladen), kun je de VBA-code dienovereenkomstig aanpassen - bijvoorbeeld door InputBoxes toe te voegen voor een criteriumwaarde, of door verschillende werkbladen te doorlopen om bereiken te combineren voordat je middelt.

Deze aanpak biedt maximale flexibiliteit en kan complexe of herhalende dynamische gemiddeldeberekeningen automatiseren. Zorg er echter voor dat je macros inschakelt en deze methode gebruikt in een vertrouwd werkboek om veiligheidsrisico's te voorkomen. Sla je werk op voordat je nieuwe macros uitvoert en overweeg back-ups te maken bij het automatiseren van wijzigingen.

Voordelen: Automatisering mogelijk, handelt complexe of grote gegevensscenario's af, kan worden aangepast voor zeer specifieke bedrijfslogica. Nadelen: Vereist basiskennis van VBA en procedures moeten worden onderhouden als de structuur verandert.