Excel [지식인 해결 시리즈] 008. 콤보박스 활용하여 필터 변경하기

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)

007. 상반기/하반기 매출 집계(SUMIF)

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

 

질문자가 사용하고자 하는 시트

 

문제를 정리하면 다음과 같다.

 1. 데이터가 있는 영역의 B열에는 국가에 대한 데이터가 채워져 있고, AK열에는 날짜에 대한 정보가 채워져 있다.

 2. 이 영역은 필터가 설정되어 있다.

 3. 콤보박스 중에 국가 부분에서 아이템을 선택하면, 필터가 해당국가를 선택한 것으로 변경되어야 한다.

 4. 콤보박스 중에 월을 선택하면, 필터가 ETD열에서  해당월을 선택한 것으로 변경되어야 한다.

 

02. 문제해결 방법

 

이 문제를 해결하기 위해서는 콤보 박스와  AutoFilter에 대한 지식이 필요하다.

 

다음 포스트들을 참고하도록 하자.

 - 콤보박스 관련

https://eggdrop.tistory.com/22

Excel 매크로 (VBA) - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

 - AutoFilter 관련

https://eggdrop.tistory.com/25

Excel 매크로 (VBA) - 013. 자동필터(Auto Filter)

 

1) 콤보박스에 국가 부분 채우기

국가에 해당하는 콤보박스를 편의상 콤보박스(국가)로 부르겠다. 

콤보 박스(국가)에는 B열에 있는 국가 정보를 중복되는 것은 제거한 상태로 담아야 한다.

 

데이터 중에서 중복되지 않는 데이터들을 추출하는 것은 예전에 다룬적이 있다.

https://eggdrop.tistory.com/10?category=1087789 

Excel 매크로 (VBA) - 005. 시트에 있는 데이터(셀) 순회하기

대략적인 논리의 흐름은 다음과 같다.

 1) 국가 데이터를 저장할 스트링 배열을 선언한다.

 2) B열을 순회하며 배열에 B열에서 추출한 국가 데이터가 있는지 확인한다.

 3) 배열에 해당 국가 데이터가 없는 경우, 배열에 국가 데이터를 할당한다.

 4) 배열에 해당 국가 데이터가 있는 경우, B열의 다음행으로 이동하여 2)의 작업을 수행한다.

 5) B열의 모든 행에 대해 2)~4)의 과정을 반복한다.

 

자세한 코드는 예제 부분에서 소개하겠다.

 

 

2) 콤보박스에 월 채우기

월에 해당하는 콤보박스를 편의상 콤보박스(월)로 부르겠다.

월은 1월에서 12월까지 이므로, 고정적으로 1월에서 12월을 채워준다.

 

3) 초기화 버튼 추가하기

초기화 버튼을 하나 추가하여, 버튼을 눌렀을 때 콤보박스(국가)와 콤보박스(월)의 내용을 초기화하는 작업을 수행해보자.

초기화 버튼 추가

추가한 버튼의 캡션(화면에 보이는 텍스트)을 "초기화"로 변경한 후에 이벤트 핸들러를 연결해보자.

 

버튼은 엑셀 폼 컨트롤이므로 우클릭 후에 매크로 지정을 통해 이벤트 핸들러를 연결할 수 있다.

 

 

모듈이 하나 추가되면서 단추 클릭 시 처리할 루틴을 지정할 수 있는 Sub 프로시저가 하나 추가되었을 것이다. 질문자가 남긴 엑셀에서는 "단추212_Click"이다. 해당 부분에 초기화 코드를 넣어주자.

 

4) 콤보박스에 이벤트 핸들러 연결하기

콤보박스(국가), 콤보박스(달)도 우클릭 후에 이벤트 핸들러을 연결해보자. 질문자의 엑셀파일에서는 해당 콤보박스가 엑셀폼 콤보박스이다.  콤보박스 부분이 궁금하면 다음 링크를 참고하자.

https://eggdrop.tistory.com/22

Excel 매크로 (VBA) - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

 

콤보박스(국가)의 경우 Dropdown01_select,  콤보박스(달)의 경우 Dropdown02_select로 핸들러를 지정했다.

 

03. 예제 및 설명

 

이제 동작할 수 있게 코드를 작성해보자.

 

(1) 초기화

일단 콤보박스의 내용을 채워주는 초기화 버튼을 클릭했을 때의 처리루틴부터 살펴보자

 

