엑셀 데이터를 다루다 보면 필터를 적용하거나 특정 행을 숨겼을 때, 합계나 평균값이 의도와 다르게 계산되어 당황스러운 순간이 찾아옵니다. 일반적인 SUM이나 AVERAGE 함수는 사용자가 눈으로 볼 수 없는 숨겨진 데이터까지 전부 포함하여 계산하기 때문입니다.
이러한 불편함을 해결하기 위해 실무에서는 조건에 맞는 데이터만 골라 계산하는 함수를 찾게 됩니다. 그중 대표주자가 바로 엑셀 SUBTOTAL과 AGGREGATE 함수 차이: 숨겨진 행 제외 계산 공식에 대한 정확한 이해와 활용법입니다.
이번 시간엔 두 함수의 핵심적인 메커니즘과 구체적인 활용 대상을 비교하여 여러분의 칼퇴를 앞당겨 줄 명확한 해답을 제시해 드리겠습니다.
1. 지원 대상 및 상세 자격 요건
본 가이드에서 다루는 ‘지원 대상’은 실무에서 대량의 필터링 데이터를 다루며 오류 없는 보고서를 작성해야 하는 모든 직장인, 회계 담당자, 그리고 데이터 분석가들입니다.
SUBTOTAL 함수의 자격 요건은 단순히 필터로 숨겨진 행을 제외하고 싶은 사용자에게 적합합니다. 수동으로 행을 숨겼을 때의 처리 방식과 필터링을 통한 제외 방식이 나뉘어 있어 세부적인 옵션 선택이 필요합니다.
반면 AGGREGATE 함수는 SUBTOTAL의 상위 호환 개념으로, 오류(#N/A, #VALUE! 등)가 포함된 복잡한 데이터 세트를 다루거나, 숨겨진 행뿐만 아니라 오류 값까지 철저히 제외하고 계산해야 하는 고난도 업무 환경에 최적화되어 있습니다.
따라서 연령이나 소득 기준 대신, 처리하려는 데이터의 오류 포함 여부와 숨김 방식에 따라 본인에게 적합한 함수 사용 자격(?)이 결정된다고 볼 수 있습니다.
2. 지원 혜택 및 상세 금액 비교
여기서 ‘지원 혜택’이란 두 함수를 적재적소에 활용함으로써 절약하게 되는 시간과 데이터 오류 수정 비용을 의미합니다. 아래 표를 통해 두 함수의 기능적 차이를 명확히 비교해 드립니다.
| 구분 | SUBTOTAL 함수 | AGGREGATE 함수 |
|---|---|---|
| 기본 정의 | 지정한 목록이나 데이터베이스의 부분합을 구함 | 다양한 함수 번호와 옵션을 제공하는 통합 집계 함수 |
| 숨겨진 행 처리 | 옵션(1~9 또는 101~109)에 따라 수동 숨김 제외 가능 | 옵션 인수를 통해 수동 숨김, 필터 숨김 등을 세밀하게 제어 |
| 오류 값 처리 | 계산 범위 내 오류(#VALUE! 등)가 있으면 오류 반환 | 옵션 번호로 ‘오류 값 무시’ 지정 가능 (매우 강력함) |
| 함수 확장성 | 기본적인 11가지 연산 제공 (SUM, AVERAGE 등) | PERCENTILE, LARGE 등 19가지 이상의 확장 연산 제공 |
표에서 보듯 AGGREGATE는 오류 값을 무시하는 기능이 포함되어 있어, 데이터 청소가 완전히 끝나지 않은 상태에서도 곧바로 정확한 합계나 평균을 산출해 주는 엄청난 시간 절약 혜택을 제공합니다.
3. 신청 절차 단계별 가이드 및 필수 준비 서류
여기서 ‘신청’이란 실무 엑셀 시트에 해당 공식을 직접 입력하고 적용하는 단계를 의미합니다. 아래의 단계별 가이드를 따라 차근차근 시트에 적용해 보시기 바랍니다.
첫 번째 단계는 SUBTOTAL 함수 적용 방법입니다. 셀에 =SUBTOTAL(9, A1:A100)을 입력하면 필터로 걸러진 데이터만 합산됩니다. 만약 수동으로 숨긴 행까지 제외하려면 첫 번째 인수를 9 대신 109로 변경해야 합니다.
두 번째 단계는 AGGREGATE 함수 적용 방법입니다. 셀에 =AGGREGATE(9, 6, A1:A100) 형태로 입력합니다. 여기서 첫 번째 ‘9’는 SUM(합계)을 의미하고, 두 번째 ‘6’은 계산 범위 내의 오류 값과 숨겨진 행을 동시에 무시하라는 특별 옵션 코드입니다.
필수 준비물은 당연히 정리 대상이 되는 원본 데이터 시트이며, 별도의 추가 프로그램 설치 없이 마이크로소프트 엑셀 프로그램만 실행되어 있으면 곧바로 실습이 가능합니다.
4. 자주 묻는 질문(FAQ) 및 신청 전 필수 주의사항
독자들이 가장 많이 궁금해하는 사항들을 모아 정리했습니다. 함수를 실무에 도입하기 전 반드시 아래의 주의사항과 FAQ를 확인하여 오류를 미연에 방지하시기 바랍니다.
Q. SUBTOTAL과 AGGREGATE 중 어떤 걸 써야 할까요?
단순히 자동 필터로 숨겨진 행만 제외하고 싶다면 익숙한 SUBTOTAL을 써도 충분합니다. 하지만 데이터 중간에 #VALUE! 같은 오류가 섞여 있거나, 수동 숨김과 필터 숨김을 복합적으로 제어해야 한다면 무조건 AGGREGATE 함수를 권장합니다.[필수 주의사항]
SUBTOTAL 사용 시 1~9 사이의 숫자를 쓰면 수동으로 숨긴 행은 계산에 포함됩니다. 수동 숨김 행까지 제외하려면 반드시 101~109 사이의 번호를 입력해야 하므로 이 점을 절대 잊지 마세요!
추가로 AGGREGATE 함수를 사용할 때 옵션 번호(두 번째 인수)를 0부터 7까지 지정할 수 있는데, 상황에 따라 숨긴 행만 무시할지, 오류 값만 무시할지 세부 조정이 가능하므로 엑셀의 도움말(F1)을 한 번 가볍게 읽어보는 것을 추천합니다.
엑셀 SUBTOTAL과 AGGREGATE 함수 차이: 숨겨진 행 제외 계산 공식에 대한 핵심 내용을 완벽히 숙지하셨다면, 이제 여러분의 업무 시트에서 불필요한 수작업과 야근을 확실하게 줄여보실 차례입니다.
지금 바로 본문의 공식을 복사해서 실무 파일에 적용해 보세요. 시간절약연구소는 앞으로도 직장인의 소중한 시간을 지켜주는 실무 꿀팁으로 찾아뵙겠습니다.
