Как в excel 2019 создать выпадающий список: Добавление списка или списка на лист в Excel

Добавление списка или списка на лист в Excel

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

Добавление списка на лист

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

2. На вкладке Разработчик нажмите кнопку Вставить.

   Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

3. В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).

4. Щелкните ячейку, в которой нужно создать список.

5. Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:

   • В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.

     Примечание: 

     Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.

   • В поле Связь с ячейкой введите ссылку на ячейку.

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

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

     Примечание:  Если вы хотите выбрать параметр набора значений

     или списка значений, подумайте о том, чтобы использовать элемент ActiveX «Список».

Добавление поля со списком на лист

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

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

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

   Примечание: Можно также создать список на другом листе той же книги.

2. На вкладке Разработчик нажмите кнопку Вставить.

   Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке

   Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

3. Выберите тип поля со списком, которое нужно добавить:

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

Советы: 

• org/ListItem»>

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

• Чтобы переместить поле со списком на листе, выделите его и перетащите в нужное место.

Форматирование элемента управления формы «Поле со списком»

1. Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.

2. Откройте вкладку Элемент управления и настройте следующие параметры.

   • Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.

   • Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.

     Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.

     Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС.

     В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст «Фруктовое мороженое».

   • Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.

3. Нажмите кнопку

   ОК.

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

org/ItemList»>

1. На вкладке Разработчик нажмите кнопку Режим конструктора.

2. Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.

   Вот как можно настроить свойства поля со списком на этом рисунке:

   Настраиваемое свойство	Действие
   Цвет заливки	Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
   Тип, начертание или размер шрифта	Щелкните свойство Font (Шрифт), нажмите кнопку … и выберите тип, размер или начертание шрифта.
   Цвет шрифта	Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
   Связь с ячейкой для отображения значения, выбранного в списке	Щелкните свойство LinkedCell (Связанная ячейка).
   Связывание поля со списком и списка элементов	Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
   Изменение количества отображаемых элементов списка	Щелкните поле ListRows и введите число элементов.

3. Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.

4. org/ListItem»>

   Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Обзор форм, элементов управления формы и ActiveX элементов управления на сайте

Добавление флажков и переключателей (элементы управления формы)

Выпадающий список в ячейке листа

3886 02.10.2012 Скачать пример

Видео

 У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео:

Кому интересны подробности и нюансы всех описанных способов — дальше по тексту.

Способ 1. Примитивный

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

Способ 2. Стандартный

1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
2. Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать.  Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

Нажмите ОК.

Все! Наслаждайтесь!

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

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

Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:

1. В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel — флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:
   
   Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник — будущий список.
3. Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте

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

После нажатия на ОК списком можно пользоваться.

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

Способ 4. Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

Самые нужные и полезные свойства, которые можно и нужно настроить:

• ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
• ListRows — количество отображаемых строк
• Font — шрифт, размер, начертание (курсив, подчеркивание и т. д. кроме цвета)
• ForeColor и BackColor — цвет текста и фона, соответственно

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

При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

 

Итоговая сравнительная таблица всех способов

	Способ 1. Примитивный	Способ 2. Стандартный	Способ 3. Элемент управления	Способ 4. Элемент ActiveX
Сложность	низкая	средняя	высокая	высокая
Возможность настройки шрифта, цвета и т.д.	нет	нет	нет	да
Количество отображаемых строк	всегда 8	всегда 8	любое	любое
Быстрый поиск элемента по первым буквам	нет	нет	нет	да
Необходимость использования дополнительной функции ИНДЕКС	нет	нет	да	нет
Возможность создания связанных выпадающих списков	нет	да	нет	нет

Ссылки по теме:

• Выпадающий список с данными из другого файла
• Создание зависимых выпадающих списков
• Автоматическое создание выпадающих списков надстройкой PLEX
• Выбор фото из выпадающего списка
• Автоматическое удаление уже использованных элементов из выпадающего списка
• Выпадающий список с автоматическим добавлением новых элементов

Создание раскрывающихся списков в Excel (простое руководство)

Создание раскрывающихся списков | Разрешить другие записи | Добавить/удалить элементы | Динамический раскрывающийся список | Удалить раскрывающийся список | Зависимые раскрывающиеся списки | Table Magic

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

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

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

1. На втором листе введите элементы, которые должны отображаться в раскрывающемся списке.

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

2. На первом листе выберите ячейку B1.

3. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

Появится диалоговое окно «Проверка данных».

4. В поле Разрешить щелкните Список.

5. Щелкните в поле Источник и выберите диапазон A1:A3 на Листе2.

6. Нажмите OK.

Результат:

Примечание: чтобы скопировать/вставить выпадающий список, выберите ячейку с выпадающим списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.

7. Вы можете также вводите элементы непосредственно в поле «Источник» вместо использования ссылки на диапазон.

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

Разрешить другие записи

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

1. Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке.

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

2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

Появится диалоговое окно «Проверка данных».

3. На вкладке «Предупреждение об ошибке» снимите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

4. Нажмите OK.

5. Теперь вы можете ввести значение, которого нет в списке.

Добавить/удалить элементы

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

1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.

2. Щелкните правой кнопкой мыши и выберите Вставить.

3. Выберите «Сдвинуть ячейки вниз» и нажмите «ОК».

Результат:

Примечание. Excel автоматически изменил ссылку на диапазон с Лист2!$A$1:$A$3 на Лист2!$A$1:$A$4. Вы можете проверить это, открыв диалоговое окно «Проверка данных».

4. Введите новый элемент.

Результат:

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

Динамический раскрывающийся список

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

1. На первом листе выберите ячейку B1.

2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

Появится диалоговое окно «Проверка данных».

3. В поле Разрешить щелкните Список.

4. Щелкните в поле Источник и введите формулу: =СМЕЩ(Лист2!$A$1,0,0,СЧЁТ(Лист2!$A:$A),1)

Пояснение: функция СМЕЩ принимает 5 аргументов. Ссылка: Sheet2!$A$1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA(Sheet2!$A:$A) и ширина: 1. COUNTA(Sheet2!$A:$A) подсчитывает число значений в столбце A на Sheet2, которые не пусты. Когда вы добавляете элемент в список на Листе2, СЧЕТЧИК(Лист2!$A:$A) увеличивается. В результате диапазон, возвращаемый функцией OFFSET, расширяется, а выпадающий список обновляется.

5. Нажмите OK.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

Удалить раскрывающийся список

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

1. Выберите ячейку из раскрывающегося списка.

2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

Появится диалоговое окно «Проверка данных».

3. Щелкните Очистить все.

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

4. Нажмите OK.

Зависимые раскрывающиеся списки

Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.

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

2. Второй раскрывающийся список содержит пункты Пицца.

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

Table Magic

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

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

2. На вкладке Вставка в группе Таблицы щелкните Таблица.

3. Excel автоматически выбирает данные за вас. Нажмите «ОК».

4. Если выбрать список, Excel покажет структурированную ссылку.

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

Объяснение: функция ДВССЫЛ в Excel преобразует текстовую строку в допустимую ссылку.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

Примечание: попробуйте сами. Загрузите файл Excel и создайте этот раскрывающийся список.

7. При использовании таблиц используйте функцию UNIQUE в Excel 365/2021 для извлечения уникальных элементов списка.

Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.

8. Используйте этот диапазон разлива, чтобы создать волшебный раскрывающийся список.

Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.

Результат:

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

Как создать раскрывающийся список в Excel: динамический, редактируемый, с возможностью поиска

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

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

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

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

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

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

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

1. Выберите одну или несколько ячеек, в которых должен отображаться раскрывающийся список. Это может быть одна ячейка, диапазон ячеек или целый столбец. Чтобы выделить несколько несмежных ячеек, нажмите и удерживайте клавишу Ctrl.
2. На вкладке Data в группе Data Tools щелкните Data Validation .
3. На вкладке Параметры диалогового окна Проверка данных выполните следующие действия.
   • В поле Разрешить выберите Список .
   • В поле Источник введите элементы, разделенные запятой с пробелами или без них. Или выберите диапазон ячеек на листе, содержащем элементы.
   • Убедитесь, что в раскрывающемся списке В ячейке установлен флажок (по умолчанию), иначе стрелка раскрывающегося списка не будет отображаться рядом с ячейкой.
   • Установите или снимите флажок Игнорировать пустые в зависимости от того, как вы хотите обрабатывать пустые ячейки.
   • Когда закончите, нажмите OK.

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

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

Совет. Чтобы ускорить ввод данных в листы Excel, вы также можете использовать форму ввода данных.

Сделать выпадающее меню из диапазона ячеек

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

1. Начните с создания списка элементов, которые вы хотите включить в раскрывающийся список. Для этого просто введите каждый элемент в отдельную ячейку. Это можно сделать на том же рабочем листе, что и раскрывающийся список, или на другом листе.
2. : Выберите ячейку (ячейки), которые должны содержать список.
3. На ленте щелкните вкладку Данные > Проверка данных .
4. В диалоговом окне Проверка данных выберите Список в раскрывающемся меню Разрешить . Поместите курсор в поле Источник и выберите диапазон ячеек, содержащих элементы, или щелкните значок Свернуть диалоговое окно и затем выберите диапазон. Когда закончите, нажмите OK.

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

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

Вставить раскрывающийся список из именованного диапазона

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

1. Составьте список предметов на листе. Значения должны быть введены в один столбец или строку без пустых ячеек.

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

2. Создайте именованный диапазон. Самый быстрый способ — выбрать ячейки и ввести нужное имя прямо в поле имени . Когда закончите, нажмите Enter, чтобы сохранить только что созданный именованный диапазон. Дополнительные сведения см. в разделе, как определить имя в Excel.

   В качестве примера создадим диапазон с именем Ingredients :

3. Выберите ячейки для раскрывающегося списка — на том же листе, что и именованный диапазон, или на другом листе.
4. Откройте диалоговое окно Data Validation и настройте правило:
   • В поле Разрешить выберите Список .
   • В поле Источник введите знак равенства, за которым следует имя диапазона. В нашем случае это = Ингредиенты .
   • Щелкните OK .

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

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

Недостатки : Настройка занимает немного больше времени.

Создать раскрывающийся список из таблицы Excel

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

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

1. Выберите ячейки, в которые вы хотите вставить раскрывающийся список.
2. Откройте диалоговое окно Проверка данных .
3. Выберите Список в раскрывающемся списке Разрешить .
4. В новом поле Источник введите формулу, относящуюся к определенному столбцу в таблице, не включая ячейку заголовка. Для этого используйте функцию ДВССЫЛ с такой структурированной ссылкой:

   = ДВССЫЛ ("Имя_таблицы[имя_столбца]")

5. Когда закончите, нажмите OK .

В этом примере мы создаем раскрывающееся меню из столбца с именем Ингредиенты в Таблица 1 :

= ДВССЫЛ ("Таблица 1 [Ингредиенты]")

Преимущества : Простой и быстрый способ вставить расширяемое динамическое выпадающее меню в Excel.

Недостатки : Не найдено 🙂

Как создать динамический выпадающий список в Excel

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

Самый быстрый способ создать динамическое раскрывающееся меню в Excel — использовать таблицу, как показано выше. Это поведение таблиц Excel по умолчанию; никаких дополнительных настроек или перемещений не требуется.

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

1. Введите элементы раскрывающегося меню в отдельные ячейки.
2. Создайте именованную формулу. Для этого нажмите Ctrl + F3, чтобы открыть диалоговое окно New Name . Введите нужное имя в поле Имя , а затем введите следующую формулу в поле Относится к .

   =СМЕЩЕНИЕ(Лист3!$A$2, 0, 0, СЧЁТ(Лист3!$A:$A), 1)

   Где:

   • Sheet3 — имя листа
   • A — столбец, в котором расположены выпадающие элементы
   • $A$2 — ячейка, содержащая первый элемент
3. Определив имя формулы, создайте раскрывающийся список на основе именованного диапазона, как обычно.

Как работает эта формула

Формула содержит две функции — СМЕЩ и СЧЕТЧИК. Функция COUNTA подсчитывает все непустые значения в указанном столбце. OFFSET использует этот счетчик для аргумента height , поэтому он возвращает ссылку на диапазон, который включает только непустые ячейки, начиная с ячейки, содержащей первый элемент, указанный вами для ссылка аргумент.

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

Недостатки : Немного сложный процесс установки.

Создание динамического раскрывающегося списка в Excel 365/2021

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

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

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

=УНИКАЛЬНЫЙ(A2:A21)

При желании вы можете отсортировать извлеченные значения в алфавитном порядке, заключив их в функцию SORT:

=СОРТИРОВКА(УНИКАЛЬНАЯ(A2:A21))

Эта формула динамического массива вводится только в одну ячейку (E2) и автоматически распределяется по стольким ячейкам, сколько необходимо для отображения всех уникальных элементов.

Затем вы настраиваете раскрывающийся список, используя ссылку на диапазон разлива, которая представляет собой адрес ячейки, за которым следует символ решетки. В нашем случае это =$E$2# или =Sheet1!$E$2#, если раскрывающийся список находится на другом листе:

Результатом является расширяемый динамический раскрывающийся список — функция UNIQUE автоматически извлекает новые элементы по мере их добавления в исходную таблицу, а ссылка на диапазон разброса заставляет Excel соответствующим образом обновлять раскрывающийся список.

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

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

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

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

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

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

1. В исходной книге создайте именованный диапазон для исходного списка, скажем, Source_list .
2. В основной книге определите имя, которое ссылается на ваш список источников. Для этого примера мы создаем имя Items , которое относится к:

   =SourceFile.xlsx!Source_list

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

   =’Исходный файл.xlsx’!Source_list

   Дополнительные сведения см. в разделе Как сделать внешнюю ссылку в Excel.

3. В основной книге выберите ячейки для раскрывающегося списка и щелкните вкладку Данные > Проверка данных . В поле Source укажите имя, созданное на шаге 2. В нашем случае это =Items.

Примечания:

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

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

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

Раскрывающийся список с возможностью поиска в Excel 365

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

Вставить раскрывающийся список с сообщением

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

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

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

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

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

1. Откройте диалоговое окно Проверка данных .
2. На вкладке Оповещение об ошибке снимите флажок Показывать оповещение об ошибке после ввода неверных данных .

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

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

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

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

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