Еще один вариант — ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / 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, то формула будет выглядеть так:

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

Сортировка данных

Сортировка данных — упорядочивание по значениям в одном или нескольких столбцах. Без этой операции никуда: а как иначе посмотреть топ продаж по какому-либо параметру (городам, товарам и т. д.)? Это помогает упорядочить таблицу, анализировать данные становится проще.

Быстрее всего отсортировать данные по возрастанию или убыванию можно на вкладке «Данные».

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

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

Кроме того, сортировать можно с помощью фильтра (о нем ниже).

А если простой сортировки по одному столбцу недостаточно, открывайте окно «Сортировка» (одноименная кнопка на ленте).

Здесь есть возможность сортировать по нескольким столбцам. Допустим, мы хотим упорядочить таблицу по клиентам, а в рамках каждого клиента — по сумме, чтобы самые крупные сделки были сверху. Тогда мы добавляем два соответствующих уровня сортировки в этом окне — сначала компанию, а затем, нажав кнопку «Добавить уровень» (Add Level), — сумму.

Результат:

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

Сортировка с помощью пользовательских списков

Не всегда нужно сортировать данные по алфавиту.

Возможно, вы уже обращали внимание, что месяцы или дни недели сортируются не по алфавиту, а в правильном (хронологическом) порядке. Это происходит потому, что в Excel встроены соответствующие списки для сортировки — с короткими и длинными названиями месяцев и дней недели. Мы не можем их удалить или изменить, но можем добавлять новые!

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

Списки добавляются и удаляются в параметрах Excel → Дополнительно → Общие (раздел в самом конце вкладки «Дополнительно») → Изменить списки (Options → Advanced → General → Edit Custom Lists).

В окне со списками есть списки дней недели и месяцев — их редактировать и удалять нельзя. Но можно добавлять свои.

После добавления сортировка в диапазонах, таблицах и сводных таблицах может осуществляться по такому порядку. Кроме того, список можно создать непосредственно в процессе сортировки. В любом случае нужно выбрать «Настраиваемый список…» (Custom List…) в окне сортировки.

И далее в открывшемся окне выбрать список (или добавить новый).

Еще один плюс пользовательских списков: они работают при протягивании.

В Google Таблицах пользовательских списков нет.

Фильтрация данных

Фильтрация данных — еще одна базовая операция, без которой никуда в Excel или Google Таблицах. Фильтрация — отбор и отображение строк по заданным критериям (текстовым, числовым или даже по цвету ячейки/шрифта).

Фильтр можно установить и снять сочетанием клавиш Ctrl + Shift + L или на ленте инструментов на вкладке «Данные» (Data → Filter).

Кроме того,