Sukurkite „Excel“ išskleidžiamąjį sąrašą su paieškos pasiūlymais

Mes visi naudojame „Google“ kaip savo kasdienybės dalį. Viena iš jo funkcijų yra paieškos pasiūlymas, kai „Google“ elgiasi protingai ir pateikia mums pasiūlymų sąrašą, kol rašome.

Šioje pamokoje sužinosite, kaip „Excel“ sukurti išskleidžiamąjį sąrašą, kuriame galima ieškoti, t. Y. Išskleidžiamąjį sąrašą, kuriame bus rodomi atitinkami elementai rašant.

Žemiau yra šios pamokos vaizdo įrašas (jei norite žiūrėti vaizdo įrašą, o ne skaityti tekstą).

Ieškomas išskleidžiamasis sąrašas „Excel“

Šioje pamokoje naudoju 20 geriausių šalių pagal BVP duomenis.

Tikslas yra sukurti „Excel“ išskleidžiamąjį sąrašą su paieškos pasiūlymų mechanizmu, kad būtų rodomas išskleidžiamasis meniu su atitinkamomis parinktimis, kai įvedu paieškos juostoje.

Kažkas, kaip parodyta žemiau:

Norėdami tai padaryti, atsisiųskite pavyzdinį failą iš čia

Išskleidžiamojo sąrašo, kuriame galima ieškoti, sukūrimas „Excel“ būtų trijų dalių procesas:

1. Paieškos laukelio konfigūravimas.
2. Duomenų nustatymas.
3. Parašykite trumpą VBA kodą, kad jis veiktų.

1 veiksmas - paieškos laukelio konfigūravimas

Šiame pirmame žingsnyje naudosiu kombinuotą langelį ir sukonfigūruosiu jį taip, kad įvedus tekstą tekstas taip pat atsispindėtų langelyje realiuoju laiku.

Štai žingsniai, kaip tai padaryti:

1. Eikite į skirtuką Kūrėjas -> Įterpti -> „ActiveX“ valdikliai -> Sudėtinis langelis („ActiveX“ valdiklis).
   • Yra galimybė, kad juostelėje nerasite kūrėjo skirtuko. Pagal numatytuosius nustatymus jis yra paslėptas ir jį reikia įjungti. Spustelėkite čia, kad sužinotumėte, kaip gauti „Excel“ juostos kūrėjo skirtuką.
2. Perkelkite žymeklį į darbalapio sritį ir spustelėkite bet kur. Jis įdės kombinuotą langelį.
3. Dešiniuoju pelės mygtuku spustelėkite kombinuotąjį laukelį ir pasirinkite Ypatybės.
4. Savybių dialogo lange atlikite šiuos pakeitimus:
   • „AutoWordSelect“: Netiesa
   • „LinkedCell“: B3
   • ListFillRange: DropDownList (2 veiksme sukursime pavadintą diapazoną tokiu pavadinimu)
   • Įėjimas: 2 - fmMatchEntryNone

(B3 langelis yra susietas su kombinuotu langeliu, o tai reiškia, kad viskas, ką įvedate į kombinuotą laukelį, įvedama į B3)

4. Eikite į skirtuką Kūrėjas ir spustelėkite Dizaino režimas. Tai leis įvesti tekstą į kombinuotąjį laukelį. Be to, kadangi langelis B3 yra susietas su sudėtiniu laukeliu, bet koks tekstas, kurį įvedate į kombinuotą laukelį, taip pat bus rodomas B3 realiuoju laiku.

2 žingsnis - duomenų nustatymas

Dabar, kai paieškos laukelis yra nustatytas, turime surinkti duomenis. Idėja yra ta, kad kai tik įvedate bet ką į paieškos laukelį, jis rodo tik tuos elementus, kuriuose yra tas tekstas.

Norėdami tai padaryti, mes naudosime

• Trys pagalbinės kolonos.
• Vienas dinaminis pavadintas diapazonas.

Pagalbinė 1 stulpelis

Įdėkite šią formulę į langelį F3 ir vilkite jį per visą stulpelį (F3: F22)

=-ISNUMBER (IFERROR (PAIEŠKA ($ B $ 3, E3,1), ""))

