Izveidojiet Excel nolaižamo sarakstu ar meklēšanas ieteikumiem

Mēs visi izmantojam Google kā daļu no ikdienas rutīnas. Viena no tās funkcijām ir meklēšanas ieteikumi, kuros Google rīkojas gudri un sniedz mums ieteikumu sarakstu, kamēr mēs rakstām.

Šajā apmācībā jūs uzzināsit, kā programmā Excel izveidot meklējamu nolaižamo sarakstu, t.i., nolaižamo sarakstu, kurā rakstot tiks parādīti atbilstošie vienumi.

Zemāk ir šīs apmācības video (ja vēlaties skatīties video, nevis lasīt tekstu).

Meklējams nolaižamais saraksts programmā Excel

Šajā apmācībā es izmantoju Top 20 valstu datus pēc IKP.

Mērķis ir izveidot Excel nolaižamo sarakstu ar meklēšanas ieteikumu mehānismu tā, lai tajā tiktu parādīta nolaižamā izvēlne ar atbilstošajām opcijām, rakstot meklēšanas joslā.

Kaut kas, kā parādīts zemāk:

Lai turpinātu, lejupielādējiet piemēra failu no šejienes

Meklējamā nolaižamā saraksta izveide programmā Excel būtu trīsdaļīgs process:

1. Meklēšanas lodziņa konfigurēšana.
2. Datu iestatīšana.
3. Īsa VBA koda uzrakstīšana, lai tas darbotos.

1. darbība. Meklēšanas lodziņa konfigurēšana

Šajā pirmajā solī es izmantošu kombinēto lodziņu un konfigurēšu to tā, lai, ierakstot to, teksts tiktu atspoguļots arī šūnā reālā laikā.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atveriet cilni Izstrādātājs -> Ievietot -> ActiveX vadīklas -> kombinētais lodziņš (ActiveX vadīkla).
   • Pastāv iespēja, ka lentē neatradīsit izstrādātāja cilni. Pēc noklusējuma tas ir paslēpts, un tas ir jāiespējo. Noklikšķiniet šeit, lai uzzinātu, kā Excel lentē iegūt cilni Izstrādātājs.
2. Pārvietojiet kursoru uz darblapas apgabalu un noklikšķiniet jebkurā vietā. Tas ievietos kombinēto lodziņu.
3. Ar peles labo pogu noklikšķiniet uz kombinētā lodziņa un atlasiet Rekvizīti.
4. Rekvizītu dialoglodziņā veiciet šādas izmaiņas:
   • AutoWordSelect: Nepatiess
   • LinkedCell: B3
   • ListFillRange: DropDownList (2. darbībā izveidosim nosauktu diapazonu ar šo nosaukumu)
   • MatchEntry: 2 - fmMatchEntryNone

(Šūna B3 ir saistīta ar kombinēto lodziņu, kas nozīmē, ka viss, ko ierakstāt kombinētajā lodziņā, tiek ievadīts B3.)

4. Dodieties uz cilni Izstrādātājs un noklikšķiniet uz Dizaina režīms. Tas ļaus ievadīt tekstu kombinētajā lodziņā. Turklāt, tā kā šūna B3 ir saistīta ar kombinēto lodziņu, jebkurš kombinētajā lodziņā ievadītais teksts tiks atspoguļots arī B3 reāllaikā.

2. solis - datu iestatīšana

Tagad, kad meklēšanas lodziņš ir iestatīts, mums ir jāiegūst dati. Ideja ir tāda, ka, tiklīdz meklēšanas lodziņā ierakstāt kaut ko, tas parāda tikai tos vienumus, kuros ir šis teksts.

Lai to izdarītu, mēs izmantosim

• Trīs palīgu kolonnas.
• Viens dinamisks diapazons.

Palīgs 1. aile

Ievietojiet šādu formulu šūnā F3 un velciet to pa visu kolonnu (F3: F22)

=-ISNUMBER (IFERROR (MEKLĒT ($ B $ 3, E3,1), ""))

