Avattavan luettelon luominen Excelissä (ainoa tarvitsemasi opas)

Avattava luettelo on erinomainen tapa antaa käyttäjälle mahdollisuus valita ennalta määritetystä luettelosta.

Sitä voidaan käyttää, kun käyttäjä saa lomakkeen täytettyä tai kun luodaan interaktiivisia Excel -hallintapaneeleja.

Avattavat luettelot ovat melko yleisiä verkkosivustoilla/sovelluksissa ja ovat erittäin intuitiivisia käyttäjälle.

Katso video - avattavan luettelon luominen Excelissä

Tässä opetusohjelmassa opit luomaan avattavan luettelon Excelissä (se kestää vain muutaman sekunnin) sekä kaikki mahtavat asiat, joita voit tehdä sen kanssa.

Kuinka luoda pudotusvalikko Excelissä

Tässä osassa opit tarkat vaiheet avattavan Excel-luettelon luomiseksi:

1. Solujen tietojen käyttäminen.
2. Tietojen syöttäminen manuaalisesti.
3. OFFSET -kaavan avulla.

#1 Solujen tietojen käyttäminen

Oletetaan, että sinulla on luettelo kohteista alla olevan kuvan mukaisesti:

Voit luoda Excel -pudotusvalikon seuraavasti:

1. Valitse solu, johon haluat luoda avattavan luettelon.
2. Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
3. Valitse Tietojen vahvistus -valintaikkunan Asetukset -välilehden Validointiehdot -luettelo.
   • Heti kun valitset Lista, lähdekenttä tulee näkyviin.
4. Kirjoita lähdekenttään = $ A $ 2: $ A $ 6 tai napsauta Lähde -kenttää ja valitse solut hiirellä ja napsauta OK. Tämä lisää avattavan luettelon soluun C2.
   • Varmista, että Soluissa oleva pudotusvalikko on valittu (joka on oletusarvoisesti valittu). Jos tämä vaihtoehto ei ole valittuna, solu ei näytä avattavaa valikkoa, mutta voit kirjoittaa arvot manuaalisesti luetteloon.

merkintä: Jos haluat luoda avattavia luetteloita useisiin soluihin kerralla, valitse kaikki solut, joihin haluat luoda sen, ja noudata sitten yllä olevia ohjeita. Varmista, että soluviittaukset ovat absoluuttisia (kuten $ A $ 2) eivätkä suhteellisia (kuten A2, A $ 2 tai $ A2).

#2 Syötä tiedot manuaalisesti

Yllä olevassa esimerkissä Lähde -kentässä käytetään soluviittauksia. Voit myös lisätä kohteita suoraan syöttämällä ne manuaalisesti lähdekenttään.

Oletetaan esimerkiksi, että haluat näyttää kaksi vaihtoehtoa, Kyllä ja Ei, solun avattavassa valikossa. Voit syöttää sen suoraan tietojen validointilähteen kenttään seuraavasti:

• Valitse solu, johon haluat luoda avattavan luettelon (solu C2 tässä esimerkissä).
• Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
• Valitse Tietojen validointi -valintaikkunan Asetukset -välilehden Validointiehdot -luettelo.
  • Heti kun valitset Lista, lähdekenttä tulee näkyviin.
• Kirjoita lähdekenttään Kyllä, Ei
  • Varmista, että pudotusvalikko on valittu.
• Napsauta OK.

Tämä luo avattavan luettelon valittuun soluun. Kaikki lähdekentässä luetellut kohteet pilkuilla erotettuna on lueteltu avattavan valikon eri riveillä.

Kaikki lähdekenttään syötetyt kohteet pilkulla erotettuna näytetään avattavan luettelon eri riveillä.

merkintä: Jos haluat luoda avattavia luetteloita useisiin soluihin kerralla, valitse kaikki solut, joihin haluat luoda sen, ja noudata sitten yllä olevia ohjeita.

#3 Excel -kaavojen käyttäminen

Sen lisäksi, että valitset soluista ja syötät tietoja manuaalisesti, voit myös käyttää lähdekentän kaavaa Excel -pudotusvalikon luomiseen.

Mitä tahansa kaavaa, joka palauttaa arvoluettelon, voidaan käyttää avattavan luettelon luomiseen Excelissä.

Oletetaan esimerkiksi, että sinulla on alla esitetyt tiedot:

Tässä on vaiheet Excel -pudotusvalikon luomiseksi OFFSET -toiminnolla:

