엑셀

엑셀 subtotal 화면에 보이는 값만 합하기

콘파냐 2015. 10. 11. 23:41
반응형

subtotal 함수는 엑셀에서 활용도가 높음 함수에 속한다. 잘 알다시피 sum 함수와 마찬가지로 subtotal 함수로도 합을 구할 수 있는데, 제목에도 썼듯이 보통 데이타가 화면에 보이는 경우만 더하는 용도로 subtotal을 사용한다. 그러면 화면에 안 보이는 경우는 어떤 경우가 있나? 필터를 통해 필터를 한 경우 테이블의 원래 데이타를 지운 경우가 아닌 조건에 따라 필터링을 한 경우다. 그럼 사용법을 알아보자.

SUBTOTAL

사용법을 알아보기 전에 위 그림에 표시된 두 합의 결과를 비교해보자.

위와 같이 기본급의 합에는 SUM을, 상여급의 합에는 SUBTOTAL을 사용했다. 이젠 여기에다 자동필터를 사용할 건데 자동필터에 대한 내용은 다음을 살펴보자. (우선 표의 아무 셀을 선택하고 Ctrl+Shift +L 을 동시에 눌러보자)

2015/07/04 - [엑셀] - 엑셀 고급필터 조건 넣기 및 활용


위와 같이 필터가 생겼을 것이다. 우리가 찾고자 하는 것이 전산 특기의 기본급의 합과 상여급의 합이라고 하자.


특기 필터를 눌러서 전산만 체크한 뒤 확인을 누르고 합계를 확인해 보자.

이런 경우 전산 특기에 해당하는 합계들이 필요할텐데 sum 함수의 경우는 필터링을 하기 전이나 후나 값이 같다. SUBTOTAL함수의 경우는 전산에 해당하는 사람의 상여급의 합을 제대로 나타낸다.

 

SUBTOTAL사용방법

이 함수를 사용해야 하는 필요성은 위에서 알아보았다. 그런데 subtotal은 단순히 합을 구하는용도로만 사용되는 것이 아니다. 사용법은 SUM과 비슷한데 인수가 하나 더 있다. 첫 번째 인수에 9라고 쓰여 있는데 여기에 쓰이는 값은 아래와 같이 정해져 있다. function_num이라고 하는 이 인수는 일종의 옵션 버튼이라고 생각하면 되고 합을 구하는 기능(9)은 11가지 종류의 함수중 한가지인 것이다.

function_num의 이름은 말 그대로 함수로 존재하는 녀석들이다. 우리가 사용한 9번은 SUM인 경우다. 즉 SUBTOTAL은 합 뿐만 아니라, 평균, 셀의 개수, 최대값, 최소값 등 다양한 옵션을 가지고 있다. 어짜피 위와 같이 자동완성 형식으로 펼쳐지므로 외울 필요는 없다.

1~11번과 101~ 111까지는 함수 이름이 중복되는데, 이렇게 나눈 이유는 행 숨기기와 열 숨기기와 관계가 있다.

1~11 : 행 숨기기 또는 열 숨기기를 해도 여전히 값이 포함됨

101~111 : 이 경우는 숨기기를 하면 값이 포함되지 않는다.

사실 좀 헷갈리고 숨기기를 권장하지 않는다 왜냐면 다음과 같은 경우 때문이다.

 

필터링을 한 후 숨기기를 한 경우(엑셀 2007의 경우)

전산으로 필터링한 화면이다. 여기에 7행을 숨기기를 해보겠다.

필터링 후 행을 숨겼고, (SUBTOTAL의 첫 번째 인수가 행,열 숨기기에 영향 받지 않는 9지만) 위와 같이 합계에서 숨긴 행의 값이 빠져버린 결과가 나온다. 필터링을 하지 않고 그냥 숨기기만 한 경우는 아래와 같이 합계에 영향을 미치지 않는데 말이다.

필터링은 하지 않고 최만수의 데이터만 남기고 모두 숨기기한 경우, 숨긴 행들이 합계에 영향을 미치지 않은 것을 알 수 있다. 버그인지 아닌지 알 수 없지만, 2007에서는 이런 현상이 나타난다. 다른 버전에서도 한번 확인해 보길 바란다. 그리고 너무 복잡하게 사용할 필요는 없다. 가능하면 심플하게 필요한 기능만 사용하는 것이 데이터 관리에 좋으니 말이다.

반응형