Vi bruker alle Google som en del av vår daglige rutine. En av funksjonene er søkeforslag, der Google handler smart og gir oss en liste over forslag mens vi skriver.
I denne opplæringen lærer du hvordan du oppretter en søkbar rullegardinliste i Excel-dvs. en rullegardinliste som viser de matchende elementene mens du skriver.
Nedenfor er en video av denne opplæringen (i tilfelle du foretrekker å se en video fremfor å lese teksten).
Søkbar rullegardinliste i Excel
I forbindelse med denne opplæringen bruker jeg dataene fra de 20 beste landene etter BNP.
Intensjonen er å opprette en Excel -rullegardinliste med en forslag til mekanisme for søk, slik at den viser en nedtrekksmeny med matchende alternativer mens jeg skriver i søkefeltet.
Noe som vist nedenfor:
For å følge med, last ned eksempelfilen herfra
Å lage den søkbare rullegardinlisten i Excel vil være en tredelt prosess:
- Konfigurere søkeboksen.
- Angi data.
- Skrive en kort VBA -kode for å få det til å fungere.
Trinn 1 - Konfigurering av søkeboksen
I dette første trinnet vil jeg bruke en kombinasjonsboks og konfigurere den slik at når du skriver inn den, reflekteres teksten også i en celle i sanntid.
Her er trinnene for å gjøre dette:
- Gå til Utvikler -fanen -> Sett inn -> ActiveX -kontroller -> Kombinasjonsboks (ActiveX -kontroll).
- Det er en mulighet for at du ikke finner utviklerfanen på båndet. Som standard er den skjult og må aktiveres. Klikk her for å vite hvordan du får utviklerfanen i båndet i Excel.
- Det er en mulighet for at du ikke finner utviklerfanen på båndet. Som standard er den skjult og må aktiveres. Klikk her for å vite hvordan du får utviklerfanen i båndet i Excel.
- Flytt markøren til regnearkområdet og klikk hvor som helst. Det vil sette inn en kombinasjonsboks.
- Høyreklikk på kombinasjonsboksen og velg Egenskaper.
- Gjør følgende endringer i dialogboksen for egenskaper:
- AutoWordSelect: Falsk
- LinkedCell: B3
- ListFillRange: DropDownList (vi oppretter et navngitt område med dette navnet i trinn 2)
- MatchEntry: 2 - fmMatchEntryNone
(Celle B3 er koblet til kombinasjonsboksen, noe som betyr at alt du skriver i kombinasjonsboksen er angitt i B3)
- Gå til Utvikler -fanen og klikk på Designmodus. Dette lar deg skrive inn tekst i kombinasjonsboksen. Siden celle B3 er koblet til kombinasjonsboksen, vil all tekst du skriver inn i kombinasjonsboksen også gjenspeiles i B3 i sanntid.
Trinn 2 - Angi data
Nå som søkeboksen er klar, må vi få dataene på plass. Tanken er at så snart du skriver noe i søkefeltet, viser det bare de elementene som har teksten i den.
For å gjøre dette, vil vi bruke
- Tre hjelperkolonner.
- Ett dynamisk navngitt område.
Hjelperkolonne 1
Sett følgende formel i celle F3 og dra den for hele kolonnen (F3: F22)
=-ISNUMBER (IFERROR (SØK ($ B $ 3, E3,1), ""))
Denne formelen returnerer 1 når teksten i kombinasjonsboksen er der i navnet på landet til venstre. For eksempel, hvis du skriver UNI, er det bare verdiene for United -stater og United Kingdom er 1 og alle gjenværende verdier er 0.
Hjelperkolonne 2
Sett følgende formel i celle G3 og dra den for hele kolonnen (G3: G22)
= HVIS (F3 = 1, COUNTIF ($ F $ 3: F3,1), "")
Denne formelen returnerer 1 for den første forekomsten der tekstboksen samsvarer med landnavnet, 2 for den andre forekomsten, 3 for den tredje og så videre. For eksempel, hvis du skriver UNI, vil G3 celle vise 1 som den samsvarer med USA, og G9 vil vise 2 slik den samsvarer med Storbritannia. Resten av cellene vil være tomme.
Hjelperkolonne 3
Sett følgende formel i celle H3 og dra den for hele kolonnen (H3: H22)
= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "")
Denne formelen stabler alle matchende navn sammen uten noen tomme celler mellom dem. For eksempel, hvis du skriver UNI, viser denne kolonnen 2 og 9 sammen, og resten vil være tom.
Opprette det dynamiske navngitte området
Nå som hjelperkolonnene er på plass, må vi lage det dynamiske navngitte området. Dette navngitte området vil bare referere til de verdiene som samsvarer med teksten som er angitt i kombinasjonsboksen. Vi vil bruke dette dynamiske navngitte området for å vise verdiene i nedtrekksboksen.
Merk: I trinn 1 skrev vi inn DropDownList i alternativet ListFillRange. Nå skal vi lage det navngitte området med samme navn.
Her er trinnene for å lage den:
- Gå til Formler -> Name Manager.
- Klikk Ny i dialogboksen Navneadministrator. Den åpner dialogboksen Nytt navn.
- Skriv inn DropDownList i navnefeltet
- I feltet Refererer til feltet skriver du inn formelen: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)
Trinn 3 - Sette VBA -koden i arbeid
Vi er nesten der.
Den siste delen er å skrive en kort VBA -kode. Denne koden gjør rullegardinmenyen dynamisk slik at den viser de matchende elementene/navnene mens du skriver i søkeboksen.
Slik legger du til denne koden i arbeidsboken din:
- Høyreklikk på kategorien Regneark og velg Vis kode.
- I VBA -vinduet, kopier og lim inn følgende kode:
Private Sub ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Slutt Sub
Det er det!!
Du er klar med din egen Google -type søkefelt som viser matchende elementer mens du skriver den.
For et bedre utseende kan du dekke celle B3 med kombinasjonsboksen og skjule alle hjelperesøylene. Du kan nå vise deg frem litt med dette fantastiske Excel -trikset.
For å følge med, last ned filen herfra
Hva tror du? Vil du kunne bruke denne rullegardinlisten for søkeforslag i arbeidet ditt? Gi meg beskjed om tankene dine ved å legge igjen en kommentar.
Hvis du har likt denne opplæringen, er jeg sikker på at du også vil ha følgende Excel -opplæringsprogrammer:
- Dynamisk filter - Trekk ut matchende data mens du skriver.
- Trekk ut data basert på et rullegardinlistevalg.
- Opprette avhengige rullegardinlister i Excel.
- Den ultimate guiden for bruk av Excel VLOOKUP -funksjon.
- Hvordan gjøre flere valg i en rullegardinliste i Excel.
- Slik setter du inn og bruker en avkrysningsboks i Excel.
