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


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

Зависимый выпадающий список позволяет сделать трюк, который очень часто хвалят пользователи шаблонов Excel. Трюк, который делает работу проще и быстрее. Трюк, благодаря которому ваши формы будут удобны и приятны.

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.



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

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

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

Зависимый выпадающий список подкатегорий

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

Рабочая исходная таблица Excel

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

Можно было бы также использовать таблицы с первого изображения. Разумеется, формулы были бы разными. Однажды даже я нашел в сети такое решение, но оно мне не понравилось, потому что там была фиксированная длина списка: а значит, иногда список содержал пустые поля, а иногда и не отображал все элементы. Конечно, я могу избежать этого ограничения, но признаюсь, что мне больше нравится мое решение, поэтому к тому решению я больше не возвращался.

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

  1. Выберите диапазон A3:A5.
  2. В поле имени (поле слева от строки формулы) введите название "Категория".
  3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать "Рабочий_Список". Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

  1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
  2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно "Проверка вводимых значений".
  3. В качестве типа данных выберите "Список".
  4. В качестве источника введите: =Категория (рисунок ниже).
  5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Результат следующий:

Раскрывающийся список для категории.

3. Создание зависимого выпадающего списка для подкатегории

Сейчас будет весело. Создавать списки мы умеем - только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите "Данные" / "Проверка данных», а в качестве типа данных - "Список".

В источник списка введите следующую формулу:

Вид окна "Проверка вводимых значений":

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

В нашем примере диапазон будет перемещаться по столбцу Подкатегория в рабочей таблице (G2:h25). Перемещение начнем от ячейки h3, которая также является первым аргументом нашей функции. В формуле ячейку h3 записали как абсолютную ссылку, потому что предполагаю, что мы будем использовать раскрывающийся список во многих ячейках.

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:h21, для Транспорта - диапазон h22: h25 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки h3 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки h3 (начинается с 4 ячейки от h3). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне - это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $h4: $h25. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

Скачать пример зависимого выпадающего списка в Excel

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

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

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

ПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0)

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

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

СЧЁТЕСЛИМН(F5:F39;B4;G5:G39;C4)

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

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

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

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

Скачать зависимые выпадающие списки в Excel

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

Создание зависимого раскрывающегося списка в Excel [Пошаговое руководство]

Смотреть видео — Создание зависимого раскрывающегося списка в Excel

формы ввода данных или информационные панели Excel.

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

Ниже приведен пример раскрывающегося списка Excel:

В приведенном выше примере я использовал элементы в A2:A6 для создания раскрывающегося списка в C3.

  Прочитано:  Вот подробное руководство о том, как создать раскрывающийся список Excel. 

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

В Excel они называются зависимыми раскрывающимися списками.

Ниже приведен пример того, что я имею в виду под зависимым раскрывающимся списком в Excel:

Вы можете видеть, что параметры в раскрывающемся списке 2 зависят от выбора, сделанного в раскрывающемся списке 1. Если я выберу «Фрукты», в раскрывающемся списке 1 отображаются названия фруктов, но если я выбираю овощи в раскрывающемся списке 1, мне отображаются названия овощей в раскрывающемся списке 2.

В Excel это называется условным или зависимым раскрывающимся списком.

Вот шаги для создания зависимого выпадающего списка в Excel:

  • Выберите ячейку, в которой вы хотите разместить первый (основной) раскрывающийся список.
  • Перейти к данным -> Проверка данных. Это откроет диалоговое окно проверки данных.
  • В диалоговом окне проверки данных на вкладке настроек выберите Список.
  • В поле Источник укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
  • Нажмите кнопку "ОК". Это создаст раскрывающийся список 1.
  • Выберите весь набор данных (в данном примере A1:B6).
  • Перейдите в Формулы -> Определенные имена -> Создать из выделения (или можно использовать сочетание клавиш Control + Shift + F3).
  • В диалоговом окне "Создать имя из выделенного" установите флажок "Верхняя строка" и снимите все остальные. При этом создается 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а названный диапазон овощей относится ко всем овощам в списке.
  • Нажмите кнопку "ОК".
  • Выберите ячейку, в которой вы хотите разместить зависимый/условный раскрывающийся список (E3 в этом примере).
  • Перейти к данным -> Проверка данных.
  • В диалоговом окне "Проверка данных" на вкладке настроек убедитесь, что выбран параметр "Список".
  • В поле Источник введите формулу =ДВССЫЛ(D3). Здесь D3 — это ячейка, содержащая основной раскрывающийся список.
  • Нажмите кнопку "ОК".

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

