Hoe berekent u het gemiddelde van gefilterde cellen of lijsten in Excel?

Wanneer u de GEMIDDELDE-functie rechtstreeks gebruikt op een gefilterde dataset, berekent deze nog steeds het gemiddelde van alle cellen in het opgegeven bereik — inclusief de rijen die verborgen zijn door het filter. Dit levert onnauwkeurige resultaten op als u alleen het gemiddelde van de zichtbare rijen wilt bepalen. Voor het echte gemiddelde van gefilterde (zichtbare) gegevens biedt de SUBTOTAAL-functie van Excel een slimme en efficiënte oplossing. Deze functie past haar berekening automatisch aan op basis van het filter en neemt geen verborgen rijen mee, waardoor ze perfect geschikt is voor dit doel.

Volg deze stappen om het gemiddelde te berekenen van alleen de gefilterde resultaten:

• Bepaal het bereik dat al uw gefilterde gegevens bevat in de kolom waarvan u het gemiddelde wilt berekenen (in dit voorbeeld: de waarden bevinden zich in)C12:C24 van de kolom Bedrag).
• Voer in een lege cel de volgende formule in:

=SUBTOTAL(1,C12:C24)

Deze formule berekent het gemiddelde van de zichtbare (gefilterde) cellen in het beperkte bereik ()C12:C24). De parameter 1 geeft SUBTOTAAL de opdracht om de GEMIDDELDE-functie te gebruiken, en SUBTOTAAL negeert automatisch alle rijen die verborgen zijn door filteren.

Druk na het typen van de formule op Enter. U ziet dan direct het gemiddelde van alleen de zichtbare rijen verschijnen, zoals hieronder wordt weergegeven:

Alleen zichtbare cellen optellen/aantal bepalen/gemiddelde berekenen, waarbij verborgen of gefilterde cellen/rijen/kolommen worden genegeerd

Standaard SOM-, AANTAL- of GEMIDDELDE-functies in Excel berekenen alle cellen binnen het opgegeven bereik, ongeacht of ze zichtbaar of verborgen zijn door filters of handmatig verborgen rijen/kolommen. Voor een robuustere aanpak van dergelijke situaties kunt u Kutools voor Excel gebruiken. Met de specifieke functies SOMZICHTBAAR, AANTALZICHTBAAR en GEMIDDELDEZICHTBAAR berekent u eenvoudig totalen, aantallen en gemiddelden van alleen de daadwerkelijk zichtbare cellen in elk bereik – waarbij zowel gefilterde als handmatig verborgen cellen, rijen of kolommen automatisch worden uitgesloten. Deze functionaliteit helpt fouten te voorkomen in complexe werkbladen en bespaart tijd ten opzichte van het gebruik van ingewikkelde formules of aangepaste code.

Gefilterde gegevens/lijst middelen met de AGGREGAAT-functie

Als u Excel 2010 of nieuwer gebruikt, biedt de AGGREGAAT-functie nog meer flexibiliteit dan SUBTOTAAL voor het berekenen van gemiddelden op gefilterde gegevens, met extra opties om fouten en verborgen rijen te negeren. Zo past u deze toe:

• Voer in een lege cel de volgende formule in (ervan uitgaande dat uw gefilterde gegevens zich bevinden in C12:C24):

=AGGREGATE(1,5, C12:C24)

• Het eerste argument ()1) geeft aan dat de GEMIDDELDE-functie moet worden gebruikt, net als bij SUBTOTAAL.
• Het tweede argument ()5) instrueert AGGREGAAT om verborgen rijen (door filteren) en fouten te negeren.

Druk op Enter nadat u de formule hebt getypt om direct het gemiddelde van de zichtbare rijen in uw filterbereik weer te geven. Wilt u de methode voor het verbergen van rijen aanpassen of andere aggregatiefuncties gebruiken? Pas dan de tweede parameter dienovereenkomstig aan.

VBA-macro om alleen echt zichtbare cellen te middelen

Voor meer geavanceerde of aangepaste behoeften kunt u een eenvoudige VBA-macro gebruiken om alleen zichtbare (niet-verborgen en niet-gefilterde) cellen in een Bereik selecteren te middelen. Dit is vooral nuttig in werkbladen waarin gegevens op meerdere manieren worden verborgen. Zo kunt u dit doen:

1. Ga naar het tabblad Ontwikkelaar in Excel en selecteer Visual Basic om de VBA-editor te openen. Klik in de editor op Invoegen > Module om een nieuwe module te maken.
2. Kopieer en plak de volgende VBA-code in het modulevenster:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Sluit de VBA-editor. Ga terug naar uw werkblad, druk op Alt+F8, selecteer GemiddeldeZichtbareCellen en klik op Uitvoeren. Selecteer bij de prompt uw doelbereik Gegevensbereik. De macro berekent en toont vervolgens automatisch het gemiddelde van alleen de zichtbare (niet-gefilterde, niet-verborgen) numerieke cellen.

Gewogen gemiddelde

Gemiddelde/samengestelde jaarlijkse groeivoet berekenen

Voortschrijdend/glijdend gemiddelde

Lopend totaal / lopend gemiddelde

Gemiddelde veranderingssnelheid