Как сделать выпадающее меню в excel со связью

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделить область, куда вставить перечень. На верхней панели перейти во вкладку «Данные», далее – «Проверка данных».

В новом окне найти параметр «Тип данных». Выбрать пункт «Список».

  • Теперь указать «Источник». Кликнуть левой кнопкой мышки по полю, вписать знак «=» без кавычек и название из п.2. В качестве источника могут указываться столбцы из других листов рабочей книги. Нажать на «Ок».
  • Ввести заголовок и непосредственно сообщение – некая подсказка для пользователя.
  • Связанный выпадающий список

    Задать диапазонам с перечислением блюд имя в соответствии с его категорией. Общее название каждого диапазона должно четко совпадать с тем, что записано в первой ячейке каждого столбца.

    На отдельной области создать раскрывающийся список через пункт меню «Данные» — «Проверка данных». Источник – первая ячейка каждого столбца.

  • Далее перейти на формирование перечисления блюд одной из категорий. В этом поможет функция ДВССЫЛ (на англ. INDIRECT), которая преобразовывает текст в обычную экселевскую ссылку и источник данных для него. Нужно выбрать категорию, повторно открыть «Проверку данных» и написать функцию =ДВССЫЛ(H2).
  • При выборе другого элемента в H2 автоматически изменяется и ссылка-источник для H3. То есть источник для связанного выпадающего перечня в H3 меняется с учетом данных, которые были выбраны в H2.

    Пробелы в названии при создании связанного выпадающего списка

    Именуя диапазон значений, важно использовать вместо пробела нижнее подчеркивание «_». Например, «Мясные_блюда». Однако есть вероятность упустить данное правило в одном из названий, и в результате будет ошибка при выполнении операции. Можно в самих названиях использовать обычный пробел, а в момент подстановки в список, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

    =ПОДСТАВИТЬ(F3;" ";"_") без кавычек.

    Итоговая формула выглядит так:

    =ДВССЫЛ(ПОДСТАВИТЬ($F$3;" ";"_")) без кавычек.

    Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Автоматизировать данный процесс при построении имени также можно посредством функции:

    =ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);" ";"_")) без кавычек.

    Список с автозаполнением

    Способ 1

    Теперь при добавлении новых элементов они также появятся в готовом выпадающем перечне.

    Способ 2

    Щелкнуть по сторонней ячейке. Зайти в «Данные» – «Проверка данных». В качестве источника указать присвоенное таблице имя из п.1 через знак «=».

  • Чтобы добавить еще одно значение, нужно воспользоваться вставкой пустой строки. Выделить ячейку с любым наименованием, на «Главной» клацнуть по «Вставить» — «Вставить строки на лист». На листе отобразится пустая ячейка, вписать туда название.
  • Способ 3

  • Обозначить готовую таблицу как источник, вписать формулу =ДВССЫЛ("Таблица1[Горячее]"), где таблица1 – автоматически присвоенное ей имя, [Горячее] – название столбца.
  • Если в нижние строки вписать новые значения, они тут же отобразятся в готовом перечне.

    Как в Excel сделать выпадающий список в ячейке с выбором нескольких значений?

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

    Чтобы добавить макрос, кликнуть правой кнопкой мыши по ярлыку листа с выпадающим перечнем и выбрать вариант «Исходный текст». Откроется Visual Basic, в окне редактора следует использовать код для горизонтального отображения:

    В результате должно получиться следующее:

    Для вертикального отображения можно воспользоваться кодом:

    В результате получится так:

    Для того чтобы элементы накапливались в той же самой ячейке, подойдет код:

    Ссылка на основную публикацию