Выпадающий список в excel

Содержание:

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

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

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

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

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

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

Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке

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

Как сократить длину выпадающих списков?

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

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

Список продуктов.
Бананы Перловая каша
Ванильный сахар Петрушка
Горох сушеный Просо
Горчица молотая Рис
Греча Рыба Сом
Грибы Сало копченое
Дрожжи сухие Сахар обычный
Желатин Сахарная пудра
Какао порошок Свинина
Какао порошок Сгущённое молоко
Карри (специя) Сливки молочные
Картофель Сливочное масло
Кефир Сметана
Килька в томате Смородина
Консервированная кукуруза Сода
Консервированный горошек Соевый соус
Корица Соль
Котлеты свиные Спагетти
Кофе Суповой набор куриный
Крабовые палочки Суповой набор мясной
Крахмал Сухие грибы
Крупа кукурузная Сыр
Крыжовник Творог зернистый
Курица (филе, бедра, окорочка) Тесто
Лавровый лист Томатная паста
Лимон Укроп сушеный
Лук Уксус обычный
Макароны Фасоль
Малина Чай зеленый
Манка Чай Каркаде
Маргарин Чай черный
Масло растительное Черный перец
Масло сливочное Чеснок
Мед Шпинат
Перец Яблоки
Яйца

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

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

  1. Для начала необходимо присвоить ячейкам имя:
  • Выделяется диапазон ячеек;
  • Во вкладке «формулы» выбирается кнопка «присвоить имя»;
  • Ячейкам присваивается имя соответствующее их категории (например, для овощей имя «Овощи»);

На новом листе создается форма для заполнения:

В первой ячейке создается выпадающий список, состоящий из имен ячеек (категорий: овощи, фрукты и т.д.)

  1. Выбирается вкладка «Данные» -> «Проверка данных»
  2. Выбирается «Список» и указывается диапазон, из которого будут выбираться значения.
  3. Нажимаем «ОК»

Во второй ячейке создается список, зависящий от первого.

Создается список аналогично с первым, но в качестве источника данных выбирается не диапазон ячеек, а функция ДВССЫЛ(). В качестве аргумента функции указывается адрес первой ячейки со списком категории.

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

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

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

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

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

Как создать связанные между собой списки

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

К примеру, выбираем в основном списке «картофель». После чего нам предлагается определить размерность (килограммы или граммы).

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

Во-вторых, каждому списку необходимо присвоить свое имя. Эти действия мы уже рассмотрели ранее.

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

  • Дальше выбираем вторую ячейку. Но данные графу источник заполняем следующим образом: «=ДВССЫЛ».
  • И продолжаем в скобках ссылкой на первую ячейку массива.

В итоге мы увидим готовый результат.

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

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

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

Выбор нескольких значений из выпадающего списка Excel

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

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

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выпадающий список с поиском

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
  3. Жмем «Свойства» – открывается перечень настроек.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

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

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

Как в эксель сделать выпадающий список с другого листа?

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

Выделяем нужную область и в верхней левом верхнем углу присваиваем ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы потом диапазонами управлять, нужно открыть вкладку «Формулы» и найти там «Диспетчер имен»:

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

А в строке «Источник» копируем адрес нашего диапазона из «диспетчера имен»…

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

Как создать выпадающий список в Microsoft Excel?

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

Способ № 1.

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

В открывшемся окне, в разделе Параметры нужно найти «Тип данных» и выбрать Список. Чуть ниже появится поле «Источник», где нужно будет указать диапазон значений. Для этого нужно выделить данные (в нашем случае это весь список фамилий с А2 по А10).

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

Способ № 2.

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

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

Далее нужно нажать ОК—Закрыть.

Теперь нужно переключиться на вкладку Данные, выбрать «Проверка данных», в «Тип данных» выбрать «Список», в появившемся поле «Источник» прописать =Авторы (у вас будет свое название листа).

Способ № 3.

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

  • Если такой вкладки нет, то ее можно включить, нажав кнопку Office, выбрав Параметры Excel.
  • В открывшемся окне найти параметр «Показывать вкладку «Разработчик» на ленте», поставить галочку и нажать ОК.
  • Во вкладке Разработчик нажать на команду «Вставить» и выбрать «Поле со списком (элемент управления формы)».

Пока что этот список пустой. Чтобы его заполнить, нужно нажать по созданному прямоугольнику правой клавишей мыши и выбрать Формат объекта. В открывшемся окне можно заполнить до 3 полей.

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

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

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

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

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

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

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

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

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

Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).

Как видим, список создан.

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

Всё, таблица создана.

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

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

Как создать выпадающее меню в MS Excel | MSoffice-Prowork.com

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

Итак, зачем необходим вообще выпадающий список в MS Excel? Первое, что приходит на ум – это заполнение ячейки данными перечень которых ограничен и/или заполнение требует времени на рутинную операцию.

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

  • Выпадающее меню создается с помощью вкладки «Данные», группа «Работа с данными», пункт «Проверка данных»

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

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

Создание выпадающего списка с использованием функции «СМЕЩ»

