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

Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel

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

Видеоурок к статье:

Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда , каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо , одежда – Верхняя_одежда и Спортивные_товары .

Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.

На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты , содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо , содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).

Остальные данные организуются по такому же принципу.

Список 1 уровня

Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3

Создать список полей конкретной таблицы:

=ДВССЫЛ(» Таблица1 «&»[#Заголовки]»)

Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[ Поле1 ]»)

Список 2 уровня

Создать список полей таблицы, выбираемой в вышестоящем списке:

=ДВССЫЛ( A1 &»[#Заголовки]»)

Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[«& А1 &»]»)

Список 3 уровня

Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &»[«& B1 &»]»)

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

Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в надстройке SubEx для Excel , которая моментально сформирует за вас нужные формулы!

Всего возможны три вида выпадающих списков:

1 уровня (первичный, не связанный ни с чем список)

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

3 уровня (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)

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

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

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

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

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

Подробный пример создания 6-уровневых выпадающих списков на примере номенклатуры товаров магазина приведен в видеоуроке!

Связанный список в MS EXCEL

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

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

  • ОтделСотрудники отдела. При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
  • Город – Улица – Номер дома. При заполнении адреса проживания можно из списка выбрать город, затем из списка всех улиц этого города – улицу, затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен только двухуровневый связанный список. Многоуровневый связанный список рассмотрен в одноименной статье Многоуровневый связанный список.
Создание иерархических структур данных позволяет избежать неудобств выпадающих списков связанных со слишком большим количеством элементов.
Связанный список можно реализовать в EXCEL, с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список (пример создания приведен в данной статье) или с помощью элемента управления формы Список (см. статью Связанный список на основе элемента управления формы).

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

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

Таблицу, в которую будут заноситься данные с помощью Связанного списка, разместим на листе Таблица. См. файл примера Связанный_список.xlsx

Список регионов и перечни стран разместим на листе Списки.

Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран (В1:Е1).

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны). Быстрее всего это сделать так:

  • выделитьячейки А1:Е6 на листе Списки(т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран);
  • нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
  • Убедиться, что стоит только галочка «В строке выше»;
  • Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Читать еще:  Как сделать общий доступ к файлу excel на гугл диске?

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица, для ячеек A5:A22 сформируем выпадающий список для выбора Региона.

  • выделяем ячейки A5:A22;
  • вызываем инструмент Проверка данных;
  • устанавливаем тип данных – Список;
  • в поле Источник вводим: =Регионы

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

  • выделяем ячейки B5:B22;
  • вызываем инструмент Проверка данных;
  • устанавливаем тип данных – Список;
  • в поле Источник вводим: =ДВССЫЛ(A5)

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A5 РегионАмерика, вызываем связанный список в ячейке B5 и балдеем – появился список стран для Региона Америка: США, Мексика

Теперь заполняем следующую строку. Выбираем в ячейке A6 РегионАзия, вызываем связанный список в ячейке B6 и опять балдеем: Китай, Индия

Необходимо помнить, что в именах нельзя использовать символ пробела. Поэтому, при создании имен, вышеуказанным способом, он будет автоматически заменен на нижнее подчеркивание «_». Например, если вместо Америка (ячейка В1) ввести «Северная Америка» (соответственно подкорректировав ячейку А2), то после нажатия кнопки Создать из выделенного фрагмента будет создано имя «Северная_Америка». В этом случае формула =ДВССЫЛ(A5) работать не будет, т.к. при выборе региона «Северная Америка» функция ДВССЫЛ() не найдет соответствующего имени. Поэтому формулу можно подкорректировать, чтобы она работала при наличии пробелов в названиях Регионов: =ДВССЫЛ(ПОДСТАВИТЬ(A5;» «;»_»)) .

Теперь о недостатках.
При создании имен с помощью кнопки меню Создать из выделенного фрагмента, все именованные диапазоны для перечней Стран были созданы одинаковой длины (равной максимальной длине списка для региона Европа (5 значений)). Это привело к тому, что связанные списки для других регионов содержали пустые строки.

Конечно, можно вручную откорректировать диапазоны или даже вместо Именованных диапазонов создать Динамические диапазоны. Но, при большом количестве имен делать это будет достаточно трудоемко.
Кроме того, при добавлении новых Регионов придется вручную создавать именованные диапазоны для их Стран.

Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка. Рассмотрим этот подход в другой статье: Расширяемый Связанный список.

Выпадающий список в Excel

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

Выпадающий список в Excel

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

Для начала рассмотрим самый простой способ, который поможет вам сделать раскрывающийся список. Чтобы осуществить его, делайте всё как в инструкции ниже:

Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

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

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»

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

    вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;

