대용량 엑셀 통합 문서 성능 높이기

5,000행일 때 빠르던 통합 문서가 50만 행이 되면 1분씩 멈추기도 해요. 원인은 행 수 자체인 경우가 드물어요. 데이터보다 비용이 더 빨리 커지는 몇 가지 선택 때문이죠. 휘발성 함수, 모든 행에서 표 전체를 훑는 수식, 거대한 파일을 매번 다시 읽는 쿼리, 빈 셀 백만 개에 걸린 서식 같은 것들이요. 이 글에서는 그런 비용을 찾아내고, 비싼 것부터 고치고, 튜닝이 아니라 데이터베이스가 필요한 때를 알아보는 방법을 다뤄요.

엑셀이 다시 계산할 셀을 정하는 방식

엑셀은 종속성 트리(dependency tree) 를 관리해요. 수식마다 어떤 셀을 읽는지 기록해 두는 거예요. 셀 하나를 바꾸면 엑셀은 그 셀과, 직접 또는 다른 수식을 거쳐 그 셀에 기대는 모든 셀을 다시 계산할 대상으로 표시하고, 그것들만 다시 계산해요. 그래서 수식이 10만 개 있는 모델이라도 잘 만들었다면 입력 하나를 바꿔도 바로 결과가 나와요.

이 효율을 무너뜨리는 것은 두 가지예요.

• 휘발성 함수는 입력이 바뀌었든 아니든, 통합 문서에서 무엇이든 다시 계산될 때마다 매번 다시 계산돼요. NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO가 그래요. 휘발성 셀에 기대는 수식도 모두 함께 다시 계산돼요.
• 긴 종속 관계 사슬. 엑셀은 종속 관계 순서, 즉 계산 체인(calculation chain) 을 따라 다시 계산해요. 체인 앞쪽에 있는 느린 수식 하나가 그 뒤의 모든 계산을 늦추고, 몇 단계를 거쳐 그 결과를 읽는, 겉보기엔 상관없어 보이는 셀까지 영향을 받아요. 느린 셀을 선택하고 수식› then 수식 분석› then 참조하는 셀 추적으로 영향이 어디까지 미치는지 확인해 보세요.

다시 계산은 키보드로도 조절할 수 있어요.

고치기 전에 먼저 재기

먼저 재고, 고칠 때마다 다시 재세요. 그래야 무엇이 효과가 있었는지 알 수 있어요.

• 전체 다시 계산 시간을 재세요. Ctrl+Alt+F9를 누르고 상태 표시줄을 지켜보거나, 기록해 둘 숫자가 필요하면 아래의 작은 매크로를 쓰세요.

Sub TimeFullCalc()
    Dim t As Double
    t = Timer
    Application.CalculateFull
    MsgBox "Full calculation: " & Format(Timer - t, "0.00") & " seconds"
End Sub

• 시트마다 실제 끝을 확인하세요. Ctrl+End를 누르세요. 1,048,576행이나 데이터보다 한참 아래로 이동한다면, 서식이 걸렸거나 한때 쓰였던 빈 셀까지 시트의 사용 범위에 들어가 파일을 부풀리고 있는 거예요.
• 어느 시트가 느린지 찾으세요. 잠시 수동 계산으로 바꾼 다음(아래 참고) 시트마다 차례로 Shift+F9를 눌러 보세요.
• 부분별로 따로 재세요. 다시 계산, 데이터› then 쿼리 및 연결› then 모두 새로 고침, 열기와 저장은 원인도 해결 방법도 달라요.

복사할수록 비용이 불어나는 수식 고치기

가장 비싼 수식은 일을 많이 하면서 표 전체에 복사된 수식이에요. 예를 들어 행마다 그 지역의 합계를 보여 주려고 데이터 표 5만 행 전체에 이 수식을 복사했다고 해 볼게요.

=SUMPRODUCT((Data[Region]=[@Region])*Data[Amount])

수식 하나하나가 5만 행을 모두 훑으니, 한 번 다시 계산할 때 50,000 × 50,000 = 25억 번 비교해요. 같은 답을 지역마다 SUMIFS 하나씩 쓴 작은 요약표로 얻을 수 있어요. 몇 행이 각각 효율적으로 한 번씩만 훑으면 돼요.

E2fx=SUMIFS($B$2:$B$9,$A$2:$A$9,D2)

	A	B	C	D	E
1	지역	금액		지역	합계
2	동부	120		동부	380
3	서부	80		서부	310
4	동부	200		남부	260
5	남부	150
6	서부	90		마지막 금액	140
7	동부	60
8	남부	110
9	서부	140

