엑셀의 SUBTOTAL 함수는 필터링된 데이터에서 특정 계산(합계, 평균, 개수 등)을 수행하는 함수입니다.
VBA에서는 WorksheetFunction.Subtotal을 사용하여 특정 데이터 범위의 합계를 구하거나, Range.Subtotal 메서드를 사용하여 자동 소계를 생성할 수 있습니다.
1. 엑셀 SUBTOTAL 함수 기본 구조
|
Application.WorksheetFunction.Subtotal(FunctionNum, Range)
|
|
매개변수
|
설명
|
|
FunctionNum
|
계산 유형 (예: 9 = 합계, 1 = 평균, 2 = 개수 등)
|
|
Range
|
계산할 범위
|
📌 필터링된 데이터에서만 작동하며 숨겨진 행은 계산에서 제외됨
📌 FunctionNum이 100 이상이면 수동 숨김된 행도 제외됨
2. FunctionNum 값과 계산 유형
|
FunctionNum
|
FunctionNum (100+)
|
계산 유형
|
|
1
|
101
|
평균 (AVERAGE)
|
|
2
|
102
|
숫자 개수 (COUNT)
|
|
3
|
103
|
빈 셀 개수 (COUNTA)
|
|
4
|
104
|
최대값 (MAX)
|
|
5
|
105
|
최소값 (MIN)
|
|
6
|
106
|
곱 (PRODUCT)
|
|
7
|
107
|
표준편차 (STDEV)
|
|
8
|
108
|
표준편차 (표본) (STDEVP)
|
|
9
|
109
|
합계 (SUM)
|
|
10
|
110
|
분산 (VAR)
|
|
11
|
111
|
분산 (표본) (VARP)
|
📌 100 이상을 사용하면 사용자가 숨긴 행까지 제외하고 계산됨
3. 필터링된 데이터의 합계 구하기
|
Sub 필터된데이터_합계()
Dim 합계 As Double
합계 = Application.WorksheetFunction.Subtotal(9, Range("B2:B100"))
MsgBox "필터링된 데이터의 합계: " & 합계
End Sub
|
📌 B2:B100 범위의 필터링된 데이터만 합계를 계산
4. 필터링된 데이터의 평균 구하기
|
Sub 필터된데이터_평균()
Dim 평균 As Double
평균 = Application.WorksheetFunction.Subtotal(1, Range("C2:C100"))
MsgBox "필터링된 데이터의 평균: " & 평균
End Sub
|
📌 C2:C100 범위에서 필터링된 데이터의 평균을 계산
5. 사용자가 숨긴 데이터도 제외한 합계
|
Sub 숨김행제외_합계()
Dim 합계 As Double
합계 = Application.WorksheetFunction.Subtotal(109, Range("D2:D100"))
MsgBox "숨겨진 행을 제외한 합계: " & 합계
End Sub
|
📌 109를 사용하면 필터링된 데이터 + 수동 숨긴 행도 제외하고 합계 계산
6. 엑셀 Range.Subtotal을 활용하여 자동 소계 삽입
|
Sub 자동소계추가()
Dim 데이터 As Range
Set 데이터 = Range("A1:D100") ' 데이터 범위 설정
' 기존 소계 제거
데이터.RemoveSubtotal
' A열(1번째 열)을 기준으로 그룹화 후 B열(2번째 열) 합계 계산
데이터.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
End Sub
|
📌 A열을 기준으로 그룹을 만든 후, B열의 합계를 자동으로 삽입
📌 기존 소계를 제거한 후 추가 (RemoveSubtotal 사용)
7. 자동 소계 생성 (여러 개의 열)
|
Sub 자동소계_여러열()
Dim 데이터 As Range
Set 데이터 = Range("A1:E100")
데이터.RemoveSubtotal ' 기존 소계 제거
' A열을 기준으로 B, C, D열의 합계를 계산하여 소계 삽입
데이터.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4)
End Sub
|
📌 A열 기준으로 B, C, D열의 합계를 계산하여 자동 소계를 생성
8. 자동 소계 제거
|
Sub 자동소계_제거()
Dim 데이터 As Range
Set 데이터 = Range("A1:D100")
데이터.RemoveSubtotal ' 기존 소계 제거
End Sub
|
📌 기존에 설정된 소계를 삭제하는 코드
9. 필터링된 데이터의 최대값 구하기
|
Sub 필터된데이터_최대값()
Dim 최대값 As Double
최대값 = Application.WorksheetFunction.Subtotal(4, Range("E2:E100"))
MsgBox "필터링된 데이터의 최대값: " & 최대값
End Sub
|
📌 E2:E100 범위에서 필터링된 데이터의 최대값 계산
10. 필터링된 데이터의 개수 세기
|
Sub 필터된데이터_개수()
Dim 개수 As Double
개수 = Application.WorksheetFunction.Subtotal(2, Range("F2:F100"))
MsgBox "필터링된 데이터의 개수: " & 개수
End Sub
|
📌 F2:F100 범위에서 필터링된 데이터의 개수를 계산
📌 SUBTOTAL 사용 시 주의할 점
- 필터링된 데이터만 계산할 경우 1~11을 사용
- 사용자가 숨긴 행도 제외하려면 101~111을 사용
- Range.Subtotal은 소계를 자동 추가하지만 기존 데이터를 변경할 수 있음
- 필터링되지 않은 전체 데이터를 대상으로 SUBTOTAL을 사용하면 일반 합계와 동일
📝 정리
|
기능
|
VBA 코드 (SUBTOTAL 활용)
|
|
필터링된 데이터 합계
|
Application.WorksheetFunction.Subtotal(9, Range("B2:B100"))
|
|
필터링된 데이터 평균
|
Application.WorksheetFunction.Subtotal(1, Range("C2:C100"))
|
|
숨긴 행 제외한 합계
|
Application.WorksheetFunction.Subtotal(109, Range("D2:D100"))
|
|
자동 소계 추가
|
Range("A1:D100").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
|
|
자동 소계 제거
|
Range("A1:D100").RemoveSubtotal
|
|
필터링된 데이터 최대값
|
Application.WorksheetFunction.Subtotal(4, Range("E2:E100"))
|
|
필터링된 데이터 개수
|
Application.WorksheetFunction.Subtotal(2, Range("F2:F100"))
|
🔥 VBA의 SUBTOTAL을 활용하면 필터링된 데이터만 계산하거나 자동 소계를 설정할 수 있습니다! 🚀