Me kaikki käytämme Googlea osana päivittäistä rutiiniamme. Yksi sen ominaisuuksista on hakuehdotus, jossa Google toimii fiksusti ja antaa meille luettelon ehdotuksista kirjoittaessamme.
Tässä opetusohjelmassa opit luomaan avattavan haettavissa olevan luettelon Excelissä eli avattavan luettelon, joka näyttää vastaavat kohteet kirjoittaessasi.
Alla on video tästä opetusohjelmasta (jos haluat katsoa videon tekstin lukemisen sijaan).
Haettava pudotusvalikko Excelissä
Tässä opetusohjelmassa käytän 20 parhaan maan tietoja BKT: n mukaan.
Tarkoituksena on luoda Excel -pudotusvalikko, jossa on haun ehdotusmekanismi, niin että se näyttää avattavan valikon vastaavilla vaihtoehdoilla kirjoittaessani hakupalkkiin.
Jotain alla olevan kuvan mukaisesti:
Jos haluat jatkaa, lataa esimerkkitiedosto täältä
Hakukelpoisen avattavan luettelon luominen Excelissä olisi kolmiosainen prosessi:
- Hakukentän määrittäminen.
- Tietojen asettaminen.
- Lyhyen VBA -koodin kirjoittaminen sen toimimiseksi.
Vaihe 1 - Hakukentän määrittäminen
Tässä ensimmäisessä vaiheessa käytän yhdistelmäruutua ja määritän sen niin, että kun kirjoitat sen, teksti näkyy myös solussa reaaliajassa.
Tässä on seuraavat vaiheet:
- Valitse Kehittäjä -välilehti -> Lisää -> ActiveX -komponentit -> Yhdistelmäruutu (ActiveX -ohjaus).
- On mahdollista, että et löydä kehittäjä -välilehteä valintanauhasta. Oletuksena se on piilotettu ja se on otettava käyttöön. Napsauta tätä saadaksesi tietää, miten saat kehittäjävälilehden Excel -valintanauhassa.
- On mahdollista, että et löydä kehittäjä -välilehteä valintanauhasta. Oletuksena se on piilotettu ja se on otettava käyttöön. Napsauta tätä saadaksesi tietää, miten saat kehittäjävälilehden Excel -valintanauhassa.
- Siirrä kohdistin laskentataulukkoalueelle ja napsauta mitä tahansa. Se lisää yhdistelmäruudun.
- Napsauta yhdistelmäruutua hiiren kakkospainikkeella ja valitse Ominaisuudet.
- Tee ominaisuudet -valintaikkunassa seuraavat muutokset:
- AutoWordSelect: Väärä
- LinkedCell: B3
- ListFillRange: DropDownList (luomme nimetyn alueen tällä nimellä vaiheessa 2)
- Ottelu: 2 - fmMatchEntryNone
(Solu B3 on yhdistetty yhdistelmäruutuun, mikä tarkoittaa, että kaikki yhdistelmäruutuun kirjoittamasi tiedot merkitään kohtaan B3)
- Siirry Kehittäjä -välilehteen ja napsauta Suunnittelutila. Tämän avulla voit kirjoittaa tekstiä yhdistelmäruutuun. Lisäksi koska solu B3 on yhdistetty yhdistelmäruutuun, kaikki yhdistelmäruutuun syöttämäsi teksti näkyy myös B3: ssa reaaliajassa.
Vaihe 2 - Tietojen asettaminen
Nyt kun hakukenttä on valmis, meidän on saatava tiedot paikoilleen. Ajatuksena on, että heti kun kirjoitat jotain hakukenttään, se näyttää vain ne kohteet, joissa on kyseinen teksti.
Tätä varten käytämme
- Kolme auttajapylvästä.
- Yksi dynaaminen nimetty alue.
Auttaja -sarake 1
Laita seuraava kaava soluun F3 ja vedä sitä koko sarakkeessa (F3: F22)
=-ISNUMBER (IFERROR (HAKU ($ B $ 3, E3,1), ""))
Tämä kaava palauttaa arvon 1, kun yhdistelmäruudun teksti on vasemmalla olevan maan nimessä. Jos esimerkiksi kirjoitat UNI, vain arvot United valtioita ja United Kingdom on 1 ja kaikki muut arvot ovat 0.
Auttaja -sarake 2
Laita seuraava kaava soluun G3 ja vedä sitä koko sarakkeessa (G3: G22)
= JOS (F3 = 1, LASKEA ($ F $ 3: F3,1), "")
Tämä kaava palauttaa arvon 1 ensimmäiselle esiintymälle, jossa yhdistelmäruudun teksti vastaa maan nimeä, 2 toiselle esiintymälle, 3 kolmannelle ja niin edelleen. Jos esimerkiksi kirjoitat UNI, G3 -solu näyttää 1, koska se vastaa Yhdysvaltoja, ja G9 näyttää 2, koska se vastaa Yhdistynyttä kuningaskuntaa. Loput solut ovat tyhjiä.
Auttaja -sarake 3
Laita seuraava kaava soluun H3 ja vedä sitä koko sarakkeessa (H3: H22)
= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (RIVIT ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "")
Tämä kaava pinottaa kaikki vastaavat nimet yhteen ilman tyhjiä soluja niiden välissä. Jos esimerkiksi kirjoitat UNI, tässä sarakkeessa näkyvät 2 ja 9 yhdessä ja loput kaikki solut ovat tyhjiä.
Dynaamisen nimetyn alueen luominen
Nyt kun apusarakkeet ovat paikoillaan, meidän on luotava dynaaminen nimetty alue. Tämä nimetty alue viittaa vain niihin arvoihin, jotka vastaavat yhdistelmäruutuun syötettyä tekstiä. Käytämme tätä dynaamista nimettyä aluetta näyttääksesi arvot avattavasta ruudusta.
Merkintä: Vaiheessa 1 kirjoitimme DropDownListin ListFillRange -vaihtoehtoon. Luomme nyt nimetyn alueen samalla nimellä.
Voit luoda sen seuraavasti:
- Siirry kohtaan Kaavat -> Nimenhallinta.
- Napsauta nimenhallintaikkunassa Uusi. Se avaa uuden nimen valintaikkunan.
- Kirjoita Nimi -kenttään DropDownList
- Kirjoita Viittaukset -kenttään kaava: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)
Vaihe 3 - VBA -koodin käyttöönotto
Olemme melkein siellä.
Viimeinen osa on kirjoittaa lyhyt VBA -koodi. Tämä koodi tekee avattavasta valikosta dynaamisen siten, että se näyttää vastaavat kohteet/nimet kirjoittaessasi hakukenttään.
Voit lisätä tämän koodin työkirjaasi seuraavasti:
- Napsauta hiiren kakkospainikkeella laskentataulukon välilehteä ja valitse Näytä koodi.
- Kopioi ja liitä VBA -ikkunassa seuraava koodi:
Yksityinen ali -yhdistelmälaatikko1_Muuta () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub
Se siitä!!
Kaikki ovat valmiina omalla Google -tyyppisellä hakupalkilla, joka näyttää vastaavat kohteet kirjoittaessasi sitä.
Paremman ulkonäön ja vaikutelman saavuttamiseksi voit peittää solun B3 yhdistelmälaatikolla ja piilottaa kaikki apupylväät. Voit nyt näyttää hieman tällä hämmästyttävällä Excel -temppulla.
Jos haluat jatkaa, lataa tiedosto täältä
Mitä mieltä sinä olet? Voisitko käyttää tätä avattavaa hakuehdotusluetteloa työssäsi? Kerro mielipiteesi jättämällä kommentti.
Jos pidit tästä opetusohjelmasta, olet varma, että haluat myös seuraavat Excel -opetusohjelmat:
- Dynaaminen suodatin - poimi vastaavat tiedot kirjoittaessasi.
- Pura tiedot avattavan luettelon valinnan perusteella.
- Riippuvien pudotusvalikkojen luominen Excelissä.
- Lopullinen opas Excel VLOOKUP -toiminnon käyttöön.
- Kuinka tehdä useita valintoja Excelin avattavasta luettelosta.
- Valintaruudun lisääminen ja käyttö Excelissä.
