Одной из наиболее полезных функций проверки данных является возможность создания раскрывающегося списка, позволяющего выбрать значение из предопределенного списка. Но когда вы начнете применять это к своим электронным таблицам, вы неизбежно столкнетесь с проблемой: вам нужно сделать выпадающий список в Excel зависимым от одного значения в другом. Другими словами, как создать динамический многоуровневый связанный список?
Вот примеры таких задач:
- список городов в зависимости от выбранной страны.
- товары, произведенные конкретным производителем.
- наименования товаров, входящих в товарную группу.
- выбрать модель автомобиля в зависимости от марки.
Это может выглядеть примерно так:
Такие списки называются зависимыми, или связанными, потому что их содержимое зависит друг от друга. Такие сложные конструкции можно изготовить несколькими способами.
Содержание- 1. Именованные области + функция ДВССЫЛ.
- А как же пробелы?
- 2. Комбинация OFFSET + MATCH
Начнем с более простого и стандартного подхода.
1. Именованные диапазоны + функция ДВССЫЛ.
Это может показаться сложным, но на самом деле это очень просто и является хорошим примером того, как можно использовать INDIRECT.
Рассмотрим небольшой пример. У нас есть список автомобилей различных марок. Давайте поместим их каждый в отдельный столбец. В первую ячейку каждого столбца записываем производителя — Тойота, Форд, Ниссан. Нужно, чтобы после того, как мы впервые выбрали, например, Тойоту, мы увидели бы только модели этой марки, и ничего более. То есть нам нужен двухуровневый связанный список.
Сначала создадим именованные серии моделей автомобилей. Каждому из них мы присвоим имя в соответствии с маркой автомобиля. Важно, чтобы название каждого из них в точности совпадало со значением, записанным в первой строке соответствующего столбца. Другими словами, если мы создаем именованный диапазон из ячеек A2:A100, имя должно совпадать со значением в ячейке A1 (регистр и строчные буквы значения не имеют). Посмотрите на картинку, как это выглядит.
Итак, у нас есть 3 названных направления — «тойота», «форд», «ниссан». Сделать их статичными (фиксированными) или динамическими (автоматически пополняемыми) — решайте сами. Информацию о том, как создать список автозаполнения, см по ссылке в конце этой статьи.
Затем в ячейке F3 создаем выпадающий список моделей автомобилей первого уровня, как показано в нашем примере на рисунке. Источником данных являются первые ячейки в каждом столбце. Обратите внимание, что инструмент проверки ввода, который мы используем для его создания, прекрасно работает как с вертикальными (по строкам), так и с горизонтальными (по столбцам) данными.
А затем выбираем интересующего нас производителя. Например "Форд".
Затем в ячейке F6 с помощью зависимого выпадающего списка на втором уровне выбираем уже определенную модель этой марки. Список, который мы увидим, определяется тем, какая марка автомобиля была выбрана ранее.
В этом нам поможет функция ДВССЫЛ. Функция ДВССЫЛ преобразует текст в стандартную ссылку Excel.
Если мы напишем
= ДВССЫЛ ("F3")
то это будет соответствовать нам написанию формулы в ячейке
=F3
Но зачем так усложнять? Дело в том, что Excel почему-то отказывается распознавать некоторые типы формул как источник данных для выпадающего списка. Вот и приходится идти на такие ухищрения.
«Трюк» функции ДВССЫЛ (или ДВССЫЛ) заключается в том, что она позволяет вам использовать текст так же, как обычную ссылку на ячейку. Это дает нам два основных преимущества:
- Вы можете составить текстовую ссылку (то есть записать ее как текстовую переменную), что полезно для определенных типов динамических ссылок.
- Вы можете выбрать текстовые значения на листе и использовать их в качестве ссылки на ячейку в формуле.
В примере на этой странице мы объединяем последнюю идею с именованными диапазонами, чтобы создать многоуровневый раскрывающийся список. ДВССЫЛ преобразует обычный текст в имя, которое затем становится обычной ссылкой и источником данных для него.
Итак, в этом примере мы берем текстовые значения из A1:C1, выбираем одно из них. Например "Форд". Поскольку одна из наших именованных областей имеет такое же имя, мы используем ДВССЫЛ, чтобы преобразовать текст «Ford» в ссылку =ford. И теперь мы используем его в качестве источника для связанного выпадающего списка.
Итак, в качестве источника значений используем формулу
=ДВССЫЛ($F$3)
В ячейке F3, которая используется в качестве аргумента, находится текстовое выражение, совпадающее с названием соответствующего именованного диапазона марок автомобилей.
В результате функция возвращает ссылку на нашу таблицу Excel
= вперед
Буква символа в данном случае не имеет значения — все автоматически преобразуется в нижний регистр. И это то, что будет источником данных.
Меняя значения в F3, мы автоматически меняем исходную ссылку списка в F6. В результате источник данных для зависимого выпадающего списка в F6 динамически меняется в зависимости от того, что было выбрано в F3. Если мы выбираем Ford, мы видим только каталог автомобилей этой марки. Точно так же, если мы выберем Toyota или Nissan.
Многоуровневые выпадающие списки могут быть вложены более двух раз. В нашем примере вы можете добавить еще один третий уровень. Может содержать наименования отдельных запчастей для ранее выбранной модели. Так формируются сложные списки, где каждый следующий уровень зависит от предыдущего.
А как быть с пробелами?
Название группы товаров или категории может содержать пробелы. А именованные пространства не допускают пробелов в своих именах. Обычно их заменяют символом подчеркивания «_». Как нам быть в этом случае? На самом деле в таблице названия категорий товаров с символом подчеркивания будут выглядеть несколько необычно. Например "Косметика_товары". По привычке вы можете просто забыть ввести нужный символ. И тогда наши формулы не будут работать.
Выход довольно прост. Создавайте именованные списки, заменяя пробелы в их именах символами подчеркивания. В самих значениях, записанных в ячейках таблицы Excel, используйте обычные пробелы. И перед тем, как использовать их в формуле, мы обработаем их особым образом с помощью функции ЗАМЕНИТЬ.
Внутри текстовой строки вместо любого символа замените другой. Чтобы символ подчеркивания отображался вместо пробела, вы можете использовать формулу наподобие
=ПОДСТАВИТЬ(F3;" ";"_")
Это означает, что мы предварительно обработаем значения, чтобы они соответствовали правилам написания имен. Вместо =ДВССЫЛ($F$3) мы пишем
=ДВССЫЛ(ЗАМЕНИТЬ($F$3," ","_"))
Кавычки здесь не нужны, потому что ПОДСТАВИТЬ возвращает текстовую строку. Если в нашем тексте нет пробелов и он состоит из одного слова, он будет возвращен «как есть». Просто убедитесь, что у вас случайно нет пробелов в начале и конце обрабатываемой текстовой переменной. Ведь они тоже будут заменены на подчеркивания. Ну а чтобы не заниматься этой ручной проверкой, формулу немного усложняем с помощью функции TRIM. Он автоматически удалит начальные и конечные пробелы из текста. В результате получаем:
=ДВССЫЛ(ПОДСТАВИТЬ(ОБРЕЗАТЬ ПРОБЕЛ($F$3)," ","_"))
Ну а теперь — еще один способ создания многоуровневого зависимого выпадающего списка в Excel.
2. Комбинация СМЕЩ + ПОИСКПОЗ
Итак, у нас остался список марок и моделей автомобилей. Просто пишется немного по другому.
Снова перед нами стоит задача создать двухуровневый выпадающий список. Сначала - выбор марки, потом - только модели этой марки.
Первое условие — исходные данные должны быть отсортированы по маркам, а внутри марок — по моделям. То есть надо сортировать по столбцу А, а потом - по Б.
Начнем с простого. В ячейке D1 мы создадим выпадающий список марок автомобилей. Для этого в F1:F3 мы запишем их имена и потом будем использовать их как исходники. Напомню, что необходимо нажать Меню - Данные - Проверка данных.
Далее нам нужно создать второй уровень в D2, где будут только модели выбранной марки. На этот раз мы определим источник данных немного иначе, чем раньше. Давайте воспользуемся тем фактом, что функция OFFSET может возвращать массив данных, которые мы можем просто использовать для заполнения нашего второго списка. Но для этого она должна передать целых 5 параметров:
- координаты ячейки вверху слева,
- на сколько строк двигаться вниз - А,
- на сколько столбиков сдвинуть вправо - В,
- высота массива (строк) - C,
- ширина массива (столбцов) D.
Новый выбор выделен зеленым цветом на рисунке. Если параметры C и D не указаны, будет возвращено содержимое единственной ячейки, в которую мы переместились, сделав несколько шагов вниз и вправо.
Традиционно отправной точкой для функции СМЕЩ является ячейка A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нам нужно перейти, чтобы указать левый верхний угол нового списка моделей. Предположим, мы сначала выбрали Ford.
На сколько ступенек вам нужно спуститься? Давайте воспользуемся функцией ПОИСКПОЗ, которая вернет номер позиции первого вхождения слова «Форд".
=ПОИСКПОЗ($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, так как ранее был выбран именно этот бренд.
Точно так же вы можете создать раскрывающийся список, зависящий от третьего уровня.
Еще полезная дополнительная информация:
Как использовать функцию ДВССЫЛ — примеры формул. В этой статье объясняется синтаксис функции ДВССЫЛ, ее основные области применения и приводится ряд примеров формул, демонстрирующих использование функции ДВССЫЛ в Excel. В Microsoft Excel много функций, некоторые из них просты для понимания, другие требуют долгого изучения. При этом первый…
Создать выпадающий список в Excel с помощью формул - Задача: Создать выпадающий список в Excel, чтобы все новые значения автоматически попадали в него. Мы сделаем это с помощью формул, чтобы этот метод можно было использовать не только в Excel 2007 и более поздних версиях, но и в Excel 2003. Как…
5 способов создать раскрывающийся список в ячейке Excel. Одной из наиболее полезных функций ввода данных является возможность использовать раскрывающийся список в Excel. Он позволяет выбрать значение из предопределенного списка и позволяет вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов…
Александр Трифунтов2022-02-18T14:13:00+03:00 30 ноября 20193 Комментарии
