Эксель проверка данных: Применение проверки данных к ячейкам

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

Примечание: Это расширенная статья о проверке данных. Общие сведения о проверке данных, а также сведения о проверке данных в ячейке или диапазоне см. в статье Добавление проверки данных в ячейку или диапазон ячеек.

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

Ситуации, в которых полезно использовать проверку данных

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

• org/ListItem»>

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

• Ограничение использования чисел за пределами указанного диапазона: например, можно указать максимальный процент годового роста зарплаты сотрудника (например, 3 %) или разрешить только целое число от 1 до 100.

• Ограничение дат, находящихся за пределами определенного промежутка времени: например, можно предотвратить выбор даты, которая предшествует текущей, при запросе отгула.
• org/ListItem»>

  Ограничение времени, которое находится за пределами определенного промежутка времени: например, вы можете указать, что собрания должны проводиться в период между 8:00 и 17:00.

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

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

Подсказки и сообщения об ошибках

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

Вы можете настроить сообщение на второй вкладке «Проверка данных».

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

Также можно выводить сообщение об ошибке при вводе пользователем неверных данных.

Существует три типа сообщений об ошибке.

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

Советы по использованию проверки данных

Обратите внимание на советы и рекомендации по использованию проверки данных в Excel.

Примечание: Если вы хотите использовать проверку данных в книгах в Службы Excel или Excel Web App, сначала потребуется создать ее в классическом приложении Excel.

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

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

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

• Можно применить поверку данных к ячейкам, в которые уже введены данные. Однако в приложении Excel отсутствует возможность автоматического уведомления о том, что в существующих ячейках имеются недопустимые данные. В этом случае можно выделить недопустимые данные на листе с помощью кругов. Когда недопустимые данные найдены, круги можно скрыть. При исправлении недопустимых данных они исчезают автоматически.

  Чтобы обвести данные, выделите нужные ячейки и выберите Данные > Работа с данными > Проверка данных > Обвести неверные данные.

• Чтобы быстро отменить проверку данных в ячейке, выделите ее и выберите Данные > Работа с данными > Проверка данных > Параметры

  > Очистить все.

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

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

• Если вы измените параметры проверки одной ячейки, вы можете автоматически применить изменения ко всем остальным ячейкам, имеющим такие же параметры. Для этого на вкладке Параметры установите флажок Распространить изменения на другие ячейки с тем же условием.

• Если проверка данных не работает, проверьте указанные ниже условия.

  • Пользователи не копируют и не вставляют данные. Проверка данных предназначена для отображения сообщений и предотвращения ввода недопустимых данных только в том случае, если пользователь вводит данные непосредственно в ячейку. Если данные копируются или вставляются, сообщения не отображаются. Чтобы предотвратить копирование и вставку данных путем перетаскивания ячеек, выберите Файл > Параметры > Дополнительно > Параметры правки и снимите флажок Разрешить маркеры заполнения и перетаскивание ячеек, а затем защитите лист.

  • org/ListItem»>

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

  • Отсутствуют ошибки в формулах. Убедитесь, что формулы в ячейках, для которых включена проверка, не являются причиной возникновения ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Пока ошибки не будут устранены, приложение Excel будет пропускать проверку данных.

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

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

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

  • org/ListItem»>

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

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

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

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

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

Руководство по проверке данных Excel

Содержание

1. Вступление
2. Контроль достоверности данных
3. Важное ограничение
4. Определение правил проверки данных
5. Параметры проверки данных
6. Простое выпадающее меню
7. Проверка данных с помощью пользовательской формулы
8. Формулы устранения неполадок
9. Примеры формул проверки данных
10. Проверка данных, чтобы обвести недействительные записи
11. Найти ячейки с проверкой данных
12. Скопировать данные проверки из одной ячейки в другую
13. Очистить все данные проверки

Вступление

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

• значение является числом от 1 до 6
• дата произойдет в следующие 30 дней
• текстовая запись содержит менее 25 символов

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

Сообщение отображается автоматически при выборе ячейки

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

Пример сообщения об ошибке

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

Пример раскрывающегося меню проверки данных

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

Контроль достоверности данных

Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» на ленте.

