De ultieme handleiding voor doorzoekbare keuzelijst in Excel

Het maken van keuzelijsten in Excel stroomlijnt gegevensinvoer en minimaliseert fouten. Maar bij grotere datasets wordt het scrollen door lange lijsten omslachtig. Zou het niet gemakkelijker zijn om gewoon te typen en snel uw item te vinden? Een "doorzoekbare keuzelijst" biedt deze voordelen. Deze handleiding begeleidt u door vier methoden om zo'n lijst in Excel in te stellen.

• Doorzoekbare keuzelijst in Excel 365
• Maak een doorzoekbare keuzelijst (voor Excel 2019 en later)
• Maak eenvoudig een doorzoekbare keuzelijst (voor alle Excel-versies)
• Maak een doorzoekbare keuzelijst met een combinatievak en VBA (complexer)

Video: Maak een doorzoekbare keuzelijst

Doorzoekbare keuzelijst in Excel 365

Excel 365 heeft een langverwachte functie aan zijn gegevensvalidatie keuzelijsten toegevoegd: de mogelijkheid om binnen de lijst te zoeken. Met de doorzoekbare functionaliteit kunnen gebruikers snel items vinden en selecteren op een efficiëntere manier. Nadat u de keuzelijst zoals gewoonlijk hebt ingevoegd, klikt u gewoon op een cel met een keuzelijst en begint u te typen. De lijst zal onmiddellijk filteren om overeen te komen met de getypte tekst.

In dit geval typ ik San in de cel en filtert de keuzelijst steden die beginnen met de zoekterm San, zoals San Francisco en San Diego. Daarna kunt u een resultaat selecteren met uw muis of de pijltjestoetsen gebruiken en op Enter drukken.

Maak een doorzoekbare keuzelijst (voor Excel 2019 en later)

Als u Excel 2019 of latere versies gebruikt, kan de methode in deze sectie ook worden gebruikt om een keuzelijst doorzoekbaar te maken in Excel.

Stel dat u een keuzelijst hebt gemaakt in cel A2 van Sheet2 (afbeelding rechts) met behulp van gegevens in het bereik A2:A8 van Sheet1 (afbeelding links), volg dan deze stappen om de lijst doorzoekbaar te maken.

Stap 1. Maak een hulpcolumn die de zoekitems bevat

Hier hebben we een hulpcolumn nodig om de items weer te geven die overeenkomen met uw brongegevens. In dit geval maak ik de hulpcolumn in kolom D van Sheet1.

1. Selecteer de eerste cel D1 in kolom D en voer de kolomkop in, zoals "Zoekresultaten" in dit geval.
2. Voer de volgende formule in cel D2 in en druk op Enter.

   =FILTER(A2:A8;ISNUMBER(SEARCH(Sheet2!A2;A2:A8));"Niet gevonden")

   

Stap 2: Herconfigureer de keuzelijst

1. Selecteer de keuzelijstcel (in dit geval selecteer ik cel A2 van Sheet2), ga vervolgens naar Gegevens > Gegevensvalidatie > Gegevensvalidatie.
   
2. In de Gegevensvalidatie dialoogvenster moet u als volgt configureren.
   1. Onder het tabblad Opties tab, klik op de knop in de Bron vak.
      
   2. Het dialoogvenster Gegevensvalidatie zal doorverwijzen naar Sheet1, selecteer de cel (bijv., D2) met de formule uit Stap 1, voeg een # symbool toe, en klik op de Sluiten -knop.
      
   3. Ga naar het Foutmelding tabblad, vink het selectievakje Foutmelding weergeven na het invoeren van ongeldige gegevens uit, en klik ten slotte op de OK-knop om de wijzigingen op te slaan.
      

Resultaat

De keuzelijst in cel A2 van Sheet2 is nu doorzoekbaar. Typ tekst in de cel, klik op de keuzelijstpil om de keuzelijst uit te vouwen, en u zult zien dat de lijst onmiddellijk wordt gefilterd om overeen te komen met de getypte tekst.

Maak eenvoudig een doorzoekbare keuzelijst (voor alle Excel-versies)

Gezien de verschillende beperkingen van de bovenstaande methoden, hier is een zeer effectief hulpmiddel voor u - Kutools voor Excel's Maak Keuzelijst Doorzoekbaar, Auto-popupfunctie. Deze functie is beschikbaar in alle Excel-versies en laat u eenvoudig het gewenste item in de keuzelijst zoeken met een eenvoudige instelling.

Na het downloaden en installeren van Kutools voor Excel, selecteer Kutools > Keuzelijst > Maak Keuzelijst Doorzoekbaar, Auto-popup om deze functie in te schakelen. In het dialoogvenster Maak de Keuzelijst Doorzoekbaar moet u:

1. Selecteer het bereik dat de keuzelijsten bevat die moeten worden ingesteld als doorzoekbare keuzelijsten.
2. Klik OK om de instellingen te voltooien.
   

Resultaat

Wanneer u op een keuzelijstcel in het opgegeven bereik klikt, verschijnt een lijstvak aan de rechterkant. Typ tekst om de lijst onmiddellijk te filteren, selecteer vervolgens een item of gebruik de pijltjestoetsen en druk op Enter om het toe te voegen aan de cel.

Maak een doorzoekbare keuzelijst met een combinatievak en VBA (complexer)

Als u eenvoudig een doorzoekbare keuzelijst wilt maken zonder een specifiek type keuzelijst te specificeren. Deze sectie biedt een alternatieve aanpak: het gebruik van een combinatievak met VBA-code om de taak uit te voeren.

Stel dat u een lijst met landnamen hebt in kolom A zoals weergegeven in de onderstaande schermafbeelding, en nu wilt u ze gebruiken als de brongegevens van de doorzoekbare keuzelijsten, kunt u als volgt te werk gaan om het te doen.

U moet een combinatievak invoegen in plaats van een gegevensvalidatie keuzelijst in uw werkblad.

1. Als de Ontwikkelaar tab niet wordt weergegeven op de lint, kunt u de Ontwikkelaar tab als volgt inschakelen.
   1. In Excel 2010 of latere versies, klik Bestand > Opties. En in het dialoogvenster Excel Opties, klik Aanpassen Lint in het linkerpaneel. Ga naar de Aanpassen van het Lint lijstvak, vink het vak Ontwikkelaar aan, en klik vervolgens op de OK-knop. Zie schermafbeelding:
      
   2. In Excel 2007, klik Office-knop > Excel Opties. In het dialoogvenster Excel Opties, klik Populair in het linkerpaneel, vink het vak Toon Ontwikkelaar-tabblad in het Lint aan, en klik ten slotte op de OK-knop.
      
2. Na het tonen van het Ontwikkelaar tabblad, klik Ontwikkelaar > Invoegen > Combinatievak.
   
3. Teken een combinatievak in het werkblad, klik met de rechtermuisknop erop en selecteer Eigenschappen in het rechtermuisknopmenu.
   
4. In het Eigenschappen dialoogvenster moet u:
   1. Selecteer Nee in het veld AutoWoordSelectie;
   2. Specificeer een cel in het veld GekoppeldeCel. In dit geval voeren we A12 in;
   3. Selecteer 2-fmMatchEntryNone in het veld MatchEntry;
   4. Typ DropDownList in het veld LijstVullenBereik;
   5. Sluit het dialoogvenster Eigenschappen. Zie schermafbeelding:
      
5. Schakel nu de ontwerpmodus uit door te klikken op Ontwikkelaar > Ontwerpmodus.
6. Selecteer een lege cel zoals C2, voer de onderstaande formule in en druk op Enter. Sleep vervolgens de automatische vulgreep naar beneden naar cel C9 om de cellen automatisch te vullen met dezelfde formule. Zie schermafbeelding:

   =--ISNUMBER(IFERROR(SEARCH($A$12;A2;1);""))

   
   Opmerkingen:

   1. $A$12 is de cel die u hebt gespecificeerd als de GekoppeldeCel in stap 4;
   2. Na het afronden van de bovenstaande stappen kunt u nu testen: voer een letter C in het combinatievak in, en dan kunt u zien dat de formulecellen die verwijzen naar de cellen die het teken C bevatten, worden gevuld met het nummer 1.
7. Selecteer cel D2, voer de onderstaande formule in en druk op Enter. Sleep vervolgens de automatische vulgreep naar beneden naar cel D9.

   =ALS(C2=1;AANTAL.ALS($C$2:C2;1);"")

   
8. Selecteer cel E2, voer de onderstaande formule in en druk op Enter. Sleep vervolgens de automatische vulgreep naar beneden naar E9 om dezelfde formule toe te passen.

   =ALS.FOUT(INDEX($A$2:$A$9;VERGELIJKEN(RIJEN($D$2:D2);$D$2:$D$9;0));"")

   
9. Nu moet u een naamgebied creëren. Klik op Formules > Naam Definiëren.
   
10. In het Nieuwe Naam dialoogvenster, typ DropDownList in het Naam vak, voer de onderstaande formule in het Verwijst naar vak, en klik vervolgens op de OK knop.

    =$E$2:INDEX($E$2:$E$9;MAX($D$2:$D$9);1)
    

    
11. Schakel nu de ontwerpmodus in door te klikken op Ontwikkelaar > Ontwerpmodus. Dubbelklik vervolgens op het combinatievak om het Microsoft Visual Basic for Applications-venster te openen.
12. Kopieer en plak de onderstaande VBA-code in de Code-editor.
    
    VBA-code: maak keuzelijst doorzoekbaar

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Druk op de Alt + Q toetsen om het Microsoft Visual Basic for Applications-venster te sluiten.

Vanaf nu, wanneer een teken wordt ingevoerd in het combinatievak, zal het een fuzzy search uitvoeren en vervolgens de relevante waarden in de lijst weergeven.

Gerelateerde artikelen: