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