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

Правильное введение данных

Алгоритм, как сделать выпадающий список в «Excel» рассмотрели, но следует обратить внимание на ряд дополнительных моментов, корректного внесения информации:

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

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

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

Если у Вас остались вопросы по теме «Что такое выпадающий список в Excel и как его создать?», то можете задать их в комментария

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

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

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

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

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

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

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

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

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

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

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

При работе с перечнем элементов, расположенным в другой книге, файл Источник.xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки.

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

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

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

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

Создание списка с применением инструментов разработчика

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

1. В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
2. В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
3. Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
4. Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
5. Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
6. Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
7. В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
8. Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
9. В результате мы получаем выпадающий список с заранее определенным перечнем.
10. Чтобы вставить его в несколько ячеек, наводим курсор на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

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

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

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

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

Подробнее о сводных таблицах читайте в этой статье.

Делаем выпадающий список в ячейке эксель с выбором по первой букве

Быстро понимаешь, что хорошо бы иметь возможность выбора из списка по первой букве, тогда процесс идет гораздо быстрее. Следующий способ познакомит вас с разработкой в эксель. Сначала Включим в настройках Excel режим конструктора через «Параметры».

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

• ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
• ListRows — количество отображаемых строк в списке;
• Font — выбираем шрифт, размер, стиль;
• ForeColor и BackColor — цвет текста и фона;
• ListRow — количество возвращаемых строк в списке;
• PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Процесс создания списка

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

1

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

1. Самостоятельное указание элементов списка через точку с запятой в поле «Источник», расположенного на той же вкладке того же диалогового окна.

   2

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

   3

3. Указание именованного диапазона. Метод, повторяющий прошлый, но только необходимо предварительно назвать диапазон. 4

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

На основе данных из перечня

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

5

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

Выбрать ячейку, отведенную под будущий перечень.
На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных». 6

Найти пункт «Тип данных» и переключить значение на «Список». 7

В поле, обозначающем опцию «Источник», ввести нужный диапазон

Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась. 8

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

С ручной записью данных

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

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

1. Нажать по ячейке, отведенной под перечень.
2. Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».

   9

3. Снова выбираем тип «Список».

   10

4. Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

11

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

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

Поделиться «3 способа как в экселе сделать выпадающий список»

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

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

24

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

1. Создать 1-й перечень с именами диапазонов.

   25

2. В месте ввода источника один за одним выделяются требуемые показатели.

   26

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

Теперь все готово.

27

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

Связанные выпадающие списки.xls (216,5 KiB, 1 364 скачиваний)

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2 . В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки . А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 — т.е. список выбранной категории продуктов. Например выбрали в А2 значение Овощи — в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо — в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка . И т.д.

Подготовка Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить — если сами списки расположено на листе, отличном от того, на котором списки выпадающие — то обязательно создавать именованный диапазон с назначением области действия — Книга. В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках. Если ваши категории содержат пробел — необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий. В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data) —Проверка данных (Data validation) —Список (List) . Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2 ), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT)

Просто записываем эту формулу в поле Источник (Source) : =ДВССЫЛ( $A2 ) На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно)

Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги Сами списки товара могут находится и в другой книге

Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так: =ДВССЫЛ(«‘Лист1’!»&$A$1) На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы

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

Ограничения : данный способ создания списков хорош, но не обошлось и без ложки дегтя. Даже двух:

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

И ничего с этими ограничениями не поделать при подобном подходе.

Tips_Lists_Connect_Validation.xls (26,5 KiB, 16 309 скачиваний)

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

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

Здравствуйте! В связанных списках для заголовков одной таблицы использую ДВССЫЛ таким образом =ДВССЫЛ(«Таблица»), дабы при добавлении нового столбца или изменении заголовков в динамической таблице автоматом отображались данные изменения и добавления. Но в некоторых случаях происходит некое зависание, данный список не раскрывается, он будто не видит данных которые необходимо отобразить. Подскажите в чем может быть причина?

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

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

Как использовать раскрывающийся список на защищенном листе в Excel?

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

Дополнительные руководства для раскрывающегося списка …

Использовать раскрывающийся список на защищенном листе в Excel

1. Во-первых, вам нужно выбрать все ячейки с раскрывающимся списком на листе, который нужно защитить. Пожалуйста, нажмите Найти и выбрать > проверка достоверности данных под Главная меню.

2. Затем все ячейки с выпадающим списком сразу выбираются на текущем листе. Щелкните их правой кнопкой мыши и выберите Формат ячеек в контекстном меню. Смотрите скриншот:

3. в Формат ячеек диалогового окна, перейдите к Protection вкладка, снимите флажок Заблокированный поле, а затем щелкните OK кнопку.

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

Легко создать раскрывающийся список с флажками в Excel:

