Ref-sgain.ru

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

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

Несколько выпадающих списков в Excel

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

Динамические выпадающие списки бывают двух видов:

  • Растущий;
  • Зависимый.

Растущий выпадающий список

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

Вот пример того, как создать такую систему.

Список будущих элементов появляется на любом листе. Вот как настраивается правило проверки данных: Вкладка «Данные» -> область «Работа с данными» -> кнопка «Проверить данные» -> тип данных «Список». Необходимо создать следующую формулу для источника:

=Ассоциация($A$1;;DISPLAY($A:$A)), где :

  • $A$1 — ячейка со значением первого элемента в списке
  • $A:$A — колонка со списком всех элементов в списке.

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

Параметры динамического раскрывающегося списка:

Параметры ростущего выпадающего списка

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

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

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

  • Бытовое_оборудование (пробел в названии намеренно заменен знаком подчеркивания, так как менеджер имен, который будет использоваться ниже, не принимает символ пробела);
  • Электроника;
  • Мебель.

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

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

Наконец, устанавливаем связанные выпадающие списки, которые мы создали для ячеек с помощью проверки данных (вкладка «Данные» -> область «Работа с данными»).

Читайте так же:
Как скрыть флажок, когда строка скрыта в Excel?

Для основного исходного списка достаточно указать ссылку на диапазон, а для зависимого списка — формулу:

=DWSSSIL($A$1), где :

  • A$1 — это ячейка, содержащая главный список.

Параметры зависимых списков в excel

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

Excel works!

menu

Выпадающий список по значению

Выпадающий список по значению, зависимый выпадающий список, связанный выпадающий список. Как только не называют эту хитрую возможность Excel. «А в чем хитрость?» — спросите вы. Это выпадающий список, который выдает только те значения, которые привязаны к конкретному значению другой ячейки. Т.е. условно, вы выбираете город, а к нему выпадают списком только те улицы, которые есть в этом городе (а не все улицы подряд из столбца). Делается это непросто, но мы разберемся.

Выпадающий список по значению. Введение.

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

  • Может использоваться для целого столбца значений;
  • Не перемещается при копировании и перетаскивании.
Зависимый выпадающий список. Задача

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

Логически: найдите город, его положение в столбце и перенесите в выпадающий список только адреса соседнего столбца. Как в примере на рисунке выше. Это делается с помощью формул SEARCH и HUMMING, а также в моем варианте CUENTASLI.

  • ПОИСКПОЗ — находит первую позицию выбранного города в столбце городов
  • СМЕЩ — сначала находя ячейку по ПОИСКПОЗ, а потом передавая в выпадающий список нужный диапазон улиц.
  • Формулу мы будем вписывать не в ячейку, а в так называемый диспетчер имен (когда создаем диапазон с именем).
Читайте так же:
Как скрыть отрицательные числа в Excel?
Создаем список для примера

vyipadayushhiy-1

На основе города в ячейке D3 мы добавили выпадающий список в ячейку E3.

Сперва добавляем новый диапазон Формулы — Диспетчер имен — Создать

vyipadayushhiy-2

В окне создания имени введите название (например, Independent_Street) и введите формулу

Это выглядит сложно, но это не нереально, и мы разберемся с этим.

СМЕЩ , как работает формула по реквизитам (ячейка от которой начинаем отчет позиции; на сколько смещаемся по строкам (находим при помощи ПОИСКПОЗ, вычитаем 2 и пр), на сколько смещаем по столбцам =1; Смещаемся вверх на столько ячеек сколько улиц привязано к городу (считаем их СЧЕТЕСЛИ); и на один столбец вправо) — это даст нам диапазон, который отображается в окне выбора выпадающего списка.

Теперь создадим выпадающий список Данные — Проверка данных — выбираем список и источник (диапазон Зависимая_улица). Получилось:

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

Как обычно, пример можно скачать здесь

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

Столбец с основными данными — городами должен быть заполнен правильно в каждой строке — как это «правильно» можно прочитать здесь

3 эффективных способа сделать выпадающий список в Excel

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

Здравствуйте, мои дорогие и уважаемые читатели. Меня зовут Федор Лыков. Не так давно я начал интересоваться Microsoft Excel и начал знакомиться с ним. Сегодня я расскажу вам, как создать выпадающий список в Excel.

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

Прежде чем начать эту статью, я хотел бы облегчить вам задачу. Курс «Excel + Google Spreadsheets с нуля до PRO» позволяет всем пользователям узнать подробную информацию о функциях Microsoft Excel и получить персональный сертификат по окончании программы обучения.

Как в Эксель сделать выпадающий список: применение дополнительного массива

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

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

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

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

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

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

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

Если первым символом является буква, то ограничений нет.

  • Вы также можете написать небольшое пояснение. Но это зависит от вас.
  • Если все введено правильно, нажмите «OK».

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

Но на этом история раскрывающегося списка не заканчивается.

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

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

Появится окно, в котором необходимо тщательно проверить введённые данные.

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

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

  • После этого нажмите «ОК».

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

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

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

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

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

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

Если ваша цель — изучить все возможности MS Office и инструментов Google, запишитесь на обучение к профессионалам, перечисленным в ссылке выше.

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

  • Для этого нужно перейти из верхнего меню на вкладку «Файл» и выбрать пункт «Параметры».
Читайте так же:
Как случайным образом заполнить значения из списка данных в Excel?

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

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

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

  • Результатом этого действия будет вкладка «Разработчик».
  • После этого продолжайте.

В наши дни в Интернете существует множество мошенничеств. И если вы хотите научиться работать с формулами, лучше обратить внимание на проверенные ресурсы. Одним из них является курс EXCEL.

Продолжение

Дальнейшее соответствует предыдущему варианту.

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

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

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

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

  • Далее нам нужен режим конструктора. Здесь нас интересует кнопка «Свойства управления».

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

  • Нам показывают окно, в котором ведется список.
  • Мы снова смотрим на колонку «ListFillRange», где мы указываем диапазон с информацией.

Например, она может выглядеть так: «A2:A8».

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

  • Кликаем на пустую ячейку, и последовательно переходим «Объект ComboBox» — «Edit».

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

  • Таким образом, у нас есть ролл-лист в другую сторону.

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

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

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

Все, о чем мы рассказываем на страницах моего блога, ориентировано на начинающего пользователя. Вы можете максимизировать свой набор навыков, посетив курс «Power BI & Excel PRO».

Как создать связанные между собой списки

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

Читайте так же:
Как создать зависимый выпадающий список в листе Google?

Например, мы выбираем «картофель» в основном списке. Затем нам предлагается определить размерность (килограммы или граммы).

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

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

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

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

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

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

  • Затем выберите вторую ячейку. Но источник колонки данных заполняется следующим образом: «=DVSSSIL».
  • А в скобках мы указываем ссылку на первую ячейку массива.

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

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

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

Заключение

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector