Kā ierobežot un pārbaudīt datus savā Excel finanšu modelī

Kad esat pabeidzis izveidot finanšu modeli, jums var rasties kārdinājums paturēt to pie sevis, jo nevēlaties, lai kāds sajauc jūsu formulas vai izmanto modeli neatbilstoši. Modeļiem jābūt sadarbīgiem, taču jums ir jāizveido savs modelis tā, lai to būtu viegli izmantot citiem un grūti sajaukt. Viens lielisks veids, kā padarīt savu modeli izturīgu, lai to varētu izmantot citi, ir piemērot modelim datu validāciju un aizsardzību. Tādā veidā lietotājs var ievadīt tikai tos datus, kas viņam ir jāievada.

Lietotāju datu ievades ierobežošana jūsu finanšu modelī

Lai iegūtu praktisku piemēru, kā izmantot datu validāciju, ņemsim vērā šo projekta izmaksu analīzi.

Dienas personāla likmes aprēķināšana, izmantojot absolūto atsauci.

Jūsu kolēģis izmanto jūsu izveidoto modeli, un viņš var noteikt, kādā šūnā D3 ir formatēta (ar ēnojumu), ka jūs gaidījāt, ka cilvēki tajā veiks izmaiņas. Viņš vairs nav pārliecināts, cik dienas šis projekts turpināsies, tāpēc viņš ieraksta TBA šūnā D3. Tiklīdz viņš ieraksta TBA, tas patiešām sajauc lietas! Kā redzat tālāk, jūsu jau izveidotās formulas šūnā D3 gaidīja skaitli, nevis tekstu.

Ievades teksts rada kļūdas.

Tā vietā, lai ļautu lietotājam ievietot jebko jebkurā šūnā, varat mainīt šīs šūnas rekvizītus, lai atļautu ievadīt tikai skaitļus. Varat arī to mainīt, lai atļautu tikai veselus skaitļus vai skaitļus noteiktā diapazonā.

Veiciet tālāk norādītās darbības.

Lejupielādējiet failu 0601.xlsx un atlasiet cilni ar nosaukumu 6-17.

Atlasiet šūnu D3.

Lentē atveriet cilni Dati un sadaļā Datu rīki nospiediet ikonu Datu validācija.

Tiek parādīts dialoglodziņš Datu validācija.

Cilnes Iestatījumi nolaižamajā sarakstā Atļaut atlasiet Vesels numurs; nolaižamajā sarakstā Dati atlasiet Lielāks nekā; un laukā Minimums ievadiet 0.

Tagad šūnā D3 var ievadīt tikai veselus skaitļus, kas lielāki par nulli. Mēģiniet ievadīt tekstu, piemēram, TBA. Mēģiniet ievadīt negatīvu vērtību. Programma Excel to neatļaus, un tiks parādīts kļūdas brīdinājums.

Ja vēlaties, dialoglodziņa Datu validācija cilnē Ievades ziņojums varat ievadīt brīdinājuma ziņojumu. Piemēram, jūs varētu vēlēties, lai tiktu parādīts šāds ziņojums: “Uzmanību! Ievadiet tikai skaitliskas vērtības. Cilnē Error Alert varat ievadīt citu ziņojumu, kas tiek parādīts, ja kāds ignorē brīdinājumu un mēģina ievadīt nederīgu tekstu.

Nolaižamo lodziņu izveide ar datu validācijām jūsu finanšu modelī

Datu validācijas rīks ne tikai neļauj lietotājiem ievadīt jūsu modelī nepareizus datus, bet arī varat to izmantot, lai izveidotu nolaižamos lodziņus. Dialoglodziņa Datu validācija nolaižamajā sarakstā Atļaut atlasiet Saraksts. Laukā Avots ievadiet vērtības, kuras vēlaties rādīt sarakstā ar komatu starp tām, piemēram, Jā, Nē. Šūnā B12 tiek izveidots vienkāršs nolaižamais saraksts ar tikai divām iespējām: Jā un Nē. neko citu nevar ievadīt.

Datu validācijas izmantošana, lai izveidotu vienkāršu nolaižamo sarakstu.

Neviens nevar ievadīt šūnā vērtību, kas ir pretrunā jūsu datu validācijas noteikumiem, taču joprojām ir iespējams ielīmēt šūnu, kuru ierobežo datu validācija. Tādā veidā lietotāji var netīši (vai apzināti) ievadīt jūsu modelī datus, kurus jūs neplānojāt.

Varat arī izveidot nolaižamo sarakstu, kas veido saites uz esošām šūnām modelī. Piemēram, tālāk jūs nevēlaties, lai lietotāji iekļautu reģionu, kas nav iekļauts F slejā parādītajā sarakstā. Tāpēc varat izmantot datu validācijas sarakstu, bet tā vietā, lai ievadītu vērtības (tas būtu ļoti laiks -patērē), varat izveidot saiti uz diapazonu, kurā jau ir reģioni — $F$2:$F$5 —, kas ir daudz ātrāks veids, kā ievietot nolaižamo sarakstu.

Datu validācijas izmantošana, lai izveidotu saistītu, dinamisku nolaižamo sarakstu.

Tā kā esat saistījis nolaižamo sarakstu, šis nolaižamais saraksts tagad ir dinamisks. Ja kāds rediģē kādu no šūnām diapazonā F2:F5, opcijas nolaižamajā sarakstā tiks automātiski mainītas.

Jūsu finanšu modeļa šūnu aizsardzība un bloķēšana

Varat arī pievienot savam modelim aizsardzību, lentē atverot cilni Pārskatīšana un sadaļā Izmaiņas noklikšķinot uz pogas Aizsargāt lapu. Ievadiet paroli, ja tādu vēlaties, un noklikšķiniet uz Labi. Tas aizsargās katru šūnu visā darblapā, tāpēc neviens nevarēs veikt nekādas izmaiņas

Ja vēlaties, lai lietotāji varētu rediģēt noteiktas šūnas, jums ir jāizslēdz aizsardzība, jāizceļ šīs šūnas (un tikai tās šūnas, kuras vēlaties mainīt), lentē dodieties uz cilni Sākums un noklikšķiniet uz pogas Formatēt. sadaļā Šūnas. Noņemiet atzīmi no opcijas Bloķēt šūnu, kas tiek parādīta nolaižamajā sarakstā. Atkal ieslēdziet aizsardzību, un tiks atbloķētas tikai atlasītās šūnas.

Ņemiet vērā, ka ir diezgan viegli uzlauzt Excel paroli (meklējiet internetā Excel paroles uzlauzēju), tāpēc, ja kāds vēlas iekļūt un veikt izmaiņas jūsu aizsargātajā modelī, viņš to var izdarīt. Es iesaku Excel paroles uzskatīt par preventīvu līdzekli, nevis galīgu drošības risinājumu.