Kelpoisuustarkistusluettelo dynaamiseksi

Kelpoisuustarkistusluettelo dynaamiseksi

Voit käyttää Excelissä kelpoisuustarkistusta (data validation), jos haluat rajoittaa soluun tai solualueelle syötettäviä tietoja tai haluat helpottaa tietojen syöttämistä solusta avautuvan luettelon avulla. Kelpoisuustarkistusten avulla saat aikaan taulukoita, joihin sinä tai kollegasi ette voi syöttää ihan mitä tahansa.

H-sarakkeeseen on luotu kelpoisuustarkistus (Data > Validation, Tiedot > Kelpoisuustarkistus), joten soluihin hyväksytään vain jokin neljästä luetellusta raaka-aineesta:

kelpoisuustarkistus 1
(Jos kummastelet taikinoita ja raaka-aineita, lue Onsightin blogista edellinen juttuni eli Tarina leipurista ja vanhanaikaisesta Excel-työkirjasta.)

Kelpoisuusluettelon voi muodostaa myös taulukkoon syötetyistä arvoista, kuten alla olevassa kuvassa, jossa lähdealueeksi on valittu alue $D$2:$D$5.

kelpoisuustarkistus2

Dynaamisuus

Entä jos haluaisit kelpoisuusluettelon kasvavan automaattisesti, kun syötät raaka-aineluetteloon uusia raaka-aineita?

Ensimmäinen mieleen tuleva vaihtoehto saattaisi olla raaka-aineluettelon muuntaminen taulukoksi. Taulukothan ovat dynaamisia ja kasvavat kun niihin lisätään tietoja! Jospa taulukolle antaisi nimeksi Ainekset ja käyttäisi nimeä kelpoisuustarkistuksen lähdealueena, esim. =Ainekset tai =Ainekset[Raaka-aine]

Mutta … eipäs juhlita voittoa etukäteen …

… nimittäin taulukot ovat olleet Excelissä vasta 2007-versiosta lähtien ja valitettavasti Microsoftin Excel tiimi on unohtanut päivittää kelpoisuustarkistus-toiminnon uudelle taulukko-aikakaudelle. Kelpoisuustarkistusikkuna ei hyväksy lähdealueeksi taulukon nimeä, vaikka kuinka yrittäisit. Tavalliset Excelin aluenimet sille kyllä kelpaavat, mutta taulukkonimet eivät. Piste.

Saamme automaattisesti muuttuvan luettelon kuitenkin toimimaan pienellä ylimääräisellä operaatiolla, joka perustuu siihen, että kelpoisuustarkistuksessa voi käyttää tavallista nimeä ja tavallisessa nimessä voi puolestaan viitata taulukkoon.

Ohje

Muuta ensin luettelosi taulukoksi ja anna sille myös kuvaava nimi Design (Rakenne)  –välilehdeltä:

ainekset

Lisää työkirjaan tämän jälkeen myös tavallinen aluenimi Formulas > Define Name (Kaavat > Määritä nimi) –toiminnolla ja määritä Refers to (Viittaus) –kenttää juuri luomasi taulukon nimi:

dynaaminen nimi

Näin olet luonut dynaamisen nimen Aineet, jota voit käyttää kelpoisuustarkistuksessasi ja kelpoisuustarkistusluettelosi muuttuu automaattisesti, kun teet muutoksia Ainekset-taulukkoon.

kelpoisuustarkistus 3

PS. Juu, kyllä muitakin tapoja on. Alueeseen voi määrittää kasvuvaraa ja käyttää kelpoisuustarkistuksessa ylisuurta aluetta (=$D$2:$D$50). Tällöin luettelossa myös näkyy kymmenittäin tyhjiä vaihtoehtoja ja Excel ehdottaa luettelon avaamisen yhteydessä ensimmäistä tyhjää vaihtoehtoa, joten käyttäjän pitää vierittää esiin ne oikeat vaihtoehdot. Jos käytät tätä kasvuvaraan perustuvaa tapaa, jätä luettelon ensimmäinen solu tyhjäksi ($D$2), jotta Excel ehdottaa sitä eikä luettelon alapuolelta löytyvää tyhjää solua. Muitakin funktioihin ja dynaamisuuteen perustuvia tapoja on, mutta minun mielestäni taulukon ja aluenimen yhdistäminen on se kaikkein helpoin ja tyylikkäin tapa.

2 kommenttia artikkeliin ”Kelpoisuustarkistusluettelo dynaamiseksi

  1. Mahtavaa!! Kelpoisuustarkistuksen voinkin sitten ruksia omalta kirjoita näistä -listalta hoidettuna. 😀

    Tuo on ehdottomasti tyylikkäin tapa hoitaa homma. Datataulut sopivat niin moneen. Ennen vanhaan kun miehet olivat miehiä ja naiset koodasivat Exceliä Notepadilla, dynaamiset aluenimet tehtiin OFFSETilla ja COUNTilla. 🙂

    Tykkää

  2. Aivan, melko usein edelleen käytän OFFSET – COUNT (SIIRTYMÄ – LASKE) -funktioihin perustuvaa dynaamista aluenimitekniikkaa, kun pitää piirtää kaavioita, jotka osaavat poimia aineistosta esim. kymmenen viimeistä arvoa.

    Tykkää

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s