Ограничения проверки данных в Excel

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

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

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

Аналогично, чтобы проверить время, выберите «Время» в поле «Значение» и задайте нужные критерии. Например, для разрешения только дат между датой в ячейке B1 и датой в ячейке B2, используйте следующее правило проверки:

Проверка будних и выходных дней

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

Формулы для разрешения ввода только в будни или только в выходные дни выглядят так:

Для будних дней:

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

Для выходных:

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

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

Иногда нужно использовать текущую дату как начальную для проверки. Для этого используйте функцию СЕГОДНЯ (TODAY), добавив к ней нужное количество дней для вычисления даты окончания периода. Пример:

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

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

Для проверки времени используйте предопределенное правило с формулой:

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

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

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

Чтобы ограничить ввод данных определенной длиной, выберите «Длина текста» в поле «Разрешить» и задайте критерии проверки.

Пример для ограничения ввода до 15 символов:

Или вы можете использовать такую формулу:

=ДЛСТР(ячейка)<=15

Проверка чисел

Для разрешения ввода в ячейку только чисел, используйте формулу:

=ЕЧИСЛО(C2)

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

Проверка текста

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

=ЕТЕКСТ(B2)

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

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

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

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

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

Ограничения проверки данных с логикой ИЛИ

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

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

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

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

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

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

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

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

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

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

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

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

=ЕЧИСЛО(ПОИСК("АР";A2))

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

Проверка на уникальность

Для разрешения ввода только уникальных значений используйте формулу СЧЁТЕСЛИ:

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

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

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

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

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

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

=И(ячейка>=100;ячейка<=999)

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

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

=СЕГОДНЯ()

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

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

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

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

=МИН($C$2:$C$10)
=МАКС($C$2:$C$10)

Примечание:

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

Надеюсь, это сделает ваши ограничения проверки данных Excel более точными и эффективными для работы!