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

전체 목차

 - 001. 특정 행을 복사하여 삽입하기

 - 002. 선택하여 붙여 넣기 기능 정리

 - 003. 변수/Assignment/Loop/비교

 - 004. 데이터가 있는 셀의 범위 알아내기

 - 005. 데이터 시트(쉘) 순회하기

 - 006. 셀 병합 하기

 - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능) 

 - 008. 정렬하기 (Sort)

 - 009. 버튼 컨트롤 (Excel Form vs. ActiveX)

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

 - 011. 확인란/옵션단추 컨트롤(Excel Form vs. ActiveX)

 - 012. 스핀단추 컨트롤(스피너, Excel Form vs. ActiveX)

 

포스트 목차.

 

01. 엑셀에서의 자동 필터

02. 필터 적용 유무 확인 

03. 필터 해제

04. 필터 적용 방법

05. 예제 및 설명

 

본 포스트에서는 은근히 사용하는 사람이 많은 엑셀의 자동필터 기능에 대해 다루려고 한다. 필터는 말 그대로 사용자가 원하는 기준으로 데이터를 걸러내는 기능을 의미한다.

 

01. 엑셀에서의 필터

 

데이터 선택 >> 메뉴의 데이터>> 필터를 이용하여 특정 영역에 대한 필터 준비 상태로 만들 수 있다.

 

필터 준비 상태에서 제목 행의 세모 버튼을 클릭 하면 필터에 필요한 조건을 입력할 수 있고, 그 이후 필터를 적용할 수 있다.

 

예를 들어, 위의 시트에서 소속에 있는 세모 버튼을 클릭하면 다음과 같은 창이 뜬다.

 

여기서 밀짚모자 일당만 선택하고 싶다면 다른 체크박스를 해제하고 확인을 누르면 된다.

 

소속이 밀짚모자 일당이 아닌 데이터들은 숨김 처리되며, 소속 옆에 있는 세모버튼이 필터 모양 아이콘으로 변경된 것을 확인할 수 있다.

 

필터를 해제하고 싶다면, 필터 버튼을 한 번 클릭하면 된다.

 

 

02. 필터 적용 유무 확인 

 

현재 시트에 필터가 적용되어있는지를 알아보는 스크립트부터 작성해보자. 

모듈 및 Sub 프로시저를 하나 추가하자.

 

Sub 필터유무()
   MsgBox (ActiveSheet.AutoFilterMode)
End Sub

 

위 매크로를 실행하면, 해당 시트에 필터가 적용되어 있을 경우 True를, 필터가 적용되어 있지 않은 경우 False를 출력하는 메시지 박스를 표시한다.

 

아무런 조건이 없는 필터 준비 상태도 피터 적용 상태로 간주된다는 것을 기억하자 

 

03. 필터 해제

 

필터 적용 유무를 조금만 응용해서 필터를 해제하는 매크로를 작성해보자.

AutoFilterMode = False를 입력하면 필터를 해제할 수 있다.

 

Sub 필터해제()
 
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
 
End Sub

 

아무 필터나 걸려 있는 상태에서 위의 매크로를 실행하면 다음과 같이 필터가 풀리는 것을 확인할 수 있다.

필터(소속: 빅 맘 해적단)

매크로 실행 후

 

04. 필터 적용 방법

 

이제 필터를 적용하는 방법에 대해서 알아보자.

 

Range객체의 AutoFilter 메소드를 통해 필터를 적용할 수 있다.

AutoFilter 메소드는 다음과같은 파라미터들을 제공한다.

 

AutoFilter의 파라미터 (모두 생략가능)

