Excel выпадающий список динамический

Динамический выпадающий список в MS EXCEL

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

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

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

  • создаем на листе Список исходный перечень элементов Выпадающего (раскрывающегося) списка, например перечень сотрудников компании;
  • на листе ДинамическийСписок определяем диапазон для ввода сотрудников, например, выдвинутых на премию. Диапазон должен содержать неповторяющиеся фамилии (иначе кто-то получит 2 или 3 премии!) и все сотрудники должны быть из компании (иначе премию получат чужие!)
  • создаем на листе Список модифицированный перечень сотрудников, не содержащий фамилии, которые уже введены на листе ДинамическийСписок;
  • создаем обычный Выпадающий список на основе модифицированного перечня сотрудников, созданного на предыдущем шаге.
  • На листе Список, в столбце А создадим исходный перечень фамилий сотрудников. Введем заголовок – Сотрудники.

  • На листе ДинамическийСписок определяем диапазон, в который будут вводиться фамилии сотрудников с помощью Выпадающего списка (Ведомость для премии). Это диапазон A3:A16.
  • На листе Список, в столбце B, напротив каждого значения из исходного перечня введем формулу = ЕСЛИ(СЧЁТЕСЛИ(ДинамическийСписок!$A$3:$A$16;A2);»»;СТРОКА())

Формула ищет уже введенные в диапазон A3:A16 на листе ДинамическийСписок фамилии и, в случае успеха, возвращает значение Пустой текст («»). Если значение не найдено, то выводится номер строки, в которой находится формула;

  • На листе Список, в ячейке С2, введем Формулу массива = ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$9; НАИМЕНЬШИЙ($B$2:$B$9;СТРОКА(1:1))-СТРОКА($A$1));»») После ввода вместо ENTER нажмите CTRL+SHIFT+ENTER.

Разберем работу формулы подробнее. Функция НАИМЕНЬШИЙ() сортирует по возрастанию столбец B и для каждой строки выводит значение. Функция ИНДЕКС() , в зависимости от результата функции НАИМЕНЬШИЙ() , извлекает фамилии из исходного перечня.
Формулу скопируйте вниз до конца исходного перечня сотрудников. В результате в столбце С формируется перечень фамилий, еще не введенных на листе ДинамическийСписок. Это как раз наша цель.
Промежуточный результат легко увидеть с помощью клавиши F9 (например, выделите в строке формул НАИМЕНЬШИЙ($B$2:$B$9;СТРОКА(1:1)) , нажмите F9 — вместо формулы отобразится ее результат).

Чтобы наш Динамический выпадающий список содержал именно столько позиций, сколько имеется фамилий в столбце С, создадим Динамический диапазон:

  • На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
  • В поле Имя введите: Сотрудники;
  • В поле Область введите Книга;
  • В поле Диапазон введите формулу

Примечание: для числовых значений используйте = СМЕЩ(Cписок!$C$2;;;СУММПРОИЗВ(—ЕЧИСЛО(Cписок!$C$2:$C$29)))

Завершающий шаг: создаем Динамический выпадающий список:

  • На листе ДинамическийСписок выделим диапазон, в который будут вводиться значения с использованием Динамического выпадающего списка (A3:A16);
  • Вызываем инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ), устанавливаем тип данных Список, в поле Источник указываем =Сотрудники

Протестируем наше решение.
На листе ДинамическийСписок с помощью выпадающего списка выберем фамилию Сидоров.

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

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

Excel выпадающий список динамический

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

Функционал

Поддержка любого уровня вложенности

Обеспечение целостности данных. Вы не можете изменить уровень N, если уже определили уровень N+1 и выше. Иными словами, если вы выбрали фрукт яблоки , а затем сорт яблок — Антоновка , то, не очистив ячейку с Антоновкой , вы не измените яблоки на, скажем, груши .

Преимущества

Простота инфраструктуры решения

Удобное ведение справочников

Ни строчки VBA кода. Вы можете использовать файлы типа XLSX

Файл примера

Скачать пример

Скачать пустой шаблон

Обязательные условия для работы решения

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

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