Элементы управления проверкой данных на вкладке ДАННЫЕ

Важное ограничение

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

Определение правил проверки данных

Проверка данных определяется в окне с 3 вкладками: Параметры, Сообщение для ввода и Сообщение об ошибке:

Окно проверки данных имеет три основные вкладки

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

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

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

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

Вкладка «Сообщение об ошибке» определяет, как выполняется проверка. Например, когда вид установлен на «Останов», неверные данные вызывают окно с сообщением, и ввод не разрешен.

Вкладка предупреждения об ошибке проверки данных

Пользователь видит сообщение, подобное этому:

Пример сообщения об ошибке проверки данных

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

Вид: Останов

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

Вид: Предупреждение

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

Вид: Сообщение

Сообщает пользователям, что данные являются недействительными. Это сообщение не делает ничего, чтобы остановить ввод неверных данных. Информационное окно имеет 3 кнопки: «ОК», чтобы принять недействительные данные, «Отмена», чтобы удалить их и «Справка».

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

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

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

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

Действительное — работает как опция целого числа, но допускает десятичные значения. Например, если для параметра «Действительное» задано значение от 0 до 3, допустимы все значения, такие как 0,5 и 2,5.

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

Дата — разрешены только даты. Например, вам может потребоваться дата между 1 января 2018 года и 31 декабря 2021 года или дата после 1 июня 2018 года.

Время — разрешено только время. Например, вы можете указать время между 9:00 и 17:00 или разрешить время только после 12:00.

Длина текста — проверяет ввод на основе количества символов или цифр. Например, вам может потребоваться код из 5 цифр.

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

На вкладке параметров также есть два флажка:

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

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

Простое выпадающее меню

Вы можете предоставить пользователю раскрывающееся меню опций, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить записи действиями «ПРИНЯТ», «В ОБРАБОТКЕ» или «ОТГРУЖЕН», вы можете ввести эти значения через точку с запятой:

Раскрывающееся меню проверки данных с жестко заданными значениями

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

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

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

Значения выпадающего меню проверки
данных со ссылкой на диапазон

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

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

Вы также можете использовать именованные диапазоны для указания значений. Например, с именованным диапазоном под названием «размер» для F4:F6, вы можете ввести имя непосредственно в окне, начиная со знака равенства:

Значения выпадающего меню проверки
данных с именованным диапазоном

Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

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

Проверка данных с помощью пользовательской формулы

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

= ЕЧИСЛО (А1)

Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

Чтобы включить проверку данных с помощью формулы, выберите «Другой» на вкладке «Параметры», затем введите формулу, начиная со знака равенства (=), как обычно.

Формулы устранения неполадок

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

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

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

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

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

Чтобы разрешить только 5 символьных значений, начинающихся с «z», вы можете использовать:

= И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

Чтобы разрешить ввод даты в течение 30 дней с сегодняшнего дня:

= И (А1> СЕГОДНЯ (), А1 <= (СЕГОДНЯ () + 30))

Чтобы разрешить только уникальные значения:

= СЧЁТЕСЛИ (диапазон, А1) <2

Разрешить только адрес электронной почты

