Связанные выпадающие списки в Excel

Сегодня хочу рассказать о техническом приеме в Excel под названием Связанные выпадающие списки.

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

Представьте, что у вас есть каталог товаров с разделением по категориям. Чтобы выбрать товар из нужной категории вам приходится вручную искать товар в длинном списке. Но можно сделать так, что выбор категории будет определять список доступных товаров. Это именно то, что делают Связанные выпадающие списки.

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

Многие используют обычные выпадающие списки (Data Validation), не связывая их между собой. Это часто приводит к ошибкам выбора и увеличению времени на поиск нужных значений. Умные Связанные списки решают эту задачу с помощью динамического диапазона и формул — обычно через функции СМЕЩ (OFFSET) и ДВССЫЛ (INDIRECT).

Как создать Связанные выпадающие списки

1. Создаем таблицу с категориями и подкатегориями.
2. Первый выпадающий список с категориями создаем привычным способом. См. Как создать выпадающий список в Excel
3. Второму списку назначаем источник, который зависит от выбора первого, например =ДВССЫЛ(A1), где A1 — ячейка с выбором категории.
4. Важно, чтобы диапазоны для подкатегорий были названы точно по названиям категорий.
5. Профессиональный лайфхак. Если хотите, чтобы всё выглядело красиво, оформите всё это на отдельном листе и спрячьте его.

На первый взгляд сложновато, но в результате получаем важные преимущества:

• Автоматическая подстановка только актуальных вариантов.
• Удобство и простота выбора для пользователя.
• Меньше ошибок и неполных или дублирующихся данных.
• Возможность расширять и модифицировать списки без глобальной перестройки.

Особенно полезно для таблиц с большим объемом данных, где многоуровневый выбор критичен — финансовые отчеты, инвентаризации, клиентские базы.