• Valitse solu, johon haluat luoda avattavan luettelon (solu C2 tässä esimerkissä).
• Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
• Valitse Tietojen validointi -valintaikkunan Asetukset -välilehden Validointiehdot -luettelo.
  • Heti kun valitset Lista, lähdekenttä tulee näkyviin.
• Kirjoita Lähde -kenttään seuraava kaava: = OFFSET ($ A $ 2,0,0,5)
  • Varmista, että pudotusvalikko on valittu.
• Napsauta OK.

Tämä luo avattavan luettelon, jossa luetellaan kaikki hedelmien nimet (kuten alla).

merkintä: Jos haluat luoda avattavan luettelon useista soluista kerralla, valitse kaikki solut, joihin haluat luoda sen, ja noudata sitten yllä olevia ohjeita. Varmista, että soluviittaukset ovat absoluuttisia (kuten $ A $ 2) eivätkä suhteellisia (kuten A2, A $ 2 tai $ A2).

Miten tämä kaava toimii ??

Yllä olevassa tapauksessa käytimme OFFSET -toimintoa avattavan luettelon luomiseen. Se palauttaa luettelon kohteista ra

Se palauttaa luettelon kohteista alueelta A2: A6.

Tässä on OFFSET -toiminnon syntaksi: = OFFSET (viite, rivit, sarakkeet, [korkeus], ​​[leveys])

Se vaatii viisi argumenttia, joissa määritimme viittaukseksi A2 (luettelon lähtökohta). Rivit/Cols määritetään 0: ksi, koska emme halua muuttaa viitesolua. Korkeus on 5, koska luettelossa on viisi elementtiä.

Kun käytät tätä kaavaa, se palauttaa taulukon, jossa on luettelo viidestä hedelmästä kohdassa A2: A6. Huomaa, että jos kirjoitat kaavan soluun, valitse se ja paina F9 -näppäintä, näet, että se palauttaa hedelmien nimien taulukon.

Dynaamisen pudotusvalikon luominen Excelissä (käyttämällä OFFSETia)

Yllä olevaa tekniikkaa kaavan käyttämiseksi avattavan luettelon luomiseksi voidaan laajentaa luomaan myös dynaaminen avattava luettelo. Jos käytät OFFSET -toimintoa, kuten yllä on esitetty, vaikka lisäät luetteloon lisää kohteita, avattava valikko ei päivity automaattisesti. Sinun on päivitettävä se manuaalisesti aina, kun muutat luetteloa.

Tässä on tapa tehdä siitä dynaaminen (eikä se ole muuta kuin pieni muutos kaavassa):

• Valitse solu, johon haluat luoda avattavan luettelon (solu C2 tässä esimerkissä).
• Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
• Valitse Tietojen validointi -valintaikkunan Asetukset -välilehden Validointiehdot -luettelo. Heti kun valitset Lista, lähdekenttä tulee näkyviin.
• Kirjoita lähdekenttään seuraava kaava: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Varmista, että pudotusvalikko Solussa on valittu.
• Napsauta OK.

Tässä kaavassa olen korvannut argumentin 5 arvolla COUNTIF ($ A $ 2: $ A $ 100, ””).

COUNTIF-toiminto laskee ei-tyhjät solut alueella A2: A100. Siten OFFSET-toiminto säätää itsensä sisältämään kaikki ei-tyhjät solut.

merkintä:

• Jotta tämä toimisi, EI saa olla tyhjiä soluja täytettyjen solujen välissä.
• Jos haluat luoda avattavan luettelon useista soluista kerralla, valitse kaikki solut, joihin haluat luoda sen, ja noudata sitten yllä olevia ohjeita. Varmista, että soluviittaukset ovat absoluuttisia (kuten $ A $ 2) eivätkä suhteellisia (kuten A2, A $ 2 tai $ A2).

Kopioi Liittäminen-pudotusvalikot Excelissä

Voit kopioida ja liittää solut tietojen validoinnilla muihin soluihin, ja se kopioi myös tietojen validoinnin.

Jos sinulla on esimerkiksi avattava luettelo solussa C2 ja haluat käyttää sitä myös C3: C6: ssa, kopioi solu C2 ja liitä se kohtaan C3: C6. Tämä kopioi avattavan luettelon ja tekee sen saataville C3: C6: ssa (pudotusvalikon kanssa se myös kopioi muotoilun).