Šī formula atgriež 1, ja teksts kombinētajā lodziņā atrodas valsts nosaukumā kreisajā pusē. Piemēram, ja ierakstāt UNI, tad tikai vērtības United valstis un United Kingdom ir 1, un visas atlikušās vērtības ir 0.

Palīgs 2. sleja

Ievietojiet šādu formulu šūnā G3 un velciet to pa visu kolonnu (G3: G22)

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

Šī formula atgriež 1 pirmajam gadījumam, kad kombinētā lodziņa teksts atbilst valsts nosaukumam, 2 otrajam gadījumam, 3 trešajam un tā tālāk. Piemēram, ja ierakstāt UNI, G3 šūnā tiks parādīts 1, jo tas atbilst Amerikas Savienotajām Valstīm, un G9 parādīs 2, kā tas atbilst Apvienotajai Karalistei. Pārējās šūnas būs tukšas.

Palīgs 3. sleja

Ievietojiet šādu formulu šūnā H3 un velciet to pa visu kolonnu (H3: H22)

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

Šī formula saliek visus atbilstošos nosaukumus kopā bez tukšām šūnām. Piemēram, ja ierakstāt UNI, šajā slejā kopā tiktu parādīti 2 un 9, un pārējā šūna būtu tukša.

Dinamiskā nosaukuma diapazona izveide

Tagad, kad palīgu kolonnas ir izveidotas, mums ir jāizveido dinamiskais nosauktais diapazons. Šis nosauktais diapazons attieksies tikai uz tām vērtībām, kas atbilst kombinētajā lodziņā ievadītajam tekstam. Mēs izmantosim šo dinamisko nosaukto diapazonu, lai parādītu vērtības nolaižamajā lodziņā.

Piezīme: Pirmajā solī mēs ievadījām DropDownList opcijā ListFillRange. Tagad mēs izveidosim nosaukto diapazonu ar tādu pašu nosaukumu.

Tālāk ir norādītas tās izveidošanas darbības.

1. Dodieties uz Formulas -> Vārdu pārvaldnieks.
2. Vārdu pārvaldnieka dialoglodziņā noklikšķiniet uz Jauns. Tiks atvērts dialoglodziņš Jauns vārds.
3. Laukā Nosaukums ievadiet DropDownList
4. Laukā Atsauces uz ievadiet formulu: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

3. darbība - VBA koda ieviešana darbā

Mēs esam gandrīz tur.

Pēdējā daļa ir īsa VBA koda uzrakstīšana. Šis kods padara nolaižamo izvēlni dinamisku, parādot atbilstošos vienumus/nosaukumus, kad rakstāt meklēšanas lodziņā.

Lai pievienotu šo kodu darbgrāmatai:

1. Ar peles labo pogu noklikšķiniet uz cilnes Darblapa un atlasiet Skatīt kodu.
2. VBA logā kopējiet un ielīmējiet šādu kodu:

   Privāta apakškombinācija1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Beigu apakš

Tieši tā!!

Jūs esat iestatījis savu Google tipa meklēšanas joslu, kurā tiek parādīti atbilstošie vienumi, rakstot tajā.

Lai iegūtu labāku izskatu un izskatu, varat pārklāt šūnu B3 ar kombinēto lodziņu un paslēpt visas palīgu kolonnas. Tagad jūs varat nedaudz parādīt šo apbrīnojamo Excel triku.

Lai turpinātu, lejupielādējiet failu no šejienes

Ko tu domā? Vai jūs varētu izmantot šo meklēšanas ieteikumu nolaižamo sarakstu savā darbā? Ļaujiet man zināt savas domas, atstājot komentāru.

Ja jums patika šī apmācība, esmu pārliecināts, ka jums patiks arī šādas Excel apmācības:

• Dinamiskais filtrs - rakstīšanas laikā iegūstiet atbilstošos datus.
• Iegūstiet datus, pamatojoties uz nolaižamā saraksta izvēli.
• Atkarīgu nolaižamo sarakstu izveide programmā Excel.
• Galīgais ceļvedis Excel funkcijas VLOOKUP izmantošanai.
• Kā veikt vairākas atlases Excel nolaižamajā sarakstā.
• Kā ievietot un izmantot izvēles rūtiņu programmā Excel.