이번 강좌에서는 매크로를 이용하여 원하는 조건의 데이터만 표시(필터링)한 후,

화면에 표시된 데이터에 한해서만 자동으로 계산되는 예제를 만들어보겠다.

 

 

 

 

아래 예제 파일로 같이 실습한 후 완성 파일과 비교해보자.

 

예제 파일과 완성 파일

 

예제 파일.xlsm 

완성 파일.xlsm

 

 

 

 

여기에서 사용되는 기능은 데이터 유효성 검사, 고급 필터, SUBTOTAL, 매크로 기능인데,

이 중 모르는 기능이 있다면 아래 글을 보고 미리 공부해두는 것도 좋을듯하다.

 

엑셀 함수 17 - SUBTOTAL 함수 (자동필터 결과 합하기, 일련번호 만들기 예제) by Y 

고급필터로 데이터 추출하기 (고급필터 조건 작성법) by Y

엑셀 매크로 실습 (데이터를 원하는 위치로 이동하기) by Y

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y 

 

 

 

 

 

데이터 유효성 검사

 

 

 

먼저 데이터 유효성 검사를 이용하여

부서를 선택할 수 있도록 만들어보겠다.

 

B6셀을 선택한 후 [데이터] 탭의 [데이터 유효성 검사] 명령을 클릭한다.

 

 

 

 

[데이터 유효성] 창이 나타나면 '제한 대상'에서 '목록'을 선택한 후

원본에 '총무부,인사부,기획실'을 입력한다.

 

*** 만약 입력할 데이터가 많다면 시트에 미리 입력해놓고 범위를 지정해도 된다. ***

 

 

 

 

이제 B6 셀에서 목록 버튼을 클릭하여 원하는 부서를 선택할 수 있다.

 

 

 

 

 

 

SUBTOTAL 함수

 

 

 

다음으로 SUBTOTAL 함수를 이용하여 화면에 표시되는

데이터의 각각의 평가점수를 평균 계산해보겠다.

 

F6 셀에서 '=SUBTOTAL('까지 입력하면 사용할 수 있는 함수 목록이 나타난다.

여기에서는 평균을 구하기 위해 '1 - AVERAGE'를 더블 클릭하여 선택한다.

 

 

 

 

'=SUBTOTAL(1'까지 입력되면 쉼표를 입력하고 '직무수행능력' 데이터를 범위 지정한다.

완성된 식은 'SUBTOTAL(1,F9:F22)'이다.

 

 

 

 

같은 방법으로 나머지 세 과목의 평균 점수를 구한 후

가운데 정렬하고 소수점 이하 한 자리까지만 표시한다.

 

 

 

 

 

 

고급필터와 매크로

 

 

 

이제 마지막으로 원하는 부서만 필터링하는 고급필터를 매크로를

이용하여 자동으로 실행되도록 만들어보겠다.

 

매크로 기록을 시작하기 위해 [개발 도구] 탭의 [매크로 기록] 명령을 클릭한다.

 

 

 

 

매크로 기록 창이 나타나면 매크로 이름과 바로 가기 키를 입력한 후 [확인] 버튼을 클릭한다.

 

 

 

 

데이터 범위를 모두 지정한 후 [데이터] 탭의 [고급 필터] 명령을 클릭한다.

 

*** 매크로 기록이 시작되면 모든 작업이 저장되므로 신중히 작업해야 한다. ***

 

 

 

 

고급 필터 창이 나타나면 '조건 범위' 입력란을 클릭한 후 [B5:B6] 범위를 선택하고 [확인] 버튼을 클릭한다.

 

 

 

 

고급 필터가 실행되면 할 일이 모두 끝났으므로 [개발 도구]로 돌아가 [기록 중지] 명령을 클릭한다.

 

 

 

 

 

 

완성된 결과 보기

 

 

 

이제 부서를 변경한 후 매크로 단축키로 지정한 Ctrl+R 키를 누르면

자동으로 필터링이 되어 해당 부서 데이터만 표시되고, 평균점수가 다시 계산된다.

 

 

 

 

필터링을 제거하고 모든 데이터를 표시하고 싶다면

[데이터]-[정렬 및 필터]-[지우기]를 클릭하면 된다.

 

 

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

Posted by Y&S