Поле серия содержит ошибку ввода

Вопросы и ответы — Евробайт

Ошибка: Серия и номер имеет неверное значение

В каком формате вводить серию и номер паспорта??

14.09.2018   13:14
Пользователь ID 109956

ОТВЕТЫ НА ВОПРОС (1)

Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверку данных.

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы. Кроме того, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых значений, чтобы исключить возможные ошибки. Проверка данных Excel позволяет выполнять все эти действия во всех версиях Microsoft Excel 365, 2019, 2016, 20013, 2010 и более ранних версиях.

Что такое проверка данных в Excel?

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

Вот лишь несколько примеров того, что может сделать проверка данных в Excel:

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

Например, вы можете настроить правило, которое ограничивает ввод данных 3-значными числами от 100 до 999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что было сделано неправильно:

Как сделать проверку данных в Excel

Чтобы добавить проверку данных в Excel, выполните следующие действия.

1. Откройте диалоговое окно «Проверка данных».

Напомним, где находится кнопка проверки данных в Excel. Выбрав одну или несколько ячеек для проверки, перейдите на вкладку «Данные» > группа «Работа с данными» и нажмите кнопку «Проверка данных».

2. Создайте правило проверки Excel.

На вкладке «Параметры» определите критерии проверки в соответствии с вашими потребностями. В критериях вы можете указать любое из следующего:

• Значения — введите числа в поля критериев, как показано на снимке экрана ниже.
• Ссылки на ячейки — создание правила на основе значения или формулы в другой ячейке.
• Формулы — позволяют выразить более сложные условия.

В качестве примера создадим правило, разрешающее пользователям вводить только целое число от 100 до 999:

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

3. Подсказка по вводу (необязательно).

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте соответствующую вкладку  и выполните следующие действия:

• Убедитесь, что установлен флажок Отображать подсказку при выборе ячейки.
• Введите заголовок и текст сообщения в соответствующие поля.
• Нажмите OK, чтобы закрыть диалоговое окно.

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

4. Отображение предупреждения об ошибке (необязательно)

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

Тип оповещения	Описание
Стоп (по умолчанию)	Самый строгий тип предупреждений, запрещающий пользователям вводить неверные данные. Вы нажимаете «Повторить», чтобы ввести другое значение, или «Отмена», чтобы удалить запись.
Предупреждение	Предупреждает пользователей о том, что данные недействительны, но не препятствует их вводу. Вы нажимаете «Да», чтобы ввести недопустимое значение, «Нет», чтобы изменить его, или «Отмена», чтобы удалить запись.
Информация	Наименее строгий тип оповещения, который информирует пользователей только о неверном вводе данных. Нажмите «ОК», чтобы ввести недопустимое значение, или «Отмена», чтобы удалить его из ячейки.

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку «Сообщение об ошибке» и задайте следующие параметры:

• Установите флажок Выводить сообщение об ошибке (обычно установлен по умолчанию).
• В поле Вид выберите нужный тип оповещения.
• Введите заголовок и текст сообщения об ошибке в соответствующие поля.
• Нажмите ОК.

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

Примечание. Если вы не введете собственное сообщение, появится стандартное предупреждение Stop со следующим текстом: Это значение не соответствует ограничениям проверки данных, установленным для этой ячейки.

Как настроить ограничения проверки данных Excel

При добавлении правила проверки данных в Excel вы можете выбрать один из предопределенных параметров или указать новые критерии на основе собственной формулы. Ниже мы обсудим каждую из встроенных опций.

Как вы уже знаете, критерии проверки определяются на вкладке «Параметры» диалогового окна «Проверка данных» (вкладка «Данные» > «Проверка данных»).

В первую очередь нужно настроить проверку типа записываемых данных.

К примеру, чтобы ограничить ввод данных целым или десятичным числом, выберите соответствующий элемент в поле Тип данных. Затем выберите один из следующих критериев в поле Данные:

• Равно или не равно указанному числу
• Больше или меньше указанного числа
• Между двумя числами или вне, чтобы исключить этот диапазон чисел

Например, вот как выглядят ограничения по проверке данных Excel, которые допускают любое целое число больше 100:

Проверка даты и времени в Excel

Чтобы проверить даты, выберите «Дата» в поле «Тип данных», а затем выберите соответствующий критерий в поле «Значение». Существует довольно много предопределенных параметров на выбор: разрешить только даты между двумя датами, равные, большие или меньшие определенной даты и т. д.

