sborplanov4а

Сбор планов от подразделений: обеспечение корректности ввода и защита таблицы

Зачем это нужно

При формировании планового бюджета у финансиста возникает задача сбора данных из подразделений. При этом могут возникнуть ошибки.

  1. Данные могут прийти не в нужном виде, придётся тратить время на их дополнительную обработку. Для этого нужно предусмотреть шаблон сбора данных (в формате Excel) и проверку ввода данных.
  2. Шаблон сбора данных может быть случайно (или намеренно) испорчен. Для исключения такой ошибки нужно защищать структуру шаблона.
  3. Данные могут не отвечать поставленной задаче планирования, например, план продаж не совпадает со стратегическими целями или не учитывает производственные возможности.

Продемонстрируем, как Excel может помочь в сборе данных.

Рассмотрим практическую задачу. Финансист запрашивает годовой план продаж у коммерческого отдела в формате «месяц-продукт-объём продаж». При этом собственниками задана цель по выручке, которой план продаж должен соответствовать. Кроме того, есть ограничение максимального объёма производства в месяц. Для простоты считаем, что производство и продажа товара происходит в одном отчётном месяце (т.е. не учитываем запасы готовой продукции, в общем это несложно сделать в модели Excel). Также для упрощения ограничение максимального объёма производства задано в рублях.

Скачайте файл с примером sbor-planov-nachalo. В нём шаблон сбора данных, обладающий всеми вышеуказанными недостатками.

Проверка ввода данных

Коммерческий отдел должен выдать данные о продажах, заполнив соответствующими числами диапазон С4:Е15. Для корректного заполнения можно ограничить ввод данных в этот диапазон. Выделите его. В меню Данные нажмите кнопку Проверка данных. В открывшемся окне выберите Тип данных – «Целое число», Значение – «больше», Минимум – 0, ОК.

sborplanov1

Теперь Excel не даст ввести в ячейку значение, отличное от заданного условия.

Проверка корректности вводимых значений

Теперь облегчим работу сотрудникам, заполняющим таблицу. Excel даёт возможность «на лету» проверять нужные условия. Рассмотрим разные способы.

Количественная проверка

Проверим, сколько раз в таблице нарушается условие по объёму производства. Для этого в ячейке Е20 введена формула «=СЧЁТЕСЛ?(C4:C15;»>»&I6)+СЧЁТЕСЛ?(D4:D15;»>»&I7) +СЧЁТЕСЛ?(E4:E15;»>»&I8)»

Качественная проверка

В ячейках Е21 и Е22 проверяется выполнение заданных условий. Соответственно в Е21 введена простая логическая формула «=ЕСЛ?(E20=0;»да»;»нет»)», в Е22 «=ЕСЛ?(E17>=I3;»да»;»нет»)»

Визуальная проверка

При заполнении больших таблиц удобно сразу выделять неверные значения. Это можно сделать с помощью условного форматирования. Выделите диапазон С4:С15, в меню Главная нажмите кнопку Условное форматирование, Правила выделения ячеек, Больше…:

sborplanov2

В открывшемся окне в левом поле выберите ячейку I6, справа выберите например «Красный текст».

sborplanov3

Подробнее о форматировании ячеек в учебнике Excel онлайнГлава 2. Работа с ячейками: ввод, форматирование

Теперь все ячейки с неверно введёнными данными подсвечиваются красным. Такой метод хорошо подходит для контроля ячеек, в которых данные не вводятся, а вычисляются автоматически.

sborplanov3

Автоматическая проверка данных

Можно задать диапазон вводимых данных с помощью уже рассмотренного инструмента Проверка данных. Выделите нужный столбец данных. В меню Данные нажмите кнопку Проверка данных. В открывшемся окне выберите Тип данных – «Целое число», Значение – «между», Минимум – 0, Максимум – нажмите на кнопку справа поля и выберите нужную ячейку с максимальным объёмом производства. Такой метод не всегда удобен, если значение всё же допустимо ввести (для расчёта различных сценариев плана).

Защита шаблона

Осталось защитить структуру шаблона от изменения, оставив возможность редактирования только заданного диапазона.

Выделите диапазон С4:Е16, вызовите меню Формат ячеек любым способом (например, через контекстное меню). В открывшемся окне на вкладке Защита снимите галочку Защищаемая ячейка.

sborplanov5

Теперь в меню Рецензирование нажмите кнопку Защитить лист. Все галочки стоит оставить как есть, нужно задать пароль для отключения защиты.

sborplanov6

Подробнее о защите книги в учебнике Excel онлайнГлава 4. Защита данных в Excel от нежелательных изменений

Теперь при попытке изменить что-то на этом листе Excel выдаст предупреждение о том, что данные защищены от изменений. Шаблон готов для сбора данных. Готовый файл можно скачать тут: sbor-planov-gotov. Здесь не задан пароль и можно легко отключить защиту с помощью кнопки Снять защиту листа меню Рецензирование.

Смотрите также статьи:

Порядок составления бюджета компании (БДР)

Простой план продаж в Excel на основе данных прошлых периодов

Планирование продаж в Excel с учётом сезонности

Leave a Reply

Ваш e-mail не будет опубликован.