Dynamisch Excel-filterzoekvak (extraheer gegevens terwijl u typt)

Excel Filter is een van de meest gebruikte functionaliteiten als je met data werkt. In deze blogpost laat ik je zien hoe je een dynamisch Excel-filterzoekvak maakt, zodat het de gegevens filtert op basis van wat je in het zoekvak typt.

Iets zoals hieronder weergegeven:

Dit heeft een dubbele functionaliteit - u kunt de naam van een land selecteren in de vervolgkeuzelijst, of u kunt de gegevens handmatig invoeren in het zoekvak, en het zal u alle overeenkomende records tonen. Als u bijvoorbeeld "I" typt, krijgt u alle landnamen met het alfabet I erin.

Bekijk video - Een dynamisch Excel-filterzoekvak maken

Een dynamisch Excel-filterzoekvak maken

Dit Dynamic Excel-filter kan in 3 stappen worden aangemaakt:

1. Een unieke lijst met items krijgen (in dit geval landen). Dit zou worden gebruikt bij het maken van de vervolgkeuzelijst.
2. Het zoekvak maken. Hier heb ik een Combo Box (ActiveX Control) gebruikt.
3. De gegevens instellen. Hier zou ik drie hulpkolommen met formules gebruiken om de overeenkomende gegevens te extraheren.

Hier is hoe de onbewerkte gegevens eruit zien:

NUTTIGE TIP: Het is bijna altijd een goed idee om uw gegevens om te zetten in een Excel-tabel. U kunt dit doen door een willekeurige cel in de gegevensset te selecteren en de sneltoets Control + T te gebruiken.

Stap 1 - Een unieke lijst met items krijgen

1. Selecteer alle landen en plak deze in een nieuw werkblad.
2. Selecteer de landenlijst -> Ga naar gegevens -> Duplicaten verwijderen.
3. Selecteer in het dialoogvenster Duplicaten verwijderen de kolom waarin u de lijst hebt en klik op OK. Hiermee worden duplicaten verwijderd en krijgt u een unieke lijst zoals hieronder weergegeven:
4. Een extra stap is het maken van een benoemd bereik voor deze unieke lijst. Om dit te doen:
   • Ga naar het tabblad Formule -> Definieer naam
   • In Dialoogvenster Naam definiëren:
     • Naam: Landenlijst
     • Toepassingsgebied: werkboek
     • Verwijst naar: =UniqueList!$A$2:$A$9 (ik heb de lijst in een apart tabblad met de naam UniqueList in A2:A9. U kunt verwijzen naar waar uw unieke lijst zich ook bevindt)

OPMERKING: Als u de methode 'Duplicaten verwijderen' gebruikt en uw gegevens uitbreidt om meer records en nieuwe landen toe te voegen, moet u deze stap opnieuw herhalen. Als alternatief kunt u ook een formule bedenken om dit proces dynamisch te maken.

Stap 2 - Het dynamische Excel-filterzoekvak maken

Om deze techniek te laten werken, moeten we een 'zoekvak' maken en dit aan een cel koppelen.

We kunnen de Combo Box in Excel gebruiken om dit zoekvakfilter te maken. Op deze manier zou elke keer dat u iets in de keuzelijst invoert, dit ook in realtime in een cel worden weergegeven (zoals hieronder weergegeven).

Hier zijn de stappen om dit te doen:

1. Ga naar Developer Tab -> Controls -> Insert -> ActiveX Controls -> Combo Box (ActiveX Controls).
   • Als het tabblad Ontwikkelaars niet zichtbaar is, volgen hier de stappen om dit in te schakelen.
2. Klik ergens op het werkblad. Het zal de Combo Box invoegen.
3. Klik met de rechtermuisknop op Combo Box en selecteer Eigenschappen.
4. Breng in het venster Eigenschappen de volgende wijzigingen aan:
   • Gekoppelde cel: K2 (u kunt elke cel kiezen waar u wilt dat de invoerwaarden worden weergegeven. We zullen deze cel gebruiken bij het instellen van de gegevens).
   • ListFillRange: CountryList (dit is het benoemde bereik dat we in stap 1 hebben gemaakt. Dit zou alle landen in de vervolgkeuzelijst tonen).
   • MatchEntry: 2-fmMatchEntryNone (dit zorgt ervoor dat een woord niet automatisch wordt ingevuld terwijl u typt)
