Как сделать выпадающий список в excel 2019: Выпадающий список в ячейке листа

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

Видео

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

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

Способ 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	любое	любое
Быстрый поиск элемента по первым буквам	нет	нет	нет	да
Необходимость использования дополнительной функции ИНДЕКС	нет	нет	да	нет
Возможность создания связанных выпадающих списков	нет	да	нет	нет

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

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

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

1. Откройте нужный документ в Excel, в котором нужно сделать список.  Создайте столбец с данными, из которых будете делать выпадающий список.

2. Выделите его и кликните по этой области левой кнопкой мыши. В меню найдите «Присвоить имя».

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

3. Выделите ячейку, в которую хотите вставить выпадающий список. На панели главного меню вверху документа перейдите во вкладку «Данные». И найдите на ней окошко «

Работа с данными» -> «Проверка данных».

4. Появится новое окно, в котором нужно найти параметр «Тип данных». В нем выберите пункт «Список».

5. Ниже укажите «Источник». Это можно сделать несколькими способами:

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

• Выбрав «Источник» вручную. Нажмите на кнопку в конце строки и выделите диапазон с данными будущего списка.

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

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

Другие полезные советы по Excel:

Фото: pixabay.com

Видео: CHIP

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

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

Способ № 1.

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

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

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

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

Способ № 2.

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

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

Способ № 3.

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

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

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

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

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

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

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

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

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

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

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

Ниже приведены самые простые способы создания выпадающего списка для пользователей MacOS и Windows 10.

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

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

Использование существующих данных

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

(Изображение предоставлено Мишель Рей Уй)

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

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

(Изображение предоставлено Мишель Рей Уй)

Шаг 2. Перейти к Данные затем нажмите на вкладку Проверка данных.

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

(Изображение предоставлено Мишель Рей Уй)

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

(Изображение предоставлено Мишель Рей Уй)

Шаг 4 Нажми на Источник поле. Затем щелкните и перетащите указатель мыши на все ячейки, которые содержат элементы, которые вы хотите использовать в раскрывающемся списке.

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

Шаг 5 Нажмите ОК.

Ручной ввод предметов

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

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

Шаг 2. Перейти к Данные затем нажмите на вкладку Проверка данных открыть диалоговое окно «Проверка данных».

Шаг 3. в настройки выберите вкладку Список под Разрешать. Обязательно отметьте раскрывающийся список «В ячейке», если он еще не установлен.

(Изображение предоставлено Мишель Рей Уй)

Шаг 4 Нажми на Источник поле. Затем введите каждый элемент в раскрывающемся списке, разделяя каждый элемент запятой.

Шаг 5 Нажмите ОК.

(Изображение предоставлено Мишель Рей Уй)

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

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

(Изображение предоставлено Мишель Рей Уй)

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

Шаг 2. Перейти к Данные затем нажмите на вкладку Проверка данных открыть диалоговое окно «Проверка данных».

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

(Изображение предоставлено Мишель Рей Уй)

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

Шаг 5 Нажмите ОК.

(Изображение предоставлено Мишель Рей Уй)

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

Фото предоставлено: Мишель Рей Уй

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

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

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

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

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

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

2. Выберите Данные .

3. Выберите « Проверка данных», чтобы открыть диалоговое окно «Проверка данных».

4. Выберите вкладку « Настройки  ».

5. В разделе «Разрешить» выберите стрелку вниз.

6. Выберите  Список .

7. Поместите курсор в текстовое поле «Источник».

8. Выделите ячейки от E3 до  E10 на листе, чтобы добавить данные из этого диапазона ячеек в список.

9. Выберите ОК . За исключением Excel для Mac, где вы выбираете Готово .

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

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

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

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

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

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

2. Выберите Данные .

3. Выберите « Проверка данных», чтобы открыть диалоговое окно «Проверка данных».

4. Выберите вкладку « Настройки ».

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

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

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

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

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

 

Для реализации задуманного, действуйте в последовательности:

