Wie mittelt man gefilterte Zellen/Liste in Excel?

Wenn Sie die MITTELWERT-Funktion direkt auf einen gefilterten Datensatz anwenden, berechnet sie weiterhin den Mittelwert aller Zellen im angegebenen Bereich – einschließlich der durch den Filter ausgeblendeten Zeilen. Das führt zu ungenauen Ergebnissen, sobald Sie ausschließlich die sichtbaren Zeilen mitteln möchten. Für den korrekten Durchschnitt der gefilterten (also sichtbaren) Daten bietet Excel die TEILERGEBNIS-Funktion als effiziente Lösung: Sie passt ihre Berechnung automatisch an aktive Filter und ausgeblendete Zeilen an und ist damit ideal für diesen Zweck geeignet.

Führen Sie die folgenden Schritte aus, um den Durchschnitt nur für die gefilterten Ergebnisse zu berechnen:

• Identifizieren Sie den Bereich, der alle Ihre gefilterten Daten in der Spalte enthält, deren Werte Sie mitteln möchten (in diesem Beispiel gehen wir davon aus, dass sich die Werte in)C12:C24 der Betrag-Spalte befinden).
• Geben Sie in eine leere Zelle die folgende Formel ein:

=SUBTOTAL(1,C12:C24)

Diese Formel berechnet den Durchschnitt der sichtbaren (gefilterten) Zellen im Bereich C12:C24. Der Parameter 1 weist TEILERGEBNIS an, die MITTELWERT-Funktion zu verwenden – und TEILERGEBNIS ignoriert automatisch alle durch Filtern ausgeblendeten Zeilen.

Drücken Sie nach der Eingabe der Formel Enter. Der Durchschnittswert der sichtbaren Zeilen wird sofort angezeigt, wie unten dargestellt:

Nur sichtbare Zellen summieren/zählen/mitteln unter Ignorierung ausgeblendeter oder gefilterter Zellen/Zeilen/Spalten

Die herkömmlichen Excel-Funktionen wie SUMME, ANZAHL oder MITTELWERT berechnen sämtliche Zellen innerhalb des angegebenen Bereichs – unabhängig davon, ob diese sichtbar oder durch Filter bzw. manuelles Ausblenden ausgeblendet sind. Für eine präzisere und zuverlässigere Auswertung empfehlen wir Kutools für Excel. Mit den speziellen Funktionen SUMMESICHTBAR, ANZAHL_SICHTBAR und MITTELWERT_SICHTBAR berechnen Sie mühelos Summen, Anzahlen und Durchschnitte ausschließlich der tatsächlich sichtbaren Zellen – egal, ob Zeilen oder Spalten gefiltert oder manuell ausgeblendet wurden. So vermeiden Sie Fehler in komplexen Tabellen und sparen wertvolle Zeit gegenüber umständlichen Formeln oder benutzerdefiniertem Code.

Gefilterte Daten/Liste mit der AGGREGAT-Funktion mitteln

Wenn Sie Excel 2010 oder eine neuere Version verwenden, bietet die AGGREGAT-Funktion noch mehr Flexibilität als TEILERGEBNIS bei der Berechnung von Durchschnitten in gefilterten Daten – plus zusätzliche Optionen für den Umgang mit Fehlern und ausgeblendeten Zeilen. So wenden Sie sie an:

• Geben Sie in eine leere Zelle die folgende Formel ein (angenommen, Ihre gefilterten Daten befinden sich in C12:C24):

=AGGREGATE(1,5, C12:C24)

• Das erste Argument ()1) gibt die MITTELWERT-Funktion an – genau wie bei TEILERGEBNIS.
• Das zweite Argument ()5) weist AGGREGAT an, ausgeblendete Zeilen (durch Filterung) sowie Fehler zu ignorieren.

Drücken Sie nach der Eingabe der Formel Enter, um sofort den Durchschnitt der sichtbaren Zeilen in Ihrem gefilterten Bereich anzuzeigen. Möchten Sie die Formel für andere Arten des Zeilenausblendens oder für andere Aggregatfunktionen anpassen, ändern Sie einfach den zweiten Parameter entsprechend.

VBA-Makro zum Mitteln ausschließlich wirklich sichtbarer Zellen

Für fortgeschrittene oder individuelle Anforderungen können Sie ein einfaches VBA-Makro verwenden, um ausschließlich sichtbare (nicht ausgeblendete und nicht gefilterte) Zellen in einem Bereich auswählen zu mitteln. Dies ist besonders nützlich in Arbeitsblättern, in denen Daten auf unterschiedliche Weise ausgeblendet werden. So geht’s:

1. Wechseln Sie zur Entwicklertools-Registerkarte in Excel und wählen Sie Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im Editor auf Einfügen > Modul, um ein neues Modul zu erstellen.
2. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das Modulfenster ein:

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. Schließen Sie den VBA-Editor. Drücken Sie in Ihrem Arbeitsblatt Alt+F8, wählen Sie AverageVisibleCells und klicken Sie auf Ausführen. Wählen Sie bei Aufforderung Ihren Zielbereich bzw. Datenbereich aus. Das Makro berechnet dann den Durchschnitt ausschließlich der aktuell sichtbaren (nicht gefilterten, nicht ausgeblendeten) numerischen Zellen und zeigt ihn an.

Gewogener Durchschnitt

Durchschnittliche/jährliche Wachstumsrate berechnen

Gleitender/rollierender Durchschnitt

Laufende Summe / laufender Durchschnitt

Durchschnittliche Änderungsrate