Как создать многоуровневый зависимый выпадающий список в excel?

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

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

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

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

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

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

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

то два варианта. там, это сделала

вводимых данных. Находясь. любой ячейки. В имеющимся списочным элементам ввести в ячейкупосле символа
использовать перечень, если«Список» из имеющихся данных, как создать диапазон, а затем —
Ненужный раскрывающийся список на If End Sub в выпадающий список?». Получаем следующий вид1. В поле
сотрудница, которая работала в любом разделеТакже вместо этого действия

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

к полю “Создание списка в имя, смотрите вилиWindows macOS Online снизу, вставляем другой

еще одна строкаСтавим курсор в ячейку,

точкой с запятой. не знаю зачем нужно совершить единственное обозначенному фрагменту правой отсутствует. при желании его
то данное действие оператора – такой таблице будет«Источник» Excel без повторов”.
статье «Диапазон вЭтих же  код обработчика.Private Sub со значением «баобаб».
где будет находиться Просто добавляете новое она это сделала.

действие — нажать кнопкой мышки иЧто же делать, если можно выбрать, чтобы заблокируется. При этом,«ДВССЫЛ» легче менять значения,. Тут нужно указатьКак удалить выпадающий список Excel» здесь.. Далее повторите действия,Выделите ячейку, в которой Worksheet_Change(ByVal Target AsКогда значения для выпадающего выпадающий список. Открываем значение в список. А через проверку на кнопку остановиться на варианте значения добавлялись в вставить в элемент если вы вносили

. После этого в тем самым автоматически группу наименований, предназначенную Excel.Список можно написать описанные выше.

есть раскрывающийся список. Range) On Error
списка расположены на параметры инструмента «Проверка2. В поле данных не получается,«Очистить всё»
«Копировать» окно проверки данных таблицы. сообщение предупреждения в скобках в качестве
изменяя списочные элементы. для использования вНужно выделить ячейки

и в столбцеЕсли вместо удаления раскрывающегосяЕсли вы хотите удалить Resume Next If другом листе или данных» (выше описан

support.office.com⁪>

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

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

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

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

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

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

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

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

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

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

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

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

Как в Excel создать выпадающий список с удалением использованных элементов | Сводные таблицы Excel 2010

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

Недельный график дежурств, который надо заполнить именами сотрудников

Чтобы реализовать подобный вариант выпадающего списка, выполним несколько простых шагов.

Шаг 1. Кто сколько работает?

Сначала давайте подсчитаем, кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2).

Подсчитаем, кто из наших сотрудников уже назначен

Фактически формула просто вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.

Шаг 2. Кто еще свободен?

Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников: =ЕСЛИ(F2-G2

Выясним, кто из наших сотрудников еще свободен

Шаг 3. Формируем список

Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи – на следующем шаге – с выпадающим списком.

Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу: =ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1))) или соответственно, =IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1))).

Формируем список свободных сотрудников

При всей внешней жуткости вида эта формула делает одну простую вещь – выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

Шаг 4. Создаем именованный диапазон свободных сотрудников

Жмем кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем новый именованный диапазон Имена по следующей формуле: =СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10)) или в англоязычной версии: =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10)).

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

Фактически мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.

Шаг 5. Создаем выпадающий список в ячейках

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

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

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

Способ 2 – Элемент управления.

В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:

Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.

Открываем панель элементов

В появившемся диалоговом окне элементов ActiveX, находим значок со списком «Поле со списком».

Выносим элемент на страницу документа.

Формат элемента «Список»

В данном меню заносим диапазон значений и привязываем к ячейке данные.

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

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

Уловки для создания раскрывающихся списков, позволяющих выбрать несколько элементов

Наш приглашенный блоггер Дебра Далглиш — консультант, специализирующийся на программировании и разработке Microsoft Office.Каждую неделю она делится своими знаниями об Excel в блоге Contextures и ежегодно получает награду Excel MVP с 2001 года.

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

Перед волшебством

Чтобы отслеживать, кто будет оставаться в офисе каждую неделю, Ди, менеджер офиса, создал файл Excel.Этот файл содержит список имен сотрудников и список недель. Ди создал раскрывающийся список имен сотрудников на каждую неделю.

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

Добавьте волшебство

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

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

