Як обмежити та підтвердити дані у вашій фінансовій моделі 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 як засіб стримування, а не остаточне рішення безпеки.