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

Содержание

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

Excel 2007-2013

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

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

Excel Online

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

В Excel Online щелкните «Открыть в Excel» для открытия файла в классическом приложении Excel.
В классическом приложении создайте раскрывающийся список.
Теперь сохраните вашу книгу.
В Excel Online откройте книгу для просмотра и использования раскрывающегося списка.

Узнайте больше о работе с раскрывающимися списками в Excel Online

Excel для Mac 2011

Выберите ячейки, в которой должен отображаться список.
На вкладке «Данные» в разделе «Инструменты» щелкните «Проверить».
Щелкните вкладку «Параметры», а затем во всплывающем меню «Разрешить» выберите пункт «Список».
Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.
Чтобы закрыть диалоговое окно, в щелкните «ОК».

Узнайте больше о работе с раскрывающимися списками в Excel Online Узнайте больше о создании раскрывающихся списков в Excel для Mac 2011

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

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

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

Процесс создания списка

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

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

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

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

На основе данных из перечня

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

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

Выбрать ячейку, отведенную под будущий перечень.
На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных».
6
Найти пункт «Тип данных» и переключить значение на «Список».
7
В поле, обозначающем опцию «Источник», ввести нужный диапазон. Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась.
8

Кроме этого, предусмотрена функция генерации списков сразу больше, чем в одной ячейке. Чтобы этого добиться, следует выбрать их все, и совершить аналогичные описанным раньше действия. Снова необходимо удостовериться, что записаны абсолютные ссылки. Если в адресе нет значка доллара возле названия колонки и ряда, то нужно их добавить путем нажатия клавиши F4 до тех пор, пока возле названия столбца и ряда не будет стоять знак $.

С ручной записью данных

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

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

Нажать по ячейке, отведенной под перечень.
Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
9
Снова выбираем тип «Список».
10
Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

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

Создание раскрывающегося списка при помощи функции СМЕЩ

Кроме классического метода возможно применение функции

СМЕЩ, чтобы генерировать выпадающие меню.

Откроем лист.

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

Выбрать интересующую ячейку, где надо разместить будущий список.
Открыть последовательно вкладку «Данные» и окно «Проверка данных».
13
Задаем «Список». Делается это аналогично предыдущим примерам. Наконец, используется такая формула: =СМЕЩ(A$2$;0;0;5). Мы ее вводим там, где задаются ячейки, которые будут использоваться в качестве аргумента.

Потом программой создастся меню с перечнем фруктов.

Синтаксис этой такой:

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

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

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

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

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

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

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

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

Выпадающий список с данными другого листа или файла Excel

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

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

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

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

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

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

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

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

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

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

Иногда нет возможности отдать предпочтение только одному значению, поэтому надо выбрать больше одного. Тогда надо добавить в код страницы макрос. С использованием комбинации клавиш Alt + F11 открывается редактор Visual Basic. И туда вставляется код.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(“Е2:Е9”)) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False

If Len(Target.Offset(0, 1)) = 0 Then

Target.Offset(0, 1) = Target

Else

Target.End(xlToRight).Offset(0, 1) = Target

End If

Target.ClearContents

Application.EnableEvents = True

End If

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(“Н2:К2”)) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False

If Len(Target.Offset(1, 0)) = 0 Then

Target.Offset(1, 0) = Target

Else

Target.End(xlDown).Offset(1, 0) = Target

End If

Target.ClearContents

Application.EnableEvents = True

End If

End Sub

Ну и наконец, для записи в одной ячейке используется этот код.

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

Диапазоны редактируемы.

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

В этом случае надо изначально использовать другой тип перечня. Открывается вкладка «Разработчик», после чего надо кликнуть или тапнуть (если экран сенсорный) на элемент «Вставить» – «ActiveX». Там есть «Поле со списком». Будет предложено нарисовать этот список, после чего он добавится в документ.

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

Выпадающий список с автоматической подстановкой данных

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

Создать набор ячеек для будущего перечня. В случае с нами это набор цветов. Выделяем его.
14
Далее его необходимо отформатировать, как таблицу. Нужно нажать одноименную кнопку и осуществить выбор стиля таблицы. 15
16

Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».

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

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

Выбрать ячейку, где перечень располагается.
Открыть диалог «Проверка данных».
19
Тип данных выставляем «Список», а как значения даем имя таблицы через знак =. 20
21

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

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

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

