Многоуровневый список в excel

Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel

В данной статье рассматривается методика быстрого создания многоуровневых (каскадных) выпадающих списков в Excel на основе умных таблиц для моделирования иерархических данных. В качестве примера в видеоуроке создаются 6-уровневые выпадающие списки.

Видеоурок к статье:

Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда , каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо , одежда – Верхняя_одежда и Спортивные_товары .

Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.

На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты , содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо , содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).

Остальные данные организуются по такому же принципу.

Список 1 уровня

Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3

Создать список полей конкретной таблицы:

=ДВССЫЛ(» Таблица1 «&»[#Заголовки]»)

Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[ Поле1 ]»)

Список 2 уровня

Создать список полей таблицы, выбираемой в вышестоящем списке:

=ДВССЫЛ( A1 &»[#Заголовки]»)

Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[«& А1 &»]»)

Список 3 уровня

Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &»[«& B1 &»]»)

После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:

Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в надстройке SubEx для Excel , которая моментально сформирует за вас нужные формулы!

Всего возможны три вида выпадающих списков:

1 уровня (первичный, не связанный ни с чем список)

2 уровня (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)

3 уровня (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)

Обратите внимание! Все уровни иерархии моделируются с использованием этих трёх видов выпадающих списков, сгруппированных в различных комбинациях.

Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого — ставится ссылка на выпадающий список вышестоящего уровня:

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

Следующие списки будут связанными, т.е. зависеть от выбранного значения вышестоящего списка. Список может быть 2 уровня, т.е. ссылаться на один вышестоящий список, или 3 уровня, т.е. ссылаться на два вышестоящих списка (например, на список с именем таблицы и список с именем поля таблицы).

Как было сказано выше полученная система таблиц и списков является полностью динамической: можно добавлять новые поля и элементы в любую таблицу и они автоматически подвяжутся в выпадающие списки.

Подробный пример создания 6-уровневых выпадающих списков на примере номенклатуры товаров магазина приведен в видеоуроке!

Двухуровневый выпадающий список в Excel

Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных.

Первый способ создания двухуровнего списка

Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

  1. Выберите ячейку, в которую будете вставлять выпадающий список;
  2. Переходим на вкладку ленты Данные;
  3. Выбираем команду Проверка данных;
  4. В выпадающем списке выбираем значение Список;
  5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1[#Заголовки]»).

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1[«&F2&»]»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

Читать еще:  Как сделать таблицу в excel чтобы сумма считалась автоматически

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

  • Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
  • Смещ_по_строкамПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
  • Cмещ_по_столбцам1 — так как нам необходим столбец с подгруппами (городами);
  • [Высота]СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
  • [Ширина]1 — так как это ширина нашего столбца с подгруппами.

Связанные выпадающие списки и формула массива в Excel

Итак, как сделать два связанных списка в Excel: категория, подкатегория и категория более нижнего уровня. Своими словами в данном случае нижний уровень — это «подподкатегория» если она вообще существует. Но для лучшего понимания данного обучающего материала, предположим, что существует.

Два связанных выпадающих списка с формулой массива

В любом случае, с самого начала напишем, что этот учебный материал является продолжением материала: Как сделать зависимые выпадающие списки в ячейках Excel, в котором подробно описали логику и способ создания одного из таких списков. Рекомендуем вам ознакомиться с ним, потому что здесь подробно описывается только то, как сделать тот другой связанный выпадающий список 🙂 А это то, что мы хотим получить:

  • тип автомобиля: Легковой, Фургон и Внедорожник (Категория)
  • производитель: Fiat, Volkswagen i Suzuki (Подкатегория) и
  • модель: . немножечко их есть 🙂 (Подподкатегория)

В то же время мы имеем следующие данные:

Этот список должен быть отсортирован в следующей очередности:

Он может быть любой длины. Что еще важно: стоит добавить к нему еще два меньших списка, необходимых для Типа и Производителя, то есть к категории (первый список) и подкатегории (второй список). Эти дополнительные списки списки выглядят следующим образом:

Дело в том, что эти списки не должны иметь дубликатов записей по Типу и Производителю, находящихся в списке Моделей. Вы можете создать их с помощью инструмента «Удалить дубликаты» (например, это показано в этом видео продолжительностью около 2 минут). Когда мы это сделали, тогда .

Первый и второй связанный выпадающий список: Тип и Производитель

Для ячеек, которые должны стать раскрывающимися списками в меню «Данные» выбираем «Проверка данных» и как тип данных выбираем «Список».

Для Типа как источник данных мы просто указываем диапазон B7:B9.

Для Производителя мы уже используем формулу, которая подробно описана здесь. Она выглядит так:

Модель — описание для этой записи сделаем таким же самым образом.

Третий связывающий выпадающий список: Модель

Теперь рассмотрим, как связать выпадающий список в Excel. Поскольку Модель зависит как от Типа, так и от Производителя — значит мы будем использовать сложную формулу. После поместим ее не в проверку данных, а в именной диапазон. Соответственно проверка данных будет содержать ссылку на это имя. Предположим, мы хотим отобразить в нем легковые модели Fiat. В первом списке мы выбрали Легковой, во втором — Fiat.

Мы будем перемещать ячейку H4 на столько строк, пока не найдем позицию первого легкового Fiatа. Поэтому в колонке Тип мы должны иметь значение Легковой, а в колонке Производитель должен быть Fiat. Если бы мы использовали промежуточный столбец (это было бы отличным решением, но хотели бы показать вам что-то более крутое ;-), то мы бы искали комбинацию этих данных: Легковой Fiat. Однако у нас нет такого столбца, но мы можем создать его «на лету», другими словами, используя формулу. Набирая эту формулу, вы можете себе представить, что такой промежуточный столбец существует, и вы увидите, что будет проще 😉

Читать еще:  Как в excel в ячейке создать список

Для определения положения Легковой Fiat, мы, конечно, будем использовать функцию ПОИСКПОЗ. Смотрите:

Вышеописанное означает, что мы хотим знать позицию Легкового Fiatа (отсюда и связь B4&C4). Где? В нашем воображаемом вспомогательном столбце, то есть: F5:F39&G5:G39. И здесь самая большая сложность всей формулы.

Остальное уже проще, а наибольшего внимания требует функция СЧЁТЕСЛИМН, которая проверяет, сколько есть Легковых Fiatов. В частности, она проверяет, сколько раз в списке встречаются такие записи, которые в столбце F5:F39 имеют значение Легковой, а в столбце G5:G39 — Fiat. Функция выглядит так:

А вся формула для именного диапазона раскрывающегося списка это:

Если вы планируете использовать эту формулу в нескольких ячейках — не забудьте обозначить ячейки как абсолютные ссылки!

Теперь чтобы правильно использовать данную формулу по назначению в третьем выпадающем списке нам необходимо выполнить рад последовательных действий:

  1. Создаем новое имя. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».
  2. При создании имени в поле «Имя:» вводим слово – модель, а в поле «Диапазон:» вводим выше указанную формулу и нажимаем на всех открытых диалоговых окнах ОК:
  3. Перейдите на ячейку D4 чтобы там создать выпадающий список, в котором на этот раз в поле ввода «Источник:» следует указать ссылку на выше созданное имя с формулой =модель.

Когда вы перейдете в меню «Данные», «Проверка данных» и выберите как Тип данных «список», а в поле «Источник» вставьте не саму формулу, а ссылку на имя «=модель» именного диапазона с этой формулой. Такой подход обеспечит стабильность работы третьего выпадающего списка.

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

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

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А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)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой. В ином случае возможны ошибки (если, конечно, это не было сделано специально с пониманием того, что делалось).

Читать еще:  Как распечатать таблицу excel на одном листе

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

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

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

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

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

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

Здравствуйте!
В связанных списках для заголовков одной таблицы использую ДВССЫЛ таким образом =ДВССЫЛ(«Таблица[#Заголовки]»), дабы при добавлении нового столбца или изменении заголовков в динамической таблице автоматом отображались данные изменения и добавления.
Но в некоторых случаях происходит некое зависание, данный список не раскрывается, он будто не видит данных которые необходимо отобразить.
Подскажите в чем может быть причина?

Поделитесь своим мнением

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

Зависимые выпадающие списки в Excel

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):


В результате мы хотим получить своеобразный двухуровневый зависимый выпадающий список: на первом уровне (списке) — категории блюд, на втором — блюда из выбранной категории.

Как сделать зависимые выпадающие списки?

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:


Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):


Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:


Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):


Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector