Kako omejiti in potrditi podatke v vašem Excelovem finančnem modelu

Ko končate z gradnjo finančnega modela, vas bo morda zamikalo, da bi ga obdržali zase, saj ne želite, da bi kdo zmotil vaše formule ali model uporabljal neprimerno. Modeli bi morali biti sodelujoči, vendar morate svoj model zgraditi tako, da ga bodo drugi lahko uporabljali in težko zamočili. Eden od odličnih načinov, kako narediti vaš model robustnega za druge, je uporaba preverjanja veljavnosti podatkov in zaščite za model. Tako lahko uporabnik vnese le tiste podatke, ki naj bi jih vnesel.

Omejitev vnosa uporabniških podatkov v vaš finančni model

Za praktičen primer uporabe preverjanja veljavnosti podatkov vzemimo to analizo stroškov projekta.

Izračun dnevne stopnje osebja z uporabo absolutnega referenciranja.

Vaš kolega uporablja model, ki ste ga zgradili, in po načinu, na katerega je bila formatirana celica D3 (s senčenjem), lahko ugotovi, da ste pričakovali, da jo bodo ljudje spremenili. Ni več prepričan, koliko dni se bo ta projekt nadaljeval, zato namesto tega vnese TBA v celico D3. Takoj, ko vpiše TBA, to res zmoti stvari! Kot lahko vidite spodaj, so formule, ki ste jih že zgradili, pričakovali številko v celici D3, ne besedilo.

Besedilo v vnosu, ki povzroča napake.

Namesto da bi uporabniku dovolili, da v katero koli celico vnese karkoli, lahko spremenite lastnosti te celice, da omogočite vnos samo številk. Lahko ga tudi spremenite, da dovolite samo cela števila ali številke v določenem obsegu.

Sledite tem korakom:

Prenesite datoteko 0601.xlsx in izberite zavihek z oznako 6-17.

Izberite celico D3.

Pojdite na zavihek Podatki na traku in pritisnite ikono za preverjanje veljavnosti podatkov v razdelku Podatkovna orodja.

Prikaže se pogovorno okno Preverjanje podatkov.

Na zavihku Nastavitve na spustnem seznamu Dovoli izberite Celotna številka; na spustnem seznamu Podatki izberite Večje od; in v polje Minimum vnesite 0.

Zdaj lahko v celico D3 vnesete samo cela števila, večja od nič. Poskusite vnesti besedilo, kot je TBA. Poskusite vnesti negativno vrednost. Excel tega ne bo dovolil in prikazalo se bo opozorilo o napaki.

Če želite, lahko vnesete opozorilno sporočilo na zavihku Vhodno sporočilo v pogovornem oknu Preverjanje podatkov. Na primer, morda želite, da se prikaže naslednje sporočilo: »Opozorilo! Vnesite samo številčne vrednosti." Na zavihku Opozorilo o napaki lahko vnesete drugo sporočilo, ki se prikaže, če nekdo prezre opozorilo in poskuša vnesti neveljavno besedilo.

Ustvarjanje spustnih polj s potrditvami podatkov v vašem finančnem modelu

Ne samo, da orodje za preverjanje veljavnosti podatkov uporabnikom prepreči vnašanje napačnih podatkov v vaš model, ampak ga lahko uporabite tudi za ustvarjanje spustnih polj. V pogovornem oknu Preverjanje veljavnosti podatkov na spustnem seznamu Dovoli izberite Seznam. V polje Vir vnesite vrednosti, ki jih želite prikazati na seznamu z vejico med njimi, na primer Da, Ne. V celici B12 se ustvari preprost spustni seznam z le dvema možnostma: Da in Ne. Uporabnik ne more vnesti ničesar drugega.

Uporaba preverjanja veljavnosti podatkov za ustvarjanje preprostega spustnega seznama.

Nihče ne more vnesti vrednosti v celico, ki je v nasprotju z vašimi pravili za preverjanje veljavnosti podatkov, vendar je še vedno mogoče prilepiti celico, ki je omejena s preverjanjem veljavnosti podatkov. Na ta način lahko uporabniki nehote (ali namerno) v vaš model vnesejo podatke, ki jih niste nameravali.

Ustvarite lahko tudi spustni seznam, ki povezuje obstoječe celice znotraj modela. Spodaj na primer ne želite, da uporabniki vključijo regijo, ki ni vključena na seznam, prikazan v stolpcu F. Torej lahko uporabite seznam za preverjanje veljavnosti podatkov, vendar namesto vnašanja vrednosti (kar bi bilo zelo dolgo -consuming), se lahko povežete z obsegom, ki že vsebuje regije — $F$2:$F$5 — kar je veliko hitrejši način za vstavljanje spustnega seznama.

Uporaba preverjanja veljavnosti podatkov za ustvarjanje povezanega dinamičnega spustnega seznama.

Ker ste spustni seznam povezali, je ta spustni meni zdaj dinamičen. Če nekdo uredi katero koli od celic v obsegu F2:F5, se bodo možnosti na spustnem seznamu samodejno spremenile.

Zaščita in zaklepanje celic v vašem finančnem modelu

Svojemu modelu lahko dodate tudi zaščito, tako da odprete zavihek Pregled na traku in kliknete gumb Zaščiti list v razdelku Spremembe. Vnesite geslo, če ga želite, in kliknite V redu. To bo zaščitilo vsako posamezno celico na celotnem delovnem listu, tako da nihče ne bo mogel narediti nobenih sprememb

Če želite, da lahko uporabniki urejajo določene celice, boste morali izklopiti zaščito, označiti te celice (in samo tiste celice, ki jih želite spremeniti), pojdite na zavihek Domov na traku in kliknite gumb Oblika v razdelku Celice. Počistite izbiro možnosti Zakleni celico, ki se prikaže na spustnem seznamu. Ponovno vklopite zaščito in odklenjene bodo samo celice, ki so bile izbrane.

Upoštevajte, da je dovolj enostavno vdreti geslo za Excel (poiščite v internetu Excelov program za razbijanje gesel), tako da, če želi nekdo vstopiti in spremeniti vaš zaščiten model, lahko. Priporočam, da gesla za Excel obravnavate kot odvračilno sredstvo in ne kot dokončno varnostno rešitev.