Всем привет!
Раскрывающиеся списки в Excel — это мощные подручные инструменты. Они позволяют предоставить пользователям указатель раскрывающегося списка, который при выборе предоставляет им список вариантов. Это может уменьшить количество ошибок при вводе данных, поскольку избавляет пользователей от необходимости вводить ответы напрямую. Excel даже позволяет вам извлекать элементы для этих раскрывающихся списков из диапазона ячеек.
Используя некоторые творческие способы настройки проверки данных для раскрывающихся ячеек, вы даже можете создать несколько связанных раскрывающихся списков, в которых элементы, доступные во втором списке, зависят от раздела, созданного пользователем в первом списке.
Для чего нужны неоднократно раскрывающиеся списки?
Большинство онлайн-форм заполняют вторичные раскрывающиеся списки на основе того, что вы ответили в раскрывающемся списке перед ним. Это означает, что вы можете сделать свои листы ввода данных Excel такими же продвинутыми, как онлайн-формы. Он будет изменяться в зависимости от ответов пользователя.
Например, предположим, что вы используете электронную таблицу Excel для сбора информации о компьютерах пользователей, нуждающихся в ремонте компьютеров .
Варианты входа могут выглядеть так:
- Компьютерная часть : монитор, мышь, клавиатура, базовая система
- Тип детали:
- Монитор : стекло, корпус, шнур питания, внутренняя электроника
- Мышь : Колесико, Светодиодная подсветка, Шнур, Кнопки, Корпус
- Клавиатура : клавиши, корпус, мембрана, шнур, внутренняя электроника
- Базовая система : корпус, кнопки, порты, питание, внутренняя электроника, операционная система
Как видно из этого дерева, информация, которая должна быть доступна для выбора «Тип части», зависит от того, какую часть компьютера пользователь выбирает в первом раскрывающемся списке.
В этом примере ваша электронная таблица может выглядеть примерно так:
Если вы создаете несколько связанных раскрывающихся списков, вы можете использовать элемент, выбранный из раскрывающегося списка в B1, для управления содержимым раскрывающегося списка в B2.
Давайте посмотрим, как вы можете настроить это.
1. Создайте свой исходный лист раскрывающегося списка
Самый простой способ настроить что-то подобное — создать новую вкладку в Excel, где вы можете настроить все элементы раскрывающегося списка.
Чтобы настроить эти связанные раскрывающиеся списки, создайте таблицу, в которой заголовок вверху — это все части компьютера, которые вы хотите включить в первый раскрывающийся список. Затем перечислите все элементы (типы деталей), которые должны находиться под этим заголовком.
Затем вам нужно будет выбрать и назвать каждый диапазон, чтобы при последующей настройке проверки данных вы могли выбрать правильный диапазон. Для этого выберите все элементы в каждом столбце и назовите выбранный диапазон так же, как заголовок. Чтобы назвать таблицу, вы просто вводите имя в поле над столбцом «A».
Например, выберите ячейки с A2 по A5 и назовите этот диапазон «Монитор».
Повторяйте этот процесс, пока у вас не будут названы все диапазоны соответствующим образом.
Альтернативный способ сделать это — использовать функцию Excel «Создать из выделенного». Это позволяет вам назвать все диапазоны, как в ручном процессе выше, но одним щелчком мыши. Для этого просто выберите все диапазоны на втором листе, который вы создали. Затем выберите « Формулы » в меню и выберите « Создать из выделенного » на ленте.
Появится всплывающее окно. Убедитесь, что выбрана только верхняя строка , а затем нажмите кнопку ОК . Этот способ использует значения заголовка в верхней строке, чтобы назвать каждый из диапазонов под ним.
2. Настройте свой первый раскрывающийся список.
Теперь пришло время настроить несколько связанных раскрывающихся списков.
1. Вернувшись на первый лист, выберите пустую ячейку справа от первого значения. Затем выберите « Данные » в меню и выберите « Проверка данных » на ленте.
2. В открывшемся окне «Проверка данных» выберите « Список» в разделе «Тип данных», а в разделе «Источник» выберите значок с таблицей. Это позволит вам выбрать диапазон ячеек, которые вы хотите использовать в качестве источника для этого раскрывающегося списка.
3. Выберите второй лист, на котором вы настроили исходные данные раскрывающегося списка, а затем выберите только поля заголовка. Они будут использоваться для заполнения начального раскрывающегося списка в выбранной вами ячейке.
4. Вы увидите, что выбранный вами диапазон теперь отображается в поле Источник . Выберите ОК , чтобы закончить.
5. Теперь, вернувшись на главный лист, вы заметите, что первый раскрывающийся список содержит каждое из полей заголовка из второго листа.
Теперь, когда ваш первый раскрывающийся список готов, пришло время создать следующий связанный раскрывающийся список.
3. Настройте свой первый раскрывающийся список
Выберите вторую ячейку, для которой вы хотите загрузить элементы списка, в зависимости от того, что выбрано в первой ячейке.
Повторите описанный выше процесс, чтобы открыть окно проверки данных. Выберите Список в раскрывающемся списке Тип данных. Поле «Источник» — это то, что будет отображать элементы списка в зависимости от того, что выбрано в первом раскрывающемся списке. Для этого введите следующую формулу:
=ДВССЫЛ($B$1)
Как работает функция ДВССЫЛ?
Эта функция возвращает допустимую ссылку Excel (в данном случае на диапазон) из текстовой строки. В этом случае текстовая строка — это имя диапазона, переданного первой ячейкой ($B$1). Таким образом, ДВССЫЛ берет имя диапазона, а затем обеспечивает проверку раскрывающихся данных с правильным диапазоном, связанным с этим именем.
Примечание . Если вы настроите проверку данных для этого второго раскрывающегося списка, не выбрав значение из первого раскрывающегося списка, вы увидите сообщение об ошибке. Вы можете выбрать Да , чтобы игнорировать ошибку и продолжить.
Теперь протестируйте свои новые множественные связанные раскрывающиеся списки. Используйте первое раскрывающееся меню, чтобы выбрать одну из частей компьютера. При выборе второго раскрывающегося списка вы должны увидеть соответствующие элементы списка для этой части компьютера. Это были типы деталей в столбце на втором листе , который вы заполнили для этой детали.
Как видите, это очень классный способ сделать ваши таблицы более динамичными. Заполняя последующие раскрывающиеся списки в зависимости от того, что пользователи выбирают в других ячейках , вы можете сделать свои таблицы более чувствительными к пользователям, а данные — более полезными.
Поэкспериментируйте с приведенными выше советами и посмотрите, какие интересные связанные раскрывающиеся списки вы можете создать в своих таблицах. Поделитесь некоторыми из ваших собственных интересных советов в разделе комментариев ниже.
На этом у меня всё. 🏁 Если вам понравился сегодняшний трюк, ставьте лайки 👍 и подписывайтесь на канал. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ , обязательно еще что-нибудь присмотрите )) Спасибо!
