엑셀함수강좌 23강




수학/삼각 함수

SUBTOTAL





SUBTOTAL 함수에 대해 설명한 강의입니다.


▶ SUBTOTAL( Function_num, 참조1, 참조2...)

목록이나 데이터베이스의 부분합을 구함




=SUBTOTAL(1, A1:A10)

A1:A10 범위의 숫자의 평균값을 구한다.

숨겨진 셀값까지 모두 계산한다.

단, 필터를 이용해 추출된 데이터는 표시된 데이터만 계산한다.


=SUBTOTAL(101, A1:A10)

A1:A10 범위의 숫자의 평균값을 구한다.

수동으로 숨겨진 셀값은 제외하고 계산한다.







아래의 연습파일을 다운로드해서 연습하세요.


subtotal 함수.xlsx











아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.








 

 

 

 


Posted by Y&S

 

 

 

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

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

 

 

 

 

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

 

예제 파일과 완성 파일

 

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

 

 

 

SUBTOTAL 함수란?

목록이나 데이터베이스의 부분합을 구하는 함수이고, [데이터] 메뉴의 [부분합] 명령을 사용하여 부분합 목록을 작성하는 방법과 같이 11가지 기능을 가진 다 기능 함수다. SUBTOTAL로 많이 사용하는 함수는 SUM, AVERAGE, COUNT 등의 함수이다.

 

 

SUBTOTAL 함수의 특징

1. SUBTOTAL 함수는 필터 결과에 포함되지 않은 행을 모두 무시하기 때문에 자동 필터와 함께 많이 사용한다.

2. [데이터]-[부분합]으로 부분합을 계산할 경우 사용되는 11가지의 계산이 SUBTOTAL 함수를 이용한 계산이므로 함수를 수정하여 부분합의 계산을 수정할 수 있다.

3. 범위에 다른 부분합이 있으면 이중으로 계산되는 것을 피하기 위해 중첩된 부분합을 무시한다. 즉, 영역 안에 다른 부분합은 계산하지 않는다.

 

 

=SUBTOTAL(함수 번호, 계산할 범위)

범위를 함수 번호에 해당하는 함수로 계산한다.

예) =SUBTOTAL(9, B5:B10)  =>  B5:B10 범위에 해당하는 숫자들을 합한다.

 

 

함수 번호에 해당하는 함수와 설명

 

 

101~111은 행을 숨길 경우 숨긴 행의 값을 계산하지 않을 때 사용하면 된다.

반면에, SUBTOTAL 함수는 숨긴 열에는 영향을 받지 않는다.

 

 

 

SUBTOTAL 활용 1

 

 

 

1. 자동필터를 적용한 후 화면에 표시되는 데이터만 자동으로 계산되는 예제를 만들어보자.

무게와 단가에 대한 합을 H15셀과 I15셀에 SUBTOTAL 함수로 미리 구해보았다.

여기까지는 SUM을 사용했을 때와 답이 다르지 않다.

 

무게의 합(H15) => SUBTOTAL(9, H3:H14)

단가의 합(I15) => SUBTOTAL(9, I3:I14)

  

 

 

 

 

2. 자동 필터를 적용하기 위해

합계 행을 제외한 데이터를 범위 지정한 후

[데이터]-[필터] 명령을 클릭한다.

 

 

 

 

3. 제목행에 조건에 따라 필터링할 수 있는 단추가 표시된다.

 

 

 

 

4. '지점명'이 '대륙전자'인 데이터만 필터링하기 위해

'지점명'의 단추를 클릭한 후 '제일전자'를 체크 해제한다.

 

 

 

 

5. 자동필터가 실행되어 '대륙전자' 데이터만 표시되고,

무게와 단가의 합계가 화면에 표시된 데이터의 합계만 구해진다.

SUM을 사용했다면 여전히 972, 7246000 이 구해졌을 것이다.

이렇게 SUBTOTAL 함수는 자동필터와 함께 자주 사용된다.

 

 

 

 

SUBTOTAL 활용 2

 

 

 

1. 자동필터링되도 일련번호가 항상 '1, 2, 3...'으로 유지되는 예제를 만들어보자.

B3 셀에 '=SUBTOTAL(3, $C$3:C3)' 식을 입력한 후 아래로 수식을 복사한다.

 

'=SUBTOTAL(3, $C$3:C3)' 식에서 '3'은 함수 'COUNTA'를 의미한다.

범위에 이렇게 사용한 이유는

'$C$3:C3'에서 앞의 'C3'을 절대참조로 고정하여

'C3:C4', 'C3:C5', 'C3:C6', 'C3:C7'과 같이 참조가 변하도록 하기 위해서다.

 

참조에 대해 궁금하다면 함 보자!!  엑셀 셀 참조 (상대참조, 절대참조, 혼합참조) by Y

 

 

 

 

2. 식이 복사되어 아래와 같이 일련번호가 만들어진다.

 

 

 

 

3. 위의 활용 예제에서와 같이 자동필터를 실행하면

눈에 보이는 범위만 자동으로 재계산하여 정상적으로 일련번호가 표시된다.

 

 

이와 같이 SUTOTAL 함수를 자동필터와 함께 사용하면 편리하게 계산할 수 있다.

 

 

 

 

☞ 일련번호 만드는 다른 방법

 

2013/04/29 - 엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y

2013/05/14 - 엑셀 자동채우기 (일련번호, 수식복사, 날짜채우기 등 실습하기) by Y 

 

 

 

 

 

 

 

Posted by Y&S