Точно так же, чтобы проверить время, выберите Время в поле Значение, а затем определите необходимые критерии.

Например, чтобы разрешить только даты между датой начала в B1 и датой окончания в B2, примените это правило проверки даты Excel:

Разрешить только будни или выходные

Чтобы разрешить пользователю вводить даты только  будних или выходных дней, настройте пользовательское правило проверки на основе функции ДЕНЬНЕД (WEEKDAY).

Если для второго аргумента установлено значение 2, функция возвращает целое число в диапазоне от 1 (понедельник) до 7 (воскресенье). Так, для будних дней (пн-пт) результат формулы должен быть меньше 6, а для выходных (сб и вс) — больше 5.

Таким образом, разрешить только рабочие дни:

=ДЕНЬНЕД( ячейка ; 2)<6

Разрешить только выходные :

=ДЕНЬНЕД( ячейка ; 2)>5

Например, чтобы разрешить ввод только рабочих дней в ячейки C2:C8, используйте следующую формулу:

=ДЕНЬНЕД(A2;2)<6

Проверить даты на основе сегодняшней даты

Во многих случаях может потребоваться использовать сегодняшнюю дату в качестве начальной даты допустимого диапазона дат. Чтобы получить текущую дату, используйте функцию СЕГОДНЯ , а затем добавьте к ней нужное количество дней, чтобы вычислить дату окончания временного периода.

Например, чтобы ограничить ввод данных через 6 дней (7 дней, включая сегодняшний день), мы можем использовать встроенное правило даты с критериями в виде формул:

• Выберите Дата в поле Тип данных
• Выберите в поле Значение – между
• В поле Начальная дата введите выражение    =СЕГОДНЯ()
• В поле Конечная дата  введите   =СЕГОДНЯ() + 6

Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты. Для этого выберите меньше или больше, чем в поле Значение, а затем введите =СЕГОДНЯ() в  поле Начальная дата или Конечная дата соответственно.

Проверка времени на основе текущего времени

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

В поле Тип данных выберите Время .

В поле Значение выберите «меньше», чтобы разрешить только время до текущего времени, или «больше», чтобы разрешить время после текущего времени.

