Vvmebel.com

Новости с мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ниспадающий список в excel 2020

Двухуровневый выпадающий список в Excel

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

Первый способ создания двухуровнего списка

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

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

  1. Выберите ячейку, в которую будете вставлять выпадающий список;
  2. Переходим на вкладку ленты Данные;
  3. Выбираем команду Проверка данных;
  4. В выпадающем списке выбираем значение Список;
  5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1[#Заголовки]»).

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1[«&F2&»]»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

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

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

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

  • Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
  • Смещ_по_строкамПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
  • Cмещ_по_столбцам1 — так как нам необходим столбец с подгруппами (городами);
  • [Высота]СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
  • [Ширина]1 — так как это ширина нашего столбца с подгруппами.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data)Проверка данных (Data Validation) . Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List) .
Поле Источник (Source) : предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:

  • Вариант 1
    Ввести список значений вручную (значения в этом случае необходимо заносить через «точку-с-запятой» для русской локализации и через запятую для английской).
  • Вариант 2
    Указание в качестве источника обычной ссылки на диапазон ячеек
    При использовании обычного диапазона в поле Источник (Source) просто указываем диапазон ячеек со значениями (A1:A10). Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно: =$A$1:$A$10

    Недостаток данного метода: нельзя применять ссылки на другие листы и книги. Список будет работать только в случае, если ячейки расположены на том же листе, что и сам список. Что порой не очень удобно, поэтому чаще используется Вариант 3.
  • Вариант 3
    Указание в качестве источника именованного диапазона
    При использовании именованного диапазона необходимо сначала его создать: Создание именованных диапазонов. Предположим, что мы создали именованный диапазон с именем Список1.
    После создания именованного диапазона в поле Источник (Source) вписываем имя этого диапазона, не забыв поставить перед именем знак равно:

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

Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) — данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.

  • Вкладка Сообщение для ввода (Input message)
    Указывается текст, который будет отображаться при активации ячейки с проверкой данных:

    это сообщение можно отключить, сняв галку с пункта Отображать подсказку, если ячейка является текущей (Show input message when cell is selected)
  • Вкладка Сообщение об ошибке (Error alert)
    Здесь указывается следует ли отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

  • Останов (Stop) , Сообщение (Message) — можно ввести только значение из выпадающего списка. Различается только внешний вид сообщения.
  • Предупреждение (Information) — помимо выбора из списка есть возможность ввести и другое значение, которое отсутствует в списке. Может пригодится, если жесткий контроль над вводом данных не нужен и у пользователя была возможность добавить какие-то иные данные в ячейку.

Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных

Выпадающие списки.xls (45,5 KiB, 12 611 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

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

Виталий, только через написание кода(VBA)

Я использовал код при создании списков и отключении, но структура такой проверки не приходит в голову. это же как то надо их перебирать, а потом из диапазона в коде
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=»=$F$4:$F$13″
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = «»
.ErrorTitle = «»
.InputMessage = «»
.ErrorMessage = «»
.ShowInput = True
.ShowError = True
End With
Formula1:=»=$F$4:$F$13″ удалять лишние строки разбивая диапазон на разные группы(

Списки не обязательно изменять — надо их делать сразу динамическими: http://www.excel-vba.ru/chto-umeet-excel/dinamicheskie-imenovannye-diapazony/
А кодом потом удалять те, которые уже использованы.

Подскажите, пожалуйста, если у меня есть список, в котором каждое значение имеет свой стиль (для простоты цвет шрифта: значение_1 — красный, значение_2 — зеленый и т.д.), как сделать что бы выбранное значение автоматически подтягивало его стиль (при выборе значения_1 — текст красный, при выборе значения_2 — текст зеленый и т.д.)?

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

Добрый день.
Как сделать так, чтобы всплывало примечание в изменяющейся ячейке, например, при значении «1» примечание одно, при значении «2» примечание другое?
Спасибо.

Поделитесь своим мнением

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

Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel

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

Видеоурок к статье:

Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда , каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо , одежда – Верхняя_одежда и Спортивные_товары .

Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.

На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты , содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо , содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).

Остальные данные организуются по такому же принципу.

Список 1 уровня

Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3

Создать список полей конкретной таблицы:

=ДВССЫЛ(» Таблица1 «&»[#Заголовки]»)

Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[ Поле1 ]»)

Список 2 уровня

Создать список полей таблицы, выбираемой в вышестоящем списке:

=ДВССЫЛ( A1 &»[#Заголовки]»)

Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[«& А1 &»]»)

Список 3 уровня

Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &»[«& B1 &»]»)

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

Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в надстройке SubEx для Excel , которая моментально сформирует за вас нужные формулы!

Всего возможны три вида выпадающих списков:

1 уровня (первичный, не связанный ни с чем список)

2 уровня (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)

3 уровня (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)

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

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

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

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

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

Подробный пример создания 6-уровневых выпадающих списков на примере номенклатуры товаров магазина приведен в видеоуроке!

Зависимые выпадающие списки в Excel

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

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


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

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

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:


Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):


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

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:


Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):


Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

Ссылка на основную публикацию
Adblock
detector
×
×