숨겨진 셀은 계산 안 해요! SUBTOTAL과 AGGREGATE 함수

필터를 걸었는데 합계가 그대로라니?

반갑습니다. 실무 엑셀의 가려운 곳을 긁어드리는 직장인 엑셀 꿀팁 저장소입니다!

지난 포스팅에서는 보조 열 없이 복잡한 계산을 한 방에 끝내는 SUMPRODUCT 함수에 대해 다뤘습니다.

데이터 분석의 기초가 계산이라면, 분석의 핵심은 ‘필터링(Filtering)’이겠죠.

그런데 엑셀 초보 시절, 이런 경험 한 번쯤 있으실 겁니다. 방대한 데이터에서 ‘서울’ 지역만 보고 싶어서 필터를 걸었습니다.

그리고 맨 아래 합계를 확인했는데, 웬걸? 전체 전국 합계가 그대로 떠 있는 겁니다.

이건 엑셀이 고장 난 게 아닙니다. 우리가 흔히 쓰는 SUM 함수는 눈에 보이지 않는(숨겨진) 데이터까지 모조리 더하는 성질이 있기 때문입니다.

오늘은 보이는 셀만 똑똑하게 계산해 주는 엑셀 SUBTOTAL 함수와, 에러 값까지 피해 가는 상위 호환 버전인 AGGREGATE 함수를 마스터해 보겠습니다.

보이는 것만 계산한다: SUBTOTAL 함수

SUBTOTAL은 말 그대로 ‘부분 합(Sub-Total)’을 구하는 함수입니다.

이 함수는 우리가 필터를 걸어서 화면에 데이터를 몇 개만 남겨두면, 딱 그 남은 데이터만 가지고 계산을 수행합니다.

• 기본 공식: =SUBTOTAL(함수번호, 범위)

여기서 ‘함수번호’가 핵심입니다. SUBTOTAL은 덧셈만 하는 게 아니라 평균, 개수, 최댓값 등 다양한 계산을 수행할 수 있는 ‘만능 열쇠’ 같은 존재거든요. 가장 많이 쓰는 번호는 9번(SUM)입니다.

전체 함수 번호 목록이 궁금하시다면 마이크로소프트 SUBTOTAL 공식 문서를 참고하세요. 하지만 실무에서는 9번(합계)과 1번(평균) 정도만 기억해도 충분합니다.

함수 번호 9번과 109번의 차이점 (중요!)

함수를 입력하려고 보면 9-SUM도 있고 109-SUM도 있어서 혼란스러우셨죠? 이 둘의 차이를 명확히 아는 것이 ‘엑셀 고수’로 가는 지름길입니다.

쉽게 정리해 드릴게요. “나는 오직 ‘필터’ 기능만 쓴다”라고 하면 9번을 써도 무방합니다. 하지만 마우스 우클릭으로 [행 숨기기]를 자주 사용한다면 반드시 109번을 써야 숨겨진 값을 빼고 계산합니다. 헷갈린다면 그냥 109번을 쓰는 게 마음 편하겠죠?

오류까지 무시하는 끝판왕: AGGREGATE 함수

SUBTOTAL도 훌륭하지만 치명적인 단점이 있습니다. 계산하려는 범위에 #DIV/0!이나 #N/A 같은 오류가 하나라도 있으면 결과도 오류가 뜹니다.

이때 등장하는 구세주가 바로 AGGREGATE 함수입니다.

엑셀 2010 버전부터 추가된 이 함수는 ‘집계하다’라는 뜻처럼 SUBTOTAL의 모든 기능을 포함하면서 옵션이 더 강력해졌습니다.

• 공식: =AGGREGATE(함수번호, 옵션번호, 범위)

가장 많이 쓰는 꿀조합은 ‘9(합계)’와 ‘6(오류 값 무시)’입니다.
=AGGREGATE(9, 6, 범위)

이렇게 입력하면 숨겨진 행도 무시하고, 중간중간 섞여 있는 에러 값도 쿨하게 무시하고 숫자만 쏙쏙 골라 더해줍니다. 데이터가 지저분할 때 이보다 좋은 함수는 없습니다.

실전 예제: 필터된 매출 현황판 만들기

상사에게 보고서를 낼 때, 필터를 바꿀 때마다 상단의 요약 합계가 자동으로 바뀌도록 세팅해서 보내보세요. “오, 엑셀 좀 하는데?”라는 소리를 듣게 될 겁니다.

1. 데이터 맨 윗줄이나 아랫줄에 요약 행을 만듭니다.
2. 합계 칸에 =SUBTOTAL(9, 합계범위)를 입력합니다.
3. 이제 데이터에 필터를 걸어보세요. 화면에 보이는 항목들의 합계만 실시간으로 반영됩니다.

마무리하며

오늘은 숨겨진 셀은 계산하지 않는 똑똑한 엑셀 SUBTOTAL 함수와 오류까지 처리하는 AGGREGATE 함수에 대해 알아봤습니다.

필터를 자주 사용하신다면 SUM 대신 SUBTOTAL을 쓰는 습관을 들여보세요.

다음 시간에는 필터 버튼을 누를 필요도 없이, 조건에 맞는 데이터만 자동으로 뽑아오는 FILTER 함수로 원하는 데이터만 동적 추출하기 편으로 돌아오겠습니다.

여러분의 칼퇴를 응원합니다!