Защита от ошибочного ввода данных

Защита от ошибочного ввода данных ExcelЗащита от ошибочного ввода данных в Excel.

Когда я создавал формы для ввода данных другими сотрудниками, они неизбежно совершали там массу ошибок, искажающих конечный результат.

То букву вместо цифры занесут, то пробелов понаставят.

А дело было очень ответственное — все-таки таблицы расчета заработных плат сотрудников. Сами понимаете — малейшая ошибка, и демотивированный сотрудник нам обеспечен. Excel позволяет максимально снизить вероятность подобных ошибок. И, поверьте моему печальному опыту, этим лучше не пренебрегать.

Рассмотрим образец защиты от ошибочного ввода данных на примере формы табеля рабочего времени. Нам необходимо добиться, чтобы отработанное время указывалось в специально предназначенных для этого строках (желтые ячейки). А код рабочего времени необходимо заносить в другие строки (зеленые ячейки).

Для начала, необходимо ограничить ввод числовых значений. Другими словами — сделать так, чтобы в ячейки для чисел невозможно было ввести буквы или слова. Кроме того, не помешает, если мы запретим вносить числа больше 24. Если руководитель напишет число 41, то, очевидно, что это опечатка.

Выделим все ячейки, в которые необходимо разрешить ввод только чисел от 0 до 24. В линейке меню выберите закладку «Данные» и в блоке «Работа с данными найдите пункт «Проверка данных«. При нажатии на этот пункт появится окошко для настройки ограничения на ввод.

В поле «Тип данных» выбираем пункт «Действительное«, если допускается внесение дробных значений, или «Целое число«, если дробные значения не допускаются.В поле «Значение» выбираем «Между«. В поле «Минимум» вносим «0«, в поле «Максимум» вносим «24«.

При желании можно настроить появление сообщения при наведении табличного курсора на ячейку. Для этого перейдите в закладку «Сообщение для ввода«, поставьте галочку в пункте «Отображать подсказку, если ячейка является текущей» и внесите заголовок и текст сообщения. Похожим образом можно настроить сообщение, появляющееся в случае, если введено неверное значение.

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

Теперь идем на страницу табеля, выделяем интересующие нас ячейки, и снова выбираем пункт «Проверка данных«. В поле «Тип данных» выбираем пункт «Список«. В поле «Источник» вносим после знака «=» имя нашего списка.

Если вы отмените галочку в пункте «Игнорировать пустые ячейки«, то программа не даст оставлять ячейки пустыми. Этого нам не надо. А вот в пункте «Список допустимых значений» нам галочка ни к чему. Если мы ее оставим, то в ячейке появиться кнопка выпадающего списка, из которого необходимо будет выбирать необходимое значение. Поскольку список у нас очень большой, то выбор из списка будет неудобным. Кроме того наши ячейки очень узкие, и кнопка выпадающего меню будет только мешаться (см. картинку справа).

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

Так настраиваем всю таблицу.

В ячейках, в которых внесены формулы, целесообразно полностью запретить какие бы то ни было изменения. Для выборочного запрета изменений в ячейках мы используем команду «Защитить лист» в группе «Изменения» закладки «Рецензирование«.

Но предварительно необходимо отметить ячейки, изменения в которых мы хотим разрешить. Выделяем все эти ячейки, и нажимаем правую кнопку мыши. Откроется контекстное меню. Выбираем пункт «Формат ячеек«. Открываем закладку «Защита«.

По умолчанию, во всех ячейках стоит галочка напротив пункта «Защищаемая ячейка«. Эту галочку мы отжимаем, разрешив, таким образом, изменения в выделенных ячейках. Нажимаем «ОК«. Теперь можно заняться защитой листа.

После выбора команды «Защитить лист» открывается окошко, позволяющее выбрать параметры защиты:

Назначить пароль я рекомендую, а прочие пункты можно оставить в том виде, в котором они заданы. Нажимаем «ОК«, и проверяем. Попробуйте внести какую-нибудь запись вне того блока, который мы выделяли. Должно появиться окошко, как на рисунке. Если появилось, значит все проделано верно.

