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

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

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

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

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

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

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

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

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

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

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

Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

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

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»)

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

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Простой выпадающий список в Excel

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

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

Затем следует нажать на «Источник» и ещё раз выделить заполненный нами столбец, только после этого нажмите «ОК». Итак, мы теперь знаем, как сделать в Эксель выпадающий список его ещё называют «всплывающий». После его создания он выглядит так (Скрин 3).

Вы можете нажимать на стрелочку и выбирать написанные ранее слова в таблице. Мы рассмотрели простой вариант. Далее, будут ещё списки, но с более усложнённом вариантом.

Видеоурок по теме

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

Есть мнение?Оставьте комментарий

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем, что допустили ее. Пожалуйста, выделите ее и нажмите на клавиатуре CTRL + ENTER.

2007-2019 «Педагогическое сообщество Екатерины Пашковой — PEDSOVET.SU». 12+ Свидетельство о регистрации СМИ: Эл №ФС77-41726 от 20.08.2010 г. Выдано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций. Адрес редакции: 603111, г. Нижний Новгород, ул. Раевского 15-45 Адрес учредителя: 603111, г. Нижний Новгород, ул. Раевского 15-45 Учредитель, главный редактор: Пашкова Екатерина Ивановна Контакты: +7-920-0-777-397, info@pedsovet.su Домен: http://pedsovet.su/ Копирование материалов сайта строго запрещено, регулярно отслеживается и преследуется по закону.

Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.

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

сервис вебинаров

О работе с сайтом

Мы используем cookie.

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

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

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

Создание или удаление настраиваемого списка для сортировки и подстановки данных

Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.

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

Сравнение встроенных и настраиваемых списков

В Excel есть указанные ниже встроенные списки дней недели и месяцев года.

Пн, Вт, Ср, Чт, Пт, Сб, Вс

Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье

янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек

Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь

Примечание:

Изменить или удалить встроенный список невозможно.

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

Высокое, Среднее, Низкое

Большое, Среднее, Малое

Север, Юг, Восток, Запад

Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель

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

Примечание:

Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.

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

Введение значений напрямую

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

В Excel 2010

и более поздних версиях выберите пунктыФайл >Параметры >Дополнительно >Общие >Изменить списки .

В Excel 2007

нажмитекнопку Microsoft Office и выберите пунктыПараметры Excel >Популярные >Основные параметры работы с Excel >Изменить списки .

Выберите в поле Списки

пунктНОВЫЙ СПИСОК и введите данные в полеЭлементы списка , начиная с первого элемента.

После ввода каждого элемента нажимайте клавишу ВВОД.

Завершив создание списка, нажмите кнопку Добавить

На панели Списки

появятся введенные вами элементы.

Нажмите два раза кнопку ОК

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

Выполните указанные ниже действия.

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

Убедитесь, что ссылка на выделенные значения отображается в окне Списки

в полеИмпорт списка из ячеек , и нажмите кнопкуИмпорт .

На панели Списки

появятся выбранные вами элементы.

«Параметры» > «Дополнительно» > «Общие» > «Изменить списки». Если вы используете Excel 2007, нажмите кнопку Office и выберите «Параметры Excel» > «Популярные» > «Изменить списки».» /> Два раза нажмите кнопку ОК

Примечание:

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

Выполните указанные ниже действия.

По приведенным выше инструкциям откройте диалоговое окно «Списки».

Выделите список, который нужно удалить, в поле Списки

и нажмите кнопкуУдалить .

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

Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки

впараметрах Excel : его можно выбрать только в столбцеПорядок диалогового окнаСортировка . Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для командыЗаполнить .

При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel

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

Выпадающий список в Excel с подстановкой данных (+ с использованием функции СМЕЩ)

В приведенном случае СМЕЩ позволила создать всплывающее меню, расположенное в фиксированном диапазоне. Недостаток этого метода – после добавления пункта придется самостоятельно редактировать формулу.

Чтобы создать динамический перечень с поддержкой ввода новой информации, необходимо:

1. Осуществить выделение интересующей ячейки.
2. Раскрыть вкладку «Данные» и нажать по «Проверка данных».
3. В открывшемся окошке снова осуществляем выбор пункта «Список» и источником данных указываем такую формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Нажимаем «ОК».

Здесь содержится функция СЧЕТЕСЛИ, чтобы сразу определять, сколько ячеек заполнено (хотя у нее есть значительно большее количество применений, просто мы записываем ее здесь для конкретной цели).

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

Создание зависимого списка в Экселе

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

Cтруктурируем нашу таблицу видов расходов:

Теперь виды расходов разбиты по колонкам, соответствующим каждой из категорий. Далее мы будем использовать функцию ДВССЫЛ(текст) . Что она делает? Она пытается преобразовать введенный текст в ссылку на ячейки. Что будет, если записать такую формулу: =СУММ(ДВССЫЛ(«F1:F5»)) . Функция ДВССЫЛ распознает текст «F1:F5», как диапазон ячеек и вернет его. А функция СУММ – просуммирует все значения в этом диапазоне.

То же самое произойдет, если мы диапазону «F1:F5» присвоим имя. Например, «структура». Формула =СУММ(ДВССЫЛ(«структура»)) даст аналогичный результат. Именно этой возможностью мы и воспользуемся.

Чтобы больше узнать об именовании ячеек, прочтите эту статью. Рекомендую это сделать, имена – удобный и практичный инструмент.

