Три типа раскрывающихся списков с несколькими столбцами — пошаговое руководство

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

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

Получить образец файла:

Нажмите, чтобы скачать образец файла

Создание Динамический список на основе нескольких столбцов

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

Использование формул для создания Динамический список на основе нескольких столбцов

Шаг 1: Создайте основной Раскрывающийся список

1. Выделите ячейки (в данном случае G9:G13), в которые хотите вставить раскрывающийся список, перейдите на вкладку Данные и нажмите Проверка данных > Проверка данных.

2. В диалоговом окне Проверка данных выполните следующие настройки.

1) Перейдите на вкладку Параметры;

2) В поле Тип данныхвыберите значение СписокРаскрывающийся список;

3) Щёлкните в поле Источники выделите ячейки с континентами, которые вы хотите отобразить в Раскрывающийся список;

4) Нажмите кнопку OK. См. снимок экрана:

Шаг 2: Создайте вторичный Раскрывающийся список

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

2. В диалоговом окне Создать из выделения установите флажок только напротив Верхняя строка и нажмите кнопку OK.

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

4. В диалоговом окне Проверка данных выполните следующие действия:

1) Оставайтесь на вкладке Параметры;

2) В поле Тип данныхвыберите значение СписокРаскрывающийся список;

3) Введите следующую формулу в поле Источник.

=INDIRECT(SUBSTITUTE(G9," ","_"))

Где G9— первая ячейка основных ячеек Раскрывающийся список.

4) Нажмите кнопку OK.

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

Вторичный раскрывающийся список теперь готов: при выборе континента в основном раскрывающемся списке во вторичном отображаются только страны выбранного континента.

Шаг 3: Создайте третий Раскрывающийся список

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

2. В диалоговом окне Создать из выделенияустановите флажок только напротив Верхняя строкаи нажмите кнопку OK.

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

4. В диалоговом окне Проверка данных выполните следующие действия:

1) Оставайтесь на вкладке Параметры;

2) В поле Тип данныхвыберите значение СписокРаскрывающийся список;

3) Введите следующую формулу в поле Источник.

=INDIRECT(SUBSTITUTE(H9," ","_"))

Где H9— первая ячейка дополнительных ячеек Раскрывающийся список.

4) Нажмите кнопку OK.

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

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

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

Создание Динамический список на основе нескольких столбцов за несколько кликов с помощью Kutools для Excel

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

Как видите, вся операция выполняется всего за несколько кликов. Вам нужно лишь:

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

Выполнение множественного выбора в Раскрывающийся список в Excel

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

Использование кодов VBA для множественного выбора в раскрывающемся списке ExcelРаскрывающийся список

Следующий сценарий VBA позволяет осуществлять множественный выбор в раскрывающемся списке Excel без дубликатов. Выполните следующие действия.

Шаг 1: Откройте редактор кода VBA и скопируйте код

1. Перейдите на вкладку листа, щёлкните по ней правой кнопкой мыши и выберите Просмотреть код в контекстном меню.

2. Откроется окно Microsoft Visual Basic для приложений, в которое необходимо скопировать следующий код VBA в редактор Лист (Код).

Код VBA: Разрешить множественный выбор в Раскрывающийся список без дубликатов

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Шаг 2: Проверка кода

После вставки кода нажмите клавиши Alt+Q, чтобы закрыть редактор Visual Basic и вернуться на лист.

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

Множественный выбор в раскрывающемся списке Excel за несколько кликов с помощью Kutools для ExcelРаскрывающийся список

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

После установки Kutools для Excel перейдите на вкладку Kutools, выберите Раскрывающийся список > Создать выпадающий список с множественным выбором и выполните следующие настройки.

1. Укажите диапазон, содержащий раскрывающийся список, из которого нужно выбрать несколько элементов.
2. Укажите разделитель для количества выбранных элементов в ячейке «Раскрывающийся список».
3. Нажмите ОК, чтобы завершить настройку.
   

Результат

Теперь при щелчке по ячейке с раскрывающимся списком в ограниченном диапазоне рядом с ней появится список. Просто нажмите кнопку «+» рядом с нужными элементами, чтобы добавить их в ячейку раскрывающегося списка, или кнопку «–», чтобы удалить ненужные. См. демонстрацию ниже:

Отображение нескольких столбцов в Раскрывающийся список

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

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

Шаг 1: Вставка поля со списком (элемент управления ActiveX)

1. Перейдите на вкладку Разработчик, нажмите Вставить > Поле со списком (элемент управления ActiveX).

Совет: Если вкладка Разработчик не отображается на ленте, следуйте инструкциям из этого руководства «Отображение вкладки Разработчик», чтобы отобразить её.

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

Шаг 2: Изменение свойств поля со списком

1. Щёлкните правой кнопкой мыши поле со списком и выберите в контекстном меню пункт Свойства.

2. В диалоговом окне Свойства выполните следующие настройки.

1) В поле ColumnCountукажите число, соответствующее количеству столбцов, которые необходимо отобразить в Раскрывающийся список;

2) В поле ColumnWidthsзадайте ширину каждого столбца. Здесь ширина каждого столбца установлена как 80 pt;100 pt;80 pt;80 pt;80 pt;

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

4) В поле ListFillRangeвведите Диапазон данных, который необходимо отобразить в Раскрывающийся список.

5) В поле ListWidthукажите ширину всего Раскрывающийся список.

6) Закройте диалоговое окно Свойства.

Шаг 3: Отображение указанных столбцов в Раскрывающийся список

1. На вкладке Разработчик отключите режим Конструктор, просто щёлкнув значок Режим конструктора.

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

Шаг 4: Отображение элементов из других столбцов в определённых ячейках

1. Выберите ячейку под полем со списком, введите приведённую ниже формулу и нажмите клавишу Enter, чтобы получить значение из второго столбца в той же строке.

=IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),"")

2. Чтобы получить значения из третьего, четвёртого и пятого столбцов, последовательно примените следующие формулы.

=IFERROR(VLOOKUP(B1,B3:F6,3,FALSE),"")

=IFERROR(VLOOKUP(B1,B3:F6,4,FALSE),"")

=IFERROR(VLOOKUP(B1,B3:F6,5,FALSE),"")

Связанные статьи