Сводные таблицы в Excel

Сводные таблицы в ExcelСводные таблицы Excel.

Чтобы анализировать большое количество данных, простое подведение итогов в нижней строке таблицы недостаточно.

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

Допустим, у нас есть массив, включающий список сотрудников по подразделениям с указанием выплат по заработной плате (все данные вымышлены). Понятно, что отчет по этой таблице должен быть представлен в различных срезах.

К примеру:

  • количество сотрудников компании, получающих зарплату;
  • суммы, затрачиваемые на окладную часть;
  • суммы, затрачиваемые на премиальную часть
  • общие выплаты по подразделениям;
  • количество сотрудников в разных подразделениях;
  • другое.

Одним словом, если все это программировать формулами, то придется повозиться. Парой-тройкой кликов здесь не обойдешься. Вот тут и самое время обратиться к уникальному инструменту Excel, под названием «Сводные таблицы«.

Вставка сводной таблицы

Установите табличный курсор в любом поле нашего массива, и выберите в закладке меню «Вставка» пункт «Сводная таблица«. Обратите внимание — программа сама определила диапазон, который у нас будет обрабатываться (его границы отмечены бегущим пунктиром). Однако, диапазон будет определен неверно, если какая-то строка таблицы или какой-то столбец будут полностью пустыми. В этом случае будет выделен диапазон до пустой строки или столбца.

Кроме того, если к массиву где-то примыкает какая-нибудь ячейка с введенным значением или текстом, то граница выделенного диапазона будет смещена на эту ячейку. Поэтому, если граница выделенного диапазона расположена не там, где ожидается, проверьте границы всего массива на наличие лишних заполненных ячеек.

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

Настройка полей сводной таблицы

Справа вы видите список полей сводной таблицы, и области, куда можно перетаскивать эти поля. Впрочем, перетаскивать их можно и на шаблон.

Например, нам необходимо определить, какие суммы затрачиваются на выплату окладной части заработной платы по управлениям. Управления будем размещать в строках. Поэтому захватываем мышкой поле «Управление» и переносим его к области «Названия строк«. Теперь, поскольку нам необходимо вычислить затраты по окладной части, захватываем поле «Оклад«, и переносим его к области «Значения«. Вот что у нас получилось:

Но это еще не все. К примеру, нам нужно вывести еще и данные по отделам, но чтобы группировка по управлениям не терялась. Наименования отделов можно представить в колонках. для этого поле «Отдел» переносим к области «Названия столбцов«. Но это будет не совсем удобно (см.рис).

Правильнее будет, если отделы мы разместим в строках и сгруппируем по управлениям. Захватываем поле «Отдел» и переносим на поле «Названия строк» ниже поля «Управление«. Согласитесь, в таком виде таблица становится намного понятнее.

А вот в названия столбцов логичнее было бы разместить месяцы. У нас таблица с данными по одному месяцу, но если бы их было несколько, то мы смогли бы проследить динамику изменений по выплатам окладной части.

Можно разместить колонки «Оклад» и «Премия» рядом. Если, при добавлении этих полей, они размещаются в одну колонку, посмотрите на область «Названия строк«. У вас там должен появиться пункт «∑ значения«. Переместите его к области «Названия столбцов»

Настройка фильтра сводной таблицы

Предположим, нас не интересуют никакие иные управления, кроме управления маркетинга. Чтобы отфильтровать значения по какому либо из значений поля «Управление«, перенесем это поле к области «Фильтр отчета«. Это поле появится в верхней части сводной таблицы. Нажимаем на кнопку выбора из списка (серый квадратик с черным треугольничком), и выбираем пункт «Управление маркетинга«.

Кстати, если есть желание отфильтровать по нескольким подразделениям, то для этого есть специальная галочка в нижней части диалогового окна. А в этот раз жмем «ОК«, и получаем значения, отфильтрованные по критерию «Управление маркетинга».

Вставка формул в сводную таблицу

Теперь было бы неплохо посчитать сумму выплат оклада и премии. Это можно сделать с помощью формулы в сводной таблице:

  • устанавливаем табличный курсор в любом поле сводной таблицы;
  • жмем вкладку меню «Параметры«;
  • выбираем пункт «формулы«-«вычисляемое поле«.

В открывшемся окне заносим имя нашего поля (я назвал «Общая ЗП»), а в пункте «Формула» заносим операции с интересующими нас полями: =Оклад + Премия. Жмем «ОК». В области значений появляется требуемый результат.

Изменение параметра полей значений и оформление

Допустим, нам необходимо посчитать количество людей, получающих зарплату по подразделениям. В этом случае нужно вернуть в поле значений поле с окладом (поскольку оклад получают все сотрудники, а, значит, пустых записей в таблице быть не должно).

Устанавливаем табличный курсор в поле значений, и выбираем во вкладке меню «Параметры» пункт «Параметры поля«. В открывшемся окне находим пункт «Количество» и выбираем его. При желании можно сменить наименование этого поля (у меня — «Количество сотрудников«).

Настройки представления таблицы можно осуществить во вкладке меню «Конструктор«. Я, например, выбрал «Макет отчета» — «Показать в сжатой форме» и поставил галочку в пункте «Чередующиеся строки«. Вот такая таблица у меня получилась:

и вот такое у нее размещение полей по областям.

Условное форматирование

Условное форматирование ExcelУсловное форматирование в Excel.

Условное форматирование позволяет выделять ячейки таблицы цветом или какими-либо иными способами для облегчения восприятия данных.

Это очень удобно, когда список достаточно большой, и требуется выделить записи, чтобы они бросались в глаза.

Допустим у вас есть список сотрудников (на рисунке он вымышленный), и вам необходимо в этом списке выделить тех, кто моложе 25 лет (менеджерам по персоналу часто приходится сортировать сотрудников по разным признакам). Это можно сделать очень просто, использовав условное форматирование:

Выделяем все ячейки с возрастом, и на вкладке «Главная» жмем на кнопку «Условное форматирование». В выпавшем списке подводим курсор к надписи «Правила выделения ячеек» и в появившемся меню справа кликаем мышкой на строке  «Меньше».

В открывшемся окне выбираем значение, которое будут выступать условием (в нашем случае — 25), и цвета заливки и шрифта соответствующих условию значений. Смотрим результат, и меняем, по необходимости. Можно выбрать «Пользовательский формат», и настроить цвета по своему усмотрению.

Например, мне нужно сделать желтую заливку, а шрифт выделить жирным и красным. На закладке «Шрифт» выбираю «Полужирный», а на закладке «Заливка» на палитре выбираю желтый цвет. Вот что получилось.

Помимо выделения цветом, для повышения наглядности, можно использовать различные значки и стрелки. Рассмотрим эту возможность на другом примере.

Предположим, отдел маркетинга представил нам срез по ценам торгуемой нами продукции в других магазинах. Нам нужно посмотреть, кто торгует ниже, а кто выше, чем мы. Ряд цифр просматривать затруднительно. Было бы нагляднее, если бы возле каждой цены стрелочками указывалось — выше или ниже нашей цена у того или иного конкурента.

Выделяем колонку с ценами, и выбираем в меню условного форматирования пункт «Наборы значков». В появившемся списке выбираем то, что нам больше нравиться. В нашем случае это будут цветные стрелки вверх и вниз. Однако, если мы просто выберем стрелки, то они будут указывать отклонения вверх или вниз от среднего арифметического в этом ряду. А нам нужно, чтобы отклонения считались от нашей цены. Значит делаем следующее:

Для более продвинутого форматирования ячеек используется ввод условий вручную. Для этого в меню условного форматирования имеется пункт «Создать правило». Откроется окно со следующим списком:

Тем, кто предпочитает видеоинструкции, предлагаю неплохой вариант.