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

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

Что такое выпадающий список

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

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

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

1. Подготовка данных для списка

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

A1: Январь

A2: Февраль

A3: Март

A4: Апрель

2. Выбор ячеек для выпадающего списка

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

3. Открытие инструмента «Проверка данных»

— Перейдите на вкладку «Данные» на верхней панели инструментов.

— Нажмите на кнопку «Проверка данных» (в английской версии это «Data Validation»).

4. Настройка выпадающего списка

— В открывшемся окне «Проверка данных» на вкладке «Параметры» в разделе «Тип данных» выберите «Список» (в английской версии — «List»).

— В поле «Источник» (в английской версии — «Source»):

Если ваши данные находятся в ячейках, например, с A1 по A12, укажите диапазон $A$1:$A$12. Если вы хотите указать значения вручную, введите их через запятую, например: Январь,Февраль,Март,Апрель.

5. Нажмите ОК

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

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

Чтобы добавлять функции и элементы управления, нам нужно включить вкладку Разработчик (Developer). Вот как это сделать:

1. Перейдите в «Файл» > «Параметры» (в английской версии — Options).

2. В меню «Настроить ленту» отметьте галочкой «Разработчик» (Developer).

3. Нажмите ОК.

Создание элемента управления ComboBox

1. Перейдите на вкладку «Разработчик».

2. Нажмите «Вставить» и выберите элемент «ComboBox (ActiveX Control)».

3. Разместите элемент на вашем листе рядом с ячейкой, где вы сделали выпадающий список.

Добавление кода VBA для поиска

Visual Basic for Applications (VBA) — это язык программирования в Excel, который поможет нам реализовать функцию поиска.

1. Перейдите на вкладку «Разработчик» и нажмите «Visual Basic».

2. В открывшемся окне выберите «Вставка» > «Модуль».

3. Вставьте следующий код:

Private Sub ComboBox1_Change()

Dim ws As Worksheet

Dim rng As Range

Dim val As String

Set ws = ThisWorkbook.Sheets("Лист1")

Set rng = ws.Range("A1:A10")

val = ComboBox1.Text

ComboBox1.Clear

If val <> "" Then

For Each Cell In rng

If UCase(Left(Cell.Value, Len(val))) = UCase(val) Then

ComboBox1.AddItem Cell.Value

End If

Next Cell

ComboBox1.DropDown

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$1" Then

With ComboBox1

.Visible = True

.Left = Target.Left

.Top = Target.Top

.Width = Target.Width

.Height = Target.Height

End With

End If

End Sub

4. Сохраните и закройте окно VBA.

Настройка элемента ComboBox

1. Выберите элемент ComboBox.

2. Перейдите на вкладку «Разработчик» и убедитесь, что вы в режиме дизайна.

3. Правой кнопкой мыши на элементе ComboBox и выберите «Свойства».

4. Измените имя элемента на «ComboBox1».

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

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

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

1. Подготовка списка данных

Предположим, у нас есть данные о сотрудниках, которые мы хотим использовать. Запишем их на отдельном листе, например, на листе «Данные»:

A1: Имя

B1: Должность

C1: Отдел

D1: Телефон

A2: Иван Иванов

B2: Менеджер

C2: Продажи

D2: 123-456

A3: Мария Петрова

B3: Инженер

C3: Технический

D3: 456-789

...

2. Создание выпадающего списка

— Перейдите на лист, где вы хотите создать выпадающий список.

— Выберите ячейку, в которой будет выпадающий список, например, E2.

— Перейдите на вкладку «Данные» на верхней панели инструментов.

— Нажмите «Проверка данных» (в английской версии это «Data Validation»).

— В открывшемся окне на вкладке «Параметры» в разделе «Тип данных» выберите «Список».

— В поле «Источник» введите диапазон ячеек с именами сотрудников с листа «Данные», например: 'Данные'!$A$2:$A$10.

— Нажмите ОК.

3. Подстановка данных с функцией ВПР (VLOOKUP)

Для автоматической подстановки данных будем использовать функцию ВПР (в английской версии — VLOOKUP).

