Один из моих коллег спросил меня, можно ли в Excel сделать множественный выбор в раскрывающемся списке.
Когда вы создаете раскрывающийся список, вы можете сделать только один выбор. Если вы выберете другой элемент, первый будет заменен новым выбором.
Он хотел сделать множественный выбор из одного и того же раскрывающегося списка таким образом, чтобы выбор добавлялся к уже имеющемуся значению в ячейке.
Что-то вроде того, что показано на картинке ниже:
Вы не можете сделать это с помощью встроенных функций Excel.
Единственный способ - использовать код VBA, который запускается всякий раз, когда вы делаете выбор, и добавляет выбранное значение к существующему значению.
Посмотреть видео - Как выбрать несколько элементов из раскрывающегося списка Excel
Как сделать множественный выбор в раскрывающемся списке
В этом руководстве я покажу вам, как сделать множественный выбор в раскрывающемся списке Excel (с повторением и без повторения).
Это один из самых популярных руководств по Excel на этом сайте. Поскольку у меня много похожих вопросов, я решил создать раздел часто задаваемых вопросов в конце этого руководства. Так что если после прочтения у вас возникнут какие-либо вопросы, пожалуйста, сначала ознакомьтесь с разделом часто задаваемых вопросов.Создание раскрывающегося списка, позволяющего выбрать несколько вариантов, состоит из двух частей:
- Создание выпадающего списка.
- Добавление кода VBA в серверную часть.
Создание раскрывающегося списка в Excel
Вот шаги для создания раскрывающегося списка в Excel:
- Выберите ячейку или диапазон ячеек, в которых должен отображаться раскрывающийся список (C2 в этом примере).
- Перейдите в Data -> Data Tools -> Data Validation.
- В диалоговом окне «Проверка данных» на вкладке настроек выберите «Список» в качестве критериев проверки.
- В поле «Источник» выберите ячейки, содержащие нужные вам элементы, в раскрывающемся списке.
- Щелкните ОК.
Теперь в ячейке C2 есть раскрывающийся список, который показывает имена элементов в A2: A6.
На данный момент у нас есть раскрывающийся список, в котором вы можете выбирать по одному элементу за раз (как показано ниже).
Чтобы включить это раскрывающееся меню, чтобы мы могли сделать несколько вариантов выбора, нам нужно добавить код VBA в серверную часть.
Следующие два раздела этого руководства дадут вам код VBA, позволяющий выбрать несколько вариантов в раскрывающемся списке (с повторением и без повторения).
Код VBA для разрешения множественного выбора в раскрывающемся списке (с повторением)
Ниже приведен код Excel VBA, который позволит нам выбрать более одного элемента из раскрывающегося списка (с возможностью повторения выбора):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Чтобы сделать несколько вариантов выбора в раскрывающемся списке в Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Тогда, если Target.SpecialCells (xlCellTypeAllValidation) - ничего, тогда GoTo Exitsub Else: If Target.Value = "" Тогда GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value Если Oldvalue = "" Тогда Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Теперь вам нужно поместить этот код в модуль редактора VB (как показано ниже в разделе «Куда поместить код VBA»).
Когда вы поместите этот код в серверную часть (рассматривается позже в этом руководстве), он позволит вам сделать несколько вариантов выбора в раскрывающемся списке (как показано ниже).
Обратите внимание, что если вы выберете элемент более одного раза, он будет введен снова (повторение разрешено).
Попробуйте сами… Загрузите файл примера
Код VBA для разрешения множественного выбора в раскрывающемся списке (без повторения)
Многие люди спрашивают о коде для выбора нескольких элементов из раскрывающегося списка без повторения.
Вот код, который гарантирует, что элемент можно выбрать только один раз, чтобы не было повторений:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Чтобы разрешить множественный выбор в раскрывающемся списке в Excel (без повторения) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = Истина при ошибке GoTo Exitsub If Target.Address = "$ C $ 2" Тогда Если Target.SpecialCells (xlCellTypeAllValidation) не имеет значения, тогда GoTo Exitsub Else: Если Target.Value = "" Тогда GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Значение Application.Undo Oldvalue = Target.Value If Oldvalue = "" Тогда Target.Value = Newvalue Else If InStr (1, Oldvalue, Newvalue) = 0 Тогда Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Теперь вам нужно поместить этот код в модуль в редакторе VB (как показано в следующем разделе этого руководства).
Этот код позволит вам выбрать несколько элементов из раскрывающегося списка. Однако вы сможете выбрать элемент только один раз. Если вы попытаетесь выбрать его снова, ничего не произойдет (как показано ниже).
Попробуйте сами… Загрузите файл примера
Куда поместить код VBA
Прежде чем вы начнете использовать этот код в Excel, вам нужно поместить его в серверную часть, чтобы он запускался всякий раз, когда в раскрывающемся списке есть какие-либо изменения.
Выполните следующие шаги, чтобы поместить код VBA в серверную часть Excel:
- Перейдите на вкладку разработчика и нажмите Visual Basic (вы также можете использовать сочетание клавиш - Alt + F11). Откроется редактор Visual Basic.
- Слева должна быть панель Project Explorer (если ее нет, используйте Control + R, чтобы сделать ее видимой).
- Дважды щелкните имя рабочего листа (на левой панели), где находится раскрывающийся список. Это открывает окно кода для этого рабочего листа.
- В окне кода скопируйте и вставьте приведенный выше код.
- Закройте редактор VB.
Теперь, когда вы вернетесь в раскрывающийся список и сделаете выбор, это позволит вам сделать несколько вариантов (как показано ниже):
Попробуйте сами… Загрузите файл примера
Примечание: Поскольку для этого мы используем код VBA, вам необходимо сохранить книгу с расширением .xls или .xlsm.
Часто задаваемые вопросы (FAQ)
Я создал этот раздел, чтобы ответить на некоторые из наиболее часто задаваемых вопросов об этом руководстве и коде VBA. Если у вас есть какие-либо вопросы, я прошу вас сначала просмотреть этот список запросов.
В: В коде VBA функциональность предназначена только для ячейки C2. Как мне получить его для других ячеек? Ответ: Чтобы получить это раскрывающееся меню с множественным выбором в других ячейках, вам необходимо изменить код VBA в серверной части. Предположим, вы хотите получить это для C2, C3 и C4, вам нужно заменить следующую строку в коде: If Target.Address = "$ C $ 2", то на эту строку: If Target.Address = "$ C $ 2" Или Target.Address = "$ C $ 3" Или Target.Address = "$ C $ 4" Тогда
В: Мне нужно создать несколько раскрывающихся списков во всем столбце «C». Как мне получить это для всех ячеек в столбцах с функцией множественного выбора? Ответ: Чтобы включить множественный выбор в раскрывающихся списках во всем столбце, замените в коде следующую строку: If Target.Address = "$ C $ 2" Then этой строкой: If Target.Column = 3 Then On аналогичных строках, если если вам нужна эта функция в столбцах C и D, используйте следующую строку: Если Target.Column = 3 или Target.Column = 4 Then
В: Мне нужно создать несколько раскрывающихся списков подряд. Как я могу это сделать? Ответ: Если вам нужно создать раскрывающиеся списки с несколькими вариантами выбора в строке (скажем, вторая строка), вам необходимо заменить следующую строку кода: Если Target.Address = "$ C $ 2" Затем этой строкой: Если Target.Row = 2 Then Аналогично, если вы хотите, чтобы это работало для нескольких строк (скажем, второй и третьей строки), используйте вместо этого следующую строку кода: Если Target.Row = 2 или Target.Row = 3 Then
В: На данный момент множественный выбор разделяется запятой. Как я могу изменить это, чтобы разделить их пробелом (или любым другим разделителем). Ответ: Чтобы разделить их разделителем, отличным от запятой, вам необходимо заменить следующую строку кода VBA: Target.Value = Oldvalue & "," & Newvalue этой строкой кода VBA: Target.Value = Oldvalue & "" & Newvalue Аналогично, если вы хотите заменить запятую другим символом, например |, вы можете использовать следующую строку кода: Target.Value = Oldvalue & "|" & Newvalue
В: Могу ли я выделить каждый выбор в отдельной строке в той же ячейке? Ответ: Да, можно. Чтобы получить это, вам необходимо заменить следующую строку кода VBA: Target.Value = Oldvalue & "," & Newvalue на эту строку кода: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine вставляет новую строку в ту же ячейку . Поэтому всякий раз, когда вы делаете выбор из раскрывающегося списка, он будет вставлен в новую строку.
В: Могу ли я сделать так, чтобы функция множественного выбора работала на защищенном листе? Ответ: Да, можно. Для этого вам нужно сделать две вещи: добавить в код следующую строку (сразу после оператора DIM): Me.Protect UserInterfaceOnly: = True Во-вторых, вам необходимо убедиться, что ячейки, в которых есть раскрывающийся список с функцией множественного выбора, не заблокированы, когда вы защищаете весь лист. Вот руководство о том, как это сделать: Блокировка ячеек в Excel
