Excel 에서 소계를 포함하지 않거나 제외하고 값을 합산하려면 어떻게 해야 하나요？
Excel 에서 데이터셋을 작업할 때， 다양한 그룹이나 범주별로 숫자를 정리하고 분석하기 위해 소계를 계산하는 것이 일반적입니다。 그러나 이러한 소계를 추가한 후에는 최종 합계에 소계가 중복 포함되지 않도록 하여 정확한 전체 합계를 구하고자 할 수 있습니다。 문제는 모든 숫자를 직접 더하면 소계도 전체 합계에 포함되어 의도보다 큰 결과가 나온다는 점입니다。 이를 방지하려면 원본 데이터만을 합산하면서 소계 행은 제외하는 방법이 필요합니다。 본 문서에서는 이 문제를 해결하기 위한 몇 가지 실용적인 방법을 소개하여 다양한 상황에서 정확한 결과를 효율적으로 계산할 수 있도록 도와드립니다。
Excel 에서 SUM 함수로 소계를 제외하고 값 합산하기
그룹별 소계를 계산하는 널리 사용되는 방법은 각 관련 그룹 내부 또는 하단에 SUM 함수를 삽입하는 것입니다。 그러나 전체 목록의 전체 합계를 계산하면서 이러한 그룹 소계 행을 무시하려면 추가 단계가 종종 필요합니다。 이를 달성하기 위한 방법은 다음과 같습니다：
1. 다음 스크린샷과 같이 각 그룹 내에서 SUM 함수를 사용하여 해당 소계를 생성합니다：
2. 다음으로， 소계 행을 포함하지 않은 전체 합계를 구하려면 결과를 표시할 빈 셀에 다음 수식을 입력합니다：
=SUM(B2:B21)/2
다음을 누르세요Enter정확한 합계를 얻을 수 있습니다. 이 수식은 관련 그룹 바로 다음에 SUM 함수로 소계를 삽입했기 때문에 원본 데이터의 합계가 두 배로 계산된다고 가정합니다. 2 으로 나누면 중복된 합계가 제거됩니다。 다만 이 방법은 원본 값과 소계의 총합이 원본 데이터 합계의 정확히 두 배가 되는 구조의 데이터셋에 가장 적합합니다。
데이터 구조가 더 복잡하거나 소계 행의 수가 다양하게 변하는 경우， 아래 설명된 대안 방법들이 보다 정확하고 유연한 해결책을 제공할 수 있습니다。
Excel 에서 SUBTOTAL 함수로 소계를 제외하고 값 합산하기
Excel 의 SUBTOTAL 함수는 필터링되거나 표시된 행에 대해서만 계산을 수행하는 내장 기능을 제공합니다。 이는 소계가 포함된 목록 작업 시 특히 유용하며， SUBTOTAL 수식을 설정하여 범위 내 다른 SUBTOTAL 결과를 무시하도록 구성할 수 있으므로 값이 중복 계산되는 것을 방지할 수 있습니다。
이러한 맥락에서 SUBTOTAL 함수를 사용하려면 다음 단계를 따르세요：
1. 각 그룹에 대해 다음과 같이 SUBTOTAL 함수를 입력합니다：
=SUBTOTAL(9,B2:B10)
이는 SUM 연산(함수 번호 9)을 제한된 범위에 적용하는 것으로, Excel 의 기본 제공 '데이터 > 소계‘ 기능의 일부로 자주 사용됩니다。
2. 내부 소계를 제외한 전체 합계를 구하려면 선택한 셀에 다음 수식을 입력합니다：
=SUBTOTAL(9,B2:B21)
다음을 누르세요Enter범위 내 중첩된 SUBTOTAL 함수를 자동으로 무시하고 데이터 값을 합산합니다。 이 방법은 그룹 요약에 SUBTOTAL 함수를 사용하는 목록에 적합하며， 소계가 여러 번 합산되는 위험을 제거합니다。
SUBTOTAL 함수는 소계 셀이 SUM 이 아닌 SUBTOTAL 을 사용하여 생성된 경우에 가장 잘 작동합니다. 또한 행을 필터링하거나 숨길 경우, SUBTOTAL 을 설정하여 표시된(숨겨지지 않은) 데이터만 합산할 수 있으므로 동적 보고 상황에서 유용합니다。
VBA 코드 - 소계 행을 제외하고 값 합산하기
데이터 구조가 복잡하거나 원본 데이터 행만(소계 제외) 자동으로 합산하고자 하는 경우 간단한 VBA 매크로를 활용할 수 있습니다。 이 방법을 통해 특정 서식， 수식 존재 여부 또는 기타 구분 특성을 기준으로 조건에 맞는 행만 프로그래밍 방식으로 식별하고 합산할 수 있습니다。
예를 들어， 소계 행이 수식(SUM 또는 SUBTOTAL 등)으로 계산되고 원본 데이터 행에는 정적 값만 포함된 경우， VBA 코드를 설정하여 열 지정 내 수식이 없는 셀만 합산하도록 할 수 있습니다。 다음은 이를 수행하는 방법입니다：
1. 다음을 클릭하세요개발자 도구>Visual BasicMicrosoft Visual Basic for Applications 창을 엽니다。
2. 새 창에서 다음을 클릭하세요삽입>모듈모듈에 다음 코드를 붙여넣습니다：
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. 다음을 클릭하세요
실행코드를 실행합니다。 데이터가 포함된 범위를 선택하라는 메시지가 나타납니다。 매크로는 선택한 범위 중 수식이 없는 셀만 합산하여 일반적인 소계 행을 효과적으로 건너뜁니다。
필터링 - 필터와 SUBTOTAL 을 사용해 표시된 행(소계 제외)만 합산하기
데이터셋에서 소계 행을 필터링할 수 있는 경우(예: 소계를 식별할 수 있는 레이블, 키워드 또는 패턴이 있는 경우), Excel 의 기본 제공 필터 기능과 SUBTOTAL 함수를 함께 사용하여 표시된(필터링되지 않은) 데이터 행만 합산할 수 있습니다。 이 기법은 소계에 일관된 표시자나 레이블이 있는 경우에 실용적입니다。
1. 데이터셋 내 아무 곳이나 클릭한 후데이터탭으로 이동하여 다음을 클릭하세요필터열에 필터를 활성화합니다。
2. 보조 열의 드롭다운을 클릭하고 「소계」 확인란을 해제하여 소계 행을 숨기고 원본 데이터만 표시합니다。
3. 별도의 셀에 표시된(필터링된) 행만 합산하는 다음 수식을 입력합니다。
=SUBTOTAL(9,B2:B21)SUBTOTAL 함수(함수 번호 9 은 SUM)는 숨겨진 행을 무시하고 현재 표시된 행(즉， 필터링 후 유지된 소계가 아닌 행)의 합계만 반환합니다。 이 해결책은 신속하고 동적이며， 필터를 다시 적용하거나 데이터셋을 확장해도 수식이 자동으로 업데이트됩니다。
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약