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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комментарии

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

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

Источник: office-menu.ru

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

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

Функционал

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

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

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

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

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

Ни строчки 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.

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

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

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

Источник: perfect-excel.ru

Двухуровневый выпадающий список в 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 – так как это ширина нашего столбца с подгруппами.

Источник: micro-solution.ru

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

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

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

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

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

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

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

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

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

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

Источник: excel2.ru

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

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

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

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

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

Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А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

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

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

Источник: www.planetaexcel.ru

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

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

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

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

Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».

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

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

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

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

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

  1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов».
  6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

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

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

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

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

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

Возьмем три именованных диапазона:

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

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

Выбор нескольких значений из выпадающего списка Excel

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

  1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
  2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
  3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( “C2:C5” )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & “,” & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
  3. Жмем «Свойства» – открывается перечень настроек.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Источник: exceltable.com