В поле Время окончания или Время начала (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:

Чтобы проверить дату и время на основе текущей даты и времени:

=ТДАТА()

Чтобы проверить время на основе текущего времени, используйте выражение:

=ВРЕМЯ(ЧАС(ТДАТА());МИНУТЫ(ТДАТА());СЕКУНДЫ(ТДАТА()))

Проверка длины текста

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

Например, чтобы ограничить ввод до 15 символов, создайте такое правило:

Примечание. Параметр «Длина текста» ограничивает количество символов, но не тип данных. Это означает, что приведенное выше правило разрешает как текст, так и числа до 15 символов или 15 цифр соответственно.

Список проверки данных Excel (раскрывающийся список)

Чтобы добавить для проверки вводимых данных раскрывающийся список элементов в ячейку или группу ячеек, выберите целевые ячейки и выполните следующие действия:

1. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
2. На вкладке «Настройки» выберите «Список» в поле «Тип данных».
3. В поле Источник введите элементы списка проверки Excel, разделенные точкой с запятой. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да; Нет; Н/Д.
4. Убедитесь, что выбрана опция Список допустимых значений, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
5. Нажмите ОК.

Выпадающий список проверки данных Excel будет выглядеть примерно так:

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

Другие способы создания списка проверки данных в Excel

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

• Создать список проверки данных из диапазона ячеек.
• Создать динамический список проверки данных на основе именованного диапазона.
• Получить список проверки данных Excel из умной таблицы. Лучше всего то, что раскрывающийся список на основе таблицы является динамическим по своей природе и автоматически обновляется при добавлении или удалении элементов из этой таблицы.

Во всех этих случаях вы просто записываете соответствующую ссылку на диапазон либо элемент таблицы в поле Источник.

Разрешить только числа

В дополнение к встроенным правилам проверки данных Excel, обсуждаемым в этом руководстве, вы можете создавать собственные правила с собственными формулами проверки данных. 

Удивительно, но ни одно из встроенных правил проверки данных Excel не подходит для очень типичной ситуации, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это можно легко сделать с помощью пользовательской формулы проверки данных, основанной на функции ЕЧИСЛО(), например:

=ЕЧИСЛО(C2)

Где C2 — самая верхняя ячейка диапазона, который вы хотите проверить.

Примечание. Функция ЕЧИСЛО допускает любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в Excel.

Разрешить только текст

Если вы ищете обратное — разрешить только текстовые записи в заданном диапазоне ячеек, то создайте собственное правило с функцией ЕТЕКСТ (ISTEXT), например:

=ЕТЕКСТ(B2)

Где B2 — самая верхняя ячейка выбранного диапазона.

Разрешить текст, начинающийся с определенных символов

Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:

=СЧЁТЕСЛИ(A2; » текст *»)

Например, чтобы убедиться, что все идентификаторы заказов в столбце A начинаются с префикса «AРТ-», «арт-», «Aрт-» или «aРт-» (без учета регистра), определите пользовательское правило с этой проверкой данных.

=СЧЁТЕСЛИ(A2;»АРТ-*»)

Формула проверки с логикой ИЛИ (несколько критериев)

В случае, если есть 2 или более допустимых префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:

=СЧЁТЕСЛИ(A2;»АРТ-*»)+СЧЁТЕСЛИ(A2;»АБВ-*»)

Проверка ввода с учетом регистра

Если регистр символов имеет значение, используйте СОВПАД  (EXACT) в сочетании с функцией ЛЕВСИМВ, чтобы создать формулу проверки с учетом регистра для записей, начинающихся с определенного текста:

=СОВПАД(ЛЕВСИМВ(ячейка; число_символов); текст)

Например, чтобы разрешить только те коды заказов, которые начинаются с «AРТ-» (ни «арт-», ни «Арт-» не допускаются), используйте эту формулу:

=СОВПАД(ЛЕВСИМВ(A2;4);»АРТ-«)

В приведенной выше формуле функция ЛЕВСИМВ извлекает первые 4 символа из ячейки A2, а СОВПАД выполняет сравнение с учетом регистра с жестко заданной подстрокой (в данном примере «AРТ-«). Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит успешно; в противном случае возвращается ЛОЖЬ и проверка завершается неудачно.

Разрешить только значения, содержащие определенный текст

Чтобы разрешить ввод значений, которые содержат определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО (ISNUMBER) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH) в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

Проверка без учета регистра:

ЕЧИСЛО(ПОИСК( текст ; ячейка ))

Проверка с учетом регистра:

ЕЧИСЛО(НАЙТИ( текст ; ячейка ))

В нашем примере, чтобы разрешить только записи, содержащие текст «AР» в ячейках A2: A8, используйте одну из следующих формул, создав правило проверки в ячейке A2:

Без учета регистра:

=ЕЧИСЛО(ПОИСК(«ар»;A2))

С учетом регистра:

=ЕЧИСЛО(НАЙТИ(«АР»;A2))

Формулы работают по следующей логике:

Вы ищете подстроку «AР» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Если поиск успешен и «АР» найден в ячейке, мы получаем номер позиции в тексте, где эта подстрока была найдена. Далее функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае, если подстроку не удалось найти, результатом будет ошибка и ЕЧИСЛО возвращает ЛОЖЬ. Запись не будет разрешена в ячейке.

Разрешить только уникальные записи и запретить дубликаты

В ситуациях, когда определенный столбец или диапазон ячеек не должны содержать дубликатов, настройте пользовательское правило проверки данных, разрешающее только уникальные записи. Для этого мы можем использовать классическую формулу СЧЁТЕСЛИ для выявления дубликатов :

=СЧЁТЕСЛИ( диапазон ; самая верхняя_ячейка )<=1

Например, чтобы убедиться, что в ячейки с A2 по A8 вводятся только уникальные идентификаторы заказов, создайте настраиваемое правило со следующей формулой проверки данных:

=СЧЁТЕСЛИ($A$2:$A$8; A2)<=1

При вводе уникального значения формула возвращает ИСТИНА, и проверка проходит успешно. Если такое же значение уже существует в указанном диапазоне (счетчик больше 1), функция СЧЁТЕСЛИ возвращает ЛОЖЬ, и вводимые данные не проходят проверку.