Ši formulė grąžina 1, kai tekstas sudėtiniame laukelyje yra šalies pavadinime kairėje. Pvz., Jei įvedate UNI, nurodykite tik reikšmes United valstybėms ir UniKaralystė yra 1, o visos likusios vertės yra 0.

Pagalbinė 2 stulpelis

Įdėkite šią formulę į langelį G3 ir vilkite ją per visą stulpelį (G3: G22)

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

Ši formulė grąžina 1 pirmą kartą, kai sudėtinio laukelio tekstas atitinka šalies pavadinimą, 2 - antrą kartą, 3 - trečią ir pan. Pvz., Jei įvedate UNI, G3 langelyje bus rodomas 1, kai jis atitinka Jungtines Valstijas, o G9 - 2, kaip ir Jungtinė Karalystė. Likusios ląstelės bus tuščios.

Pagalbinė kolona 3

Įdėkite šią formulę į langelį H3 ir vilkite ją per visą stulpelį (H3: H22)

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

Ši formulė sukrauna visus atitinkančius pavadinimus be tuščių langelių. Pvz., Jei įvesite UNI, šiame stulpelyje bus rodomi 2 ir 9 kartu, o visi langeliai bus tušti.

Dinaminio pavadinto diapazono kūrimas

Dabar, kai pagalbiniai stulpeliai yra vietoje, turime sukurti dinaminį pavadintą diapazoną. Šis pavadintas diapazonas nurodys tik tas vertes, kurios atitinka kombinuotame laukelyje įvestą tekstą. Šį dinaminį pavadintą diapazoną naudosime norėdami parodyti vertes išskleidžiamajame laukelyje.

Pastaba: 1 žingsnyje į „ListFillRange“ parinktį įvedėme „DropDownList“. Dabar sukursime pavadintą diapazoną tuo pačiu pavadinimu.

Štai jo sukūrimo veiksmai:

1. Eikite į Formulės -> Vardų tvarkytuvė.
2. Vardų tvarkyklės dialogo lange spustelėkite Naujas. Bus atidarytas dialogo langas Naujas vardas.
3. Lauke Pavadinimas įveskite DropDownList
4. Lauke Nuorodos į lauką įveskite formulę: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

3 žingsnis - VBA kodo veikimas

Mes beveik ten.

Paskutinė dalis yra parašyti trumpą VBA kodą. Šis kodas išskleidžiamąjį meniu daro dinamišką, kad rodytų atitinkančius elementus/pavadinimus, kai rašote paieškos laukelyje.

Norėdami pridėti šį kodą prie savo darbaknygės:

1. Dešiniuoju pelės mygtuku spustelėkite darbalapio skirtuką ir pasirinkite Peržiūrėti kodą.
2. VBA lange nukopijuokite ir įklijuokite šį kodą:

   Privatus antrinis ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Pabaiga Sub

Viskas!!

Viskas paruošta naudojant „Google“ tipo paieškos juostą, kurioje rodomi atitinkami elementai, kai įvedate.

Norėdami geriau atrodyti ir jaustis, galite uždengti langelį B3 naudodami kombinuotą dėžutę ir paslėpti visus pagalbinius stulpelius. Dabar galite šiek tiek pademonstruoti šį nuostabų „Excel“ triuką.

Norėdami tęsti, atsisiųskite failą iš čia

Ką tu manai? Ar galėtumėte naudoti šį paieškos pasiūlymų išskleidžiamąjį sąrašą savo darbe? Praneškite man savo mintis palikdami komentarą.

Jei jums patiko ši pamoka, esu tikras, kad jums taip pat patiks šios „Excel“ pamokos:

• Dinaminis filtras - ištraukite atitinkamus duomenis, kai rašote.
• Ištraukite duomenis pagal išskleidžiamojo sąrašo pasirinkimą.
• Priklausomų išskleidžiamųjų sąrašų kūrimas „Excel“.
• Galutinis „Excel“ VLOOKUP funkcijos naudojimo vadovas.
• Kaip „Excel“ išskleidžiamajame sąraše atlikti kelis pasirinkimus.
• Kaip įterpti ir naudoti žymės langelį „Excel“.