=SUMIFS($B$2:$B$9,$A$2:$A$9,D2)복사

데이터 행마다 정말 그룹 합계가 필요하다면, 행마다 요약표에서 한 번씩 조회하거나, 파워 쿼리(Power Query)나 피벗 테이블(PivotTable)로 계산하세요.

휘발성 함수를 바꾸세요. OFFSET과 INDIRECT는 주로 데이터와 함께 늘어나는 범위를 만들 때 써요. INDEX도 참조를 돌려줄 수 있는데, 휘발성이 아니에요. 아래 두 수식은 모두 B2:B9의 합계인 950을 돌려주지만, 통합 문서 어디서든 무언가 바뀔 때마다 다시 계산되는 건 첫 번째뿐이에요.

=SUM(OFFSET(B2,0,0,COUNTA(B:B)-1,1))
=SUM(B2:INDEX(B:B,COUNTA(B:B)))

더 좋은 방법은 데이터를 표로 만들고 =SUM(Data[Amount])를 쓰는 거예요. 데이터가 늘면 저절로 함께 늘어나요. 목록의 마지막 값은 OFFSET 없이 =INDEX(B2:B9,COUNTA(B2:B9))로 구할 수 있고, 140이 나와요.

다른 해결책도 생각은 같아요. 일은 한 번만 하고, 그 결과를 다시 쓰는 거예요.

• 날짜가 정말 휘발성이어야 하는지 따져 보세요. TODAY()를 몇 개의 셀에서 부르든, 거기에 기대는 수식은 모두 매번 다시 계산돼요. 보고서에 고정된 기준일을 써도 된다면 입력 셀 하나에 날짜를 넣고(Ctrl+;를 누르면 오늘 날짜가 값으로 들어가요) 수식이 그 셀을 보게 하세요. 그러면 그 셀을 바꿀 때만 다시 계산돼요.
• 한 번 찾고 여러 번 꺼내 쓰세요. 열 다섯 개가 같은 키로 각각 XLOOKUP을 한다면, 도우미 열을 하나 만들어 MATCH(key, lookup_column, 0)를 한 번만 계산하고, 다섯 열에서는 그 위치로 INDEX를 쓰세요. 정확히 일치하는 조회는 목록을 한 행씩 훑어요. 정렬된 데이터라면 XLOOKUP(…, …, …, , 0, 2) 같은 이진 검색이 훨씬 빠르지만, 데이터가 정렬되어 있지 않으면 틀린 결과를 돌려줘요.
• 범위에 끝을 정해 두세요. SUMIFS는 A:A 같은 열 전체 참조도 효율적으로 처리하지만, SUMPRODUCT를 비롯한 배열 계산은 1,048,576행을 하나도 빠짐없이 계산해요. 표 참조(Data[Amount])는 저절로 데이터 범위에 맞춰져요.
• 동적 배열은 데이터 크기에 맞추세요. =UNIQUE(A2:A1000000)은 수식 하나라서, 참조하는 값이 하나라도 바뀌면 백만 행짜리 배열 전체를 다시 계산해요. =UNIQUE(Data[Region])은 실제로 있는 행만 처리해요. 수식 안에서 비싼 중간 결과를 되풀이한다면 LET으로 한 번만 계산하세요.

파워 쿼리 새로 고침 빠르게 하기

엑셀의 파워 쿼리에는 증분 새로 고침이 없어요. 새로 고칠 때마다 원본을 처음부터 다시 읽어요. 쿼리가 네트워크 공유 폴더의 2GB짜리 CSV를 읽는다면, 바뀐 게 아무리 적어도 새로 고칠 때마다 2GB를 네트워크로 가져와요.

• 행 필터링과 열 제거는 최대한 앞 단계에서 하세요. 원본이 데이터베이스라면 앞쪽 단계가 폴딩(folding) 될 수 있어요. 즉 데이터베이스 쿼리의 일부로 넘어가서 원본에서 필터링되고, 네트워크로 오는 데이터가 줄어요. 적용된 단계에서 단계를 마우스 오른쪽 단추로 클릭해 네이티브 쿼리 보기(View Native Query) 를 쓸 수 있다면 그 단계는 폴딩되는 거예요. CSV나 엑셀 같은 파일은 폴딩되지 않지만, 일찍 필터링하면 그 뒤 모든 단계의 일이 줄어요.
• 큰 파일은 로컬이나 원본 쪽에서 먼저 준비하세요. 하루에 한 번 빠른 로컬 위치나 SharePoint로 파일을 복사해 두거나, 데이터를 관리하는 팀에 필요한 행과 열만 담은 추출 파일을 요청하세요.
• 쓰는 것만 로드하세요. 다른 쿼리에 데이터를 넘겨주기만 하는 준비 단계 쿼리는 연결만 만들기로 두세요(닫기 및 다음으로 로드 › 연결만 만들기). 시트나 데이터 모델로 로드하는 쿼리가 하나 늘 때마다 로드 시간과 파일 크기가 늘어요.