1. Выделите ячейки, в которых нужно выбирать из списка. Например, целый столбец (как выделять – читайте здесь)
2. Нажмите на ленте Данные – Проверка данных
3. В открывшемся окне выберите Тип данных – Список
4. В поле Источник перечислите возможные варианты через точку с запятой
5. Либо укажите ссылку на диапазон, в котором находится список для выбора
6. Нажмите Ок, готово!

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

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

Дополнительные настройки выпадающего списка

Можно сделать некоторые правки, которые влияют на поведение инструмента.

На вкладке «Параметры» есть опции:

• Галка «Игнорировать пустые ячейки» — не будет предлагать в списке пустые значения, если они встречаются в источнике
• Галка «Список допустимых значений». Если она снята, выбор из выпадающего списка excel не будет работать, но программа проверит, чтобы вводимые значения были предусмотрены в источнике. Если внесенной строки нет в списке допустимых – выдаст ошибку

Настройки вкладки «Сообщение для ввода»:

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

Вкладка «Сообщение об ошибке» позволяет задать содержимое диалогового окна, которое появляется при введении ошибочного значения.

• Галка «Выводить сообщение об ошибке». Если она снята, вы можете добавлять в таблицу любые данные, но в раскрывающемся списке будут лишь те, что указаны заранее
• Вид – выбрать операцию, которая будет выполнена при вводе недопустимого значения
  • Останов – ввод будет прерван, пока вы не внесете верные данные
  • Предупреждение – Эксель переспросит, действительно ли вы хотите внести значение не из списка
  • Сообщение – вам напомнят, что такой вариант не предусмотрен, но его все равно можно будет добавить
• «Заголовок» и «Сообщение» — текст диалогового окна

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

На этом всё, если статья вам понравилась – поделитесь ею с друзьями и коллегами. Что-то не получилось – спросите в комментариях!

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

Автор Глеб Захаров На чтение 2 мин. Просмотров 27 Опубликовано 25.07.2019

Ограничить данные, которые могут быть введены в ячейку

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

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

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

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

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

1. Выберите ячейку B3 , чтобы сделать ее активной.

2. Выберите Данные .

3. Выберите Проверка данных , чтобы открыть диалоговое окно Проверка данных.

4. Выберите вкладку Настройки .

5. В разделе «Разрешить» выберите стрелку вниз.

6. Выберите List .

7. Поместите курсор в текстовое поле «Источник».

8. Выделите ячейки E3 – E10 на листе, чтобы добавить данные из этого диапазона ячеек в список.

9. Выберите ОК . За исключением Excel для Mac, где вы выбираете Готово .

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

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

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

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

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

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

2. Выберите Данные .

3. Выберите Проверка данных , чтобы открыть диалоговое окно Проверка данных.

4. Выберите вкладку Настройки .

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

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

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

Видео: создание раскрывающихся списков и управление ими

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

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

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

1. Выберите ячейки, в которых вы хотите разместить списки.

2. На ленте щелкните ДАННЫЕ > Проверка данных .

3. В диалоговом окне установите Разрешить на Список .

4. Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), который вы хотите в раскрывающемся списке, и щелкните OK .

Хотите больше?

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

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

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

Блокируйте клетки, чтобы защитить их

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

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

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

На ленте щелкните вкладку ДАННЫЕ и щелкните Проверка данных .

В диалоговом окне установите Разрешить на Список .

Щелкните в Source .

В этом примере мы используем список с разделителями-запятыми.

Текст или числа, которые мы вводим в поле Source , разделяются запятыми.

И нажмите ОК . Теперь у ячеек есть раскрывающийся список.

Далее, Настройки раскрывающегося списка .

Создание раскрывающегося списка — служба поддержки Office

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

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

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

2. Выберите ячейки, в которые вы хотите ограничить ввод данных.

3. На вкладке Data в разделе Tools щелкните Data Validation или Validate .

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

4. Щелкните вкладку Settings , а затем во всплывающем меню Allow щелкните List .

