이번 강좌에서는 매크로를 이용하여 원하는 조건의 데이터만 표시(필터링)한 후,
화면에 표시된 데이터에 한해서만 자동으로 계산되는 예제를 만들어보겠다.
아래 예제 파일로 같이 실습한 후 완성 파일과 비교해보자.
예제 파일과 완성 파일
|
여기에서 사용되는 기능은 데이터 유효성 검사, 고급 필터, 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 키를 누르면
자동으로 필터링이 되어 해당 부서 데이터만 표시되고, 평균점수가 다시 계산된다.
필터링을 제거하고 모든 데이터를 표시하고 싶다면
[데이터]-[정렬 및 필터]-[지우기]를 클릭하면 된다.
동영상 강좌 보기
'말랑말랑 컴퓨터 > 엑셀 강좌&팁' 카테고리의 다른 글
엑셀 가상 분석 기능 '시나리오' by Y (8) | 2013.12.18 |
---|---|
엑셀 함수 23 - OFFSET 함수 (동적인 셀 참조하기) by Y (0) | 2013.11.28 |
엑셀 가상 분석 기능 '데이터 표' by Y (14) | 2013.11.22 |
[엑셀기초] 모든 셀에 같은 수 더하기/곱하기 (선택하여 붙여넣기 - 연산 기능) by Y (8) | 2013.10.11 |
엑셀 함수 22 - INDIRECT 함수 (동적인 셀 참조하기) by Y (14) | 2013.09.30 |
엑셀 함수 21 - TRANSPOSE 함수, 선택하여 붙여넣기 (행/열 바꿈) by Y (0) | 2013.09.09 |
엑셀 차트 만들기 3 - 데이터 선택, 차트 이동, 축 방향 변경 등 by Y (7) | 2013.08.16 |
엑셀 차트 만들기 2 - 차트의 구성 요소 추가/삭제/편집 by Y (3) | 2013.08.14 |
엑셀 차트 만들기 1 - 차트 레이아웃, 차트 스타일 by Y (2) | 2013.08.14 |
2007 엑셀파일 암호(비밀번호)설정 및 해제방법 by S (6) | 2013.08.13 |