5. Met de keuzelijst geselecteerd, ga je naar het tabblad Ontwikkelaars -> Besturingselementen -> Klik op Ontwerpmodus (hiermee verlaat je de ontwerpmodus en nu kun je alles typen in de keuzelijst. Nu, wat je ook typt, wordt weerspiegeld in cel K2 live)

Stap 3 - De gegevens instellen

Ten slotte koppelen we alles door middel van helperkolommen. Ik gebruik hier drie hulpkolommen om de gegevens te filteren.

Hulpkolom 1: Voer het serienummer van alle records in (20 in dit geval). U kunt hiervoor de formule ROWS() gebruiken.

Hulpkolom 2: In helperkolom 2 controleren we of de tekst die in het zoekvak is ingevoerd overeenkomt met de tekst in de cellen in de landkolom.

Dit kan met een combinatie van ALS, ISNUMBER en SEARCH functies.

Hier is de formule:

=ALS(ISGETAL(ZOEKEN($K$2,D4)),E4,"")

Deze formule zoekt naar de inhoud in het zoekvak (dat is gekoppeld aan cel K2) in de cel met de landnaam.

Als er een overeenkomst is, retourneert deze formule het rijnummer, anders wordt een spatie geretourneerd. Als de keuzelijst bijvoorbeeld de waarde 'VS' heeft, hebben alle records met land als 'VS' het rijnummer en is de rest leeg ("")

Hulpkolom 3: In helperkolom 3 moeten we alle rijnummers van Helperkolom 2 op elkaar gestapeld krijgen. Om dit te doen, kunnen we een combinatie gebruiken als IFERROR en SMALL formules. Hier is de formule:

=IFERROR(KLEIN($F$4:$F$23,E4),"")

Deze formule stapelt alle overeenkomende rijnummers op elkaar. Als de Combo Box bijvoorbeeld de waarde US heeft, worden alle rijnummers met 'US' erin gestapeld.

Als we nu de rijnummers op elkaar hebben gestapeld, hoeven we alleen de gegevens in deze rijnummers te extraheren. Dit kan eenvoudig worden gedaan met behulp van de indexformule (voeg deze formule in waar u de gegevens wilt extraheren. Kopieer deze naar de cel linksboven waar u de gegevens wilt extraheren en sleep deze vervolgens naar beneden en naar rechts).

=IFERROR(INDEX($B$4:$D$23,$G4,KOLOMMEN($I$3:I3)),"")

Deze formule bestaat uit 2 delen:
INHOUDSOPGAVE - Dit extraheert de gegevens op basis van het rijnummer.
IFERROR - Dit geeft blanco terug als er geen gegevens zijn.

Hier is een momentopname van wat je uiteindelijk krijgt:

De Combo Box is zowel een drop-down als een zoekvak. U kunt de originele gegevens en helperkolommen verbergen om alleen de gefilterde records weer te geven. U kunt de onbewerkte gegevens en helperkolommen ook in een ander blad plaatsen en dit dynamische Excel-filter in een ander werkblad maken.

Wees creatief! Probeer enkele variaties

U kunt het proberen en aanpassen aan uw vereisten. Misschien wilt u meerdere Excel-filters maken in plaats van één. U wilt bijvoorbeeld records filteren waarin Verkoopvertegenwoordiger Mike is en Land Japan. Dit kan precies volgens dezelfde stappen met enige wijziging in de formule in helperkolommen.

Een andere variatie zou kunnen zijn om gegevens te filteren die beginnen met de tekens die u invoert in de keuzelijst. Als u bijvoorbeeld 'I' invoert, wilt u misschien landen extraheren die met I beginnen (in vergelijking met de huidige constructie waar u ook Singapore en de Filippijnen zou krijgen omdat het het alfabet I bevat).

Zoals altijd zijn de meeste van mijn artikelen geïnspireerd op de vragen/antwoorden van mijn lezers. Ik zou graag uw feedback ontvangen en van u leren. Laat uw mening achter in het opmerkingengedeelte.

Opmerking: als u Office 365 gebruikt, kunt u de functie FILTER gebruiken om de gegevens snel te filteren terwijl u typt. Het is eenvoudiger dan de methode die in deze zelfstudie wordt getoond.