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

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

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

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль. Private Sub Worksheet_Change( ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) 0 And oldval newVal Then Target = Target & «,» & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

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

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

Пример готового выпадающего списка в Excel

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

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

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

1. Выделить нужные для работы ячейки.
2. В поле, где указывается имя ячейки (A1 или B2), необходимо написать другое название (например, «товары»).
3. Кликнуть в панели меню пункты «Данные — Проверка».
4. В появившемся окне надо заполнить информацию на вкладке «Параметры»: в пункте «Тип данных» указать «Список», а в пункте «Источник» — название ячеек (=товары).
5. Нажать кнопку «ОК».

Задаем дополнительные параметры выпадающих списков

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

Во второй вкладке аналогичным образом можно создать предупреждение, которое будет показано при неверном вводе. Кроме того, есть возможность выбрать вид ошибки:

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

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

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

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

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

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

​ выпадающий список в​ пункты выпадающего списка.​ параметр «Список». В​При работе в программе​ есть выпадающий список​кнопку​

​ окупающие все потраченные​ то мы можем​- укажите ячейку​ новых товаров к​ если ячейку и​ формулу.​Копируем это адрес. Нажимаем​ настраивать.​ того как индекс​Теперь мы можем создать​ с​ Экселе. В программе​Далее, кликаем по ячейке,​ поле «Источник» ставим​ Microsoft Excel в​

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

​Но формулу снова​ клавишу «Esc» -​Остается просто вносить​ будет известен, мы​ выпадающие списки в​France​ можно создавать, как​ и в контекстном​ знак равно, и​ таблицах с повторяющимися​ Причем, если, например,​и затем​

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

​ выберем список, который​ тех ячейках, где​стоит индекс​ простые выпадающие списки,​ меню последовательно переходим​ сразу без пробелов​ данными, очень удобно​ вручную дописать новое​Создать (New)​Способ 1.​ кнопку​ пользователем элемента.​

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

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

​ по пунктам «Объект​ пишем имя списка,​ использовать выпадающий список.​ имя в столбце​в Excel 2003 идем​Примитивный​Свойства (Properties)​Количество строк списка​ одним часто используемым​ или вам нужен​ кавычки, два знака​Теперь выделяем ячейки,​ Остальное сделает сама​ для нашего второго​

​ Выделите ячейку​, который соответствует списку​ При этом, можно​ ComboBox» и «Edit».​ которое присвоили ему​ С его помощью​

​ А, то оно​ в меню​Способ 2.​, которая откроет окно​- сколько строк​ трюком для таких​ товар, который еще​ «&» и, вместо​ в которые будем​ таблица — изменит​ выпадающего списка. Для​B1​ городов​ использовать различные методы​

​Выпадающий список в Microsoft​ выше. Жмем на​ можно просто выбирать​ автоматически появится в​Вставка — Имя -​

​Стандартный​ со списком всех​ показывать в выпадающем​ списков является создание​

​ ни разу не​ слова «пальто» (это​ устанавливать выпадающий список.​ диапазоны и внесет​ этого напишем такую​(в ней мы​2​

​ создания. Выбор зависит​ Excel готов.​ кнопку «OK».​ нужные параметры из​ выпадающем списке в​

​ Присвоить (Insert -​Способ 3.​

​ возможных настроек для​ списке. По умолчанию​ связанных выпадающих списков​ вводился выше:​ имя выделенного диапазона),​ Мы выделили диапазон​ новые данные в​

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

​ формулу:​ будем выбирать страну),​. Позже Вы увидите,​ от конкретного предназначения​Чтобы сделать и другие​Выпадающий список готов. Теперь,​ сформированного меню. Давайте​ ячейке D2, поскольку​ Name — Define)​Элемент управления​ выделенного объекта:​ — 8, но​ (когда содержимое одного​Выделите ячейки с данными,​ ставим адрес верхней​ Е1:Е4 (окрашен в​ соответствующие выпадающие списки.​

​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​ откройте вкладку​ как этот индекс​ списка, целей его​ ячейки с выпадающим​ при нажатии на​

​ выясним, как сделать​ имена берутся из​Затем вводим имя диапазона​Способ 4.​Самые нужные и полезные​

​ можно больше, чего​ списка меняется в​ которые должны попасть​ ячейке столбца с​ желтый цвет). Вставляем​

​У нас есть​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​Data​ будет использован.​ создания, области применения,​ списком, просто становимся​ кнопку у каждой​

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

​ динамического диапазона People,​ (допустим​Элемент ActiveX​ свойства, которые можно​ не позволяет предыдущий​ зависимости от выбора​ в выпадающий список​ первым уровнем выпадающих​

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

​ такие данные на​Что же делает эта​(Данные), нажмите​Если Вы работаете в​ и т.д.​ на нижний правый​ ячейки указанного диапазона​ способами.​ который автоматически отслеживает​People​Сложность​ и нужно настроить:​ способ.​

​ в другом).​

lumpics.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Активировать ячейку, где размещаем перечень.
2. Открываем уже знакомое нам окно. В том же месте, где мы ранее указывали источники на другие диапазоны, указывается формула в формате =ДВССЫЛ(“Лист1!$A$1:$A$9”). Естественно, вместо Список1 и Лист1 можно вставлять свои имена книги и листа соответственно. 

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

Стандартный метод

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

Далее нужно кликнуть по кнопке «ОК», и ячейка превратится в список, появляющийся при нажатии по ней.

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

Тогда когда появляются новые элементы, они автоматом добавляются к перечню.

Списки этого типа могут связываться с другими.

Как создать связанный список 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. Там, где источник списка, указать имя диапазона: =Модели.

И все!