Skapa en Excel -rullgardinslista med sökförslag

Vi använder alla Google som en del av vår dagliga rutin. En av dess funktioner är sökförslag, där Google agerar smart och ger oss en lista med förslag medan vi skriver.

I den här självstudien lär du dig hur du skapar en sökbar listruta i Excel-dvs en rullgardinslista som visar matchande objekt medan du skriver.

Nedan finns en video av denna handledning (om du föredrar att titta på en video framför att läsa texten).

Sökbar rullgardinsmeny i Excel

För denna handledning använder jag data från de 20 bästa länderna efter BNP.

Avsikten är att skapa en Excel -rullgardinslista med en sökförslagsmekanism, så att den visar en nedrullning med matchningsalternativen när jag skriver i sökfältet.

Något som visas nedan:

För att följa med, ladda ner exempelfilen härifrån

Att skapa den sökbara listrutan i Excel skulle vara en tredelad process:

1. Konfigurera sökrutan.
2. Ställa in data.
3. Skriva en kort VBA -kod för att få det att fungera.

Steg 1 - Konfigurera sökrutan

I det här första steget kommer jag att använda en kombinationsruta och konfigurera den så att när du skriver in den reflekteras texten också i en cell i realtid.

Här är stegen för att göra detta:

1. Gå till fliken Utvecklare -> Infoga -> ActiveX -kontroller -> Kombinationsruta (ActiveX -kontroll).
   • Det finns en möjlighet att du kanske inte hittar utvecklarfliken i menyfliksområdet. Som standard är det dolt och måste aktiveras. Klicka här för att veta hur du får utvecklarfliken i menyfliksområdet i Excel.
2. Flytta markören till kalkylbladsområdet och klicka var som helst. Det kommer att infoga en kombinationsruta.
3. Högerklicka på kombinationsrutan och välj Egenskaper.
4. Gör följande ändringar i dialogrutan för egenskaper:
   • AutoWordVälj: Falsk
   • LinkedCell: B3
   • ListFillRange: DropDownList (vi skapar ett namngivet område med detta namn i steg 2)
   • MatchEntry: 2 - fmMatchEntryNone

(Cell B3 är länkad till kombinationsrutan, vilket innebär att allt du skriver i kombinationsrutan anges i B3)

4. Gå till fliken Utvecklare och klicka på Design Mode. Detta gör att du kan skriva in text i kombinationsrutan. Eftersom cell B3 är länkad till kombinationsrutan, kommer all text som du anger i kombinationsrutan också att återspeglas i B3 i realtid.

Steg 2 - Ställa in data

Nu när sökrutan är klar måste vi få data på plats. Tanken är att så snart du skriver något i sökrutan visar det bara de objekt som har den texten i den.

För att göra detta kommer vi att använda

• Tre hjälparpelare.
• Ett dynamiskt namnområde.

Hjälpkolumn 1

Sätt följande formel i cell F3 och dra den för hela kolumnen (F3: F22)

=-ISNUMBER (IFERROR (SÖK ($ B $ 3, E3,1), ""))

Denna formel returnerar 1 när texten i kombinationsrutan finns där i namnet på landet till vänster. Om du till exempel skriver UNI är det bara värdena för Unitedstater och United Kingdom är 1 och alla återstående värden är 0.

Hjälpkolumn 2

Sätt följande formel i Cell G3 och dra den för hela kolumnen (G3: G22)

= IF (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

Denna formel returnerar 1 för den första förekomsten där kombinationsruta -texten matchar landets namn, 2 för den andra förekomsten, 3 för den tredje och så vidare. Till exempel, om du skriver UNI, visar G3 -cell 1 när den matchar USA, och G9 visar 2 när den matchar Storbritannien. Resten av cellerna kommer att vara tomma.

Hjälpkolumn 3

Sätt följande formel i cell H3 och dra den för hela kolumnen (H3: H22)

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

Denna formel staplar ihop alla matchande namn utan några tomma celler mellan dem. Till exempel, om du skriver UNI, skulle den här kolumnen visa 2 och 9 tillsammans, och vila alla celler skulle vara tomma.

Skapa det dynamiska namngivna intervallet

Nu när hjälpkolumnerna är på plats måste vi skapa det dynamiska namngivna området. Det här namngivna intervallet hänvisar bara till de värden som matchar texten i kombinationsrutan. Vi kommer att använda detta dynamiska namngivna område för att visa värdena i listrutan.

Notera: I steg 1 angav vi DropDownList i alternativet ListFillRange. Nu kommer vi att skapa det namngivna intervallet med samma namn.

Här är stegen för att skapa det:

1. Gå till Formler -> Namnhanterare.
2. Klicka på Ny i dialogrutan namnhanterare. Det öppnar dialogrutan Nytt namn.
3. I namnfältet anger du DropDownList
4. I referenserna till fältet anger du formeln: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Steg 3 - Att sätta VBA -koden i arbete

Vi är nästan där.

Den sista delen är att skriva en kort VBA -kod. Denna kod gör rullgardinsmenyn dynamisk så att den visar matchande objekt/namn när du skriver i sökrutan.

Så här lägger du till den här koden i din arbetsbok:

1. Högerklicka på fliken Arbetsblad och välj Visa kod.
2. Kopiera och klistra in följande kod i VBA -fönstret:

   Private Sub ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Avsluta Sub

Det är allt!!

Du är redo med din egen sökfält av Google -typ som visar matchande objekt medan du skriver i den.

För ett bättre utseende kan du täcka cell B3 med kombinationsboxen och dölja alla hjälparkolumner. Du kan nu visa upp dig lite med detta fantastiska Excel -trick.

För att följa med, ladda ner filen härifrån

Vad tror du? Skulle du kunna använda den här listrutan för sökförslag i ditt arbete? Låt mig veta dina tankar genom att lämna en kommentar.

Om du har haft den här självstudien är jag säker på att du också vill ha följande Excel -självstudier:

• Dynamiskt filter - Extrahera matchande data medan du skriver.
• Extrahera data baserat på ett listrutval.
• Skapa beroende listrutor i Excel.
• Den ultimata guiden för att använda Excel VLOOKUP -funktion.
• Hur man gör flera val i en rullgardinsmeny i Excel.
• Så här infogar och använder du en kryssruta i Excel.