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

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

 

 

 

 

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

 

예제 파일과 완성 파일

 

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

 

 

 

데이터유효성검사.xlsx

데이터유효성검사완성.xlsx

 

 

데이터 유효성 검사는 셀에 입력될 데이터를 제한하기 위해 사용하는 기능이다. 게다가 데이터 유효성 검사로 데이터가 제한된 셀은 목록 상자의 버튼으로 쉽게 데이터를 바꿀 수 있어, 계산이나 서식 등 다방면에 유용하게 사용할 수 있다. 이번에 실습하는 예제 파일과 완성 파일을 첨부하였으니 따라서 실습해보면 되겠다.

 

 

 

 

D13 셀에서 부서를 선택할 수 있도록 데이터 유효성 검사 기능 적용

 

 

 

 

1. D13 셀을 선택한 후 [데이터]-[데이터 도구]-[데이터 유효성 검사] 명령을 클릭한다.

 

 

 

 

 

2. [데이터 유효성] 대화 상자가 나타나면 '제한 대상'을 '목록'으로 선택한다.

 

 

 

 

 

3. '원본' 부분에 허용할 데이터 값을 쉼표(,)로 구분하여 입력한다. 입력값이 시트에 있는 경우 그 범위를 선택해도 된다.

 

 

 

 

 

4. 데이터 유효성 검사가 적용될 D13셀을 선택하면 나타날 설명 메시지를 입력하기 위해 [설명 메시지] 탭을 클릭한 후 내용을 입력한다.

 

 

 

 

 

5. [확인] 버튼을 누르면 D13 셀에 목록 버튼이 나타나고 설명 메시지가 표시된다.

 

 

 

 

 

6. 목록 버튼을 누르면 원하는 항목을 선택할 수 있다.

 

 

이와 같이 입력될 값을 제한하는 것이 데이터 유효성 검사다.

잘만 활용하면 실무에서 일을 쉽게 할 수 있으므로 많이 연습해보자.

 

 

 

 

데이터 유효성 검사를 이용한 조건부 서식 자동으로 지정하기

 

 

 

 

D13 셀의 부서명을 이용하여 조건부 서식을 적용하는 실습을 통해 데이터 유효성 검사의 활용에 대해 알아보자.

조건부 서식에 대해 모른다면 다음 글을 먼저 참조한다.

엑셀 조건부 서식 3 (수식을 이용한 조건부 서식) by Y

 

 

 

 

1. 조건부 서식을 적용할 범위 [B5:H11]를 선택한 후 [홈]-[스타일]-[조건부 서식]-[새 규칙] 명령을 선택한다.

 

 

 

 

 

2. 순서대로 조건부 서식을 작성한다. 먼저 '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후 '수식' 입력란에 '=$C5=$D$13'을 입력한다. 이 수식의 의미는' 부서가 D13셀 값의 부서와 같을 경우'라는 뜻이다. [서식] 버튼을 눌러 원하는 서식을 지정한다. 여기에서는 채우기를 '노랑'으로 지정했다.

 

 

 

 

 

3. [확인] 버튼을 누르면 D13셀에서 선택한 부서와 같은 부서의 행에만 서식이 지정된 것을 볼 수 있다.

 

 

 

 

 

4. D13셀의 값을 변경하면 조건부 서식도 자동으로 변경된다.

 

 

이런 식으로 데이터 유효성 검사를 이용한다면 활용할 수 있는 부분이 아주 많은 것을 짐작할 수 있을 것이다.

 

 

 

 

 

 

 

 

Posted by Y&S