앞선 포스팅에서 일반 필터링 기능에 대해 다루었고,
더 복잡한 조건에 대해서도 빠르게 필터링 할 수 있는 엑셀 고급필터 기능까지 소개했습니다.
고급필터 기능을 알고 나서 실무에 적용하면서 업무 시간이 2배 이상 단축되고,
데이터 분석 업무에 더욱 재미를 붙이고 있습니다.
오늘은 이렇게 유용한 고급필터 기능에 간단한 매크로까지 적용해
고급필터 자동화 버튼 생산까지 해보겠습니다.
잠시 복습 및 고급필터를 알아두어야 하는 점에 대해 간략히 집고 넘어가겠습니다.
일반필터 기능만 알고 있다면 생길 수 있는 문제점, 불편사항이 있습니다.
엑셀 기본필터(자동필터)의 문제점
엑셀의 기본필터인 자동필터는 원본 데이터 위로 필터를 적용합니다.
쉽게 말해 원본데이터는 말 그대로 raw데이터로 변동없이 최초의 상태로 유지하는 것이 중요합니다.
그런데, 자동필터를 사용하게 되면 원본 데이터에 필터링을 적용하고
중간중간 사용자가 나도 모르게 원본데이터를 수정하는 일이 발생하는 경우가 있습니다.
고급필터의 장점
이렇게 원본데이터의 훼손 가능성이 있는 자동필터(기본필터)와 달리
엑셀 고급필터 기능은 원본데이터를 유지한 채 별도 데이터를 원하는 곳에 출력할 수 있습니다.
대신, 고급필터를 사용하기 위해서는 필터링 할 조건을 별도로 작성해주어야 합니다.
아래의 원본데이터에서 우측에 조건을 별도로 작성하기 위해
조건으로 사용할 머리글을 불러옵니다.
[고객명], [지역], [대분류]를 조건으로 사용해보겠습니다.
지난번 고급필터 활용 포스팅에서도 언급했듯이
고급필터에서 사용할 조건범위의 머리글은 원본데이터의 머리글과 일치해야 합니다.
아래 포스팅을 클릭하시면 보다 자세한 내용을 복습할 수 있습니다
저는 "삼성"이 포함된 고객명과 대분류 사무용품을 필터링 해보겠습니다.
해당 단어를 포함하는 조건을 지정할 때는 *(별표)를 앞/뒤로 붙어주면 됩니다.
*삼성* 이렇게 말이죠
이제 원본데이터를 범위로 지정한 뒤 고급필터를 실행해줍니다.
원본데이터를 선택할 때는 원본데이터 영역 안에 셀을 가져다 놓고
단축키 ctrl+A를 눌러 현재영역을 선택해줍니다.
이제 고급필터를 실행해줍니다.
[데이터] > [정렬 및 필터] > [고급]을 눌러 고급필터 창을 띄웁니다.
원본데이터로 부터 조건범위를 설정하고 그 결과를 다른 곳으로 추출하기 위해서
다른 장소에 복사를 선택합니다.
그런 다음 원본데이터의 범위, 조건범위를 확인해주고
복사할 셀의 위치를 선택해줍니다.(저는 O1을 지정하였습니다.)
그럼 아래와 같이 O1번 셀부터 조건에 맞는 데이터를 원본으로 부터 불러옵니다.
고급필터 사용 시, 원본데이터에서 필요한 열만 가져오는 방법
여기서 원본데이터에서 내게 굳이 필요하지 않는 열을 가져올 필요는 없습니다.
그러려면 어떻게 해야할까요?
내가 원하는 열의 머리글만 복사할 위치에 적어놓습니다.
고급필터 실행 시, 복사위치란에 해당 머리글을 범위지정하면 됩니다.
이제 이 과정을 매크로로 기록해보겠습니다.
엑셀 고급필터 매크로 자동화 지정하기
1) 엑셀 고급필터 매크로 기록하기
[개발도구]탭에서 매크로 기록 버튼을 눌러 줍니다.
만약, [개발도구]탭이 보이지 않는다면
EXCEL 옵션에서 [리본 사용자 지정] > 개발도구를 체크 해주면 됩니다.
매크로 이름으로는 고급필터자동화로 지정하고
앞서 고급필터를 적용했던 과정을 똑같이 수행하며 매크로를 기록해보겠습니다.
1. 원본데이터 범위 지정
2. 고급필터 창 실행
3. 조건범위 및 복사위치에 복사 머리글 범위 지정
고급필터를 적용이 끝났다면 이제 매크로 기록을 중단해줍니다.
매크로 중단은 [개발도구]탭 기록 중지 버튼이나
왼쪽 하단에 위치한 중지 버튼을 눌러주면 됩니다.
2) 엑셀 고급필터 매크로 편집하기
ALT+F11을 눌러 매크로 VBA 코딩 창을 띄워 줍니다.
코딩이라고 어려울 것 없습니다. 여기까지 오신 분들 조금만 더 참아주세요.
고급필터 실행에 따라 자동으로 기록된 VBA 코딩 창 입니다.
빨간 네모 박스 위에 다른 코딩이 들어 있다면 드래크 혹은 커서를 옮기는 과정 등에서
고급필터 실행과 무방하게 얼마든지 다른 내용으로 코딩 될 수 있고 지워주어도 무방합니다.
이제 네모 안의 고급필터 명령어를 한 줄에 하나씩 보기 좋게 구분해주겠습니다.
Range("A1:I510").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("K1:M2"), _
CopyToRange:=Range("O1:S1"), _
Unique:=False
범위.AdvancedFilter : 해당 범위에 고급필터를 적용
Action:=xlFilterCopy, : 필터링 된 데이터를 다른 곳으로 복사
CriteriaRange:=범위 : 해당범위를 조건으로 지정
CopyToRange:=범위 : 해당범위에 필터링된 데이터 복사
고급필터 자동화 범위지정 currentregion
실무에서 문제점은 원본데이터와 또한 필터링 조건이 매번 바뀔 것입니다.
데이터가 입력된 전체 범위를 선택하는 단축키는 ctrl+A 였습니다.
이 역할을 해줄 수 있는 매크로 VBA 코딩 명령어가 currentregion 입니다.
currentregion 명령어를 활용해서 전체 범위를 선택해주는 명령어를 아래와 같이 작성해줍니다.
해당 코딩을 VBA창에 복사 붙여넣기 해주세요
Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("K1").CurrentRegion, _
CopyToRange:=Range("O1").CurrentRegion.Rows(1), _
Unique:=False
Range("A1").CurrentRegion.AdvancedFilter _
A1 셀에서 ctrl+A 한 것 처럼 원본 데이터 범위 전체를 선택합니다.
CriteriaRange:=Range("K1").CurrentRegion, _
K1 셀에서 ctrl+A 한 것 처럼 조건 범위 전체를 선택합니다.
CopyToRange:=Range("O1").CurrentRegion.Rows(1), _
O1 셀에서 ctrl+A 한 것 처럼 복사할 범위 전체 중 머리글이 입력된 첫째 행만을 복사합니다.
3) 고급필터 매크로 자동화 버튼 추가하기
그런 다음 매크로 실행 버튼을 지정하기 위해서 도형을 하나 삽입해줍니다.
[삽입] > [도형] > 모서리가 둥근사각형 삽입
Tip 셀 크기에 맞게 도형 삽입하는 방법
도형 삽입 시, ALT키를 누른 채로 삽입하면 셀의 크기에 맞게 삽입할 수 있습니다.
도형을 선택에 마우스 우클릭 > 매크로 지정에서 편집이 끝난 매크로를 버튼에 지정해줍니다.
이제 다 끝났습니다.
조건을 바꿔가며 매크로 버튼을 눌러 테스트해보세요!
1) 고객명에 삼성을 포함하고, 지역기 경기이면서 대분류가 사무용품인 데이터 추출
2) 1)이거나 고객명이 해태를 포함하거나, 현대이면서 지역이 서울인 데이터 추출
저는 제조업 실무에서 다음과 같이 활용하고 있습니다.
하나의 검사장비에서 제품의 품질특성 여러개가 전수검사 되는 장비가 있습니다.
각각의 항목의 요구 SPEC이 다 다르기 때문에 rawdata를 다운받았을 때 원본데이터의 양이 정말 많습니다.
또한 제조업 특성상 생산 중간중간 변동성/노이즈에 의해 측정값이 정상범위가 아닌 경우도 있습니다.
이런 데이터는 제외하고 SPEC 근처의 값만을 추출해야 할 때가 있습니다.
여러 품질특성 항목들 중 내가 그 시점에 관심있는 항목만을 추출해야할 때도 있습니다.
이럴 때 이러한 고급필터 자동화 매크로 기능을 활용하면 업무시간을 2배 이상 단축할 수 있습니다.
많은 도움되셨다면 공감 부탁드립니다.
'실무엑셀기초' 카테고리의 다른 글
| 엑셀 날짜 데이터 단축키 활용으로 빠르게 해결하기 (1) | 2023.10.21 |
|---|---|
| 퇴근이 빨라지는 직장인 실무 엑셀 단축키 완벽 정리! (0) | 2023.09.14 |
| 컴활1급 실무 엑셀 고급필터로 원하는 조건에 따른 필터링 하는 방법! (2) | 2023.09.07 |
| 회사에서 사용하는 기초 실무엑셀 배우기, 엑셀 필터를 통한 데이터 정리 및 보고서 시각화 (2) | 2023.09.04 |
| 회사 엑셀실무 배우기, 엑셀 필터 기능 활용을 위한 단축키 콤보! (2) | 2023.09.04 |
댓글