Ошибка связанные выпадающие списки

Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах, то неизбежно столкнетесь с проблемой: нужно сделать один выпадающий список в 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, поскольку эта марка была выбрана ранее.

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

Еще полезная дополнительная информация:

Хитрости »

1 Май 2011              198404 просмотров

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

Скачать файл, используемый в видеоуроке:

  Связанные выпадающие списки.xls (216,5 KiB, 3 084 скачиваний)

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2. В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки. А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 — т.е. список выбранной категории продуктов. Например выбрали в А2 значение Овощи — в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо — в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка. И т.д.

Подготовка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить — если сами списки расположено на листе, отличном от того, на котором списки выпадающие — то обязательно создавать именованный диапазон с назначением области действия — Книга.
В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках.
Если ваши категории содержат пробел — необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков
В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.
В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data)—Проверка данных (Data validation)—Список (List). Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT). Просто записываем эту формулу в поле Источник (Source): =ДВССЫЛ($A2)

На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
=ДВССЫЛ(«‘[Книга со списком.xls]Лист1’!»&$A$1)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой. В ином случае возможны ошибки (если, конечно, это не было сделано специально с пониманием того, что делалось).

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

1. обе книги должны быть открыты. Если вы закроете книгу со списками, то получите ошибку — выпадающие списки просто перестанут работать
2. созданные подобным образом связанные списки не будут работать с динамическими именованными диапазонами

И ничего с этими ограничениями не поделать при подобном подходе.

Скачать пример

  Tips_Lists_Connect_Validation.xls (26,5 KiB, 18 128 скачиваний)

Так же см.:
Выпадающие списки
Именованные диапазоны
Динамические именованные диапазоны

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика