Как создать зависимый выпадающий список в Excel?
Одна из наиболее полезных функций проверки данных — это возможность создавать раскрывающийся список, который позволяет вам выбирать значение из предварительно определенного списка. Но как только вы начнете применять его в своих таблицах, вы неизбежно столкнетесь с проблемой: вам придется создать раскрывающийся список в Excel, в зависимости от значения в другом. Другими словами, как создать многоуровневый динамически связанный список?
Вот несколько примеров таких задач:
- список городов по выбранной стране.
- товары конкретного производителя.
- наименования продуктов, входящих в группу продуктов.
- выбрать модель автомобиля по марке.
Это могло бы выглядеть так:
Такие списки называются зависимыми или связанными, поскольку их содержание зависит друг от друга. Такие сложные конструкции можно создавать несколькими способами. Начнем с более простого и стандартного подхода.
1. Именованные диапазоны + функция ДВССЫЛ.
Это может показаться сложным, но на самом деле это очень просто и является отличным примером того, как можно применить INDIRECT.
Давайте посмотрим на небольшой пример. У нас есть список автомобилей различных марок. Разместим их каждую в отдельный столбец. В первой ячейке каждого столбца указываем производителя: Toyota, Ford, Nissan. Это необходимо, чтобы, изначально выбрав, например, Тойоту, мы видели только модели этой марки и не более того. То есть нам нужен двухуровневый связанный список.
Сначала создадим именованные диапазоны с моделями автомобилей. Назовем каждого из них по марке автомобиля. Важно, чтобы название каждого из них в точности совпадало со значением, записанным в первой строке соответствующего столбца. Другими словами, если мы создаем именованный диапазон из ячеек A2: A100, его имя должно соответствовать значению в A1 (вероятность не имеет значения). Взгляните на изображение, чтобы увидеть, как оно выглядит.
Итак, мы получили 3 названных линейки: «Тойота», «Форд», «Ниссан». Сделать их статическими (фиксированными) или динамическими (сбрасываются автоматически) — решаете сами. Для получения информации о том, как создать список автозаполнения, перейдите по ссылке в конце этой статьи.
Затем в ячейке F3 создайте раскрывающийся список верхнего уровня с моделями автомобилей, как показано в нашем примере на рисунке. Источником данных являются первые ячейки каждого столбца. Обратите внимание, что инструмент проверки ввода, который мы используем для его создания, отлично работает как с вертикальной (строка), так и с горизонтальной (столбец).
А потом выбираем того производителя, который нас интересует. Например, «Форд».
Затем в ячейке F6 с помощью зависимого раскрывающегося списка второго уровня выбираем уже определенную модель этой марки. Список, который мы увидим, зависит от того, какая марка автомобиля была выбрана ранее.
В этом нам поможет функция INDIRECT. Функция ДВССЫЛ преобразует текст в стандартную ссылку Excel.
Если мы напишем
= КОСВЕННО («F3»)
тогда это будет похоже на запись формулы в ячейку
= F3
Но зачем так усложнять? Дело в том, что Excel почему-то отказывается воспринимать некоторые типы формул как источник данных для выпадающего списка. Так что придется идти на такие уловки.
«Уловка» функции КОСВЕННАЯ (или КОСВЕННАЯ) заключается в том, что она позволяет использовать текст так же, как и обычную ссылку на ячейку. Это дает нам два ключевых преимущества:
- вы можете собрать текстовую ссылку (то есть записать ее как текстовую переменную), что полезно для определенных типов динамических ссылок.
- Вы можете выбрать текстовые значения на листе и использовать их в качестве ссылки на ячейку в формуле.
В примере на этой странице мы объединяем эту последнюю идею с именованными диапазонами для создания многоуровневого раскрывающегося списка. INDIRECT преобразует простой текст в имя, которое затем становится обычной ссылкой и источником данных.
Итак, в этом примере возьмем текстовые значения из A1: C1, выберите одно. Например, «Форд». Поскольку у нас одно и то же имя для одного из именованных диапазонов, мы используем INDIRECT для преобразования текста «Ford» в ссылку = ford. И теперь мы используем его как источник для связанного выпадающего списка.
Итак, в качестве источника значений мы используем формулу
= КОСВЕННО ($ F $ 3)
Ячейка F3, используемая в качестве аргумента, содержит текстовое выражение, которое соответствует названию именованного диапазона, соответствующего автомобильным маркам.
В результате функция возвращает ссылку на нашу таблицу Excel
= форд
Регистр символов в этом случае не имеет значения: все автоматически переводится в нижний регистр. И это будет источник данных.
Изменяя значения в F3, мы автоматически меняем ссылку на источник для списка на F6. В результате источник данных для зависимого раскрывающегося списка в F6 динамически изменяется в зависимости от того, что было выбрано в F3. Если мы выберем Ford, мы увидим только каталог автомобилей этой марки. Точно так же, если мы выберем Тойоту или Ниссан.
Многоуровневые раскрывающиеся списки могут быть вложены более двух раз. В нашем примере вы можете добавить еще один третий уровень. Он может содержать названия некоторых запчастей для ранее выбранной модели. Так формируются сложные списки, в которых каждый последующий уровень зависит от предыдущего.
А как быть с пробелами?
Может случиться так, что название вашей группы товаров или категории содержит пробелы. Именованные диапазоны не допускают пробелов в имени. Их принято заменять знаком подчеркивания «_». Как же нам быть в таком случае? На самом деле, в таблице названия товарных категорий со знаком подчеркивания будут выглядеть несколько необычно. Например, «Косметические товары». По привычке можно просто забыть ввести нужный символ. И тогда наши формулы не работают.
Выход довольно простой. Создавайте именованные списки, заменяя пробелы в их именах подчеркиванием. В самих значениях, записанных в ячейках таблицы Excel, используйте обычные пробелы. И прежде чем применять их в формуле, мы обработаем их особым образом с помощью функции ЗАМЕНИТЬ.
В текстовой строке он заменяет одни символы на другие. Чтобы вместо пробела отображалось подчеркивание, можно использовать формулу вида
= ПОДСТАВИТЬ (F3; «»; «_»)
То есть мы заранее обработаем значения, чтобы они соответствовали правилам написания имен. Вместо = КОСВЕННО ($ F $ 3) напишем
= КОСВЕННО (ПОДСТАВИТЬ ($ F $ 3; «»; «_»))
Кавычки здесь не нужны, потому что SUBSTITUTE возвращает текстовую строку. Если в нашем тексте нет пробелов и он состоит из одного слова, он будет возвращен «как есть». Просто убедитесь, что у вас случайно нет пробелов в начале и в конце обрабатываемой текстовой переменной. Ведь они тоже будут заменены подчеркиванием. Что ж, чтобы не заниматься этой ручной проверкой, немного усложните свою формулу, используя функцию ОБРЕЗАТЬ. Он автоматически удалит из текста начальные и конечные пробелы. В результате получаем:
= КОСВЕННО (ПОДСТАВИТЬ (ОБРЕЗАТЬ ($ F $ 3); «»; «_»))
А теперь — еще один способ создания многоуровневого зависимого выпадающего списка в Excel.
2. Комбинация СМЕЩ + ПОИСКПОЗ
Итак, у нас снова есть список марок и моделей автомобилей. За исключением того, что это пишется немного по-другому.
Опять же, перед нами стоит задача создать двухуровневый выпадающий список. В первую очередь — выбор марки, затем — только модели этой марки.
Первое условие — исходные данные должны быть отсортированы по брендам, а внутри брендов — по моделям. То есть вам нужно отсортировать по столбцу A, а затем по столбцу B.
Начнем с простого. В ячейке D1 создайте раскрывающийся список автомобильных марок. Для этого напишите их имена в F1: F3, а затем используйте их как источник. Напомню, что вам нужно нажать Меню — Данные — Проверить данные.
Далее нам нужно создать второй уровень в D2, где будут только модели выбранной марки. На этот раз мы определим источник данных немного иначе, чем раньше. Мы пользуемся тем фактом, что функция OFFSET может возвращать массив данных, которые мы можем просто использовать для заполнения нашего второго списка. Но для этого нужно передать до 5 параметров:
- координаты верхней левой ячейки,
- на сколько строк спуститься — A,
- сколько столбцов идти вправо — B,
- высота массива (строк) — C,
- ширина массива (столбцы) D.
Новый диапазон выделен зеленым на рисунке. Если параметры C и D не указаны, после выполнения нескольких шагов вниз и вправо будет возвращено содержимое единственной ячейки, в которую мы переместились.
Традиционно мы берем ячейку A1 в качестве отправной точки для функции СМЕЩЕНИЕ. Теперь нам нужно решить, сколько позиций внизу и справа нам нужно пройти, чтобы обозначить верхний левый угол нового списка с шаблонами. Допустим, мы изначально выбрали Форд.
Сколько ступенек спуститься? Мы используем функцию ПОИСКПОЗ, которая вернет нам номер позиции первого появления слова «Форд».
= ПОИСК ($ D $ 1, $ A $ 1: $ A $ 22,0)
Если нужное нам слово впервые попалось, например, в 7-й позиции, отнимите 1, чтобы получить количество шагов. То есть, начиная с первого значения, нужно сделать 6 шагов.
Мы устанавливаем третий параметр равным 1, так как нам нужно перейти прямо от A к B. Мы находимся в начальной точке нашего диапазона. А теперь посчитаем, сколько ячеек внизу будет продолжаться. Для этого мы подсчитываем, сколько раз «Форд» появляется в нашем списке. Многие и будут медвежьими значениями.
= СЧЁТЕСЛИ ($ A $ 1: $ A $ 22; $ D $ 1)
Теперь объединим все в OFFSET:
= СМЕЩЕНИЕ ($ A $ 1; ПОИСК ($ D $ 1; $ A $ 1: $ A $ 22,0) -1,1; СЧЁТЕСЛИ ($ A $ 1: $ A $ 22; $ D $ 1); 1)
Последнее означает, что массив состоит из одного столбца.
В D2 создайте раскрывающийся список, используя это выражение. В нем будут представлены только модели Ford, поскольку этот бренд был выбран ранее.
Точно так же вы можете создать зависимый раскрывающийся список третьего уровня.
