Создаем выпадающий список в Excel при помощи формул
Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в Excel 2007 и старше, но и в Excel 2003.
Как обычно, создадим на листе нашей рабочей книги диапазон допустимых значений для выпадающего списка. Расположим их в столбце с таким расчетом, чтобы в конец этого перечня можно было дописывать новые позиции. Пусть это будет столбец А.
Попробуем при помощи функции СМЕЩ (OFFSET) имитировать работу «умной» таблицы Excel. Это нам может быть полезно также в тех случаях, когда приходится использовать старые версии Excel до 2007 года.
Формула источника будет выглядеть следующим образом:
=СМЕЩ(A2,0,0,СЧЁТЗ(A2:A200),1)
Давайте разберемся, как она работает.
Функция СМЕЩ формирует ссылку на диапазон заданного размера.
А2 — это начальная ячейка, по отношению к которой будет выделен наш диапазон. Он всегда задается двумя координатами — левой верхней и правой нижней. Подразумевается, что начало диапазона (левый верхний его угол) будет находиться или в начальной ячейке, или чуть ниже и правее нее. На нашем рисунке это зелёная область. Параметр А показывает, на сколько ячеек вниз нам нужно опуститься. Параметр В — на сколько сдвинуться вправо. Если, как в нашем случае, они оба равны нулю, значит в нашей начальной ячейке и будет находиться левая верхняя точка.
И теперь нужно определить правую нижнюю точку. Теперь уже от начальной точки диапазона делаем С шагов вниз и D шагов вправо. В нашем примере D=1. То есть нам нужен только один столбец. Осталось только определиться, сколько шагов вниз нужно сделать, сколько строк взять в наш список. Нам нужна последняя заполненная строка в столбце А. Точнее, не сама строка, а ее порядковый номер.
Здесь нам поможет вторая функция — СЧЕТЗ (COUNTA). Она подсчитывает количество значений в заданном нами диапазоне A2:A200. Можно взять и более удаленные координаты, но думается, что 200 значений в списке будет вполне достаточно. Естественно, вместо ссылки можно использовать именованный диапазон.
В нашем примере мы имеем 8 значений. Значит, возьмем 8 строк, начиная с А2. То есть, A2:A9.
Если в ячейке А10 появится новое значение, тогда значений станет уже 9. Соответственно диапазон автоматически расширится до A2:A10. И так далее.
Но будьте внимательны! Если вы случайно пропустите строку и введете новое значение не в конец списка, а несколько ниже, то наша стройная система даст сбой. Диапазон значений для раскрывающегося списка увеличится, но захватит пустую ячейку. А наша последняя запись останется вне списка, расширение не дотянется до нее. Ведь в нашем случае мы имеем 9 значений и 10 строк. Естественно, последняя строка из списка выпадает.
Возможно, этот способ создания автоматически пополняемого выпадающего списка будет для вас удобен. Тем более, что на его основе можно делать и другие интересные вещи. Но об этом — в наших следующих материалах.
А вот еще о работе с выпадающими списками в Excel:
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,… 5 способов создания выпадающего списка в ячейке Excel — Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим… 0 0 голосРейтинг статьи
Как сделать выпадающий список в Excel
Выпадающий список – невероятно полезный инструмент, способный помочь сделать работу с информацией более комфортным. Он дает возможность вместить в ячейке сразу несколько значений, с которыми можно работать, как и с любыми другими. Чтобы выбрать нужное, достаточно нажать на значок стрелочки, после чего появляется перечень значений. После выбора определенного, ячейка автоматически заполняется им, и формулы пересчитываются, исходя из него.
Excel предусматривает множество различных методов генерации выпадающего меню, и кроме того, дает возможность гибко настраивать их. Давайте проанализируем эти методы более подробно.
Процесс создания списка
Чтобы сгенерировать всплывающее меню, следует кликнуть на пункты меню по пути «Данные» – «Проверка данных». Откроется диалоговое окошко, где надо найти вкладку «Параметры» и нажать по ней, если она до этого еще не была открыта. В нем есть множество настроек, но нам важен пункт «Тип данных». Из всех значений «Список» – это то, что надо.
1Количество методов, которыми осуществляется ввод информации во всплывающий перечень, достаточно большое.
- Самостоятельное указание элементов списка через точку с запятой в поле «Источник», расположенного на той же вкладке того же диалогового окна.
2
- Предварительное указание значений. В поле «Источник» содержится диапазон, где имеется необходимая информация.
3
- Указание именованного диапазона. Метод, повторяющий прошлый, но только необходимо предварительно назвать диапазон.
4
Любой из этих методов выдаст требуемый результат. Давайте разберем методы генерации раскрывающихся перечней в реальных ситуациях.
На основе данных из перечня
Допустим, у нас есть таблица, описывающая виды разных фруктов.
5Для создания перечня в раскрывающемся меню, основываясь на этом наборе информации, нужно совершить такие действия:
- Выбрать ячейку, отведенную под будущий перечень.
- На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных».
6
- Найти пункт «Тип данных» и переключить значение на «Список».
7
- В поле, обозначающем опцию «Источник», ввести нужный диапазон. Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась.
8
Кроме этого, предусмотрена функция генерации списков сразу больше, чем в одной ячейке. Чтобы этого добиться, следует выбрать их все, и совершить аналогичные описанным раньше действия. Снова необходимо удостовериться, что записаны абсолютные ссылки. Если в адресе нет значка доллара возле названия колонки и ряда, то нужно их добавить путем нажатия клавиши F4 до тех пор, пока возле названия столбца и ряда не будет стоять знак $.
С ручной записью данных
В приведенной раньше ситуации перечень записывался путем выделения необходимого диапазона. Это удобный метод, но иногда необходимо вручную записывать данные. Это даст возможность избежать дублирования информации в рабочей книге.
Допустим, перед нами стоит задача создать список, содержащий два возможных варианта выбора: да и нет. Чтобы реализовать поставленную задачу, необходимо:
- Нажать по ячейке, отведенной под перечень.
- Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
9
- Снова выбираем тип «Список».
10
- Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.
После нажатия «ОК» у нас появился следующий результат.
11Далее программой будет автоматически создано раскрывающееся меню в подходящей ячейке. Вся информация, которую пользователь указал качестве пунктов всплывающего перечня. Правила создания перечня в нескольких ячейках аналогичные предыдущим за тем лишь исключением, что следует указывать информацию вручную с использованием точки с запятой.
Создание раскрывающегося списка при помощи функции СМЕЩ
Кроме классического метода возможно применение функции СМЕЩ, чтобы генерировать выпадающие меню.
Откроем лист.
Чтобы применять функцию для выпадающего списка надо выполнить такое:
- Выбрать интересующую ячейку, где надо разместить будущий список.
- Открыть последовательно вкладку «Данные» и окно «Проверка данных».
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 не сможет применять в качестве источника информации файл, закрытый в данный момент.
Также следует отметить, что название файла сам по себе есть смысл указывать только если требуемый документ располагается в той же папке, что и та, где будет вставляться перечень. Если нет, то необходимо полностью указывать адрес этого документа.
Создание зависимых выпадающих списков
Зависимый список – это тот, на содержимое которого влияет выбор пользователя в другом перечне. Допустим, перед нами открыта таблица, содержащая три диапазона, каждому из которых присвоено имя.
24Нужно действовать по таким шагам для генерации перечней, на результат которых влияет опция, выбранная в другом списке.
- Создать 1-й перечень с именами диапазонов.
25
- В месте ввода источника один за одним выделяются требуемые показатели.
26
- Создать 2-й перечень, зависящий от типа растений, который предпочел человек. Как вариант, если в первом указать деревья, то информацией во втором списке станет «дуб, граб, каштан» и дальше. Необходимо записать в месте ввода источника данных формулу =ДВССЫЛ(E3). E3 – ячейка содержащая название диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с наименованием списка 1.
Теперь все готово.
27Как выбрать несколько значений из выпадающего списка?
Иногда нет возможности отдать предпочтение только одному значению, поэтому надо выбрать больше одного. Тогда надо добавить в код страницы макрос. С использованием комбинации клавиш 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». Там есть «Поле со списком». Будет предложено нарисовать этот список, после чего он добавится в документ.
28Далее он настраивается через свойства, где в опции ListFillRange прописывается диапазон. Ячейка, где отобразиться определенное пользователем значение, настраивается с помощью опции LinkedCell. Далее нужно просто записывать первые символы, как программа автоматически подскажет возможные значения.
Выпадающий список с автоматической подстановкой данных
Также предусмотрена функция, что данные подставляются автоматически после их добавления к диапазону. Сделать это проще простого:
- Создать набор ячеек для будущего перечня. В случае с нами это набор цветов. Выделяем его.
14
- Далее его необходимо отформатировать, как таблицу. Нужно нажать одноименную кнопку и осуществить выбор стиля таблицы.
15
16
Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».
17Выделяем получившуюся таблицу и даем ей имя через поле ввода, находящееся сверху столбца А.
18Все, таблица есть, и она может использоваться в качестве основы для выпадающего списка, для чего надо:
- Выбрать ячейку, где перечень располагается.
- Открыть диалог «Проверка данных».
19
- Тип данных выставляем «Список», а как значения даем имя таблицы через знак =.
20
21
Все, ячейка готова, и в ней показываются названия цветов, как нам изначально и было нужно. Теперь добавлять новые позиции можно просто записывая их в ячейку, располагающуюся немного ниже непосредственно за последней.
22В этом и заключается преимущество таблицы, что диапазон автоматически увеличивается при добавлении новых данных. Соответственно, это самый удобный способ добавления списка.
23Как скопировать выпадающий список?
Для копирования достаточно использовать комбинацию клавиш Ctrl + C и Ctrl + V. Так выпадающий список будет скопирован вместе с форматированием. Чтобы убрать форматирование, нужно воспользоваться специальной вставкой (в контекстном меню такая опция появляется после копирования списка), где выставляется опция «условия на значения».
Выделение всех ячеек, содержащих выпадающий список
Чтобы выполнить эту задачу, необходимо воспользоваться функцией «Выделить группу ячеек» в группе «Найти и выделить».
29После этого откроется диалоговое окно, где следует в меню «Проверка данных» выбрать пункты «Всех» и «Этих же». Первый пункт выделяет все списки, а второй – только похожие на определенные.
Оцените качество статьи. Нам важно ваше мнение:
Как сделать выпадающий список в Excel
При работе в программе Microsoft Excel в таблицах с повторяющимися данными, очень удобно использовать выпадающий список. С его помощью можно просто выбирать нужные параметры из сформированного меню. Давайте выясним, как сделать раскрывающийся список различными способами.
Создание дополнительного списка
Самым удобным, и одновременно наиболее функциональным способом создания выпадающего списка, является метод, основанный на построении отдельного списка данных.
Прежде всего, делаем таблицу-заготовку, где собираемся использовать выпадающее меню, а также делаем отдельным списком данные, которые в будущем включим в это меню. Эти данные можно размещать как на этом же листе документа, так и на другом, если вы не хотите, чтобы обе таблице располагались визуально вместе.
Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».
Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.
Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».
Выпадающий список готов. Теперь, при нажатии на кнопку у каждой ячейки указанного диапазона будет появляться список параметров, среди которых можно выбрать любой для добавления в ячейку.
Создание выпадающего списка с помощью инструментов разработчика
Второй способ предполагает создание выпадающего списка с помощью инструментов разработчика, а именно с использованием ActiveX. По умолчанию, функции инструментов разработчика отсутствуют, поэтому нам, прежде всего, нужно будет их включить. Для этого, переходим во вкладку «Файл» программы Excel, а затем кликаем по надписи «Параметры».
В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».
После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».
Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.
Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».
Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.
Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».
Выпадающий список в Microsoft Excel готов.
Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.
Связанные списки
Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного – литры и миллилитры.
Прежде всего, подготовим таблицу, где будут располагаться выпадающие списки, и отдельно сделаем списки с наименованием продуктов и мер измерения.
Присваиваем каждому из списков именованный диапазон, как это мы уже делали ранее с обычными выпадающими списками.
В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.
Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).
Как видим, список создан.
Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.
Всё, таблица создана.
Мы разобрались, как сделать выпадающий список в Экселе. В программе можно создавать, как простые выпадающие списки, так и зависимые. При этом, можно использовать различные методы создания. Выбор зависит от конкретного предназначения списка, целей его создания, области применения, и т.д.
Мы рады, что смогли помочь Вам в решении проблемы.Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТВыпадающий список в excel 2010
При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. Excel 2010 позволяет это делать несколькими способами. Рассмотрим их.
Способ 1. Создаем раскрывающийся список в Excel 2010, используя инструмент «Проверка данных»
Этот способ считается стандартным, поскольку он прост и удобен.
1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке.
2. Задайте имя диапазону ячеек. Для этого:
- кликните верхнюю ячейку списка и, зажав левую кнопку мыши, тяните курсор вниз, пока весь список не будет выделен.
- установите курсор в поле «Имя», слева от строки формул;
- введите имя списка и нажмите Enter.
Обратите внимание, имя списка всегда должно начинаться с буквы и не содержать пробелов.
3. Выделите ячейку в таблице, где должен будет размещаться выпадающий список.
4. Откройте вкладку «Данные» и нажмите кнопку «Проверка данных». В открывшемся окне перейдите на вкладку «Параметры». В выпадающем списке «Тип данных» выберите пункт «Список».
5. В строке «Источник» нужно указать адрес, откуда будет взяты элементы создаваемого списка. Адресом будет имя, которое вы присвоили диапазону ячеек. Задать адрес можно несколькими способами.
- Вписать вручную, поставив перед ним знак «равно», например, «=месяца». Регистр букв не важен.
- Щелкнув мышью в строке «Источник» (для активации), выделить курсором все элементы списка в таблице.
6. Если нужно создать сообщение для ввода, откройте одноименную вкладку. Напишите текст, который будет появляться рядом с ячейкой с раскрывающимся списком при ее выделении. На соседней вкладке — «Сообщение об ошибке», таким же образом можно написать текст, уведомляющий об ошибках.
7. Подтвердите ввод нажатием «ОК», и раскрывающийся список готов. Для его открытия нажмите на кнопку со стрелкой вниз, которая появится рядом с ячейкой, содержащей список.
Способ 2. Быстрое создание выпадающего списка
Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка.
1. Перечислите в столбик все элементы будущего выпадающего списка.
2. Выделите ячейку, находящуюся под последним элементом и нажмите сочетание клавиш «Alt» + «стрелка вниз» — список будет создан. Этот метод позволит задать ячейке значение одного из элементов.
Способ 3. Создание выпадающего списка как элемента управления
Для применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана.
1. Перечислите элементы будущего списка в столбик.
2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».
3. Нарисуйте на листе курсором ваш будущий раскрывающийся список. Кликните по нему правой кнопкой мыши и выберите из меню пункт «Формат объекта».
4. Значением поля «Формировать список по диапазону» должен быть список элементов — выделите его курсором, и поле будет заполнено автоматически. В поле «Связь с ячейкой» указывается адрес ячейки, где будет показан порядковый номер выделенного элемента. Выберите ячейку и кликните по ней. Поле «Количество строк списка» позволяет настроить, сколько элементов будет отображено при раскрытии списка.
5. Подтвердите ввод и нажмите «ОК». Список будет создан.
Способ 4. Создание выпадающего списка как элемента ActiveX
Самый сложный метод, но обладающий максимально гибкими настройками.
1. Создайте список вышеописанным способом.
2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы ActiveX» — «Поле со списком».
3. Нарисуйте на листе будущий раскрывающийся список.
4. Опция, которая позволяет редактировать выпадающий список называется «Режим конструктора». Если данный режим активен — будет выделена одноименная кнопка в разделе «Элементы управления», рядом с кнопкой «Вставить». Если кнопка не выделена — режим редактирования отключен.
5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.
6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.
- ListRows — аналог значения «Количество строк в списке», покажет, сколько строк будет отображаться.
- Font — настройки шрифта. Позволяет выбрать шрифт и его начертание.
- ForeColor — выбор из таблицы цвета шрифта.
- BackColor — цвет заднего фона.
- ListFillRange — расположение списка элементов в формате: лист(«!» — разделитель) и диапазон ячеек. Например: Лист2!D2:D6. Прописывается вручную.
- LinkedCell — связь с ячейкой. Вручную указывается адрес ячейки, где будет показываться порядковый номер выделенного элемента списка.
7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает.
как создать раскрывающийся список простого и связанного типа
Если при работе с программой Microsoft Excel необходимо упростить процедуру ввода информации или же ограничить записи определенными элементами, то можно создать выпадающий список. Выпадающий список в Excel создается из любых заполненных ячеек. При создании такого списка в ячейках будет отображаться стрелочка. Чтобы ввести что-то в это поле, надо щелкнуть на стрелочку, а затем указать необходимую информацию.
Видео по связанным выпадающим спискам Excel
Как сделать выпадающий список?
Пример готового выпадающего списка в Excel
При работе с базами данных или большими таблицами в Excel очень удобно использовать выпадающий список.Ведь в этом случае можно выбирать только те значения, которые были отобраны ранее. Кроме того, раскрывающийся список позволяет избежать таких ситуаций, когда ошибочно вводится неверная информация, что впоследствии может привести к нежелательным результатам. Именно для этого разработчики Microsoft Excel добавили в программу инструменты для проверки данных, в число которых входит и создание раскрывающегося списка.
Итак, самый главный вопрос заключается в том, как сделать список. Допустим, у пользователя есть база данных, которую он постоянно использует в работе. Чтобы не заполнять каждый раз, к примеру, реквизиты одного и того же товара, можно просто создать раскрывающийся список. Им может быть прайс-лист, размещенный на другом листе Excel, или несколько колонок, которые размещены рядом с основной таблицей.
Чтобы создать раскрывающийся список, надо выполнить следующие действия:
- Выделить нужные для работы ячейки.
- В поле, где указывается имя ячейки (A1 или B2), необходимо написать другое название (например, «товары»).
- Кликнуть в панели меню пункты «Данные — Проверка».
- В появившемся окне надо заполнить информацию на вкладке «Параметры»: в пункте «Тип данных» указать «Список», а в пункте «Источник» — название ячеек (=товары).
- Нажать кнопку «ОК».
Задаем дополнительные параметры выпадающих списков
По желанию можно еще заполнить 2 оставшиеся вкладки — «Сообщение для ввода» и «Сообщение об ошибке». На первой вкладке можно написать подсказку для других пользователей о том, что надо делать. То есть в заголовок можно написать «Подсказка», а в поле «Сообщение» — небольшую инструкцию (например, «выберите нужный товар»). Теперь, если щелкнуть на определенное поле, то можно увидеть всплывающее сообщение, которое будет выглядеть примерно так: «Подсказка: выберите нужный товар».
Во второй вкладке аналогичным образом можно создать предупреждение, которое будет показано при неверном вводе. Кроме того, есть возможность выбрать вид ошибки:
- «останов» — программа сообщит об ошибке и не разрешит ввести ошибочные данные;
- «предупреждение» — программа сообщит об ошибке, но пользователь сможет сам решить, сохранять или нет эту информацию;
- «сообщение» — стандартное сообщение.
Даже если конкретное предупреждение об ошибке не будет создано, программа все равно выдаст свой вариант на этот случай.
Вот и все — первый выпадающий список Excel был успешно создан. Теперь при выборе определенного поля будет появляться подсказка, а также перечень товаров. Если же пользователь введет несоответствующие данные (например, число), то появится сообщение об ошибке.
Для удобства выпадающий список можно разместить на одном листе, а саму таблицу — на другом. Например, на первом листе можно создать прайс-лист, а на другом — выбирать нужный вид товара. Но для создания такого перечня нужно будет задать ему имя. Для этого надо выделить нужные поля, щелкнуть по ним правой кнопкой мыши и выбрать пункт «Имя диапазона». В появившемся окне в пункте «Имя» нужно указать название для этих ячеек без пробелов (например, «Перечень_изделий»). После этого надо скопировать диапазон ячеек, а затем перейти на другой лист и кликнуть по пунктам «Данные — Проверка». В новом поле нужно указать «Тип данных» (как обычно), а в поле «Источник» — вставить ранее скопированную информацию. Теперь вы знаете, как сделать раскрывающийся список, даже если перечень изделий находится на другом листе.
Создание одного списка на основании другого
Готовый пример связанного выпадающего списка Excel
Допустим, есть база данных авто, в которых указана марка машин и запчасти к ним. И нужно, чтобы выбор запчастей систематизировался по названию автомобиля. Сделать это можно с помощью функции ДВССЫЛ.
Сначала необходимо прописать марки машин (например, Audi, Renault, Nissan) и присвоить этим ячейкам имя («авто»). Чуть ниже нужно написать название запчастей и тоже присвоить имя этим полям. Обязательно нужно, чтобы поля с запчастями Nissan назывались Nissan и т.д.
После этого можно перейти к созданию одного выпадающего перечня на основе другого. Таблица должна состоять из 2 колонок: марка авто и запчасти. Первый столбец делается по знакомому уже принципу: «Данные — Проверка», в качестве источника указывается перечень автомобилей. Теперь можно перейти ко второму столбцу — «Запчасти». Здесь тоже надо щелкнуть в панели меню пункты «Данные — Проверка», но в поле «Источник» нужно написать следующее: =ДВССЫЛ(A5). А5 — это название поля, в котором указан автомобиль из колонки «Марка авто». После того как в первом столбце будет указана марка, во втором столбце можно будет выбрать только те детали, которые подходят к конкретной машине.
В общем, теперь должно быть более-менее понятно, как можно упростить процедуру ввода информации или ограничить записи определенными элементами. С помощью этих знаний можно будет сделать работу пользователей с большими таблицами или базами данных более комфортной и продуктивной.
Выпадающий список в Excel
В программе Microsoft Excel есть возможность создать выпадающий список – то есть сделать так, чтобы одна ячейка содержала несколько значений, между которыми может переключаться пользователь. Это очень удобный инструмент для работы с таблицами, в частности, если речь идёт о проверке введённых данных. Поэтому если вы не знаете, как сделать в экселе выпадающий список, то вам помогут приведённые в статье инструкции.
Выпадающий список в Excel
Создание выпадающего списка
Для начала рассмотрим самый простой способ, который поможет вам сделать раскрывающийся список. Чтобы осуществить его, делайте всё как в инструкции ниже:
Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).
Переходим во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выбираем инструмент проверки данных
Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.
В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»
Шаг 3. Теперь в поле ввода данных «Источник» вы можете указывать значения, которые будут составлять раскрывающийся список. Сделать это можно следующими способами:
- вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;
В поле «Источник» вводим значения нужных ячеек
- с указанием диапазона. Чтобы сделать это, вбейте в поле ввода адрес первой и последней ячейки через двоеточие. Например, если вы хотите сложить в список значения всех ячеек от «A1» до «A7», то вводите «=$A$1:$A$7».
Вбиваем в поле ввода адрес первой и последней ячейки через двоеточие, нажимаем «ОК»
На заметку! Чтобы не вводить диапазон ячеек вручную, достаточно левой кнопкой мышки выделить первую ячейку со значением, поставить двоеточие и выделить мышью последнюю ячейку со значением.
После того, как вы воспользуетесь любым из способов и укажете имя диапазона значений, вы получите такой результат, как на картинке ниже.
Результат сделанного выпадающего списка
На заметку! Есть ещё один способ указать значение в источнике – написать в поле ввода имя диапазона. Этот способ самый быстрый, но прежде чем прибегать к нему, нужно создать именованный диапазон. О том, как это сделать, мы поговорим позже.
Форма для быстрого создания выпадающего списка
Видео — Создание выпадающих списков в Excel
Раскрывающийся список с подстановкой данных
Если ваша таблица предполагает постоянные изменения, и вы не хотите, чтобы это повлияло на содержимое вашего выпадающего списка, то вам поможет следующий способ. Воспользуйтесь им, и тогда редактирование данных ячеек будет автоматически отображено в выпадающем списке. Для этого проделайте следующее:
- Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».
Выделяем левой кнопкой мышки диапазон для списка, открываем вкладку «Главная» и выбираем меню «Форматировать как таблицу»
- После этого откроется меню выбора стилей. Стиль никакой роли не играет, кроме визуальной, поэтому выбирайте любой по вашему вкусу.
Выбираем любой понравившийся стиль
- Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона. Здесь важно установить галочку возле «Таблица с заголовками», так как наличие заголовка в данном случае играет ключевую роль.
Устанавливаем галочку возле «Таблица с заголовками», нажимаем «ОК»
- После проделанных процедур вы получите следующий вид диапазона.
Результат отформатированной таблицы
- Теперь выделите левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и перейдите во вкладку «Данные» (в предыдущем способе сказано, как это сделать).
Выделяем левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и переходим во вкладку «Данные»
- В поле ввода «Источник» вам нужно вписать функцию с синтаксисом «=ДВССЫЛ(“Имя таблицы[Заголовок]”)». На скриншоте указан более конкретный пример.
В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы[Заголовок]”)», подставляя свои данные, как на примере
Итак, список готов. Выглядеть он будет вот так.
Готовый список
На заметку! У этого способа есть принципиальное отличие от первого – в качестве списка используется готовая таблица, а не диапазон значений. Это значит, что любые изменения в таблице будут отображаться и в выпадающем списке.
Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.
Добавляем в таблицу строку с новым значением, она автоматически появится в выпадающем списке
Если удалить ячейку (в данном случае мы удалили «берёза»), это тоже отобразится на содержании раскрывающегося списка.
Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка
На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.
Зависимые раскрывающиеся списки
Чтобы создать зависимые выпадающие списки, следуйте дальнейшей пошаговой инструкции:
- Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».
Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»
Нажимаем «Создать»
- Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.
Пишем имя «Деревья», нажимаем «ОК»
- По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать. В данном примере это ещё два диапазона: «Кустарники» и «Травы».
Создаем таким же способом остальные диапазоны
- Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте.
В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»
Выпадающий список с названием диапазона ячеек
- Теперь вам нужно создать дополнительный раскрывающийся список по той же схеме. В этом списке будут отражаться те слова, которые соответствуют заголовку. Например, если вы выбрали «Дерево», то это будут «береза», «липа», «клен» и так далее. Чтобы осуществить это, повторите вышеуказанные шаги, но в поле ввода «Источник» введите функцию «=ДВССЫЛ(E1)». В данном случае «E1» – это адрес ячейки с именем первого диапазона. По такому же способу вы сможете создавать столько взаимосвязанных списков, сколько вам потребуется.
В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»
Результат выпадающего связанного списка
Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.
Видео — Связанные выпадающие списки: легко и быстро
Видео: создание раскрывающихся списков и управление ими
Ввод данных происходит быстрее и точнее, если вы используете раскрывающийся список для ограничения записей, которые люди могут делать в ячейке. Когда кто-то выбирает ячейку, в раскрывающемся списке появляется стрелка вниз, и они могут щелкнуть по ней и сделать выбор.
Создать раскрывающийся список
Вы можете сделать рабочий лист более эффективным с помощью раскрывающихся списков. Кто-то, использующий ваш рабочий лист, щелкает стрелку, а затем щелкает запись в списке.
Выберите ячейки, в которых вы хотите разместить списки.
На ленте щелкните ДАННЫЕ > Проверка данных .
В диалоговом окне установите Разрешить на Список .
Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), которые вы хотите в раскрывающемся списке, и щелкните OK .
Хотите больше?
Создать раскрывающийся список
Добавить или удалить элементы из раскрывающегося списка
Удалить раскрывающийся список
Блокируйте клетки, чтобы защитить их
Ввод данных происходит быстрее и точнее, если вы используете раскрывающийся список для ограничения записей, которые люди могут делать в ячейке.
Когда вы выбираете ячейку, в раскрывающемся списке появляется стрелка вниз, щелкните ее и сделайте выбор.
Вот как создавать раскрывающиеся списки: Выберите ячейки, которые вы хотите содержать списки.
На ленте щелкните вкладку ДАННЫЕ и щелкните Проверка данных .
В диалоговом окне установите Разрешить на Список .
Щелкните в Source .
В этом примере мы используем список с разделителями-запятыми.
Текст или числа, которые мы вводим в поле Source , разделяются запятыми.
И нажмите ОК . Теперь у ячеек есть раскрывающийся список.
Далее, Настройки раскрывающегося списка .
Уловки для создания раскрывающихся списков, позволяющих выбрать несколько элементов
Наш приглашенный блоггер Дебра Далглиш — консультант, специализирующийся на программировании и разработке Microsoft Office.Каждую неделю она делится своими знаниями об Excel в блоге Contextures и ежегодно получает награду Excel MVP с 2001 года.
На Фабрике мистической магии * одно из преимуществ — то, что офисные сотрудники могут исчезать в полдень по большинству пятниц. Однако офис остается открытым, поэтому по крайней мере два человека дежурят, чтобы отвечать на телефонные звонки и приветствовать посетителей.
Microsoft Excel
Превратите данные в идеи.
Получить ExcelПеред волшебством
Чтобы отслеживать, кто будет оставаться в офисе каждую неделю, Ди, менеджер офиса, создал файл Excel.Этот файл содержит список имен сотрудников и список недель. Ди создал раскрывающийся список имен сотрудников на каждую неделю.
Выпадающий список хорошо подходит для выбора одного имени, но если Ди выбирает второе имя в ячейке, оно перезаписывает первое имя.
Добавьте волшебство
Чтобы позволить Ди выбрать несколько имен в каждой ячейке, мы добавим немного магии на ее рабочий лист с помощью нескольких строк кода VBA. Код сохраняет старое имя в ячейке и добавляет запятую, за которой следует новое имя.
Давайте взглянем на код и посмотрим, как вы можете использовать этот трюк в своих таблицах.
За занавеской
Чтобы увидеть код для рабочего листа, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код . Это откроет Visual Basic Explorer (VBE).
В списке проектов слева указана рабочая книга FridayStaffName, а лист Friday_Staff выделен. Справа мы видим код события Worksheet_Change для этого рабочего листа.Этот код запускается автоматически, если Ди вносит изменения в лист.
Скопируйте код в свой файл
Даже если вы ничего не знаете о программировании, вы можете скопировать этот код и использовать его в своей книге. Есть одна строка кода, которую вы можете изменить, если раскрывающийся список отсутствует в столбце B.
В коде Target относится к ячейке, которую вы изменили. На снимке экрана ниже код проверяет, в каком столбце находится эта целевая ячейка.Затем, если это был столбец 2 (B), остальная часть кода будет выполнена.
Вместо использования столбца 2 вы можете изменить его число или использовать несколько столбцов, например: Case 2, 5, 6
Пусть волшебство работает на вас
С помощью этого небольшого волшебства, добавленного к ее рабочему листу, Ди может быстро и легко выбрать офисный персонал для дневных смен в пятницу. На телефоны ответят, пока все отдыхают.
Вы можете загрузить образец файла, чтобы увидеть, как он работает, а также добавить этот удивительный трюк в свои рабочие книги: Загрузите файл образца имен сотрудников Friday
* Mystical Magic Factory — вымышленная компания, но эта тема блога основана на реальной проблеме клиента Contextures.
Дополнительная информация в раскрывающихся списках Excel:
Как создать раскрывающийся список ячеек в Excel 2010
Как создать раскрывающийся список в Excel
Выпадающий список — это инструмент, который может помочь вам заставить пользователей выбрать конкретное значение из предопределенного набора значений. Всегда полезно иметь раскрывающиеся списки для приема пользовательского ввода.
Этот подход лучше, потому что он проверяет каждый ввод от пользователя на соответствие предопределенному набору значений, и, следовательно, пользователи могут вводить только допустимые и исключенные значения.
Excel также имеет функцию, которая позволяет создавать раскрывающиеся списки. И сегодня я расскажу вам, как составлять раскрывающийся список в Excel .
Как создать раскрывающийся список в Excel:
- Прежде всего откройте свой лист Excel и выберите ячейку, в которой вы хотите создать раскрывающийся список.
- Затем перейдите на вкладку «Данные» на ленте Excel и нажмите кнопку «Проверка данных».
- Теперь откроется окно «Проверка данных».В раскрывающемся списке «Разрешить» выберите параметр «Список». И в текстовом поле «Источник» введите значения, которые вы хотите иметь в раскрывающемся списке, эти значения должны быть разделены запятой (,). Также убедитесь, что установлен флажок «Раскрывающийся список в ячейке».
- После этого нажмите кнопку «ОК», и выпадающий список готов к использованию.
Рекомендуемая литература: Как добавить флажок в Excel
Несколько важных вещей о выпадающих списках Excel:
1.Вы не можете удалить раскрывающийся список, нажав клавишу удаления. Его можно удалить только после выбора раскрывающегося списка, выбора опции «Проверка данных» и нажатия кнопки «Очистить все» в окне «Проверка данных».
2. Ширина раскрывающегося списка не устанавливается автоматически. Вы должны вручную настроить его в соответствии с самым длинным значением в раскрывающемся списке.
3. Выпадающие списки Excel могут содержать максимум 32 767 значений.
Лучший тип источника данных для раскрывающихся списков:
Как вы уже видели, вы можете ввести значения раскрывающегося списка прямо в текстовое поле «Источник».Это нормально, если вы планируете создавать только небольшие списки, но у вас есть большой список значений, которые вы хотите заполнить в раскрывающемся списке, тогда есть лучший метод.
Вы можете ввести значения, которые вы хотите видеть в раскрывающемся списке, в виде таблицы, а затем использовать диапазон этой таблицы в столбце «Источник». Ниже приведена пошаговая процедура для этого:
- Сначала с открытым листом Excel введите значения, которые вы планируете заполнить, в раскрывающемся списке в таблице.
- Затем выберите ячейку, для которой вы планируете создать раскрывающийся список.
- Теперь перейдите к Data> Data Validation.
- В окне «Проверка данных» выберите «Список» в раскрывающемся списке «Разрешить», установите флажок «Выпадающий список в ячейке» и в текстовом поле «Источник» введите диапазон таблицы (который мы создали на шаге 1). Нажмите «ОК», и выпадающий список создан.
Косметический совет: Если вы используете раскрывающийся список с таблицей значений в качестве источника, попробуйте разместить эту таблицу на отдельном листе, который вы можете скрыть позже.Это делает ваши выпадающие списки более естественными, и никто не сможет вмешиваться в ваши выпадающие значения.
Бонусный совет: Вместо прямого ввода исходного диапазона в текстовое поле «source» вы также можете назвать свой диапазон, а затем использовать то же самое в текстовом поле «source».
Итак, это все о создании раскрывающегося списка в Excel.
Вам также может понравиться
Как создать раскрывающийся список в Microsoft Excel
Выпадающие списки ограничивают количество записей для поля ввода.Они помогают предотвратить опечатки и орфографические ошибки. А Microsoft Excel поддерживает удивительно универсальные функции раскрывающегося списка. Таким образом, если вы хотите использовать Excel для форм или сбора данных, упростите для пользователей выбор элементов с помощью раскрывающихся списков.
Используйте раскрывающийся список в ячейке, если для этой ячейки доступны определенные параметры.Например, вы можете создавать раскрывающиеся списки, содержащие такие параметры, как Мужской и Женский , Да и Нет или любой другой настраиваемый список параметров.
Мы покажем вам, как добавлять раскрывающиеся списки в электронные таблицы Excel.
Как создать раскрывающийся список в Excel
Создать раскрывающийся список в Excel просто, но процесс неочевиден.Вот краткое описание шагов по созданию настраиваемого раскрывающегося списка в Excel:
- Создайте именованный диапазон: Этот шаг необходим для создания списка, который мы будем использовать в раскрывающемся списке.
- Добавить подтверждение данных: Это не столь очевидный шаг, на котором вы подготовите ячейку для отображения раскрывающегося списка.
- Добавьте именованный диапазон к проверке данных: Наконец, вы соберете все вместе и определите источник раскрывающегося списка (т.е. ваш ранее именованный диапазон).
- Установить входное сообщение для проверки данных: Этот шаг не является обязательным.Он позволяет добавить всплывающее сообщение, чтобы направлять пользователей электронных таблиц.
Excel — это непросто, но мощно.Это означает, что у вас есть масса вариантов, как вы вскоре увидите. Теперь давайте рассмотрим шаги по созданию раскрывающегося списка Excel более подробно.
1.Создать именованный диапазон
Один из способов добавить список элементов в раскрывающийся список с помощью проверки данных — это добавить свой список на рабочий лист и присвоить имя диапазону ячеек, содержащему список, .Вы можете добавить список либо на тот же рабочий лист, на который вы собираетесь добавить раскрывающийся список, либо на другой рабочий лист. Использование именованного диапазона ячеек в раскрывающемся списке упрощает обслуживание.
Мы собираемся создать раскрывающийся список, содержащий несколько различных типов еды, на Sheet2 в качестве примера.Введите каждый элемент в отдельную ячейку в одном столбце или одной строке. Выберите элементы, введите имя диапазона выбранных ячеек в поле Имя и нажмите Введите .
2.Добавить подтверждение данных
Перейдите на рабочий лист, на который вы хотите добавить раскрывающийся список.Щелкните вкладку Data , а затем щелкните Data Validation в разделе Data Tools .
Убедитесь, что вкладка Settings активна в диалоговом окне Data Validation .
3. Добавьте именованный диапазон в проверку данных
Затем выберите Список из раскрывающегося списка Разрешить .Мы собираемся использовать имя из именованного диапазона ячеек, который мы определили, для заполнения раскрывающегося списка. Введите следующий текст в поле Источник .
= Еда
Замените « Food » любым именем, которое вы дали диапазону ячеек.Нажмите ОК .
Флажок Игнорировать пустое поле установлен по умолчанию.Это позволяет выбрать ячейку, а затем отменить выбор ячейки, не выбирая элемент. Если вы хотите, чтобы параметр был выбран из раскрывающегося списка, снимите флажок Игнорировать пустое поле .
Советы Тома для Excel: используйте проверку данных вместо комментария для выбранной ячейки: Alt + D + L> вкладка «Входное сообщение»> введите сообщение> Ck Показать входное сообщение…
— Том Уртис (@TomUrtis) 22 января 2017 г.
4.Установить входное сообщение для проверки данных
Если вы хотите, чтобы при выборе ячейки, содержащей раскрывающийся список, отображалось всплывающее сообщение, щелкните вкладку Входное сообщение .Установите флажок Показать входное сообщение при выборе ячейки и заполните поля Название и Входное сообщение . Вы также можете использовать вкладку Error Alert , чтобы добавить сообщение, которое отображается, когда в раскрывающемся списке вводится недопустимый ввод (например, если кто-то вводит в ячейку, а не выбирает параметр). Установите флажок Показать предупреждение об ошибке после ввода неверных данных в поле . Выберите стиль и заполните поля Название и Сообщение об ошибке .
Нажмите ОК .
Когда вы выбираете ячейку, содержащую раскрывающийся список, стрелка вниз для выбора параметра отображается справа от ячейки.Кнопка со стрелкой вниз отображается только тогда, когда ячейка выбрана. Позже в этой статье мы покажем вам, как постоянно отображать стрелку вниз рядом с раскрывающимся списком.
Если раскрывающийся список содержит более восьми элементов, вы увидите полосу прокрутки в раскрывающемся списке, когда нажмете стрелку.
Расширенные параметры раскрывающегося списка
Теперь, когда у вас есть базовый раскрывающийся список, давайте погрузимся в расширенные настройки Excel для редактирования и настройки вашего списка.
Редактировать или удалить именованный диапазон
Если вам нужно отредактировать или удалить именованный диапазон, вы должны использовать Name Manager .Щелкните вкладку Формулы , а затем щелкните Диспетчер имен в разделе Определенные имена .
Чтобы изменить диапазон ячеек для имени в диалоговом окне Name Manager , выберите Name в списке, а затем нажмите кнопку диапазона ячеек в нижней части диалогового окна.Затем выберите диапазон ячеек и снова нажмите кнопку диапазона ячеек в компактной версии диалогового окна Name Manager , так же, как мы описали в разделе выше.
Затем щелкните зеленую галочку, чтобы сохранить новый диапазон ячеек для выбранного Имя .
Вы также можете изменить имя , выбрав его в списке, щелкнув Изменить , отредактировав имя в диалоговом окне Изменить имя и нажав ОК .Вы также можете изменить диапазон ячеек в диалоговом окне Изменить имя .
Чтобы удалить имя, выберите Имя в списке и нажмите Удалить .
Создание зависимого раскрывающегося списка
Зависимый раскрывающийся список — это тот, в котором параметры меняются в зависимости от выбора в другом раскрывающемся списке.
Например, когда мы выбираем Пицца в раскрывающемся списке, который мы создали в разделе Как создать раскрывающийся список выше, второй зависимый раскрывающийся список содержит различные виды пиццы.Если вы выберете Chinese , варианты во втором зависимом раскрывающемся списке будут содержать разные типы китайских блюд.
Только что научился создавать зависимый раскрывающийся список в Excel, например.g., ячейка1-состояние, ячейка2-города в этом состоянии. РЕБЯТА! Это меняет жизнь.
— Жизнь в Deloitte (@lifeatdeloitte) 4 февраля 2014 г.
Прежде чем продолжить, вернитесь в раздел Как создать раскрывающийся список и создайте основной раскрывающийся список Любимая еда , если вы еще этого не сделали.
Теперь мы создадим еще три списка и назовем их.Введите список для каждого из вариантов в основном раскрывающемся списке. Выберите один из других списков, введите имя для этого списка в поле Имя и нажмите Введите . Повторите для каждого списка.
Имена других списков должны соответствовать параметрам в основном раскрывающемся списке.Например, один из трех других наших списков содержит типы файлов cookie и называется Cookies , как показано ниже. Два других списка в красном поле на изображении ниже названы Pizza и Chinese .
Перед созданием зависимого раскрывающегося списка необходимо выбрать элемент в основном раскрывающемся списке.Неважно, какой вариант вы выберете. Затем выберите ячейку, в которую вы хотите добавить зависимый раскрывающийся список.
Щелкните вкладку Data , а затем щелкните Data Validation в разделе Data Tools .Выберите Список в раскрывающемся списке Разрешить .
Введите следующий текст в поле Источник .Замените « $ B $ 2 » ссылкой на ячейку, содержащую ваш основной раскрывающийся список. Сохраните знаки доллара в ссылке на ячейку. Это указывает на абсолютную ссылку на ячейку, которая не изменится, даже если вы скопируете или переместите формулу, которая ссылается на эту ячейку.
= КОСВЕННО (2 млрд долларов)
Функция ДВССЫЛ возвращает ссылку, указанную в текстовой строке, в данном случае текст из параметра, выбранного в основном раскрывающемся списке в ячейке B2.Например, если вы выберете китайский из основного раскрывающегося списка, = КОСВЕННО ($ B $ 2) вернет ссылку китайский . В результате второй раскрывающийся список содержит китайских элементов.
Нажмите ОК .
Список в раскрывающемся списке Favorite Dish в приведенном ниже примере изменяется в зависимости от того, что выбрано в раскрывающемся списке Favorite Food .
Скопировать и вставить раскрывающийся список
Если вам нужно продублировать раскрывающийся список с проверкой данных в другие ячейки, скопируйте и вставьте ячейку, используя Ctrl + C и Ctrl + V .Это копирует раскрывающийся список и форматирование.
Если вы просто хотите скопировать раскрывающийся список с проверкой данных, но НЕ с форматированием, выберите ячейку и скопируйте ее обычно, используя Ctrl + C .Затем перейдите на вкладку Home и щелкните Paste в разделе Clipboard . Выберите Специальная вставка .
В диалоговом окне Специальная вставка выберите Проверка в разделе Вставить .Затем нажмите ОК .
Это скопирует только раскрывающийся список, но не форматирование исходной ячейки.
Примечание: Будьте осторожны при работе с раскрывающимися списками в Excel.Когда вы копируете ячейку, которая не содержит раскрывающегося списка, поверх ячейки, содержащей раскрывающийся список, раскрывающийся список теряется. Excel не предупреждает вас и не запрашивает подтверждение действия. Однако вы можете отменить действие, используя Ctrl + Z .
Выбрать все ячейки, содержащие раскрывающиеся списки
Поскольку кнопка со стрелкой вниз не отображается в раскрывающемся списке, если эта ячейка не выбрана, трудно определить, какие ячейки содержат раскрывающиеся списки.Вы можете применить различное форматирование к ячейкам с раскрывающимися списками. Однако вам нужно сначала найти все раскрывающиеся списки, если вы еще не отформатировали их. Есть способ выбрать все ячейки, содержащие раскрывающиеся списки, что позволит вам увидеть, где они находятся.
Сначала выберите ячейку, содержащую раскрывающийся список.Перейдите на вкладку Home и щелкните Find & Select в разделе Editing . Затем выберите Перейти к специальному .
В диалоговом окне Перейти к специальному выберите Проверка данных .Параметр Все ниже Проверка данных выбирает все ячейки, к которым применено какое-либо правило проверки данных. Параметр Same выбирает только ячейки с раскрывающимися списками с использованием того же типа правила проверки данных, что и в выбранной ячейке.
Мы примем выбор по умолчанию: Все , потому что наши раскрывающиеся списки имеют немного другие правила.Один использует именованный диапазон, чтобы получить его значение, а другой использует функцию ДВССЫЛ.
Нажмите ОК .
Выбраны два наших раскрывающихся списка.
Теперь вы можете отформатировать эти ячейки, чтобы отличать их от других ячеек, чтобы вы знали, где находятся все раскрывающиеся списки.Если вы не хотите применять другое форматирование к раскрывающимся спискам, в следующем разделе будет показан другой способ сделать их всегда видимыми.
Сделать стрелку раскрывающегося списка всегда видимой
Кнопка со стрелкой вниз в раскрывающемся списке исчезает, если ячейка не выбрана.Мы собираемся обойти эту проблему, добавив постоянную кнопку со стрелкой вниз справа от раскрывающихся списков.
Мы сделали снимок экрана кнопки со стрелкой вниз в раскрывающемся списке Excel.Мы собираемся вставить это изображение в ячейку справа от раскрывающегося списка, чтобы вы увидели кнопку со стрелкой вниз, даже если раскрывающийся список не выбран. При выборе раскрывающегося списка кнопка со стрелкой вниз для списка отображается над вставленным изображением.
Для начала загрузите раскрывающийся список со стрелкой вниз.png (щелкните эту ссылку правой кнопкой мыши и выберите Сохранить ссылку как ). Затем выберите ячейку справа от раскрывающегося списка и перейдите на вкладку Вставить .
Затем щелкните иллюстраций и выберите изображений .
В диалоговом окне Insert Picture перейдите туда, где вы сохранили стрелку раскрывающегося списка .png и выберите файл. Затем нажмите Вставить .
Изображение вставляется в левую часть ячейки, как будто оно подключено к раскрывающемуся списку в ячейке слева.Теперь вы знаете, где находится этот раскрывающийся список, и можете выбрать эту ячейку, и настоящая кнопка со стрелкой вниз отобразится над фальшивой.
Удалить раскрывающийся список из ячейки
Если вы решите, что хотите удалить раскрывающийся список из ячейки, выберите ячейку и откройте диалоговое окно Data Validation , как описано ранее в разделе Как создать раскрывающийся список (перейдите на вкладку Data и щелкните Data Validation в разделе Data Tools ).Нажмите кнопку Очистить все , которая доступна независимо от того, какая вкладка выбрана в данный момент.
Параметры в диалоговом окне Data Validation сбрасываются до значений по умолчанию.Нажмите ОК .
Выпадающий список будет удален, а для ячейки будет восстановлен формат по умолчанию.Если при удалении раскрывающегося списка был выбран параметр, ячейка заполняется значением этого параметра.
Если вы не хотите, чтобы какие-либо значения сохранялись при удалении раскрывающегося списка, вы можете скопировать пустую ячейку и вставить ее поверх ячейки, содержащей раскрывающийся список.Выпадающий список удаляется, и ячейка становится пустой.
Сделайте так, чтобы раскрывающиеся списки работали на вас
В полной мере используйте раскрывающиеся списки для ввода данных в Excel, чтобы повысить производительность и упростить себе жизнь.В разделе Controls вкладки Developer доступно множество других элементов управления формой, с которыми вы можете поэкспериментировать на своих листах.
Как использовать раскрывающиеся списки в Excel? Поделитесь с нами своими мыслями и идеями в комментариях ниже.Кроме того, вы уже освоили диаграммы Excel?
7 подземных торрент-сайтов для получения контента без цензурыВам нужны специализированные поисковые системы, чтобы найти легальные торренты, закрытые дома, публичные записи и даже НЛО.Войдите в даркнет.
Об авторе Sandy Writtenhouse (Опубликовано 462 статей)Имея степень бакалавра информационных технологий, Сэнди много лет проработала в ИТ-индустрии в качестве менеджера проекта, руководителя отдела и руководителя PMO.Затем она решила следовать своей мечте и теперь постоянно пишет о технологиях.
Больше От Sandy WrittenhouseПодпишитесь на нашу рассылку новостей
Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!
Еще один шаг…!
Подтвердите свой адрес электронной почты в только что отправленном вам электронном письме.
Как создать раскрывающийся список в Excel
Последнее обновление , Дэвид Уэбб .
Эта статья научит вас , как создавать раскрывающиеся списки в Microsoft Excel , используя функцию под названием Data Validation . При применении функция проверки данных служит для ограничения данных, которые могут быть введены в целевую ячейку, требуя от пользователей выбора значения из списка заранее определенных входных данных.Эта функция очень полезна в ряде случаев, от планирования графиков отпусков сотрудников до мониторинга расходов и общего управления проектами. Вот как начать.Создать раскрывающийся список в Microsoft Excel
Для выполнения этой операции вам потребуется как минимум два открытых листа в вашей книге: рабочий лист и чистый лист, на котором вы можете составить свои списки.Первый шаг — создать свой список. Для этого перейдите к своему пустому листу и перечислите элементы, которые вы хотите включить в раскрывающийся список в столбце A.Список может быть любой длины и может включать в себя информацию любого типа — некоторые общие элементы списка: Да / Нет (часто сокращенно Д / Н), Пройден / Не пройден, нумерованные списки и даты.
Теперь вернитесь на свой рабочий лист и щелкните ячейку или ячейки, которые вы хотите проверить. Затем перейдите на вкладку Data и найдите параметр для Data Validation в разделе Data Groups :
Затем перейдите на вкладку Settings и найдите поле Allow .Выберите List из раскрывающегося меню:
Заполнение выпадающего меню в Excel
Перейдите к листу со списком и выберите все элементы, которые вы хотите включить в список. Поле Source в диалоговом окне автоматически регистрирует поля по мере их выбора. Обратите внимание: если вы когда-либо решите добавить или удалить элемент из своего списка, Excel автоматически обновит проверку данных:Убедитесь, что раскрывающийся список в ячейке выбран, чтобы вы обязательно видели стрелку раскрывающегося списка рядом с ячейкой.Когда вы закончите, нажмите ОК .
Теперь выпадающий список должен отображаться в ячейках, указанных на рабочем листе. Чтобы проверить проверку данных, попробуйте ввести в ячейки как действительные, так и недопустимые данные. Действительные данные должны регистрироваться правильно, а неверные данные (информация, не включенная в ваш список) должны возвращать сообщение об ошибке.
Создание длинного раскрывающегося списка в Excel
Если вы хотите добавить элементы в свой раскрывающийся список, но обнаружите, что ваше пространство ограничено, вам следует попробовать вставить ячейки чуть выше последней ячейки, а не под ней.Изображение: © Дмитрий Кляпицкий — 123RF.com
Выпадающий список | Семантический интерфейс
Документы пользовательского интерфейса Начало работы Новое в версии 2.4Введение
Интеграции Инструменты сборки Рецепты ГлоссарийИспользование
Тематика МакетыГлобал
Сброс СайтЭлементы
Кнопка Контейнер Делитель Флаг Заголовок Икона Образ Ввод метка Список Загрузчик Заполнитель Железнодорожный Выявить Сегмент ШагКоллекции
Панировочные сухари Форма Сетка Меню Сообщение СтолПросмотры
Реклама Карта Комментарий Подача Вещь СтатистикаМодули
Аккордеон Флажок Диммер Падать Встроить Модальный Выскакивать Прогресс Рейтинг Поиск Форма Боковая панель Липкий Вкладка ПереходПоведение
API Проверка формы ВидимостьМеню
Падать
Документы пользовательского интерфейса Начало работы Новое в 2.4Введение
Интеграции Инструменты сборки Рецепты ГлоссарийИспользование
Тематика МакетыГлобал
Сброс СайтЭлементы
Кнопка Контейнер Делитель Флаг Заголовок Икона Образ Ввод метка Список Загрузчик Заполнитель Железнодорожный Выявить Сегмент ШагКоллекции
Панировочные сухари Форма Сетка Меню Сообщение СтолПросмотры
Реклама Карта Комментарий Подача Вещь СтатистикаМодули
Аккордеон Флажок Диммер Падать Встроить Модальный Выскакивать Прогресс Рейтинг Поиск Форма Боковая панель Липкий Вкладка ПереходПоведение
API Проверка формы Видимость .