Kako ograničiti i potvrditi podatke u vašem Excel financijskom modelu

Nakon što završite s izradom financijskog modela, možda ćete biti u iskušenju da ga zadržite za sebe, jer ne želite da itko zabrlja vaše formule ili koristi model na neprikladan način. Modeli bi trebali biti suradnički, ali morate izgraditi svoj model na takav način da ga drugi mogu koristiti i teško zabrljati. Jedan sjajan način da vaš model učinite robusnim za korištenje drugima je primjena provjere valjanosti podataka i zaštite na model. Na taj način korisnik može unijeti samo podatke koje je trebao.

Ograničavanje unosa korisničkih podataka u vaš financijski model

Za praktičan primjer kako koristiti provjeru valjanosti podataka, uzmimo ovu analizu troškova projekta.

Izračunavanje dnevne stope osoblja korištenjem apsolutnog referenciranja.

Vaš kolega koristi model koji ste izradili i on može reći prema načinu na koji je ćelija D3 formatirana (sa zasjenjenjem) da ste očekivali da će ljudi napraviti promjene u njoj. Više nije siguran koliko će se dana ovaj projekt nastaviti, pa umjesto toga upisuje TBA u ćeliju D3. Čim upiše TBA, to stvarno zabrlja stvari! Kao što možete vidjeti u nastavku, formule koje ste već izgradili očekivale su broj u ćeliji D3, a ne tekst.

Tekst u unosu uzrokuje pogreške.

Umjesto da dopustite korisniku da stavi bilo što u bilo koju ćeliju, možete promijeniti svojstva ove ćelije kako biste dopustili unos samo brojeva. Također ga možete promijeniti tako da dopušta samo cijele brojeve ili brojeve u danom rasponu.

Prati ove korake:

Preuzmite datoteku 0601.xlsx i odaberite karticu s oznakom 6-17.

Odaberite ćeliju D3.

Idite na karticu Podaci na vrpci i pritisnite ikonu Provjera valjanosti podataka u odjeljku Alati za podatke.

Pojavljuje se dijaloški okvir Provjera valjanosti podataka.

Na kartici Postavke na padajućem popisu Dopusti odaberite Cijeli broj; na padajućem popisu Podaci odaberite Veće od; a u polje Minimum unesite 0.

Sada dopustite da se u ćeliju D3 mogu unijeti samo cijeli brojevi veći od nule. Pokušajte unijeti tekst kao što je TBA. Pokušajte unijeti negativnu vrijednost. Excel to neće dopustiti i pojavit će se upozorenje o pogrešci.

Ako želite, možete unijeti poruku upozorenja na kartici Input Message dijaloškog okvira Data Validation. Na primjer, možda želite da se pojavi sljedeća poruka: „Upozorenje! Unesite samo numeričke vrijednosti.” Na kartici Upozorenje o pogrešci možete unijeti drugu poruku koja se pojavljuje ako netko zanemari upozorenje i pokuša unijeti nevažeći tekst.

Izrada padajućih okvira s provjerama valjanosti podataka u vašem financijskom modelu

Alat za provjeru valjanosti podataka ne samo da sprječava korisnike da unose netočne podatke u vaš model, već ga možete koristiti i za stvaranje padajućih okvira. U dijaloškom okviru Provjera valjanosti podataka s padajućeg popisa Dopusti odaberite Popis. U polje Izvor unesite vrijednosti koje želite da se pojavljuju na popisu sa zarezom između njih, poput Da, Ne. U ćeliji B12 kreira se jednostavan padajući popis sa samo dvije opcije: Da i Ne. Korisnik ne mogu unijeti ništa drugo.

Korištenje provjere valjanosti podataka za izradu jednostavnog padajućeg popisa.

Nitko ne može unijeti vrijednost u ćeliju koja je protivna vašim pravilima za provjeru valjanosti podataka, ali je još uvijek moguće zalijepiti ćeliju koja je ograničena provjerom valjanosti podataka. Na taj način korisnici mogu nehotice (ili namjerno) unijeti podatke u vaš model koje niste namjeravali.

Također možete stvoriti padajući popis koji povezuje postojeće ćelije unutar modela. Na primjer, u nastavku, ne želite da korisnici uključe regiju koja nije uključena u popis prikazan u stupcu F. Dakle, možete koristiti popis za provjeru valjanosti podataka, ali umjesto upisivanja vrijednosti (što bi bilo vrlo vrijeme -consuming), možete povezati s rasponom koji već sadrži regije — $F$2:$F$5 — što je mnogo brži način umetanja padajućeg popisa.

Korištenje provjere valjanosti podataka za izradu povezanog, dinamičnog padajućeg popisa.

Budući da ste povezali padajući popis, ovaj padajući izbornik je sada dinamičan. Ako netko uredi bilo koju od ćelija u rasponu F2:F5, opcije na padajućem popisu automatski će se promijeniti.

Zaštita i zaključavanje ćelija u vašem financijskom modelu

Također možete dodati zaštitu svom modelu tako da odete na karticu Pregled na vrpci i kliknete gumb Zaštiti list u odjeljku Promjene. Unesite lozinku ako je želite i kliknite U redu. To će zaštititi svaku ćeliju u cijelom radnom listu, tako da nitko neće moći napraviti nikakve promjene

Ako želite da korisnici mogu uređivati ​​određene ćelije, morat ćete isključiti zaštitu, označiti te ćelije (i samo one ćelije koje želite promijeniti), idite na karticu Početna na vrpci i kliknite gumb Format u odjeljku Ćelije. Poništite odabir opcije Zaključaj ćeliju koja se pojavljuje na padajućem popisu. Ponovno uključite zaštitu i otključat će se samo ćelije koje su odabrane.

Imajte na umu da je relativno lako razbiti Excel lozinku (na Internetu potražite alat za razbijanje lozinki za Excel), pa ako netko želi ući i promijeniti vaš zaštićeni model, može. Preporučam da Excelove lozinke tretirate kao sredstvo odvraćanja, a ne kao definitivno sigurnosno rješenje.