Применение проверки данных к ячейкам

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

Скачивание образцов

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

Скачать примеры проверки данных Excel

Добавление проверки данных в ячейку или диапазон ячеек

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

  1. Выделите одну или несколько ячеек, к которым нужно применить проверку.

  2. На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

    Функции проверки данных находятся на вкладке "Данные" в группе "Работа с данными"
  3. На вкладке Параметры в разделе Разрешить нажмите кнопку Список.

    Вкладка "Параметры" в диалоговом окне "Проверка данных"
  4. В поле Источник введите значения, разделенные точкой с запятой. Например:

    1. Для ограничения ответа на вопрос (например, "Есть ли у вас дети?") двумя вариантами введите Да;Нет.

    2. Для ограничения рейтинга качества производителя тремя позициями введите Низкое;Среднее;Высокое.

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

      Рекомендация: можно также создать список значений с помощью ссылки на диапазон ячеек в любой части книги. Удобнее всего создать список, а затем отформатировать его как таблицу Excel (на вкладке Главная щелкните Стили > Форматировать как таблицу и выберите нужный стиль таблицы). Затем выберите диапазон данных таблицы, то есть часть таблицы, содержащую сам список без заголовка (в данном случае — "Отдел"), и присвойте ему понятное имя в поле "Имя" над столбцом A.

      В поле "Имя" введите понятное название списка.

    Теперь в поле Источник проверки данных вместо значений введите знак равенства (=) и имя, которое вы только что задали.

    Добавьте знак = перед именем таблицы

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

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

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

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

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

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

Примечания : 

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

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

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

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

Чтобы:

Сделайте следующее:

Разрешить вводить только целые числа из определенного диапазона

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше.

  2. В списке Разрешить выберите значение Целое число.

  3. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

  4. Введите минимальное, максимальное или точное значение.

    Диалоговое окно условий проверки

    Вы также можете ввести формулу, которая возвращает числовое значение.

    Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

Разрешить вводить только десятичные числа из определенного диапазона

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше.

  2. В поле Разрешить выберите значение Десятичный.

  3. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

  4. Введите минимальное, максимальное или точное значение.

    Вы также можете ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6 % от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.

    Примечание : Чтобы пользователи могли вводить проценты, например "20 %", в поле Разрешить выберите значение Десятичное число, в поле Данные задайте необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Процентный формат Изображение кнопки на вкладке Главная в группе Число.

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

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше.

  2. В поле Разрешить выберите значение Дата.

  3. В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.

  4. Введите дату начала, окончания или определенную дату.

    Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.

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

Разрешить вводить только время в заданном интервале

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше.

  2. В поле Разрешить выберите значение Время.

  3. В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.

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

    Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.

    Параметры проверки, ограничивающие значения времени заданным интервалом

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

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше.

  2. В поле Разрешить выберите значение Длина текста.

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

  4. В данном случае нам нужно ограничить длину вводимого текста 25 символами, поэтому выберем меньше или равно в поле Данные и введем 25 в поле Максимальное значение.

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

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

  1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше. В поле Разрешить выберите нужный тип данных.

  2. В поле Данные выберите необходимый тип ограничения.

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

    Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >= =E1.

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

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

Чтобы

Введите формулу

Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.

= И(ЛЕВСИМВ(C2;3)="ID-";ДЛСТР(C2)>9)

Пример 6. Формулы для проверки данных

Ячейка с наименованием продукта (D2) содержала только текст.

=ЕТЕКСТ(D2)

Пример 2. Формулы для проверки данных

Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)

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

Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.

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

Пример 4. Формулы для проверки данных

Примечание : Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

Дополнительные сведения

Адрес электронной почты в ячейке B4 содержал символ @.

=ЕЧИСЛО(НАЙТИ("@";B4)

Пример проверки данных для контроля наличия символа @ в адресе электронной почты

  • Почему команда "Проверка данных" неактивна на ленте? У этой проблемы может быть несколько причин.

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

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

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

  • Можно ли изменить размер шрифта? Нет, размер шрифта фиксирован. Единственный способ изменить размер — настроить масштаб с помощью значка в правом нижнем углу окна Excel. Однако можно использовать поле со списком ActiveX. См. статью Добавление на лист списка или поля со списком.

  • Можно ли выполнять автозаполнение или автовыбор с помощью проверки данных? Нет, но для этого можно использовать поле со списком ActiveX.

  • Можно ли выбрать несколько значений в списке проверки данных? Нет, если вы не используете поле со списком ActiveX или список.

  • Можно ли при выборе элемента из списка проверки данных заполнять другой список? Да! Это называется проверкой зависимых данных. Дополнительные сведения см. в статье Создание зависимых раскрывающихся списков.

  • Как удалить все проверки данных на листе? Вы можете использовать диалоговое окно Выделитьгруппу ячеек. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (или нажмите F5 или CTRL+G на клавиатуре), а затем выберите Выделить группу ячеек > проверка данных и вариант всех (чтобы найти все ячейки с проверкой данных) или этих же (чтобы найти ячейки с определенными параметрами проверки).

    Диалоговое окно "Специальная вставка"

    Затем откройте диалоговое окно проверки данных (вкладка Данные > Проверка данных), нажмите кнопку Очистить все, а затем нажмите кнопку ОК.

  • Можно ли заставить пользователей вводить значения в ячейки с помощью проверки данных? Нет, но вы можете с помощью VBA (Visual Basic для приложений) проверять, внесено ли значение, при выполнении определенных условий, например при сохранении и закрытии книги. Если пользователь не выбрал значение, вы можете отменить событие и не позволять ему продолжить, пока значение не будет выбрано.

  • Как изменять цвет ячеек в соответствии со значением, выбранным в списке проверки данных? Вы можете использовать условное форматирование с параметром Форматировать только ячейки, которые содержат.

    Параметр "Форматировать только ячейки, которые содержат"
  • Как проверить адрес электронной почты? Выберите Выделить группу ячеек > формулы и введите формулу для проверки наличия символа @ в записи: =ЕЧИСЛО(НАЙТИ("@";D2)). Функция НАЙТИ ищет символ @ и, если он найден, возвращает его позицию в текстовой строке, что показывает, что запись допустима. Если символ не найден, НАЙТИ возвращает сообщение об ошибке, а запись не вводится.

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

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

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

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

Дополнительные сведения

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

Видео: создание раскрывающихся списков и управление ими

Добавление и удаление элементов раскрывающегося списка

Удаление раскрывающегося списка

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×