Дадим имена всем столбцам с исходными данными. При этом, диапазон с видами должен именоваться точно так же, как и его категория. Например, диапазону J4:J8 дадим имя «Канцелярия». Именуем:

1. Выделяем ячейки с категориями I4:I6
2. В области имен запишем «Категории», жмем Enter

Выделяем J4:J8 и называем «Канцелярия»

Аналогично именуем «Хозматериалы» и «Питание»

Теперь, если кликнуть Формулы – Определенные имена – Диспетчер имен – можно увидеть все заданные имена. Если Вы где-то ошиблись, или список изменился, в этом окне можно внести исправления.

Еще раз настраиваем проверку данных:

1. Выделяем в таблице столбец с категориями
2. Жмем на ленте Данные – Работа с данными – Проверка данных
3. Тип данных указываем «Список», Источник – =Категории . Программа определит, что это именованный диапазон и выберет из него пункты списка. Жмем Ок

Выделяем столбец с видами расходов
Тип данных указываем «Список», Источник – =ДВССЫЛ(B4) . Вместо B4 подставится название выбранной категории. Это название совпадает с именем диапазона, в котором хранятся расходы данной категории. Функция ДВССЫЛ преобразует это имя в диапазон, а Excel извлечет из этого диапазона пункты.

Жмем Ок , теперь все работает

Если все еще не понятен принцип, опишу по пунктам, как теперь будет работать наша таблица:

• С помощью обычного списка, в столбце B выбираем категорию товаров. Например, «Питание»
• Слово «Питание» попадает, как источник данных в столбец C, т.е. в виды расходов
• У нас есть диапазон данных L4:L8, который называется Питание. Функция ДВССЫЛ это определяет и заменяет на слово «Питание» на диапазон L4:L8
• Теперь этот диапазон будет источником для списка вида расходов

Все перечисленное подтверждаю изображением ниже:

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

Думаю, Вы разобрались, как сделать зависимый список в Excel. Если не разобрались – пишите комментарии. А я статью закончил, продуктивной Вам работы!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

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

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

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

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

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

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

PrivateSub Worksheet_Change(ByVal Target As Range)   Dim lReply AsLong       If Target.Cells.Count > 1 ThenExitSubIf Target.Address = "$C$2"ThenIf IsEmpty(Target) ThenExitSubIf WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then           lReply = MsgBox("Добавить введенное имя " & _                          Target & " в выпадающий список?", vbYesNo + vbQuestion)           If lReply = vbYes Then               Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target           EndIfEndIfEndIfEndSub  

Сохраняем, установив тип файла «с поддержкой макросов».</li>Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».</li></ol>

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

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

Подготовка

1. На листе №1 создайте несколько столбцов (т.е. дайте им заголовки), например дата, отдeл, наименование проблемы, причина неисправности, фамилия специалиста техподдержки и итог устранения. Так вот, дату проблему и причину мы трогать не будем, а вот oтдел и фамилию специалиста, устранившего неисправность, мы занесем в шаблон, так как вариантов тут может быть немного.
2. Чтобы было удобнее создайте новый лист и обзовите его как-нибудь. Я бы назвал «Данные». Именно сюда мы и будем вносить все возможные значения, из которых потом и будем выбирать то, что нам надо.
3. На листе 2 (Данные) начинайте записывать значения. В начале сделаем всё это для графы «Отдел». Я напишу в столбик несколько отделов, например отдeл статистики, администрация, бухгалтерия, отдел планирования, oтдел информационных технологий, ну и хватит пожалуй. Вы можете писать что-то свое.
4. Точно также напишите в столбик фамилии сотрудников, которые занимаются устранением проблем, например Иванов, Петров, Сидоров, Смирнов. Записали? Отлично. Тогда идем дальше. То же самое делаем с «Итогом устранения». Пишем данные, например устранено, на устранении, устранение невозможно.

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь – преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д.

Возьмем, к примеру, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Список моделей автомобилей

Выделим весь список моделей Toyota (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager), кнопка Создать (Create). Затем повторим то же самое со списками моделей Ford и Nissan, задав имена диапазонам Ford и Nissan соответственно.

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

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (ячейки A1:C1 в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

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

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

• В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны, задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
• Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»)) .
• Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

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

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

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

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

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

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

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

Раскрывающийся список с подстановкой данных

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

Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».

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

Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона

Здесь важно установить галочку возле «Таблица с заголовками», так как наличие заголовка в данном случае играет ключевую роль.

После проделанных процедур вы получите следующий вид диапазона.

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

В поле ввода «Источник» вам нужно вписать функцию с синтаксисом «=ДВССЫЛ(“Имя таблицы”)». На скриншоте указан более конкретный пример. 

Итак, список готов. Выглядеть он будет вот так.

Готовый список

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

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

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

Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка

Список с автозаполнением

Способ 2

1. Выделить имеющийся перечень наименований и присвоить ему имя в левой строке формул.
2. Щелкнуть по сторонней ячейке. Зайти в «Данные» – «Проверка данных». В качестве источника указать присвоенное таблице имя из п.1 через знак «=».
3. Чтобы добавить еще одно значение, нужно воспользоваться вставкой пустой строки. Выделить ячейку с любым наименованием, на «Главной» клацнуть по «Вставить» — «Вставить строки на лист». На листе отобразится пустая ячейка, вписать туда название.

Способ 3

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

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