Программа Excel позволяет контролировать вводимые данные в ячейки таблицы. То есть задать тип и диапазон данных, которые можно ввести в ячейку, все остальные данные при этом считаются неверными, и ввести их в ячейку не удастся. Также можно задать диапазон допустимых значений чисел, например, от 1 до 100, дат, например, не раньше текущей даты, предельное максимальное или минимальное значение, количество вводимых символов и т.д. В том случае если вводимые в ячейку данные не удовлетворяют заданным правилам, на экране, после нажатия клавиши
- Для осуществления контроля вводимых данных требуется определить условие проверки этих данных:
- 1. Выделить ячейку или группу ячеек, для которых нужно определить процедуру контроля.
- 2. На вкладке Данные -> Работа с данными сделать щелчок на кнопке Проверка данных.
- 3. На вкладке Параметры окна Проверка вводимых значений задать условие проверки данных.
Условие проверки задается путем выбора в раскрывающихся списках Тип данных и Значение соответственно типа данных (целое число, действительное число, дата и др.), которые считаются правильными, и правила сравнения (между, больше, меньше и т.д.), а также границ диапазона допустимых значений (поля Минимум и Максимум). Например, чтобы задать условие, означающее, что в ячейку можно ввести только целое число от 1 до 99, в списке Тип данных надо выбрать Целое число, в списке Значение - вариант между, а в поля Минимум и Максимум ввести, соответственно, 1 и 99.
Если для ячейки определено правило проверки, а значение, которое пользователь ввел в ячейку, неверное, то в результате нажатия клавиши
Разработчик может задать сообщение, которое будет появляться на экране как реакция на ввод ошибочных данных. Для этого следует раскрыть вкладку Сообщение об ошибке, в списке Вид выбрать вид сообщения и в поля Заголовок и Сообщение ввести, соответственно, заголовок и текст сообщения.
Вид сообщения определяет вид значка, отображаемого в окне сообщения, а также действия, которые могут быть выполнены, если данные неверные.
| Вид сообщения | Значок | Командные кнопки |
| Остановка |  | Повторить, Отмена |
| Предупреждение |  | Да, Нет, Отмена |
| Сообщение |  | OK, Отмена |
Независимо от типа сообщения при возникновении ошибки и в результате щелчка на кнопке Отмена операция ввода данных в ячейку отменяется, состояние ячейки остается таким, каким оно было до попытки ввода данных.
Предупреждающее сообщение заканчивается вопросом "Продолжить?". В результате щелчка на кнопке Да неверные данные записываются в ячейку. Щелчок на кнопке Нет активизирует режим редактирования.
Щелчок на кнопке OK в окне информационного сообщения оставляет неверные данные в ячейке таблицы.
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов организации и позволить пользователю лишь выбирать значения из этого списка. Этот подход поможет ускорить процесс ввода и уменьшить количество опечаток.
Выпадающий список можно создать с помощью
В этой статье создадим Выпадающий список с помощью () с типом данных Список .
Выпадающий список можно сформировать по разному.
А. Простейший выпадающий список - ввод элементов списка непосредственно в поле Источник
Предположим, в ячейке B 1 необходимо создать выпадающий список для ввода единиц измерений. Выделим ячейку B 1 и вызовем Проверку данных.
Если в поле Источник указать через точку с запятой единицы измерения шт;кг;кв.м;куб.м , то выбор будет ограничен этими четырьмя значениями.
Теперь смотрим, что получилось. Выделим ячейку B 1 . При выделении ячейки справа от ячейки появляется квадратная кнопка со стрелкой для выбора элементов из выпадающего списка .
Недостатки
этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B
1
); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков.
Преимущество
: быстрота создания списка.
Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)
Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента указать ссылку на этот диапазон.
Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4
Преимущество
: наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков.
Недостатки
: если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A
1:
A
100
. Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.
Второй недостаток: диапазон источника должен располагаться на том же листе, что и выпадающий список , т.к. для правил нельзя использовать ссылки на другие листы или книги (это справедливо для EXCEL 2007 и более ранних).
Избавимся сначала от второго недостатка – разместим перечень элементов выпадающего списка на другом листе.
B. Ввод элементов списка в диапазон (на любом листе)
Ввод элементов списка в диапазон ячеек, находящегося в другой книге
Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:
- в книге Источник. xlsx создайте необходимый перечень элементов;
- в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте , например СписокВнеш;
- откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
- выделите нужный диапазон ячеек, вызовите инструмент , в поле Источник укажите =ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;
При работе с перечнем элементов, расположенным в другой книге, файл Источник .xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки .
Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на =ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")
СОВЕТ:
Если на листе много ячеек с правилами Проверки данных
, то можно использовать инструмент (Главная/ Найти и выделить/ Выделение группы ячеек
). Опция Проверка данных
этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных
). При выборе переключателя Всех
будут выделены все такие ячейки. При выборе опции Этих
же
выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.
Примечание
:
Если выпадающий список
содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список
одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.
В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).
Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список , содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура или .
Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel. Собственно, ответ на вопрос дочери см. .
Средство проверки данных
Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. Например, необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. В случае если пользователь введет неправильное значение, программа выведет соответствующее сообщение (рис. 1).
Рис. 1. Вывод сообщения о неправильном вводе данных
Скачать заметку в формате или , примеры в формате
Excel позволяет легко создавать критерии для проверки данных, в том числе с использованием формул для создания более сложных критериев.
Средство проверки вводимых данных имеет принципиальный недостаток - если пользователь скопирует ячейку, а потом вставит ее в ячейку, в которой производится проверка вводимых данных, то все определенные для проверки формулы исчезнут.
Определение критерия проверки
Для определения типа данных, которые могут содержаться в ячейке или диапазоне ячеек, необходимо выполнить следующие действия.
1. Выделите ячейку или диапазон ячеек.
2. Выберите вкладку Данные , область Работа с данными → Проверка данных . Excel отобразит диалоговое окно Проверка вводимых значений.
3. Щелкните на вкладке Параметры (рис. 2).
Рис. 2. Вкладка Параметры диалогового окна Проверка вводимых значений
4. Выберите одну из опций из раскрывающегося списка Тип данных . В зависимости от выбранной опции может измениться внешний вид вкладки Параметры путем добавления или удаления дополнительных элементов управления (для определения формулы нужно выбрать опцию Другой ).
5. С помощью имеющихся на этой вкладке элементов управления задайте критерий проверки данных. Доступные элементы управления зависят от выбора, сделанного на предыдущем шаге.
6. (Опционально) Щелкните на вкладке Сообщение для ввода и введите сообщение, которое должно появиться на экране при выделении указанной ячейки . Это сообщение выводится для того, чтобы сообщить пользователю, какие данные можно вводить. Если пропустить этот шаг, то при выделении ячейки не появится никакого сообщения.
7. (Опционально) Щелкните на вкладке Сообщение об ошибке и введите сообщение, которое должно появляться в случае, когда пользователь введет недопустимое значение . Выбор вида сообщения в списке Вид определит, какой выбор будет у пользователя при внесении неверных данных. Для предотвращения ввода неправильных значений нужно выбрать вид сообщения Останов . Если пропустить этот шаг, то при возникновении ошибки будет появляться стандартное сообщение об ошибке.
8. Щелкните ОК.
После выполнения этих действий в выделенной ячейке или диапазоне будет производиться проверка вводимых данных.
Типы проверяемых данных
Вкладка Параметры диалогового окна Проверка вводимых значений предоставляет большой выбор типов данных для условий проверки. Выбрать тип допустимых данных можно в раскрывающемся списке Тип данных (как уже отмечалось, остальные элементы управления этой вкладки изменяются в зависимости от выбранного типа данных). Можно выбрать следующие типы данных.
- Любое значение . Выбор этой опции удаляет условие проверки данных. Однако сообщение для ввода все равно будет выводиться, если не снять флажок Выводить сообщение об ошибке во вкладке Сообщение для ввода .
- Целое число . Пользователь должен ввести целое число. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, можно определить, что вводимое значение должно быть целым числом и большим или равным 100.
- Действительное . Пользователь должен ввести действительное число. Диапазон допустимых значений можно определить с помощью раскрывающегося списка Значение . Например, можно определить, что вводимое число должно быть больше или равно 0 и меньше или равно 1.
- Список . Пользователь должен выбрать значение из предложенного списка значений. Подробнее см. ниже раздел .
- Дата . Пользователь должен ввести дату. С помощью раскрывающегося списка Значение можно определить допустимый диапазон дат. Например, можно определить, что вводимая дата должна быть больше или равна 1 января 2012 года и меньше или равна 31 декабря 2012 года.
- Время . Пользователь должен ввести значение времени. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, вводимое значение времени должно быть больше чем 12:00.
- Длина текста . Ограничивается длина вводимой строки (количество символов). С помощью раскрывающегося списка Значение можно определить допустимую длину строки. Например, можно определить, что длина вводимой строки должна равняться 1 (один символ).
- Другой . Логическая формула, которая определяет правильность вводимых пользователем данных. Формулу можно занести непосредственно в поле Формула (которое появляется при выборе этого типа) или определить ссылку на ячейку с формулой. Ниже приводятся примере нескольких полезных формул.
Во вкладке Параметры диалогового окна Проверка вводимых значений содержатся две опции.
- Игнорировать пустые ячейки . Если установлен флажок этой опции, то допускаются пустые записи в ячейках, для которых определена проверка данных. Этот флажок действует немного странно. Если флажок снять и определить в ячейке какое-нибудь правило проверки данных, то флажок позволит оставить ячейку пустой. Но как только вы попытаетесь в ячейке ввести что-то недопустимое, и после этого попробуете оставить ячейку пустой, то это не получится.
- Распространить изменения на другие ячейки с тем же условием. Если флажок этой опции установлен, то все внесенные изменения будут применяться также и к другим ячейкам, которые содержат исходные условия проверки данных. Если вы определили некое правило в одной или нескольких ячейках, а затем выбрали диапазон, включающий эти ячейки и некоторые другие, и кликнули в меню Проверка данных , то появится сообщение: «Выделенная область содержит ячейки без условий на значения. Распространить условия на эти ячейки?». Если нажмете «Да», то появится вкладка Параметры диалогового окна Проверка вводимых значений. Так вот сейчас флажок Распространить изменения на другие ячейки с тем же условием ставить не нужно.
Важно помнить, что даже после того, как была определена проверка вводимых данных, пользователь все равно может внести неправильные значения. Даже если в раскрывающемся списке Вид вкладки Сообщение об ошибке выбран вид сообщения Останов , вероятность внесения неверных данных все еще существует. Также стоит обратить внимание на то, что проверка вводимых данных не реагирует на результаты вычисления формул. Другими словами, применение проверки данных к ячейке, содержащей формулу, не даст никакого результата.
В Excel имеется команда Данные → Работа с данными → Проверка данных → Обвести неверные данные , после выбора которой все неверные значения будут обведены красным кружком (рис. 3).
Рис. 3. Ячейки с неверными значениями (значения которых больше 100) обведены кружками
Создание раскрывающегося списка
Возможно, проверка вводимых данных чаще всего используется для создания раскрывающегося списка значений. На рис. 4 приведен пример, в котором имена месяцев, содержащиеся в диапазоне А1:А12, используются для создания раскрывающегося списка.
Рис. 4. Список, созданный с помощью средства проверки данных
Чтобы создать такой список:
1. Введите список значений в один столбец или строку. Эти значения будут использоваться в раскрывающемся списке.
2. Выберите ячейку, которая должна содержать раскрывающийся список (в нашем примере – D3).
3. Во вкладке Параметры диалогового окна Проверка вводимых данных выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $А$1:$А$12).
4. Удостоверьтесь, что установлен флажок Список допустимых значений .
5. Сделайте другие установки в диалоговом окне Проверка вводимых данных , как описано в предыдущем разделе.
После выполнения этих действий при активизации ячейки справа от нее будет появляться кнопка раскрывающегося списка. Щелкните на этой кнопке, чтобы развернуть список и выбрать необходимое значение.
Если список должен содержать небольшое количество значений, то их можно ввести непосредственно в поле Источник во вкладке Параметры диалогового окна Проверка вводимых значений (это поле появится, если выбрать из раскрывающегося списка Тип данных тип Список ). Между вводимыми значениями нужно вставить разделитель, определенный в соответствии с региональными настройками (для России – это точка с запятой).
Если для задания раскрывающегося списка используется диапазон, то он обязательно должен находиться на том же листе, что и активизированная ячейка. В случае если список должен использовать значения из диапазона, который находится на другим листе, ему можно присвоить имя и затем использовать это имя в поле Источник (после символа равно). Например, если список содержит значения из списка МойСписок, нужно ввести следующую формулу: =МойСписок
Проверка данных с использованием формул
Создавать простые проверки данных с помощью средства проверки вводимых данных достаточно просто. Но настоящую мощь этого свойства можно ощутить только в том случае, если работать с формулами проверки данных.
Формула, определенная для проверки данных, должна возвращать логическое значение ИСТИНА или ЛОЖЬ. Если она вернет значение ИСТИНА, то введенное значение будет записано в ячейку. Если же результат вычисления формулы будет равен значению ЛОЖЬ, то появится сообщение об ошибке, в котором будет содержаться предупреждение, определенное во вкладке Сообщение об ошибке диалогового окна Проверка вводимых значений .
Определить формулу можно в диалоговом окне Проверка вводимых значений . Для этого из раскрывающегося списка Тип данных вкладки Параметры выберите тип Другой . Формулу можно ввести непосредственно в поле Формула или же вставить ссылку на ячейку, содержащую эту формулу. Поле Формула появляется во вкладке Параметры только в том случае, если выбран тип Другой .
Тип ссылок на ячейки в формулах для проверки данных
Если формула, введенная в диалоговое окно Проверка вводимых значений , содержит ссылку на ячейку, то она будет рассматриваться как относительная ссылка по отношению к верхней левой ячейке выделенного диапазона.
Следующий пример пояснит, почему именно относительные ссылки надо использовать в формулах для проверки данных. Предположим, что в диапазон В2:В10 необходимо только нечетные числа. В диалоговом окне Проверка вводимых значений нет такого типа данных, поэтому для создания критерия проверки данных надо использовать формулу. Для создания такого критерия проверки данных выполните следующие действия.
1. Выделите диапазон В2:В10 таким образом, чтобы ячейка В2 стала активизированной.
2. Выберите команду Данные → Работа с данными → Проверка данных , чтобы открыть диалоговое окно Проверка вводимых значений .
3. Перейдите на вкладку Параметры и в списке Тип данных выберите Другой .
4. Введите следующую формулу в поле Формула (рис. 5) =ЕНЕЧЁТ(В2). В этой формуле применена функция ЕНЕЧЁТ, которая возвращает значение ИСТИНА, если ее аргумент является нечетным числом.
5. Перейдите на вкладку Сообщение об ошибке и выберите вид сообщения Останов . Также введите текст сообщения «Разрешается ввод только нечетных чисел».
6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Проверка вводимых значений .
Рис. 5. Ввод формулы в диалоговое окно Проверка вводимых значений
Заметьте, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Эта формула должна применяться ко всему диапазону ячеек, поэтому следует ожидать, что каждая ячейка этого диапазона содержит такую же формулу. Поскольку в формуле ссыпка на ячейку относительная, то эта формула изменяется для каждой отдельной ячейки диапазона В2:В10. Чтобы в этом удостовериться, поставьте курсор, например, в ячейку В5, и откройте диалоговое окно Проверка вводимых значений . В этом окне вы должны увидеть формулу =ЕНЕЧЁТ(В5)
В общем случае, когда вводится формула для проверки данных в диапазон ячеек, следует использовать относительную ссылку на активизированную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют ситуации, когда надо сделать ссылку на некоторую конкретную ячейку. Например, вы хотите, чтобы в диапазон А1:В10 вводились только такие значения, которые превышают значение в ячейке С1. Для этого используется формула =А1>$С$1
Примеры формул для проверки данных
Здесь приведено несколько примеров, в которых для проверки вводимых данных используются формулы. Все формулы были введены непосредственно в поле Формула во вкладке Параметры диалогового окна Проверка вводимых значений .
Ввод только текста. Для того чтобы разрешить ввод только текста (и запретить ввод числовых значений) в ячейку или диапазон, используется следующая формула: =ЕТЕКСТ(А1). Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона.
Ввод значений, больших, чем в предыдущей ячейке. Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке: =А2>А1. В формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.
Ввод только уникальных значений. Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне А1:С20 повторяющиеся значения: =СЧЁТЕСЛИ($А$1:$С$20;А1)=1. Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ ($А$1:$С$20) используется абсолютная ссылка. Вторым аргументом (А1) является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона. На рис. 6 показано, как работает эта формула. Здесь сделана попытка ввести в ячейку А5 значение 2, которое уже есть в диапазоне А1:С20.
Рис. 6. Использование средства проверки данных для предотвращения ввода дублирующихся значений
Ввод текста, начинающегося с буквы А. В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра): =ЛЕВСИМВ(А1)= " а " . В этой формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А1.
Ниже приведена немного модифицированная формула проверки данных. С помощью этой формулы можно организовать ввод строки, которая состоит из пяти букв и начинается с буквы А:
=СЧЁТЕСЛИ (А1; " А???? ") =1
Цитируется по книге Джон Уокенбах. Microsoft Excel 2007. Библия пользователя. – М: ООО «И.Д. Вильямс», 2008. – С. 482–489.
В MS Excel есть ряд очень полезных инструментов для удивительно простого создания по-настоящему сложных форм сбора информации. Однако, вот незадача — чаще всего камнем преткновения во взаимодействии с ними служат не возможности табличного редактора, а человеческая «косорукость». Пользователи стирают формулы из ячеек, вносят некорыстные данные, в общем, портят плоды наших трудов.
К счастью они делают это не со зла, и все что нужно нам — разработчикам, снабдить их подсказками и уже готовыми заранее ответами. Как это сделать, я расскажу в этой статье.
Подсказки в MS Excel
Первый способ вставки подсказки
Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?
Самое простое, это щелкнуть по нужной ячейке правой кнопкой мыши и выбрать пункт «Вставить примечание «. В появившемся поле достаточно написать требуемый текст, и если пользователь поднесет курсор мыши к этой ячейке, текст немедленно появится во всплывающей подсказке.
Обратите внимание: ячейка с примечанием помечена особым значком — маленьким красным треугольником в правом верхнем углу. Сам же текст примечания может быть отформатирован как любой другой — вы можете сделать его жирным или курсивным, чтобы подчеркнуть особо важные моменты своего послания.
Удалить примечания можно только из контекстного меню правой кнопки мыши
Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».
Второй способ вставки подсказки
Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.
Поставьте курсор мыши в выбранную ячейку, на панели «Данные » в группе «Работа с данным » откройте инструмент «Проверка данных «, и заполните поля «заголовок» и «текст сообщения» на вкладке «Сообщение для ввода «. Нажмите «Ок».
Теперь, при щелчке на ячейке с подсказкой (обратите внимание — отмеченная таким способом ячейка не помечается красным треугольником, и выглядит так же как любая другая) также будет выведен его текст в желтом прямоугольнике. Удалить и изменить примечание введенное через «Проверку данных» с помощью правой кнопки мыши, нельзя.
Проверка по числу введенных символов
Подсказки — это хорошо, но что делать с самыми «прогрессивными» пользователями, которые подсказок не читают? Остается одно — бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.
Снова открываем инструмент «Проверка данных «, переходим на вкладку «Параметры » и смотрим на наши возможности по ограничению возможностей пользователя.
Первое, что может оказаться полезным, это проверка по длине введенного текста . Выбираем пункт «Длина текста » и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.
Проверка по числу
Ещё один хороший способ фильтрации который пригодится без сомнения: фильтрация по числу. Установите фильтр подобный указанному на рисунке и навсегда избавьтесь от проблем с тем, что в поле цена появляются буквы, пробелы и лишние символы.
Выбор из имеющегося списка значений
Самая мощная и интересная проверка ввода в MS Excel — выборка из выпадающего списка. Она подойдет не для всех типов ячеек, но там где нужна точность, а диапазон значений не слишком велик, выборка по списку это то что надо.
А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.
Выбираем в «Проверке данных » вкладку «Параметры «, и указываем «Тип данных» → «Список» . Ввести «предустановленные» значения можно двумя способами:
- Выбрать диапазон данных с помощью кнопки «Диапазон данных».
- Вписать значения выпадающего списка вручную, через точку с запятой.
…или выберем из диапазона
Второй способ более интересен, так как данные «спрятаны» от пользователя, зато набить их больше десятка подряд — проблематично. Первый способ позволяет без проблем задать намного больший список для выбора данных, однако прежде придется этот список разместить где-то в документе (каждое значение на отдельной строке), например на отдельном «служебном» листе в той же книге MS Excel.
Выбор из списка на листе MS Excel. Других данных ввести не удастся.
Вот и готово — теперь заполнить ячейку можно лишь теми значениями, что мы указали в списке. При попытке ввести данные вручную, MS Excel выдаст нам уже знакомую ошибку.
Кстати, можно изменить и её текст — для этого перейдите на вкладку «Сообщение для вывода » в «Проверке данных», как мы уже делали при «втором способе» вставке подсказок, измените текст на требуемый.
При заполнении таблиц в Экселе вручную приходится вводить очень много данных, что может привести к появлению множества опечаток и ошибок. Некоторые такие ошибки может исключить проверка данных в Excel при вводе, что мы далее и рассмотрим.
Сразу стоит отметить, что проверка вводимых данных в Excel будет работать только при вводе этих данных с клавиатуры. При вставке информации из буфера обмена или другого источника проверка не выполняется. Для уже введенных значений все-же можно произвести проверку, что приведет к обведению неверных данных красным овалом, но об этом далее.
Чтобы сделать проверку данных в Excel для определенных ячеек, их необходимо выделить, перейти на вкладку «Данные» , и найти в разделе «Работа с данными» меню «Проверка данных» . При нажатии на стрелочку справа от данного пункта появляется три пункта меню, из которых нам нужен самый первый «Проверка данных…» .
В появившемся окошке «Проверка вводимых значений» настраивается проверка. На вкладке «Параметры» выбирается из списка тип данных, которые будут проверяться и вводиться в выделенный нами диапазон ячеек. Для примера выберем «Целое число» .
После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:» . Выберем для примера «Между» .
На следующей вкладке «Сообщение для ввода» можно указать заголовок и само сообщение подсказку, которое будет высвечиваться при активации ячейки с проверкой вводимых данных.
На вкладке «Сообщение об ошибке» можно указать заголовок и само сообщение, появляющееся при ошибочном вводе данных, а также действие, которое при этом будет производится. На вкладке «Вид:» доступно три варианта. «Останов» предотвращает ввод недопустимых данных и предлагает повторить попытку. «Предупреждение» выдает сообщение об ошибке, но дает возможность продолжить ввод недопустимого значения. «Сообщение» просто проинформирует о недопустимом введенном значении.
Сообщение подсказка.
Вид «Останов» .
Вид «Предупреждение» .