ПðúÃÂøÃÂõÃÂúø òÃÂõ ôõùÃÂÃÂòøàò ôðýýþù ÃÂð÷ÃÂðñþÃÂúõ ÿÃÂþøÃÂÃÂþôÃÂÃÂ, úðú ò ÿÃÂõôÃÂôÃÂÃÂõù. ÃÂøÃÂàÃÂþûÃÂúþ ýð ÿþÃÂûõôýõü ÃÂÃÂðÿõ òðü ýõþñÃÂþôøüþ ÃÂôõûðÃÂàÃÂûõôÃÂÃÂÃÂõõ:
⢠ÃÂõÃÂõùÃÂø òþ òúûðôúàëÃÂðýýÃÂõû ø ò ñûþúõ ëàðñþÃÂð àôðýýÃÂüøû òÃÂñÃÂðÃÂàëÃÂÃÂþòõÃÂúð ôðýýÃÂÃÂû;
⢠áýþòð ò ÿþÃÂòøòÃÂõüÃÂàþúýõ ò óÃÂðÃÂõ ëâøÿ ôðýýÃÂÃÂû òÃÂÃÂÃÂðòûÃÂõÃÂõ ëáÿøÃÂþúû;
⢠àÿþûõ ëÃÂÃÂÃÂþÃÂýøúû ÷ðÿþûýÃÂõÃÂõ ÷ýðÃÂõýøõ ò òøôõ ÃÂþÃÂüÃÂûÃÂ: =áÃÂÃÂÃÂ(A222;0;0; áçÃÂâÃÂáÃÂÃÂ($A22:2A$100;âÂÂ<>âÂÂ));
ÃÂþýÃÂÃÂÃÂÃÂúÃÂøàòÃÂÿðôðÃÂÃÂõóþ ÃÂÿøÃÂúð ø÷üõýøûðÃÂÃÂ. àýõù ÃÂõÿõÃÂàÿÃÂøÃÂÃÂÃÂÃÂÃÂòÃÂõàÿÃÂøüõýÃÂõüðàÃÂÃÂýúÃÂøÃÂ.

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

В первом случае надо применить функцию ДВССЫЛ, способную преобразовывать в ссылку любой текст. Например, если написано A1, то функция вернет ссылку на ячейку, имеющую такой же адрес. Если же в ячейке написан текст «Маша», то эта формула сможет сделать линк на именованный диапазон с таким названием. Проще говоря, она может менять ссылку и название местами.

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

Во время присвоения имен важно не забывать: нельзя составлять имена диапазонов с пробелами и знаками препинания. Также нельзя начинать их с цифры

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

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

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

Но такой метод имеет ряд существенных недостатков:

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

Использование функции СМЕЩ и ПОИСКПОЗ

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

Для формирования первого списка, который будет влиять на содержимое второго, подойдет и стандартный способ, описанный выше, то есть:

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

А вот для второго списка, на который будет влиять первый, придется выполнять другие действия, а именно создать именованный диапазон с функцией =СМЕЩ, создающей ссылку на ячейку, где указаны исключительно модели определенной марки. А для этого осуществляются следующие действия:

  1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
  2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $

После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

Результат будет следующий:

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

  1. Выделить ячейку G8.
  2. Выбрать команду «Проверка данных» на вкладке «Данные».
  3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

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

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

24

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

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

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

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

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

27

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

Однако рассмотренный способ не единственный. Как вариант допускается использовать функционал, который называется инструментами разработчика. По-другому он называется ActiveX.

При установке данная функция не активна. Поэтому первым делом нужно ее включить.

Чтобы это сделать нам нужно, переключить верхнее меню на вкладку «Файл» и выбрать пункт «Параметры».

  • После открытия дополнительного окна нам нужно обратить свой взор на подпункт «Настройка ленты» и активизировать флажок рядом со строкой «Разработчик».
  • Подтверждаем свои действия, кликнув по кнопке «OK».

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

Продолжение

Дальнейшее действие похоже на предыдущий вариант.

  • Выделяем массив с данными, которые должны превратиться в выпадающее меню.
  • После этого в разделе «Разработчик» кликаем на кнопку «Вставить» и выбираем в появившемся списке «Поле со списком».

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

Дальше нам понадобится режим конструктора. В котором нам интересна кнопка «Свойства элемента управления».

  • Нам будет показано окно, в котором осуществляется управление списком.
  • Переводим свой взгляд на графу «ListFillRange», где указываем диапазон с информацией.

Кликаем на пустую ячейку, и последовательно переходим «Объект ComboBox» — «Edit».

Таким образом, мы получили выпадающий список вторым способом.

Как быстро и правильно сделать выпадающий список в Excel

Назначение этого инструмента

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

Чаще всего они используются в двух случаях:

  1. При необходимости ввода одинаковых значений.
  2. Чтобы не произошел ввод в ячейку некорректных и ошибочных данных.

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

Встроенные возможности

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

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

Поэтому следует знать, как можно сдавать собственные выпадающие списки.

Как создать самому

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

И также потребуется заполнить поле «Источник». Здесь буду располагаться значения, из которых пользователь будет выбирать. Существует несколько вариантов выбора.

  1. Все данные, которые будут появляться в выпадающем списке, можно ввести вручную. При этом все значения следует разделять для русской версии точкой с запятой, а для английской – запятой.
  2. В качестве значений может выступать обычная ссылка на диапазон ячеек. В указанном поле следует просто установить диапазон ячеек, из которых будет формироваться в дальнейшем список. Существенным минусом такого варианта является то, что нельзя использовать ссылки на другие листы или книги.
  3. Именованный диапазон ячеек может также выступать в качестве источника. Правда, его следует предварительно создать. После того как диапазон будет создан, следует в поле «источник» внести имя списка, поставив, естественно, перед ним знак равно. Преимуществом такого метода является то, что пользоваться списками очень удобно в пределах одной книги, особенно актуальна такая возможность для версии Microsoft Excel 2007.
  4. Очень удобно использовать для значений в выпадающем списке именованные динамичные диапазоны, например, прайс-листы. В таком случае при добавлении новых строк будет автоматически изменяться и сам список. Каждый раз перед поиском нужного значения перечень данных будет обновлен.
  5. В некоторых случаях очень удобно использовать связанные выпадающие списки в Excel, когда от выбора в одной ячейке будет зависеть диапазон параметров для выбора в другой.

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

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector