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

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

Однако это не останавливается на достигнутом. Используя некоторые творческие способы настройки проверки данных для раскрывающихся ячеек, вы даже можете создать несколько связанных раскрывающихся списков, в которых элементы, доступные во втором списке,^{(available in a second list)} зависят от раздела, созданного пользователем в первом списке.

Для чего нужны несколько связанных ^(Linked)раскрывающихся^(Good) списков ?

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

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

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

• Компьютерная часть^{(Computer Part)} : монитор, мышь^(Mouse) , клавиатура^(Keyboard) , базовая система^{(Base System)}
• Тип детали:
  • Монитор^(Monitor) : стекло, корпус^(Housing) , шнур питания^{(Power Cord)} , внутренняя электроника^{(Internal Electronics)}
  • Мышь^(Mouse) : колесо, светодиодная подсветка^{(LED Light)} , шнур^(Cord) , кнопки^(Buttons) , корпус
  • Клавиатура^(Keyboard) : Клавиши, Корпус^(Housing) , Мембрана^(Membrane) , Шнур^(Cord) , Внутренняя электроника^{(Internal Electronics)}
  • Базовая система^{(Base System)} : корпус, кнопки^(Buttons) , порты^(Ports) , питание^(Power) , внутренняя электроника^{(Internal Electronics)} , операционная система^{(Operating System)}

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

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

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

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

Создайте свой исходный лист раскрывающегося списка^{(List Source Sheet)}

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

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

Затем вам нужно будет выбрать и назвать каждый диапазон, чтобы при последующей настройке проверки данных^{(Data Validation)} вы могли выбрать правильный диапазон.

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

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

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

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

Для этого просто выберите все диапазоны на втором листе, который вы создали. Затем выберите « Формулы^(Formulas) » в меню и выберите « Создать из выделенного^{(Create from Selection)} » на ленте.

Появится всплывающее окно. Убедитесь, что выбрана только верхняя строка , а затем нажмите ^{(Top row)}кнопку ОК^(OK) .

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

Настройте свой первый раскрывающийся список

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

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

2. В открывшемся окне « Проверка данных » выберите « ^{(Data Validation)}Список»^(List) в разделе « Разрешить^(Allow) » и в разделе « Источник^(Source) » выберите значок со стрелкой вверх. Это позволит вам выбрать диапазон ячеек, которые вы хотите использовать в качестве источника для этого раскрывающегося списка.

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

4. Выберите стрелку вниз в окне выбора, чтобы развернуть окно проверки данных^{(Data Validation)} . Вы увидите, что выбранный вами диапазон теперь отображается в поле Источник^(Source) . Выберите ОК^(OK) , чтобы закончить.

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

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

Настройте свой первый раскрывающийся список

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

Повторите описанный выше процесс, чтобы открыть окно проверки данных . ^{(Data Validation)}Выберите Список^(List) в раскрывающемся списке Разрешить . ^(Allow)Поле « Источник^(Source) » — это то, что будет отображать элементы списка в зависимости от того, что выбрано в первом раскрывающемся списке.

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

=INDIRECT($B$1)

Как работает функция ДВССЫЛ^(INDIRECT) ?

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

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

Теперь протестируйте свои новые множественные связанные раскрывающиеся списки. Используйте первое раскрывающееся меню, чтобы выбрать одну из частей компьютера. При выборе второго раскрывающегося списка вы должны увидеть соответствующие элементы списка для этой части компьютера. Это были типы деталей в столбце на втором листе^{(on the second sheet)} , который вы заполнили для этой детали.

Использование нескольких связанных^{(Multiple Linked)} раскрывающихся списков в Excel

Как видите, это очень классный способ сделать ваши электронные таблицы более динамичными. Заполняя последующие раскрывающиеся списки в зависимости от того, что пользователи выбирают в других ячейках^{(select in other cells)} , вы можете сделать свои электронные таблицы более чувствительными к пользователям, а данные — более полезными.

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

How to Create Multiple Linked Drop Down Lists in Excel

Drop-down liѕts іn Excel are powerful tools. They let you provide users with a drop-down arrow thаt, when selected, provides them with a list of choices.

This can reduce data-entry errors because it avoids users from having to type answers directly. Excel even lets you pull the items for those drop-down lists from a range of cells.

However, it doesn’t stop there. Using some creative ways to configure data validation for drop-down cells, you can even create multiple, linked drop-down lists, where the items that are available in a second list depend on the section the user made in the first list.

What Are Multiple Linked Drop-Down Lists Good For?