5. Щелкните поле Source , а затем на листе выберите список допустимых записей.

   Диалоговое окно сворачивается, чтобы лист было удобнее видеть.

6. Нажмите RETURN или щелкните Expand кнопку, чтобы восстановить диалоговое окно, а затем нажмите ОК .

   Советы:

   • Вы также можете ввести значения непосредственно в поле Source , разделив их запятыми.

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

   • Вы можете указать собственное сообщение об ошибке для ответа на ввод неверных данных. На вкладке Data щелкните Data Validation или Validate , а затем щелкните вкладку Error Alert .

См. Также

Применить проверку данных к ячейкам

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

2. Выберите ячейку на листе, в которой требуется раскрывающийся список.

3. Перейдите на вкладку Data на ленте, затем щелкните Data Validation .

4. На вкладке Настройки в поле Разрешить щелкните Список .

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

   Фрукты, овощи, злаки, молочные продукты, закуски

6. Если люди могут оставлять ячейку пустой, установите флажок Игнорировать пустое поле .

7. Установите флажок в раскрывающемся списке в ячейке .

8. Щелкните вкладку Входное сообщение .

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

9. Щелкните вкладку Предупреждение об ошибке .

   • Если вы хотите, чтобы сообщение появлялось, когда кто-то вводит что-то, чего нет в вашем списке, установите флажок Show Alert , выберите вариант в Type и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение появлялось, снимите флажок.

10. Щелкните ОК .

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

Обзор таблиц Excel — служба поддержки Office

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

Узнайте об элементах таблицы Excel

Таблица может включать в себя следующие элементы:

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

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

• Строки с полосами Альтернативная заливка или полосатость в строках помогает лучше различать данные.

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

• Строка итогов После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора таких функций, как СУММ, СРЕДНЕЕ и т. Д.Когда вы выбираете один из этих параметров, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, которая игнорирует строки, которые были скрыты с помощью фильтра по умолчанию. Если вы хотите включить в свои вычисления скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЙ ИТОГ.

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

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

  Информацию о других способах изменения размера таблицы см. В разделе Изменение размера таблицы путем добавления строк и столбцов.

Создать таблицу

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

Чтобы быстро создать таблицу в Excel, сделайте следующее:

1. Выберите ячейку или диапазон данных.

2. Выберите Home > Format как Таблица .

3. Выберите стиль стола.

4. В диалоговом окне Форматировать как таблицу установите флажок рядом с Моя таблица как заголовки , если вы хотите, чтобы первая строка диапазона была строкой заголовка, а затем нажмите ОК .

Также посмотрите видео о создании таблицы в Excel.

Эффективная работа с табличными данными

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

• Использование структурированных ссылок Вместо использования ссылок на ячейки, таких как A1 и R1C1, вы можете использовать структурированные ссылки, которые ссылаются на имена таблиц в формуле.Дополнительные сведения см. В разделе Использование структурированных ссылок с таблицами Excel.

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

Экспорт таблицы Excel на сайт SharePoint

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

Нужна дополнительная помощь?

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

См. Также

Отформатируйте таблицу Excel

Проблемы совместимости таблиц Excel

Сортировка данных в диапазоне или в таблице

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

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

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

Проверьте языковой стандарт Порядок сортировки зависит от языкового стандарта. Убедитесь, что у вас установлен правильный языковой стандарт в Региональные настройки или Региональные и языковые параметры на Панели управления на вашем компьютере.Для получения информации об изменении настройки локали см. Справочную систему Windows.

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

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

• Чтобы исключить первую строку данных из сортировки, поскольку это заголовок столбца, на вкладке Домашняя страница в группе Редактирование щелкните Сортировка и фильтр , щелкните Пользовательская сортировка , а затем выберите Мои данные имеет заголовки .

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

Создать раскрывающееся меню или поле со списком в Excel 2016/2019

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

Создать раскрывающееся меню Рабочий лист Excel