Для копирования достаточно использовать комбинацию клавиш Ctrl + C и Ctrl + V. Так выпадающий список будет скопирован вместе с форматированием. Чтобы убрать форматирование, нужно воспользоваться специальной вставкой (в контекстном меню такая опция появляется после копирования списка), где выставляется опция «условия на значения».

Выделение всех ячеек, содержащих выпадающий список

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

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

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

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

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

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

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

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

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

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

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

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

Фото: pixabay.com

Видео: CHIP

Выпадающие списки

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

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

Выпадающий список с мультивыбором

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

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

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

Подробнее… Выпадающий список в ячейке листа

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

Подробнее… Выбор фото из выпадающего списка

Хотите выбирать наименование товара из выпадающего списка и получать в соседней ячейке фотографию выбранного продукта? Или выбирать человека из списка и получить рядом его фотографию? Причем без всякого программирования — только с помощью формул?

Связанные (зависимые) выпадающие списки

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

Выпадающий список с добавлением новых элементов

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

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

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

выбрать элементы с выпадающими списками.

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

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

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

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

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

Я люблю Excel достаточно, что даже простая проверка данных + условное форматирование радует меня
— x — lily (@ungilded) 25 октября 2017 г.

Excel не легко

это мощно. Это означает, что у вас есть множество вариантов, как вы вскоре увидите.

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

1. Создайте именованный диапазон

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

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

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

2. Добавьте проверку данных

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

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

3. Добавить именованный диапазон к проверке данных

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

=Food

Заменить «питаниеС любым именем, которое вы дали вашему диапазону клеток. Нажмите Хорошо.

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

Советы Тома для Excel: используйте проверку данных вместо комментария для выбранной ячейки: Alt + D + L> вкладка «Входное сообщение»> введите msg> Ck Показать входные сообщения…
— Том Уртис / Атлас (@TomUrtis) 22 января 2017 г.

4. Установите входное сообщение для проверки данных

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

Нажмите Хорошо.

Когда вы выбираете ячейку

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

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

Расширенные параметры выпадающего списка

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

Изменить или удалить именованный диапазон

Если вам нужно отредактировать или удалить именованный диапазон, вы должны использовать Имя менеджера. Нажмите на Формулы вкладка, а затем нажмите Имя менеджера в Определенные имена раздел.

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

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

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

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

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

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

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

Только что узнал, как создать зависимый выпадающий список в Excel, например, cell1-state, cell2-city в этом состоянии. РЕБЯТА! Это меняет жизнь.
— Жизнь в Deloitte (@lifeatdeloitte) 4 февраля 2014 г.

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

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

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

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

Нажмите на Данные вкладка, а затем нажмите Проверка данных в Инструменты данных раздел. Выбрать Список в Разрешать выпадающий список.

Введите следующий текст в Источник коробка. Заменить «$ B $ 2»Со ссылкой на ячейку, содержащую ваш основной выпадающий список. Держите знаки доллара в ячейке ссылки. Это указывает на абсолютную ссылку на ячейку, которая не изменится, даже если вы скопируете или переместите формулу, которая ссылается на эту ячейку.

=INDIRECT($B$2)

Функция INDIRECT возвращает ссылку, указанную в текстовой строке, в данном случае текст из опции, выбранной в основном раскрывающемся списке в ячейке B2. Например, если вы выберете китайский язык из основного выпадающего списка, = ДВССЫЛ ($ B $ 2) возвращает китайский язык ссылка. В результате второй выпадающий список содержит китайский язык Предметы.

Нажмите Хорошо.

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

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

Если вам нужно продублировать раскрывающийся список с проверкой данных на другие ячейки, скопируйте и вставьте ячейку, используя Ctrl + C а также Ctrl + V. Это копирует выпадающий список и форматирование.

Если вы просто хотите скопировать выпадающий список с проверкой данных, но НЕ с форматированием, выберите ячейку и скопируйте ее обычным способом, используя Ctrl + C. Затем перейдите к Главная вкладка и нажмите Вставить в буфер обмена раздел. Выбрать Специальная паста.

На Специальная паста диалоговое окно, выберите Проверка в Вставить раздел. Затем нажмите Хорошо.

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

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

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

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

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

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

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

Нажмите Хорошо.

Наши два выпадающих списка выбраны.

Теперь вы можете отформатировать эти ячейки

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

Сделать стрелку выпадающего списка всегда видимой

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

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

Для начала загрузите выпадающий arrow.png файл (щелкните правой кнопкой мыши по этой ссылке и выберите Сохранить ссылку как). Затем выберите ячейку справа от выпадающего списка и перейдите к Вставить Вкладка.

Затем нажмите иллюстрации и выберите Фотографий.

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

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

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

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

Варианты на Проверка данных диалоговое окно сбрасывается до значений по умолчанию. Нажмите Хорошо.

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

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

Сделайте так, чтобы выпадающие списки работали для вас

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

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

еще?

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

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

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

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

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

Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
На экране появится окно “Создание имени”. Называем список так, как хочется, но с условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

Создание списка с применением инструментов разработчика

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

В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
В результате мы получаем выпадающий список с заранее определенным перечнем.
Чтобы вставить его в несколько ячеек, наводим курсор на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

Связанный список

У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.

Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
Задаем ему имя, например, “Питание”.
Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
В качестве источника указываем “=Питание” (согласно нашему названию).
Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Заключение

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

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

Последнее обновление 02.12.2019, 16:43 от пользователя Макс Вега .

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

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

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

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

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

Перейдите на вкладку Настройки и найдите поле Разрешить. В открывшемся меню выберите Список:

Ввод данных

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

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

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

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

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

Изображение: © Dzmitry Kliapitski — 123rf.com

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

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

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

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

Выберите ячейки, в которых вы хотите разместить списки.
На ленте щелкните ДАННЫЕ > Проверка данных .
В диалоговом окне установите Разрешить на Список .
Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), который вы хотите в раскрывающемся списке, и щелкните OK .

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

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

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

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

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

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

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

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

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

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

Щелкните в Source .

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

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

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

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

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

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

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

Создайте список допустимых записей для раскрывающегося списка, набранный на листе в одном столбце или строке без пустых ячеек.
Выберите ячейки, в которые вы хотите ограничить ввод данных.
На вкладке Data в разделе Tools щелкните Data Validation или Validate .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
Щелкните вкладку Settings , а затем во всплывающем меню Allow щелкните List .
Щелкните поле Source , а затем на листе выберите список допустимых записей.
Диалоговое окно сворачивается, чтобы лист было удобнее видеть.
Нажмите RETURN или щелкните Expand кнопку, чтобы восстановить диалоговое окно, а затем нажмите ОК .
Советы:
- Вы также можете ввести значения непосредственно в поле Source , разделив их запятыми.
- Чтобы изменить список допустимых записей, просто измените значения в исходном списке или отредактируйте диапазон в поле Источник .
- Вы можете указать собственное сообщение об ошибке для ответа на ввод неверных данных. На вкладке Data щелкните Data Validation или Validate , а затем щелкните вкладку Error Alert .

См. Также

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

На новом листе введите записи, которые должны появиться в раскрывающемся списке.В идеале у вас будут элементы списка в таблице Excel.
Выберите ячейку на листе, в которой требуется раскрывающийся список.
Перейдите на вкладку Data на ленте, затем щелкните Data Validation .
На вкладке Настройки в поле Разрешить щелкните Список .
Если вы уже создали таблицу с раскрывающимися записями, щелкните поле Source , а затем щелкните и перетащите ячейки, содержащие эти записи. Однако не включайте ячейку заголовка. Просто включите ячейки, которые должны появиться в раскрывающемся списке. Вы также можете просто ввести список записей в поле Source , разделив их запятыми, например:
Фрукты, овощи, злаки, молочные продукты, закуски
Если люди могут оставлять ячейку пустой, установите флажок Игнорировать пустое поле .
Установите флажок в раскрывающемся списке в ячейке .
Щелкните вкладку Входное сообщение .
- Если вы хотите, чтобы при щелчке по ячейке появлялось всплывающее сообщение, установите флажок Показать сообщение и введите заголовок и сообщение в поля (до 225 символов).Если вы не хотите, чтобы сообщение появлялось, снимите флажок.
Щелкните вкладку Предупреждение об ошибке .
- Если вы хотите, чтобы сообщение появлялось, когда кто-то вводит что-то, чего нет в вашем списке, установите флажок Show Alert , выберите вариант в Type и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение появлялось, снимите флажок.
Щелкните ОК .

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

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

Раскрывающиеся списки в 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 на листе Sheet2, которые не являются пустыми. Когда вы добавляете элемент в список на 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 [Пошаговое руководство]

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

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

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