Consider that most forms online fill out secondary drop-down lists based on what you answer in the drop-down list before it. This means you can make your Excel data-entry sheets just as advanced as online forms. It will modify itself based on the user’s answers. 

For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need computer repairs. 

The entry options might look like this:

• Computer Part: Monitor, Mouse, Keyboard, Base System
• Part Type:
  • Monitor: Glass, Housing, Power Cord, Internal Electronics
  • Mouse: Wheel, LED Light, Cord, Buttons, Casing
  • Keyboard: Keys, Housing, Membrane, Cord, Internal Electronics
  • Base System: Casing, Buttons, Ports, Power, Internal Electronics, Operating System

As you can see from this tree, the information that should be available to select for “Part Type” depends on which Computer Part the user selects in the first dropdown list.

In this example, your spreadsheet may start out looking something like this:

If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.

Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.

Create Your Drop-Down List Source Sheet

The cleanest way to set something like this up is to create a new tab in Excel where you can configure all of your drop-down list items.

To set up these linked drop-down lists, create a table where the header at the top are all of the computer parts you want to include in the first dropdown list. Then list all of the items (part types) that should go under that header.

Next, you’ll want to select and name each range so that when you’re setting up Data Validation later, you’ll be able to select the correct one.

To do this, select all of the items under each column and name that selected range the same as the header. To name a table, you simply type the name in the field over the “A” column.

For example, select cells A2 through A5, and name that range “Monitor”.

Repeat this process until you have all of the ranges named appropriately. 

An alternative way to do this is using Excel’s Create from Selection feature. This lets you name all of the ranges like the manual process above, but with a single click.

To do this, just select all of the ranges in the second sheet that you created. Then select Formulas from the menu, and select Create from Selection in the ribbon.

A pop-up window will appear. Make sure only Top row is selected and then select OK.

This will use the header values in the top row to name each of the ranges underneath it. 

Set Up Your First Drop Down List

Now it’s time to set up your multiple, linked drop-down lists. To do this:

1. Back on the first sheet, select the blank cell to the right of the first label. Then select Data from the menu, and select Data Validation in the ribbon.

2. In the Data Validation window that opens, select List under Allow, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.

3. Select the second sheet where you set up the drop-down list source data, and then select only the header fields. These will be used to fill out the initial drop-down list in the cell you’ve selected.

4. Select the down arrow in the selection window to expand the Data Validation window. You’ll see the range you’ve selected now displayed in the Source field. Select OK to finish.

5. Now, back on the main sheet, you’ll notice that the first drop-down list contains each of the header fields from the second sheet.

Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.

Set Up Your First Drop Down List

Select the second cell that you want to load list items for depending on what’s selected in the first cell.

Repeat the process above to open the Data Validation window. Select List in the Allow drop-down. The Source field is what will pull in list items depending on what’s selected in the first drop-down list.

To do this, enter the following formula:

=INDIRECT($B$1)

How does the INDIRECT function work?

This function returns a valid Excel reference (in this case to a range), from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the name of the range and then provides the drop-down data validation with the correct range associated with that name.

Note: If you configure the data validation for this second drop-down without selecting a value from the first drop-down, you’ll see an error message. You can select Yes to ignore the error and continue.

Now, test your new multiple, linked drop-down lists. Use the first drop-down to select one of the computer parts. When you select the second drop-down, you should see the appropriate list items for that computer part. These were the part types in the column on the second sheet that you filled in for that part.

Using Multiple Linked Drop-Down Lists in Excel

As you can see, this is a very cool way to make your spreadsheets much more dynamic. By filling in subsequent drop-down lists in response to what users select in other cells, you can make your spreadsheets far more responsive to users, and the data far more useful.

Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheets. Share some of your own interesting tips in the comments section below.

Related posts

• Как быстро вставить несколько строк в Excel

• Как объединить данные в нескольких файлах Excel

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• 4 способа использования галочки в Excel

• Google Таблицы против Microsoft Excel — в чем разница?

• Использование инструмента поиска цели для анализа «что, если» в Excel

• Как вставить CSV или TSV в рабочий лист Excel

• Как сортировать по дате в Excel

• Как написать формулу/оператор ЕСЛИ в Excel

• 3 способа разделить ячейку в Excel

• Различия между Microsoft Excel Online и Excel для рабочего стола

• Как исправить строку в Excel

• Как переместить столбцы в Excel

• Расширенное руководство по VBA для MS Excel

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Когда использовать Index-Match вместо VLOOKUP в Excel

• Центрируйте данные рабочего листа в Excel для печати

• Подключение Excel к MySQL