Обратите внимание, что мы фиксируем диапазон абсолютными ссылками на ячейки (A$2:$A$8) и используем относительную ссылку для верхней ячейки (A2), чтобы формула корректно изменялась для каждой ячейки в проверяемом диапазоне.

Как отредактировать проверку данных в Excel

Чтобы изменить правило проверки Excel, выполните следующие действия:

1. Выберите любую из проверенных ячеек.
2. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
3. Внесите необходимые изменения.
4. Установите флажок Применить эти изменения ко всем другим ячейкам с теми же параметрами, чтобы скопировать внесенные вами изменения во все остальные ячейки с исходными критериями проверки.
5. Нажмите OK, чтобы сохранить изменения.

Например, вы можете отредактировать список проверки данных Excel, добавив или удалив элементы из поля «Источник», и применить эти изменения ко всем другим ячейкам, содержащим тот же раскрывающийся список.

Как скопировать правило проверки данных Excel в другие ячейки

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + С , чтобы скопировать его.
2. Выберите другие ячейки, которые вы хотите проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
3. Щелкните выделенный фрагмент правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Условия на значения».

Либо используйте комбинацию клавиш Ctrl + Alt + V,  и затем — Н.

1. Нажмите ОК.

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

Как найти ячейки с проверкой данных в Excel

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку «Главная» > группа «Редактирование» и нажмите «Найти и выделить» > «Проверка данных» :

Это выберет все ячейки, к которым применены какие-либо правила проверки данных. Если необходимо, из этих выбранных ячеек вы можете удалить проверку.

Как убрать проверку данных в Excel

В целом, есть два способа удалить проверку в Excel: стандартный подход, разработанный Microsoft, и метод без мыши, разработанный фанатами Excel, которые никогда не отрывают руки от клавиатуры без крайней необходимости (например, чтобы выпить чашку кофе:)

Способ 1: Обычный способ отключить проверку данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

1. Выберите ячейку (ячейки) с проверкой данных.
2. На вкладке «Данные» нажмите кнопку «Проверка данных».
3. На вкладке «Настройки» нажмите кнопку «Очистить все», а затем нажмите «ОК».

Советы:

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

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

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + С, чтобы скопировать ее в буфер обмена.
2. Выберите ячейки, в которых вы хотите отключить проверку данных.
3. Нажмите Ctrl + Alt + V, и затем Н. Эта комбинация клавиш вызовет «Специальная вставка» > «Проверка данных».
4. Нажимаем ОК.

Советы по проверке данных в Excel

Теперь, когда вы знакомы с основами проверки данных в Excel, позвольте мне поделиться несколькими советами, которые могут сделать ваши правила намного более эффективными.

Проверка данных на основе другой ячейки

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

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

Например, чтобы разрешить любое целое число, находящееся между 100 и 999, выберите критерии «минимум» и «максимум» в вкладке «Параметры» и введите в них адреса ячеек с этими числами:

Рис18

Вы также можете ввести формулу в ячейку, на которую указывает ссылка, и Excel проверит ввод на основе этой формулы.

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Конечная дата, что будет иметь тот же эффект.

Правила проверки на основе формул

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

Например, чтобы ограничить ввод минимальным и максимальным значениями в существующем списке чисел, скажем, A1:A10, используйте следующие формулы:

=МИН($С$2:$С$10)

=МАКС($С$2:$С$10)

Обратите внимание, что мы фиксируем диапазон с помощью знака $ (абсолютная ссылка на ячейки), чтобы наше правило проверки Excel работало правильно для всех выбранных ячеек.

Как найти неверные данные на листе

Хотя Microsoft Excel позволяет применять проверку данных к ячейкам, в которых уже есть данные, он не уведомит вас, если некоторые из уже существующих значений не соответствуют критериям проверки.

Чтобы найти недействительные данные, которые попали в ваши рабочие листы до того, как вы добавили проверку данных, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Обвести неверные данные».

Это выделит все ячейки, которые не соответствуют критериям проверки.

Как только вы исправите неверную запись, отметка автоматически исчезнет. Чтобы удалить все отметки, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Удалить обводку неверных данных».

Как поделиться книгой с проверкой данных

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

После совместного использования книги ваши правила проверки данных продолжат работать, но вы не сможете ни изменить их, ни добавить новые правила.

Почему проверка данных Excel не работает?

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

Проверка данных не работает для скопированных данных