Ниже приведен пример раскрывающегося списка Excel:

В приведенном выше примере я использовал элементы в A2: A6 для создания раскрывающегося списка в C3.

  Прочтите:  Вот подробное руководство по созданию раскрывающегося списка Excel.

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

В Excel они называются зависимыми раскрывающимися списками.

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

Вы можете видеть, что параметры в раскрывающемся списке 2 зависят от выбора, сделанного в раскрывающемся списке 1. Если я выберу «Фрукты» в раскрывающемся списке 1 мне показаны названия фруктов, но если я выберу овощи в раскрывающемся списке 1, то мне будут показаны названия овощей в раскрывающемся списке 2.

Это называется условным или зависимым раскрывающимся списком в Excel.

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

Вот шаги для создания зависимого раскрывающегося списка в Excel:

Теперь, когда вы сделаете выбор в раскрывающемся списке 1, параметры, перечисленные в раскрывающемся списке 2, будут автоматически Обновить.

Загрузите файл примера

Как это работает? — Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3).Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание: Если основная категория состоит из нескольких слов (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ” _ ”)) Вместо простой функции ДВССЫЛ, показанной выше.

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

Сброс / очистка содержимого зависимого раскрывающегося списка автоматически

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

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

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

Вот код VBA для очистки содержимого зависимого раскрывающегося списка:

 Private Sub Worksheet_Change (ByVal Target As Range)
При ошибке Возобновить Далее
Если Target.Столбец = 4 Тогда
 Если Target.Validation.Type = 3, то
 Application.EnableEvents = False
 Target.Offset (0, 1) .ClearContents
 Конец, если
Конец, если
exitHandler:
 Application.EnableEvents = True
 Выйти из подводной лодки
End Sub

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

Вот как заставить этот код работать:

Скопируйте код VBA.
В книге Excel, где у вас есть зависимый раскрывающийся список, перейдите на вкладку «Разработчик» и в группе «Код» нажмите Visual Basic (вы также можете использовать сочетание клавиш — ALT + F11).
В окне редактора VB слева в проводнике проекта вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.
Вставьте код в окно кода справа.
Закройте редактор VB.

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

Загрузите файл примера

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

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

Выберите ячейку, в которой есть зависимые раскрывающиеся списки.
Перейдите на главную страницу -> Условное форматирование -> Новое правило.
В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения ячеек для форматирования».
В поле формулы введите следующую формулу: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
Установите формат.
Нажмите ОК.

В формуле используется функция ВПР, чтобы проверить, является ли элемент в зависимом раскрывающемся списке элементом из основной категории. Если это не так, формула возвращает ошибку. Это используется функцией ISERROR для возврата TRUE, что указывает условному форматированию выделить ячейку.

Загрузите файл примера

Вам также могут понравиться следующие руководства по Excel :

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

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

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

Метод 1. Вручную

Если вам нужен простой вариант (например, Черный / Белый ; Да / Нет / Не знаю и т. Д.), То самый быстрый способ — сделать это вручную.

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

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

Нажмите ОК.

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

Загрузите бесплатный файл практики с раскрывающимся списком!

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

Метод 2 — Ссылка на данные из других ячеек

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

Для этого:

Конечно, сначала вам нужно настроить источник или список в другом месте.Мы ввели исходные данные в ячейки с A2 по A7 на другом листе с именем Source в той же книге.
Затем выберите ячейку или диапазон ячеек, в которых вы хотите создать раскрывающийся список. В нашем примере мы выбрали B2: C5.

Перейдите на вкладку «Данные». В группе команд Data Tools выберите значок Data Validation .

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

Нажмите ОК.

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

Метод 3 — Формула СМЕЩЕНИЯ (динамические раскрывающиеся списки)

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

Синтаксис функции СМЕЩЕНИЕ:

  = СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

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

Ссылка — это ячейка или диапазон ячеек (смежных), из которых можно основывать смещение. Ссылка — это отправная точка.
Строки — это количество строк (вниз или вверх), на которое нужно переместиться от начальной точки. Если строк, — положительное число, формула перемещается вниз от начальной ссылки.В случае отрицательного числа оно идет вверх от начальной ссылки.
Cols — количество столбцов, на которое формула должна переместиться от начальной точки. Как и строки, cols могут быть положительными (справа от начальной ссылки) или отрицательными (слева от начальной ссылки).
Высота — количество строк, которое должен содержать результат. Если опущено, используется высота , ссылка .
Ширина — количество столбцов, которые должен содержать результат.Если опущено, используется ширина ссылки .

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

