Wie zählt man leere oder nicht leere Zellen in einem gefilterten Bereich in Excel?
Beim Arbeiten mit großen Datensätzen in Excel setzen Sie üblicherweise Filter ein, um nur die relevanten Daten anzuzeigen. Nach dem Filtern möchten Sie diese Daten oft weiter analysieren – beispielsweise, indem Sie zählen, wie viele Zellen in den sichtbaren (gefilterten) Ergebnissen leer oder nicht leer sind. Obwohl Excel grundlegende Funktionen zum Zählen sichtbarer Zellen bereitstellt, gestaltet sich das gezielte Zählen ausschließlich leerer oder ausschließlich nicht leerer Zellen in einer gefilterten Liste ohne die passende Methode als herausfordernd. Eine präzise Ermittlung dieser Werte ist jedoch entscheidend – etwa bei der Datenbereinigung, beim Zusammenfassen von Umfrageantworten oder beim Aufspüren unvollständiger Einträge in gefilterten Berichten. Dieser Artikel stellt mehrere effektive Lösungen für diese Aufgabe vor und deckt sowohl formelbasierte als auch VBA-basierte Ansätze ab, um unterschiedlichste praktische Anforderungen zu erfüllen. Zudem erhalten Sie wertvolle Hinweise zur Fehlerbehebung häufig auftretender Probleme sowie praktische Tipps, um jede Lösung optimal an Ihre spezifischen Szenarien anzupassen.
Leere Zellen in Filterbereich mithilfe einer Formel zählen
Nicht leere Zellen in Filterbereich mithilfe einer Formel zählen
Leere oder nicht leere Zellen in Filterbereich mithilfe von VBA-Code zählen
Leere Zellen in Filterbereich mithilfe einer Formel zählen
Um ausschließlich die leeren Zellen innerhalb eines Filterbereichs zu zählen, können Sie eine Kombination aus der SUBTOTAL-Funktion und einer Hilfsspalte verwenden. Diese Methode eignet sich ideal für Listen mit gefilterten Daten, bei denen ausgeblendete Zeilen ignoriert und eine genaue Anzahl sichtbarer leerer Einträge in einer Spalte ermittelt werden soll.
Geben Sie diese Formel in eine Zelle ein, in der die Anzahl leerer Zellen angezeigt werden soll:
=SUBTOTAL(3;A2:A20)-SUBTOTAL(3;B2:B20)
Drücken Sie nach der Eingabe der Formel die Enter-Taste. Das Ergebnis zeigt die Anzahl der sichtbaren leeren Zellen in Ihrer gefilterten Liste an. Ein Beispiel finden Sie in der folgenden Abbildung:
Erläuterung und Nutzungstipps:
- In dieser Formel
A2:A20sollte eine Hilfsspalte verwendet werden, die garantiert keine leeren Zellen enthält – beispielsweise eine Spalte mit fortlaufenden Nummern oder eindeutigen Zeilenkennungen. B2:B20ist die Spalte, in der Sie die leeren Zellen zählen möchten.- Die SUBTOTAL(3; Bereich)-Funktion gibt die Anzahl der nicht leeren, sichtbaren Zellen innerhalb des begrenzten Bereichs zurück. Indem Sie hier die Anzahl der nicht leeren Zellen in
B2:B20von der Gesamtanzahl in der Hilfsspalte abziehen, erhalten Sie die Anzahl der leeren Zellen in den gefilterten (sichtbaren) Daten. - Dieser Ansatz berücksichtigt ausschließlich Zellen, die nach Anwendung von Filtern sichtbar bleiben – leere Zellen in ausgeblendeten Zeilen werden daher nicht gezählt.
- Passen Sie unbedingt die Bereiche ()
A2:A20undB2:B20) an Ihre tatsächlichen Daten an. Die Hilfsspalte (A) muss in jeder Zeile Einträge enthalten – andernfalls können Leerstellen in Ihrer Hilfsspalte zu ungenauen Ergebnissen führen.
Häufige Probleme und Fehlerbehebung:
- Wenn die Hilfsspalte ausgeblendete oder leere Werte enthält, wird die Anzahl der leeren Zellen falsch ermittelt. Stellen Sie sicher, dass Ihre Hilfsspalte vollständig ist.
- Wenn Sie Zeilen hinzufügen oder entfernen, passen Sie den Formelbereich entsprechend an – andernfalls werden möglicherweise Daten am Anfang oder Ende nicht berücksichtigt.
- Stellen Sie sicher, dass der Filter tatsächlich angewendet wurde – andernfalls berücksichtigt die Teilsumme alle Zeilen.
Nicht leere Zellen in Filterbereich mithilfe einer Formel zählen
Um die Anzahl der sichtbaren, nicht leeren Zellen in einer gefilterten Spalte zu zählen, bietet Excel eine einfache Lösung mithilfe der SUBTOTAL-Funktion. Diese Methode ist ideal, wenn Sie nur die Anzahl der nicht leeren Einträge ermitteln möchten, die nach dem Filtern sichtbar bleiben – ausgeblendete Zeilen werden dabei automatisch ignoriert.
Geben Sie zur Verwendung dieser Lösung die folgende Formel in eine leere Zelle ein, in der die Anzahl angezeigt werden soll:
=SUBTOTAL(102;B2:B20)
Drücken Sie anschließend die Enter-Taste. Excel zeigt sofort die Anzahl der sichtbaren, nicht leeren Zellen im begrenzten Bereich an. Ein Beispiel finden Sie in der folgenden Abbildung:
Erläuterung und Nutzungstipps:
- Hierbei steht
B2:B20für die Spalte, die Sie analysieren. Passen Sie diesen Bereich an Ihren Datensatz an. - Das
102-Argument in der SUBTOTAL-Funktion zählt ausschließlich sichtbare Zellen und ignoriert dabei sowohl ausgeblendete Zeilen (durch Filter) als auch leere Zellen. - Diese Lösung eignet sich ideal für standardmäßige gefilterte Listen innerhalb einer einzelnen Spalte.
Vorsichtsmaßnahmen:
- Bei dieser Methode gelten Zellen nicht als leer, wenn sie zwar optisch „leer“ erscheinen, aber Formeln enthalten, die einen leeren Text („") zurückgeben – denn Excel behandelt sie nicht als wirklich leer.
- Wenn Sie mit zusammengeführten oder unregelmäßigen Bereichen arbeiten, überprüfen Sie die Formelausgabe auf Genauigkeit.
- Denken Sie daran, den Bereich zu aktualisieren, sobald neue Zeilen hinzugefügt oder Daten verschoben werden.
Leere oder nicht leere Zellen in Filterbereich mithilfe von VBA-Code zählen
Für anspruchsvollere Szenarien – etwa wenn Ihr Filterbereich nicht zusammenhängende Bereiche, mehrere Spalten oder einen anpassbaren Ansatz erfordert – kann ein VBA-Makro effizient nur die sichtbaren Zellen durchlaufen und präzise die Anzahl leerer oder nicht leerer Zellen liefern. Diese Methode ist besonders dann ideal, wenn Sie maximale Flexibilität benötigen, beispielsweise beim Zählen leerer oder nicht leerer Zellen über mehrere Spalten hinweg oder in komplexen Datenlayouts, die sich mit Formeln nur schwer bewältigen lassen.
Vorteile und Anwendungsszenarien:
- Kann in einem einzigen Vorgang mehrere Spalten und nicht zusammenhängende Bereiche verarbeiten
- Passt sich Filtern problemlos an – berücksichtigt ausschließlich sichtbare Zellen
- Ermöglicht Ihnen die Wahl, ob leere oder nicht leere Zellen in einem einzigen Vorgang gezählt werden sollen
- Am besten geeignet für fortgeschrittene Anwender, die mit Makros vertraut sind und bei denen Standardformeln nicht ausreichen
Einschränkungen:
- Erfordert Zugriff auf den VBA-Editor und aktivierte Makroberechtigungen
- Die Zähllogik behandelt Zellen, die Formelergebnisse wie „" enthalten, als leer
1. Klicken Sie auf der Entwickler-Registerkarte auf Visual Basic, um den Microsoft Visual Basic for Applications-Editor zu öffnen. Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul zu erstellen. Kopieren Sie anschließend den folgenden Code in das Modulfenster:
Sub CountVisibleBlanksOrNonBlanks()
Dim rng As Range
Dim cell As Range
Dim countBlanks As Long
Dim countNonBlanks As Long
Dim resp As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the filtered range to analyze", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resp = MsgBox("Do you want to count BLANK cells? (Click No to count non-blank cells)", vbYesNo + vbQuestion, xTitleId)
countBlanks = 0
countNonBlanks = 0
For Each cell In rng.SpecialCells(xlCellTypeVisible)
If cell.Value = "" Then
countBlanks = countBlanks + 1
Else
countNonBlanks = countNonBlanks + 1
End If
Next cell
If resp = vbYes Then
MsgBox "Number of visible blank cells: " & countBlanks, vbInformation, xTitleId
Else
MsgBox "Number of visible non-blank cells: " & countNonBlanks, vbInformation, xTitleId
End If
End Sub2. Drücken Sie F5, um den Code auszuführen.
- Es erscheint eine Eingabeaufforderung, in der Sie aufgefordert werden, Ihren Zielbereich auszuwählen oder zu bestätigen.
- Das Makro fragt, ob Sie leere Zellen zählen möchten – klicken Sie in diesem Fall auf „Ja“. Andernfalls klicken Sie auf „Nein“, um nur nicht leere Zellen zu zählen.
- Das Ergebnis wird in einem Meldungsfeld angezeigt, das die Anzahl der sichtbaren leeren bzw. nicht leeren Zellen anzeigt.
Bedienungshinweise und Fehlerbehandlung:
- Auch wenn Ihr Bereich zusammengeführte Zellen enthält, zählt das Makro weiterhin korrekt – achten Sie jedoch auf mögliche Überschneidungen oder Fehlausrichtungen in Ihren gefilterten Daten.
- Wenn Sie das Makro ausführen, ohne zuvor einen Bereich auszuwählen, werden Sie aufgefordert, einen gültigen Bereich zu wählen.
- Bei großen Datensätzen benötigt das Makro möglicherweise einige Sekunden – bitte warten Sie auf die Ergebnis-Meldung.
- Wenn eine Fehlermeldung oder ein Dialog mit dem Hinweis „Keine Zellen gefunden“ angezeigt wird, vergewissern Sie sich, dass Ihre Auswahl mindestens eine sichtbare Zeile enthält und der Filter aktiviert ist.
Zusammenfassende Empfehlungen: Für den täglichen Gebrauch sind die Formelmethoden von Excel einfach und schnell – und erfordern keine zusätzliche Einrichtung. Sollten Sie jedoch auf Szenarien mit mehreren Spalten, nicht zusammenhängenden Daten oder einem erhöhten Bedarf an Flexibilität stoßen, bietet die VBA-Lösung maximale Anpassungsfähigkeit. Speichern Sie stets Ihre Arbeit, bevor Sie Makros ausführen, und stellen Sie sicher, dass Ihre Makrosicherheitseinstellungen die Codeausführung zulassen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins