Andmete valideerimine ja ripploendid Excelis

Sel nädalavahetusel pusisin natuke Exceliga. PÕhjus oli proosaline ja tulemus ilus. Seepärast mõtlesin seda pisuke jagada.

Excelis on tore võimalus korduvalt kasutatavate töölehtede andmete sisestuseks. Kui tahad laisk olla ja kiiremini toimetada – Data Validation ehk Andmete Valideerimine.

Võtad lahtri või peotäie lahtreid või kasvõi terve posti (veeru) ja valid menüüribalt Andmed (Data) vastava nupu.

image 

Määrad näiteks loetelu lubatud väärtustest ja sinu töölehel tuleb selle lahtri juurde ripploend.

image

 image

 

 

 

 

 

Mulle meeldib, kui selline loend on tabelina kuskil olemas. Siis piisab tabeli täiendamisest, kui tahad midagi loetellu lisada või ka sealt eemaldada.

Üldiselt saab seal andmete valideerimises anda lihtsal viisil viiteid lahtritele, kust võtta anmeid. Ja Excelis saab ilma vaevata viidata tabelile kujul =Tabel[Veerunimi]. Paraku need kaks Excelis koos ei toimi. Andmevalideerimisel viide tabelile (saati siis veerule) ei toimi – tabelinimed ei ole Excelis “päris nimed”.

Sellest piirangust sain lahti lihtsasti: kirjeldasin lihtsalt nimelise ploki.

image

  • Tabeli nimeks mul: Tabel1.
  • Nimeplokki lisasin nime TabelNimi väärtusega =Tabel1[Nimetus]

Nüüd sain andmevalideerijale öelda, et mu loetelu on plokis =TabelNimi

image

Igati toimiv ja üldse mitte keeruline lahendus.

Nüüd tuli aga mul keerukam mõte. Ma polnud seda varem Excelis teha püüdnud – mitmetasemeline ripploend. See on selline, kus alguses valid suurema portsu ja siis sealt konkreetsema. Näiteks alguses valid maakonna ja siis valla.

Kuna ma olen selline juurikate-kaalikate armastaja, siis tegin väikese näite kõikvõimalike kapsate ja kartulitega.

Koostasin sellised kaks tabelit:

image

Esimene tabel sisaldab kõiki juurikaid, mida ma tunnen (oi herned unustasin). Sellele tabelile panin nimeks “Viljad” (NB! see on tabeli nimi mitte ploki nimi)

Teine tabel sisaldab juurikate gruppe ja kannab nime “GrupiTabel”. Panin sinna kaks veergu – ük snäitab, kust reast vastav grupp algab, teine mitmest reast koosneb. Vastavad valemid tabelis on:

=MATCH([Grupp];Viljad[Grupp];0)              //NB see viimane seal on null

=COUNTIF(Viljad[Grupp];[@Grupp])

Selle lahenduse puhul on oluline, et see suurem tabel oleks sorteeritud ja just selle grupi nime veergu pidi.

Nüüd tegin juba tuttava nime, et saaks ripploendiks kasutada:

image

GrupiNimi =GrupiTabel[Grupp]         // =tabelinimi[veerunimi]

JA nüüd tuleb kogu loo AINUKE keeruline koht. Ma pean kuidagi sellele andmete valideerijale suutma ette mängida et võtku minu viljade tabelist ainult see osa, mis kuulub minu valitud gruppi.image

Selleks läksin ühte lahtrisse (Siin on oluline, mis lahtrisse sa lähed – valemi kirjutamisel omab see tähtsust) –  mina valisin lahtrisse P4 – ja kirjutasin SEAL OLLES oma nimetabelisse uue blokinime “Vili” ja määrasin selle lahtri P4 andmevalideerimise loendiks.

Vili =OFFSET(Viljad;VLOOKUP(Leht2!O4;GrupiTabel;2;FALSE)-1;0;VLOOKUP(Leht2!O4;GrupiTabel;3;FALSE);1)

Vaatame korraks sellele valemile otsa. Põhineb see =OFFSET funktsioonil, millele antakse ette viis parameetrit:

1. Lahter, millest alates minu viide peaks arvutatama (minul tabel Viljad)

2. mitu rida tabeli nurgast allapoole (minu GrupiTabelis teine veerg annab selle – sealt otsimiseks kasutan grupinime).

3. Mitu veergu tabeli nurgast paremale – ei ühtegi

4. Mitmerealist asja ma selleks loeteluks tahan – GrupiTabel annab selle mulle kolmandast veerust)

5. kuna mul vaja ühte veergu andmeid, siis viies parameeter on 1

Üks võtmetegija on siin =VLOOKUP, millega sealt Grupitabelist andmeid loen. Oluline on siin anda ette otsitav ilma dollariteta (suhtelise viitena). Nüüd saab minu näidises olevat valikut “ülalt alla” lohistada või otse mõnes tabelis kasutada.

Et lihtsam oleks jälgida, proovida ja katsetada, panin tänase näidise oma tavapärasesse kohta http://www.sarv.ee/ftp/henn/excel

nime all JUURIKAVALIDEERIMINE.XLSX

Jõudu katsetamisel ja igasugune tagasiside on tere ja tulnud

PS! KOlmandale lehele tegin veel ühe variandi. PEaltnäha lihtsam, aga nõuab palju tabeleid – kui gruppe on vähe, siis on ehk isegi mugavam. Siin on hästi oluline tabeli nimedega ja ploki nimedega korrektne “mängimine”. Siin kasutan ma funktsiooni =INDIRECT

Henn

This entry was posted in Andmed, Excel, Funktsioonid, Funktsioonid, Office and tagged . Bookmark the permalink.

Andmete valideerimine ja ripploendid Excelis on saanud 4 vastust

  1. Toomas Mölder ütles:

    Suurepärane! Kasutasin mitmetasemelise valideerimise ideid ka oma isikliku eelarvetabeli koostamisel. Tänud!

    > … blokinime “Vili” ja määrasin selle lahtri P4 andmevalideerimise loendiks. Vili =OFFSET(Viljad;VLOOKUP(Leht2!O4;GrupiTabel;2;FALSE)-1;0;VLOOKUP(Leht2!O4;GrupiTabel;3;FALSE);1)

    Siin pole vist see eraldi blokinimi isegi vajalik, piisab ka kui valem =OFFSET… kirjutada otse andmevalideerijaks.

    Oleksin eriti “ilusana” tahtnud ka, et viite Leht2!O4 asemel õnnestub ka seal kasutada tabelit (=$O$3:$Q$4 with headers) ja andmevalideerimise valemis tabeliviidet [@Grupp] =OFFSET(Viljad;VLOOKUP([@Grupp];GrupiTabel;2;FALSE)-1;0;VLOOKUP([@Grupp];GrupiTabel;3;FALSE);1) aga see veel Exceli kõhus ei õnnestu …

    • sarviktaat ütles:

      Jahh on mõned kohad, kus tabelinimed veel ei toimi. Üks on andmevalideerimise piirkonnad. Teine on tingimusliku vormindamise valemid

      seal aitabki nimeliste plokkide nipp – nimelise ploki valemis toimib tabelinimi ilusti. Nimeline plokk jälle sobib valideerijale

      Henn

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s