= ЕЧИСЛО (НАЙТИ ( «@», A1)

Проверка данных, чтобы обвести недействительные записи

После проверки данных можно попросить Excel обвести ранее введенные недействительные значения. На вкладке «Данные» на ленте нажмите «Проверка данных» и выберите «Обвести неверные данные»:

Обведите недействительные значения с проверкой данных

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

= И (ЛЕВСИМВ (А1) = «z», ДЛСТР (A1) = 5)

Недопустимые значения для проверки данных обведены на листе

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

Чтобы найти ячейки с примененной проверкой данных, используйте диалоговое окно Переход — Выделить. Введите сочетание клавиш Ctrl + G, затем нажмите кнопку «Выделить». Когда появится окно, выберите «Проверка данных»:

Переход к кнопке Выделить Выберите проверку данных

Скопировать данные проверки из одной ячейки в другую

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

Использование специальной вставки
для копирования проверки данных

Примечание: вы можете использовать сочетание клавиш Ctrl + Alt + V, чтобы вызвать Специальную вставку без мыши.

Очистить все данные проверки

Чтобы очистить все данные проверки из диапазона ячеек:

• выделите ячейки
• нажмите кнопку «Проверка данных» на вкладке «Данные»
• нажмите кнопку «Очистить все»

Очистить данные проверки

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

Проверка данных Excel: как проверить формат данных в Excel

По советам и рекомендациям по Excel от Pryor.com Категории: Excel® Теги: проверка данных

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

Укажите формат данных

1. Выберите ячейку или ячейки, которые вы хотите проверить во время ввода.
2. На вкладке Data в группе Data Tools щелкните Data Validation , чтобы открыть диалоговое окно Data Validation .
3. На вкладке Настройки укажите критерии, которым должны соответствовать введенные данные:
   • Выберите тип данных Время в раскрывающемся меню Разрешить .
   • Выберите между из раскрывающегося меню Данные: .
   • Задайте диапазон допустимого времени начала и окончания .
4. На вкладке Входное сообщение вы можете отобразить всплывающую подсказку с инструкциями для пользователя, прежде чем он наберет .
   • Установите флажок Показывать входное сообщение при выборе ячейки , чтобы включить подсказки.
5. В предупреждении об ошибке укажите, что произойдет, если данные не соответствуют вашим критериям:
   • Установите флажок Показывать оповещение об ошибке после ввода неверных данных , чтобы включить оповещения.
   • Выберите тип предупреждения:
     • Стоп — Запрещает Excel принимать неверные данные и позволяет пользователю только Повторить попытку или Отменить .
     • Предупреждение — пользователь предупрежден о том, что данные недействительны, но он может нажать Да , чтобы ввести данные в любом случае, Нет для редактирования записи или Отмена для удаления записи.
     • Информация — Пользователь предупреждается о том, что данные недействительны, но не запрещается их ввод. Пользователь может нажать OK , чтобы все равно ввести данные, или Cancel , чтобы удалить их.

6. Щелкните OK , чтобы применить проверку данных.

Удалить проверку данных

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

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

PRYOR+ 7 ДНЕЙ БЕСПЛАТНОГО ОБУЧЕНИЯ

---

Курсы по обслуживанию клиентов, Excel, управлению персоналом, лидерству, ОСАГО и многое другое. Нет кредитной карты. Без комментариев. Индивиды и команды.

НАЧАТЬ ПРОБНЫЙ ПЕРИОД

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

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

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

• Создать пользовательское правило проверки на основе формулы
• Проверка данных Excel для разрешения только чисел
• Проверка данных Excel для разрешения только текста
• Разрешить текст, начинающийся с определенных символов
• Разрешить записи, содержащие определенный текст
• Разрешить только уникальные записи и запретить дубликаты
• Формулы проверки даты и времени
• Пользовательская проверка данных Excel не работает

Как создать пользовательскую проверку данных с помощью формулы

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

1. Выберите одну или несколько ячеек для проверки.
2. Откройте диалоговое окно проверки данных. Для этого нажмите кнопку Data Validation на вкладке Data в группе Data Tools или нажмите последовательность клавиш Alt > D > L (каждая клавиша нажимается отдельно).
3. На вкладке Настройки диалогового окна Проверка данных выберите Пользовательский в поле Разрешить и введите формулу проверки данных в поле Формула .
4. Нажмите OK .

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

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

Примечание. Все правила проверки данных Excel, встроенные и настраиваемые, проверяют только новые данные, введенные в ячейку после создания правила. Скопированные данные не проверяются, равно как и ввод данных в ячейку перед созданием правила. Чтобы закрепить существующие записи, которые не соответствуют вашим критериям проверки данных, используйте функцию Circle Invalid Data , как показано в разделе Как найти недопустимые данные в Excel.

Проверка данных Excel для разрешения только чисел

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

=ISNUMBER(C2)

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

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

Проверка данных Excel, чтобы разрешить только текст

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

=ISTEXT(D2)

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

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

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

СЧЁТЕСЛИ( ячейка ,» текст *»)

Например, чтобы убедиться, что все идентификаторы заказов в столбце A начинаются с префикса «AA-«, «aa-«, «Aa-» или «aA-» (случай -нечувствительный), определите пользовательское правило с этой формулой проверки данных:

=СЧЁТЕСЛИ(A2,"aa-*")

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

Правило проверки данных работает с логикой ИЛИ:

=СЧЁТЕСЛИ(A2,"aa-*")+СЧЁТЕСЛИ(A2,"bb-*")

Формула проверки с учетом регистра

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

EXACT(LEFT( ячейка , number_of_chars ), текст )

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

=EXACT(LEFT(A2,3),"AA-")

В приведенной выше формуле функция LEFT извлекает первые 3 символа из ячейки A2, а EXACT выполняет регистр- чувствительное сравнение с жестко запрограммированной подстрокой (в данном примере «AA-«). Если две подстроки точно совпадают, формула возвращает TRUE и проверка проходит успешно; в противном случае возвращается FALSE и проверка завершается неудачно.

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

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

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

  ISNUMBER(ПОИСК( текст , ячейка ))

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

  ISNUMBER(FIND( text , сотовый ))

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

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

=ЧИСЛО(НАЙТИ("AA", A2))

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

Вы ищете подстроку «AA» в ячейке A2, используя ПОИСК или ПОИСК , и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Для любого числового значения, возвращаемого в результате поиска, функция ISNUMBER возвращает TRUE, и проверка данных проходит успешно. В случае ошибки ISNUMBER возвращает FALSE, и запись не будет разрешена в ячейке.

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

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

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

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

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

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

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

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

Формулы проверки для даты и времени

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

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

Разрешить даты между двумя датами

Чтобы ограничить ввод датой в указанном диапазоне, вы можете использовать либо предопределенное правило даты с критерием «между», либо создать пользовательское правило проверки с помощью этой общей формулы:

И( ячейка >= start_date ), ячейка <= end_date )

