들어가며
엑셀에는 특정 범위 대상으로 원하는 조건에 맞는 데이터를 추출할 수 있는 기능을 제공하고 있습니다. 이런 기능을 '필터'라고하며 간단하고 쉽게 적용할 수 있는 '자동 필터'와 복잡하지만 세부적이면서 여러 조건을 지정할 수 있는 '고급 필터'가 있습니다.
이번 포스트에서는 '고급 필터'가 무엇인지, 엑셀에서 고급 필터를 사용하기 위해서는 어떻게 해야하는지 살짝 살펴 본 후, VBA 스크립트를 이용해 고급 필터를 사용하는 법에 대해 중점적으로 알아 보도록하겠습니다.
고급 필터 퀵 가이드
| Task | Cell formula | Examples where true |
| 특정 단어 포함하는 문자열 선택 | Pea ="Pea" ="*Pea*" |
Peach, Pea, Appear |
| 특정 단어 포함 하지 않는 문자열 선택 | ="<>*Pea*" | Pea를 포함하지 않는 모든 것들 |
| 정확하게 매칭 되는 문자열 선택 | ="=Pea" | Pea |
| 정확하게 매칭 되지 않는 모든 문자열 선택 | ="<>Pea" | Peach, Pear 등등 |
| 특정 단어로 시작하는 문자열 선택 | ="=Pea*" | Peach, Pear, Pea |
| 특정 단어로 끝나는 문자열 선택 | ="=*Pea" | SweetPea, GreenPea |
| ?(물음표) : 한 글자와 매칭 | ="=Pea?" | Pear, Peas 와 같이 "Pea"로 시작하는 4글자 단어 선택 |
| Any of the symbols *?~ | ="=Pea~*" | Pea*, Pea? |
| 대소문자 구분(Using Formulas as Criteria) | =EXACT(A7,"Peach") | Peach |
| 지정된 값 보다 큰 셀 선택 | =">700" | 701,702 etc. |
| 지정된 값과 같거나 큰 셀 선택 | =">=700" | 700, 701,702 etc. |
| 지정된 값 보다 작은 셀 선택 | ="<700" | 699,698 etc. |
| 지정된 값과 같거나 작은 셀 선택 | ="<=700" | 700, 699, 698 etc. |
| 지정된 값과 같은 셀 선택 | ="=700" | 700 |
고급 필더에 대한 이해
먼저 고급 필터를 이용해 구체적으로 무엇을 할 수 있는지 살펴 보도록 하겠습니다. 앞서 고급 필터는 특정 범위의 데이터들에서 내가 원하는 조건에 맞는 데이터들만 추출하는 기능이라고 했습니다. 아래 데이터 목록을 대상으로 고급 필터를 직접 적용하는 예를 통해 고급 필터를 사용하는 방법에 대해 살펴 보도록 하겠습니다.
가장 먼저 고급 필터를 사용하기 위해서는 3가지 범위(Range)에 대해 알아야 합니다.
목록 범위
- 목록 범위는 필터링 할 '원본 데이터'가 입력된 범위입니다.
- 목록 범위에는 행의 의미를 나타내는 헤더가 포함되어야 합니다(예: 학과, 이름, 점수)
조건 범위
- 조건 범위는 필터링 할 '조건'이 입력된 범위입니다.
- 조건 범위의 헤더는 목록 범위 열 헤더 중 하나여야 합니다. 그렇지 않으면 무시됩니다.
- 조건 범위의 헤더는 임의의 순서로 지정할 수 있습니다.
- 필요한 만큼 조건 범위에 헤더를 포함할 수 있습니다.
- 동일한 헤더를 여러 번 사용할 수 있습니다. 이를 통해 동일한 열에 대해 여러 AND 연산을 수행할 수 있습니다.
복사 대상 범위
- 이 범위는 '결과'를 '다른 장소에 복사'를 선택했을 경우에만 유효합니다.
- 필터링한 결과를 다른 범위에 출력할 경우, 출력될 위치를 지정합니다.
- 오류를 방지하려면 이 범위가 출력 범위의 헤더 행 영역이어야 합니다 .
- 목록 범위의 일부 또는 모든 열을 출력으로 사용할 수 있으며 순서에 상관없이 사용할 수 있습니다.
- 이 범위의 열 헤더는 '목록 범위' 열 헤더 이어야 하며 그렇지 않으면 VBA 런타임 오류 1004가 발생합니다.
아래는 고급 필터를 사용하기 위해 엑셀 시트에 '조건 범위'와 '복사 위치'를 추가한 후 스크린샷입니다.
'목록 범위'에서 '학과'가 '컴퓨터공학과'인 학생을 추출하여 그 학생의 '학과', '이름', '점수'를 '복사 위치'에 출력해보도록 하겠습니다.
고급 필터는 Excel 리본 메뉴에 있는 '데이터' 탭 의 '정렬 및 필터' 섹션 에서 찾을 수 있습니다.
아래는 고급 필터 대화창을 띄워 '목록 범위', '조건 범위', '복사 위치'를 설정한 후 확인을 선택 했을 때 아래 처럼 지정된 조건에 맞는 데이터들만 필터링 되어 복사 위치에 출력 되는 것을 확인 할 수 있습니다.
사실 이 정도의 기능은 '자동 필터'에서도 충분히 가능한 기능입니다. '고급 필터'는 여기에 더하여 AND 조건, OR 조건, 범위등을 지정할 수 있습니다. 엑셀 시트에서도 조건들을 지정하는 것이 가능하지만 이 포스트는 VBA 스크립트를 배우고 이용하는 것이 주 목표이므로 이제 부터는 고급 필터를 VBA 스크립트를 통해 제어하는 방법에 대해 살펴 보도록 하겠습니다.
AdvancedFilter 함수 인자
AdvancedFilter 함수의 인자 목록은 아래와 같습니다.
DataRange.AdvancedFilter Filter Action, Criteria, [CopyTo], [Unique]| 매개 변수 | 필수여부 | 타입 | 상세 |
| Action | 필수 | xlFilterAction | xlFilterInPlace 또는 xlFilterCopy. |
| CriteriaRange | 선택 | Range | 데이터 필터링에 사용되는 기준 범위. |
| CopyToRange | 선택 | Range | Action 매개변수가 xlFilterCopy로 설정된 경우 대상 범위 |
| Unique | 선택 | Boolean | 단일 고유 레코드만 허용 여부 |
Microsoft 도움말 페이지 에서 매개변수에 대해 보다 자세한 내용을 확인할 수 있습니다.
고급 필터는 범위(Range) 단위 함수 입니다. 이 말은 여러 분은 고급 필터를 이용하여 특정 범위의 데이터에 대해 여러분이 원하는 조건에 의해 필터링 된 데이터들만 뽑아 낼 수 있다는 뜻입니다.
AdvancedFilter 함수를 이용하여 여러분은 목록 범위에 필터링 결과를 출력할 수도 있고 특정 위치(복사 위치)에 필터링 된 결과를 출력할 수도 있습니다.
' 목록 범위에 그대로 출력하기
rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange
' 복사 위치에 출력하기
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination첫번째 인자는 여러분의 필터링 된 결과가 어디에 출력 될지를 결정합니다.
- xlFilterInPlace - 원본 데이터 위치에 필터링 결과를 출력 합니다.
- xlFilterCopy - 복사 위치에 필터링 결과를 출력 합니다. 이 경우 세번째 인자로 복사 위치를 지정해주어야 합니다.
중복된 필터 결과를 제거하기 위해선 Unique 인자를 True로 지정하면 됩니다.
' Filter in place
rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange, , True
' Filter and copy data
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination, TrueVBA 코드 작성하기
데이터 범위(range)를 설정하는 가장 쉬운 방법은 인접한 모든 셀들을 선택하는 CurrentRegion을 이용하는 겁니다. CurrentRange에 대한 자세한 설명은 [여기]를 참고하세요.
아래와 같은 방법으로 CurrentRegion을 이용하여 '범위를 선택할 수 있습니다.
Dim rgData As Range, rgCriteriaRange As Range
Set rgData = Range("A1").CurrentRegion
Set rgCriteriaRange = Range("A10").CurrentRegion복사 위치(CopyTo 인자) 범위를 설정하기 위해서는 전체 헤더 행을 지정해야 합니다. 이것 또한 CurrentRegion의 첫번째 행만 얻어 오는 방법으로 손쉽게 지정 가능합니다.
Dim rgCopyToRange As Range
Set rgCopyToRange = shFruit.Range("E6").CurrentRegion.Rows(1)전체 코드는 아래와 같습니다.
Sub RunAdvancedFilter()
' 변수 선언
Dim rgData As Range, rgCriteriaRange As Range, rgCopyToRange As Range
' 각 범위 선언
Set rgData = Sheet1.Range("A1").CurrentRegion
Set rgCriteriaRange = Sheet1.Range("A10").CurrentRegion
Set rgCopyToRange = Sheet1.Range("E6").CurrentRegion.Rows(1)
' AdvancedFilter 함수 이용해 필터 적용
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange
End Sub여러분은 위 코드를 어떠한 AdvencedFilter가 필요한 곳에 적용할 수 있습니다. 여러분이 해야 할 일은 적절한 범위를 지정해주는 것 뿐입니다. Sheet1은 여러분이 원하는 어떤 워크시트나 코드 네임으로 대체 될 수 있습니다. 워크시트와 '코드 네임'에 대한 보다 자세한 설명은 [여기]를 참고 하세요.
[NOTE] VBA에서 AdvancedFilter를 사용 할 때 지정되는 범위는 꼭 같은 워크시트나 같은 워크북에 있는 범위가 아니어도 됩니다.
필터링 된 데이터 클리어하기
만일 xlFilterInPlace 인자를 이용하여 목록 범위에 필터링 된 결과를 출력한 후 원본 데이터를 다시 복구해야 할 경우는 ShowAllData 함수를 호출함으로써 원본 데이터를 복구 할 수 있습니다. 단, ShowAllData 함수를 호출하기전 워크시트의 FilterMode가 True로 설정되어 있는지 확인해야만 합니다.
If Sheet1.FilterMode = True Then
Sheet1.ShowAllData
End IfxlFilterCopy를 이용하여 '복사 위치'에 필터링 결과를 출력할 경우, 이전 결과 위에 덮어 쓰기 때문에 새로운 결과를 출력하기 전에 ClearContents를 이용하여 이전 결과를 지워 줘야만 합니다. 그렇지 않으면 새로운 결과와 이전 결과가 뒤섞여 출력 될수 있습니다.
Sheet1.Range("E6").CurrentRegion.Offset(1).ClearContents헤더 행은 클리어하지 않고 남겨 두기 위해 Offset을 지정했음에 주의 해주세요.
고급 필터 조건(Advenced Filter Criteria) 적용
필터 조건을 사용하기 위해서 '목록 범위'의 헤더 컬럼과 해당 행의 값들을 이용합니다. 다음 조건은 '학과'가 '컴퓨터공학과'인 모든 행들을 리턴합니다.
위 조건은 아래의 행들을 리턴합니다.
여러개 조건 동시 적용하기
여러개의 조건을 동시에 적용하는 방식에는 AND 연산과 OR 연산이라는 두가지 개념이 있습니다. AND 연산이란 주어진 모든 조건이 만족할 때만 조건에 맞다고 판단합니다. 반면에 OR 연산은 주어진 조건 중 하나라도 만족이 된다면 조건에 맞다고 판단 합니다.
예를 들어 '학과'가 '컴퓨터공학과'이면서 '점수'가 '50'점 이상인 학생들만 필터링하고 싶다고 가정해 보겠습니다. 이 경우 '컴퓨터공학과', '50점 이상' 이렇게 두 가지 조건이 모두 만족해야하니 이것은 AND 연산입니다.
VBA AdvencedFilter 에서 AND 연산을 위해서는 옆으로, 즉 아래 그림 처럼 각 행마다 조건을 지정해줍니다.
위 조건은 다음과 같은 행을 리턴합니다.
AND 말고 OR 연산을 위해서는 세로, 즉 아래 그림 처럼 필터링하고 싶은 값들을 여러개의 열에 걸쳐 나열해 줍니다.
결과를 확인하면 아래 그림의 '학과'가 '유아교육과' 이거나 '컴퓨터공학과'인 행들이 리턴되는 것을 볼 수 있습니다.
서로 다른 컬럼에도 OR 연산을 적용 할 수 있습니다.
위 조건은 아래 처럼 '컴퓨터공학과'이거나 50점이 넘는 모든 학생들을 리턴합니다.
AND와 OR 같이 적용하기
아래와 같이 AND 연산과 OR 연산을 같이 지정할 수도 있습니다.
결과로 '간호학과'이면서 '50점'이 넘거나 '컴퓨터공학과'이면서 '50점'이 넘는 학생들을 선별 할 수 있습니다.
범위 조건 지정하기
특정 값만을 비교하는 것이 아니라 범위를 지정할 수동 있습니다. 예를 들어 점수가 40점 이상 60점 미만인 학생들을 필터링 해보도록 하겠습니다. 범위를 지정하기 위해서는 동일한 컴럼을 두번 써줍니다.
결과는 아래와 같습니다.
고급 필터의 장점과 제약 사항
고급 필터는 사용하기 쉽고 그 기능을 아주 잘 수행합니다. 그러나 모든 도구와 마찬가지로 수행할 수 있는 작업에는 장점과 제한이 있습니다.
고급 필터를 사용하면 다음과 같은 이점이 있습니다.
- 속도 - 고급 필터는 데이터를 복사하고 필터링하는 가장 빠른 방법입니다(다만 행이 많아질수록 느려지긴 합니다)
- 고급 필터 - 수식 사용을 포함한 보다 복잡한 필터링 옵션을 제공합니다.
- 매우 적은양의 코드 - 대부분의 경우 똑같은 코드를 반복적으로 사용할 수 있으며 VBA에서 코드를 복사하고 필터링하는 다른 방법들에 비해 간단합니다.
- 서식 지정 - 결과를 복사할 때 원본 데이터와 일치하도록 결과 데이터 셀의 서식을 자동으로 지정합니다.
다음은 고급 필터의 제약 사항 입니다.
- 속도 - AdvancedFilter를 사용하면 매우 빠르지만 동일한 코드에서 여러번 호출하면 당연히 속도가 느려집니다.
- 조건은 Range만 사용할 수 있으며 배열과 같은 자료구조를 사용할 수 없습니다. 해결 방법은 배열을 Range에 쓴다음 조건으로 사용하는 것입니다.
- 데이터를 변경할 수 없음 - AdvancedFilter는 단순히 데이터를 필터링하고 복사합니다. 필터링 후 복사하기 전에는 데이터를 변경할 수 없습니다.
- 데이터를 추가할 수 없음 - 데이터를 추가하기 위해서는 추가 코드 작성이 필요합니다.
고급 필터의 일반적인 오류
우리가 자주 마주치게 되는 고급 필터의 일반적인 오류는 다음과 같습니다.
'오류 1004 - 추출 범위의 필드 이름이 잘못되었거나 없습니다.'
VBA 런타임 오류 1004는 아래 예에서 볼 수 있듯이 하나 이상의 출력 열 헤더가 원본 데이터에 없을 때 발생합니다. 이 오류가 발생하면 CopyTo 범위의 헤더에 철자나 헤더 존재 유무, 올바른 범위를 참조하고 있는지 확인해야 합니다.