Читать еще:  Зависимые выпадающие списки в excel как сделать

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

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

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

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

Как, чёрт побери, это работает?!

Повторюсь, что все делает формула в ИД SubList . Формула, на первый взгляд, выглядит страшновато, но давайте попробуем разобраться. Кстати, если вы не чувствуете пока в себе сил вникать в нюансы работы таких формул, то и не надо. Решение в любом случае готово к употреблению.

Всё относительно

Первое, что вы должны знать об этой формуле, это то, что она относительная. Она содержит относительные ссылки, поэтому, если вы встанете на ячейку B3 листа Smart (так называется лист, содержащий таблицу tblEntry ) и посмотрите на формулу в ИД SubList , то она будет выглядеть так:

=ЕСЛИ( ЕПУСТО( Smart! C3 ); ЕСЛИ( tblEntry[#Заголовки] Smart! B:B = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart! A3 ; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart! A3 ; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )

а, если перейдёте в C5 и ещё раз посмотрите, то увидите уже это:

=ЕСЛИ( ЕПУСТО( Smart! D5 ); ЕСЛИ( tblEntry[#Заголовки] Smart! C:C = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart! B5 ; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart! B5 ; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )

Декомпозиция

Посмотрим из каких функциональных блоков состоит формула и за что они отвечают. На примере ячейки B3 .

Обратите внимание на первую, внешнюю формулу ЕСЛИ . У ней только 2 параметра: условие — блок 1 на рисунке, и параметр, возвращаемый в случае истинности блока 1 (большой блок 2 ), а вот параметра для ложного условия нет вообще.

Блок 1 при помощи функции ЕПУСТО проверяет ячейку справа от текущей и, если она не пустая, то работа формулы на этом прекращается. Она возвращает Null в качестве источника строк для функции Проверка данных и выпадающий список не срабатывает.

Если ячейка справа пуста, то значит необходимо определить, какой список необходимо показать, надо его найти в таблице tblLists и вернуть все его строки. За всё это отвечает блок 2 , также обёрнутый в формулу ЕСЛИ .

Параметр-условие формулы ЕСЛИ ( блок 3 ) определяет имя столбца таблицы tblEntry над активной ячейкой. Определяет через операцию пересечения диапазонов (символ пробела) при помощи следующей конструкции tblEntry [#Заголовки] Smart ! B:B . Результатом этой операции для B3 становится ячейка B2 . Далее идёт выяснение того, является ли этот заголовок заголовком корневого списка. Если это так (а для B3 это так), то выполняется блок 4 , а если нет, то блок 5 .

Блок 4 формирует диапазон — набор строк для корневого списка. Нам необходимо использовать СМЕЩ и СЧЁТЗ , так как в каждом столбце количество непустых строк может быть разное. Тут всё стандартно: первый параметр задаёт точку отсчёта (не надо пугаться, что точка отсчёта задана диапазоном, так как формула всё равно возьмёт первую ячейку), второй параметр — смещение по строкам (у нас это 1), третий — смещение по столбцам (опущен — у нас ширина массива 1 столбец), четвёртый параметр — число строк (их просто считаем через СЧЁТЗ ), пятый параметр — число столбцов (опущен).

Блок 5 формирует набор строк для случая промежуточного (не корневого) списка. Это означает, что надо взять значение ячейки слева от текущей и искать столбец с таким же именем, а потом действовать примерно так же, как в блоке 4 . Всё это присутствует в блоке 5 : мы видим опять СМЕЩ , первый параметр tblLists — это в качестве точки отсчёта берётся первая ячейка таблицы без заголовка (это Ref ! A2 ), смещение по строкам — ноль, так как мы уже стоим на области данных, смещение по столбцам определяется в блоке 6 , высота диапазона определяется в блоке 7, ширина диапазона равна 1.

Читать еще:  Недопустимое имя сводной таблицы в excel

Обратите внимание, что блок 6 и блок 8 одинаковые — ведь мы определяем столбец динамически.

Если для вас функции СМЕЩ и СЧЁТЗ не пустой звук, то прочтя 2-3 раза раздел про декомпозицию, вы должно быть поняли, как это всё работает. Если же нет, то не расстраивайтесь, — всему своё время. Почитайте про структурные формулы умных таблиц, описания функций СМЕЩ , СЧЁТЗ , ПОИСКПОЗ и через некоторое время, когда знания улягутся в голове, вернитесь к этому описанию снова.

Данный рецепт подсмотрен мною для вас на сайте известного индийского экселиста Chandoo (Purna Duggirala).

Динамические выпадающие списки Excel

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

Говоря о динамических выпадающих списках, подразумевается 2 варианта:

Растущий выпадающий список

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

Рассмотрим пример создания такого списка.

На любом листе размещается перечень будущих пунктов. Далее стандартным образом настраивается правило проверки данных: Вкладка «Данные» -> Область «Работа с данными» -> Кнопка «Проверка данных» -> Тип данных «Список». Для источника необходимо создать следующую формулу:

=СМЕЩ($A$1;;;СЧЁТЗ($A:$A)), где:

  • $A$1 – ячейка со значением первого пункта списка;
  • $A:$A – столбец с перечнем всех пунктов списка.

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

Параметры динамического выпадающего списка:

Связанные (зависимые) выпадающие списки

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

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

  • Бытовая_техника (пробел в названии специально заменен нижним подчеркиванием, т.к. диспетчер имен, который будет использован далее, не принимает символ пробела);
  • Электроника;
  • Мебель.

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

В завершении задаем для ячеек созданные нами связанные выпадающие списки с помощью проверки данных (вкладка «Данные» -> Область «Работа с данными»).

Для главного списка источником достаточно указать ссылку на диапазон, а для зависимых указывается формула:

=ДВССЫЛ($A$1), где:

  • $A$1 – ячейка с главным списком.

На этом зависимые выпадающие списки созданы, а так выглядит готовый результат:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

Комментарии

Добрый день, Александр!

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Скопировать таблицу из excel в word

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

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

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

Динамические именованные диапазоны

Очень часто при использовании связки Выпадающий списокИменованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке — для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(Ctrl+F3), задаем имя диапазона и в поле Диапазон (Refers to) пишем формулу:

Вариант с формулой СМЕЩ
=СМЕЩ(Лист2! $A$1 ;;;СЧЁТЗ(Лист2! $A$1:$A$1000 );)
=OFFSET(Лист2! $A$1 . COUNTA(Лист2! $A$1:$A$1000 ),)

  • Где Лист2! $A$1 — первая ячейка значений для выпадающего списка
  • а Лист2! $A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка. Если значений может быть более 1000, то необходимо увеличить диапазон $A$1:$A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей

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

Вариант с формулой ИНДЕКС
=Лист2!$A$1:ИНДЕКС(Лист2! $A$1:$A$1000 ;ПРОСМОТР(2;1/(Лист2! $A$1:$A$1000 <>«»);СТРОКА(Лист2! $A$1:$A$1000 )))
=Лист2!$A$1:INDEX(Лист2! $A$1:$A$1000 ,LOOKUP(2,1/(Лист2! $A$1:$A$1000 <>«»),ROW(Лист2! $A$1:$A$1000 )))

Изменяемые ссылки для этой формулы такие же, как и в случае с вариантом через СМЕЩ:

  • Лист2! $A$1 — первая ячейка значений для выпадающего списка
  • Лист2! $A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка

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

Примечание: созданные таким образом диапазоны нельзя использовать в составе функции ДВССЫЛ (INDIRECT) для создания зависимых выпадающих списков — список просто не будет работать

Tips_Lists_Dinamic_Range.xls (37,5 KiB, 6 032 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Есть вариант ещё интереснее.
Размещаем список в «Таблице» (ВставкаТаблица)
Создаём Диапазон ссылающийся на часть таблицы с данными например =Таблица1[Почта](если заголовок списка «Почта» в таблице «Таблица1»)
Далее всё как указано в основном описании по созданию списка в третьем варианте отсюда: http://www.excel-vba.ru/chto-umeet-excel/vypadayushhie-spiski/

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

Поделитесь своим мнением

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

Похожие статьи

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