이 글의 목적은 엑셀에서 수십만 행 이상의 큰 파일을 빠르게 열고 계산하며 저장하도록 성능을 체계적으로 최적화하는 실무 절차와 설정값을 제공하는 것이다.
1. 성능 튜닝의 전체 구조 이해
엑셀 성능은 입력 데이터 크기, 수식 구조, 서식 복잡도, 추가기능 및 외부 데이터 처리 방식, 하드웨어 자원에 의해 좌우되며 병목을 정확히 진단한 뒤 단계별로 제거하는 방식이 효율적이다.
| 영역 | 주요 병목 | 핵심 레버 | 우선순위 |
|---|---|---|---|
| 파일 구조 | 형식 비효율, 불필요 시트·이름·객체 | XLSB 저장, 불필요 요소 제거 | 상 |
| 수식 | 휘발성 함수, 전체열 참조, 중복계산 | 도움열, 범위 축소, 계산 모드 관리 | 상 |
| 서식 | 무분별한 조건부 서식, 색채우기·테두리 남발 | 규칙 통합, 표준 테마 최소화 | 중 |
| 데이터 입출 | 복사·붙여넣기 반복, 비접힘 쿼리 | Power Query 접힘, 배치 로드 | 상 |
| VBA/자동화 | 셀 단위 반복, 화면 갱신 | 배열 처리, ScreenUpdating Off | 상 |
| 하드웨어 | RAM 부족, 저장장치 속도 | 64비트 엑셀, SSD, 충분한 메모리 | 중 |
2. 파일 형식·구조 최적화
2.1 XLSB로 저장
XLSB는 이진 형식으로 저장·열기 속도가 빠르고 파일 크기를 줄이는 데 유리하다.
주의 : 매크로나 외부 시스템 호환 요구가 없는 한 기본 저장 형식을 XLSB로 두는 것이 안정적이다.
2.2 불필요 요소 제거
- 사용하지 않는 시트, 범위 이름, 피벗 캐시, 차트, 도형, 연결을 정리한다.
- UsedRange를 정리하여 빈 행·열의 잔여 서식을 제거한다.
- 하나의 통합문서에 지나치게 많은 개체가 있으면 책갈피처럼 보조 파일로 분리한다.
'VBA로 이름 정의 일괄 삭제 예시 Sub ClearOrphanNames() Dim n As Name For Each n In ThisWorkbook.Names On Error Resume Next If InStr(1, n.RefersTo, "#REF!") > 0 Then n.Delete On Error GoTo 0 Next n End Sub 2.3 대용량은 시나리오별 파일 분할
입력·정규화·분석을 별도 파일로 모듈화하면 열기와 저장이 빨라지며 손상 위험을 낮출 수 있다.
3. 계산 엔진 최적화
3.1 계산 모드 전략
- 대량 편집 시 계산 모드를 수동으로 전환하고 저장 전 한 번만 재계산한다.
- 워크시트 상단에 재계산 버튼을 만들거나 VBA로 절차화한다.
'계산 모드 제어 템플릿 Sub Calc_Manual_Context() Dim prev As XlCalculation prev = Application.Calculation Application.Calculation = xlCalculationManual Application.ScreenUpdating = False '대량 편집 로직 Application.CalculateFullRebuild Application.ScreenUpdating = True Application.Calculation = prev End Sub 3.2 휘발성 함수 최소화
OFFSET, INDIRECT, TODAY, RAND, NOW, CELL, INFO 등 휘발성 함수는 모든 변경 시 재계산되어 병목이 된다.
- OFFSET은 INDEX로 대체한다.
- INDIRECT는 구조화 참조나 동적 이름범위로 대체한다.
- 날짜·난수는 값 고정 또는 필요 시만 새로고침한다.
'OFFSET 대체 예시 '기존: =SUM(OFFSET(A1,0,0,ROWS(A:A),1)) '대체: =SUM(INDEX(A:A,1):INDEX(A:A,COUNTA(A:A))) 3.3 전체 열 참조 지양
=SUMIF(A:A, ...), =VLOOKUP(…, B:B, …) 같은 전체열 참조는 필요 이상 범위를 스캔하므로 성능을 크게 저하시킨다.
- 정확한 UsedRange에 맞는 동적 범위를 정의한다.
- 테이블(표)로 변환하여 자동 확장을 활용한다.
3.4 반복 조회 최적화
- 대량 VLOOKUP은 XLOOKUP 또는 INDEX+MATCH로 전환하고 필요한 열만 참조한다.
- 다중 조건 합계는 SUMIFS를 사용하되 조건 범위 길이를 동일하게 맞춘다.
- 중복 계산을 줄이기 위해 도움열을 사용하여 복잡 수식을 단계 분해한다.
3.5 배열 수식과 동적 배열 관리
동적 배열의 스필 범위를 가급적 좁게 정의하고 필요 시 스필 결과를 값으로 고정한다.
3.6 사용자 정의 함수(UDF) 최소화
VBA UDF는 셀 단위 호출 오버헤드가 크므로 가능하면 네이티브 함수나 Power Query로 대체한다.
4. 서식·조건부 서식 최적화
4.1 조건부 서식 규칙 통합
- 동일 범위에 다수 규칙이 중첩되면 우선순위 정리와 통합을 수행한다.
- 전체 행·열 단위 규칙 대신 정확 범위 지정으로 계산 영역을 축소한다.
4.2 과도한 서식 제거
- 불필요한 테두리, 채우기, 색조합은 최소화한다.
- 셀 스타일 남용을 피하고 표준 스타일만 유지한다.
주의 : 대용량 시트에서 서식 복사 붙여넣기는 내부 서식 레코드를 기하급수적으로 증가시켜 스크롤과 저장이 급격히 느려진다.
5. 데이터 입출력 속도 향상
5.1 Power Query 로드 전략
- 가능하면 변환 단계가 데이터 원본에서 실행되도록 접힘을 유도한다.
- 참조 테이블은 연결만 만들고 필요 시 피벗테이블이나 DAX로 소비한다.
- 증분 로드가 가능한 원본이면 날짜키 기반으로 범위를 제한한다.
5.2 텍스트·CSV 대량 로드
- 불필요 열 제거 후 로드하여 메모리 사용을 줄인다.
- 형식 추론 자동화 기능을 끄고 명시적 형식을 지정한다.
5.3 피벗 캐시 재사용
여러 피벗이 같은 데이터 원본을 사용하면 캐시를 공유해 메모리 사용을 줄인다.
5.4 계산 결과 값 고정
주기적 리프레시가 불필요한 중간결과는 값으로 치환하고 원본 수식을 보관 시 별도 시트로 분리한다.
6. VBA로 일괄 처리 가속
6.1 화면 갱신·이벤트·알림 차단
'대용량 처리 표준 래퍼 Sub FastBatch(ByVal work As String) Dim prevCalc As XlCalculation prevCalc = Application.Calculation With Application .ScreenUpdating = False .EnableEvents = False .DisplayStatusBar = True .Calculation = xlCalculationManual .DisplayAlerts = False End With
Select Case work
Case "정규화": Call NormalizeLargeSheet
Case "정리": Call CleanupFormats
Case Else: '업무별 라우팅
End Select
With Application
.DisplayAlerts = True
.Calculation = prevCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
6.2 배열로 읽고 쓰기
셀을 한 건씩 접근하는 대신 Variant 배열로 한 번에 읽고 계산 후 일괄 쓰기 한다.
Sub NormalizeLargeSheet() Dim rng As Range, v, i As Long Set rng = Sheet1.Range("A1").CurrentRegion v = rng.Value2 For i = 2 To UBound(v, 1) If v(i, 1) = "" Then v(i, 1) = v(i - 1, 1) Next i rng.Value2 = v End Sub 6.3 Find와 Match의 혼합 사용
키 인덱스가 있는 경우 Range.Find로 시작 위치를 잡고 Application.Match로 대량 매칭을 수행한다.
6.4 고급 필터와 사전(Dictionary)
Scripting.Dictionary로 중복 제거와 그룹화를 수행하면 속도가 향상된다.
Sub DedupWithDict() Dim dict As Object, r As Range, v, i As Long Set dict = CreateObject("Scripting.Dictionary") v = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value2 For i = 1 To UBound(v, 1) dict(v(i, 1)) = 1 Next i Range("C2").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.Keys) End Sub 7. 데이터 모델과 DAX 활용
7.1 데이터 모델 로드
수백만 행은 데이터 모델에 적재하고 피벗테이블로 분석하는 것이 효율적이다.
7.2 관계와 키 설계
- 별모양 스키마를 구성하고 팩트 테이블을 얇게 유지한다.
- 정수형 서러게이트 키를 사용해 메모리 효율을 높인다.
7.3 측정값 분리
계산 열보다 측정값을 우선 사용하여 저장 공간을 줄인다.
8. 메모리·저장장치·플랫폼
8.1 64비트 엑셀과 메모리
대용량 처리에는 64비트 엑셀이 유리하며 시스템 RAM을 충분히 확보해야 한다.
8.2 SSD 사용
임시 파일과 캐시가 SSD에서 빠르게 동작하므로 저장·열기 시간이 단축된다.
8.3 외부 연동의 배치화
SharePoint나 네트워크 드라이브를 사용할 때는 로컬에 임시 저장 후 배치 업로드하는 전략이 유리하다.
9. 실무 체크리스트
| # | 점검 항목 | 권장 설정·조치 | 검증 방법 |
|---|---|---|---|
| 1 | 파일 형식 | XLSB 저장 | 저장 시간 비교 |
| 2 | UsedRange | 빈 서식 제거 | 파일 크기 감소 확인 |
| 3 | 계산 모드 | 작업 중 수동, 종료 시 재계산 | 상태표시줄 확인 |
| 4 | 휘발성 함수 | INDEX, 구조화 참조로 대체 | 재계산 시간 측정 |
| 5 | 조건부 서식 | 규칙 통합 및 범위 축소 | 서식 관리자 점검 |
| 6 | Power Query | 접힘 유지, 불필요 열 제거 | 미리 보기 단계 성능 체크 |
| 7 | 피벗 캐시 | 공유 캐시 사용 | 피벗 옵션 확인 |
| 8 | VBA 처리 | 배열 읽기·쓰기, 화면 갱신 Off | 실행 시간 로깅 |
| 9 | 데이터 모델 | 관계형 모델로 적재 | 피벗 응답속도 확인 |
| 10 | 하드웨어 | 64비트 엑셀, SSD, 충분한 RAM | 작업관리자·자원 모니터 |
10. 오류·손상 방지 팁
- 작업 단계마다 버전 넘버로 저장하고 자동 복구 파일 위치를 확인한다.
- 외부 링크는 상대 경로보다 명시적 연결 관리로 유지한다.
- 매크로 서명과 신뢰 위치를 설정하여 보안 경고로 인한 지연을 줄인다.
11. 사례별 최적화 레시피
11.1 100만 행 로깅 데이터 분석
- CSV를 Power Query로 로드하고 필요 열만 선택한다.
- 타임스탬프를 정수 키로 변환하여 범위 필터를 적용한다.
- 데이터 모델에 로드 후 측정값으로 집계한다.
- 보고서는 피벗과 슬라이서만 사용하여 경량 유지한다.
11.2 복잡한 수식 기반 보고서
- 모든 OFFSET, INDIRECT 제거 및 INDEX·XLOOKUP으로 교체한다.
- 도움열로 단계 계산을 분리하여 중복 호출을 제거한다.
- 조건부 서식 규칙을 두세 개로 통합한다.
- XLSB로 저장하고 계산 수동 모드로 편집 후 재계산한다.
11.3 VBA로 대량 변환
- 입력 범위를 한번에 배열로 읽는다.
- 변환 로직을 메모리에서 수행한다.
- 결과를 한 번에 써서 I/O 호출을 최소화한다.
12. 진단과 측정
최적화는 측정과 함께 진행해야 한다.
- 파일 열기·저장·재계산 시간을 초 단위로 기록한다.
- 계산 상태표시줄 메시지와 성능 모니터로 CPU·메모리 사용을 확인한다.
- 단일 변경 후 영향도를 평가하고 되돌릴 수 있도록 버전 관리를 수행한다.
13. 스크립트·매크로 스니펫 모음
'실행 시간 측정 유틸리티 Function Tick() As Double Tick = Timer End Function
Sub ProfiledCalc()
Dim t As Double: t = Tick
Application.CalculateFull
Debug.Print "Recalc sec:", Format(Tick - t, "0.00")
End Sub
'UsedRange 축소용 포맷 제거 Sub TrimFormats() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.UsedRange ws.Cells.ClearFormats ws.UsedRange Next ws End Sub '조건부 서식 규칙 범위 통일 Sub CF_Consolidate(ByVal wsName As String, ByVal addr As String) Dim ws As Worksheet: Set ws = Worksheets(wsName) Dim fr As FormatCondition For Each fr In ws.Cells.FormatConditions fr.ModifyAppliesToRange ws.Range(addr) Next fr End Sub 14. 운영 가이드
- 원본 데이터는 변경하지 않고 변환층을 별도 시트 또는 파일로 둔다.
- 정기 작업은 배치 스케줄로 한 번에 실행한다.
- 공유 환경에서는 읽기 전용 열기와 결과만 배포하여 충돌을 방지한다.
FAQ
대용량 파일에서 가장 먼저 할 일은 무엇인가
파일을 XLSB로 저장하고 계산 모드를 수동으로 바꾼 뒤 UsedRange 정리와 조건부 서식 규칙 통합을 수행하는 것이 우선이다.
휘발성 함수를 반드시 제거해야 하는가
반드시 제거하지는 않으나 병목의 주요 원인이므로 사용 위치를 제한하고 대체 함수를 우선 고려해야 한다.
Power Query와 데이터 모델 중 무엇이 더 빠른가
데이터 변환에는 Power Query가 적합하며 집계·분석에는 데이터 모델이 효율적이다.
하드웨어 업그레이드가 언제 필요한가
재계산 중 메모리 부족 경고가 발생하거나 디스크 페이징이 빈번하면 64비트 엑셀과 RAM 증설이 필요하다.
VBA와 수식 중 어떤 것이 더 빠른가
대량 반복 변환은 VBA 배열 처리 또는 Power Query가 빠르며 집계는 수식이나 데이터 모델이 간결하다.