Создание раскрывающихся списков

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

Это самая удобная форма проверки данных:

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

Внутри раскрывающийся список представляет собой обычное правило проверки данных типа Список.

Самый простой раскрывающийся список использует значения, введённые непосредственно в правило. В диалоговом окне «Проверка данных» Excel выберите Разрешить: Список, а в поле Источник введите:

Yes,No,Maybe

Разделяйте элементы запятыми. В Google Таблицах выберите Раскрывающийся список и введите каждый вариант отдельно.

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

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

=$F$2:$F$6

Теперь изменение ячеек в столбце F сразу обновляет раскрывающийся список.

• Используйте абсолютные ссылки, чтобы источник оставался неизменным.
• При желании разместите ячейки с вариантами на аккуратно оформленном листе для справочных данных.

Именно здесь именованные диапазоны и проверка данных особенно хорошо работают вместе. Присвойте диапазону вариантов имя RegionList, а затем задайте для источника проверки данных просто:

=RegionList

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

• Одинаково работает в Excel и Google Таблицах.
• Имя и раскрывающийся список остаются синхронизированными.

Представьте таблицу для отслеживания задач. На вспомогательном листе перечислите статусы в диапазоне A1:A4: Открыта, В работе, Заблокирована, Завершена. Присвойте этому диапазону имя StatusList.

Выберите столбец статуса, откройте «Проверка данных», выберите Список и задайте для источника =StatusList.

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

Если добавить новый вариант в исходный список, фиксированный диапазон вроде =$F$2:$F$6 его не включит. Есть два способа сделать так, чтобы список расширялся:

• Преобразуйте источник в Excel таблицу и присвойте столбцу имя — таблицы расширяются автоматически.
• Или задайте динамический именованный диапазон с помощью оператора развертывания, например =A2# в новых версиях Excel.

В Google Таблицах указание в качестве источника целого столбца, например F2:F, включает будущие добавления.

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

Классический приём в Excel заключается в том, чтобы назвать каждый вложенный список в соответствии с категорией, а затем использовать INDIRECT в источнике:

=INDIRECT(A2)

Если в A2 находится France, а именованный диапазон France содержит список её городов, раскрывающийся список адаптируется. Это сложный, но мощный приём.

По умолчанию правило «Список» всё равно позволяет пользователям вручную вводить значения. Вы можете управлять этим:

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

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

Маленькая стрелка появляется только при выборе ячейки и только если установлен флажок Раскрывающийся список в ячейке (Excel) или включён стиль раскрывающегося списка (Google Таблицы).

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

Этот параметр управляет только отображением; само правило разрешённых значений в обоих случаях остаётся одинаковым.

Поскольку раскрывающийся список использует источник =RegionList, поддерживать его легко:

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

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

Раскрывающиеся списки становятся ещё полезнее в сочетании с формулами поиска. Выберите регион в раскрывающемся списке в ячейке A2, а затем найдите его объём продаж:

=XLOOKUP(A2,RegionList,SalesList)

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

• Раскрывающийся список управляет вводом.
• Функция поиска реагирует на выбранный вариант.

Это сочетание лежит в основе интерактивных отчётов на основе формул.

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

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

• Используйте правило проверки данных Список с элементами, введёнными вручную, диапазоном ячеек или именованным диапазоном.
• Задавайте источник раскрывающегося списка как =RegionList, чтобы формула была понятной, а обслуживание — простым.
• Расширяйте списки с помощью таблиц или источников в виде целых столбцов.
• Используйте оповещения об ошибках Остановить, чтобы разрешить только значения из списка.

Вы завершили тему «Именованные диапазоны и проверка данных» — теперь Ваши формулы понятнее, а ввод контролируется и надёжно защищён.