Opret en Excel -rulleliste med søgeforslag

Vi bruger alle Google som en del af vores daglige rutine. En af dens funktioner er søgeforslag, hvor Google handler smart og giver os en liste over forslag, mens vi skriver.

I denne vejledning lærer du, hvordan du opretter en rulleliste, der kan søges i i Excel-dvs. en rulleliste, der viser de matchende elementer, mens du skriver.

Nedenfor er en video af denne vejledning (hvis du foretrækker at se en video frem for at læse teksten).

Søgbar rulleliste i Excel

I forbindelse med denne vejledning bruger jeg data fra de 20 bedste lande efter BNP.

Hensigten er at oprette en excel -rulleliste med en søgeforslagsmekanisme, sådan at den viser en rullemenu med de matchende muligheder, mens jeg skriver i søgelinjen.

Noget som vist herunder:

For at følge med skal du downloade eksemplet herfra

Oprettelse af den søgbare rulleliste i Excel ville være en tredelt proces:

1. Konfiguration af søgefeltet.
2. Indstilling af data.
3. At skrive en kort VBA -kode for at få det til at fungere.

Trin 1 - Konfiguration af søgefeltet

I dette første trin vil jeg bruge en kombinationsboks og konfigurere den, så når du skriver den, reflekteres teksten også i en celle i realtid.

Her er trinene til at gøre dette:

1. Gå til fanen Udvikler -> Indsæt -> ActiveX -kontrolelementer -> Kombinationsboks (ActiveX -kontrol).
   • Der er en mulighed for, at du muligvis ikke finder fanen Udvikler på båndet. Som standard er den skjult og skal aktiveres. Klik her for at vide, hvordan du får fanen Udvikler på båndet i Excel.
2. Flyt markøren til regnearksområdet, og klik hvor som helst. Det vil indsætte en kombinationsboks.
3. Højreklik på kombinationsboksen, og vælg Egenskaber.
4. I egenskabsdialogboksen skal du foretage følgende ændringer:
   • AutoWordVælg: Falsk
   • LinkedCell: B3
   • ListFillRange: DropDownList (vi opretter et navngivet område med dette navn i trin 2)
   • MatchEntry: 2 - fmMatchEntryNone

(Celle B3 er knyttet til kombinationsboksen, hvilket betyder, at alt, hvad du skriver i kombinationsboksen, indtastes i B3)

4. Gå til fanen Udvikler, og klik på Designtilstand. Dette giver dig mulighed for at indtaste tekst i kombinationsboksen. Da celle B3 også er knyttet til kombinationsboksen, vil enhver tekst, du indtaster i kombinationsboksen, også blive afspejlet i B3 i realtid.

Trin 2 - Indstilling af data

Nu hvor søgefeltet er klar, skal vi få dataene på plads. Ideen er, at så snart du skriver noget i søgefeltet, viser det kun de elementer, der har den tekst i den.

For at gøre dette vil vi bruge

• Tre hjælperkolonner.
• Et dynamisk navngivet område.

Hjælperkolonne 1

Sæt følgende formel i celle F3 og træk den for hele kolonnen (F3: F22)

=-ISNUMBER (IFERROR (SØG ($ B $ 3, E3,1), ""))

Denne formel returnerer 1, når teksten i kombinationsboksen er der i navnet på landet til venstre. Hvis du f.eks. Skriver UNI, er det kun værdierne for Unitedstater og United Kingdom er 1, og alle de resterende værdier er 0.

Hjælperkolonne 2

Sæt følgende formel i celle G3 og træk den for hele kolonnen (G3: G22)

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

Denne formel returnerer 1 for den første forekomst, hvor kombinationsboksteksten matcher landets navn, 2 for den anden forekomst, 3 for den tredje og så videre. Hvis du f.eks. Skriver UNI, viser G3 -celle 1, som den matcher USA, og G9 viser 2, som den matcher Storbritannien. Resten af ​​cellerne vil være tomme.

Hjælperkolonne 3

Sæt følgende formel i celle H3 og træk den for hele kolonnen (H3: H22)

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

Denne formel stabler alle de matchende navne sammen uden tomme celler imellem dem. For eksempel, hvis du skriver UNI, viser denne kolonne 2 og 9 sammen, og resten vil alle celler være tomme.

Oprettelse af det dynamiske navngivne område

Nu hvor hjælperkolonnerne er på plads, skal vi oprette det dynamiske navngivne område. Dette navngivne område refererer kun til de værdier, der matcher teksten i kombinationsboksen. Vi vil bruge dette dynamiske navngivne område til at vise værdierne i rullemenuen.

Bemærk: I trin 1 indtastede vi DropDownList i indstillingen ListFillRange. Nu opretter vi det navngivne område med samme navn.

Her er trinene til at oprette det:

1. Gå til Formler -> Name Manager.
2. Klik på Ny i dialogboksen Navneadministrator. Det åbner en dialogboks Nyt navn.
3. Indtast DropDownList i feltet Navn
4. Indtast formlen i feltet Refers to field: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Trin 3 - Sæt VBA -koden i arbejde

Vi er næsten der.

Den sidste del er at skrive en kort VBA -kode. Denne kode gør rullemenuen dynamisk, så den viser de matchende elementer/navne, mens du skriver i søgefeltet.

Sådan føjer du denne kode til din projektmappe:

1. Højreklik på fanen Regneark, og vælg Vis kode.
2. I VBA -vinduet skal du kopiere og indsætte følgende kode:

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

Det er det!!

Du er klar med din egen Google -søgelinje, der viser matchende elementer, mens du skriver den.

For et bedre udseende kan du dække celle B3 med kombinationsboksen og skjule alle hjælperkolonnerne. Du kan nu vise dig lidt frem med dette fantastiske Excel -trick.

For at følge med skal du downloade filen herfra

Hvad synes du? Vil du være i stand til at bruge denne rulleliste med søgeforslag i dit arbejde? Fortæl mig dine tanker ved at efterlade en kommentar.

Hvis du har nydt denne vejledning, er jeg sikker på, at du også gerne vil have følgende Excel -selvstudier:

• Dynamisk filter - Udtræk matchende data, mens du skriver.
• Udtræk data baseret på en rullelistevalg.
• Oprettelse af afhængige rullelister i Excel.
• Den ultimative guide til brug af Excel VLOOKUP -funktion.
• Sådan foretages flere valg i en rulleliste i Excel.
• Sådan indsættes og bruges en afkrydsningsfelt i Excel.