Как да ограничите и потвърдите данните във вашия финансов модел на Excel

След като завършите изграждането на финансов модел, може да се изкушите да го запазите за себе си, защото не искате някой да обърка формулите ви или да използва модела неподходящо. Моделите трябва да са съвместни, но трябва да изградите своя модел по такъв начин, че да е лесен за използване от други и да е трудно да се обърка. Един чудесен начин да направите своя модел здрав за използване от други хора е да приложите валидации и защити на данните към модела. По този начин потребителят може да въведе само данните, които трябва.

Ограничаване на въвеждането на потребителски данни във вашия финансов модел

За практически пример как да използвате валидирането на данни, нека вземем този Анализ на разходите по проекта.

Изчисляване на дневната норма на персонала, като се използва абсолютна справка.

Вашият колега използва модела, който сте изградили, и той може да разбере по начина, по който е форматирана клетка D3 (със засенчване), че сте очаквали хората да направят промени в нея. Той вече не е сигурен колко дни ще продължи този проект, така че вместо това въвежда TBA в клетка D3. Щом напише TBA, това наистина обърква нещата! Както можете да видите по-долу, формулите, които вече сте изградили, очакваха число в клетка D3, а не текст.

Текст във въвеждане, причиняващ грешки.

Вместо да позволявате на потребителя да поставя каквото и да е в клетка, можете да промените свойствата на тази клетка, за да позволите да се въвеждат само числа. Можете също да го промените, за да разрешите само цели числа или числа в даден диапазон.

Следвай тези стъпки:

Изтеглете файла 0601.xlsx и изберете раздела с етикет 6-17.

Изберете клетка D3.

Отидете в раздела Данни на лентата и натиснете иконата за проверка на данни в секцията Инструменти за данни.

Появява се диалоговият прозорец Проверка на данни.

В раздела Настройки в падащия списък Разрешаване изберете Цяло число; в падащия списък Данни изберете По-голямо от; и в полето Минимум въведете 0.

Сега в клетка D3 могат да се въвеждат само цели числа, по-големи от нула. Опитайте да въведете текст като TBA. Опитайте да въведете отрицателна стойност. Excel няма да го позволи и ще се появи предупреждение за грешка.

Ако желаете, можете да въведете предупредително съобщение в раздела Входно съобщение на диалоговия прозорец Проверка на данни. Например, може да искате да се появи следното съобщение: „Предупреждение! Въведете само цифрови стойности." В раздела Предупреждение за грешка можете да въведете друго съобщение, което се появява, ако някой игнорира предупреждението и се опита да въведе невалиден текст.

Създаване на падащи полета с валидиране на данни във вашия финансов модел

Инструментът за валидиране на данни не само спира потребителите да въвеждат неправилни данни във вашия модел, но можете да го използвате и за създаване на падащи полета. В диалоговия прозорец Проверка на данни от падащия списък Разрешаване изберете Списък. В полето Източник въведете стойностите, които искате да се показват в списъка със запетая между тях, като Да, Не. В клетка B12 се създава прост падащ списък само с две опции: Да и Не. Потребителят не може да въвежда нищо друго.

Използване на валидиране на данни за създаване на прост падащ списък.

Никой не може да въведе стойност в клетка, която противоречи на вашите правила за валидиране на данни, но все пак е възможно да поставите върху клетка, която е ограничена от проверка на данни. По този начин потребителите могат по невнимание (или умишлено) да въведат данни във вашия модел, които не сте възнамерявали.

Можете също да създадете падащ списък, който се свързва със съществуващи клетки в модела. Например, по-долу, не искате потребителите да включват регион, който не е включен в списъка, показан в колона F. Така че можете да използвате списък за валидиране на данни, но вместо да въвеждате стойностите (което би било много време -consuming), можете да се свържете с диапазона, който вече съдържа регионите — $F$2:$F$5 — което е много по-бърз начин за вмъкване на падащ списък.

Използване на валидиране на данни за създаване на свързан, динамичен падащ списък.

Тъй като сте свързали падащия списък, това падащо меню вече е динамично. Ако някой редактира някоя от клетките в диапазона F2:F5, опциите в падащия списък ще се променят автоматично.

Защита и заключване на клетки във вашия финансов модел

Можете също да добавите защита към вашия модел, като отидете в раздела Преглед на лентата и щракнете върху бутона Защита на листа в секцията Промени. Въведете парола, ако искате такава, и щракнете върху OK. Това ще защити всяка отделна клетка в целия работен лист, така че никой няма да може да прави никакви промени

Ако искате потребителите да могат да редактират определени клетки, ще трябва да изключите защитата, да маркирате тези клетки (и само тези клетки, които искате да промените), да отидете на раздела Начало на лентата и да щракнете върху бутона Форматиране в секцията Клетки. Премахнете избора на опцията за заключване на клетка, която се показва в падащия списък. Включете отново защитата и само клетките, които са били избрани, ще бъдат отключени.

Имайте предвид, че е сравнително лесно да се разбие парола за Excel (потърсете в Интернет за кракер на пароли за Excel), така че ако някой иска да влезе и да направи промени във вашия защитен модел, той може. Препоръчвам ви да третирате паролите на Excel като възпиращ фактор, а не като окончателно решение за сигурност.