Освободи Себя Раскрывающийся список с флажками полезности Kutools for Excel может помочь вам легко создать раскрывающийся список с флажками в указанном диапазоне, текущем листе, текущей книге или всех открытых книгах в зависимости от ваших потребностей.Загрузите полнофункциональную 30-дневную бесплатную версию Kutools for Excel прямо сейчас!

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

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

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

Автоматическое заполнение других ячеек при выборе значений в раскрывающемся списке Excel Допустим, вы создали раскрывающийся список на основе значений в диапазоне ячеек B8: B14. При выборе любого значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне ячеек C8: C14 автоматически заполнялись в выбранной ячейке. Для решения проблемы методы, описанные в этом руководстве, окажут вам услугу.

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

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

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

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

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

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

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

Примеры выпадающих списков в Excel

#1. Стандартный

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

Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».

Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

#2. Список с подстановкой данных

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

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

Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:

#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel. 

Подробнее о том, что такое  «Умные таблицы» и как с ними работать — на наших курсах. 

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

Чтобы выпадающий список в Excel стал динамическим, выделяем любую из ячеек, где он находится, переходим в раздел «Проверка данных». Нам подтянется текущее условие проверки. 

В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ(«Товары»). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

#2. Диспетчер имен. Этот способ аналогичный предыдущему, только с тем отличием, что имя будет присвоено диапазону без преобразования его в умную таблицу. 

Имя диапазона так же, как и в умной таблице, не должно содержать пробелы и знаки препинания. Выделяем диапазон ячеек с запасом пустых строк. Например, в нашем случае, мы понимаем, что в списке больше 25 значений содержаться не будет. Переходим во вкладку «Формулы», раздел «Определенные имена», меню «Диспетчер имен», нажимаем «Создать».

Называем будущий список, при необходимости корректируем диапазон значений.

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

Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

Выпадающий список HTML: учимся создавать и задавать стили с помощью CSS

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

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

Выпадающий список создается с помощью тега

Использование атрибута value

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

В следующем примере мы создадим выпадающий список с атрибутом value :

Пример получения доступа к выбранному варианту в JavaScript

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

Для option value HTML используется следующий код:

Следующая строка кода используется в JavaScript , чтобы получить доступ к значению атрибута value варианта

Вот как обеспечивается доступ к этому значению в HTML select option selected JavaScript :

Также можно получить доступ к значению с помощью JQuery-метода $.val() :

Замените эту строку в приведенном выше примере, и код будет отображать шорткод / значение цвета в атрибуте value , а не видимый текст.

Пример получения значения в скрипте PHP

В этом примере получения значения выбранного из выпадающего списка варианта форма создается с помощью тега в разделе разметки. После выбора цвета из выпадающего списка нажмите на кнопку “ Submit ”. Данные формы будут передаваться в тот же PHP-файл , после чего отобразится выбранный цвет:

Метод формы, используемый в примере — POST , поэтому можно получить значения формы с помощью массива PHP $_POST . Это код формы, используемый в примере:

А вот как PHP-скрипт используется, чтобы получить значение HTML select option :

Если в форме указан метод GET , тогда используйте PHP-массив $_GET .

Определение стилей выпадающего списка с помощью CSS

Теперь рассмотрим, как определить стили выпадающего списка , используя возможности CSS . В следующей демо-версии я использовал несколько простых свойств CSS и свойства градиента CSS3 :

Вместе с linear-gradient здесь использовано свойство box-shadow . Полный код CSS выглядит следующим образом:

Закругленные углы с помощью свойства border-radius

Для HTML select option мы зададим свойство CSS3 border-radius , чтобы сделать закругленные углы. Цветовая гамма также изменяется. Вы можете экспериментировать с рамками, шириной, полями и другими свойствами, как захотите:

Использование нескольких атрибутов и стилей CSS

Чтобы дать пользователям возможность выбирать из списка несколько вариантов, нужно использовать атрибут multiple . В приведенном выше примере может быть выбран только один вариант. При использовании multiple можно выбрать несколько вариантов, нажав клавишу CTRL :

Использование фреймворка Bootstrap и плагинов для создания красивых выпадающих списков

Если вы используете фреймворк Bootstrap , то у вас есть возможность применять плагины для создания крутых option HTML .

Один из таких плагинов — Bootstrap-Select . Он добавляет для выпадающего списка полезные функции. Например, можно выполнить поиск значения, вводя текст в поле. Это особенно полезно, если в выпадающем списке много вариантов.

Кроме этого все выбранные варианты помечаются галочками, и можно установить лимит на количество выбранных вариантов, используя атрибут multiple .

В приведенной демо-версии пользователь может выбрать два варианта из списка:

Демо-версия выпадающего списка с опцией поиска

Используя тот же плагин, можно предоставить пользователям возможность искать нужные варианты в списке HTML select option :