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:

(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.

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ä:

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:

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

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.