Maak een Excel-vervolgkeuzelijst met zoeksuggesties

We gebruiken Google allemaal als onderdeel van onze dagelijkse routine. Een van de functies is zoeksuggestie, waarbij Google slim handelt en ons een lijst met suggesties geeft terwijl we aan het typen zijn.

In deze zelfstudie leert u hoe u een doorzoekbare vervolgkeuzelijst in Excel maakt, d.w.z. een vervolgkeuzelijst die de overeenkomende items toont terwijl u typt.

Hieronder vindt u een video van deze tutorial (voor het geval u liever een video bekijkt dan de tekst leest).

Doorzoekbare vervolgkeuzelijst in Excel

Voor deze tutorial gebruik ik de gegevens van Top 20-landen per BBP.

De bedoeling is om een ​​Excel-vervolgkeuzelijst te maken met een zoeksuggestiemechanisme, zodat het een vervolgkeuzelijst toont met de overeenkomende opties terwijl ik typ in de zoekbalk.

Iets zoals hieronder weergegeven:

Download hier het voorbeeldbestand om mee te volgen

Het maken van de doorzoekbare vervolgkeuzelijst in Excel zou een proces in drie delen zijn:

1. Het zoekvak configureren.
2. De gegevens instellen.
3. Een korte VBA-code schrijven om het te laten werken.

Stap 1 - Het zoekvak configureren

In deze eerste stap zal ik een keuzelijst met invoervak ​​gebruiken en deze zo configureren dat wanneer je erin typt, de tekst ook in realtime in een cel wordt weergegeven.

Hier zijn de stappen om dit te doen:

1. Ga naar het tabblad Ontwikkelaar -> Invoegen -> ActiveX-besturingselementen -> Combo Box (ActiveX-besturingselement).
   • Het is mogelijk dat u het tabblad Ontwikkelaar niet in het lint vindt. Standaard is het verborgen en moet het worden ingeschakeld. Klik hier om te weten hoe u het ontwikkelaarstabblad in het lint in Excel kunt krijgen.
2. Verplaats uw cursor naar het werkbladgebied en klik ergens. Het zal een combobox invoegen.
3. Klik met de rechtermuisknop op de keuzelijst en selecteer Eigenschappen.
4. Breng in het dialoogvenster Eigenschappen de volgende wijzigingen aan:
   • AutoWoordSelecteren: niet waar
   • gekoppelde cel: B3
   • ListFillRange: DropDownList (we zullen een benoemd bereik maken met deze naam in stap 2)
   • Wedstrijdinvoer: 2 - fmMatchEntryGeen

(Cel B3 is gekoppeld aan de Combo Box, wat betekent dat alles wat je typt in de Combo Box wordt ingevoerd in B3)

4. Ga naar het tabblad Ontwikkelaar en klik op Ontwerpmodus. Hiermee kunt u tekst invoeren in de keuzelijst. Omdat cel B3 is gekoppeld aan de keuzelijst met invoervak, wordt elke tekst die u invoert in de keuzelijst ook in realtime weergegeven in B3.

Stap 2 - De gegevens instellen

Nu het zoekvak helemaal is ingesteld, moeten we de gegevens op hun plaats krijgen. Het idee is dat zodra u iets in het zoekvak typt, het alleen die items toont die die tekst bevatten.

Om dit te doen, gebruiken we

• Drie helperkolommen.
• Eén dynamisch genoemd bereik.

Helper Kolom 1

Zet de volgende formule in cel F3 en sleep deze voor de hele kolom (F3:F22)

=--ISGETAL(IFERROR(ZOEKEN($B$3,E3,1),""))

Deze formule retourneert 1 wanneer de tekst in het keuzevak links in de naam van het land staat. Als u bijvoorbeeld UNI typt, worden alleen de waarden voor United Staten en United Kingdom zijn 1 en alle overige waarden zijn 0.

Helperkolom 2

Zet de volgende formule in cel G3 en sleep deze voor de hele kolom (G3:G22)

=ALS(F3=1,AANTAL.ALS($F$3:F3,1),"") 

Deze formule retourneert 1 voor de eerste keer dat de tekst van het invoervak ​​overeenkomt met de landnaam, 2 voor de tweede keer, 3 voor de derde enzovoort. Als u bijvoorbeeld UNI typt, wordt in cel G3 1 weergegeven als deze overeenkomt met de Verenigde Staten, en in G9 met 2 als deze overeenkomt met Verenigd Koninkrijk. De rest van de cellen zijn leeg.

Helperkolom 3

Zet de volgende formule in cel H3 en sleep deze voor de hele kolom (H3:H22)

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"") 

Deze formule stapelt alle overeenkomende namen op elkaar zonder lege cellen ertussen. Als u bijvoorbeeld UNI typt, worden in deze kolom 2 en 9 samen weergegeven en is de rest van de cel leeg.

Het dynamisch benoemde bereik maken

Nu de helperkolommen aanwezig zijn, moeten we het dynamische benoemde bereik maken. Dit benoemde bereik verwijst alleen naar die waarden die overeenkomen met de tekst die in de keuzelijst met invoervak ​​is ingevoerd. We zullen dit dynamische benoemde bereik gebruiken om de waarden in de vervolgkeuzelijst weer te geven.

Opmerking: In stap 1 hebben we DropDownList ingevoerd in de optie ListFillRange. Nu zullen we het benoemde bereik met dezelfde naam maken.

Hier zijn de stappen om het te maken:

1. Ga naar Formules -> Naambeheer.
2. Klik in het dialoogvenster Name-manager op Nieuw. Er wordt een dialoogvenster Nieuwe naam geopend.
3. Voer in het veld Naam DropDownList . in
4. Voer in het veld Verwijst naar de formule in: =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Stap 3 - De VBA-code aan het werk zetten

We zijn er bijna.

Het laatste deel is het schrijven van een korte VBA-code. Deze code maakt de vervolgkeuzelijst dynamisch, zodat de overeenkomende items/namen worden weergegeven terwijl u in het zoekvak typt.

Om deze code aan uw werkmap toe te voegen:

1. Klik met de rechtermuisknop op het tabblad Werkblad en selecteer Code weergeven.
2. Kopieer en plak in het VBA-venster de volgende code:

   Privé Sub ComboBox1_Change() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Einde sub

Dat is het!!

U bent helemaal klaar met uw eigen zoekbalk van het Google-type die overeenkomende items toont terwijl u erin typt.

Voor een beter uiterlijk en gevoel, kunt u cel B3 afdekken met de keuzelijst en alle hulpkolommen verbergen. Je kunt nu een beetje pronken met deze geweldige Excel-truc.

Download het bestand hier om mee te volgen

Wat denk je? Zou u deze vervolgkeuzelijst met zoeksuggesties in uw werk kunnen gebruiken? Laat me je mening weten door een reactie achter te laten.

Als je deze tutorial leuk vond, weet ik zeker dat je ook de volgende Excel-tutorials wilt:

• Dynamisch filter - Extraheer overeenkomende gegevens terwijl u typt.
• Gegevens extraheren op basis van een keuze uit een vervolgkeuzelijst.
• Afhankelijke vervolgkeuzelijsten maken in Excel.
• De ultieme gids voor het gebruik van de Excel VERT.ZOEKEN-functie.
• Meerdere selecties maken in een vervolgkeuzelijst in Excel.
• Een selectievakje invoegen en gebruiken in Excel