Poldet1.ru

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

Как распечатать все варианты в выпадающем списке в Excel?

Excel works!

menu

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

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

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

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

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

Зависимый выпадающий список. Задача

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

В этом случае необходимо найти город, его положение в колонке и перенести в выпадающий список только адреса из соседней колонки. Как в примере на рисунке выше. Мой вариант ACCOUNTESLI также реализует это в формулах ПОИСК и СЧЕТ.

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

Создаем список для примера

vyipadayushhiy-1

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

Читайте так же:
Как разделить диапазон ячеек на число в Excel?

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

vyipadayushhiy-2

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

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

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

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

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

Пример, как обычно, можно загрузить здесь

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

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

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

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

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

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

  1. Сделайте стол. Первая строка каждого столбца должна содержать категорию меню (для удобства).
Читайте так же:
Как проверить или найти значение в списке в Excel?

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

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

  1. Затем перейдите к созданию списка блюд из одной из категорий. В этом вам поможет функция INDIRECT, которая преобразует текст в стандартную ссылку Excel и ее источник данных. Вам нужно выбрать категорию, снова открыть Data Checker и ввести функцию =DVSSYL(H2).

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

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

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

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

=POSE(F3;» «;»_») без перевернутой запятой.

Итоговая формула выглядит следующим образом

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

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

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

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

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

  1. Активируйте ячейку, в которой необходимо разместить выпадающий список.
  2. Откройте параметры проверки данных. В поле «Источник» введите формулу: =DVSource(«[List1.xlsx]Sheet1!$A$1:$A$9»).
Читайте так же:
Как разделить общий платеж по месяцам в Excel?

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

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

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

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

Создать источник данных

Источник данных Excel

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

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

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

Читайте так же:
Как разделить по разделителю и транспонировать ячейки в Excel?

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

Раскрывающийся список Excel

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

Настроить списки в Excel

  • В разделе Configuration> Validation Criteria> Allow мы выбираем список.
  • Затем переходим к полю Origin и нажимаем на значок в конце поля, чтобы выбрать диапазон ячеек, содержащих данные.

Ячейки, в которых находятся диапазоны Excel

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

Excel

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

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

Содержание статьи соответствует нашим редакционным рекомендациям. Чтобы сообщить об ошибке, нажмите здесь.

Полный путь к статье: Мобильный форум » Автоматизация офиса » Microsoft Excel » Как сделать выпадающий список в Excel

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