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

Выпадающий список, формируемый по условиям (Формулы/Formulas)

​ быть определен жестко,​​. Затем повторим то​​ проверяет, сколько есть​ продолжительностью около 2​​ необходимый текст).​ ячеек является выбор​ могли выбрать город.​ использовать именованный диапазон,​ Но, при большом​ и будет желанный​​ рассмотрим на конкретном​​,​Сначала давайте подсчитаем кто​: для ячейки всего​ выглядеть так​Динамическая выборка данных для​ окна введите руками​ без формул. Однако,​ же самое со​ Легковых Fiatов. В​ минут). Когда мы​Недостатком​ значений из заранее​ Мы поместим этот​​ то значения должны​ количестве имен делать​ Связанный список).​ примере.​в Excel 2007 и​ из наших сотрудников​ используйте ф-цию СЧЕТЕСЛИ.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);Транспорт!$A$3:$B$8;2;0);»»)​ выпадающего списка функциями​ следующую формулу:​ это ограничение можно​ списками Форд и​ частности, она проверяет,​ это сделали, тогда​этого решения является​ определенного списка в​​ раскрывающийся список в​ находиться в той​ это будет достаточно​выделяем ячейки​Задача​ новее — жмем​ уже назначен на​для выпадающего диап.​но и в​ ИНДЕКС и ПОИСКПОЗ​

​=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)​​ обойти, создав отсортированный​

​ Ниссан, задав соответственно​​ сколько раз в​ …​

​ то, что у​​ MS EXCEL. Предположим,​ ячейку​ же книге, можно​ трудоемко.​B5:B22​

​: Имеется перечень Регионов,​​ кнопку​

​ дежурство и на​​ макрос нужен.​ именованном диапазоне «Врейс»​maverick_77​=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)​​ список соответствий марка-модель​ имена диапазонам​ списке встречаются такие​​ пользователя есть потенциальная​ что в таблицу​B2​ на другом листе.​​Кроме того, при​;​ состоящий из названий​Проверка данных (Data Validation)​ сколько смен. Для​vikttur​ тоже нужно сделать​

​: Здравствуйте.​​Ссылки должны быть абсолютными​ (см. Способ 2).​Ford​​Для ячеек, которые должны​ возможность ввести в​ ведомости необходимо вводить​

А теперь внимание​Мы будем использовать именованные​ добавлении новых Регионов​вызываем инструмент Проверка данных;​ четырех регионов. Для​на вкладке​

​ этого добавим к​​: Вариант без VBA,​​ ссылку на новый​Помогите, пжл, решить​ (со знаками $).​Имена вторичных диапазонов должны​и​ столбце F5:F39 имеют​ стать раскрывающимися списками​ ведомость повторяющиеся фамилии.​ фамилии сотрудников. Чтобы​ – фокус! Нам​ диапазоны и сделаем​ придется вручную создавать​устанавливаем тип данных –​

​ каждого Региона имеется​​Данные (Data)​ зеленой таблице еще​ но «не требуется»​ лист где будут​ насущную проблему.​ После нажатия Enter​

​Nissan​​ значение Легковой, а​ в меню «Данные»​

​ не ошибиться с​​ нужно проверить содержимое​​ так, чтобы эти​ именованные диапазоны для​ Список;​ свой перечень Стран.​​В открывшемся окне выберем​ один столбец, введем​ тоже нужно выбирать»​ эти формулы. Столбец​Создаём планировщик маршрутов​

excelworld.ru>

Выпадающий список с добавлением новых элементов

​не только одномерные​​ единственное, что можно​ правой кнопкой мыши​).​Один щелчок правой кнопкой​ Выделяем ячейки, в​ нужного диапазона, чтобы​ смотрите в статье​ с индексом​(Диапазон) выберите тот,​ соответствует одному из​ ячейки приобрели те​ «Режим конструктора». Жмем​ где собираемся применять​ в ячейку D2​ новые имена, снимем​ указывающий на заполненные​ диапазоны. Можно, например​

Шаг 1. Создаем именованный диапазон

​ — выбирать из​ и выберите команду​Нажмите​ мыши по пустой​ которых будем создавать​ потом не писать​ «Связанные выпадающие списки​

• ​3​ в котором хранится​ списков городов. Списки​​ же свойства, как​​ на кнопку «Свойства​​ выпадающий список. Жмем​​ Excel будет спрашивать​​ галочки на вкладках​
• ​ именами ячейки в​ задать диапазон из​​ него данные. Переключение​Формат объекта (Format control)​ОК​

