Jak omezit a ověřit data ve finančním modelu Excelu

Poté, co dokončíte sestavení finančního modelu, můžete být v pokušení nechat si ho pro sebe, protože nechcete, aby někdo pokazil vaše vzorce nebo použil model nevhodně. Modely by měly být společné, ale musíte svůj model postavit tak, aby jej ostatní mohli snadno používat a bylo obtížné jej zkazit. Jedním ze skvělých způsobů, jak učinit váš model robustním pro ostatní, je použít na model ověření dat a ochranu. Tímto způsobem může uživatel zadat pouze data, která má zadat.

Omezení zadávání uživatelských dat ve vašem finančním modelu

Pro praktický příklad, jak používat ověřování dat, si vezměme tuto analýzu nákladů projektu.

Výpočet denní sazby za zaměstnance pomocí absolutního odkazování.

Váš kolega používá model, který jste vytvořili, a podle toho, jak byla zformátována buňka D3 (se stínováním), pozná, že jste očekávali, že v ní lidé provedou změny. Už si není jistý, kolik dní bude tento projekt pokračovat, a tak místo toho zadá TBA do buňky D3. Jakmile napíše TBA, opravdu to zkazí! Jak můžete vidět níže, vzorce, které jste již vytvořili, očekávaly v buňce D3 číslo, nikoli text.

Text ve vstupu způsobující chyby.

Namísto toho, abyste uživateli umožnili vkládat cokoli do libovolné buňky, můžete změnit vlastnosti této buňky tak, aby umožňovala zadávat pouze čísla. Můžete jej také změnit tak, aby povoloval pouze celá čísla nebo čísla v daném rozsahu.

Následuj tyto kroky:

Stáhněte si soubor 0601.xlsx a vyberte kartu označenou 6-17.

Vyberte buňku D3.

Přejděte na kartu Data na pásu karet a stiskněte ikonu Ověření dat v části Datové nástroje.

Zobrazí se dialogové okno Ověření dat.

Na kartě Nastavení v rozevíracím seznamu Povolit vyberte Celé číslo; v rozevíracím seznamu Data vyberte možnost Větší než; a do pole Minimum zadejte 0.

Nyní lze do buňky D3 zadávat pouze celá čísla větší než nula. Zkuste zadat text, například TBA. Zkuste zadat zápornou hodnotu. Excel to nedovolí a zobrazí se upozornění na chybu.

Pokud chcete, můžete zadat varovnou zprávu na kartě Vstupní zpráva v dialogovém okně Ověření dat. Můžete například chtít, aby se zobrazila následující zpráva: „Varování! Zadejte pouze číselné hodnoty." Na kartě Error Alert můžete zadat další zprávu, která se zobrazí, pokud někdo ignoruje varování a pokusí se zadat neplatný text.

Vytváření rozevíracích polí s ověřením dat ve vašem finančním modelu

Nástroj pro ověřování dat nejen zabrání uživatelům v zadávání nesprávných dat do vašeho modelu, ale můžete jej také použít k vytvoření rozevíracích polí. V dialogovém okně Ověření dat z rozevíracího seznamu Povolit vyberte možnost Seznam. Do pole Zdroj zadejte hodnoty, které chcete v seznamu zobrazit, s čárkou mezi nimi, například Ano, Ne. V buňce B12 se vytvoří jednoduchý rozevírací seznam s pouze dvěma možnostmi: Ano a Ne. Uživatel nelze zadat nic jiného.

Použití ověření dat k vytvoření jednoduchého rozevíracího seznamu.

Nikdo nemůže do buňky zadat hodnotu, která je v rozporu s vašimi pravidly ověřování dat, ale stále je možné vložit buňku, která je omezena ověřováním dat. Uživatelé tak mohou do vašeho modelu neúmyslně (nebo záměrně) zadat data, která jste nezamýšleli.

Můžete také vytvořit rozevírací seznam, který odkazuje na existující buňky v modelu. Například níže nechcete, aby uživatelé zahrnuli oblast, která není zahrnuta v seznamu zobrazeném ve sloupci F. Můžete tedy použít seznam pro ověření dat, ale místo zadávání hodnot (což by bylo velmi časově náročné – náročné), můžete odkazovat na rozsah, který již regiony obsahuje — $F$2:$F$5 — což je mnohem rychlejší způsob vložení rozevíracího seznamu.

Použití ověření dat k vytvoření propojeného, ​​dynamického rozevíracího seznamu.

Protože jste propojili rozevírací seznam, je nyní tento rozevírací seznam dynamický. Pokud někdo upraví některou z buněk v rozsahu F2:F5, automaticky se změní možnosti v rozevíracím seznamu.

Ochrana a uzamčení buněk ve vašem finančním modelu

Do svého modelu můžete také přidat ochranu tak, že přejdete na kartu Recenze na pásu karet a kliknete na tlačítko Chránit list v části Změny. Pokud chcete, zadejte heslo a klikněte na OK. Tím bude chráněna každá jednotlivá buňka v celém listu, takže nikdo nebude moci provádět žádné změny

Pokud chcete, aby uživatelé mohli upravovat určité buňky, budete muset vypnout ochranu, zvýraznit tyto buňky (a pouze ty buňky, které chcete změnit), přejít na kartu Domů na pásu karet a kliknout na tlačítko Formát v sekci Buňky. Zrušte výběr možnosti Zamknout buňku, která se zobrazí v rozevíracím seznamu. Znovu zapněte ochranu a odemknou se pouze buňky, které byly vybrány.

Mějte na paměti, že je poměrně snadné prolomit heslo aplikace Excel (vyhledejte na internetu cracker hesel pro Excel), takže pokud se někdo chce dostat dovnitř a provést změny ve vašem chráněném modelu, může. Doporučuji, abyste s hesly Excelu zacházeli jako s odstrašujícím prostředkem, nikoli s definitivním řešením zabezpečení.