파리미터명	설명
Field	필터의 대상이되는 열의 번호
Criteria1	걸러내고자하는 기준값1, 다음과 같은 특수값 허용( "=" 값이 공백인 경우, "<>" 값이 공백이 아닌 경우, "><" (No Data) 생략될 경우, 모든 데이터를 선택하는 것과 같다.
Operator	열겨형 XlAutoFilterOperaotr에 자세히 설명
Criteria2	걸러내고자하는 기준값2
SubField	Ofiice365에서 제공하는 Stock/GeoGraphy 데이터 타입
VisibleDropDown	제목 필드에 세모 버튼을 표기할지 유무

열거형 XlAutoFilterOperator

이름	값	설명
xlAnd	1	Criteria1 과 Criteria2에 대한논리적 AND 연산 결과
xlOr	2	Criteria1 과 Criteria2에 대한논리적 OR 연산 결과
xlTop10Items	3	상위 10 개 아이템
xlBottom10Items	4	하위 10 개 아이템
xlTop10Percent	5	상위 10 퍼센트
xlBottom10Percent	6	하위 10 퍼센트
xlFilterValues	7	값에 대한 필터
xlFilterCellColor	8	셀의 색깔에 대한 필터
xlFilterFontColor	9	글자색에 대한 필터
xlFilterIcon	10	아이콘에 대한 필터
xlFilterDynamic	11	다이나믹 필터

Operator를 xlFilterDynamic으로 지정할 경우, Criteria1에 다음과 같은 열거형들을 사용할 수 있다. VBA에서 모두 지원하는 확인해보겠다.

열거형	값	의미
xlFilterAboveAverage	33	Filter all above-average values.
xlFilterAllDatesInPeriodApril	24	Filter all dates in April.
xlFilterAllDatesInPeriodAugust	28	Filter all dates in August.
xlFilterAllDatesInPeriodDecember	32	Filter all dates in December.
xlFilterAllDatesInPeriodFebruray	22	Filter all dates in February.
xlFilterAllDatesInPeriodJanuary	21	Filter all dates in January.
xlFilterAllDatesInPeriodJuly	27	Filter all dates in July.
xlFilterAllDatesInPeriodJune	26	Filter all dates in June.
xlFilterAllDatesInPeriodMarch	23	Filter all dates in March.
xlFilterAllDatesInPeriodMay	25	Filter all dates in May.
xlFilterAllDatesInPeriodNovember	31	Filter all dates in November.
xlFilterAllDatesInPeriodOctober	30	Filter all dates in October.
xlFilterAllDatesInPeriodQuarter1	17	Filter all dates in Quarter1.
xlFilterAllDatesInPeriodQuarter2	18	Filter all dates in Quarter2.
xlFilterAllDatesInPeriodQuarter3	19	Filter all dates in Quarter3.
xlFilterAllDatesInPeriodQuarter4	20	Filter all dates in Quarter4.
xlFilterAllDatesInPeriodSeptember	29	Filter all dates in September.
xlFilterBelowAverage	34	Filter all below-average values.
xlFilterLastMonth	8	Filter all values related to last month.
xlFilterLastQuarter	11	Filter all values related to last quarter.
xlFilterLastWeek	5	Filter all values related to last week.
xlFilterLastYear	14	Filter all values related to last year.
xlFilterNextMonth	9	Filter all values related to next month.
xlFilterNextQuarter	12	Filter all values related to next quarter.
xlFilterNextWeek	6	Filter all values related to next week.
xlFilterNextYear	15	Filter all values related to next year.
xlFilterThisMonth	7	Filter all values related to the current month.
xlFilterThisQuarter	10	Filter all values related to the current quarter.
xlFilterThisWeek	4	Filter all values related to the current week.
xlFilterThisYear	13	Filter all values related to the current year.
xlFilterToday	1	Filter all values related to the current date.
xlFilterTomorrow	3	Filter all values related to tomorrow.
xlFilterYearToDate	16	Filter all values from today until a year ago.
xlFilterYesterday	2	Filter all values related to yesterday.

 

05. 예제 및 설명

 

위에서 작성한 "필터해제" Sub 프로시저를 작성한 상태에서 

 

다음과 같은 4개의 필터를 매크로로 작성해보자.

 

(1) 필터1: 소속이 "밀짚모자 일당" 이고, 나이가 19세인 경우

Sub 자동필터1()
 
   Call 필터해제
 
   Dim mySection As Range
   rowEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   colEnd = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
   Set mySection = ActiveSheet.Range(Cells(1, 1), Cells(rowEnd, colEnd))
   
   mySection.AutoFilter _
                Field:=1, _
                Criteria1:="밀짚모자 일당"
                
   mySection.AutoFilter _
                Field:=5, _
                Criteria1:="19"
    
End Sub

03: 필터해제 Sub 프로시저를 호출하여, 이미 적용된 필터가 있다면 해제한다.

05-08: 데이터가 있는 영역을 선택하여, mySection이라는 Range 객체에 지정한다.

10-12: 1번 필터, 1번째 열을 기준으로 값이 "밀짚모자 일당" 인 데이터 추출

14-16: 2번 필터, 5번째 열을 기준으로 값이 "19"인 데이터 추출

*Visual Basic은 스크립트 언어이기 때문에 에디터 상에서 엔터를 다른 라인으로 인식한다. 같은 명령문을 여러줄로 쓰고 싶은 경우 마지막에 "_"를 이용하면 하나의 문장으로 인식된다.

 

결과는 다음과 같다.

 

(2) 필터2: 나이가 19세 또는 20세 인경우

Sub 자동필터2()
 
   Call 필터해제
    
   Dim mySection As Range
   rowEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   colEnd = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
   Set mySection = ActiveSheet.Range(Cells(1, 1), Cells(rowEnd, colEnd))
 
   mySection.AutoFilter _
            Field:=5, _
            Criteria1:=19, _
            Criteria2:=20, _
            Operator:=xlOr
 
End Sub

(3) 필터3: 현상금 상위 10명

Sub 자동필터3()
   
   Call 필터해제
    
   Dim mySection As Range
   rowEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   colEnd = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
   Set mySection = ActiveSheet.Range(Cells(1, 1), Cells(rowEnd, colEnd))
 
   mySection.AutoFilter _
            Field:=6, _
            Operator:=xlTop10Items
 
End Sub

10-12: Operator에 xlTop10Itmes을 지정하여 6번째 필드(현상금) 상위 10개 데이터만 추출한다.

 

결과는 다음과 같다.

 

(4) 필터4: 현상금 평균 이상

 

Sub 자동필터4()
   
   Call 필터해제
    
   Dim mySection As Range
   rowEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   colEnd = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
   Set mySection = ActiveSheet.Range(Cells(1, 1), Cells(rowEnd, colEnd))
 
   mySection.AutoFilter _
            Field:=6, _
            Criteria1:=xlFilterAboveAverage, _
            Operator:=xlFilterDynamic
 
End Sub

10-14: Operator에 xlFilterDynamic을 지정하고, Criteria1에 xlFilterAboveAverage (평균 이상)을 선택하여, 현상금이 평균값(약14.3억)을 넘는 데이터만 표기한다.

 

결과는 다음과 같다.