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

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

 

 

 

 

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

 

예제 파일과 완성 파일

 

예제 파일.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

댓글을 달아 주세요

  1. 비밀댓글입니다

    2013.11.06 01:21 [ ADDR : EDIT/ DEL : REPLY ]
    • 고급필터에서 조건범위를 실수하신 듯합니다.
      그 앞부분은 별로 실수할 부분이 없는거 같아서요.
      매크로 내용이 고급필터이기도 하구요.

      2013.11.06 01:26 신고 [ ADDR : EDIT/ DEL ]
  2. 한여자

    감사합니다~!!! 덕분에 복잡한일이 한결 수월해 졌어요

    2013.11.20 16:39 [ ADDR : EDIT/ DEL : REPLY ]
  3. 질문이에요 ^^

    왜 저는 드롭다운목록에서 부서를 선택하면 자동으로 하위 목록들이 변경되지 않을까요? 예제로 올려주신 파일을 열어서 해봐도 동영상에서 처럼 부서선택으로 하위목록 변경이 자동으로 실행이 되지 않네요 ?? 뭔가 엑셀자체의 설정이 잘못된 걸까요?

    2014.02.10 14:46 [ ADDR : EDIT/ DEL : REPLY ]
    • 매크로가 실행이 되도록 설정이 안되어 있을 가능성이 있겠네요.
      엑셀 매크로 실행 설정을 검색하셔서 설정을 바꿔보세요~
      아니면 매크로 단축키를 눌러야 고급필터가 실행되는데 단축키 누르셨나요?

      2014.02.10 15:56 신고 [ ADDR : EDIT/ DEL ]
  4. 질문이에요 ^^

    아 메크로 실행단축키를 눌러줘야 하는거군요? 그냥 드롭다운목록에서 부서만 변경해주면 자동으로 넘어가는걸로 생각을 했어요..ㅎ

    2014.02.10 16:40 [ ADDR : EDIT/ DEL : REPLY ]
  5. 엑셀

    안녕하세요ㅠㅠ검색하다가 우연히 보게 되서 정말 많은 도움이 되었는데 한가지만 여쭤보고싶어서요! 지금 예제에서는 조건범위가 한가지 조건밖에 없는데, 저는 여러조건으로 하고싶은데요ㅠㅠ여러 조건을 넣어서 하니까 데이터가 하나도 안보이더라구요! 여러 조건을 하고싶다면 다르게 해야 하나요?

    2015.02.05 11:10 [ ADDR : EDIT/ DEL : REPLY ]
    • http://yslife.tistory.com/338 글에 고급필터 조건 작성법이 나와있으니 도움이 될거 같네요^^
      즐거운 하루 되세요~

      2015.02.05 14:03 신고 [ ADDR : EDIT/ DEL ]
  6. 혹시 단축키를 누르지 않고도 자동으로 필터링 되는 방법은 없을까요??

    2015.07.22 16:39 [ ADDR : EDIT/ DEL : REPLY ]
    • 매크로는 단축키나 도형에 저장해놓고 쓰는거라서 안될 듯..
      그래도 컴퓨터한테 이제 시작해라~!라고 알려줄 시발점은 필요하니까요.

      2015.07.27 09:56 신고 [ ADDR : EDIT/ DEL ]