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 프로시저를 하나 추가하자.
|
1
2
3
|
Sub 필터유무()
MsgBox (ActiveSheet.AutoFilterMode)
End Sub
|
cs |
위 매크로를 실행하면, 해당 시트에 필터가 적용되어 있을 경우 True를, 필터가 적용되어 있지 않은 경우 False를 출력하는 메시지 박스를 표시한다.
아무런 조건이 없는 필터 준비 상태도 피터 적용 상태로 간주된다는 것을 기억하자
03. 필터 해제
필터 적용 유무를 조금만 응용해서 필터를 해제하는 매크로를 작성해보자.
AutoFilterMode = False를 입력하면 필터를 해제할 수 있다.
|
1
2
3
4
5
6
7
|
Sub 필터해제()
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
End Sub
|
cs |
아무 필터나 걸려 있는 상태에서 위의 매크로를 실행하면 다음과 같이 필터가 풀리는 것을 확인할 수 있다.
 필터(소속: 빅 맘 해적단) |
 매크로 실행 후 |
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세인 경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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
|
cs |
03: 필터해제 Sub 프로시저를 호출하여, 이미 적용된 필터가 있다면 해제한다.
05-08: 데이터가 있는 영역을 선택하여, mySection이라는 Range 객체에 지정한다.
10-12: 1번 필터, 1번째 열을 기준으로 값이 "밀짚모자 일당" 인 데이터 추출
14-16: 2번 필터, 5번째 열을 기준으로 값이 "19"인 데이터 추출
*Visual Basic은 스크립트 언어이기 때문에 에디터 상에서 엔터를 다른 라인으로 인식한다. 같은 명령문을 여러줄로 쓰고 싶은 경우 마지막에 "_"를 이용하면 하나의 문장으로 인식된다.
결과는 다음과 같다.
(2) 필터2: 나이가 19세 또는 20세 인경우
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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
|
cs |
10-14: 나이가 19 세 또는 20세 인 데이터만 추출(같은 필드에 여러 조건(Criteria) 적용에 대한 예제)
결과는 다음과 같다.
(3) 필터3: 현상금 상위 10명
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
cs |
10-12: Operator에 xlTop10Itmes을 지정하여 6번째 필드(현상금) 상위 10개 데이터만 추출한다.
결과는 다음과 같다.
(4) 필터4: 현상금 평균 이상
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
|
cs |
10-14: Operator에 xlFilterDynamic을 지정하고, Criteria1에 xlFilterAboveAverage (평균 이상)을 선택하여, 현상금이 평균값(약14.3억)을 넘는 데이터만 표기한다.
결과는 다음과 같다.
