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 코드에서 다음과 같이 범위를 지정할 수 있습니다。

위의 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 팁과 요령 알아보기.

관련 문서