Looge otsingusoovitustega Exceli ripploend

Me kõik kasutame Google'i oma igapäevase rutiini osana. Üks selle funktsioone on otsingusoovitused, kus Google tegutseb nutikalt ja annab meile tippimise ajal loendi soovitustest.

Selles õpetuses saate teada, kuidas luua Excelis otsitav ripploend-st ripploend, mis kuvab sisestamisel vastavad üksused.

Allpool on selle õpetuse video (juhul kui eelistate teksti lugemise asemel videot vaadata).

Otsitav rippmenüü Excelis

Selle õpetuse jaoks kasutan SKP järgi 20 parima riigi andmeid.

Eesmärk on luua Exceli ripploend koos otsingusoovituste mehhanismiga, nii et see näitaks otsinguribale tippides sobivate valikutega rippmenüüd.

Midagi, nagu allpool näidatud:

Jätkamiseks laadige näidisfail siit alla

Otsitava ripploendi loomine Excelis oleks kolmeosaline:

1. Otsingukasti seadistamine.
2. Andmete seadistamine.
3. Töötamiseks kirjutage lühike VBA -kood.

1. samm - otsingukasti seadistamine

Selles esimeses etapis kasutan liitkasti ja seadistan selle nii, et selle sisestamisel kajastuks tekst ka lahtris reaalajas.

Selleks toimige järgmiselt.

1. Avage vahekaart Arendaja -> Lisa -> ActiveX -juhtelemendid -> liitkast (ActiveX -juhtelement).
   • Võimalik, et te ei leia lindilt arendaja vahekaarti. Vaikimisi on see peidetud ja see tuleb lubada. Klõpsake siin, et teada saada, kuidas saada Exceli lindi arendaja vahekaart.
2. Liigutage kursor töölehe alale ja klõpsake ükskõik kus. See lisab liitkasti.
3. Paremklõpsake liitkasti ja valige Atribuudid.
4. Tehke dialoogiboksis Atribuudid järgmised muudatused.
   • AutoWordSelect: Vale
   • LinkedCell: B3
   • ListFillRange: DropDownList (loome selle nimega 2. vahemikus nimega vahemiku)
   • MatchEntry: 2 - fmMatchEntryNone

(Lahter B3 on ühendatud liitkastiga, mis tähendab, et kõik, mida sisestate liitkasti, sisestatakse lahtrisse B3)

4. Minge vahekaardile Arendaja ja klõpsake kujundusrežiimi. See võimaldab sisestada liitkasti teksti. Samuti, kuna lahter B3 on ühendatud liitkastiga, kajastub kogu tekst, mille sisestate liitkasti, ka reaalajas B3-s.

2. samm - andmete seadistamine

Nüüd, kui otsingukast on valmis, peame andmed paika saama. Idee on selles, et niipea, kui sisestate otsingukasti midagi, kuvatakse see ainult need üksused, millel on see tekst.

Selleks kasutame

• Kolm abikolonni.
• Üks dünaamiline nimega vahemik.

Abistajaveerg 1

Pange lahtrisse F3 järgmine valem ja lohistage seda kogu veeru jaoks (F3: F22)

=-ISNUMBER (IFERROR (OTSING ($ B $ 3, E3,1), ""))

See valem tagastab 1, kui liitkastis olev tekst on vasakul riigi nimes. Näiteks kui sisestate UNI, siis ainult väärtused United riigid ja United Kingdom on 1 ja kõik ülejäänud väärtused on 0.

Abistav veerg 2

Pange lahtrisse G3 järgmine valem ja lohistage seda kogu veeru jaoks (G3: G22)

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

See valem tagastab esimese esinemise korral 1, kui liitkasti tekst vastab riigi nimele, 2 teise esinemise korral, 3 kolmanda ja nii edasi. Näiteks kui sisestate UNI, kuvatakse G3 lahtris 1, kuna see vastab Ameerika Ühendriikidele, ja G9 kuvab 2, kuna see vastab Ühendkuningriigile. Ülejäänud lahtrid on tühjad.

Abistav veerg 3

Pange lahtrisse H3 järgmine valem ja lohistage seda kogu veeru jaoks (H3: H22)

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

See valem koondab kõik sobivad nimed kokku, ilma et nende vahel oleks tühje lahtreid. Näiteks kui sisestate UNI, kuvatakse selles veerus 2 ja 9 koos ning ülejäänud lahtrid on tühjad.

Dünaamilise nimega vahemiku loomine

Nüüd, kui abiveerud on paigas, peame looma dünaamilise nimega vahemiku. See nimega vahemik viitab ainult nendele väärtustele, mis vastavad liitkasti sisestatud tekstile. Kasutame seda dünaamilist nimevahemikku, et näidata rippmenüü väärtusi.

Märge: Esimeses etapis sisestasime ListFillRange valikusse DropDownList. Nüüd loome nimega vahemiku sama nimega.

Selle loomiseks toimige järgmiselt.

1. Avage Valemid -> Nimehaldur.
2. Klõpsake dialoogiboksis nimehaldur nuppu Uus. See avab dialoogiboksi Uus nimi.
3. Sisestage väljale Nimi DropDownList
4. Väljale Viitab sisestage valem: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

3. samm - VBA koodi töölerakendamine

Oleme peaaegu kohal.

Viimane osa on lühikese VBA -koodi kirjutamine. See kood muudab rippmenüü dünaamiliseks nii, et see kuvab otsinguväljale tippimisel vastavad üksused/nimed.

Selle koodi lisamiseks töövihikusse tehke järgmist.

1. Paremklõpsake vahekaarti Tööleht ja valige Kuva kood.
2. Kopeerige ja kleepige VBA aknas järgmine kood:

   Privaatne alamkombinatsioonikast1_muutus () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

See on see !!

Olete valmis seadistanud oma Google'i tüüpi otsinguriba, mis kuvab sisestamisel vastavad üksused.

Parema väljanägemise ja tunde tagamiseks võite lahtri B3 katta liitkastiga ja peita kõik abiveerud. Selle hämmastava Exceli nipiga saate nüüd veidi eputada.

Jätkamiseks laadige fail siit alla

Mida sa arvad? Kas saaksite seda otsingusoovituste ripploendit oma töös kasutada? Andke mulle oma mõtetest teada, jättes kommentaari.

Kui teile see õpetus meeldis, siis olen kindel, et teile meeldiksid ka järgmised Exceli õpetused:

• Dünaamiline filter - ekstraktige sisestamise ajal sobivaid andmeid.
• Andmete väljavõtmine ripploendi valiku alusel.
• Sõltuvate ripploendite loomine Excelis.
• Ülim juhend Exceli funktsiooni VLOOKUP kasutamiseks.
• Kuidas teha Exceli ripploendis mitu valikut.
• Märkekasti sisestamine ja kasutamine Excelis.