이 글의 목적은 엑셀에서 수십만 행 이상의 대량 데이터를 안정적이고 빠르게 처리하기 위한 구조 설계, 도구 선택, 성능 최적화 절차를 체계적으로 제시하여 현장에서 즉시 적용 가능하도록 돕는 것이다.
1. 대량 데이터 처리 전략 개요
엑셀에서 대량 데이터를 다룰 때 가장 중요한 것은 “어디서 연산할 것인가”를 먼저 결정하는 것이다. 동일한 결과라도 수식, 파워쿼리, 데이터 모델(파워피벗), 피벗테이블, VBA 등 여러 경로가 존재하며, 데이터 규모와 갱신 빈도에 따라 최적의 경로가 달라진다. 다음 원칙을 우선 적용한다.
- 데이터 적재·정제(ETL)는 파워쿼리로 처리한다.
- 집계·조인은 데이터 모델(파워피벗) 또는 원본 DB로 위임한다.
- 시트에 표시하는 레코드는 최소화하고 표(Excel Table)로 관리한다.
- 수식은 “휘발성 함수 최소화, 스필·벡터화 우선, 보조열 활용”을 원칙으로 한다.
- 보고서는 피벗테이블·피벗차트로 구성하고, 필요 시 큐브함수로 세부 출력한다.
주의 : 데이터가 1,048,576행을 초과하는 경우 시트에 직접 적재하지 말고 파워쿼리→데이터 모델에 로드하여 피벗 또는 큐브함수로 표시해야 한다.
2. 파일 형식과 플랫폼 선택
파일 형식은 성능과 안정성에 직접적인 영향을 준다. 다음 표를 참고한다.
| 형식 | 특징 | 권장 용도 |
|---|---|---|
| .xlsx | 기본 Office Open XML, 압축 우수하나 재계산 시 느릴 수 있다 | 중소 규모 데이터, 수식 중심 작업 |
| .xlsb | 바이너리 저장, 열기/저장 및 계산 속도 유리 | 대용량 수식 통합문서, 반복 저장 환경 |
| .xlsm | 매크로 포함, 성능은 .xlsx와 유사 | VBA 자동화 포함 문서 |
주의 : 32비트 엑셀은 대형 모델에서 메모리 한계에 부딪히기 쉽다. 가능하면 64비트 엑셀을 사용한다.
3. 데이터 적재와 정제: 파워쿼리(ETL) 모범 절차
파워쿼리는 대량 데이터 처리의 핵심 도구이다. 단계별 모범 절차는 다음과 같다.
- 원본에 연결한다(폴더의 다중 CSV, 데이터베이스, 웹 API 등).
- 형식 지정(형 변환), 필터링, 열 선택, 오류 처리, 중복 제거를 쿼리 단계로 기록한다.
- 필요 시 매개변수(날짜, 경로, 고객코드)를 노출하여 증분 로드 또는 기간별 로드를 구성한다.
- 결과는 “시트에 로드”가 아닌 “데이터 모델에 로드”로 전송한다.
증분 로드 패턴의 간단한 Power Query M 예시는 다음과 같다.
// 매개변수: P_StartDate, P_EndDate let Source = Sql.Database("SRV01","DWH", [Query=" SELECT OrderDate, CustID, SKU, Qty, Amount FROM FactSales WHERE OrderDate >= @P_StartDate AND OrderDate < @P_EndDate "]), #"Type" = Table.TransformColumnTypes(Source,{ {"OrderDate", type date},{"CustID", type text}, {"SKU", type text},{"Qty", Int64.Type},{"Amount", type number} }), #"RemoveErrors" = Table.RemoveRowsWithErrors(#"Type"), #"KeepCols" = Table.SelectColumns(#"RemoveErrors",{"OrderDate","CustID","SKU","Qty","Amount"}) in #"KeepCols"주의 : 파워쿼리 단계에서 정렬·조인은 가능한 한 원본 DB에 위임되도록 한다. 네이티브 쿼리 또는 “변환 유지(Enable load to data source)” 원칙을 지킨다.
4. 데이터 모델(파워피벗)과 DAX 집계
데이터 모델은 열 지향 컬럼 저장소이므로 대량 집계에 강하다. 설계 요령은 다음과 같다.
- 스타 스키마: 사실 테이블(거래)과 차원 테이블(날짜, 제품, 고객)로 분리한다.
- 관계는 단방향을 기본, 필터 방향은 최소화한다.
- 측정값(Measure)로 집계 로직을 정의하고, 계산열은 최소화한다.
대표적인 DAX 측정값 예시는 다음과 같다.
매출 := SUM ( FactSales[Amount] )
전년동기매출 :=
CALCULATE ( [매출], DATEADD ( 'DimDate'[Date], -1, YEAR ) )
고객당매출 :=
DIVIDE ( [매출], DISTINCTCOUNT ( FactSales[CustID] ) )주의 : DAX 계산열은 로드 시점에 평가되어 메모리를 점유한다. 반복 로직은 가능하면 측정값으로 전환한다.
5. 시트 구조 설계: 표, 스필, 보조열
시트에서 직접 처리해야 한다면 다음 원칙을 따른다.
- 원본 범위는 반드시 표(Insert→Table)로 만든다. 구조적 참조로 수식 안정성을 확보한다.
- 스필 함수로 벡터화한다(FILTER, SORT, UNIQUE, XLOOKUP 등).
- 보조열을 도입해 복잡한 배열식을 분해한다. 계산 체인을 짧게 유지한다.
예시: 대량 매칭에서 VLOOKUP 대신 XLOOKUP과 동적 배열을 활용한다.
=XLOOKUP([@SKU], 제품표[SKU], 제품표[제품명], "없음", 0)대량 필터링 출력은 다음처럼 스필한다.
=FILTER(판매표, (판매표[OrderDate]>=G1)*(판매표[OrderDate]<G2)*(판매표[지역]=G3))주의 : SUBTOTAL, OFFSET, INDIRECT, TODAY, RAND처럼 휘발성 함수는 재계산 범위를 확대한다. 대량 데이터에서는 사용을 지양한다.
6. 수식 성능 튜닝 체크리스트
| 항목 | 권장 조치 | 기대 효과 |
|---|---|---|
| 휘발성 함수 | NOW/TODAY/OFFSET/INDIRECT 제거, 값 고정 또는 매개변수화 | 불필요한 전체 재계산 방지 |
| 대량 조회 | XLOOKUP 또는 INDEX/MATCH, 정렬+XMATCH 바이너리 검색 | 대량 매칭 속도 향상 |
| 배열 연산 | BYROW/BYCOL로 행·열 단위 집계 | 명확한 계산 체인 구축 |
| 중복 연산 | LET로 중간 결과 캐시 | 반복 평가 감소 |
| 텍스트 처리 | TEXTSPLIT/TEXTAFTER로 전처리 단순화 | 복잡한 MID/SEARCH 중첩 감소 |
예시: LET로 중간 결과를 재사용한다.
=LET(r, 판매표[Qty]*판매표[UnitPrice], 세액, r*0.1, 합계, r+세액, 합계)7. 피벗 기반 보고서 설계
피벗테이블은 대량 데이터를 시트에 전개하지 않고도 요약할 수 있다. 설계 요령은 다음과 같다.
- 피벗 캐시를 공유한다(같은 원본 범위를 사용하는 피벗은 기본적으로 캐시 공유).
- 필드 계산이 복잡하면 데이터 모델로 로드하고 DAX 측정값을 사용한다.
- 필요한 세부만 드릴다운하고, 상세는 별도 시트를 스필 수식으로 연결한다.
8. 입력·출력 병목 제거
- 자동 저장이 빈번하면 저장 지연이 누적된다. 대량 변환 중에는 수동 저장-완료 후 저장을 고려한다.
- 조건부 서식은 영역을 최소화한다. 중복 규칙을 제거하고 “이 값만” 형식으로 단순화한다.
- 차트는 데이터 시리즈 개수를 최소화한다. 차트용 요약 범위를 별도로 만든다.
9. 계산 옵션과 재계산 제어
대량 작업 시 “수식→계산 옵션→수동”으로 설정하고, 완료 후 F9로 재계산한다. VBA로 일괄 제어할 수도 있다.
Sub BatchMode() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' 대량 처리 코드 작성 Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub주의 : 자동 계산 복귀를 누락하면 이후 통합문서의 결과가 갱신되지 않는다. 오류 발생 가능성을 고려해 오류 처리에서 상태 복원을 강제한다.
10. 고급 조회·조인 구현 패턴
정규화된 대량 테이블 간의 조인은 데이터 모델 또는 파워쿼리에서 수행한다. 시트 수식으로 조인할 경우 카디널리티를 반드시 통제한다.
=LET( 키, 판매표[CustID]&"|"&판매표[SKU], 조인, XLOOKUP(키, 고객제품가격표[CustID]&"|"&고객제품가격표[SKU], 고객제품가격표[가격], NA()), IFERROR(조인, 0) )정렬된 키에서의 이진 탐색 매칭은 XMATCH의 match_mode=2를 사용한다.
=INDEX(가격열, XMATCH(찾을키, 정렬된키열, 0, 2))11. 메모리·안정성 관리
- 한 통합문서에 워크시트·피벗·차트를 과도하게 누적하지 않는다. 모델과 보고서를 분리한다.
- 숫자 저장 형식을 간소화한다. 불필요한 서식·그라데이션·아이콘 집합을 제거한다.
- .xlsb 저장을 검토한다. 열기·저장이 빠르고 용량이 줄어드는 경우가 많다.
12. 로깅·검증 자동화
대량 파이프라인에는 검증 단계가 필수이다. 파워쿼리 끝단과 피벗 결과의 레코드 수를 비교한다.
=LET(a, ROWS(판매표[SKU]), b, SUM(피벗테이블1[Count of SKU]), a=b)VBA로 처리 시간을 기록해 병목을 식별한다.
Function Tick() As Double Tick = Timer End Function
Sub Measure()
Dim t As Double: t = Tick()
' 처리 블록
Debug.Print "elapsed(sec)=", Format(Tick() - t, "0.000")
End Sub13. 실전 워크플로우: 폴더 내 CSV 300만 행 집계
- 파워쿼리→데이터 가져오기→파일/폴더에서→모든 CSV 결합.
- 형 변환, 오류 행 제거, 필요 열만 선택, 날짜 범위 매개변수화.
- 데이터 모델에 로드, 날짜·제품·고객 차원과 관계 연결.
- DAX 측정값으로 KPI 정의(매출, 전년동기, 객단가).
- 피벗테이블로 월·제품군 기준 요약, 슬라이서로 기간·지역 필터.
- 보고 시트에 큐브함수로 일부 상세만 노출.
14. 대량 데이터 성능 점검표
| 점검 항목 | 체크 방법 | 기준 |
|---|---|---|
| 원본 적재 경로 | 파워쿼리 사용 여부 | 시트 직접 적재 금지 |
| 모델 구조 | 스타 스키마 적용 | 사실-차원 분리 |
| 재계산 전략 | 수동 계산 모드 | 완료 후 F9 |
| 휘발성 함수 | 이름 관리자에서 검색 | 0개 권장 |
| 파일 형식 | 저장 형식 확인 | .xlsb 우선 |
| 조건부 서식 | 규칙 관리자 점검 | 최소 규칙 |
15. 흔한 병목과 대안 맵
| 병목 | 원인 | 대안 |
|---|---|---|
| 시트 로딩 지연 | 수십만 행을 시트에 전개 | 데이터 모델에 로드 후 피벗 |
| 수식 전체 재계산 | 휘발성 함수 사용 | LET+값 고정, 파워쿼리 전처리 |
| 조회 느림 | VLOOKUP 다중 중첩 | XLOOKUP, INDEX/XMATCH, 정렬 후 이진 탐색 |
| 저장 시간 길어짐 | .xlsx 대형 파일 | .xlsb 전환 |
| 메모리 부족 | 32비트 엑셀 | 64비트 전환, 모델 분리 |
16. 데이터 유효성, 오류 방지 설계
- 데이터 유효성 검사로 키 열의 형식·허용 목록을 강제한다.
- 파워쿼리 단계에서 Null 처리, 범주 매핑 테이블과의 조인을 통해 불량 데이터를 소거한다.
- 로그 시트에 데이터 수·합계·최소·최대 값을 기록하여 변동 감시를 자동화한다.
17. 자동화 파이프라인 패턴
파워쿼리 새로 고침→피벗 새로 고침→보고서 저장까지를 VBA로 일괄 실행한다.
Sub RefreshAllAndSave() On Error GoTo EH Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ThisWorkbook.RefreshAll DoEvents ThisWorkbook.Save EH: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub18. 데이터 품질 지표(DQ) 삽입
보고서에 간단한 품질 지표를 포함해 이상을 즉시 감지한다.
=LET(t, 판매표[Amount], 합계, SUM(t), 음수건수, SUM(--(t<0)), "합계="&TEXT(합계,"#,##0")&" / 음수="&음수건수)19. 보안·공유 고려
- 민감 데이터는 원본 DB에서 집계 후 최소 필드만 로드한다.
- 공유용은 연결 끊기 복제본을 별도로 만든다. 원본은 모델·연결을 유지한다.
- 허용되지 않은 매크로가 포함된 파일은 보안 설정으로 인해 차단될 수 있으므로, 배포는 .xlsb 또는 서명된 .xlsm을 사용한다.
20. 최종 요약: 선택의 순서
- 행 수가 크면 시트에 적재하지 않는다.
- ETL은 파워쿼리, 집계는 데이터 모델, 출력은 피벗·큐브함수로 설계한다.
- 시트 수식이 필요하면 스필·LET·XLOOKUP으로 벡터화한다.
- .xlsb, 64비트, 수동계산, 조건부 서식 최소화를 기본값으로 삼는다.
FAQ
엑셀 시트의 최대 행 수를 넘는 데이터를 어떻게 처리하나?
시트로 내리지 말고 파워쿼리에서 데이터 모델로 로드한 뒤 피벗테이블 또는 큐브함수로 표시한다. 필요 시 기간·지역 등으로 필터 매개변수를 두어 분할 로드한다.
파워쿼리와 수식 중 무엇이 더 빠른가?
정제·집계는 파워쿼리가 우수하다. 시트 수식은 뷰 층에서 최소한으로 사용한다. 다만 소규모 데이터에서 간단한 계산은 수식이 빠르다.
32비트 엑셀에서 자꾸 중단된다. 해결책은?
64비트 엑셀 전환이 최선이다. 당장 전환이 어렵다면 모델 분할, 불필요한 시트·서식 제거, .xlsb 저장, 피벗 캐시 공유로 메모리 사용을 줄인다.
피벗테이블이 느리다. 무엇을 점검하나?
필드 수를 줄이고, 계산열 대신 측정값을 사용한다. 필요 없는 세부 필드를 원본 단계에서 제거하고, 슬라이서는 핵심 차원만 유지한다.
VLOOKUP이 느릴 때 가장 간단한 개선은?
XLOOKUP으로 교체하고, 키 열을 정렬한 뒤 XMATCH의 이진 탐색을 사용한다. LET로 중간 결과를 캐싱한다.