— Решите, куда вы хотите подставлять данные. Предположим, это будут ячейки F2, G2, и H2, соответствующие Должности, Отделу и Телефону соответственно.

— В ячейку F2 введите следующую формулу:

=ВПР(E2; 'Данные'!$A$2:$D$10; 2; ЛОЖЬ)

Здесь:

- E2 — ячейка с вашим выпадающим списком.

- 'Данные'!$A$2:$D$10 — диапазон данных на листе "Данные".

- 2 — номер столбца, откуда подставляется значение (в данном случае, Должность).

- ЛОЖЬ — это точное совпадение.

— Аналогично, в ячейку G2 введите формулу для подстановки отдела:

=ВПР(E2; 'Данные'!$A$2:$D$10; 3; ЛОЖЬ)

— И в ячейку H2 введите формулу для подстановки телефона:

=ВПР(E2; 'Данные'!$A$2:$D$10; 4; ЛОЖЬ)

Теперь, когда вы выберете имя сотрудника из выпадающего списка в ячейке E2, в ячейках F2, G2 и H2 автоматически подтянутся его Должность, Отдел и Телефон соответственно.

Как сделать в Excel выпадающий список с функцией ДВССЫЛ

В Excel иногда необходимо создать выпадающие списки, которые меняются в зависимости от выбора в другом выпадающем списке. Это можно сделать с помощью функции ДВССЫЛ (в английской версии — INDIRECT).

1. Подготовка данных

Предположим, вы работаете с данными о разных категориях товаров. Например, у вас есть категории «Фрукты» и «Овощи», и для каждой из этих категорий есть свой список товаров.

На отдельном листе, который назовем «Данные», создадим следующее:

A1: Категория

A2: Фрукты

A3: Овощи

D1: Фрукты

D2: Яблоко

D3: Банан

D4: Апельсин

F1: Овощи

F2: Помидор

F3: Огурец

F4: Морковь

2. Создание основного выпадающего списка

— Перейдите на лист, где вы хотите создать выпадающий список.

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

— Перейдите на вкладку «Данные» на верхней панели инструментов.

— Нажмите «Проверка данных» (в английской версии это «Data Validation»).

— В открывшемся окне на вкладке «Параметры» в разделе «Тип данных» выберите «Список».

— В поле «Источник» введите диапазон ячеек с категориями с листа «Данные», например: 'Данные'!$A$2:$A$3.

— Нажмите ОК.

3. Именование диапазонов для подкатегорий

Чтобы можно было использовать функцию ДВССЫЛ, нам нужно дать именованные диапазоны подкатегориям:

— Выделите диапазон ячеек D2:D4 (Фрукты).

— Перейдите на вкладку «Формулы».

— Нажмите «Присвоить имя» (в английской версии это «Define Name»).

— В поле «Имя» введите Фрукты и нажмите ОК.

Повторите то же самое с диапазоном F2:F4 (Овощи):

— Выделите диапазон ячеек F2:F4.

— Перейдите на вкладку «Формулы».

— Нажмите «Присвоить имя».

— В поле «Имя» введите Овощи и нажмите ОК.

4. Создание зависимого выпадающего списка с функцией ДВССЫЛ

— Выберите ячейку, где будет зависимый выпадающий список, например, I2.

— Перейдите на вкладку «Данные».

— Нажмите «Проверка данных».

— В открывшемся окне на вкладке «Параметры» в разделе «Тип данных» выберите «Список».

— В поле «Источник» введите следующую формулу:

=ДВССЫЛ($H$2)

Здесь:

- H2 — это ячейка с выбором категории из первого выпадающего списка.

— Нажмите ОК.

Теперь в ячейке I2 у вас есть выпадающий список, который изменяется в зависимости от того, что выбрано в ячейке H2. Чтобы проверить, как это работает, выберите в ячейке H2 категорию, например, «Фрукты». Выпадающий список в ячейке I2 теперь должен содержать элементы «Яблоко», «Банан» и «Апельсин». Если вы выберете «Овощи», список изменится на «Помидор», «Огурец» и «Морковь».

Читайте также, что такое ошибка 405