Как расширить раскрывающийся список в excel

Расширяемый Связанный список в EXCEL

history 21 января 2013 г.
    Группы статей

  • Выпадающий список
  • Имена
  • Проверка данных

В статье рассмотрен улучшенный вариант Связанного списка .

В случае, если содержание Связанного списка зависит от большого количества значений ячеек (>5), то нам потребуется создать такое же количество Именованных диапазонов . Занятие это мало увлекательное, поэтому лучше настроим Связанный список так, чтобы для него было удобно добавлять новые перечни элементов (См. файл примера ).

Наложим ограничение на порядок заполнения перечней стран для Регионов на листе Списки. Теперь порядок расположения названий Регионов в столбце A в точности должен соответствовать порядку заголовков перечней Стран в строке 1 (в предыдущем случае, см. статью Связанный список , этого не требовалось, главное, чтобы все названия Регионов присутствовали в заголовках, а порядок был не важен).

Для обеспечения этого требования введем в ячейку B 1 листа Списки формулу =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

Теперь названия заголовков столбцов будут автоматически браться из столбца А и однозначно соответствовать названиям Регионов .

Модифицируем файл примера из статьи Связанный список . Сначала удалим через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) все созданные ранее Имена .

Создадим Динамический диапазон для формирования Выпадающего (раскрывающегося) списка содержащего названия Регионов . Для этого необходимо:

  • нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
  • в поле Имя ввести Регионы ;
  • в поле Диапазон ввести формулу

Формула =списки!$A$2:ИНДЕКС(списки!$A:$A;СЧЁТЗ(списки!$A:$A)) подсчитывает количество введенных Регионов в столбце А на листе Списки (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются. Также для формирования динамического диапазона можно использовать функцию СМЕЩ() .

Теперь создадим Именованную формулу Позиция для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы =ПОИСКПОЗ(A5;Регионы;0) . Т.к. в формуле использована относительная адресация , то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица . Формула вернет порядковый номер выбранного Региона. Он же является порядковым номером столбца, содержащим названия стран, выбранного Региона в строке 1 .

Создадим именованную константу МаксСтран равную 20. Константа соответствует максимальному количеству стран в регионе (константу мы устанавливаем произвольно).

Создадим Именованный диапазон Выбранный_Регион для определения диапазона на листе Списки , содержащего страны выбранного Региона : =СМЕЩ(списки!$A$2;;Позиция;МаксСтран) . Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон списки!$B$2:$B$20 , содержащего все страны этого Региона .

В принципе, можно в качестве источника Связанного списка для ячеек из столбца B на листе Таблица указать =Выбранный_Регион , но тогдав Выпадающем списке будут появляться пустые строки. Для исключения этих строк, наконец, создадим последнюю Именованную формулу Страны для более точного определения диапазона на листе Списки , содержащего страны выбранного Региона : =СМЕЩ(списки!$A$2;;Позиция;СЧЁТЗ(Выбранный_Регион))

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

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

  • выделяем диапазон B5:B22 ;
  • вызываем инструмент Проверка данных ,
  • устанавливаем тип данных Список ,
  • в поле Источник вводим: =Страны .

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

Теперь заполняем следующую строку. Выбираем в ячейке A 6 РегионАфрика , вызываем связанный список в ячейке B 6 и опять балдеем: Чад, Танзания … Опять без пустых строк.

А теперь – основное отличие от Связанного списка : для добавления новых Регионов и их Стран теперь достаточно ввести новый Регион в столбец A (лист Списки ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона . И все!

Частными случаями Связанного списка являются:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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