Sub 단추212_Click()
    '필터를 제거한다.     If ActiveSheet.AutoFilterMode Then         ActiveSheet.AutoFilterMode = False     End If
    '국가 콤보박스 초기화 시작
    '국가 열을 B열
    Dim nationCol As Long
    nationCol = 2
    
    
    'B열의 데이터는 5행부터 시작한다. (5행은 머리글행)
    Dim startRow As Long
    startRow = 5
    
    'B열의 데이터가 어디까지 있는지 확인한다.
    Dim endRow As Long
    endRow = ActiveSheet.Cells(Rows.Count, nationCol).End(xlUp).Row
    
    '국가 정보가 있는 영역을 지정하기 위한 Range 객체 선언
    Dim nationInfoRange As Range
    
    '국가 정보를 저장하기 위한 배열 선언
    Dim nationInfo() As String
    
    Dim numOfNationInfos As Long
    numOfNationInfos = 0
    arraySize = endRow - startRow
    If arraySize < 1 Then
    Else
        '국가 정보 저장하기 위한 배열 크기 설정
        ReDim nationInfo(1 To arraySize)
        '국가 정보가 있는 셀 영역을 nationInfoRange 변수에 지정
        Set nationInfoRange = ActiveSheet.Range(Cells(startRow + 1, nationCol), Cells(endRow, nationCol))
                      
        '국가 정보가 있는 모든 셀을 순회하며 국가정보 저장
        For Each Item In nationInfoRange.Cells
            '중복 데이터 확인을 위한 체커 초기화
            checker = False
            '배열에 입력된 국가의 수 만큼 루프를 돌며 중복데이터 확인
            For i = 1 To numOfNationInfos
                '이미 배열에 입력된 국가의 경우 체커를 True로 변경
                If Item.Value = nationInfo(i) Then
                    checker = True
                    Exit For
                End If
            Next i
            '이미 배령레 입력된 국가가 없는 경우 해당 국가를 배열에 입력
            If checker = False Then
                '배열 중 실제 사용하는 데이터의 크기를 나타내는 변수를 1증가
                numOfNationInfos = numOfNationInfos + 1
                '배열의 마지막 인덱스에 국가명 저장
                nationInfo(numOfNationInfos) = Item
            End If
        Next Item
        
        '콤보박스(국가)에 배열에 있는 데이터를 삽입한다.
            ActiveSheet.Shapes("드롭다운 56").ControlFormat.RemoveAllItems
        For i = 1 To numOfNationInfos
            ActiveSheet.Shapes("드롭다운 56").ControlFormat.AddItem nationInfo(i)
        Next i
        '콤보박스(달)을 초기화 한후에, 달(1~12)를 삽입한다.
            ActiveSheet.Shapes("드롭다운 57").ControlFormat.RemoveAllItems
        For i = 1 To 12
            ActiveSheet.Shapes("드롭다운 57").ControlFormat.AddItem i
        Next i
        
    End If
    
End Sub

코드가 길어 보이지만 라인 33까지는 데이터가 있는 영역의 크기를 찾는 부분이고, 라인36부터 54까지는 배열을 이용하여 중복 데이터 없이 국가명을 추출하는 부분이다.

 

즉 위에서 설명한 다음 논리의 흐름대로 진행된다.

 1) 국가 데이터를 저장할 스트링 배열을 선언한다.

 2) B열을 순회하며 배열에 B열에서 추출한 국가 데이터가 있는지 확인한다.

 3) 배열에 해당 국가 데이터가 없는 경우, 배열에 국가 데이터를 할당한다.

 4) 배열에 해당 국가 데이터가 있는 경우, B열의 다음행으로 이동하여 2)의 작업을 수행한다.

 5) B열의 모든 행에 대해 2)~4)의 과정을 반복한다.

 

실제 콤보박스에 데이터를 넣는 부분은 57~65라인까지이며, 배열을 이용하여 추출한 국가 정보와 1~12까지의 달 정보를 콤보박스에 넣게 된다.

 

초기화 버튼을 눌러서 위의 프로시저를 실행하면 콤보박스가 다음 그림처럼 초기화된다.

 

 

(2) 필터를 적용할 데이터 영역을 구하는 함수

콤보박스 처리루틴을 설명하기 전에, 두개의 콤보박스 처리루틴에서 공통적으로 사용될 수 있는 필터를 적용할 데이터 영역을 구하는 함수를 먼저 정의해 보자.

 

Function getFilterRange() As Range
 
    '데이터는 B열부터 AL열까지 있다.
    Dim nationCol As Long
    nationCol = 2
    Dim endCol As Long
    endCol = 38
    'B열의 데이터는 5행부터 시작한다. (5행은 머리글행)
    Dim startRow As Long
    startRow = 5
    'B열의 데이터가 어디까지 있는지 확인한다.
    Dim endRow As Long
    endRow = ActiveSheet.Cells(Rows.Count, nationCol).End(xlUp).Row
         
    Set getFilterRange = ActiveSheet.Range(Cells(startRow, nationCol), Cells(endRow, endCol))
 
End Function

 

데이터가 B열에서 AL열 까지 있으므로 2열~33열까지의 영역을 선택하여 반환하는 함수를 작성했다.

 

(3) 콤보박스(국가)의 데이터 선택 시 처리 루틴

다음은 콤보박스(국가)에서 데이터 선택 시의 처리 루틴이다. 

위의 함수에서 구한 데이터 영역 중에서 AutoFilter 를 B열에  적용하되, Criteria를 콤보박스에서 선택한 국가로 지정한다.

또한 콤보박스(달)을 선택되지 않은 상태로 초기화한다.

 

