오늘은 엑셀 Subtotal 함수를 사용해서 필터링된, 즉 화면에 데이터가 보이는 셀들만 계산하는 방법에 대해 알아보겠습니다.
◆엑셀 Subtotal 함수란?
- 목록이나 데이터베이스의 부분합을 계산합니다. 쉽게 말해서 필터링된 데이터, 즉 화면에 보이는 셀들만을 계산해주는 함수입니다.
◆Subtotal 함수 인수 알아보기
1) Function_num(필수요소) : 필터링된 데이터에 사용할 함수를 선택합니다.
필터링된 셀들의 평균(Average)를 구하고 싶으면 '1'을 입력하고 총합계(Sum)를 구하고 싶다면 '9'를 입력합니다. 사용할 수 있는 함수는 아래 목록과 같습니다.
2) Ref1~254(선택요소) : 부분합을 계산할 셀 또는 셀 범위입니다.
◆예제를 통해 알아보기
▲서울, 경기권의 후보 17명의 나이가 나와 있습니다. 여기서 각 지역별 인원수와 평균 나이를 Subtotal 함수를 사용하여 간단하게 구해보겠습니다.
보통 필터를 사용하지 않은 상태에서 해당 값을 구하려면 Countif 와 Averageif를 사용하곤 하는데 약식으로 눈으로 파악하기 위해 필터링을 하여 눈에 보이는 셀들의 값만 구하는 방법입니다.
▲우선 데이터목록 상단의 셀들을 드래그 하여 선택 후 [데이터 탭] > [필터] 항목을 클릭합니다.
▲필터링이 되었다면 이제 값을 구할 셀인 [H3 셀]에 커서를 두고 수식을 입력합니다. 보통은 함수마법사를 사용하기도 하지만 Subtotal 첫 인수인 함수 목록을 보기 위해서는 직접 셀에 수식을 입력하는 방법이 훨씬 편합니다.
수식 '=subtotal(' 까지 입력하면 위 사진처럼 함수 목록이 뜨는데 우리는 인원수를 구할 것이기 때문에 문자가 들어간 셀의 개수를 세주는 COUNTA 함수 넘버링인 '3'을 입력합니다.
▲그 후 ','를 입력하고 두번째 인수에 목록(B4셀부터 B20셀 까지)을 드래그 한 뒤 괄호를 닫습니다.
최종 수식은 '=Subtotal(3,B4:B20)' 이 됩니다.
▲별도의 필터링을 하지 않은 현재 상태에서는 후보가 17명이므로 인원 수에 '17'이 맞게 뜬 것을 확인할 수 있ㅅ브니다.
▲그 후 평균나이를 구할 셀인 [H4 셀]에 커서를 둔 후 수식을 입력합니다.
수식 '=subtotal('까지 입력한 후 함수 목록에서 평균을 구하는 함수인 Average 넘버링인 '1'을 입력합니다.
▲두번째 인수에는 나이가 입력되어 있는 E4셀부터 E20셀까지 드래그 하여 선택한 후 괄호를 닫습니다.
최종 수식은 '=Subtotal(1,E4:E20)' 입니다.
▲엔터를 친 후 확인을 해보면 전체 후보군 17명의 평균나이가 41세가 맞게 출력된 것을 확인할 수 있습니다.
▲이제 subtotal의 본래의 기능을 보기 위해 필터링에 조건을 변경해보겠습니다. 필터링 드롭다운 화살표 중 [지역] 드롭다운 버튼을 클릭합니다.
▲서울권에 거주하는 후보들의 인원수와 평균나이를 보기 위해 '경기'는 체크해제하고 '서울'만 체크한 뒤 [확인] 버튼을 클릭합니다.
▲필터링 되어 '서울' 거주중인 후보들만 목록에 뜹니다. 그럼 H3,4셀 값도 자동으로 서울후보들의 인원수와 평균 나이 데이터로 바뀝니다.
이렇게 Subtotal을 활용하면 가변하는 필터링 데이터들의 부분합을 편리하고 빠르게 파악할 수 있습니다. 특히 보고서 같은 자료를 만들때, 가변하는 데이터들을 수시로 보면서 내용을 조정을 해야 할 때는 SUBTOTAL 함수를 사용하는게 엄청 시간도 절약되고 편합니다.
▲이번에는 경기도권 후보들의 숫자와 평균 나이를 보겠습니다. 다시 지역 필터링 드롭다운 화살표를 클릭한 후 필터링 조건을 '경기'만 선택 후 [확인] 버튼을 클릭합니다.
▲그럼 자동으로 인원수와 평균나이가 변경된 것을 확인할 수 있습니다.
※단 주의할 점은 SUBTOTAL 함수를 사용하여 값을 도출하는 셀은 데이터 목록 위쪽에 두는 것을 추천합니다. 처음처럼 데이터 목록의 왼쪽이라 오른쪽에 두게 되면 가끔 필터링 될 때 셀이 사라져 버리기 때문에 불편합니다.
확인 후 궁금하신 사항은 댓글 남겨주시면 답변 드리겠습니다.
