在 Excel 下拉列表中選取多個項目－完整指南

Excel 下拉式清單是確保資料一致性並簡化輸入作業的絕佳工具。然而，預設情況下僅能選取單一項目。若您需要從同一個下拉式清單中選取多個項目，該如何實現？本完整指南將帶您了解如何在 Excel 下拉式清單中啟用多重選取、管理重複項目、設定自訂分隔符號，並定義這些清單的作用範圍。

• 使用 VBA 程式碼
• 透過 Kutools for Excel 點擊幾下即可完成

• 允許重複項目
• 移除所有現有項目
• 設定自訂分隔符號
• 設定限定區域
• 在受保護的工作表中執行

在下拉列表中啟用多重選取

本節提供兩種方法，協助您在 Excel 下拉式清單中啟用多重選取功能。

使用 VBA 程式碼

若要在下拉列表中啟用多重選取功能，可透過 Excel 的「Visual Basic for Applications」（VBA）來實現。此腳本能調整下拉列表的行為，將其轉換為支援多重選擇的清單。請依照下列步驟操作：

步驟 1：開啟工作表（程式碼）編輯器

1. 開啟包含您要啟用多重選擇功能之下拉式清單的工作表。
2. 以滑鼠右鍵按一下工作表標籤，然後從快捷功能表中選取「檢視程式碼」。
   

步驟 2：使用 VBA 程式碼

現在請複製下列 VBA 程式碼，並貼到已開啟的工作表（程式碼）視窗中。

VBA 程式碼：啟用 Excel 下拉式清單的多重選取功能。

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

結果

當您返回工作表時，下拉列表將允許您選擇多個選項，如下方示範所示：

透過 Kutools for Excel 點擊幾下即可完成

若您不熟悉 VBA，更簡單的替代方案是使用「Kutools for Excel」的「使下拉列表可多次選擇」功能。這款使用者友善的工具能輕鬆啟用下拉列表的多重選取功能，讓您無需費力即可自訂分隔符號並靈活管理重複項目，滿足各種需求。

安裝 Kutools for Excel 後，請前往「Kutools」選項卡，點選「下拉列表」>「使下拉列表可多次選擇」，並依下列方式進行設定。

1. 指定包含您要從中選取多個項目的下拉式清單範圍。
2. 指定用於分隔下拉列表儲存格中所選項目數量的符號。
3. 點擊「確定」即可完成設定。
   

結果

現在，當您在限定區域中點選含有下拉列表的儲存格時，旁邊會立即顯示一個清單方塊。只需點擊項目旁的「+」按鈕，即可將其加入下拉儲存格；點擊「——」按鈕，則能輕鬆移除不再需要的項目。效果如下方示範所示：

多重選取下拉列表的更多操作

本節彙整了在資料驗證下拉列表中啟用多重選取時可能遇到的各種情境。

在下拉列表中允許重複項目

當下拉式清單啟用多重選取功能時，重複項目可能引發問題。上述 VBA 程式碼會防止下拉式清單中出現重複項目。若您希望保留重複項目，請嘗試本節所提供的 VBA 程式碼。

VBA 程式碼：在資料驗證下拉列表中允許重複項目

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        Target.Value = xValue1 & delimiter & xValue2
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

結果

現在，您可以從當前工作表的下拉列表中選取多個項目。若要在下拉儲存格中重複某個項目，只需持續從清單中選取該項目即可。請參閱以下截圖：

從下拉列表中移除現有項目

從下拉式清單中選取多個項目後，有時可能需要移除儲存格內的某個既有項目。本節提供另一段 VBA 程式碼，協助您輕鬆完成這項任務。

VBA 程式碼：從下拉列表儲存格中移除任何現有項目

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRngDV As Range
    Dim TargetRange As Range
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String
    Dim allValues As Variant
    Dim valueExists As Boolean
    Dim i As Long
    Dim cleanedValue As String

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Set your desired delimiter here

    If Target.CountLarge > 1 Then Exit Sub

    ' Check if the change is within the specific range
    If Intersect(Target, TargetRange) Is Nothing Then Exit Sub

    On Error Resume Next
    Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If xRngDV Is Nothing Or Target.Value = "" Then
        ' Skip if there's no data validation or if the cell is cleared
        Application.EnableEvents = True
        Exit Sub
    End If
    On Error GoTo 0

    If Not Intersect(Target, xRngDV) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue

        ' Split the old value by delimiter and check if new value already exists
        allValues = Split(oldValue, delimiter)
        valueExists = False
        For i = LBound(allValues) To UBound(allValues)
            If Trim(allValues(i)) = newValue Then
                valueExists = True
                Exit For
            End If
        Next i

        ' Add or remove value based on its existence
        If valueExists Then
            ' Remove the value
            cleanedValue = ""
            For i = LBound(allValues) To UBound(allValues)
                If Trim(allValues(i)) <> newValue Then
                    If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
                    cleanedValue = cleanedValue & Trim(allValues(i))
                End If
            Next i
            Target.Value = cleanedValue
        Else
            ' Add the value
            If oldValue <> "" Then
                Target.Value = oldValue & delimiter & newValue
            Else
                Target.Value = newValue
            End If
        End If

        Application.EnableEvents = True
    End If
End Sub

結果

這段 VBA 程式碼讓您能從下拉清單中輕鬆選取多個項目，並隨時移除已選的項目。只要再次點選清單中的特定項目，即可將其從已選項目中移除。

設定自訂分隔符號

上述 VBA 程式碼預設使用逗號作為分隔符號。您可以將此變數修改為任何偏好的字元，用以下拉式清單中選取項目的分隔符號。操作方式如下：

如您所見，上述所有 VBA 程式碼都包含以下這一行：

delimiter = ", "

您只需將逗號替換為所需的分隔符號即可。例如，若希望以分號分隔項目，請將該行修改為：

delimiter = "; "

delimiter = vbNewLine

設定限定區域

上述 VBA 程式碼適用於目前工作表中的所有下拉式清單。若您僅希望將該程式碼套用至特定範圍的下拉式清單，可依照下列方式，在上述 VBA 程式碼中指定目標範圍。

如您所見，上述所有 VBA 程式碼都包含以下這一行：

Set TargetRange = Me.UsedRange

您只需將該行更改為：

Set TargetRange = Me.Range("C2:C10")

在受保護的工作表中執行

假設您已使用密碼「123」保護工作表，並在啟用保護前將下拉列表儲存格設為「未鎖定」，以確保多重選取功能在保護後仍可正常運作。然而，上述 VBA 程式碼在此情境下無法執行；本節將介紹另一段專為受保護工作表設計的 VBA 腳本，有效支援多重選取功能。

VBA 程式碼：在下拉列表中啟用多重選取（不含重複項目）

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range
    Dim isProtected As Boolean
    Dim pswd As Variant

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    
    ' Check if sheet is protected
    isProtected = Me.ProtectContents
    If isProtected Then
        ' If protected, temporarily unprotect. Adjust or remove the password as needed.
        pswd = "yourPassword" ' Change or remove this as needed
        Me.Unprotect Password:=pswd
    End If

    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        If isProtected Then Me.Protect Password:=pswd
        Exit Sub
    End If
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0

    ' Re-protect the sheet if it was protected
    If isProtected Then
        Me.Protect Password:=pswd
    End If
End Sub

透過在 Excel 下拉列表中啟用多重選取功能，您將大幅強化工作表的功能性與彈性！無論您熟悉 VBA 編寫，或偏好使用 Kutools 等簡易解決方案，都能輕鬆將標準下拉列表升級為動態的多重選取工具。掌握這些技巧後，即可打造更具互動性且使用者友善的 Excel 文件。若您渴望深入探索 Excel 的更多潛能，我們網站提供豐富教學資源，立即探索更多 Excel 秘技！

相關文章