Ak potrebujeme definovať presné pravidlá pre vypisovanie údajov v Exceli, použijeme nástroj [Overenie údajov]. Tiež je to jeden z nástrojov, ktorý môžeme využiť aj za účelom, na ktorý nebol vyvinutý. Poďme si ho teda vysvetliť.
Kde nástroj [Overenie údajov] nájdeme?
Zobrazí sa nám dialóg, kde už môžeme podrobne nastavovať pravidlá a na ďalších kartách správanie. Ale poďme postupne.
Vždy pred definíciou pravidiel potrebujeme vybrať bunky (väčšinou celý stĺpec).
Definícia [Overenia údajov]
Prvá záložka z dialógu [Overenie údajov] slúži na definovanie povolených hodnôt pre bunky.
Overovanie údajov prebieha vtedy, keď je zadávaná hodnota do bunky. Teda, ak je hodnota už zadaná, overovať ju v tom momente nebude. Tiež sa overovať bude pri prepisovaní – vlastne znovu zadaní.
V princípe je už overovaná každá bunka, ale na akúkoľvek hodnotu. V prípade potreby obmedzenia si môžeme vybrať zo základných možností:
- číslo
- celé číslo
- desatinné číslo
- dátum a čas
- dĺžka textu
- zoznam
- vlastné – vzorec
Príklad 1 – [Dĺžka textu]
Pre spracovanie údajov pre prihlásenie do systému (prihlasovacie údaje – meno a heslo) alebo rôzne kódy tovaru, dátumu a podobne, použijeme napr. obmedzenie [Dĺžka textu]. Vytvorme si teda príklad – tabuľku týchto údajov. V prázdnom hárku si napíšeme zatiaľ len nadpisy stĺpcov: [Meno], [Heslo], [Kód tovaru], [Dátum záznamu].
Pre zjednodušenie vyberieme celý stĺpec, pre ktorý potrebujeme zadefinovať pravidlo. Nevadí, že vyberieme aj bunku v prvom riadku, pretože tá má hodnotu už zadanú. teda nebude overovaná.
Takže v našom prípade vyberieme prvé dva stĺpce [Meno] a [Heslo]. Tam máme napríklad pravidlo, že musí byť minimálne 8 znakov a maximálne 12. A tak prejdeme na nástroj [Overenie údajov], potom vyberieme [Dĺžka textu] a spresníme [je medzi] s hodnotami [8] a [12]. Dialóg potvrdíme.
Tým sme vytvorili pravidlo, kde Excel bude kontrolovať zadávané hodnoty do týchto stĺpcov. Môžeme písať vlastne čokoľvek, pretože za text sa považuje napríklad aj číslo. Dôležité je, že musíme dodržať stanovený rozsah počtu znakov 8-12. A tak si to poďme vyskúšať:
- Ak pôjdeme zadať meno [peter], tak nás Excel upozorní na nedodržané pravidlo.
- V prípade prihlasovacieho mena [peter153] hodnotu ponechá zadanú, teda prešla testom pravidla.
- Excel dovolí zadať aj len číslo, napr. [1234567890], ako som písal.
- Ak zadáme text dlhší ako 12, znovu nás Excel zastaví.
Ako vidíme, v prípade nesprávnej hodnoty, Excel požaduje jednu z dvoch možností:
- [Znova] zadať hodnotu – opraviť zadanie.
- [Zrušiť] zadanie nesprávnej hodnoty – stornovať zadanie.
Ďalší stĺpec môžeme nastaviť na minimálnu dĺžku textu 5 znakov. Čiže vyberieme stĺpec [Kód tovaru], potom prejdeme na nástroj [Overenie údajov], kde nastavíme [Dĺžka textu], [väčší než], [5].
Opäť si poskúšame zadať rôzne hodnoty.
Stĺpec [Dátum záznam] môže byť tiež napr. len kód dátumu, teda osemciferné číslo [20131231]. A tam nastavíme pevnú dĺžku textu na [8], voľbou [rovná sa].
Skúšajme hodnoty.
Príklad 2 – [Dátum]
Pri dátume si ukážeme trocha dynamickejšie možnosti, pretože jednoduché obmedzenie je v princípe rovnaké ako pri čísle.
Pridajme si do tabuľky ďalšie stĺpce na ukážku: [Dátum narodenia], v prípade napríklad faktúr môže byť ďalší stĺpec [Dátum vystavenia] a [Dátum splatnosti].
Dátum narodenia by bolo vhodné overovať na dnešný dátum, teda dynamicky. Tu nebudeme zadávať pevný dátum, ale funkciu, ktorá bude dopĺňať do pravidla aktuálny dátum. Takže pre stĺpec [Dátum narodenia] zadáme overenie na [Dátum] a [je menší ako alebo rovný] s vypočítanou hodnotou [=ToDay()] (príp. Dnes).
Vyskúšame si, že zadať nemôžeme číslo ani text, ale len dátum a to tiež len do dnešného. Samozrejme, že zajtra už zajtrajšieho. Dátumy, ktoré už sú zadané, nie sú overované, ako sme si už viackrát písali.
A čo sa týka dátumov pre faktúry, bude niečo podobné. Pre [Dátum vystavenia] potrebujeme napríklad definovať pravidlo, aby nikto nemohol vystaviť starú faktúru, teda so starším dátumom vystavenia ako dnešným. A tak pre stĺpec [Dátum vystavenia] zadáme opäť [Dátum], ale [je väčší ako alebo rovný] s výpočtom [=ToDay()].
Vyskúšajme zadávať rôzne hodnoty.
Stĺpec [Dátum splatnosti] som pridal, aby sme si ukázali aj to, čo nevieme overiť správne. Napríklad by sme potrebovali stanoviť pravidlo s max 30-dňovou splatnosťou faktúry. To znamená, že potrebujeme overovať na [Dátum vystavenia], čo zadať vieme pre stĺpec [Dátum splatnosti]: [Dátum] s intervalom [je medzi] a zadáme počiatočnú hodnotu [=D1] (teda odkaz na bunku dátumu vystavenia a koncovú hodnotu jednoduchý výpočet [=D1+14]. Tu nám program zahlási chybu, pretože máme v [D1] text a sním sa nedá počítať, len potvrdíme, že je to v poriadku. Tým si len zjednodušujeme zadanie a všetko funguje. Inak by sme museli komplikovane vyberať bunky bez hlavičky.
Tu si treba uvedomiť, že fungovať to bude, ale len za určitých podmienok, ktoré nevieme vždy zabezpečiť: Musel by byť najprv zadaný [Dátum vystavenia] a potom [Dátum splatnosti]. [Dátum vystavenia] by sa už potom nemal meniť, pretože pri tejto zmene sa nebude overovať [Dátum splatnosti]. Overuje sa vždy len hodnota, ktorú práve zadávame alebo meníme. A preto takéto overenie pre [Dátum splatnosti] nevieme definovať spoľahlivo (, teda bez programovania nie).
Príklad 3 – [Zoznam]
Doplníme si do tabuľky stĺpec [Mesiac]. Tu potrebujeme napríklad zadávať názov mesiaca [Január] až [December]. Máme viac možností:
Alternatíva [zoznamu] A)
Ten zoznam si potrebujeme niekde vytvoriť. Tak vpravo od tabuľky vyplníme napr. pod seba zoznam všetkých mesiacov.
Potom na stĺpec [Mesiac] pôjdeme vytvoriť pravidlo na [Zoznam], kde do políčka zadáme odkaz na existujúci zoznam v hárku. (Vložíme kurzor do políčka [Zdroj] a označíme bunky, kde sme zadali mesiace.)
Ak vyskúšame zadať čokoľvek, čo sa v zozname nenachádza, hodnota nemôže ostať zadaná. Napísať môžeme naozaj len niektorú hodnotu zo zoznamu.
No a práve z toho dôvodu vznikajú rôzne iné potreby zadania [Overenia]. Preto poďme ďalej:
Alternatíva [zoznamu] B)
Zoznam, ak nie je dlhý, môžeme zadať priamo do políčka [Zdroj]. Môžeme si vyskúšať zmeniť stĺpec [Mesiac]. Zmenu urobíme naozaj len v zdroji a to vypísaním položiek zoznamu oddelených symbolom [;] (bodkočiarkou). Napr. [Január;Február;Marec]…
Výsledok pre použitie bude rovnaký ako v predošlom príklade. Opäť vyskúšame.
Alternatíva [zoznamu] C)
Ak zoznam potrebujeme mať vypísaný v bunkách, ale nechceme ho zviditeľňovať alebo sprístupniť pre toho, kto má vypisovať tabuľku, máme k dispozícii zopár možností.
Mohli by sme zadať zoznam do stĺpca za tabuľkou a stĺpec uzamknúť proti úpravám. Nebudem to rozoberať v tomto článku, ale v článku o zabezpečení.
Potom by sme mohli stĺpec skryť a možno aj uzamknúť proti úpravám, príp. len zabrániť zobrazeniu stĺpca.
Toto všetko sú však nepraktické riešenia. Uvádzam len, ako ľudia bežne uvažujú. Nepraktické napr. z toho dôvodu, že môžeme potrebovať využiť ďalšie stĺpce pre tabuľku údajov alebo len využívať bežné príkazy na prechod alebo výber (Ctrl + End a pod.) A tie by nefungovali podľa potreby. Áno, bolo by v tom prípade čistejšie riešenie dať zoznam do prvého stĺpca a ten skryť. Ale ani to nie je pekné riešenie.
Iné riešenie by bolo mať zoznam v inom hárku. Tam môžeme do verzie 2007 naraziť na problém, ak budeme zadávať odkaz na zoznam pre overenie. Excel sa totiž tvári, že to nie je možné. Vyskúšajme si to. Presuňme zoznam mesiacov do iného hárka. Ak v pôvodnom hárku budeme pre stĺpec [Mesiac] zadávať [Overenie údajov] s nastavením [Zoznam] a budeme sa pokúšať označiť zoznam z iného hárka, tak to do verzie 2007 nepôjde.
Riešiť sa to však dá napr. cez [pomenovanie]. Pretože [pomenovanie oblasti]je použiteľné kdekoľvek v zošite. Správny postup pre všetky verzie je vybrať bunky zoznamu a vľavo hore (na riadku vzorcov, kde máme adresu bunky) v [poli názvov] zadáme pomenovanie napr. [Mesiace]. Potom v tabuľke pre stĺpec [Mesiac] definujeme [Overenie údajov] s pravidlom [Zoznam] a do políčka [Zdroj] zadáme odkaz na pomenovanie oblasti [=Mesiace]. Pozor na symbol [=] (rovná sa), ten tam musí byť zadaný! Opäť môžeme vyskúšať funkčnosť overovania. Hárok so zoznamom mesiacov môžeme bez obáv skryť, uzamknúť alebo inak zneprístupniť. Je to čisté riešenie, ktoré nám neovplyvňuje akúkoľvek funkčnosť a prácu s tabuľkou, ktorú máme vypisovať.
Príklad 4 – [Vlastné] (vzorec)
Pri tejto voľbe je potrebné hlavne dobre pustiť fantáziu, pretože overenie je definované vzorcom a hlavne funkciami. Vo veľa prípadoch je potrebné sa dobre „pohrať” s absolútnym a relatívnym odkazom.
Aby som vás nezaťažil zložitými zadaniami, urobím len jednu ukážku. Zložité riešenia si nechám na neskôr, keď už budem mať popísaných viac základnejších tém k Excelu.
Vytvorme si pravidlo do nového hárka pre prvé 4 bunky pod sebou. Pre lepšiu orientáciu si ich môžeme zafarbiť a ohraničiť. Bunky vyberieme a nastavíme [Overovacie pravidlo]: Vyberieme voľbu [Vlastné] a do políčka [Vzorec] zadáme [=CountIf($A$1:$A$4; A1)=1]. Týmto pravidlom sme určili vzťah medzi bunkami, ktorý určuje, že položky musia byť zadané tak, aby nevznikli duplicity. Vyskúšame si zadať hodnoty napr. [a], [b], [c], [d]. Ak pôjdeme do poslednej bunky zadať napríklad hodnotu [c], tak nás program nepustí ďalej.
Nastavenie správania
V ďalších kartách dialógu nástroja [Overenie údajov] môžeme definovať, ako sa má overovanie správať. Môžeme definovať len oznamovaciu správu, podľa čoho užívateľ bude vedieť, akú hodnotu môže zadať. Druhá možnosť je nastavenie chybovej správy, ktorá sa zobrazí pri pokuse o zadanie nesprávnej hodnoty.
Vstupná správa
Nastavuje sa tu oznamovacia správa, ktorá sa zobrazuje podobne ako komentár bunky. Stačí, ak je bunka vybraná a správa sa sama objaví. Nedá sa vypnúť užívateľsky ako komentár. V nastavení máme k dispozícii 3 vlastnosti:
- Či sa má vôbec niečo zobrazovať. Hlavne vtedy, ak je vôbec správa zadaná.
- [Názov] – ide o nadpis správy. Napr. [Mesiac].
- [Vstupná správa] – podrobný popis oznamovacej správy pre zadanie. Napr. [Zadajte alebo vyberte názov mesiaca.]
Chybové hlásenie
Tu máme tri základné nastavenia správania sa, ak je zadaná nesprávna hodnota. Ide o akýsi stupeň dôležitosti dodržania definovanej hodnoty.
Zastavenie
Je to najvyšší stupeň, kde je povinné dodržať zadané overovacie pravidlo. Hodnotu musíme opraviť na správnu alebo stornovať zadanie nesprávnej hodnoty. Napríklad, ak ide o pravidlo, kde nemôžeme dovoliť vystavenie starej faktúry.
Upozornenie
Stredný stupeň upozorní a ponúkne na výber, ako chceme situáciu riešiť. Môžeme potvrdiť a pokračovať v zadaní „nesprávnej” hodnoty voľbou [Áno]. Alebo opraviť zadanie voľbou [Nie] a zrušiť zadanie voľbou [Zrušiť]. Ak ide napríklad o mzdu, ktorá je vo firme štandardne od 500 do 1000 €.
Informácia
Najnižší stupeň, kde Excel len oznámi správu o „nesprávnej” hodnote a nechá ju zadanú. Napríklad kód tovaru by mal mať 8 znakov, ale môže byť aj výnimka. To pravidlo by sme len na upozornenie prípadného „preklepu”.
Dôležité!
Musím upozorniť na spôsob, akým nám Microsoft pripravil nástroj [Overenie údajov]. V princípe slúži na definovanie pravidiel, aké údaje môžeme do buniek zadávať. Toto pravidlo stráca význam a svoju funkčnosť vtedy, ak údaje budeme kopírovať. Presnejšie, ak máme napríklad zadané pravidlo pre bunku [A2] len pre číslo a to v rozsahu [1] až [12] a do bunky napr. [B2] zadáme hodnotu [13], následne skopírujeme bunku a prilepíme do bunky [A2], tak vlastne vložíme hodnotu do bunky, ktorá tam nemá čo robiť!
Ide o jednoduchú záležitosť – program skopíruje celú bunku, teda aj overenie a tak to môže zadať do tej bunky. A to napriek tomu, že máme aj zamknuté úpravy vlastností bunky!
Ak je overenie údajov nefunkčné, skontrolujte, či:
Používatelia nekopírujú alebo nevypĺňajú údaje ….
Názor na túto správu o nefunkčnosti [Overenia údajov] si už vytvorí každý sám ;).