Hoe rijen automatisch te filteren op basis van celwaarde in Excel?

In Excel biedt de standaard Filterfunctie een snelle manier om gegevens te filteren op basis van criteria die u handmatig selecteert. In sommige werkstromen wilt u echter dat uw gegevens automatisch worden vernieuwd zodra u een waarde of voorwaarde invoert in een specifieke cel. Bijvoorbeeld, als u wilt dat de dataset direct wordt gefilterd op basis van een trefwoord of parameter die u invoert in een andere cel, gaat dit verder dan de native filtermogelijkheden van Excel. Dit soort dynamisch of “auto-filter” kan helpen bij het stroomlijnen van uw gegevensbeoordelingsproces, de tijd die wordt besteed aan handmatig filteren verminderen en de nauwkeurigheid verhogen door filtercriteria rechtstreeks te koppelen aan celwaarden. Als u zo'n automatisch filtersysteem wilt implementeren op basis van celinvoer, zijn er verschillende praktische oplossingen beschikbaar om dit resultaat te bereiken.

Automatisch rijen filteren op basis van celwaarde met een Excel-formuleoplossing

Automatisch rijen filteren op basis van de ingevoerde celwaarde met VBA-code

Automatisch rijen filteren op basis van celwaarde met een Excel-formuleoplossing

Als u liever een niet-macrobenadering gebruikt of VBA moet vermijden vanwege beveiligingsinstellingen of samenwerkingsproblemen, kunt u een "autofilter"-effect creëren door een combinatie van formules en ingebouwde filterfuncties in Excel te gebruiken. Deze oplossing werkt goed voor Microsoft 365, Microsoft 2019 en latere versies die dynamische arrayfuncties zoals FILTER ondersteunen. Het is vooral geschikt voor delen met collega's die mogelijk geen macro's hebben ingeschakeld, of voor lichtgewichtsjablonen waar installatievrije automatisering nodig is.

1. Selecteer een lege cel waar u wilt dat uw gefilterde resultaten beginnen (bijvoorbeeld plaats uw cursor in cel G2).

=FILTER(A2:C20, (A2:A20=E1) * (B2:B20=E2), "No match")

2. Nadat u de formule hebt ingevoerd in G2, drukt u op Enter. De gefilterde dataset verschijnt in een 'spill'-bereik dat begint bij cel G2. Wanneer u de waarden in E1 of E2 wijzigt, wordt de gefilterde lijst onmiddellijk vernieuwd.

Dit formulevoorbeeld gaat ervan uit dat uw hoofddata zich bevindt in A2:C20 en dat de filtercriteria worden ingevoerd in E1 (die overeenkomt met kolom A) en E2 (die overeenkomt met kolom B). Als u wilt filteren op slechts één kolom, kunt u de logische voorwaarde dienovereenkomstig vereenvoudigen. Het bericht "Geen overeenkomst" verschijnt als er geen rij overeenkomt met de ingevoerde criteria.

Tip: De FILTER-functie is beschikbaar in Microsoft 365 en sommige versies van Microsoft 19+. Voor oudere versies van Excel is dynamisch filteren via formules minder eenvoudig, en u kunt overwegen om hulptabellen te combineren met traditionele AutoFilter of geavanceerde filterfuncties. Controleer uw versie om compatibiliteit te garanderen.

Bij het gebruik van deze oplossing nemen de resultaten een nieuw gebied in uw werkblad in beslag, wat helpt om uw ruwe gegevens te behouden. De originele dataset blijft echter onaangetast—filters worden alleen toegepast op de weergegeven resultaten, niet op de status van de brongegevens.

Potentiële fouten: Als u een #NAAM? of #SPILL! fout ziet, controleer dan of u een compatibele Excel-versie gebruikt en dat er geen samengevoegde cellen in het resultaatgebied zijn. Vermijd ook het plaatsen van andere gegevens in het formule-spillagebied om blokkades te voorkomen.

Automatisch rijen filteren op basis van de celwaarde die u heeft ingevoerd met VBA-code

Stel dat u met een dataset werkt en de records automatisch wilt filteren op basis van criteria die u typt in specifieke cellen. Bijvoorbeeld, wanneer u uw gewenste voorwaarden invult in cel E1 en E2, wordt de data op het werkblad automatisch gefilterd om overeen te komen met die waarden, zoals hieronder geïllustreerd:

Om filtering op deze manier te automatiseren, kunt u een eenvoudige VBA-oplossing instellen. Deze methode wordt geactiveerd elke keer dat u de waarde bijwerkt in uw aangewezen filtercellen. Het is vooral nuttig voor dashboards, interactieve rapporten of sjablonen waarin gebruikers dynamisch filteren verwachten vanuit centrale parametercellen.

1. Ga naar het werkblad waarop u rijen automatisch wilt filteren op basis van de invoer in cellen.

2. Klik met de rechtermuisknop op het tabblad onderaan het Excel-venster en kies Broncode weergeven in het contextmenu. Plak in het venster Microsoft Visual Basic for Applications dat verschijnt de volgende VBA-code in het grote lege gebied (vaak codevenster of werkbladmodule genoemd), zoals hier te zien is:

VBA-code: Automatisch gegevens filteren volgens de ingevoerde celwaarde

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

Opmerking: In deze VBA-code verwijst A1:C20 naar uw gegevensbereik waarop u het filter wilt toepassen. E2 is de cel waarin u de filterwaarde typt (uw doelcriteria), en E1:E2 zijn de cellen die worden gebruikt als filtervoorwaarden. U kunt deze bereiken naar behoefte aanpassen—zorg ervoor dat ze nauwkeurig weerspiegelen waar uw gegevens en filtercriteria zich bevinden voor uw specifieke werkbladstructuur. Het is ook het beste om samengevoegde cellen te vermijden en ervoor te zorgen dat uw gegevensbereik headers bevat indien nodig voor nauwkeurig filteren.

3. Voer nu uw filtervoorwaarden in of wijzig ze in cel E1 en/of E2. Zodra u op Enter drukt, wordt de VBA-code automatisch uitgevoerd en wordt het aangewezen gegevensbereik gefilterd om alleen de rijen weer te geven die overeenkomen met uw invoercriteria.

Als het filter niet lijkt te werken nadat u een waarde heeft ingevoerd, controleer dan of macrofunctionaliteit is ingeschakeld in uw werkmap en controleer of de bereiken in de VBA-code correct overeenkomen met uw huidige werkbladlay-out. In multi-gebruiker scenario's, herinner anderen eraan dat macros moeten worden ingeschakeld om deze functie te laten werken.

Deze VBA-methode is vooral voordelig voor dynamische parametergestuurde dashboards, interactieve sjablonen voor gegevensinvoer of elk scenario waarin het handmatig opnieuw toepassen van filtercriteria inefficiënt zou zijn. Houd er echter rekening mee dat gebruikers problemen kunnen tegenkomen als ze macros uitschakelen, en VBA-gebaseerde oplossingen zijn specifiek voor het bestand waarin de code is toegevoegd.

Als uw scenario frequente criteriawijzigingen vereist door meerdere gebruikers die mogelijk verschillende Excel-beveiligingsinstellingen hebben, of als u van plan bent uw bestand breed te delen, kunt u overwegen om alternatieve formulegebaseerde oplossingen of Excel-invoegtoepassingen te gebruiken.

Demo: Automatisch rijen filteren op basis van de celwaarde die u heeft ingevoerd met VBA-code