Проверка данных в Excel предназначена для запрета ввода недопустимых данных непосредственно в ячейку, но не может помешать пользователям копировать недопустимые данные. 

Хотя нет способа отключить клавиши копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивания ячеек».

Проверка данных Excel не активна

Кнопка «Проверка данных» не активна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

Неправильные формулы проверки данных

При проверке данных на основе формул в Excel необходимо проверить три важные вещи:

• Формула проверки не возвращает ошибок.
• Формула не ссылается на пустые ячейки.
• Используются правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку «Формулы» > группу «Расчет», нажмите кнопку «Параметры расчета» и выберите «Автоматически».

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

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, такую ​​как #Н/Д, #ЗНАЧ или #ДЕЛ/0!.

Если вы создаете пользовательское правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или приравненные к ним значения 1 и 0 соответственно.

Если вы используете критерий на основе формулы во встроенном правиле (как мы делали для проверки времени на основе текущего времени), он также может возвращать неправильное числовое значение.

Формула проверки данных не должна ссылаться на пустую ячейку

Если вы активируете опцию «Игнорировать пустые ячейки» при определении правила (обычно выбрано по умолчанию), то следите, чтобы ваши формулы или условия не ссылались на пустую ячейку.

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

Абсолютные и относительные ссылки на ячейки в формулах проверки

При настройке правила проверки Excel на основе формулы помните, что все ссылки на ячейки в вашей формуле относятся к верхней левой ячейке в выбранном диапазоне.

Если вы создаете правило для нескольких ячеек и ваши критерии проверки содержат адреса конкретных ячеек , обязательно зафиксируйте ячейку в формуле при помощи абсолютной ссылки (со знаком $, например, $A$1), иначе ваше правило будет правильно работать только для первой ячейки.

Чтобы лучше проиллюстрировать это, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейках с A2 по A8 целыми числами от 100 (минимальное значение) до 999. Чтобы проще было при необходимости изменить критерии проверки, используем ссылки на ячейки с этими значениями, как показано на скриншоте ниже:

Это правило будет корректно выполняться только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3. Поскольку эти ячейки пусты, то условие ввода теперь – любое число больше либо меньше нуля. Правда, символьные значения и дробные числа вы вводить не сможете, так как продолжает действовать ограничение на тип данных – целые числа.

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы зафиксировать их: =$E$2 и $F$2. Или используйте клавишу  F4 для переключения между различными типами ссылок.

Вот как вы можете добавить и использовать проверку данных в Excel. Я благодарю вас за чтение и надеюсь ещё увидеть вас в нашем блоге!

Страница с отображением ошибки заполнения каких-либо форм приводят пользователей в настоящее бешенство! Вы только что 10 минут (в лучшем случае) заполняли 20 полей анкеты, старательно вчитываясь в вопросы и обдумывая каждое слово, и тут внезапно появляется слово “ошибка” после того, как вы нажали кнопку “регистрация”. И всё из-за одного маленького неверно заполненного окошка.

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

Ещё мы заметили, что испытуемые после подобных инцидентов начинали хитрить, чтобы избежать подобных ситуаций в будущем. Например, они ставили прочерки поле «Название фирмы», подозревая, что это поле может быть обязательным для заполнения.

Попадая на страницу ошибки после заполнения формы, пользователь чувствует недостаточность своих усилий для достижения цели, несмотря на то, что он заполнил более 90% полей регистрационной формы правильно. (Изображение:  Blue Nile)

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

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

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

1. Традиционный подход: перезагрузка текущей страницы

Вот как выглядит типичная страница ошибки при оформлении заказа на сайте Staples :

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

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

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

2. Оптимизированная перезагрузка текущей страницы

Справедливости ради рассмотрим улучшенную версию страницы ошибки от Staples:

Перед вами макет оптимизированной версии данной страницы. Обратите внимание на ссылку сверху и описание ошибки рядом с неправильно заполненными полями.

Что же изменилось:

1. Появилось описание ошибок в верхней части страницы (их количество и конкретизация).
2. Допущенные ошибки показаны ссылками, которые переносят курсор в необходимое поле (что особенно важно для длинных форм).
3. Cообщения-подсказки для каждого ошибочно заполненного поля указывают на правильно отформатированные данные (например, john@example.com), или в них содержится совет относительно того, что нужно исправить(например, «похоже в адресе электронной почты отсутствует окончание (.com, .org, и т.д) вместо непонятного “адрес электронной почты введен неправильно.”

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

Указано неверное поле для ввода по строке: Код

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

Используем CSS

В CSS существует четыре специальных псевдокласса, применимых к полям формы: :valid (валидное поле), :invalid (невалидное), :required (обязательное) и :optional (необязательное). Их можно использовать, чтобы добавлять некоторые — хотя и весьма ограниченные — подсказки пользователям, заполняющим форму.

Используя :valid и :invalid, мы можем показать пользователю, правильно ли заполнено поле по мере ввода.

input:valid {
  border-color: green;
}

input:invalid {
  border-color: red;
}

Однако с этим способом связана одна проблема: стили применяются до того, как пользователь начнёт работу с формой. Поля, обязательные для заполнения, сразу подсветятся нам как :invalid, а необязательные — как :valid. Это значит, что пользователь, даже не приступив к заполнению формы, может сразу же получить негативную обратную связь. Не очень-то хорошо.

Стилизация состояний :required и :optional сама по себе не особо полезна, поскольку эта информация обычно указывается в подписях к полям формы. Однако мы можем объединить эти состояния с псевдоклассами :valid / :invalid и стилизовать их комбинации. Например, мы хотим показывать лишь положительный результат, когда валидно обязательное к заполнению поле.

input:required:valid {
  border-color: green;
}

Используем JavaScript

JavaScript даёт намного больше возможностей для улучшения работы пользователей с формами. Давайте рассмотрим в качестве примера три числовых поля, у каждого из которых установлен минимум в 10, максимум в 100 и шаг в 10 единиц.

<form>
  <label>
    Number Input 1
   <input type="number" min="10" max="100" step="10">
  </label>

  <label>
    Number Input 2
   <input type="number" min="10" max="100" step="10">
  </label>

  <label>
    Number Input 3
   <input type="number" min="10" max="100" step="10">
  </label>

  <input type="submit">
</form>

Устанавливая атрибуты min, max и step, мы можем быть уверены в правильности значения только тогда, когда пользователь использует специальные контролы числового поля. Но что мешает пользователю ввести вручную некорректные данные? Вот что произойдёт, если он вставит 1, 12 и 123 в три поля и отправит форму:

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

Добавляем несколько сообщений об ошибках в один тултип

Валидируя поля, браузер проверяет их по определённому списку потенциальных ошибок. В каждом поле содержится специальный объект validity, включающий в себя список булевых значений, характеризующих ту или иную проверку на валидность. Например, вот такой validity-объект будет у поля, когда пользователь введёт в него 1:

input.validity = {
  valid: false // Поле валидно
  customError: false // Установленно специальное сообщение ошибки
  patternMismatch: false // Значение не удовлетворяет шаблону, установленному в атрибуте pattern
  rangeOverflow: false // Значение превосходит атрибут max
  rangeUnderflow: true // Значение меньше атрибута min
  stepMismatch: true // Значение не соответствует указаному шагу
  tooLong: false // Значение слишком длинное
  tooShort: false // Значение слишком короткое
  typeMismatch: false // Значение не соответствует указаному атрибуту type
  valueMissing: false // Отсутствует обязательное значение
};

Примечание переводчика: Слово «mismatch» переводится как «несоответствие». Поэтому в значениях patternMismatch, stepMismatch и typeMismatch обратная логика: true — значение не удовлетворяет атрибуту, false — удовлетворяет.

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

function CustomValidation() { }

CustomValidation.prototype = {
  // Установим пустой массив сообщений об ошибках
  invalidities: [],

  // Метод, проверяющий валидность
  checkValidity: function(input) {

    var validity = input.validity;

    if (validity.patternMismatch) {
      this.addInvalidity('This is the wrong pattern for this field');
    }

    if (validity.rangeOverflow) {
      var max = getAttributeValue(input, 'max');
      this.addInvalidity('The maximum value should be ' + max);
    }

    if (validity.rangeUnderflow) {
      var min = getAttributeValue(input, 'min');
      this.addInvalidity('The minimum value should be ' + min);
    }

    if (validity.stepMismatch) {
      var step = getAttributeValue(input, 'step');
      this.addInvalidity('This number needs to be a multiple of ' + step);
    }

    // И остальные проверки валидности...
  },

  // Добавляем сообщение об ошибке в массив ошибок
  addInvalidity: function(message) {
    this.invalidities.push(message);
  },

  // Получаем общий текст сообщений об ошибках
  getInvalidities: function() {
    return this.invalidities.join('. n');
  }
};

// Добавляем обработчик клика на кнопку отправки формы
submit.addEventListener('click', function(e) {
  // Пройдёмся по всем полям
  for (var i = 0; i < inputs.length; i++) {

    var input = inputs[i];

    // Проверим валидность поля, используя встроенную в JavaScript функцию checkValidity()
    if (input.checkValidity() == false) {

      var inputCustomValidation = new CustomValidation(); // Создадим объект CustomValidation
      inputCustomValidation.checkValidity(input); // Выявим ошибки
      var customValidityMessage = inputCustomValidation.getInvalidities(); // Получим все сообщения об ошибках
      input.setCustomValidity(customValidityMessage); // Установим специальное сообщение об ошибке

    } // закончился if
  } // закончился цикл
});

Теперь при попытке отправить форму мы увидим вот это:

Отображаем несколько ошибок в одном тултипе

Стало лучше, поскольку теперь будут показываться все сообщения об ошибках, связанные с конкретным полем. Однако другая проблема всё ещё не решена: ошибки по-прежнему показываются лишь для первого поля.

Это ограничение валидации, устанавливаемое браузером. Чтобы его побороть, нам нужно пойти другим путём.

Показываем все ошибки для всех полей

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

Этого можно добиться какой-то парой дополнительных строчек в нашем коде:

CustomValidation.prototype.getInvaliditiesForHTML = function() {
  return this.invalidities.join('. <br>');
}

// Добавляем обработчик клика на кнопку отправки формы
submit.addEventListener('click', function(e) {
  // Пройдёмся по всем полям
  for (var i = 0; i < inputs.length; i++) {

    var input = inputs[i];

    // Проверим валидность поля, используя встроенную в JavaScript функцию checkValidity()
    if (input.checkValidity() == false) {

      var inputCustomValidation = new CustomValidation(); // Создадим объект CustomValidation
      inputCustomValidation.checkValidity(input); // Выявим ошибки
      var customValidityMessage = inputCustomValidation.getInvalidities(); // Получим все сообщения об ошибках
      input.setCustomValidity(customValidityMessage); // Установим специальное сообщение об ошибке

      // Добавим ошибки в документ
      var customValidityMessageForHTML = inputCustomValidation.getInvaliditiesForHTML();
      input.insertAdjacentHTML('afterend', '<p class="error-message">' + customValidityMessageForHTML + '</p>')
      stopSubmit = true;

    } // закончился if
  } // закончился цикл

  if (stopSubmit) {
    e.preventDefault();
  }
});

Вот что происходит при клике на submit теперь:

Используем нестандартные проверки валидности

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

Так как мы уже проверяем все возможные ошибки вручную в нашей функции CustomValidation.prototype.checkValidity, мы можем просто-напросто добавить туда ещё несколько проверок.

CustomValidation.prototype.checkValidity = function(input) {

  // Тут идут встроенные проверки валидности

  // А тут специальные
  if (!input.value.match(/[a-z]/g)) {
    this.addInvalidity('At least 1 lowercase letter is required');
  }

  if (!input.value.match(/[A-Z]/g)) {
    this.addInvalidity('At least 1 uppercase letter is required');
  }
};

Валидация в реальном времени

Хотя текущий способ выглядит намного лучше, он тоже не без изъянов. Наихудший из недочётов заключается в том, что пользователь не сможет увидеть никаких сообщений, пока не нажмёт на кнопку отправки формы. Было бы гораздо лучше, если бы валидация поля происходила сразу же при его заполнении. Можно выделить три правила для того, чтобы с формой было удобно работать:

1. Требования для каждого поля чётко видны до того, как пользователь начал печатать.
2. Как только пользователь начинает вводить данные, соблюдая требования, он сразу видит индикатор успешного заполнения поля или подсказки, если есть ошибки.
3. Нужно отображать сообщения об ошибках таким образом, чтобы пользователь не мог отправить некорректно заполненную форму.

Если вы хотите попробовать свои силы (и даже сделать получше), вы можете воспользоваться вот этим шаблоном.

---

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.

ТелеграмПодкастБесплатные учебники