В поле «Источник» вводим значения нужных ячеек
с указанием диапазона. Чтобы сделать это, вбейте в поле ввода адрес первой и последней ячейки через двоеточие. Например, если вы хотите сложить в список значения всех ячеек от «A1» до «A7», то вводите «=$A$1:$A$7».

Вбиваем в поле ввода адрес первой и последней ячейки через двоеточие, нажимаем «ОК»

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

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

Результат сделанного выпадающего списка

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

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

Видео — Создание выпадающих списков в Excel

Раскрывающийся список с подстановкой данных

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

    Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».

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

Выбираем любой понравившийся стиль
Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона. Здесь важно установить галочку возле «Таблица с заголовками», так как наличие заголовка в данном случае играет ключевую роль.

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

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

Выделяем левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и переходим во вкладку «Данные»
В поле ввода «Источник» вам нужно вписать функцию с синтаксисом «=ДВССЫЛ(“Имя таблицы[Заголовок]”)». На скриншоте указан более конкретный пример.

В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы[Заголовок]”)», подставляя свои данные, как на примере

Читать еще:  Как сделать горизонтальную линию в excel?

Итак, список готов. Выглядеть он будет вот так.

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

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

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

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

Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

Зависимые раскрывающиеся списки

Чтобы создать зависимые выпадающие списки, следуйте дальнейшей пошаговой инструкции:

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

Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»

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

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

Создаем таким же способом остальные диапазоны
Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте.

В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»

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

В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»

Результат выпадающего связанного списка

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

Видео — Связанные выпадающие списки: легко и быстро

Сводные таблицы Excel

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

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь – преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д.

Возьмем, к примеру, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Список моделей автомобилей

Выделим весь список моделей Toyota (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager), кнопка Создать (Create). Затем повторим то же самое со списками моделей Ford и Nissan, задав имена диапазонам Ford и Nissan соответственно.

При задании имен помните о том, что они не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например, Land Rover), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Land_Rover).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (ячейки A1:C1 в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

Теперь создадим второй (зависимый) выпадающий список, в котором будут отображаться только модели выбранной в первом списке марки. Так же как в предыдущем случае, откройте окно Проверки данных, но в поле Источник нужно будет ввести вот такую формулу: =ДВССЫЛ(F3) или =INDIRECT(F3) , где F3 – адрес ячейки с первым выпадающим списком (замените на свой).

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

Минусы такого способа:

  • В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны, задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
  • Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»)) .
  • Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Читать еще:  Как в excel сделать нумерацию страниц не с первой страницы?

Список соответствий и функции СМЕЩ и ПОИСКПОЗ

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

  • дать имя диапазону D1:D3 (например, Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas).
  • выбрать на вкладке Данные (Data) команду Проверка данных (Data Validation).
  • выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source) =Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

  • Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name Manager) на вкладке Формулы (Formulas).
  • Создайте новый именованный диапазон с любым именем (например, Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:
    =СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)
    =OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1) .

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов – не пугайтесь.

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах) .

Функция СМЕЩ умеет выдавать ссылку на диапазон нужного размера

  • начальная ячейка – берем первую ячейку нашего списка, т.е. А1.
  • сдвиг_вниз – считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А).
  • сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В).
  • размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – марок авто (G7).
  • размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями.

В итоге должно получиться что-то вроде этого:

Ссылки должны быть абсолютными (со знаками $)

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

  • выделяем ячейку G8.
  • выбираем на вкладке Данные (Data) команду Проверка данных (Data Validation).
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак «равно» и имя нашего диапазона, т.е. =Модели .

Создаем связанные выпадающие списки в Excel – самый простой способ!

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?

Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:

Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D, F и H. Так, например, рядом с France стоит индекс 2, который соответствует списку городов 2. Позже Вы увидите, как этот индекс будет использован.

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

Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).

Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).

В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:

Нажмите ОК, чтобы сохранить и закрыть диалоговое окно.

Имена диапазонам, содержащим города, можно присвоить точно таким же образом.

Теперь мы можем создать выпадающие списки в тех ячейках, где планировали выбирать данные. Выделите ячейку B1 (в ней мы будем выбирать страну), откройте вкладку Data (Данные), нажмите Data Validation (Проверка данных), а затем в выпадающем меню выберите Data Validation (Проверка данных).

Откроется диалоговое окно Data Validation (Проверка вводимых значений).

Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле “=Country” и жмите ОК. Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2. А теперь внимание – фокус! Нам нужно проверить содержимое ячейки с названием страны (ячейка B1), чтобы получить индекс соответствующий базе данных с городами. Если пользователь выберет Portugal, то мы должны обратиться к базе с индексом 3, в которой хранятся названия городов Португалии. Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:

Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.

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

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

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

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