Sub Dropdown01_select()
    
    '필터를 제거한다.
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
 
    '콤보박스의 ControlFormat 를 변수에 할당
    Set cf = ActiveSheet.Shapes("드롭다운 56").ControlFormat
    '선택된 국가를 가져온다.
    selectedData = cf.List(cf.ListIndex)
    
    '콤보박스(달)의 상태를 미선택 상태로 변경한다.
    '콤보박스(달)의 ControlFormat 를 변수에 할당
    Set cfMonth = ActiveSheet.Shapes("드롭다운 57").ControlFormat
    cfMonth.ListIndex = 0
    
    Dim nationInfoRange As Range
    Set nationInfoRange = getFilterRange()
    'nationInfoRange.Select
    nationInfoRange.AutoFilter _
                Field:=1, _
                Criteria1:=selectedData
                
    
End Sub

 

(4) 콤보박스(달)의 데이터 선택 시 처리 루틴

다음은 콤보박스(달)에서 데이터 선택 시의 처리 루틴이다.

달을 적용할 열은 36번째 열이다.

 

콤보박스(국가)가 선택된 상태라면 해당 필터까지 함께 적용할 필요가 있다.

 

또한 36번째 열은 날짜로 되어 있기 때문에 콤보박스에 있는 숫자를 Criteria로 그대로 사용할 경우 원하는 결과를 얻을 수 없다.

 

이번에는 xlFilterDynamic 오퍼레이터를 사용하여, 특정 달에 해당하는 날짜에만 필터를 적용하는 걸 소개하려고 한다.

이전 포스트에서 표로만 살짝 다룬적이 있는데 이번에 자세히 한번 다뤄보자

https://eggdrop.tistory.com/25

Excel 매크로 (VBA) - 013. 자동필터(Auto Filter)

 

 

코드는 다음과 같다.

Sub Dropdown02_select()
    
    '필터를 제거한다.
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
    
    Dim selectedData As String
    selectedData = ""
    '콤보박스(국가)의 ControlFormat 를 변수에 할당
    Set cf = ActiveSheet.Shapes("드롭다운 56").ControlFormat
    '선택된 국가를 가져온다.
    If cf.ListIndex <> 0 Then
        selectedData = cf.List(cf.ListIndex)
    End If
    
    '콤보박스(달)의 ControlFormat 를 변수에 할당
    Set cfMonth = ActiveSheet.Shapes("드롭다운 57").ControlFormat
    '선택된 달을 가져온다.
    myMonth = cfMonth.ListIndex
    
    Dim arrayMonthCriteria(1 To 12)
    arrayMonthCriteria(1) = xlFilterAllDatesInPeriodJanuary
    arrayMonthCriteria(2) = xlFilterAllDatesInPeriodFebruray
    arrayMonthCriteria(3) = xlFilterAllDatesInPeriodMarch
    arrayMonthCriteria(4) = xlFilterAllDatesInPeriodApril
    arrayMonthCriteria(5) = xlFilterAllDatesInPeriodMay
    arrayMonthCriteria(6) = xlFilterAllDatesInPeriodJune
    arrayMonthCriteria(7) = xlFilterAllDatesInPeriodJuly
    arrayMonthCriteria(8) = xlFilterAllDatesInPeriodAugust
    arrayMonthCriteria(9) = xlFilterAllDatesInPeriodSeptember
    arrayMonthCriteria(10) = xlFilterAllDatesInPeriodOctober
    arrayMonthCriteria(11) = xlFilterAllDatesInPeriodNovember
    arrayMonthCriteria(12) = xlFilterAllDatesInPeriodDecember
    
    '필터 영역을 지정하기 위한 Range 객체 선언
    Dim nationInfoRange As Range
    Set nationInfoRange = getFilterRange()
    
    If selectedData <> "" Then
        nationInfoRange.AutoFilter _
                    Field:=1, _
                    Criteria1:=selectedData
    End If
    
    nationInfoRange.AutoFilter _
              Field:=36, _
              Criteria1:=arrayMonthCriteria(myMonth), _
              Operator:=xlFilterDynamic
 
 
End Sub

AutoFilter 사용 시에 Operator를 xlFilterDynamic으로 지정하면, 라인23~라인34에 있는 열거형 변수를 Filter의 Criteria로 사용할 수 있다. 해당 값을 넣는 경우 특정 달의 데이터만 추출이 가능하다.

 

본 예제에서는 해당 열거형 변수를 배열에 넣어 처리했다.

 

라인 40에서 콤보박스(국가)에서 선태한 데이터가 없다면 국가에 대한 필터를 적용하지 않고, 선택한 데이터가 있는 경우에만 국가 데이터를 이용한 필터를 적용한다.

 

(5) 실행 예제 1

콤보박스(국가)에서 스웨덴을 선택한 경우

 

 

(6) 실행 예제 2

 

콤보박스(국가)에서 스웨덴을 선택한 상태에서, 월을 3으로 선택한 경우

 

질문자가 의도한 대로 동작하는 것을 확인할 수 있다.