Разоблачение Excel: Проверка вводимых данных - выпадающие списки

Часто ли у вас возникало желание, после получения отчета в Excel (абсолютно на любую тематику), убить его автора? Если возникало и не раз – скорее всего вы наш пациент :)






1. Отчёт, отчёт и еще раз …?

Казалось бы чем можно убить любую здравую идею, подразумевающую некоторую информационную составляющую: ”Давайте соберем статистику использования различных операционок на проектах”, “Надо бы собрать сведения об используемом ПО для оптимизации расходов компании” и т.д.?

Правильно – бездарно собранной информацией!!!

Итак, смотрим, что мы получили в ответ на наш запрос:

Раз



И два



С одной стороны вроде и информация собрана, а с другой…

Бухгалтррия Плюс – это почти Бухгалтерия Плюс, но не совсем.
Антиврус (ПО) – это почти Антивирус (ПО), но не совсем.
Отдел сопровждения ПО – это почти Отдел сопровождения ПО, но не совсем.

И уж совсем ни одно и тоже Windows XP, Win XP, XP, WinXP.
Как впрочем и 1C Бухгалтерия, 1C, 1C Бухгалтерия 7.0.

Поэтому, засучив рукава, идем за большим напильником и начинаем … :)

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


2. Очень гибко хорошо, очень гибко плохо?

На вопрос, что же использовать в качестве хранилища нашей кропотливо, но в тоже время очень срочно, собираемой информации ответ настолько очевиден, что иногда вызывает недоумение: ”Вам может еще и базу данных слепить по-быстрому? Возьмите вот Excel и завтра чтобы отчет был у меня на столе.”

И действиетльно, что может быть лучше, чем использовать всем знакомый, доступный, простой в использовании Excel. Тем более, что это почти база данных – сортировка, фильтрация, автосуммирование и другие ”взрослые” функции присутствуют. Лишних заумных вещей таких как внешние ключи, индексы, контроль уникальности, строгая типизация, словари и т.д. – нет.

Все казалось бы верно, но возвращаясь к собранным отчетам, возникает чувство некоторой неполноценности: ”А можно, чтобы все также просто как в Excel, но еще и чтобы контроль данных был?” (перед глазами заботливо всплыл образ Никулина и его ”перламутровые пуговицы”).
И чтобы тоска от сознания необходимости ковырятся в VB for application, макросах, формулах не потушила робкую надежду, как минимум, дочитать эту статью до конца, а как максимум, реализовать то, что в ней написано, сразу ответим – можно и это не так уж сложно, как может показаться на первый взгляд.

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


3. Готовим прокрустово ложе.

Для начала нам нужно подготовить корректные значения для заполнения наших выпадающих списков правильными величинами. Это можно сделать на отдельной странице нашего Excel файла, назовем её просто и незатейливо – ”Списки (первый вариант)” и скопируем в нее наш список проектов.



Обозначим на этой же странице область для проведения натурных испытаний, например вот таким образом:



И активируем диалог валидации данных ( Main menu>Data>Data Validation ) для выбранной ячейки



Итак давайте внимательно разберемся, что нам предоставляет Excel в качестве инструментов тонкой огранки информации и что будет действительно удобно и полезно нам для реализации задуманного.

Первая страница диалога – Settings (в большинстве случаев её оказывается достаточно) позволяет вам ограничить входные данные согласно заданным правилам. Для настройки необходимого нам типа валидации следует выбрать значение выполнить установки согласно картинке ниже, то есть Allow = List.

Обратите внимание,что на странице появились дополнительные элементы управления:
In-cell dropdown – говорит, что значения предлагаются в виде выпадающего списка;
Source – указывает где брать допустимые значения.



Вторая страница – Input Message пытается ”отпугнуть” неправильные значения еще на ближних подступах, а точнее при активации ячейки. После перехода в ячейку с активированным данным типом валидации появляется всплывающая подсказка с вашим текстом. Но я бы советовал пользоваться данным типом предупреждений очень осторожно потому, что при активной работе такие подсказки больше раздразжают, чем помогают.



Более удобный вариант подсказки предполагает использование вкладки Error Alert. При использовании этого варианта сообщения появляются при попытке выхода из поля при условии неправильного введенного значения.



Посмотрим на результат применения всех трех заполненных вкладок:



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

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


3.1. Огласите весь список, пожалуйста.

Оказывается “огласить” список допустимых значений тоже можно не одним способом, а как минимум тремя.

Перечисление списка допустимых значений.
Первый и самый простой (именно этот способ и запечатлен на рисунке выше) – это перечисление списка значений через разделитель ; напрямую в поле Source первой вкладки:



Быстро, понятно, удобно и …
не стоит так делать (точнее делать можно, но предварительно хорошо подумав)

Представим ситуацию, когда таким образом провалидированные поля разбросаны щедрой рукой по всему Excel файлу и вдруг оказывается, что нужно добавить в список допустимых значений еще одну строчку!
Поэтому, хорошенько подумав такой стиль оформления применяйте только для абсолютно фиксированных небольших списков, например: {Да;Нет}, {M;Ж}, {Pass;Fail}

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



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

Именованный диапазон.
Оформив наш список значений в виде именованного диапазона с логичным и понятым для вас именем (в данном случае ”Проекты”). Мы можем вместо буквенно-числовой шифрограммы $A$2:$A$8 использовать осмысленное имя – Проекты.





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


4. Выводы

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


P.S. Для лентяев (к которым я причисляю и себя) в комментариях можно найти ссылу на Excel-файл, являющийся материализацией вышеизложенного материала.

С уважением,
Сергей Талалаев