데이터 모델을 가볍게 유지하기

데이터 모델(Data Model)은 열마다 따로 압축하기 때문에, 크기는 표의 행 수보다 열에 서로 다른 값이 몇 개인지에 훨씬 크게 좌우돼요. 300만 행이라도 서로 다른 월 이름이 12개뿐인 열은 아주 작아요. 반면 300만 개의 고유한 거래 ID나 초 단위 타임스탬프가 담긴 열은 커요.

• 쓰지 않는 열은 로드하기 전에 지우세요. 모델에 넣은 뒤가 아니라 파워 쿼리에서요. 가져온 열은 어떤 보고서가 쓰든 안 쓰든 메모리와 새로 고침 시간을 잡아먹어요.
• 서로 다른 값의 개수를 줄이세요. 날짜와 시간이 함께 든 열은 날짜 열과, 꼭 필요할 때만 시간 열로 나누세요. 정밀도가 다 필요 없는 값은 반올림하세요.
• 계산된 열보다 측정값을 쓰세요. 계산된 열은 모든 행에 값을 저장하고, 측정값은 피벗 테이블이 요청할 때만 계산해요.
• 큰 모델에는 64비트 엑셀을 쓰세요. 32비트 엑셀은 큰 데이터 모델에서 메모리가 부족해질 수 있어요. 어느 쪽인지는 파일 > 계정 > Excel 정보에서 확인하세요.

서식으로 인한 비대화 없애기

"혹시 몰라서" 데이터 바깥 멀리까지 건 서식은 파일을 키우고, 열기와 저장과 작업을 느리게 만들어요. 주된 원인은 두 가지예요. 하나는 넓은 빈 셀 영역에 건 셀 서식으로, 시트의 사용 범위를 늘려 놓아요. 다른 하나는 데이터보다 훨씬 넓게 걸렸거나, 행을 복사하고 삽입하는 사이에 수십 조각으로 쪼개진 조건부 서식(Conditional Formatting) 규칙이에요.

1. 조건부 서식을 정리하세요

   홈› then 스타일› then 조건부 서식› then 규칙 관리로 가서 서식 규칙 표시에서 현재 워크시트를 고르고 적용 대상을 살펴보세요. 중복된 규칙은 합치고, 조각난 규칙은 지우고, 규칙마다 표나 데이터 범위에만 적용되게 하세요.

2. 데이터 아래와 오른쪽의 서식을 지우세요

   데이터 바로 아래 첫 빈 행의 행 머리글을 클릭하고 Ctrl+Shift+Down을 눌러 시트 마지막 행까지 선택을 넓히세요. 선택한 행 머리글을 마우스 오른쪽 단추로 클릭하고 삭제를 고르세요. 오른쪽의 빈 열도 똑같이 하세요. 통합 문서를 저장한 다음 Ctrl+End를 다시 눌러 보세요. 이제 데이터 끝으로 이동해야 해요.

3. 나머지는 엑셀이 찾게 하세요

   Microsoft 365에서는 검토 탭의 성능 확인(Check Performance) 명령이 서식만 있고 비어 있는 셀을 찾아 목록으로 보여 주고, 정리까지 해 줘요.

수동 계산을 안전하게 쓰기

큰 모델을 만들거나 구조를 바꾸는 동안, 고칠 때마다 다시 계산을 기다리는 건 시간 낭비예요. 수식› then 계산› then 계산 옵션› then 수동으로 바꾸세요. 결과가 최신이 아니면 상태 표시줄에 계산이 나타나요. 결과가 필요할 때 F9를 누르세요.

주의할 점이 세 가지 있어요.

• 통합 문서별 설정이 아니에요. 엑셀은 열려 있는 모든 통합 문서에 계산 모드 하나를 쓰고, 그 모드는 그 세션에서 처음 연 통합 문서를 따라가요. 수동 모드로 저장된 파일 하나가 다른 통합 문서까지 수동으로 바꿀 수 있어요.
• 최신이 아닌 숫자도 진짜처럼 보여요. F9를 깜빡한 사람은 지난 결과를 읽게 돼요. 파일 > 옵션 > 수식에서 통합 문서를 저장하기 전에 다시 계산을 체크된 상태로 두세요.
• 넘겨주기 전에 되돌리세요. 공유하기 전에 자동으로 바꾸고 저장하세요. 느린 원인이 데이터 표라면 데이터 표만 수동(Automatic Except for Data Tables) 이 더 안전한 절충안이에요.