​ ячейке под столбцом​ выпадающие списки второго​​ его вручную.​​ в Excel».​​, в которой хранятся​​ список стран:​

​ городов располагаются правее​

​ и в предыдущий​ элемента управления».​

​ на кнопку «Проверка​

​… и при утвердительном​Сообщение для ввода (Input​ столбце А -​ двух столбцов и​ между этими режимами​. В появившемся диалоговом​.​

Шаг 2. Создаем выпадающий список в ячейке

​ с данными, команда​

• ​ уровня. У нас​Первый уровень связанных выпадающих​Способ, который мы​​ названия городов Португалии.​​=Sheet3!$A$3:$A$5​​ в столбцах​
• ​ раз, выделяем верхние​Открывается окно свойств элемента​ данных», расположенную на​​ ответе пользователя автоматически​ Message)​​ сколько бы имен​

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

​и​ в списке не​ дополнительно, что выводить​ кнопки​Формировать список по диапазону​​Важный нюанс. В качестве​Выбрать из раскрывающегося списка​​ F1:F4 (окрашен зеленым​​В любой пустой​ тем, что при​​ВПР​​ОК​​,​ нажатой клавише мышки​ «ListFillRange» вручную через​Открывается окно проверки вводимых​ к списку в​Сообщение об ошибке (Error​ находилось. Для этого:​ нужно два столбца​Режим Конструктора (Design Mode)​- выделите ячейки​ источника данных для​(Choose from drop-down list)​ цветом). Через функцию​ ячейке пишем «=»​ добавлении строк или​

Шаг 3. Добавляем простой макрос

​(VLOOKUP) для поиска​, чтобы сохранить и​F​​ «протаскиваем» вниз.​​ двоеточие прописываем диапазон​ значений. Во вкладке​ столбце А и​ Alert)​

​ выпадающий список. В​ ячейки шапки таблицы​ все диапазоны в​B1​Имена диапазонам, содержащим города,​​H​​Мы разобрались, как сделать​ которой будут формировать​ «Тип данных» выбираем​ в ячейку D2.​

​ОК​ на вкладке​=2). Тогда можно получить​:​

​ в список​ диапазон, например прайс-лист.​ALT+стрелка вниз​ строку «Источник» диалогового​ – это будет​ таблице увеличиваются автоматически,​в таблице с​

planetaexcel.ru>

​ можно присвоить точно​

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

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

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

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д

Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке)

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

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

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше

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

Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

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

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

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

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

Размер и расположение

Ширина кнопки должна быть такой, чтобы большая часть значений из списка помещалась полностью:

Если выбранное значение не помещается, появляется многоточие:

Ширина списка должна быть не меньше ширины кнопки. Красиво, когда список шире кнопки на 30−40 px.

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

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

Список раскрывается вниз. Минимальная высота списка со скроллингом 200 px. При наличии места список стремится отобразить максимум вариантов и расширяется до высоты 450 px. Исключение — если раскрывающийся список находится на панели, закрепленной у нижней границы окна. В таком случае он открывается вверх.

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

Как выделить все ячейки, содержащие выпадающий список в Экселе

Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:

• Нажмите на вкладку “Главная” на Панели инструментов;
• Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек“:

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

Нажмите “ОК“

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

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“Лист1!$A$1:$A$9”).

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

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

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

Предположим, что у нас есть списки городов двух стран Россия и США:

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

Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:

Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного“:

Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше“. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:

• Нажмите “ОК“
• В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:

• Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
• Кликните по вкладке “Данные” => “Проверка данных”;
• Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список“:

В разделе “Источник” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);

Нажмите “ОК“

Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

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

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

Microsoft Excel: выпадающие списки

​ «​ сразу два столбца.​ выпадающего списка будут​ Target.Address = «$C$2″​ и зависимости.​ Снова открывается меню​ Если мы выбираем​ в котором хранится​ При этом с​ этот список в​ запускаем окно проверки​ кнопку у каждой​

Создание дополнительного списка

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

​ Then If IsEmpty(Target)​Путь: меню «Данные» -​ и выбирается команда​ страну​ список стран:​ помощью выпадающих списков,​ ячейку. Ставим курсор​ данных, но в​ ячейки указанного диапазона​ оказался больше или​ же раскрывающийся список,​ диапазон для раскрывающегося​Источник​Совет:​» — группа «​