Jos haluat kopioida vain avattavan valikon etkä muotoilua, toimi seuraavasti:

• Kopioi avattavan valikon solu.
• Valitse solut, joihin haluat kopioida avattavan valikon.
• Siirry etusivulle -> Liitä -> Liitä erityinen.
• Valitse Liitä erityiset -valintaikkunassa Liitä asetuksissa Validointi.
• Napsauta OK.

Tämä kopioi vain avattavan valikon eikä kopioidun solun muotoilua.

Varoitus avatessasi Excel -luetteloa

Sinun on oltava varovainen, kun työskentelet avattavien luetteloiden kanssa Excelissä.

Kun kopioit solun (joka ei sisällä avattavaa luetteloa) avattavan luettelon sisältävän solun päälle, pudotusvalikko menetetään.

Pahinta tässä on se, että Excel ei näytä hälytystä tai kehotetta ilmoittaa käyttäjälle, että pudotusvalikko korvataan.

Kuinka valita kaikki solut, joissa on pudotusvalikko

Joskus on vaikea tietää, mitkä solut sisältävät avattavan luettelon.

Siksi on järkevää merkitä nämä solut joko antamalla sille selkeä reuna tai taustaväri.

Kaikkien solujen manuaalisen tarkistamisen sijaan on nopea tapa valita kaikki solut, joissa on avattavia luetteloita (tai mitä tahansa tietojen validointisääntöä).

• Siirry etusivulle -> Etsi ja valitse -> Siirry erikoistilaukseen.
• Valitse Siirry erikoisvalintaikkunaan Tietojen vahvistus
  • Tietojen validoinnissa on kaksi vaihtoehtoa: Kaikki ja Sama. Kaikki valitsisivat kaikki solut, joihin on sovellettu tietojen validointisääntöä. Sama valitsisi vain ne solut, joilla on samat tietojen validointisäännöt kuin aktiivisella solulla.
• Napsauta OK.

Tämä valitsee heti kaikki solut, joihin on sovellettu tietojen validointisääntöä (tämä sisältää myös avattavat luettelot).

Nyt voit yksinkertaisesti muotoilla solut (antaa reunuksen tai taustavärin) niin, että ne näkyvät visuaalisesti etkä vahingossa kopioi toista solua siihen.

Tässä on toinen Jon Acamporan tekniikka, jonka avulla voit aina pitää avattavan nuolikuvakkeen näkyvissä. Voit myös nähdä joitakin tapoja tehdä tämä tässä herra Excelin videossa.

Riippuvan / ehdollisen Excel -pudotusvalikon luominen

Tässä on video siitä, miten Excelistä voidaan luoda riippuvainen avattava luettelo.

Jos haluat lukea enemmän kuin katsoa videota, jatka lukemista.

Joskus sinulla voi olla useita avattavia luetteloita ja haluat, että toisessa avattavassa valikossa näkyvät kohteet riippuvat siitä, mitä käyttäjä valitsi ensimmäisessä avattavassa valikossa.

Näitä kutsutaan riippuvaisiksi tai ehdollisiksi avattaviksi luetteloiksi.

Alla on esimerkki ehdollisesta/riippuvaisesta avattavasta luettelosta:

Yllä olevassa esimerkissä, kun pudotusvalikossa 2 luetellut kohteet ovat riippuvaisia ​​pudotusvalikossa 1 tehdystä valinnasta.

Katsotaan nyt, miten tämä luodaan.

Voit luoda riippuvaisen / ehdollisen 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ä huomautus työskennellessäsi ehdollisten pudotusvalikkojen kanssa Excelissä:

• Kun olet tehnyt valinnan ja muutat sitten ylätason pudotusvalikkoa, riippuvainen pudotusvalikko ei muutu ja on siksi väärä merkintä. Jos esimerkiksi valitset maana Yhdysvallat ja valitset sitten Floridan osavaltioksi ja palaat sitten takaisin ja vaihdat maan Intiaksi, osavaltio pysyy Floridana. Tässä on hyvä Debra -opetusohjelma riippuvien (ehdollisten) avattavien luettelojen tyhjentämiseksi Excelissä, kun valintaa muutetaan.
• 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. Joten kausiluonteiset hedelmät nimetty alue olisi "Seasonal_Fruits". KÄYTTÖ -toiminnon käyttäminen epäsuorassa -toiminnossa varmistaa, että välilyöntejä on ovat muutettu alaviivoiksi.