Нам нужно, чтобы столбцы B и C на листе Games отображали раскрывающийся список всех клубов лиги. Исходные данные о названиях клубов будут взяты из другого листа (Источник) в этой книге.

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

На вкладке Данные на ленте в группе Работа с данными щелкните Проверка данных. Откроется диалоговое окно «Проверка данных».
В поле Разрешить щелкните Список.
Щелкните в поле «Источник» и введите формулу:

  = СМЕЩЕНИЕ (Источник! $ A $ 2,0,0, COUNTA (Источник! $ A: $ A), 1)

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

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

Результатом будет только один столбец шириной, поэтому 1 — последний аргумент в нашей формуле.

Нажмите ОК.

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

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

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

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

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

Для этого:

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

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

Скопировать правило проверки данных из другой ячейки

Если есть ячейка с правилом проверки данных, которое вы хотите скопировать в другую ячейку:

Перейдите в ячейку, содержащую правило, и скопируйте.
Перейдите в целевую ячейку и на вкладке «Главная» щелкните стрелку раскрывающегося списка «Вставить».
Выберите Специальная вставка .
В диалоговом окне Специальная вставка выберите переключатель Проверка и нажмите кнопку ОК.

Добавить элемент в раскрывающийся список Excel

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

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

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

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

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

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

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

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

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

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

Выберите ячейку (я) в раскрывающемся списке.
На вкладке «Данные» в группе «Работа с данными» щелкните Проверка данных .
При разрешении , измените List на Любое значение .

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

Нажмите ОК.

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

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

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

Тогда в столбцах C и D будут представлены только те клубы, которые принадлежат стране, выбранной в столбце B этой строки.

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

Шаг 1 : Создайте список стран, которые станут вашим «родительским» списком проверки данных. Мы включили Англию, Испанию и Италию в наш родительский список.

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

Шаг 3 : Создайте именованный диапазон для списка каждой страны, имена в котором совпадают с именами вашего родительского списка проверки данных.

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

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

Перейдите на вкладку Данные> щелкните значок Проверка данных> щелкните Список из раскрывающегося списка Разрешить > перейдите в поле Источник и выберите диапазон $ A $ 2: $ A $ 4 на листе источника.

Для отображения зависимого списка Опции :

Выберите ячейки, в которых мы хотим, чтобы зависимые значения отображались в раскрывающемся списке.Мы выбрали C2: D5.
Перейдите на вкладку «Данные»> щелкните значок «Проверка данных»> щелкните «Список » в раскрывающемся списке « Разрешить »> перейдите в поле «Источник» и введите = КОСВЕННО (B2).

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

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

Поэтому мы введем = КОСВЕННО ($ B2)

Теперь наши зависимые списки в столбцах C и D прекрасно работают, причем параметры меняются в зависимости от того, что было выбрано в столбце B.

Узнать больше

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

Изучайте Excel бесплатно

Начните изучать формулы, функции и полезные советы прямо сегодня с этого бесплатного курса!

Начать бесплатный курс

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

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

Есть несколько способов сделать это, от простых до сложных. В этой статье вы узнаете, как это сделать.

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

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

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

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

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

2. Выберите Data из меню, а затем выберите Data Validation на ленте.

3. Откроется окно проверки данных. Выберите раскрывающийся список под Разрешить и выберите Список .

4. Это позволяет вам вводить элементы, которые вы хотите в свой список, вручную.Просто введите каждый элемент в поле Source , разделив каждый элемент запятыми.

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

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

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

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

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

Для этого:

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

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

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

4. Вы увидите, что выбранный диапазон теперь отображается в поле «Источник». Просто выберите OK , чтобы принять эти настройки.

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

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

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

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

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

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

Для этого:

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

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

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

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

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

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

Динамическое добавление элементов в список

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

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

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

= СМЕЩЕНИЕ ($ E $ 1,0,0, COUNTA ($ E: $ E), 1)

Измените $ E $ 1 и $ E: $ E в формуле, чтобы использовать букву столбца, в который вы ввели список элементов. Нажмите OK, чтобы подтвердить эту новую конфигурацию проверки данных.

Вот как работает формула:

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

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

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

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

