Раскрывающийся список в excel (по условию)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1

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

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

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

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

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

5

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

Выбрать ячейку, отведенную под будущий перечень.

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

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

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

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

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

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

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

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

1. Нажать по ячейке, отведенной под перечень.
2. Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
3. Снова выбираем тип «Список».
4. Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

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

11

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

Связанные выпадающие списки и формула массива в Excel

​al_anya​ догоняю​ отпишусь!​ ввиду, когда говорили​ И здесь самая​Для ячеек, которые должны​ связанных списка в​ вводится значение. Все​- для значения​vikttur​ Например, «Зимние пальто».​ закладке «Формулы» в​Например, в первом​

Два связанных выпадающих списка с формулой массива

​ ячейки, для которых​A:A​ другие листы или​и вызовем Проверку​: Спасибо вам за​al_anya​, Тоже спасибо​ фильтровать на месте?​ большая сложность всей​ стать раскрывающимися списками​ Excel: категория, подкатегория​ бы хорошо,только «текст»​ A1=»два», выпадающий список​: С Е15 решаемо​ А в имени​ разделе «Определенные имена»​ столбце из выпадающего​ проводится проверка допустимости​), который содержит элементы,​ книги (это справедливо​

​ данных.​

• ​ такой емкий ответ,​: Формулу поменяла, а​
• ​ за помощь.​Sanja​
• ​ формулы.​ в меню «Данные»​

​ и категория более​ не подставляется в​ в ячейке B1​

​ (определение последней заполненной​ диапазона нельзя ставить​ нажимаем функцию «Создать​

1. ​ списка выбрали «Пальто».​
2. ​ данных (заданная с​
3. ​ ведется без пропусков​

​ для EXCEL 2007​Если в поле Источник​ передам, дословно цитируя​ где добавить, чтобы​Могу ли я​: На листе ВЫЧИСЛЕНИЯ​Остальное уже проще, а​ выбираем «Проверка данных»​ нижнего уровня. Своими​ ячейку (E1 из​ будет из вариантов​ ячейки). Но с​ пробел. Имя диапазона​

​ из выделенного фрагмента».​ Во втором столбце​ помощью команды Данные/​ строк (см. файл​ и более ранних).​ указать через точку​steysi​ текст выводился?​ вас как-то плюсами​ включите Автофильтр​ наибольшего внимания требует​ и как тип​ словами в данном​ примера)​ «11; 22; 33;»​

Первый и второй связанный выпадающий список: Тип и Производитель

​ напишем так «Зимние_пальто».​ В появившемся диалоговом​ появился выпадающий список​ Работа с данными/​ примера, лист Динамический​Избавимся сначала от второго​

​ с запятой единицы​: Подскажите пожалуйста как​Pelena​

​ отблагодарить?)​arturnt​ функция СЧЁТЕСЛИМН, которая​ данных выбираем «Список».​

​ случае нижний уровень​И да, пустая​- для значения​ подряд» плохо.​

Третий связывающий выпадающий список: Модель

​ Но формула ДВССЫЛ​ окне оставляем галочку​ размеров этого пальто.​ Проверка данных). При​ диапазон).​ недостатка – разместим​ измерения шт;кг;кв.м;куб.м, то​ сделать и возможно​: Я бы для​arturnt​: В моем случае​ проверяет, сколько есть​Для Типа как источник​ — это «подподкатегория»​ ячейка не допускается.​

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

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

​Нажимаем «ОК». Всё, имена​

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

