Hvordan begrense og validere data i Excel-finansmodellen

Etter at du er ferdig med å bygge en finansiell modell, kan du bli fristet til å holde den for deg selv, fordi du ikke vil at noen skal rote til formlene dine eller bruke modellen upassende. Modeller bør samarbeide, men du må bygge modellen din på en slik måte at den er lett for andre å bruke og vanskelig å rote til. En flott måte å gjøre modellen robust for andre å bruke, er å bruke datavalideringer og beskyttelser på modellen. På denne måten kan brukeren bare legge inn dataene han skal.

Begrense registrering av brukerdata i din økonomiske modell

For et praktisk eksempel på hvordan du bruker datavalidering, la oss ta denne prosjektkostnadsanalysen.

Beregning av daglig personalsats ved hjelp av absolutt referanse.

Din kollega bruker modellen du har bygget, og han kan se på måten celle D3 har blitt formatert på (med skyggelegging) at du forventet at folk skulle gjøre endringer i den. Han er ikke lenger sikker på hvor mange dager dette prosjektet kommer til å fortsette, så han skriver TBA inn i celle D3 i stedet. Så snart han skriver TBA, roter det virkelig til ting! Som du kan se nedenfor, forventet formlene du allerede har laget et tall i celle D3, ikke tekst.

Tekst i en inngang som forårsaker feil.

I stedet for å la brukeren legge inn hva som helst i en hvilken som helst celle, kan du endre egenskapene til denne cellen slik at bare tall kan legges inn. Du kan også endre den til å tillate bare hele tall eller tall i et gitt område.

Følg disse trinnene:

Last ned fil 0601.xlsx og velg fanen merket 6-17.

Velg celle D3.

Gå til Data-fanen på båndet og trykk på Datavalidering-ikonet i Dataverktøy-delen.

Dialogboksen Datavalidering vises.

På fanen Innstillinger, i rullegardinlisten Tillat, velg Helt tall; i rullegardinlisten Data velger du Greater Than; og i Minimum-feltet skriver du inn 0.

Tillat nå at hele tall større enn null kan legges inn i celle D3. Prøv å skrive inn tekst som TBA. Prøv å angi en negativ verdi. Excel vil ikke tillate det, og et feilvarsel vises.

Hvis du vil, kan du skrive inn en advarsel i kategorien Inndatamelding i dialogboksen Datavalidering. For eksempel vil du kanskje at følgende melding skal vises: "Advarsel! Angi bare numeriske verdier.» I kategorien Feilvarsel kan du skrive inn en annen melding som vises hvis noen ignorerer advarselen og prøver å skrive inn ugyldig tekst.

Lage rullegardinbokser med datavalideringer i din økonomiske modell

Ikke bare hindrer datavalideringsverktøyet brukere fra å legge inn feil data i modellen din, men du kan også bruke det til å lage rullegardinbokser. I dialogboksen Datavalidering velger du Liste fra rullegardinlisten Tillat. I Kilde-feltet skriver du inn verdiene du ønsker skal vises i listen med et komma mellom dem, for eksempel Ja, Nei. En enkel rullegardinliste opprettes i celle B12 med bare to alternativer: Ja og Nei. Brukeren kan ikke legge inn noe annet.

Bruke datavalidering for å lage en enkel rullegardinliste.

Ingen kan angi en verdi i en celle som er i strid med datavalideringsreglene dine, men det er fortsatt mulig å lime inn over en celle som er begrenset av datavalidering. På denne måten kan brukere utilsiktet (eller bevisst) legge inn data i modellen din som du ikke hadde til hensikt.

Du kan også lage en rullegardinliste som lenker til eksisterende celler i modellen. For eksempel, nedenfor, vil du ikke at brukerne skal inkludere en region som ikke er inkludert i listen vist i kolonne F. Så du kan bruke en datavalideringsliste, men i stedet for å skrive inn verdiene (noe som ville være veldig tidkrevende) -consuming), kan du koble til området som allerede inneholder regionene - $F$2:$F$5 - som er en mye raskere måte å sette inn en rullegardinliste.

Bruk av datavalidering for å lage en koblet, dynamisk rullegardinliste.

Fordi du har koblet til rullegardinlisten, er denne rullegardinlisten nå dynamisk. Hvis noen redigerer noen av cellene i området F2:F5, vil alternativene i rullegardinlisten automatisk endres.

Beskytte og låse celler i din økonomiske modell

Du kan også legge til beskyttelse til modellen din ved å gå til Review-fanen på båndet og klikke på Beskytt ark-knappen i Endringer-delen. Skriv inn et passord hvis du vil ha det, og klikk OK. Dette vil beskytte hver eneste celle i hele regnearket, slik at ingen vil kunne gjøre noen endringer i det hele tatt

Hvis du vil at brukere skal kunne redigere bestemte celler, må du slå av beskyttelsen, markere disse cellene (og bare de cellene du vil endre), gå til Hjem-fanen på båndet og klikke på Format-knappen i Celler-delen. Fjern merket for alternativet Lås celle som vises i rullegardinlisten. Slå beskyttelsen på igjen, og bare cellene som er valgt vil bli låst opp.

Husk at det er rimelig enkelt å knekke et Excel-passord (søk på Internett etter Excel-passordknekker), så hvis noen ønsker å komme inn og gjøre endringer i den beskyttede modellen din, kan han det. Jeg anbefaler at du behandler Excel-passord som et avskrekkende middel, ikke en definitiv sikkerhetsløsning.