Зависимый выпадающий список в Excel и Google таблицах · BIRDYX

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

Решение

Выпадающие списки будем формировать с помощью одной большой Именованной формулы :

=ЕСЛИ(Таблица!B$7=Списки!$F$5;СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!A8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!A8)))

Эту формулу можно посмотреть через Диспетчер имен (имя формулы – Выбор ), но так как формула использует относительную адресацию в ссылках на ячейки Таблица!B$7 и Таблица!A8, то для ее просмотра нужно предварительно выделить ячейку В8 на листе Таблица. О том как работает эта формула см. в разделе статьи ниже.

ПРИМЕЧАНИЕ : Функция ЕСЛИ() нужна для формирования списка самого верхнего уровня иерархии – у него нет Родителя.

Для ввода формулы выделите ячейку B8 , вызовите Диспетчер имен , и введите формулу:

Нажмите ОК.

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

structura-30.png

С помощью Маркера заполнения скопируйте ячейку вправо и вниз (или вверх и вправо): выпадающие списки всех уровней будут сформированы автоматически.

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

Для наглядности различные уровни выделены Условным форматированием .

Заполнение таблицы на листе Списки нужно производить следующим образом:

  • Столбец Номер уровня содержит числовые коды уровней. Самый верхний уровень (0) содержит название Регионов. Можно создать любое количество уровней;
  • Столбец Родитель содержит названия из столбца Потомок более верхнего уровня;
  • Столбец Потомок связывает значений из 2-х соседних уровней. Нужно иметь ввиду, что если мы начали заполнять, например, все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы. Выпадающие списки формируются именно из этого столбца.

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

Связанные выпадающие списки – это списки, в которых выпадающие значения появляются не «просто так», а в зависимости от уже заполненных данных. Так, для выбранной группы появится только список входящих в неё наименований.

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

Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.

Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.

excel, таблица

Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.

excel, таблица

Введение

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

  • Список адресов в разных городах

Задача:

  • Создать автоматически обновляемый выпадающий список уникальных городов
  • На основе выбранного города, создать зависимый выпадающий список адресов

Визуализация задачи

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

Рабочие файлы по ссылке ниже

Ссылка на файлы

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

Создадим первый выпадающий список

Сначала Задаем имя диапазону ячеек, в которых находятся название столбцов. В нашем примере это ячейки С4;В4;Е4 (Конфеты, Печенья и Торты). Назовём данный диапазон: Товары.

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

В закладке Данные, находим кнопку Проверка данных. 

Проверка данных

Нажимаем на нее. Появляется диалоговое окно Проверка вводимых значений, в которое вносим данные:

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

Источник. Здесь после знака равно «=» пишем наше Заданное имя. В нашем примере: =Товары.

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

Нажимаем ОК. У нас появляется выпадающий список с названием видом товаров.

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

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

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

Два связанных выпадающих списка.

Итак, мы имеем:

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

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

следующие данные.

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

  1. Тип.
  2. Производитель.
  3. Модель.

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

Типа и Производителя.

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

Как сделать выпадающий список в Excel?

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

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

Указываем диапазон с данными для выпадающего списка

Выпадающий список готов!

Простой выпадающий список готов

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

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ.

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

См. также

Применение проверки данных к ячейкам

Функции Excel (по категориям)

Мультивыбор

Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».

Откроется окно разработчика. В него надо вставить следующий алгоритм.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(“C2:F2”)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub


Обратите внимание, что в строке

If Not Intersect(Target, Range(“E7”)) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(“E7”)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) <> 0 And oldval <> newVal Then Target = Target & “,” & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Как только Вы переведете указатель на другую ячейку, Вы увидите перечень выбранных городов. Для создания объединенных ячеек в Excel прочитайте эту статью.

Мы рассказали, как добавить и изменить выпадающий список в ячейку Excel. Надеемся, эта информация поможет вам.

Отличного Вам дня!

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...