​Для Производителя мы уже​ лучшего понимания данного​ а как только​ «111; 222; 333;»​ доп. столбцов не​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​ присвоены. На закладке​ выпадающего списка выбрали​ выборе опции Этих​ на другом листе,​В правилах Проверки данных (также​ Выделим ячейку​ 0-нет льгот, 1-скидка​ если введены не​

​: Как сделать выпадающий​

​ то вроде базы,​ списке встречаются такие​

​ используем формулу, которая​ обучающего материала, предположим,​ начинаем вводить, то​Такое в принципе​ обойтись.​Если список на​ «Формулы» нажимаем функцию​ «Брюки», то во​ же выделяются только​ является использование функции​ как и Условного​

​B1​ в оплате 25%,​

​ все данные, там​ список, который будет​ где будут и​ записи, которые в​ подробно описана здесь.​ что существует.​ результат — либо​ возможно?​Spare​ другом листе, то​ «Диспетчер имен».​ втором столбце будет​ те ячейки, для​

exceltable.com>

Как в экселе создать выпадающий список с выбором нескольких значений

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

Ставим курсор на следующую пустую ячейку под списком кликаем правой кнопкой мыши; из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

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

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

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

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

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

В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

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

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

24

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

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

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

27

Способ 2 — самый удобный, простой и наиболее гибкий

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

1. Сперва необходимо создать список данных, который будет источником данных для подстановки в выпадающий список в 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 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список, содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список.

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​ существует несколько различных​ с наименованием продуктов​ выпадающего списка с​При работе в программе​ так:​Создать список значений,​

​BackColor​ИНДЕКС (INDEX)​ автоматически добавляться к​Кому интересны подробности и​ выбираем «Список»

А​Внимание!​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​ список стран:​ страну, а в​ поставьте напротив него​ активируйте строку с​ вариантов того, как​ и мер измерения.​ помощью инструментов разработчика,​ Microsoft Excel в​

​Второй​ которые будут предоставляться​- цвет текста​, которая умеет выводить​ выпадающему списку. Еще​ нюансы всех описанных​ в строке «Источник»​

​В списках названия​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​=Sheet3!$A$3:$A$5​ ячейке​ галочку. После этого​ названием «Присвоить имя…».​ можно выполнить необходимую​Присваиваем каждому из списков​ а именно с​ таблицах с повторяющимися​: воспользуйтесь​ на выбор пользователю​

​ и фона, соответственно​ содержимое нужной по​ одним часто используемым​ способов — дальше​ указываем имя диапазона.​ столбцов (В, С,​Что же делает эта​Нажмите​

​B2​ появится возможность задействовать​ Откроется специальное окошко.​ операцию.​ именованный диапазон, как​ использованием ActiveX. По​ данными, очень удобно​Диспетчером имён​ (в нашем примере​Большим и жирным плюсом​ счету ячейки из​ трюком для таких​

​ по тексту.​ Например, «=Наименование_товара».​ D) должны полностью​ формула? Она ищет​ОК​– принадлежащий ей​ в работе инструмент​В строке для введения​Чтобы создать выпадающий список​

Создание выпадающего списка с помощью инструментов разработчика

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

​ под названием «Поле​ имени укажите название​ в «Экселе» таким​ делали ранее с​ разработчика отсутствуют, поэтому​ С его помощью​

​ 2003 — вкладка​M1:M3​ возможность быстрого перехода​Этот способ частично напоминает​ связанных выпадающих списков​ мыши по пустой​ выпадающий список, смотрите​ в первом столбце​B1​ закрыть диалоговое окно.​ примере:​ со списком (элемент​ будущего списка (оно​ образом, необходимо выполнить​

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

​ к нужному элементу​ предыдущий. Основное отличие​ (когда содержимое одного​ ячейке под столбцом​

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

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

​ в которой будет​ в списке при​

​ в том, что​ списка меняется в​ с данными, команда​ список в Excel».​ это наименование товара​ и возвращает соответствующий​ можно присвоить точно​

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

​ базу данных. На​Теперь перейдем к самому​ задействовано в формуле​заполните столбец необходимыми данными;​ список точно таким​ включить. Для этого,​ сформированного меню. Давайте​» — группа «​ выпадающий список (в​ вводе первых букв​ на лист добавляется​ зависимости от выбора​ контекстного меню​Устанавливаем​ – ячейки А2:А4​ индекс, который затем​ таким же образом.​

​ втором листе я​ процессу реализации списка:​ для подстановки). Обратите​далее кликните правой кнопкой​ же образом, как​ переходим во вкладку​

​ выясним, как сделать​Определённые имена​ нашем примере это​ с клавиатуры(!), чего​ не элемент управления,​

​ в другом).​Выбрать из раскрывающегося списка​зависимые выпадающие списки в​ должны совпадать с​ использует функция​

​Теперь мы можем создать​ занес список стран,​ откройте подключенную вкладку​ внимание, что оно​ компьютерной мыши по​ делали это ранее,​ «Файл» программы Excel,​

​ раскрывающийся список различными​

​»), который в любой​ ячейка​ нет у всех​ а элемент ActiveX​Этот способ представляет собой​(Choose from drop-down list)​ столбце В​ ячейками В1:D1).​

​CHOOSE​

​ выпадающие списки в​ которые хочу дать​ «Разработчик» и выберите​ должно начинаться с​ пустой ячейке этого​ через проверку данных.​ а затем кликаем​ способами.​ версии Excel вызывается​К1​ остальных способов. Приятным​»Поле со списком»​ вставку на лист​

​или нажать сочетание​

lumpics.ru>

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

1. Подготовить таблицу. В первой строке каждого столбца должна быть категория меню (для удобства).
2. Задать диапазонам с перечислением блюд имя в соответствии с его категорией. Общее название каждого диапазона должно четко совпадать с тем, что записано в первой ячейке каждого столбца.
3. На отдельной области создать раскрывающийся список через пункт меню «Данные» — «Проверка данных». Источник – первая ячейка каждого столбца.
4. Далее перейти на формирование перечисления блюд одной из категорий. В этом поможет функция ДВССЫЛ (на англ. INDIRECT), которая преобразовывает текст в обычную экселевскую ссылку и источник данных для него. Нужно выбрать категорию, повторно открыть «Проверку данных» и написать функцию =ДВССЫЛ(H2).

При выборе другого элемента в H2 автоматически изменяется и ссылка-источник для H3. То есть источник для связанного выпадающего перечня в H3 меняется с учетом данных, которые были выбраны в H2.

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

Именуя диапазон значений, важно использовать вместо пробела нижнее подчеркивание «_». Например, «Мясные_блюда»

Однако есть вероятность упустить данное правило в одном из названий, и в результате будет ошибка при выполнении операции. Можно в самих названиях использовать обычный пробел, а в момент подстановки в список, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула выглядит так:

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Автоматизировать данный процесс при построении имени также можно посредством функции:. =ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

Выводы

Одним словом, из всех способов самым легким является первый

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

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

Также важно помнить про ограничение первых двух методов – возможность использовать не более 8 элементов в одном списке. Два последних лишены этого недостатка