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

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

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

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

Для чего нужны множественные связанные раскрывающиеся списки?

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

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

Параметры входа могут выглядеть так:

• Компьютерная часть: монитор, мышь, клавиатура, базовая система
• Тип детали:
  • Монитор: стекло, корпус, шнур питания, внутренняя электроника
  • Мышь: колесо, светодиодная подсветка, шнур, кнопки, корпус
  • Клавиатура: клавиши, корпус, мембрана, шнур, внутренняя электроника
  • Базовая система: корпус, кнопки, порты, питание, внутренняя электроника, операционная система

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

В этом примере ваша электронная таблица может выглядеть примерно так:

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

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

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

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

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

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

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

Например, выберите ячейки с A2 по A5 и назовите этот диапазон «Монитор».

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

Альтернативный способ сделать это – использовать функцию Excel «Создать из выделенного». Это позволяет вам присваивать имена всем диапазонам, как описано выше вручную, но одним щелчком мыши.

Для этого просто выберите все диапазоны на втором созданном вами листе. Затем выберите «Формулы» в меню и выберите «Создать из выделенного» на ленте.

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

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

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

Пришло время создать несколько связанных раскрывающихся списков. Сделать это:

1. Вернувшись на первый лист, выберите пустую ячейку справа от первой метки. Затем выберите «Данные» в меню и выберите «Проверка данных» на ленте.

1. В открывшемся окне «Проверка данных» выберите «Список» в разделе «Разрешить», а в разделе «Источник» щелкните значок стрелки вверх. Это позволит вам выбрать диапазон ячеек, который вы хотите использовать в качестве источника для этого раскрывающегося списка.

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

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

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

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

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

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

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

Для этого введите следующую формулу:

= КОСВЕННО ($ B $ 1)

Как работает функция КОСВЕННО?

Эта функция возвращает действительную ссылку Excel (в данном случае на диапазон) из текстовой строки. В этом случае текстовая строка – это имя диапазона, переданного первой ячейкой ($ B $ 1). Таким образом, INDIRECT берет имя диапазона, а затем предоставляет раскрывающееся меню проверки данных с правильным диапазоном, связанным с этим именем.

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

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

Использование нескольких связанных раскрывающихся списков в Excel

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

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