Где:

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

Например, чтобы разрешить только даты в июле 2017 года, используйте следующую формулу:

=И(C2>=ДАТА(2017,7,1),C2<=ДАТА(2017,7 ,31))

Или введите дату начала и дату окончания в некоторые ячейки (F1 и F2 в этом примере) и укажите эти ячейки в формуле:

=AND(C2>=$F$1, C2 <=$F$2)

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

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

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

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

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

В буд ( Cell , 2) <6

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

Weekday ( Cell , 2)> 5

. в ячейках C2:C6 используйте следующую формулу:

=ДЕНЬНЕД(C2,2)<6

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

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

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

1. Выберите Дата в списке Разрешить
2. Выберите между в Данные
3. В поле Дата начала введите =СЕГОДНЯ()
4. В поле Дата окончания введите = СЕГОДНЯ() + 6

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

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

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

1. В поле Разрешить выберите Время .
2. В поле Данные выберите либо меньше , чтобы разрешить только время до текущего времени, либо больше , чтобы разрешить время после текущего времени.
3. В поле Время окончания или Время начала (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:
   • Для проверки даты и времени на основе текущей даты и времени:
     =СЕЙЧАС()
   • Для проверки раз на основе текущего времени:
     =ВРЕМЯ( ЧАС(СЕЙЧАС()), МИНУТА(СЕЙЧАС()), СЕКУНДА(СЕЙЧАС()))

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

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

Если правило проверки данных на основе формулы не работает должным образом, есть 3 основных момента, которые следует чек:

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

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

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

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

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

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

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

Вот пример в простейшей форме:

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

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

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

Предположим, вы хотите ограничить ввод данных в ячейки с D2 по D5 целыми числами от 1 (минимальное значение) до результата деления A2 на B2. Итак, вы вычисляете максимальное значение с помощью этой простой формулы =A2/B2 , как показано на снимке экрана ниже:

Проблема в том, что эта, казалось бы, правильная формула не будет работать для ячеек с D3 по D5, потому что относительные ссылки изменяются в зависимости от относительное расположение строк и столбцов. Таким образом, для ячейки D3 формула изменится на =A3/B3 , а для D4 он станет =A4/B4 , что делает неправильную проверку данных!

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы заблокировать их: =$A$2/$B$2 .