Как сделать динамический выпадающий список в 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);
  • Вызываем инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ), устанавливаем тип данных Список, в поле Источник указываем =Сотрудники

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

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

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

Выпадающий список с наполнением

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

Способ 1. Если у вас Excel 2007 или новее

Простой и удобный способ почти без формул. Использует новую возможность последних версий Microsoft Excel начиная с 2007 версии — «Умные Таблицы». Суть его в том, что любой диапазон можно выделить и отформатировать как Таблицу. Тогда он превращается, упрощенно говоря, в «резиновый», то есть сам начинает отслеживать изменения своих размеров, автоматически растягиваясь-сжимаясь при добавлении-удалении в него данных.

Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home — Format as Table). Дизайн можно выбрать любой — это роли не играет:

Читать еще:  Как сделать скрипт в excel?

Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А1 со словом Сотрудники). Первая ячейка играет роль «шапки» и содержит название столбца. На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги:

Теперь выделите ячейки где вы хотите создать выпадающие списки (в нашем примере выше — это D2) и выберите в старых версиях Excel в меню Данные — Проверка (Data — Validation), а в новых нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

Смысл этой формулы прост. Выражение Таблица1[Сотрудники] — это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость — вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, живую.

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

Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter:

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

Способ 2. Если у вас Excel 2003 или старше

В старых версиях Excel до 2007 года не было замечательных «умных таблиц», поэтому придется их имитировать своими силами. Это можно сделать с помощью именованного диапазона и функции СМЕЩ (OFFSET), которая умеет выдавать ссылку на динамический диапазон заданного размера.

Откройте меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите Ctrl+F3. В открывшемся окне нажмите кнопку Добавить (New), введите имя диапазона (любое, но без пробелов и начинающееся с буквы, например — Люди) и в поле Ссылка (Reference) введите вот такую формулу:

Функция СЧЁТЗ (COUNTA) подсчитывает количество непустых ячеек в столбце с фамилиями, т.е. количество строк в диапазоне для выпадающего списка. Функция СМЕЩ (OFFSET) формирует ссылку на диапазон с нужными нам именами и использует следующие аргументы:

  • A2 — начальная ячейка
  • — сдвиг начальной ячейки по вертикали вниз на заданное количество строк
  • — сдвиг начальной ячейки по горизонтали вправо на заданное количество столбцов
  • СЧЁТЗ(A2:A100) — размер получаемого на выходе диапазона по вертикали, т.е. столько строк, сколько у нас занятых ячеек в списке
  • 1 — размер получаемого на выходе диапазона по горизонтали, т.е. один столбец

Теперь выделите ячейки, где вы хотите создать выпадающие списки, и выберите в старых версиях Excel в меню Данные — Проверка (Data — Validation). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

После нажатия на ОК ваш динамический список в выделенных ячейках готов к работе.

Как сделать динамический выпадающий список в excel?

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

Функционал

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

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

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

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

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

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

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

Файл примера

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

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

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

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

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

Единственный именованный диапазон ( 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).

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

Очень часто при использовании связки Выпадающий списокИменованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке — для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(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 098 скачиваний)

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

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

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

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

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

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

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