За занавеской

Чтобы увидеть код для рабочего листа, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код . Это откроет Visual Basic Explorer (VBE).

В списке проектов слева указана рабочая книга FridayStaffName, а лист Friday_Staff выделен. Справа мы видим код события Worksheet_Change для этого рабочего листа.Этот код запускается автоматически, если Ди вносит изменения в лист.

Скопируйте код в свой файл

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

В коде Target относится к ячейке, которую вы изменили. На снимке экрана ниже код проверяет, в каком столбце находится эта целевая ячейка.Затем, если это был столбец 2 (B), остальная часть кода будет выполнена.

Вместо использования столбца 2 вы можете изменить его число или использовать несколько столбцов, например: Case 2, 5, 6

Пусть волшебство работает на вас

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

Вы можете загрузить образец файла, чтобы увидеть, как он работает, а также добавить этот удивительный трюк в свои рабочие книги: Загрузите файл образца имен сотрудников Friday

* Mystical Magic Factory — вымышленная компания, но эта тема блога основана на реальной проблеме клиента Contextures.

Дополнительная информация в раскрывающихся списках Excel:

Как создать раскрывающийся список ячеек в Excel 2010

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

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

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

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

Формула источника будет выглядеть следующим образом:

=СМЕЩ(A2,0,0,СЧЁТЗ(A2:A200),1)

Давайте разберемся, как она работает.

Функция СМЕЩ формирует ссылку на диапазон заданного размера.

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

И теперь нужно определить правую нижнюю точку. Теперь уже от начальной точки диапазона делаем С шагов вниз и D шагов вправо. В нашем примере D=1. То есть нам нужен только один столбец. Осталось только определиться, сколько шагов вниз нужно сделать, сколько строк взять в наш список. Нам нужна последняя заполненная строка в столбце А. Точнее, не сама строка, а ее порядковый номер.

Здесь нам поможет вторая функция — СЧЕТЗ (COUNTA). Она подсчитывает количество значений в заданном нами диапазоне A2:A200. Можно взять и более удаленные координаты, но думается, что 200 значений в списке будет вполне достаточно. Естественно, вместо ссылки можно использовать именованный диапазон.

В нашем примере мы имеем 8 значений. Значит, возьмем 8 строк, начиная с А2. То есть, A2:A9.

Если в ячейке А10 появится новое значение, тогда значений станет уже 9. Соответственно диапазон автоматически расширится до A2:A10. И так далее.

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

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

А вот еще о работе с выпадающими списками в Excel:

Рейтинг статьи

Как нам это может пригодиться?

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

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

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

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

Дополнение 2. Сообщение об ошибках

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

Можно настроить это сообщение при создании или редактировании выпадающего списка во вкладке Сообщение об ошибке.

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

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д

Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке)

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне — это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше

Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15

Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

Что такое выпадающий список и для чего он нужен?

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

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

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

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

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

Подготовить таблицу. В первой строке каждого столбца должна быть категория меню (для удобства).

Задать диапазонам с перечислением блюд имя в соответствии с его категорией. Общее название каждого диапазона должно четко совпадать с тем, что записано в первой ячейке каждого столбца.

На отдельной области создать раскрывающийся список через пункт меню «Данные» — «Проверка данных». Источник – первая ячейка каждого столбца.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как сделать выпадающий список в Excel? Пошаговая инструкция

Разработчиками ПО предусмотрено несколько методов, как создать выпадающий список в «excel». Далее рассмотрим все доступные способы:

Метод №1 – самый простой

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

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

Вариант – 2 переходим в поле под заполненным списком, и нажимаем сочетание клавиш: Alt+V.

Метод №2 – стандартный алгоритм

Теперь рассмотрим метод с расширенным функционалом, для создания, в «excel» выпадающего списка в ячейке.

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

Способ – 1: выделив необходимый диапазон рабочих сегментов, открываем контекстное меню, и выбираем пункт: «Присвоить имя».

Способ – 2: выделив диапазон рабочих сегментов, переходим в раздел Формулы-Диспетчер имен-Создать, и в графе «источник» прописываем уникальное наименование для будущего реестра значений.

№2-2.

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

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

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

Важно – перед именем, необходимо вставить знак равенства «=»

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

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