안녕하세요, 짤막한 강좌 한정희 강사입니다.
이번 강좌는 고급 필터를 매크로로 기록하여 조건이 변경될 때 결과가 자동으로 구해지도록 설정해 보겠습니다.
고급 필터는 수식처럼 참조한 셀에 값이 변경되어도 결과값이 바뀌지 않습니다.
매크로는 반복되고 복잡한 작업을 바로가기 키(단축키)를 누르거나 또는 매크로 실행을 지정해둔 단추를 누르면 작업이 처리되도록 하는 기능입니다.
매크로 기록은 어렵지 않습니다.
실제 어렵다고 느끼는 건 반복되거나 처리하기 복잡한 엑셀 사용법이 어려운 겁니다.
하지만 지금 매크로로 기록하려는 작업은 크게 어렵지 않습니다.
천천히 강좌를 보고 따라서 실습을 해보면 됩니다.
실습 파일 다운로드
완성 파일 다운로드
현재 데이터는 한 과목만 또는 두 과목 이상, 세 과목 이상 방과 후 수업을 신청한 명단입니다.
조건을 설정해서 한 과목만 수업을 신청한 명단을 추출할 수도 있고 두 과목 이상 수업을 신청한 학생을 추출할 수도 있습니다.
물론 학생정보를 기준으로 정렬해서 뽑아볼 수도 있지만 지금 소개하는 고급 필터 사용법을 알면 별도의 셀에 학생 정보를 추출할 수 있습니다.
중복 데이터 추출하는 방법을 그대로 사용하면 됩니다.
이번 강좌에서도 조건을 만드는 방법을 소개하겠지만 이 강좌가 어렵다면 이전에 작성해둔 [고급 필터를 활용하여 중복 데이터 추출하기] 강좌를 보셔야 합니다.
hantip.net
조건을 작성해보겠습니다.
1. [G3] 셀에 '조건'을 입력합니다.
2. [G4] 셀에 =COUNTIF($A$4:$A$90,A4)>=2 이라고 조건식을 입력합니다.
수식은 학생정보 범위 A4:A90에서 A4 셀에 입력된 1-1-1 강만호가 입력된 개수를 구합니다.
그 결과가 2 이상이면 두 과목 이상 신청한 학생이 됩니다.
명단에서 특정 필드만 필터 하려면 결과를 나타낼 범위에 해당 필드명만 입력해야 합니다.
[I3:K3] 셀 범위에 '학생정보, 이름, 학생연락처'를 입력합니다. 열 너비를 조금 넓혀 둡니다.
그리고 신청 명단 범위를 이름 정의하겠습니다.
고급 필터를 적용할 때 신청 명단 범위를 입력해야 하는데 많은 양의 데이터 범위보다 이름으로 정의해 놓고 사용하면 편리합니다.
신청 명단 범위에 아무 셀이나 선택하고 단축키 <Ctrl + A>를 누르면 데이터 범위가 자동으로 선택됩니다.
[이름 상자]에 '데이터베이스'라고 입력하고 <Enter>를 누릅니다.
필터 준비가 끝났으니 고급 필터를 시작하겠습니다.
[데이터] 탭 - [정렬 및 필터] 그룹 - [고급]을 선택합니다.
[목록 범위] 입력창에 이름으로 정의해둔 '데이터베이스'를 입력하고 [조건 범위] 입력창에 [G3:G4] 셀 범위를 지정합니다.
그리고 [다른 장소에 복사]를 선택한 다음 [복사 위치] 입력창에 [I3:K3] 셀 범위를 지정합니다.
다른 장소에 복사를 지정하지 않으면 원본 데이터베이스(방과 후 신청 명단)가 있는 위치에 필터 되어 결과가 표시됩니다.
마지막으로 [동일한 레코드는 하나만]에 체크를 하고 [확인] 버튼을 누릅니다.
현재 조건은 두 과목 이상 신청한 학생을 추출하기 때문에 동일한 레코드는 하나만을 체크하지 않으면 같은 학생의 정보와 이름이 두 번 나타나게 됩니다.
여기서는 어떤 과목을 신청했는지를 보려는 것이 아니고 어떤 학생인지만 알려고 하기 때문에 중복되는 데이터를 구할 필요가 없습니다.
두 과목 이상 수강한 학생을 추출했습니다.
이번에는 조건을 수정하여 세 과목 이상 수강한 학생을 추출해볼까요?
조건식 셀 [G4]를 더블 클릭하고 >=2를 >=3으로 수정하고 <Enter>를 누릅니다.
결과는 바뀌지 않습니다.
앞에서 설명한 것처럼 셀에 값이 변경되어도 결과는 바뀌지 않는다고 했습니다.
다시 변경된 조건 범위를 지정해서 고급 필터를 적용해야 합니다.
이번에는 고급 필터를 사용했습니다.
결과는 정상적으로 나왔지만 기존 결과의 서식(셀 테두리)이 그대로 남아 있네요.
엑셀 버전에 따라 기존 결과 위에 새로운 결과 두 건이 표시되고 나머지 결과가 남아 있는 경우도 있습니다.
그래서 새롭게 고급 필터를 적용해 결과를 필터 하려면 기존 결과를 제거해야 합니다.
또다시 2과목만 신청한 학생만 추출하고 싶다면 어떻게 해야 할까요?
1. 조건을 수정합니다.
2. 기존 결과를 제거합니다.
3. 고급 필터를 다시 적용합니다.
바로 이 작업을 매크로로 기록하는 겁니다.
이렇게 매크로를 기록하기 전에 매크로로 기록할 작업을 충분히 연습해야 합니다.
조건을 =COUNTIF($A$4:$A$90,A4)>=2 수정합니다.
[개발 도구] 탭 - [코드] 그룹 - [매크로 기록]을 실행합니다.
[매크로 이름] 입력창에 '고급필터'를 입력합니다.
단추를 삽입해 매크로를 실행할 것이기 때문에 바로 가기 키를 따로 지정하지 않겠습니다.
[확인] 버튼을 누릅니다.
이제부터 진행되는 모든 작업은 매크로로 기록됩니다.
[I4] 셀을 선택하고 단축키 <Ctrl + Shift + 오른쪽 방향키, 아래쪽 방향키>를 눌러 앞에서 구해 둔 결과 값을 모두 선택합니다.
이때 주의해야 할 사항은 [I4] 셀을 선택하는 작업을 꼭 매크로에 기록해야 한다는 것입니다.
그렇지 않고 미리 [I4] 셀이 선택된 상태에서 범위 선택 단축키 <Ctrl + Shift + 오른쪽 방향키, 아래쪽 방향키>를 누르면 실제 매크로를 기록하는 코드 창에는 [I4] 셀이 아닌 ActiveCell, 즉 현재 선택된 셀로 기록됩니다.
그러면 매크로를 실행할 때 엉뚱한 위치가 범위 선택되어 기존 결과가 지워지지 않게 됩니다.
범위가 선택되면 마우스 오른쪽 버튼을 눌러 [삭제]를 선택합니다.
이제 고급 필터를 하겠습니다.
[데이터] 탭 - 정렬 및 필터] 그룹 - [고급]을 선택합니다.
[목록 범위] 입력창에 '데이터베이스', [조건 범위] 입력창에 [G3:G4] 셀 범위를 지정합니다.
이때 조건 범위는 앞에서 고급 필터를 이미 적용했기 때문에 지정한 주소가 아닌 자동으로 정의된 이름이 표시됩니다.
그리고 [다른 장소에 복사]를 선택하고 [복사 위치] 입력 창에 [I3:K3] 셀 범위를 지정합니다.
마찬가지로 자동으로 정의된 이름이 표시됩니다.
[확인]을 누릅니다.
고급 필터 결과가 나타나면 [개발 도구] 탭 - [코드] 그룹 - [기록 중지]를 누릅니다.
[개발 도구] 탭 - [코드] 그룹 - [매크로]를 선택합니다.
매크로 지정 대화 상자가 실행됩니다.
고급 필터 매크로가 추가된 것을 확인할 수 있습니다.
[실행] 버튼을 누르면 매크로가 실행됩니다.
하지만 매번 매크로를 실행할 때 매크로 메뉴를 찾아 실행 버튼을 누르는 것은 번거로운 일입니다.
단추를 하나 추가해 고급 필터 매크로가 실행되도록 설정하겠습니다.
[개발 도구] 탭 - [컨트롤] 그룹 - [삽입] - [양식 컨트롤] 범주에 [단추(양식 컨트롤)]를 선택합니다.
[G6] 셀에 사각형 그리듯 마우스를 드래그합니다.
마우스를 놓는 순간 [매크로 지정] 대화 상자가 실행됩니다.
고급필터 매크로를 선택하고 [확인]을 누릅니다.
단추가 추가되면 단추 2 글자를 지우고 '결과'라고 입력합니다.
단추 이름은 원하는 대로 변경할 수 있습니다.
세 과목 이상 신청한 명단을 추출할 조건식으로 수정하고 [결과] 버튼을 누릅니다.
어떤가요?
기존 결과를 제거하고 고급 필터 하는 작업을 매크로가 대신 실행해주어 결과를 바로 볼 수 있습니다.
이번에는 두 과목만 신청한 학생 명단을 추출해볼까요?
조건식을 아래와 같이 수정하고 [결과] 버튼을 누릅니다.
두 과목만 신청한 명단이 추출이 되었네요.
매크로가 적용된 문서를 저장하는 방법은 다릅니다.
[파일 형식]을 [Excel 매크로 사용 통합 문서 (*.xlsm)]으로 변경하고 저장합니다.
강좌 마치겠습니다.
즐거운 하루 보내세요.
'매크로&VBA' 카테고리의 다른 글
| 시트의 내용을 다른 시트에 한꺼번에 간단히 복사하는 VBA (1) | 2020.05.19 |
|---|---|
| 엑셀 매크로&VBA] 여러 시트 내용을 조회하고 수정하는 간단한 VBA (0) | 2020.04.12 |
| 엑셀 매크로&VBA] 고급 필터 매크로를 보완하는 VBA(변수와 InputBox 함수) (0) | 2020.04.02 |
| 엑셀 매크로&VBA] 매크로를 보완하는 VBA (2) | 2020.03.23 |
| 엑셀 매크로&VBA] 상대 참조 매크로 - 페이지 나누기 (0) | 2020.03.10 |