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

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

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

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

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

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

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

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

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

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

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

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

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

Рабочая начальная таблица Excel

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

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

Интересно почитать: Как добавить вспомогательную ось в графике в excel

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

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

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

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

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

Изберите спектр A3:A5.
В поле имени (поле слева от строчки формулы) введите заглавие «Категория».
Подтвердите при помощи клавиши Enter.

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

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

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

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

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

Раскрывающийся перечень для группы.

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

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

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

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

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

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

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

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

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

Интересно почитать: Снять защиту листа excel

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

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

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

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

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

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

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

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

Упрощает ли внедрение раскрывающегося перечня в Microsoft Excel ввод данных вам либо ваших коллег? Если вы произнесли «да» и желаете пойти далее, вы сможете так же просто сделать зависимый раскрывающийся перечень.

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

Начиная

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

Интересно почитать: Excel диапазон

Так как настройка второго перечня производится в согласовании с этим же главным действием, мы начнем с него. Потом мы перейдем к настройке зависимости.

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

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

Наши отделы включают в себя маркетинг, деньги и людские ресурсы (HR), в любом из которых работает по три сотрудника. Эти сотрудники — это те, кого нам необходимо добавить и именовать.

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

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

В нашем примере мы назовем наши группы отделами в нашем первом перечне: маркетинг, деньги и HR.

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

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

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

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

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

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

= КОСВЕННО (6 миллиардов руб.)

Примечание. Функция ДВССЫЛ «возвращает ссылку, обозначенную в текстовой строке». Доп сведения о данной нам функции см. В Служба саппорта Microsoft страничка.

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

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