Hur man begränsar och validerar data i din Excel-ekonomimodell

När du har byggt klart en finansiell modell kan du bli frestad att hålla den för dig själv, eftersom du inte vill att någon ska förstöra dina formler eller använda modellen på ett olämpligt sätt. Modeller bör samarbeta, men du måste bygga din modell på ett sådant sätt att det är lätt för andra att använda och svårt att förstöra. Ett bra sätt att göra din modell robust för andra att använda är att tillämpa datavalideringar och skydd på modellen. På så sätt kan användaren bara ange den information han ska.

Begränsa inmatning av användardata i din ekonomiska modell

För ett praktiskt exempel på hur man använder datavalidering, låt oss ta denna projektkostnadsanalys.

Beräknar daglig personalfrekvens med hjälp av absoluta referenser.

Din kollega använder modellen du har byggt och han kan se på vilket sätt cell D3 har formaterats (med skuggning) att du förväntade dig att folk skulle göra ändringar i den. Han är inte längre säker på hur många dagar det här projektet kommer att fortsätta, så han skriver in TBA i cell D3 istället. Så fort han skriver TBA, förstör det verkligen saker! Som du kan se nedan förväntade formlerna du redan har byggt ett nummer i cell D3, inte text.

Text i en ingång som orsakar fel.

Istället för att tillåta användaren att lägga in vad som helst i valfri cell, kan du ändra egenskaperna för denna cell så att endast siffror kan anges. Du kan också ändra det så att endast heltal eller tal tillåts i ett givet intervall.

Följ dessa steg:

Ladda ner fil 0601.xlsx och välj fliken märkt 6-17.

Välj cell D3.

Gå till fliken Data på menyfliksområdet och tryck på ikonen Datavalidering i avsnittet Dataverktyg.

Dialogrutan Datavalidering visas.

På fliken Inställningar, i rullgardinsmenyn Tillåt, välj Hela numret; i rullgardinsmenyn Data, välj Större än; och i fältet Minimum anger du 0.

Tillåt nu att endast heltal större än noll kan anges i cell D3. Försök att skriva in text som TBA. Försök att ange ett negativt värde. Excel tillåter inte det och ett felmeddelande visas.

Om du vill kan du ange ett varningsmeddelande på fliken Indatameddelande i dialogrutan Datavalidering. Du kanske till exempel vill att följande meddelande ska visas: "Varning! Ange bara numeriska värden." På fliken Error Alert kan du skriva ett annat meddelande som visas om någon ignorerar varningen och försöker skriva in ogiltig text.

Skapa rullgardinsrutor med datavalideringar i din ekonomiska modell

Datavalideringsverktyget hindrar inte bara användare från att ange felaktiga data i din modell, utan du kan också använda det för att skapa rullgardinsrutor. I dialogrutan Datavalidering väljer du Lista i listrutan Tillåt. I fältet Källa anger du de värden du vill ska visas i listan med ett kommatecken mellan dem, till exempel Ja, Nej. En enkel rullgardinslista skapas i cell B12 med bara två alternativ: Ja och Nej. Användaren kan inte ange något annat.

Använda datavalidering för att skapa en enkel rullgardinslista.

Ingen kan ange ett värde i en cell som strider mot dina datavalideringsregler, men det är fortfarande möjligt att klistra över en cell som är begränsad av datavalidering. På så sätt kan användare oavsiktligt (eller avsiktligt) lägga in data i din modell som du inte hade för avsikt.

Du kan också skapa en rullgardinslista som länkar till befintliga celler i modellen. Till exempel, nedan, vill du inte att användarna ska inkludera en region som inte ingår i listan som visas i kolumn F. Så du kan använda en datavalideringslista, utan istället för att skriva in värdena (vilket skulle vara mycket tid -konsumerande), kan du länka till intervallet som redan innehåller regionerna — $F$2:$F$5 — vilket är ett mycket snabbare sätt att infoga en rullgardinslista.

Använda datavalidering för att skapa en länkad, dynamisk rullgardinslista.

Eftersom du har länkat rullgardinsmenyn är den här rullgardinsmenyn nu dynamisk. Om någon redigerar någon av cellerna i området F2:F5 kommer alternativen i rullgardinsmenyn att ändras automatiskt.

Skydda och låsa celler i din ekonomiska modell

Du kan också lägga till skydd till din modell genom att gå till fliken Granska på menyfliksområdet och klicka på knappen Skydda ark i avsnittet Ändringar. Ange ett lösenord om du vill ha ett och klicka på OK. Detta kommer att skydda varje enskild cell i hela kalkylbladet, så ingen kommer att kunna göra några ändringar alls

Om du vill att användare ska kunna redigera vissa celler måste du stänga av skyddet, markera dessa celler (och bara de celler du vill ändra), gå till fliken Hem på menyfliksområdet och klicka på knappen Formatera i avsnittet Celler. Avmarkera alternativet Lås cell som visas i rullgardinsmenyn. Slå på skyddet igen, och endast de celler som har valts kommer att låsas upp.

Tänk på att det är ganska lätt att knäcka ett Excel-lösenord (sök på Internet efter Excel-lösenordsknäckare), så om någon vill komma in och göra ändringar i din skyddade modell så kan han det. Jag rekommenderar att du behandlar Excel-lösenord som ett avskräckande, inte en definitiv säkerhetslösning.