Загрузить файл примера

Как это работает?  – Условный раскрывающийся список (в ячейке E3) ссылается на =ДВССЫЛ(D3). Это означает, что когда вы выбираете "Фрукты" в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон "Фрукты" (через функцию ДВССЫЛ) и, следовательно, перечисляет все элементы в этой категории.

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

  • Причина в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет знак подчеркивания между словами. Например, когда вы создаете именованный диапазон с «Сезонные фрукты», он будет называться Season_Fruits в бэкэнде. Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ обеспечивает преобразование пробелов в символы подчеркивания .

Автоматический сброс/очистка содержимого зависимого раскрывающегося списка

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

Например, если вы выберете «Фрукты» в качестве категории, а затем выберите «Яблоко» в качестве элемента, а затем вернетесь и измените категорию на «Овощи», в зависимом раскрывающемся списке будет по-прежнему отображаться яблоко в качестве элемента.

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

Вот код VBA для очистки содержимого зависимого раскрывающегося списка:

 Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее Если Цель.Колонка = 4 Тогда Если Target.Validation.Type = 3 Тогда Приложение.EnableEvents = Ложь Цель.Смещение(0, 1).ClearContents Конец, если Конец, если обработчик выхода: Приложение.EnableEvents = Истина Выйти из подпрограммы End Sub 

Кредит за этот код относится к этому руководству Дебры по очистке зависимых раскрывающихся списков в Excel при изменении выбора.  

Вот как заставить этот код работать:

  • Скопируйте код VBA.
  • В рабочей книге Excel, где у вас есть зависимый раскрывающийся список, перейдите на вкладку «Разработчик» и в группе «Код» нажмите Visual Basic (вы также можете использовать сочетание клавиш — ALT + F11).
  • В окне редактора VB слева в обозревателе проектов вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.
  • Вставьте код в окно кода справа.
  • Закройте редактор VB.

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

Загрузите файл примера

Если вы не являетесь поклонником VBA, вы также можете использовать простой прием условного форматирования, который выделяет ячейку при наличии несоответствия. Это может помочь вам визуально увидеть и исправить несоответствие (как показано ниже).

Вот шаги t0 для выделения несоответствий в зависимых раскрывающихся списках:

  • Выберите ячейку с зависимыми раскрывающимися списками.
  • Перейти на главную -> Условное форматирование -> Новое правило.
  • В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек».
  • В поле формулы введите следующую формулу: =ЕОШИБКА(ВПР(E3,ИНДЕКС($A$2:$B$6,ПОИСКПОЗ(D3,$A$1:$B$1)),1,0))
  • Установить формат.
  • Нажмите кнопку "ОК".

Формула использует функцию ВПР, чтобы проверить, является ли элемент в зависимом раскрывающемся списке элементом из основной категории или нет. Если это не так, формула возвращает ошибку. Это используется функцией ЕОШИБКА для возврата ИСТИНА, которая указывает условному форматированию выделить ячейку.

Загрузите файл примера

Вам также могут понравиться следующие учебники по Excel :

  • Извлечение данных на основе выбора из раскрывающегося списка.
  • Создание выпадающего списка с поисковыми подсказками.
  • Выберите несколько элементов из раскрывающегося списка.
  • Создать несколько раскрывающихся списков без повторения.
  • Экономьте время с помощью форм ввода данных в Excel.

Видео: создание раскрывающихся списков и управление ими

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

Обучение Эксель 2013.

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

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

Выпадающие списки

  • Выпадающие списки
    видео
  • Настройки выпадающего списка
    видео
  • Ввод и сообщения об ошибках
    видео
  • Управление раскрывающимися списками
    видео

Следующий: Создайте сводную таблицу и проанализируйте свои данные

Ввод данных выполняется быстрее и точнее, если вы используете раскрывающийся список для ограничения записей, которые люди могут делать в ячейке. Когда кто-то выбирает ячейку, появляется стрелка вниз раскрывающегося списка, и он может щелкнуть ее и сделать выбор.

Создать раскрывающийся список

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

  1. Выберите ячейки, которые вы хотите содержать списки.

  2. На ленте щелкните ДАННЫЕ > Проверка данных .

  3. В диалоговом окне установите Разрешить на Список .

  4. Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), которые вы хотите добавить в раскрывающийся список, и щелкните OK .

Хотите больше?

Создать раскрывающийся список

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

Удалить раскрывающийся список

Блокируйте ячейки, чтобы защитить их

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


Learn more

Только новые статьи

Введите свой e-mail

Видео-курс

Blender для новичков

Ваше имя:Ваш E-Mail: