Riippuvan avattavan luettelon luominen Excelissä (vaiheittainen opetusohjelma)

Katso video - Riippuvan avattavan luettelon luominen Excelissä

Avattava Excel -luettelo on hyödyllinen ominaisuus, kun luot tietojen syöttölomakkeita tai Excel -hallintapaneeleja.

Se näyttää luettelon kohteista avattavana valikkona solussa, ja käyttäjä voi tehdä valinnan avattavasta valikosta. Tästä voi olla hyötyä, kun sinulla on luettelo nimistä, tuotteista tai alueista, jotka sinun on usein syötettävä solusarjoihin.

Alla on esimerkki avattavasta Excel -luettelosta:

Yllä olevassa esimerkissä olen käyttänyt A2: A6: n kohteita pudotusvalikon luomiseen C3: een.

Lukea: Tässä on yksityiskohtainen opas Excel -pudotusvalikon luomiseen.

Joskus saatat kuitenkin haluta käyttää useampaa kuin yhtä avattavaa luetteloa Excelissä siten, että toisessa avattavassa luettelossa olevat kohteet riippuvat ensimmäisessä avattavassa luettelossa tehdystä valinnasta.

Näitä kutsutaan Excelin riippuvaisiksi avattaviksi luetteloiksi.

Alla on esimerkki siitä, mitä tarkoitan Excelin riippuvalla avattavalla luettelolla:

Näet, että pudotusvalikon 2 vaihtoehdot riippuvat pudotusvalikossa 1 tehdystä valinnasta. Jos valitsen pudotusvalikosta 1 'Hedelmät', näytetään hedelmien nimet, mutta jos valitsen avattavasta valikosta Vihannekset, minulle näytetään vihannesten nimet pudotusvalikossa 2.

Tätä kutsutaan ehdolliseksi tai riippuvaiseksi pudotusvalikoksi Excelissä.

Riippuvan avattavan luettelon luominen Excelissä

Voit luoda riippuvaisen avattavan luettelon Excelissä seuraavasti:

• Valitse solu, johon haluat ensimmäisen (pää) avattavan luettelon.
• Siirry kohtaan Data -> Tietojen validointi. Tämä avaa tietojen validoinnin valintaikkunan.
• Valitse tietojen validointi -valintaikkunan asetusvälilehden Lista.
• Määritä Lähde -kentässä alue, joka sisältää ensimmäisessä avattavassa luettelossa näytettävät kohteet.
• Napsauta OK. Tämä luo pudotusvalikon 1.
• Valitse koko tietojoukko (tässä esimerkissä A1: B6).
• Valitse Kaavat -> Määritetyt nimet -> Luo valinnasta (tai voit käyttää pikanäppäintä Control + Vaihto + F3).
• Valitse Luo nimetty valinta -valintaikkunassa Ylärivi -vaihtoehto ja poista kaikki muut valinnat. Tämä luo kaksi nimialuetta ('Hedelmät' ja 'Vihannekset'). Hedelmät nimetty alue viittaa kaikkiin luettelon hedelmiin ja vihannekset nimetty alue viittaa kaikkiin luettelon vihanneksiin.
• Napsauta OK.
• Valitse solu, johon haluat riippuvan/ehdollisen pudotusvalikon (tässä esimerkissä E3).
• Siirry kohtaan Data -> Tietojen validointi.
• Varmista tietojen validointi -valintaikkunan asetusvälilehdessä, että Lista on valittuna.
• Kirjoita Lähde -kenttään kaava = Epäsuora (D3). Tässä D3 on solu, joka sisältää pääpudotusvalikon.
• Napsauta OK.

Kun teet valinnan pudotusvalikossa 1, pudotusvalikossa 2 luetellut vaihtoehdot päivittyvät automaattisesti.

Lataa esimerkkitiedosto

Miten tämä toimii? - Ehdollinen pudotusvalikko (solussa E3) viittaa = epäsuoraan (D3). Tämä tarkoittaa, että kun valitset "Hedelmät" solussa D3, E3: n pudotusvalikko viittaa nimettyyn alueeseen "Hedelmät" (epäsuoran toiminnon kautta) ja luettelee siten kaikki kyseisen luokan kohteet.

