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

Когда вы ищете «excel drop down list multiple columns» в 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 выше показывает только шаги для создания2-уровневого раскрывающегося списка. Если вы хотите создать список с более чем2 уровнями, нажмите здесь, чтобы узнать больше . Или скачайте бесплатную30-дневную пробную версию.

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

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

Использование VBA-кода для выбора нескольких значений в раскрывающемся списке Excel

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

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

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

2. Откроется окно Microsoft Visual Basic for Applications, скопируйте следующий VBA-код в редактор Sheet (Code).

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 Editor и вернуться к листу.

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

Несколько кликов для выбора нескольких значений в раскрывающемся списке Excel с помощью Kutools для Excel

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

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

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

Результат

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

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

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

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

Шаг1: Вставьте Комбинированный список (ActiveX Control)

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

Совет: Если вкладка Разработчик не отображается на ленте, выполните шаги из этого руководства «Показать вкладку Разработчик», чтобы включить её.

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),"")

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