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

В Google Таблицах и в Excel только с 2023 года в рамках подписки Microsoft 365 (опция появилась в начале 2022-го, но ее долго не было у обычных пользователей) в проверке данных можно искать значение, то есть после ввода части текста список фильтруется до тех значений, которые соответствуют введенному фрагменту. Это очень удобно, когда значений в исходном списке тысячи, например это названия товаров.

ОБНОВЛЯЕМЫЙ ВЫПАДАЮЩИЙ СПИСОК

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

Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Если данные на том же листе — превращаем справочник в таблицу (Ctrl + T или Ctrl + L, подробнее смотрите главу про таблицы) и ссылаемся на него.

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

Выделяем столбец в таблице на другом листе и присваиваем ему имя (можно ввести его в поле «Имя» слева от строки формул и нажать Enter).

После этого в проверке данных остается нажать F3 и выбрать в появившемся окне «Вставка имени» созданное имя.

Еще один вариант — ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / INDIRECT. Эта функция принимает текст в качестве аргумента и превращает его в ссылку, то есть это такой непрямой способ ссылаться на диапазоны, и она в данном случае помогает обойти ограничение проверки данных.

В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.

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

^{Выпадающие списки в Excel с автоматическим добавлением новых значений}

^{https://mif.to/VRU7P}

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

Для понимания этого раздела стоит разобраться с логическими значениями и формулами. Если вы еще не знакомы с этой темой, обратитесь к главе «Логические выражения и функция ЕСЛИ / IF».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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