Tärkeä muistiinpano: Jos pääluokka on useampi kuin yksi sana (esimerkiksi 'Kausiluonteiset hedelmät' hedelmien sijasta), sinun on käytettävä kaavaa = Epäsuora (KORVAUS (D3, "", "_")) sijasta yksinkertainen INDIRECT -toiminto yllä.

• Syynä tähän on se, että Excel ei salli välilyöntejä nimetyillä alueilla. Joten kun luot nimetyn alueen käyttämällä useampaa kuin yhtä sanaa, Excel lisää automaattisesti alaviivan sanojen väliin. Jos esimerkiksi luot nimettyä aluetta kausiluonteisilla hedelmillä, sen taustalla on nimi Season_Fruits. KÄYTTÖ -toiminnon käyttäminen epäsuorassa -toiminnossa varmistaa, että välilyöntejä on ovat muutettu alaviivoiksi.

Nollaa/tyhjennä riippuvaisen pudotusvalikon sisältö automaattisesti

Kun olet tehnyt valinnan ja muutat sitten ylätason avattavaa valikkoa, riippuvainen avattava luettelo ei muutu ja olisi siksi väärä merkintä.

Jos esimerkiksi valitset luokkaan "Hedelmät" ja valitset sitten kohteeksi Apple ja palaat sitten takaisin ja vaihdat luokan "Vihannekset", riippuvainen pudotusvalikko näyttää edelleen Applea.

VBA: n avulla voit varmistaa, että riippuvan avattavan luettelon sisältö nollautuu aina, kun päävalikkoa muutetaan.

Tässä on VBA -koodi, jolla tyhjennetään riippuvaisen avattavan luettelon sisältö:

Yksityinen alityöarkki_muutos (ByVal -kohde alueena) Virheessä Jatka seuraavaksi, jos kohde. Sarake = 4 Sitten, jos kohde.Validation.Type = 3 Sitten Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End End Jos End If exit Application.EnableEvents = Todellinen poistuminen alipäästä

Tämän koodin ansioksi tulee tämä Debra -opetusohjelma riippuvien avattavien luettelojen tyhjentämisestä Excelissä, kun valintaa muutetaan.

Näin saat koodin toimimaan seuraavasti:

• Kopioi VBA -koodi.
• Siirry Excel -työkirjassa, jossa on riippuvainen avattava luettelo, siirry Kehittäjä -välilehteen ja napsauta Koodi -ryhmässä Visual Basic (voit käyttää myös pikanäppäintä - ALT + F11).
• VB Editor -ikkunassa, projektinhallinnan vasemmalla puolella, näet kaikki laskentataulukon nimet. Kaksoisnapsauta sitä, jolla on avattava luettelo.
• Liitä koodi oikealla olevaan koodi -ikkunaan.
• Sulje VB Editor.

Nyt kun muutat päävalikkoa, VBA -koodi laukaistaan ​​ja se tyhjentää riippuvan avattavan luettelon sisällön (kuten alla).

Jos et ole VBA: n fani, voit myös käyttää yksinkertaista ehdollista muotoilutemppua, joka korostaa solun aina, kun se ei täsmää. Tämä voi auttaa sinua näkemään ja korjaamaan epäsuhteen visuaalisesti (kuten alla).

Tässä ovat vaiheet t0 korostamaan epäsuhtauksia riippuvissa pudotusvalikoissa:

• Valitse solu, josta on riippuvainen avattava luettelo.
• Siirry etusivulle -> Ehdollinen muotoilu -> Uusi sääntö.
• Valitse Uusi muotoilusääntö -valintaikkunassa 'Käytä kaavaa määritettävien solujen määrittämiseen'.
• Kirjoita kaavakenttään seuraava kaava: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Aseta muoto.
• Napsauta OK.

Kaava käyttää VLOOKUP -toimintoa tarkistaakseen, onko riippuvaisen pudotusvalikon kohde pääluokasta vai ei. Jos ei, kaava palauttaa virheen. ISERROR -funktio käyttää tätä palauttaakseen TOSI, joka kertoo ehdollisen muotoilun korostamaan solun.

Saatat pitää myös seuraavista Excel -opetusohjelmista:

• Pura tiedot avattavan luettelon perusteella.
• Avattavan luettelon luominen hakuehdotuksilla.
• Valitse useita kohteita avattavasta luettelosta.
• Luo useita avattavia luetteloita ilman toistoa.
• Säästä aikaa Excelin tietojen syöttölomakkeilla.