Проверка данных (Data Validation) с формулами

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Назад: Проверка данных (Data validation)

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

Файл с примерами: 3 Проверка данных с формулами.xlsx

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

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

Данные → Проверка данных → Тип данных: Другой → Формула (Data → Data Validation → Allow: Custom → Formula).

Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение — зависит от настроек в разделе «Сообщение об ошибке» (Error Alert).

Рассмотрим несколько примеров проверки с формулами.

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

Длину текста можно определить с помощью функции ДЛСТР / LEN. Она возвращает число — количество символов в ячейке. Соответственно, если мы хотим запретить ввод текста длиннее, допустим, десяти символов, то формула будет выглядеть следующим образом:

=ДЛСТР(ссылка на первую ячейку диапазона) <= 10

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

Функция ЕФОРМУЛА / ISFORMULA возвращает ИСТИНА, если ее аргумент — ячейка с формулой. Соответственно, следующая формула в проверке данных будет разрешать ввод только формул.

=ЕФОРМУЛА(ссылка на первую ячейку диапазона)

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

Уникальные значения — это значения, которые в диапазоне встречаются всего лишь один раз. Подсчитать количество можно с помощью СЧЁТЕСЛИ / COUNTIF:

=СЧЁТЕСЛИ(диапазон; критерий)

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

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)

И сравнивать это с единицей: если значение будет встречаться более одного раза, его вводить уже нельзя.

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)<=1

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

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

Назад: Проверка данных (Data validation)

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