엑셀 필터 후 합계가 안 맞을 때, SUBTOTAL 함수 사용법

엑셀로 데이터를 분석하다 보면 필터링은 필수 기능이죠.

특히 위 사진처럼 복잡한 거래 내역이나 판매 데이터를 다룰 때는 더욱 그렇습니다. 문제는 필터링을 한 후에 보이는 데이터들만의 합계를 구하고 싶을 때 생기는데요..

많은 사람들이 습관적으로 'SUM 함수'를 사용하시는데, 이렇게 하면 숨겨진 셀의 값까지 모두 합산되어 버려요. 결국 하나씩 셀을 선택해서 더하는...굉장히 비효율적인 방법을 쓰게 되죠. 바로 이럴 때 필요한 함수가 'SUBTOTAL'입니다.

👉︎ 엑셀 최신판 다운로드

필터링 후 SUM 함수의 함정

위 예시는 가상화폐 거래 내역에서 '쎄타토큰'만 필터링한 결과입니다. 이제 보이는 '정산금액' 컬럼의 합계를 구해야 하는 상황인데.. 만약 SUM 함수를 적용하면 어떻게 될까요?

SUM 함수는 필터로 숨겨진 다른 코인들의 거래 금액까지 모두 포함해서 계산합니다. 정작 원하는 건 '쎄타토큰'만의 합계인데 말이죠. 그렇다고 보이는 셀을 하나씩 클릭해서 더할 수도 없는 노릇이고...

바로 이런 상황에서 SUBTOTAL 함수가 진가를 발휘합니다.

SUBTOTAL 함수란 무엇인가?

SUBTOTAL 함수는 '목록이나 데이터베이스의 부분합을 구해주는 함수'입니다. 쉽게 말해 필터링이나 셀 숨기기로 보이지 않는 데이터는 제외하고, 현재 화면에 보이는 데이터들만 계산해주는 똑똑한 함수죠.

이 함수의 핵심은 첫 번째 인수에 있습니다. 어떤 계산을 할지(합계, 평균, 개수 등)와 함께 숨겨진 셀을 포함할지 말지를 결정하는 번호를 입력해야 해요.

SUBTOTAL 함수의 인수 체계

SUBTOTAL 함수의 인수 체계는 조금 특이합니다. 같은 합계 기능이라도 두 가지 번호가 있어요.

• 9번 : 숨겨진 행 값 포함, 필터링된 값 제외.
• 109번 : 숨겨진 행 값 제외, 필터링된 값 제외.

필터링 상황에서는 9번과 109번이 같은 결과를 보여주지만, 수동으로 행을 숨겼을 때는 결과가 달라집니다. 109번이 더 철저하게 보이지 않는 모든 셀을 제외하죠.

실제로 엑셀에서 =SUBTOTAL( 이라고 입력하면 친절하게 인수 목록이 나타납니다. 합계를 구할 때는 보통 9번이나 109번을 사용하면 되고요.

실전 적용: 필터링된 데이터 합계 구하기

이제 실제로 적용해볼까요? 특정 가상화폐로 필터링한 상태에서 정산금액 합계를 구해보겠습니다. 공식은 간단해요~

=SUBTOTAL(9, F2:F11)

여기서 9는 합계 함수 번호이고, F2:F11은 합계를 구하고 싶은 범위입니다. 필터링으로 숨겨진 다른 데이터들은 자동으로 제외되고, 현재 보이는 쎄타토큰 거래 내역만 합산되는거죠.

검증~ 이거 정말 정확할까?

SUBTOTAL 함수가 정말 정확한지 의심스럽다면 검증해볼 방법이 있어요. 보이는 셀들을 직접 선택해서 상태표시줄의 합계 값을 확인하는 거죠.

마우스로 보이는 데이터들을 드래그해서 선택하면, 엑셀 하단의 상태표시줄에 선택된 셀들의 합계가 자동으로 표시됩니다. 이 값과 SUBTOTAL 함수의 결과가 일치하는지 확인해보세요.

자, 보세요... SUBTOTAL 함수로 계산한 값과 직접 선택해서 확인한 합계가 정확히 일치합니다. 우와~ 이제 믿고 사용할 수 있겠네요.

9번 vs 109번, 언제 다를까?

9번과 109번 인수의 차이를 명확히 보여드리기 위해 실험을 해볼게요. 같은 데이터에서 두 개의 SUBTOTAL 함수를 만들어보았습니다.

• E17 셀: =SUBTOTAL(9, E7:E16)
• F17 셀: =SUBTOTAL(109, F7:F16)

필터링만 적용된 상태에서는 두 결과가 동일하죠. 하지만 이제 특정 행을 수동으로 숨겨보겠습니다.

4번째 행을 수동으로 숨긴 후의 결과입니다. 이제 차이가 확실히 보이죠?

• 9번 인수 : 수동으로 숨긴 행의 값도 포함해서 계산 (1,027,250)
• 109번 인수 : 수동으로 숨긴 행의 값은 제외하고 계산 (1,006,530)

일반적으로는 109번을 사용하는 것이 더 정확합니다. 어떤 방식으로 숨겨진 데이터든 모두 제외하고 싶으니까요.

자주 묻는 질문 (FAQ)

Q1. SUBTOTAL과 SUM 함수의 정확한 차이점은 무엇인가요?

A. SUM 함수는 숨겨진 셀까지 모든 값을 합산하지만, SUBTOTAL은 필터링이나 숨기기로 보이지 않는 셀은 제외하고 계산합니다. 필터링된 데이터만의 합계가 필요할 때는 반드시 SUBTOTAL을 써야 해요.

Q2. 9번과 109번 인수 중 어떤 걸 사용해야 하나요?

A. 109번 사용을 권장합니다. 9번은 수동으로 숨긴 행은 포함하고 필터링된 행만 제외하지만, 109번은 어떤 방식으로든 숨겨진 모든 행을 제외해서 더 정확합니다.

Q3. 필터링하지 않은 상태에서도 SUBTOTAL을 사용할 수 있나요?

A. 네, 가능합니다. 필터링하지 않은 상태에서는 SUM 함수와 동일한 결과를 보여줍니다. 나중에 필터링할 가능성이 있다면 미리 SUBTOTAL을 사용해두는 것도 좋은 방법이에요.

Q4. SUBTOTAL로 합계 말고 다른 계산도 할 수 있나요?

A. 물론이죠. 평균(101/1), 개수(102/2), 최대값(104/4), 최소값(105/5) 등 다양한 계산이 가능해요. 첫 번째 인수에 해당하는 번호만 바꿔주면 됩니다.

Q5. 여러 개의 SUBTOTAL 함수를 중첩해서 사용할 수 있나요?

A. 네, 가능합니다. 예를 들어 =SUBTOTAL(9, A1:A10) + SUBTOTAL(9, B1:B10) 이런 식으로 여러 범위의 부분합을 더할 수 있어요. 다만 SUBTOTAL 함수끼리는 서로를 인식해서 중복 계산하지 않습니다.

Q6. AGGREGATE 함수와는 어떤 차이가 있나요?

A. AGGREGATE 함수는 SUBTOTAL의 상위 버전으로, 오류 값까지 제외할 수 있고 더 많은 함수를 지원합니다. 하지만 복잡하기 때문에 일반적인 용도라면 SUBTOTAL만으로도 충분해요.

SUBTOTAL 함수 활용 팁

SUBTOTAL 함수는 데이터 분석에서 정말 유용한 도구입니다. 특히 다음과 같은 상황에서 꼭 기억해두세요.

• 필터링 후 합계가 필요할 때 : SUM 대신 SUBTOTAL(9, 범위) 사용.
• 행 숨기기까지 고려해야 할 때 : SUBTOTAL(109, 범위) 사용.
• 평균이나 개수가 필요할 때 : 인수표를 참고해서 적절한 번호 선택.

앞으로...복잡한 데이터를 필터링한 후에도 정확한 합계를 쉽게 구할 수 있겠네요. 하나씩 셀을 선택하는 번거로움은 이제 끝!