Удаление раскрывающегося списка в Excel

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

Измените раскрывающийся список Разрешить на Любое значение и выберите OK .

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

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

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

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

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

Идет загрузка.

Для любителей данных раскрывающиеся списки Excel — прекрасный подарок.

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

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

Ознакомьтесь с продуктами, упомянутыми в этой статье:

Microsoft Office (от 129 долларов США.99 в Best Buy)

Lenovo IdeaPad 130 (от 299,99 долларов в Best Buy)

MacBook Pro (от 1299,99 долларов в Best Buy)

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

1. Первый шаг — создать список со всеми необходимыми элементами в раскрывающемся списке.

Вы можете создать свой список на том же листе, где вы будете вводить данные из раскрывающегося списка.
Размещение записей списка на одной вкладке может вызвать путаницу.Лучше всего создать отдельный лист для раскрывающегося списка. Чтобы создать новую вкладку, щелкните значок «+» рядом с последней вкладкой в электронной таблице. Дважды щелкните вкладку, чтобы переименовать ее.
Вы также захотите убедиться, что ваши предметы находятся в таблице. Если это не так, вы можете преобразовать свой список в таблицу, удерживая «Ctrl» + «T» на ПК или «command» + «T» на клавиатуре Mac.

Вы можете использовать символ «+» внизу экрана, чтобы создать новый лист.Лаура МакКэми / Business Insider

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

3. Щелкните вкладку «Данные» в верхнем меню, откроется лента меню «Данные».

Выделив ячейки, выберите вкладку «Данные» в верхнем меню.Лаура МакКэми / Business Insider

4. Щелкните стрелку рядом с «Проверка данных».

Щелкните стрелку рядом с надписью «Проверка данных.» Лаура МакКэми / Business Insider

5. В раскрывающемся меню выберите «Проверка данных».

Выберите «Проверка данных… «из списка. Лаура МакКэми / Business Insider

6. На вкладке «Настройки» в верхнем меню в разделе «Разрешить» нажмите «Список».

В раскрывающемся меню выберите «Список».Лаура МакКэми / Business Insider

7. Щелкните в поле «Источник», и окно уменьшится, чтобы отобразить только это поле.

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

Диапазон ячеек, содержащий ваш список. Лаура МакКэми / Business Insider

9. Снова появится большее окно. Нажмите «ОК», чтобы настроить раскрывающийся список.

Нажмите «ОК», чтобы подтвердить диапазон для вашего списка. Лаура МакКэми / Business Insider

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

Если есть раскрывающийся список, рядом с ячейкой появится стрелка.Лаура МакКэми / Business Insider

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

При вводе данных используйте раскрывающийся список.Лаура МакКэми / Business Insider

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

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

Excel 2007-2013

Excel Online

Excel для Mac 2011

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

Процесс создания списка

На основе данных из перечня

С ручной записью данных

Создание раскрывающегося списка при помощи функции СМЕЩ

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

Выпадающий список с данными другого листа или файла Excel

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

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

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

Выпадающий список с автоматической подстановкой данных

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

Выделение всех ячеек, содержащих выпадающий список

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

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

Выпадающие списки

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

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

1. Создайте именованный диапазон

2. Добавьте проверку данных

3. Добавить именованный диапазон к проверке данных

4. Установите входное сообщение для проверки данных

Расширенные параметры выпадающего списка

Изменить или удалить именованный диапазон

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

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

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

Сделать стрелку выпадающего списка всегда видимой

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

Сделайте так, чтобы выпадающие списки работали для вас

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

Создание списка с применением инструментов разработчика

Связанный список

Заключение

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

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

Ввод данных

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

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

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

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

См. Также

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

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

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

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

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

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

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

Стол Magic

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

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

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

Метод 1. Вручную

Метод 2 — Ссылка на данные из других ячеек

Метод 3 — Формула СМЕЩЕНИЯ (динамические раскрывающиеся списки)

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

Скопировать правило проверки данных из другой ячейки

Добавить элемент в раскрывающийся список Excel

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

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

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

Узнать больше

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

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

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

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

Динамическое добавление элементов в список

Удаление раскрывающегося списка в Excel

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

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

Microsoft Office (от 129 долларов США.99 в Best Buy)

Lenovo IdeaPad 130 (от 299,99 долларов в Best Buy)

MacBook Pro (от 1299,99 долларов в Best Buy)

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