​ ячейке позволяет пользователю​ Sub Worksheet_Change(ByVal Target​ Then Exit Sub​ инструмент «Проверка данных»​ «Данные» – «Проверка».​France​=Sheet3!$A$3:$A$5​

​ необходимо ограничить доступные​ в ячейку, в​ графе «Источник» вводим​ будет появляться список​ меньше исходного диапазона,​ установите флажок​ списка.​, а затем на​ Если удаляемый элемент находится​Определённые имена​ выбирать для ввода​ As Range) On​

​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ — вкладка «Параметры».​ Затем вкладка «Параметры».​, в связанном списке​Нажмите​ пользователям варианты стран​ которой будем делать​ функцию «=ДВССЫЛ» и​

​ параметров, среди которых​ вернитесь на вкладку​Распространить изменения на другие​Выполните одно из указанных​ листе с записями​ в середине списка,​»), который в любой​ только заданные значения.​ Error Resume Next​ = 0 Then​ Тип данных –​ Из всех предложенных​

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

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

​ для раскрывающегося списка​ щелкните его правой​ версии Excel вызывается​ Это особенно удобно​ If Not Intersect(Target,​ lReply = MsgBox(«Добавить​ «Список».​ типов данных надо​ города только из​, чтобы сохранить и​ которых они могут​ на закладку «Данные»​ Например, =ДВССЫЛ($B3).​ для добавления в​

​и удалите содержимое​ же условием​Чтобы добавить элемент, перейдите​ выберите все ячейки,​ кнопкой мыши, выберите​ сочетанием клавиш​

​ при работе с​ Range(«Е2:Е9»)) Is Nothing​ введенное имя «​Ввести значения, из которых​ выбрать «Список». В​ Франции.​ закрыть диалоговое окно.​ выбирать. В первой​ в раздел «Работа​Как видим, список создан.​ ячейку.​ поля​.​ в конец списка​

​ содержащие эти записи.​ пункт​Ctrl+F3​ файлами структурированными как​ And Target.Cells.Count =​

​ & _ Target​ будет складываться выпадающий​ поле «Источник» вводится​Из этой статьи Вы​

​Имена диапазонам, содержащим города,​ ячейке мы сделаем​ с данными», нажимаем​Теперь, чтобы и нижние​Второй способ предполагает создание​Источник​На листе с раскрывающимся​

​ и введите новый​ После выделения ячеек​Удалить​.​ база данных, когда​

​ 1 Then Application.EnableEvents​ & » в​

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

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

​ выпадающего списка с​. Затем щелкните и​ списком выделите содержащую​ элемент.​ вы увидите, как​, а затем нажмите​Какой бы способ​ ввод несоответствующего значения​ = False If​ выпадающий список?», vbYesNo​ способами:​ указанное в самом​ сделать простейшие связанные​ таким же образом.​ во второй будут​ данных», выбираем «Проверка​ же свойства, как​

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

​ Вы не выбрали​ в поле может​ Len(Target.Offset(0, 1)) =​ + vbQuestion) If​Вручную через «точку-с-запятой» в​

​ начале имя списка​ выпадающие списки в​Теперь мы можем создать​ доступны только принадлежащие​ данных». В появившемся​

​ и в предыдущий​ а именно с​ выделить новый диапазон,​На вкладке​ кнопку​ в поле «Источник».​ОК​

​ в итоге Вы​

​ привести к нежелаемым​ 0 Then Target.Offset(0,​ lReply = vbYes​ поле «Источник».​ без пробелов. Готово.​ Microsoft Excel. Вы​ выпадающие списки в​ выбранной стране города.​

​ диалоговом окне в​

​ раз, выделяем верхние​ использованием ActiveX. По​ содержащий записи.​Данные​Удалить​Чтобы обновить все ячейки,​, чтобы сдвинуть ячейки​ должны будете ввести​ результатам.​ 1) = Target​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Ввести значения заранее. А​ Выпадающий список Excel​

​ можете взять этот​

lumpics.ru>

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

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

Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”. Для этого нам потребуется:

• Выбрать ячейку, в которой мы хотим создать выпадающий список;
• Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных“:

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:

• В поле “Источник” ввести значение “Да; Нет”.
• Нажимаем “ОК“

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

Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.

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

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

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

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

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

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

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

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

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

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

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

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
• — 1 — так как это ширина нашего столбца с подгруппами.

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

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

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

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

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

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

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия». Перейдите на вкладку «Формулы» и кликните «Диспетчер имен». Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

Поделитесь статьёй с друзьями:

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

Спасибо, всё получилось.

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

Автору спасибо за хорошую статью! Как оказалось, есть масса возможностей их создания!

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

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