Office
Ячейки

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

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

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Скачивание примеров

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

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

  2. Выберите данные >проверка данных.

    Проверка данных

  3. На вкладке Параметры в списке Тип данных выберите подходящий вариант:

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

    • " Дес .", чтобы ограничить ячейки как допустимые только десятичными числами.

    • Список, чтобы данные выбирались из раскрывающегося списка.

    • Дата, чтобы можно было ввести только дату.

    • Time (время ): Ограничьте ячейки, чтобы она принимала только время.

    • Длина текста, чтобы ограничить длину текста.

    • Другой, чтобы задать настраиваемую формулу.

  4.  В списке Значение выберите условие:

    • между

    • вне

    • равно

    • не равно

    • больше

    • меньше

    • больше или равно

    • меньше или равно

  5. На вкладке Параметры в списке Тип данных выберите подходящий вариант:

  6. Задайте остальные обязательные значения с учетом параметров Тип данных и Значение. Например, если выбрать значение между, выберите минимум: и максимум: значения в ячейках (ах).

  7. Установите флажок Игнорировать пустые ячейки, если нужно пропускать пустые ячейки.

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

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

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

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

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

Ограничение ввода данных

  1. Выделите ячейки, для которых нужно ограничить ввод данных.

  2. На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

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

Запрос на ввод допустимых записей

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

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

  2. На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

  4. В поле название введите название сообщения.

  5. В поле введите сообщение, которое вы хотите отобразить.

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

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

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

  2. На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

  3. На вкладке сообщение об ошибке в поле название введите название сообщения.

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

  5. Выполните одно из следующих действий:

    Задача

    На вкладке Style (стиль ) всплывающее меню выберите

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

    Остановить

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

    Предупреждение.

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

    Важно!

Ограничение ввода данных

  1. Выделите ячейки, для которых нужно ограничить ввод данных.

  2. На вкладке данные в разделе Сервиснажмите кнопку проверить.

    Вкладка "Данные", группа "Сервис"

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

  3. Во всплывающем меню Разрешить выберите тип данных, которые вы хотите разрешить.

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

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

Запрос на ввод допустимых записей

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

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

  2. На вкладке данные в разделе Сервиснажмите кнопку проверить.

    Вкладка "Данные", группа "Сервис"

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

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

  4. В поле название введите название сообщения.

  5. В поле введите сообщение, которое вы хотите отобразить.

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

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

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

  2. На вкладке данные в разделе Сервиснажмите кнопку проверить.

    Вкладка "Данные", группа "Сервис"

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

  3. На вкладке сообщение об ошибке в поле название введите название сообщения.

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

  5. Выполните одно из следующих действий:

    Задача

    На вкладке Style (стиль ) всплывающее меню выберите

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

    Остановить

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

    Предупреждение.

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

    Важно!

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

Примечание: Первые два шага, описанные в этом разделе, предназначены для добавления проверки данных любого типа. Шаги 3-7 предназначены специально для создания раскрывающегося списка. 

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

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

  3. На вкладке Параметры в разделе Разрешить нажмите кнопку Список.

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

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

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

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

Примечания: 

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

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

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

Чтобы:

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

  1. Выполните действия, описанные в 1-2 выше.

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

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

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

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

Примечания: 

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

  • Снимки экрана в этой статье взяты Excel 2016; но функциональность одинакова в Excel в Интернете.

Чтобы

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

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

= И (LEFT (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))

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

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

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

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

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

×