Связанные выпадающие списки в excel как сделать

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

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

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

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

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

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

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

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

Читать еще:  Как сделать прогнозирование в excel?

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

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

1. С помощью функции ДВССЫЛ

ДВССЫЛ (INDIRECT) преобразовывает содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Мир», то функция выдаст ссылку на именованный диапазон со словом Мир.

Итак, начнём. Выделяем весь список «Певец» (с ячейки А2 и вниз до конца списка) и нажав Ctrl+F3 или на вкладке Формулы — Диспетчер имён – Создать задаём имя диапазону (Певец). Затем таким же способом проделаем то же самое с остальными списками «Группа» и «Дата».

Слова в диапазонах не должны содержать пробелов, знаков препинания и должны начинаться обязательно с буквы. Поэтому пробелы необходимо заменять на нижнее подчёркивание.

Теперь создадим первый выпадающий список для выбора певца. Выделяем пустую ячейку и на вкладке «Данные» нажимаем кнопку «Проверка данных». Затем из выпадающего списка «Тип данных» выбираем вариант «Список» и в поле «Источник» выделяем ячейки с названиями (ячейки A1:C1).

После нажатия на ОК первый выпадающий список готов:

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

Теперь содержимое второго выпадающего списка будет выбираться по имени диапазона, выбранного в первом.

Минусы данного способа:

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

— Надо вручную создавать много именованных диапазонов (если у нас много значений).

2. Для следующего способа нам нужна таблица, разбитая по категориям и видам:

Как и в предыдущем способе через Ctrl+F3 создаём именованные диапазоны каждой категории (без заголовков). Т.е. сначала «Мебель», затем «Техника» и так каждую по отдельности.

Теперь создаём первый выпадающий список по категориям. Для этого на вкладке «Данные» — «Проверка данных» — «Тип данных» выбираем «Список» и в поле «Источник» вводим названия диапазонов (Мебель;Техника)

Далее для второго зависимого списка тем же способом для ячейки «Вид» в поле «Источник» вводим формулу =ДВССЫЛ($B$14), а для «Тип» =ДВССЫЛ($B$15). В результате у нас получится следующее:

3. С помощью функций СМЕЩ и ПОИСКПОЗ

Для использования этого способа необходим отсортированный список с отдельным образцом имеющихся значений:

Далее нажимаем Ctrl+F3, где задаём имя диапазону D1:D3 (Товар). Затем на вкладке «Данные» — «Проверка данных» — «Тип данных» — «Список» и в строке «Источник» указываем =Товар или просто выделить ячейки D1:D3 (если они на том же листе, где список).

Чтобы выпадающий список автоматически пополнялся новыми данными из категории «Товар», открыв «Диспетчер имён» в строке диапазон вписываем формулу =СМЕЩ($D$1;0;0;СЧЁТЗ($D$1:$D$400);1) где $D$400 количество ячеек необходимое для образца выпадающего списка.

Для зависимого списка товара создадим именованный диапазон с функцией СМЕЩ, который будет динамически ссылаться только на ячейки товара определенного вида. Для этого нажав Ctrl+F3 создаём новый именованный диапазон с любым именем (например, Вид) и в поле «Диапазон» в нижней части окна вводим следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов.

Функция СМЕЩ выдаёт ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. Вот так: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

— начальная ячейка – берём первую ячейку нашего списка, т.е. А1

— сдвиг_вниз – считает функция ПОИСКПОЗ, которая выдаёт порядковый номер ячейки с выбранным товаром (G2) в заданном диапазоне (столбце А)

— сдвиг_вправо = 1, т.к. мы хотим сослаться на Вид в соседнем столбце (В)

— размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ, которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – товара (G2)

— размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с Видом товара

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

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

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

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

Читать еще:  Как сделать многоуровневую таблицу в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota. В Excel 2003 и старше — это можно сделать в меню Вставка — Имя — Присвоить (Insert — Name — Define). В Excel 2007 и новее — на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

Читать еще:  Как сделать сквозную нумерацию строк в excel?

При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные — Проверка (Data — Validation) или нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) если у вас Excel 2007 или новее. Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

где F3 — адрес ячейки с первым выпадающим списком (замените на свой).

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

Минусы такого способа:

  • В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
  • Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»))
  • Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

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

  • дать имя диапазону D1:D3 (например Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas) или в старых версиях Excel — через меню Вставка — Имя — Присвоить (Insert — Name — Define)
  • выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
  • выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source)=Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

  • Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas). В версиях до 2003 это была команда меню Вставка — Имя — Присвоить (Insert — Name — Define)
  • Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь 🙂

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

  • начальная ячейка — берем первую ячейку нашего списка, т.е. А1
  • сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
  • сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
  • размер_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
  • размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

  • выделяем ячейку G8
  • выбираем на вкладке Данные (Data) команду Проверка данных (Data validation) или в меню Данные — Проверка (Data — Validation)
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модели
Ссылка на основную публикацию
Adblock
detector