Ako obmedziť a overiť údaje vo finančnom modeli Excelu

Keď dokončíte zostavovanie finančného modelu, môžete byť v pokušení nechať si ho pre seba, pretože nechcete, aby niekto pokazil vaše vzorce alebo použil model nevhodne. Modely by mali byť založené na spolupráci, ale musíte svoj model zostaviť tak, aby ho ostatní ľahko používali a ťažko ho pokazili. Jedným zo skvelých spôsobov, ako urobiť váš model robustným pre ostatných, je použiť na model validáciu údajov a ochranu. Týmto spôsobom môže používateľ zadať iba údaje, ktoré má zadať.

Obmedzenie zadávania údajov používateľa do vášho finančného modelu

Ako praktický príklad použitia overovania údajov si vezmime túto analýzu nákladov projektu.

Výpočet dennej sadzby zamestnancov pomocou absolútneho odkazovania.

Váš kolega používa model, ktorý ste vytvorili, a podľa toho, ako bola bunka D3 naformátovaná (s tieňovaním), vie, že ste očakávali, že ľudia v nej urobia zmeny. Už si nie je istý, koľko dní bude tento projekt pokračovať, a tak namiesto toho napíše TBA do bunky D3. Hneď ako napíše TBA, to naozaj pokazí veci! Ako vidíte nižšie, vzorce, ktoré ste už vytvorili, očakávali v bunke D3 číslo, nie text.

Text vo vstupe spôsobujúci chyby.

Namiesto toho, aby ste umožnili používateľovi vkladať čokoľvek do ľubovoľnej bunky, môžete zmeniť vlastnosti tejto bunky tak, aby umožňovala zadávanie iba čísel. Môžete ho tiež zmeniť tak, aby povolil iba celé čísla alebo čísla v danom rozsahu.

Nasleduj tieto kroky:

Stiahnite si súbor 0601.xlsx a vyberte kartu označenú 6-17.

Vyberte bunku D3.

Prejdite na kartu Údaje na páse s nástrojmi a stlačte ikonu Overenie údajov v časti Nástroje údajov.

Zobrazí sa dialógové okno Overenie údajov.

Na karte Nastavenia v rozbaľovacom zozname Povoliť vyberte položku Celé číslo; v rozbaľovacom zozname Údaje vyberte možnosť Väčšie ako; a do poľa Minimum zadajte 0.

Teraz je možné do bunky D3 zadávať iba celé čísla väčšie ako nula. Skúste zadať text, napríklad TBA. Skúste zadať zápornú hodnotu. Excel to nedovolí a zobrazí sa chybové hlásenie.

Ak chcete, môžete zadať varovnú správu na karte Vstupná správa v dialógovom okne Overenie údajov. Môžete napríklad chcieť, aby sa zobrazila nasledujúca správa: „Upozornenie! Zadajte iba číselné hodnoty." Na karte Error Alert môžete zadať ďalšiu správu, ktorá sa zobrazí, ak niekto ignoruje varovanie a pokúsi sa zadať neplatný text.

Vytváranie rozbaľovacích polí s overením údajov vo vašom finančnom modeli

Nástroj na overenie údajov nielen zabráni používateľom zadávať nesprávne údaje do vášho modelu, ale môžete ho použiť aj na vytvorenie rozbaľovacích polí. V dialógovom okne Overenie údajov v rozbaľovacom zozname Povoliť vyberte položku Zoznam. Do poľa Zdroj zadajte hodnoty, ktoré sa majú zobraziť v zozname s čiarkou medzi nimi, napríklad Áno, Nie. V bunke B12 sa vytvorí jednoduchý rozbaľovací zoznam s iba dvoma možnosťami: Áno a Nie. nemôže zadať nič iné.

Použitie overenia údajov na vytvorenie jednoduchého rozbaľovacieho zoznamu.

Nikto nemôže zadať do bunky hodnotu, ktorá je v rozpore s vašimi pravidlami overovania údajov, no stále je možné prilepiť bunku, ktorá je obmedzená overením údajov. Používatelia tak môžu do vášho modelu neúmyselne (alebo zámerne) zadať údaje, ktoré ste nezamýšľali.

Môžete tiež vytvoriť rozbaľovací zoznam, ktorý je prepojený s existujúcimi bunkami v rámci modelu. Napríklad nižšie nechcete, aby používatelia zahrnuli oblasť, ktorá nie je zahrnutá v zozname zobrazenom v stĺpci F. Môžete teda použiť zoznam na overenie údajov, ale namiesto zadávania hodnôt (čo by bolo veľmi časovo náročné – náročné), môžete sa pripojiť k rozsahu, ktorý už obsahuje regióny — $F$2:$F$5 — čo je oveľa rýchlejší spôsob vloženia rozbaľovacieho zoznamu.

Použitie overenia údajov na vytvorenie prepojeného dynamického rozbaľovacieho zoznamu.

Keďže ste rozbaľovací zoznam prepojili, tento rozbaľovací zoznam je teraz dynamický. Ak niekto upraví niektorú z buniek v rozsahu F2:F5, možnosti v rozbaľovacom zozname sa automaticky zmenia.

Ochrana a uzamknutie buniek vo vašom finančnom modeli

Do svojho modelu môžete pridať aj ochranu tak, že prejdete na kartu Revízia na páse s nástrojmi a kliknete na tlačidlo Chrániť hárok v časti Zmeny. Ak chcete, zadajte heslo a kliknite na tlačidlo OK. To ochráni každú jednu bunku v celom hárku, takže nikto nebude môcť robiť žiadne zmeny

Ak chcete, aby používatelia mohli upravovať určité bunky, budete musieť vypnúť ochranu, zvýrazniť tieto bunky (a iba tie bunky, ktoré chcete zmeniť), prejsť na kartu Domov na páse s nástrojmi a kliknúť na tlačidlo Formát v sekcii Bunky. Zrušte výber možnosti Zamknúť bunku, ktorá sa zobrazí v rozbaľovacom zozname. Znova zapnite ochranu a odomknú sa iba vybrané bunky.

Majte na pamäti, že je pomerne jednoduché prelomiť heslo do Excelu (vyhľadajte na internete Excel Password Cracker), takže ak sa niekto chce dostať dovnútra a urobiť zmeny vo vašom chránenom modeli, môže. Odporúčam, aby ste heslá do Excelu považovali za odstrašujúci prostriedok, nie za definitívne bezpečnostné riešenie.