엑셀 subtotal 함수 사용해서 보이는 셀값 구하기
안녕하세요, 엑셀청년입니다. 오늘은 보이는 셀값의 평균, 개수, 합계 등을 구할 수 있는 SUBTOTAL 함수를 배워보려고 합니다. 간단하게 어떻게 쓰이는 함수인지를 아래 그림을 통해 설명을 드리자면 "숨겨진 셀을 제외하고 화면에 보이는 셀만으로 평균, 개수, 합계 등의 계산식을 구하는 함수'입니다. 만약에 여러분들이 어떤 Raw data가 있는데 필터를 사용해서 분류별로 어떤 계산식을 확인해야 한다고 했을 때 쓰일 수 있는 간단한 표현입니다.
예시설명
위와 같이 베스트 셀러 입/출고 현황표가 있습니다. 상사가 실무/전문서적/컴퓨터 이런 분류로 매달 입고/출고에 대한 수치를 알려달라고 합니다. 그렇다면 필터를 걸어서 그때마다 sum함수를 사용해서 결과값을 도출해도 되지만 Subtotal로 더욱 간단하게 값을 도출할 수 있습니다. "필터를 걸었을 때 딱 보이는 셀로만 내가 원하는 계산값이 도출 된다면 얼마나 편할까요?" 바로 이럴때에 엑셀 SUBTOTAL을 사용하는 것입니다.
함수 적용방법
=SUBTOTAL(함수번호,참조대상)
즉, 위에 사진과 같은 명령문은 어떤걸 의미하냐면 "함수번호(2)는 COUNT의미 사진 참조" E7:E21대상으로 셀의 숫자를 계산하겠다라고 보시면 됩니다. 함수번호는 보시면은 1(AVERAGE), 2(COUNT) 등등이기에 자신이 구하고자하는 함수값의 맞게끔 숫자를 집어넣으시면 됩니다. 만약에 내가 SUM을 구하고 싶다면은 9번으로 입력해야겠죠? 이렇게 적용한 값이 아래와 같습니다.
적용하기
위에 사진 아닌 아래사진은 '컴퓨터'로만 필터를 적용한 상태입니다. 위에 결과값을 보면은 전체개수(15개)에서 컴퓨터로만 필터를 걸으니 7개로 바뀌고 전체합계 결과값도 달라진거 보이시죠? 이런식으로 SUBTOTAL 함수를 설정한 다음에 자신이 필터로 걸러내어 원하는 값을 실시간 업데이트 나타낼 수 있습니다. 여기서 하나 주의해야할 점은 엑셀 SUBTOTAL은 필터적용이 아닌 숨겨진 행에 대해서는 함수번호를 다르게 적용해야 합니다.
예를들어 1,2행을 숨겼고 SUM을 구하고 싶어서 9로 함수번호를 적어넣었다해도 숨겨진 값을 포함해서 계산을 합니다. 하지만 만약 숨겨진 행은 제외를 하고싶다면 함수번호를 9가 아닌 109로 적용을 해야합니다. 필터 제외값뿐만 아니라 행에 숨겨진 값 조차 무시(포함 X)면서 계산을 하기 때문에 이점 유의하시면 좋을 것 같습니다.
Subtotal함수_예시 by 엑셀청년.xlsx