이 글의 목적은 엑셀에서 부분합(Subtotal) 기능 사용 후 원본 데이터 정렬이 어긋나거나 행이 섞이는 오류를 신속하고 정확하게 복구하도록 실무 중심 절차와 체크리스트, 예방 규칙, 대체 방법을 체계적으로 제공하는 것이다.
문제 개요와 증상 정의
부분합 기능을 사용하면 엑셀이 자동으로 그룹 윤곽(Outline)을 만들고 합계 행을 삽입하며, 내부적으로 숨김 처리와 레벨별 표시를 제어한다. 이 상태에서 전체 범위를 고려하지 않은 정렬, 접힌 상태에서의 정렬, 합계 행 포함 정렬, 병합 셀과 빈 행이 섞인 상태에서 정렬을 수행하면 원본 데이터가 섞이거나 헤더가 데이터로 내려가고 같은 키를 가진 레코드가 분리되는 문제가 발생한다. 주 증상은 다음과 같다.
- 정렬 후 합계 행이 데이터 중간으로 이동하거나 데이터 행과 합계 행이 뒤섞이는 현상이 발생한다.
- 필드 간 값 매칭이 깨져 열 간 의미가 꼬인다.
- 필터·정렬 해제가 불가하거나 “선택 영역 확장” 경고가 반복 노출된다.
- 표(Table)와 범위가 혼용된 시트에서 일부 구간만 정렬되어 데이터 무결성이 훼손된다.
주의 : 부분합이 적용된 시트에서 정렬은 항상 전체 데이터 영역을 확장하여 수행해야 안전하다. “선택 영역 확장” 경고가 나오면 반드시 확장을 선택해야 한다.
빠른 복구 절차(현상 발생 즉시)
- 현재 시트 백업을 먼저 수행한다. 동일 통합문서에 복사하거나 새 파일로 저장한다.
- 그룹 윤곽 레벨을 모두 펼침한다. 데이터 탭 → 윤곽선에서 레벨 3·2·1 대신 “모두 표시”를 선택하거나 왼쪽의 레벨 숫자에서 가장 큰 수를 클릭한다.
- 부분합 합계 행을 제외하고 원본 데이터만 분리한다.
- 데이터 탭 → 부분합 → 모두 제거를 눌러 자동 삽입된 합계 행과 윤곽을 제거한다.
- 제거가 불가하거나 수동 삽입 합계와 섞였을 경우 아래 “강제 정리 VBA” 또는 “고급 필터” 절차를 사용한다.
- 병합 셀·빈 행·빈 열 제거를 수행한다.
- 홈 탭 → 찾기 및 선택 → 이동 옵션 → 빈 셀 → 우클릭 → 삭제 → 전체 행 또는 전체 열을 선택한다.
- 병합 셀은 홈 탭 → 맞춤 → 셀 병합 해제 후 데이터 정규화를 수행한다.
- 정렬 경고 대처를 활성화한다.
- 정렬 버튼 클릭 시 팝업이 나오면 반드시 선택 영역 확장 → 정렬을 선택한다.
- 고급 정렬에서 내 데이터에 머리글이 있습니다를 정확하게 지정한다.
- 키 열 기준 다중 레벨 정렬을 적용한다.
- 예: 거래일자 → 고객코드 → 품목코드 순으로 오름차순 정렬한다.
- 정렬 기준이 불명확하면 원래 보고서의 페이징·소계 기준을 역추적해 키를 구성한다.
- 정렬 후 레코드 일관성 검증을 수행한다.
- 보조열로
=A2&"|"&B2&"|"&C2같은 키를 구성하고 이전 백업본과 COUNTIF 또는 VLOOKUP/XLOOKUP으로 레코드 매칭을 검증한다.
- 보조열로
원인별 대응 전략(체크리스트)
| 원인 | 설명 | 해결 절차 | 예방 팁 |
|---|---|---|---|
| 합계 행 포함 정렬 | 부분합 합계/소계 행이 데이터 행과 동일 범위에 포함되어 이동한다. | 부분합 메뉴에서 모두 제거 후 정렬 수행 또는 합계 행을 별도 시트로 분리한다. | 합계는 피벗테이블이나 보고용 별도 영역에서 계산한다. |
| 접힌 레벨 상태 정렬 | 윤곽 레벨이 2 또는 1로 접혀 있을 때 보이는 행만 정렬되어 순서가 꼬인다. | 정렬 전 모두 표시로 펼친다. | 정렬은 항상 레벨 전체 표시 상태에서 수행한다. |
| 부분 선택 정렬 | 열 하나 또는 일부 블록만 선택하고 정렬하여 열 간 매칭이 깨진다. | 정렬 경고에서 선택 영역 확장을 선택한다. | Ctrl+A로 테이블 전체를 선택하고 정렬한다. |
| 머리글 인식 오류 | 헤더 행이 공백·병합·중복으로 인해 헤더 미인식 상태가 된다. | 헤더를 단일 행으로 통일하고 병합을 해제한다. | 보고용 줄바꿈은 셀 내 줄바꿈으로 대체한다. |
| 표와 범위 혼용 | 일부는 표(ListObject)이고 일부는 일반 범위여서 정렬 기준이 분리된다. | 모두 표로 변환하거나 모두 범위로 통일한다. | 데이터는 항상 표 기능을 사용해 관리한다. |
| 숨김 행/필터 잔존 | 필터가 남아 보이는 행만 정렬된다. | 데이터 → 지우기로 모든 필터를 해제한다. | 정렬 전 필터 해제는 기본 습관으로 유지한다. |
| 빈 행/열 포함 | 연속 범위가 끊겨 일부 구간만 정렬된다. | 빈 행·열을 삭제하고 연속 범위를 만든다. | 입력 규칙으로 필수 키의 공란을 차단한다. |
안전한 정렬 절차(표 기반 데이터 권장)
- 데이터 범위를 클릭하고 Ctrl+T로 표로 변환한다. “머리글 포함”을 확인한다.
- 부분합이 이미 있는 경우 부분합 모두 제거 후 표로 변환한다.
- 데이터 탭 → 정렬 → 수준 추가로 다중 키 정렬을 구성한다.
- 정렬 옵션에서 상하/좌우 방향을 확인하고 한국어·영문 지역의 대소문자 구분 옵션을 점검한다.
- 정렬 후 순번 검증 열(1,2,3…)과 해시 키(여러 필드 결합)를 만들어 전후 비교한다.
-- 보조열 예시(키 결합) =TEXT([@거래일],"yyyymmdd") & "|" & [@고객코드] & "|" & [@품목코드]
-- 전후 일치 검증
=IF(COUNTIF(백업시트!$Z:$Z,[@키])=1,"OK","CHECK")부분합 사용을 유지해야 할 때의 정렬 전략
보고서 형태 유지가 필요한 경우 부분합 행을 유지하되 원본 레코드 정렬은 별도 복제본에서 수행한다. 절차는 다음과 같다.
- 원본 데이터 범위를 별도 시트에 값 붙여넣기로 복제한다.
- 복제본에서 정렬·가공을 수행한다.
- 필요 시 VLOOKUP/XLOOKUP으로 원본 보고서와 값만 연결한다.
주의 : 부분합 행을 포함한 범위에 직접 정렬을 적용하면 보고서 행 번호와 레코드 일치가 깨질 수 있다. 보고서와 데이터 편집은 분리하여 관리한다.
강제 정리 VBA(합계 행 제거·윤곽 해제·정렬 복구)
자동 부분합 제거가 불가하거나 혼합 구조에서 수동 합계가 섞인 경우 아래 매크로를 사용하여 합계 패턴을 탐지하고 윤곽을 해제한 뒤 안전 정렬을 수행한다.
Sub CleanSubtotalAndSort() Dim ws As Worksheet, lo As ListObject Dim lastRow As Long, lastCol As Long Dim rng As Range, hdr As Range
Set ws = ActiveSheet
On Error Resume Next
' 1) 윤곽 해제
ws.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
ws.Cells.ClearOutline
On Error GoTo 0
' 2) 자동 부분합 제거 시도
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
Cells.Select
Selection.RemoveSubtotal
' 3) 범위 결정
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
Set hdr = rng.Rows(1)
' 4) 합계/소계 텍스트 행 제거(첫 열 기준 "합계","소계" 탐지)
Dim r As Long
For r = lastRow To 2 Step -1
If LCase$(ws.Cells(r, 1).Value) Like "*합계*" Or LCase$(ws.Cells(r, 1).Value) Like "*소계*" Then
ws.Rows(r).Delete
End If
Next r
' 5) 병합 해제
If rng.MergeCells Then rng.UnMerge
' 6) 빈 행 삭제
For r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then ws.Rows(r).Delete
Next r
' 7) 표 변환
On Error Resume Next
Set lo = rng.ListObject
On Error GoTo 0
If lo Is Nothing Then
ws.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "T_Data"
End If
' 8) 다중 키 정렬(예: A열, B열, C열)
With ws.ListObjects(1).Sort
.SortFields.Clear
.SortFields.Add Key:=ws.ListObjects(1).ListColumns(1).DataBodyRange, Order:=xlAscending
.SortFields.Add Key:=ws.ListObjects(1).ListColumns(2).DataBodyRange, Order:=xlAscending
.SortFields.Add Key:=ws.ListObjects(1).ListColumns(3).DataBodyRange, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub주의 : 자동 탐지 기준이 한국어 “합계·소계” 문자열에 기반한다. 사용자 정의 텍스트를 사용했다면 코드의 패턴을 변경해야 한다.
수식 기반 부분합과 정렬의 충돌 해결
SUBTOTAL 함수는 필터로 숨긴 행을 제외할 수 있어 보고서에 유용하다. 하지만 정렬은 합계 영역과 분리해야 한다. 권장 구조는 다음과 같다.
- 데이터 표는 원시 데이터로 관리한다.
- 요약은 별도 영역 또는 피벗테이블에서 SUBTOTAL/AGGREGATE로 계산한다.
- 정렬은 원시 데이터 표에서만 수행한다.
-- 숨김 행을 제외한 합계 예시 =SUBTOTAL(9, T_Data[금액]) ' 9=SUM
-- 오류 무시 집계가 필요할 때
=AGGREGATE(9, 6, T_Data[금액])
' 옵션 6 = 숨김 행, 오류 값 무시Power Query·피벗테이블로의 전환
부분합으로 인한 정렬 오류는 설계상의 한계에서 비롯된다. 데이터가 지속적으로 누적되는 환경에서는 다음 전환이 안정적이다.
- Power Query로 원본을 불러와 정규화, 형 변환, 열 분할, 누락 처리 후 표로 로드한다. 정렬은 표에서 수행하고 보고는 피벗으로 제공한다.
- 피벗테이블로 그룹화와 합계를 처리하고, 원본 정렬은 피벗 필드 기준으로 대체한다.
현업 점검 체크리스트
- 정렬 전 필터 해제, 모두 표시, Ctrl+A 전체 선택을 습관화한다.
- 보고서용 합계는 별도 영역에서 계산한다.
- 병합·빈 행 사용을 지양한다. 필요 시 시각적 병합은 셀 서식의 가운데 맞춤으로 대체한다.
- 표 기능을 기본 데이터 컨테이너로 사용한다.
- 정렬 전후 순번/키 검증을 수행한다.
자주 묻는 질문(실무형)
FAQ
부분합을 제거하면 계산 결과가 사라지나?
자동 부분합으로 삽입된 소계·합계 행은 제거 시 사라진다. 원본 데이터는 보존된다. 보고 목적이라면 피벗테이블 또는 별도 요약 영역으로 대체하여 유지한다.
접힌 상태에서 정렬해도 될까?
안 된다. 접힌 상태에서는 보이는 행만 정렬되어 레코드 매칭이 깨진다. 반드시 모두 표시 후 정렬한다.
정렬 경고 창에서 “현재 선택 영역만 정렬”을 골라도 문제가 없나?
대부분의 경우 문제가 발생한다. 같은 행의 다른 열이 이동하지 않아 필드 매칭이 깨진다. 항상 선택 영역 확장을 사용한다.
표에서 SUBTOTAL을 함께 써도 안전한가?
가능하다. 다만 합계 수식 범위를 표 외부에 두고, 정렬은 표 내부에서만 수행한다. 보고 영역과 데이터 영역을 분리하면 안전하다.
피벗테이블로 완전 대체가 가능한가?
대부분 가능하다. 그룹화·소계·필터·정렬을 안전하게 제공한다. 단, 피벗 외부 참조나 사용자 정의 행 삽입은 피한다.
데이터가 이미 심각하게 섞였을 때 복구 방법은?
백업본과의 키 매칭으로 복구한다. 백업이 없으면 해시 키를 구성해 중복·불일치를 탐지하고 원본 제공처에서 기준 정렬 키를 확보한 뒤 재정렬한다. 필요 시 VBA로 패턴 기반 복구를 시도한다.
현장용 표준 운영 절차(SOP) 예시
- 원시 데이터 취합 → 표 변환 → 유효성 검사(키 공란 금지) → 버전 저장을 수행한다.
- 보고서 생성은 피벗 또는 별도 영역에서 처리한다.
- 정렬·필터·가공은 원시 데이터 표에서만 수행한다.
- 일일 작업 전후에 해시 키 비교표를 자동 생성해 무결성을 검증한다.
-- 일자·고객·품목 키 해시 =HASH( TEXT([@일자],"yyyymmdd") & "|" & [@고객] & "|" & [@품목] )
-- 전후 무결성 비교(가정: 백업시트의 키 목록이 Z열)
=IF(COUNTIF(백업시트!$Z:$Z,[@키])=1,"OK","MISMATCH")정렬 품질 자동 점검용 규칙(데이터 유효성/조건부서식)
- 필수 키 열에 공란 입력 금지 유효성 규칙을 설정한다.
- 조건부 서식으로 키 중복 또는 누락을 실시간 표시한다.
-- 유효성: 공란 금지(예: 고객코드) 수식: =LEN(TRIM([@고객코드]))>0
-- 조건부서식: 키 중복 감지(열 Z가 키)
수식: =COUNTIF($Z:$Z,$Z1)>1사례로 보는 복구 시나리오
사례 A: 합계 행 포함 정렬
문제: 소계 행이 데이터 중간으로 이동했다. 조치: 부분합 모두 제거 → 표 변환 → 다중 정렬 → 키 검증으로 복구한다.
사례 B: 접힌 상태에서 정렬
문제: 보이는 레벨만 정렬되어 고객별 묶음이 해체되었다. 조치: 모두 표시 후 전 범위 정렬 → 키 재정렬 → 피벗 보고로 전환한다.
사례 C: 범위 단편 정렬
문제: 한 열만 정렬하여 열 간 매칭이 깨졌다. 조치: 백업본과 키 기준으로 재매칭 → 잘못 정렬된 열 복구 → 유효성 규칙 도입으로 재발 방지한다.
결론
부분합은 보고서 작성에는 편리하나 정렬과 공존하기 어렵다. 원본 데이터는 표로 표준화하고, 합계·요약은 피벗 또는 별도 영역에서 처리하는 것이 장기적으로 가장 안전하다. 이미 꼬인 경우에는 부분합 제거, 윤곽 해제, 표 통일, 전체 확장 정렬, 키 검증 순으로 복구하면 된다.