엑셀이 더 이상 맞는 도구가 아닐 때

어떤 통합 문서는 하나만 고친다고 빨라지지 않아요. 다음 신호가 보이면 데이터는 데이터베이스로 옮기고, 엑셀은 파워 쿼리, 피벗 테이블, Power BI를 통해 앞단에서 쓰는 게 맞아요.

데이터는 데이터베이스(SQL Server, Azure SQL, Access)나 공유 목록(Dataverse, SharePoint)에 맡기고, 엑셀은 분석과 보고에 쓰세요.

느린 통합 문서 진단하기

느린 통합 문서에서 나온 발견 사항 일곱 가지예요. 하나만 빼고 모두 규모가 커지면 실제 위험이에요.

실제 위험을 영향도와 수정 난이도로 순위를 매기세요. 조건부 서식과 SUMPRODUCT 열은 몇 분이면 고칠 수 있고 효과도 가장 큰 경우가 많아요. 반면 연결된 통합 문서 40개를 데이터베이스로 옮기는 건 하나의 프로젝트예요.

자주 하는 실수

• 재 보지 않고 최적화하기. 다시 계산, 새로 고침, 저장 시간을 먼저 재야 어떤 수정이 효과가 있었는지 알 수 있어요.
• 휘발성 함수를 다른 휘발성 함수로 바꾸기. OFFSET 대신 INDIRECT를 써도 여전히 휘발성이에요. INDEX나 표를 쓰세요.
• 통합 문서를 수동 모드로 내버려 두기. 다음 사람은 지난 숫자를 보면서도 그 사실을 몰라요. 공유할 사본을 저장하기 전에 자동으로 되돌리세요.
• 로드한 뒤에 열 지우기. 모델에서 열을 지워도 파워 쿼리는 매번 그 열을 읽어요. 쿼리에서 지우세요.

연습

연습

가장 느린 통합 문서 진단하기

1. 가장 느린 통합 문서나 모델을 열고 세 가지 시간을 기록하세요. 전체 다시 계산(Ctrl+Alt+F9), 모두 새로 고침, 저장이에요.
2. 시트마다 Ctrl+End를 눌러 데이터보다 한참 멀리 이동하는 시트를 적어 두세요.
3. 찾은 위험을 모두 적으세요. 휘발성 함수, 아래로 복사되어 표 전체를 훑는 수식, 끝이 정해지지 않은 배열 수식, 느린 쿼리, 데이터 모델 크기, 서식 비대화요.
4. 위험마다 영향도와 수정 난이도를 매기세요. 영향은 가장 크고 수정은 가장 쉬운 두 가지를 고친 다음 다시 재세요.
5. 목록 중에 작업이 엑셀의 한계를 넘어섰다는 신호가 있는지, 옮기려면 누구의 동의가 필요한지 적어 두세요.

요약표에서 바꾸는 방법을 연습해 보세요. 지역별 합계는 SUMIFS로, 마지막 금액은 INDEX로 구해요.

직접 해 보기4개 중 0개 완료

1. 1.E2에 SUMIFS로 D2 지역의 금액 합계를 구해 보세요. 아래로 복사할 수 있도록 데이터 범위는 고정하세요.힌트 보기
2. 2.E3에 D3 지역에 대해 같은 수식을 써 보세요.힌트 보기
3. 3.E4에 D4 지역의 합계를 구해 요약을 마무리해 보세요.힌트 보기
4. 4.E6에 OFFSET 없이 목록의 마지막 금액을 구해 보세요. 금액 개수와 INDEX를 쓰세요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	지역	금액		지역	합계
2	동부	120		동부
3	서부	80		서부
4	동부	200		남부
5	남부	150
6	서부	90		마지막 금액
7	동부	60
8	남부	110
9	서부	140

실력 점검

• 계산 체인에서 어떤 수식이 앞쪽에, 어떤 수식이 뒤쪽에 있는지, 그게 왜 중요한지 설명할 수 있나요?
• 규모가 커질 때 배열 수식이 정확히 왜 비싸지는지 말하고, 다시 쓸 수 있나요?
• 느린 파워 쿼리 새로 고침과 너무 커진 데이터 모델을 진단할 수 있나요?
• 통합 문서가 엑셀의 한계를 넘어섰을 때를 알아볼 수 있나요?