Hoe telt u lege of niet-lege cellen binnen een gefilterd bereik in Excel?
Bij het werken met grote datasets in Excel gebruikt u filters om alleen de gegevens weer te geven die u wilt analyseren. Na het toepassen van een filter moet u mogelijk verder analyseren door te tellen hoeveel cellen leeg of niet-leeg zijn in de zichtbare (gefilterde) resultaten. Hoewel Excel standaardfuncties biedt voor het tellen van zichtbare cellen, is het snel en nauwkeurig tellen van uitsluitend lege of niet-leeg cellen in een gefilterde lijst lastig zonder de juiste aanpak. Deze aantallen correct bepalen is essentieel — bijvoorbeeld bij het opschonen van gegevens, het samenvatten van enquêteresultaten of het identificeren van onvolledige invoer in gefilterde rapporten. Dit artikel biedt diverse effectieve oplossingen voor deze uitdaging, met zowel formule- als VBA-gebaseerde methoden die aansluiten bij verschillende praktijksituaties. Daarnaast vindt u handige tips voor het aanpassen van elke oplossing aan specifieke scenario’s en suggesties voor het oplossen van veelvoorkomende problemen.
Tel lege cellen in Filterbereik met een formule
Tel niet-lege cellen in Filterbereik met een formule
Tel lege of niet-lege cellen in Filterbereik met VBA-code
Tel lege cellen in Filterbereik met een formule
Om alleen de lege cellen binnen een filterbereik te tellen, combineert u de functie SUBTOTAL met een hulpkolom. Deze methode is ideaal voor lijsten met gefilterde gegevens waarbij u verborgen rijen wilt negeren en een nauwkeurig aantal zichtbare lege waarden in een specifieke kolom nodig hebt.
Voer deze formule in een cel in waar u het aantal lege cellen wilt weergeven:
=SUBTOTAL(3;A2:A20)-SUBTOTAL(3;B2:B20)
Nadat u de formule hebt ingevoerd, drukt u op de toets Enter. Het resultaat toont het aantal zichtbare lege cellen in uw gefilterde lijst. Zie de onderstaande voorbeeldscreenshot:
Uitleg en gebruikstips:
- In deze formule
A2:A20moet een hulpkolom worden gebruikt waarvan gegarandeerd is dat deze geen lege cellen bevat (bijvoorbeeld een kolom met opeenvolgende nummers of unieke rij-identificatoren). B2:B20is de kolom waarin u de lege cellen wilt tellen.- De functie SUBTOTAL(3; bereik) geeft het aantal niet-lege, zichtbare cellen binnen het beperkte bereik terug. Door dit aantal niet-lege cellen in
B2:B20af te trekken van het totaal in de hulpkolom, krijgt u het aantal lege cellen in de gefilterde (zichtbare) gegevens. - Deze aanpak houdt alleen rekening met cellen die zichtbaar blijven nadat filters zijn toegepast; lege cellen in door filters verborgen rijen worden dus niet meegeteld.
- Pas de bereiken ()
A2:A20enB2:B20) aan zodat ze overeenkomen met uw werkelijke gegevens. De hulpkolom (A) moet in elke rij een waarde bevatten – als er lege cellen in de hulpkolom staan, kunnen de resultaten onjuist zijn.
Veelvoorkomende problemen en probleemoplossing:
- Als de hulpkolom verborgen of lege waarden bevat, klopt het aantal lege cellen niet. Controleer of uw hulpkolom volledig is ingevuld.
- Vergeet niet het formulebereik aan te passen wanneer u rijen toevoegt of verwijdert, anders worden gegevens aan de boven- of onderkant mogelijk overgeslagen.
- Zorg ervoor dat het filter daadwerkelijk is toegepast—anders telt de subtotaalfunctie alle rijen mee.
Tel niet-lege cellen in Filterbereik met een formule
Om het aantal zichtbare, niet-lege cellen in een gefilterde kolom te tellen, biedt Excel een eenvoudige oplossing met de functie SUBTOTAL. Deze methode is perfect wanneer u snel het aantal zichtbare, niet-lege invoerwaarden wilt weten na het toepassen van een filter—verborgen rijen worden automatisch genegeerd.
Typ de volgende formule in een lege cel waar u het aantal wilt weergeven:
=SUBTOTAL(102;B2:B20)
Druk vervolgens op de toets Enter. Excel toont direct het aantal zichtbare, niet-lege cellen in het beperkte bereik. Zie de voorbeeldscreenshot hieronder:
Uitleg en gebruikstips:
- Hierbij vertegenwoordigt
B2:B20de kolom die u analyseert. Pas dit bereik aan zodat het perfect aansluit bij uw dataset. - Het argument
102in de functie SUBTOTAL zorgt ervoor dat alleen zichtbare cellen worden geteld en negeert zowel verborgen rijen (door filters) als lege cellen. - Deze oplossing is ideaal voor standaard gefilterde lijsten binnen één kolom.
Voorzorgsmaatregelen:
- Deze methode telt geen cellen die er „leeg” uitzien maar een formule bevatten die een lege tekenreeks („") retourneert, aangezien Excel deze niet als echt leeg beschouwt.
- Wanneer u werkt met samengevoegde of onregelmatige bereiken, controleer dan altijd de nauwkeurigheid van de formule-uitvoer.
- Vergeet niet het bereik bij te werken wanneer er nieuwe rijen worden toegevoegd of gegevens worden verplaatst.
Tel lege of niet-lege cellen in Filterbereik met VBA-code
Voor geavanceerdere scenario’s — zoals wanneer uw filterbereik niet-aaneengesloten bereiken of meerdere kolommen omvat, of wanneer u een aanpasbare aanpak nodig hebt — kan een VBA-macro efficiënt alleen de zichtbare cellen doorlopen en nauwkeurig tellen hoeveel cellen leeg of niet-leeg zijn. Deze methode is bij uitstek geschikt als u flexibiliteit vereist, bijvoorbeeld voor het tellen van lege of niet-lege cellen over meerdere kolommen of binnen complexe gegevensindelingen die lastig zijn te verwerken met formules.
Voordelen en toepasselijke scenario’s:
- Kan in één handeling over meerdere kolommen en niet-aaneengesloten bereiken werken
- Past zich gemakkelijk aan filters aan – telt alleen zichtbare cellen
- Stelt u in staat om in één handeling te kiezen of u lege of niet-lege cellen wilt tellen
- Het meest geschikt voor gevorderde gebruikers die comfortabel zijn met het uitvoeren van macro’s en wanneer standaardformules ontoereikend zijn
Beperkingen:
- Vereist toegang tot de VBA-editor en ingeschakelde macro-machtigingen
- De tellogica beschouwt cellen met formuleresultaten van „" als leeg
1. Klik op het tabblad Ontwikkelaar op Visual Basic om de editor Microsoft Visual Basic for Applications te openen. Klik in het VBA-venster op Invoegen > Module om een nieuwe module te maken. Kopieer en plak vervolgens de onderstaande code in het modulevenster:
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. Druk op F5 om de code uit te voeren.
- Er verschijnt een prompt waarin u wordt gevraagd uw doelbereik te selecteren of te bevestigen.
- De macro vraagt of u lege cellen wilt tellen – klik op Ja. Klik op Nee als u niet-lege cellen wilt tellen.
- Het resultaat wordt weergegeven in een berichtvenster, met het aantal lege of niet-lege zichtbare cellen.
Gebruikstips en foutafhandeling:
- Als uw bereik samengevoegde cellen bevat, telt de macro nog steeds correct — maar let wel op eventuele overlappingen of misalignments in uw gefilterde gegevens.
- Als u de macro probeert uit te voeren zonder een geselecteerd bereik, wordt u gevraagd een geldig bereik te kiezen.
- Bij grote datasets kan de macro enkele seconden duren om te voltooien. Wacht op het venster met het resultaatbericht.
- Als u een foutmelding of dialoogvenster ziet met ‘Geen cellen gevonden’, controleer dan of uw selectie minstens één zichtbare rij bevat en of het filter actief is.
Samenvattende suggesties: Voor dagelijks gebruik zijn de formulemethoden in Excel eenvoudig, snel en vereisen ze geen extra instellingen. Als u te maken krijgt met meerdere kolommen, niet-aaneengesloten gegevens of meer flexibiliteit nodig hebt, is de VBA-oplossing uiterst aanpasbaar. Sla altijd uw werk op voordat u macro’s uitvoert en controleer of uw macro-beveiligingsinstellingen het uitvoeren van de code toestaan.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins