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 팁과 요령을 알아보세요.

관련 기사