для чего нужен выпадающий список в excel
Что такое выпадающий список в Excel и как его создать?
Что такое выпадающий список и для чего он нужен?
Выпадающий список в Excel, представляет собой функцию занесения данных, статичного типа, в одну ячейку. Эта опция создана для облегчения работы с таблицами, например, при заполнении финансовых отчетов, разнообразных прайс-листов и т.п., создаются реестры статичных значений (дата, название продукции, наименование должностей и имен сотрудников и т.п.), которые потом очень удобно использовать в процессе заполнения серийной документации (множество однотипных документов).
Простыми словами, основной его функцией является быстрое заполнение, либо коррекция определенных сегментов документа. Все значения не нужно вводить вручную, а просто выбирать из перечня уже имеющиеся.
Как сделать выпадающий список в Excel? Пошаговая инструкция
Разработчиками ПО предусмотрено несколько методов, как создать выпадающий список в «excel». Далее рассмотрим все доступные способы:
Метод №1 – самый простой
Данный метод является наиболее простым, однако его функциональность имеет определенные ограничения. Его реализация достаточно проста, но есть определенная особенность, этой функцией можно воспользоваться только в графе, под ранее заполненными ячейками, на любые другие сегменты документа она не будет распространяться:
Вариант – 1 переходим в поле под перечнем значений, открываем контекстное меню, и в открывшемся окне выбираем функцию: «Выбрать из раскрывающегося списка»;
Вариант – 2 переходим в поле под заполненным списком, и нажимаем сочетание клавиш: Alt+V.
Метод №2 – стандартный алгоритм
Теперь рассмотрим метод с расширенным функционалом, для создания, в «excel» выпадающего списка в ячейке.
Способ – 1: выделив необходимый диапазон рабочих сегментов, открываем контекстное меню, и выбираем пункт: «Присвоить имя».
Способ – 2: выделив диапазон рабочих сегментов, переходим в раздел Формулы-Диспетчер имен-Создать, и в графе «источник» прописываем уникальное наименование для будущего реестра значений.
Проще говоря, создаем перечень наименований, а его имя, которое должно быть уникальным (не повторяющемся в документе), будет служить ссылкой на обозначенный диапазон ячеек с данными.
Выделяем нужную ячейку, она может располагаться в любом месте документа (как на листе где размещены значения, так и на всех дополнительных листах документа). Теперь необходимо перейти в раздел: Данные-Проверка данных, в открывшемся окне в графе «тип данных» из общего перечня выбираем «Список», и в графе «источник» прописываем имя реестра (в данном случае «продукты»).
Важно – перед именем, необходимо вставить знак равенства «=».
Таким образом мы рассмотрели еще 1 метод, как сделать выпадающий список в ячейке «Excel».
Важно – создавать данную опцию, можно не для одной графы, а сразу для любого их количества, расположенных в любой области «Документа». Выделяем через «CTRL» все ячейки, которым необходимо задать опцию конкретного перечня данных, и проделываем вышеописанный алгоритм действий.
Правильное введение данных
Алгоритм, как сделать выпадающий список в «Excel» рассмотрели, но следует обратить внимание на ряд дополнительных моментов, корректного внесения информации:
На первый взгляд может показаться что это не совсем полезная опция. Но лишь тем, кто не работал с серийными типами документации, в которой необходимо систематически вносить сложные значения.
Одним из недостатков такой опции, является тот фактор, что активное окно меню реестра, может отображаться не более чем восемь значений, для доступа к остальным необходимо пользоваться функцией прокрутки. При работе с большими перечнями позиций/наименований, рекомендовано создавать их частями, например, распределив их по буквам алфавита, категориям товара (овощи, напитки и т.п.), либо иным удобным признакам. Использовать более двадцати значений для одного реестра может вызвать сильные неудобства.
Если у Вас остались вопросы по теме «Что такое выпадающий список в Excel и как его создать?», то можете задать их в комментария
Рейтинг статьи 4.5 / 5. Голосов: 2
Пока нет голосов! Будьте первым, кто оценит этот пост.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Выпадающий список в MS Excel. Описание и примеры.
Выпадающий список в MS Excel. Описание и примеры.
В данной статье, на простых примерах показано, что такое выпадающий список в MS Excel, как им пользоваться, какие варианты использования данной функции есть. На что необходимо обратить внимание.
Простой выпадающий список в ячейки.
Предположи, нам нужно сделать ячейку, в которой будет выпадающий список из нескольких вариантов значений на выбор. Например: город, село городского типа, село. Поместим в эту ячейку выпадающий список с нужным нам перечнем значений. В нашем примере данная ячейка будет располагаться перед ячейкой с названием населенного пункта.
Выбираем ячейку, в которой будет располагаться наш выпадающий список. Это ячейка С4. Далее, в закладке Данные, находим кнопку Проверка данных.
Нажимаем на кнопку Проверка данных, появляется диалоговое окно Проверка вводимых значений. Вводим нужные данные в поля этого диалогового окна.
Тип данных. Выбираем вариант: Список
Источник. Здесь просто прописываем нужные нам значения по порядку, через точку с запятой «;».
В нашем примере это выглядит вот так: Город; Село городского тип; Село
Остальные пункты диалогового окна Проверка вводимых значений не трогаем.
Нажимаем ОК. В ячейке С4 появляется выпадающий список с нужными нам значениями.
Далее копируем или протягиваем данный выпадающий список в ячейки, исходя из задачи, которая перед нами стоит.
Выпадающий список в Excel на основе исходных данных.
У нас есть Таблица №1, в которой есть два столбца, один с названием конфет, другой с ценой на эти конфеты. Сделаем выпадающий список на основе столбца с название конфет. Сделаем выпадающий список на том же листе, что и Таблица №1.
Выбираем ячейку, в которой будет располагаться наш выпадающий список. В нашем примере это ячейка F4. Далее, в закладке Данные, находим кнопку Проверка данных.
Нажимаем на кнопку Проверка данных, появляется диалоговое окно Проверка вводимых значений. Вводим нужные данные в поля этого диалогового окна.
Тип данных. Выбираем вариант: Список;
Источник. Выбираем диапазон ячеек, с теми данными, которые должны отражаться в нашем выпадающим списке. В нашем примере, это ячейки с названием конфет в Таблице №1 (C4:C13). Выбрать диапазон можно просто поставив курсор в поле Источник. После чего появиться возможность выбрать диапазон. После выбора диапазона, ссылки на него автоматически становятся абсолютными (=$C$4:$C$13).
Остальные пункты диалогового окна Проверка вводимых значений не трогаем.
Нажимаем ОК. После этого появиться выпадающий список в указанной нами ячейки. В нашем примере, в выпадающем списке будет перечень названия конфет из Таблицы №1.
Вместо использования диапазона, в поле Источник, можно использовать Заданное имя. Тогда в поле Источник, вместо диапазона =$C$4:$C$13, нужно будет прописать Заданное имя с знаком равно перед ним. Например: = Конфеты.
Можно разместить выпадающий список на отдельном листе. Например, наша Таблица №1 будет на Листе 1, а выпадающий список, на основе этой таблицы, будет на Листе 2. Алгоритм точно такой же, просто в поле Источник, выбираем нужный нам диапазон из Таблицы 1. При этом, в поле Источник, будет прописан не только диапазон ячеек, но и название Листа, на котором он находиться: =Лист1!$E$6:$E$15. В случае, если вместо диапазона используется Заданное имя, например Конфеты, то различий вообще нет. В поле Источник, будет прописано Заданное имя, перед которым будет знак равно: =Конфеты.
Выпадающий список в Excel и ВПР.
Теперь перед нами стоит задача, что бы при выборе определенных конфет из выпадающего списка, в соседний ячейки появлялась соответствующая этим конфетам цена. Для этого мы будем использовать функцию ВПР.
Выберем «желтую» ячейку (G4) и вызовем функцию ВПР. В появившемся диалоговом окне Аргументы функции, введем в поля следующие значения.
Искомое_значение. Указываем ячейку с выпадающем списком (F4).
Номер_столбца. Здесь ставим число два «2»
Интервальный_просмотр. Здесь ставим число ноль «0».
Нажимаем ОК. Теперь при выборе определенных конфет в выпадающим списке, в ячейки рядом (G4), появляется цена на эти конфеты, которая соответствует ценам указанным в Таблице №1.
Можно сделать выпадающей список с ценой, на разных с таблицей листах. Таблица №1 на Листе 1, а выпадающий список с ценой на Листе 2. Разница для функции ВПР будет в том, что в поле Таблица, в диалоговом окне Аргументы функции, будет указан диапазон с ссылкой на лист и ячейки: Лист1!$C$4:$D$13.
Как строить выпадающие списки в Excel
Гайд от аналитика Laba.
Выпадающий список — это диапазон подготовленных заранее значений, который раскрывается в ячейке для выбора. Это удобно: вводить с клавиатуры ничего не нужно, и есть возможность запретить ввод значений, которые не соответствуют перечисленным в списке.
Есть много способов, чтобы создавать выпадающие списки в Excel. Рассмотрим некоторые вместе с аналитиком Laba Александром Галабурдой.
Как работает выпадающий список в Excel
Предположим, у нас есть настроенная таблица с выпадающими списками. Если выделить ячейку в столбце, в правом углу ячейки появится стрелка вниз. Нажав на нее, раскроется список значений для выбора.
Чтобы создать такой выпадающий список, перейдите в раздел «Данные» на панели инструментов, в группе «Работа с данными» выберите пункт «Проверка данных».
Далее всплывает окно «Проверка вводимых значений».
Окно делится на 3 вкладки:
#1. Параметры
Здесь задаются основные параметры выпадающего списка в Excel:
#2. Подсказка по вводу
В этой вкладке можно настроить всплывающую подсказку, которая будет высвечиваться при выделении ячейки со списком значений.
#3. Сообщение об ошибке
В этой вкладке можно настроить сценарий действий для Excel, если пользователь попытается ввести значение, которого нет в выпадающем списке.
Примеры выпадающих списков в Excel
#1. Стандартный
Выделяем ячейку (диапазон ячеек), где должен всплывать выпадающий список (в нашем примере это вкладка «Проект», диапазон ячеек A2:A25), переходим в раздел «Проверка данных» (описано выше), выбираем тип данных «Список», в поле «Источник» вставляем диапазон с источника.
Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».
Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.
#2. Список с подстановкой данных
Использовать списки с четким набором значений неудобно, если исходные списки нужно пополнять новыми значениями. В таких случаях лучше использовать формулы или именованные диапазоны. Это исключит необходимость каждый раз изменять условие проверки данных.
Допустим нам нужно добавить еще 3 товара: свекла, лук репчатый и мандарин. Если мы введем значения в источник с данными, в выпадающем списке новые значения так и не отобразятся.
Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:
#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel.
Подробнее о том, что такое «Умные таблицы» и как с ними работать — на наших курсах.
Как сделать выпадающий список в Excel
Выпадающий список в Excel это, пожалуй, один из самых удобных способов работы с данными. Использовать их вы можете как при заполнении форм, так и создавая дашборды и объемные таблицы. Выпадающие списки часто используют в приложениях на смартфонах, веб-сайтах. Они интуитивно понятны рядовому пользователю.
Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:
[ninja-popup itemprop=»image» itemscope itemtype=»https://schema.org/ImageObject»> 
[/ninja-popup]
Видеоурок
Как создать выпадающий список в Экселе на основе данных из перечня
Представим, что у нас есть перечень фруктов:
Для создания выпадающего списка нам потребуется сделать следующие шаги:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2 ), а не относительными (например, A2 или A$2 или $A2 ).
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова «Да» и «Нет». Для этого нам потребуется:
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле « Источник «, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках — выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную. Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Как создать выпадающий список в Excel с автоматической подстановкой данных
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
Как скопировать выпадающий список в Excel
Для того чтобы скопировать выпадающий список с текущим форматированием:
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
Нажав « ОК «, Excel выделит на листе все ячейки с выпадающим списком. Так вы сможете привести за раз все списки к общему формату, выделить границы и.т.д.
Как сделать зависимые выпадающие списки в Excel
Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:
Для создания зависимого выпадающего списка нам потребуется:
Теперь, для создания зависимого выпадающего списка:
Теперь, если вы выберите в первом выпадающем списке страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете «США» из первого выпадающего списка.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Выпадающий список в ячейке листа
Видео
Способ 1. Примитивный
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:
Способ 2. Стандартный
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).
Способ 3. Элемент управления
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:
Самые нужные и полезные свойства, которые можно и нужно настроить:
Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия: