Управление встречами, планами, бюджеты — это просто с помощью Microsoft 365.

Ячейки

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

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

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

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

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

Скачайте образец книги со всеми

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

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

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

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

    • Целое число, чтобы ячейка не вмелась только целыми числами.

    • Decimal (Десятичной), чтобы в ячейке можно было принимать только десятичных чисел.

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

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

    • Время — ограничение ячейки только временем.

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

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

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

    • между

    • вне

    • равно

    • не равно

    • больше

    • меньше

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

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

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

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

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

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

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

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

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

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

  • Какую версию вы используете?
  • Более новые версии
  • Office 2011

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

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

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

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

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

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

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

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

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

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

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

  3. На вкладке "Сообщение для ввода" выберите "Показывать сообщение при выборе ячейки".

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

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

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

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

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

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

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

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

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

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

    Задача

    Во всплывающее меню "Стиль" выберите пункт

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

    Остановка

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

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

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

    Важно

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

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

  2. На вкладке "Данные" в области "Инструменты"нажмите кнопку "Проверить".

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

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

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

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

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

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

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

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

  2. На вкладке "Данные" в области "Инструменты"нажмите кнопку "Проверить".

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

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

  3. На вкладке "Сообщение для ввода" выберите "Показывать сообщение при выборе ячейки".

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

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

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

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

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

  2. На вкладке "Данные" в области "Инструменты"нажмите кнопку "Проверить".

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

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

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

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

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

    Задача

    Во всплывающее меню "Стиль" выберите пункт

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

    Остановка

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

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

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

    Важно

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

Примечание: Первые два действия, которые находятся в этом разделе, — это добавление любого типа проверки данных. Шаги 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% в поле Максимальное значение.

    Примечание: Чтобы разрешить пользователю вводить процентные значения (например, 0,2), выберите значение "Действительное" в поле "Разрешить", выберите нужный тип ограничения в поле "Данные", введите минимальное, максимальное или определенное значение, например 0,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) знаков.

=И(ЛЕВШ(C2;3)="ИД-";ДЛДЛ(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.

×