• Откройте электронную таблицу M Microsoft Excel.
• Теперь выберите конкретную ячейку , в которую нужно вставить раскрывающееся меню .
• Затем нажмите на Data на ленте и выберите Data validation в разделе меню Data Tools .

• Далее мы собираемся определить список значений, которые будут разрешены для использования в вашем поле со списком.
• На вкладке «Настройки» нажмите раскрывающееся меню Разрешить и выберите Список .

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

• Важно: более надежный метод — указать список значений (LOV) на другом листе. Затем свяжите LOV с полем «Источник», щелкнув значок справа и указав на диапазон ячеек со списком значений.

• Нажмите ОК , чтобы вставить раскрывающееся меню.

Проверка данных

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

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

Отображение сообщений об ошибках

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

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

Все готово. Спасибо за чтение 🙂

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

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

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

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

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

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

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

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

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

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

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

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

6. Щелкните OK.

Результат:

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

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

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

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

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

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

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

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

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

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

4. Щелкните OK.

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

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

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

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

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

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

Результат:

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

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

Результат:

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

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

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

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

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

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

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

4. Щелкните поле «Источник» и введите формулу: = СМЕЩЕНИЕ (Sheet2! $ A $ 1,0,0, COUNTA (Sheet2! $ A: $ A), 1)

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

5. Щелкните OK.

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

Результат:

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

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

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

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

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

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

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

4. Щелкните OK.

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

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

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

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

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

Стол Magic

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

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

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

3. Excel автоматически выбирает данные за вас. Щелкните ОК.

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

Как добавить раскрывающийся список в ячейку Excel

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

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

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

На рисунке A показан простой раскрывающийся список на листе Excel. Чтобы использовать раскрывающееся меню, показанное здесь, кто-то поместит курсор на пустую ячейку ввода данных (E4 в этом примере) и щелкнет стрелку раскрывающегося списка, чтобы отобразить список значений, показанных в диапазоне ячеек A1: A4. Если пользователь пытается ввести что-то, что не входит в этот список значений, Excel отклоняет ввод.

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

Рисунок A

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

1. Создайте список проверки данных в ячейках A1: A4. Точно так же вы можете ввести элементы в одну строку, например A1: D1.
2. Выберите ячейку E4.(Вы можете разместить раскрывающийся список практически в любой ячейке или даже в нескольких ячейках.)
3. Выберите «Проверка данных» в меню ленты «Данные».
4. Выберите «Список» в раскрывающемся списке «Разрешить». (Видите, они повсюду.)
5. Щелкните поле «Источник» и перетащите курсор, чтобы выделить ячейки A1: A4. Или просто введите ссылку (= $ A $ 1: $ A $ 4).
6. Убедитесь, что в раскрывающемся списке установлен флажок. Если вы снимите этот флажок, Excel по-прежнему заставляет пользователей вводить только значения списка (A1: A4), но не будет отображать раскрывающийся список.
7. Нажмите ОК.

SEE: Как создать раскрывающийся список в Google Таблицах (TechRepublic)

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

Несколько небольших заметок:

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

СМ.: 10 средств экономии времени Excel, о которых вы, возможно, не знали (бесплатный PDF) (TechRepublic)

Бонусный совет Microsoft Excel

Этот совет Excel включен в бесплатный PDF-файл 30 вещей, которые никогда не следует делать в Microsoft Офис.

Полагаться на несколько ссылок

Ссылки между двумя книгами Excel являются общими и полезными. Но несколько ссылок, где значения в workbook1 зависят от значений в workbook2, которые ссылаются на workbook3 и т. Д., Трудны для управления и нестабильны. Пользователи забывают закрыть файлы, а иногда даже перемещают их. Если вы единственный человек, работающий с этими связанными книгами Excel, у вас могут не возникнуть проблемы, но если другие пользователи просматривают и изменяют их, вы напрашиваетесь на проблемы.Если вам действительно нужно столько ссылок, вы можете подумать о новом дизайне.

Получите больше советов по Excel

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