이 글의 목적은 엑셀에서 수식 자동 계산이 지연되거나 전혀 실행되지 않는 문제를 신속하고 정확하게 진단하고 복구하는 절차를 현업 수준으로 제공하는 것이다.
1. 문제 인식: “자동 계산이 안 된다”의 정확한 정의
자동 계산 문제는 크게 네 가지 패턴으로 구분된다.
- 워크북 전체가 계산되지 않는 경우이다.
- 일부 시트 또는 일부 셀만 계산되지 않는 경우이다.
- 계산은 되지만 지연되거나 중간에 멈추는 경우이다.
- 계산 결과가 예상과 다르거나 이전 값에서 갱신되지 않는 경우이다.
각 패턴은 원인이 다르므로 먼저 현재 증상을 정확히 식별해야 한다.
2. 핵심 원인 한눈에 보기
| 증상 | 주요 원인 | 즉시 조치 |
|---|---|---|
| 모든 수식이 고정값처럼 보임 | 계산 옵션이 수동으로 설정됨 | 메뉴에서 계산 옵션을 자동으로 변경하거나 Ctrl+Alt+F9로 전체 재계산 강제한다 |
| 일부 시트만 안 계산됨 | 시트 수준 순환참조, 데이터 표 모드, 계산 종속 체인 손상 | Shift+F9로 시트 재계산, 순환참조 해소, 종속 체인 재구성한다 |
| 계산이 매우 느리거나 멈춤 | 휘발성 함수 다수(OFFSET, INDIRECT, NOW 등), 대용량 배열 수식, 스레드 제한 | 휘발성 함수 대체, 동적 배열 최적화, 멀티스레드 설정 점검한다 |
| VBA 실행 이후 계산이 멈춤 | VBA에서 Application.Calculation=xlCalculationManual로 고정 | VBA 종료 전에 자동으로 되돌리거나 종료 후 자동으로 복원한다 |
| 특정 셀만 값이 그대로 | 셀 서식이 텍스트, 작은따옴표로 입력, 스페이스 포함 | 서식을 일반으로 바꾸고 F2→Enter로 재평가한다 |
| 외부연결이 반영 안 됨 | 외부 링크 업데이트 차단, 신뢰센터 제한 | 외부 콘텐츠 업데이트 허용, 연결 새로 고침한다 |
| 수식 대신 =TEXT 그대로 보임 | 표시 형식 텍스트 또는 ’=...로 입력 | 작은따옴표 삭제, 서식 일반, 다시 입력한다 |
3. 필수 점검 체크리스트(순차 진행)
- 계산 옵션 확인이다.
- 리본 > 수식 > 계산 옵션 > 자동인지 확인한다.
- “자동(데이터 표 제외)”로 되어 있으면 데이터 표만 수동임에 주의한다.
- 재계산 강제이다.
- 현재 시트만:
Shift+F9를 사용한다. - 워크북 종속체인 재작성 포함 전체:
Ctrl+Alt+F9를 사용한다. - 모든 열린 통합문서 전체:
Ctrl+Alt+Shift+F9를 사용한다.
- 현재 시트만:
- 수식이 텍스트로 인식되는지 확인이다.
- 셀 왼쪽 위에 녹색 표시가 있거나, '작은따옴표로 시작하면 텍스트이다.
- 서식을 “일반”으로 변경 후
F2→Enter로 재평가한다.
- 순환참조 확인이다.
- 상태 표시줄 또는 수식 > 오류 검사 > 순환참조 위치로 이동한다.
- 의도적 반복 계산이 필요하면 반복 계산 허용 및 최대 반복 횟수·허용 오차를 설정한다.
- 외부 연결 및 데이터 모델 점검이다.
- 쿼리, 피벗, 외부 링크를 모두 새로 고침한다.
- 신뢰 센터 설정에서 외부 콘텐츠 차단 여부를 해제한다.
- VBA·애드인 영향 확인이다.
- 애드인이 계산 모드를 변경했는지 확인한다.
- VBA에서 계산 모드를 수동으로 고정했으면 원복한다.
- 성능 병목 제거이다.
- 휘발성 함수, 과도한 3D 참조, 대규모 배열 수식, 불필요한 전체 열 참조를 줄인다.
- 동적 배열 함수와 스필을 사용해 반복 계산 범위를 축소한다.
주의 : 조직 환경에서 기본 계산 모드가 공유 통합 문서 한 개 때문에 수동으로 전파되는 경우가 있다. 엑셀은 처음으로 연 워크북의 계산 모드가 세션 전체에 적용되므로, 문제 워크북을 먼저 열면 이후 파일도 모두 수동으로 열릴 수 있다.
4. 계산 모드의 동작 원리 이해
엑셀은 세 가지 계산 모드를 가진다.
- 자동이다. 데이터 변경 시 종속 체인에 따라 즉시 계산한다.
- 자동(데이터 표 제외)이다. 데이터 표는 성능 보호를 위해 수동으로 계산한다.
- 수동이다. 사용자가 명시적으로 명령을 내릴 때만 계산한다.
세션 시작 시 첫 번째로 연 워크북의 계산 모드가 현재 엑셀 인스턴스 전체에 적용되는 특성이 있다.
5. 단축키와 명령의 정확한 사용
| 명령 | 효과 | 범위 |
|---|---|---|
| F9 | 변경된 셀만 계산한다 | 현재 워크북 |
| Shift+F9 | 현재 워크시트를 계산한다 | 현재 시트 |
| Ctrl+Alt+F9 | 모든 수식을 다시 계산한다(종속 체인 재작성) | 현재 워크북 |
| Ctrl+Alt+Shift+F9 | 열린 모든 통합 문서 전체 강제 재계산한다 | 모든 워크북 |
6. 순환참조와 반복 계산
순환참조는 수식이 직접 또는 간접적으로 자기 자신을 참조하여 계산이 수렴하지 않는 상황을 말한다. 의도적 반복 계산을 사용할 때는 다음을 준수한다.
- 파일 > 옵션 > 수식 > 계산 옵션에서 “반복 계산 사용”을 체크한다.
- 최대 반복 횟수와 최대 변경값(허용 오차)을 설정한다.
- 모델이 수렴하는지 테스트하고 수렴하지 않으면 수식 구조를 수정한다.
주의 : 순환참조를 임시로 허용한 뒤 원복을 잊으면 다른 시트 계산이 묵시적으로 수렴 실패 상태가 될 수 있다. 프로젝트 공유 시 설정을 문서화한다.
7. 휘발성 함수와 성능 최적화
휘발성 함수는 사소한 변경에도 전체 재계산을 유발한다. 다음 함수는 대표적인 고비용 함수이다.
- INDIRECT, OFFSET, NOW, TODAY, RAND, RANDBETWEEN, CELL, INFO, AREAS이다.
대체 전략이다.
- INDIRECT는 구조화 참조나 INDEX로 대체한다.
- OFFSET은 INDEX와 참조 계산으로 치환한다.
- 난수는 필요 시에만 재계산하도록 매크로로 값 고정한다.
8. 데이터 형식·입력 오류로 인한 비계산
- 셀 서식이 텍스트이면 수식이 문자열로 보관된다. 일반으로 변경 후 재입력한다.
=앞에 작은따옴표가 있으면 텍스트이다. 작은따옴표를 삭제한다.- 앞뒤 공백은 수식 구문 오류를 유발하지 않지만 데이터 비교를 실패하게 한다.
- 쉼표·세미콜론 구분자 설정 차이로 수식이 무효가 될 수 있다.
9. 표 개체, 동적 배열, 스필과 계산
표 개체(ListObject)와 동적 배열 수식은 자동 확장과 스필을 통해 계산 범위가 변동한다. 계산이 멈춘 것처럼 보이면 다음을 확인한다.
- 스필 블록에 차단 셀이 있는지 확인한다.
- 표의 계산 열이 수동 재계산으로 묶여 있지 않은지 확인한다.
- 대규모 스필은 참조 범위를 최소화하고 중간 결과를 캐시한다.
10. 피벗, 데이터 모델, 파워쿼리와 계산의 관계
피벗테이블과 파워쿼리 결과는 수식 계산과 별개로 “새로 고침”이 필요하다. 계산은 끝났는데 값이 갱신되지 않는다면 다음을 수행한다.
- 데이터 > 모두 새로 고침을 실행한다.
- 연결 속성에서 파일 열 때 새로 고침, 일정 새로 고침, 백그라운드 새로 고침을 조절한다.
11. 멀티스레드 계산과 자원 관리
수식 > 계산 옵션 > 고급에서 멀티스레드 계산을 사용으로 설정한다. CPU 코어 수가 많아도 휘발성 함수 비중이 높으면 이득이 제한된다. 대규모 배열, LOOKUP 체인, 사용자 정의 함수(UDF)의 병목을 제거하는 것이 먼저이다.
12. VBA로 인해 고정된 수동 모드 복원
다음 패턴으로 계산 모드가 수동으로 남는 경우가 많다. 안전한 설정 복원 예시이다.
' 안전한 계산 모드 래핑 패턴 Sub SafeCalcWrapper() Dim prevCalc As XlCalculation Dim prevScreen As Boolean, prevEvents As Boolean prevCalc = Application.Calculation prevScreen = Application.ScreenUpdating prevEvents = Application.EnableEvents On Error GoTo CleanUp
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
'... 대량 처리 코드 ...
CleanUp:
Application.Calculation = prevCalc
Application.ScreenUpdating = prevScreen
Application.EnableEvents = prevEvents
Application.Calculate ' 필요 시 강제 재계산
End Sub
이미 수동으로 고정된 세션을 복원하려면 다음을 실행한다.
Sub RestoreAutoCalc() Application.Calculation = xlCalculationAutomatic Application.CalculateFull ' 종속 체인 재작성 포함 End Sub 주의 : 외부에서 받은 매크로는 계산 모드, 이벤트, 화면 업데이트를 변경한 후 복원하지 않을 수 있다. 배포 전 항상 위와 같은 래핑 패턴을 적용한다.
13. 계산 상태 점검 코드
대규모 모델에서 현재 계산 상태를 모니터링하는 도우미이다.
Sub ShowCalcState() Dim s As String Select Case Application.CalculationState Case xlDone: s = "계산 완료" Case xlCalculating: s = "계산 중" Case xlPending: s = "대기 중" End Select MsgBox "모드: " & IIf(Application.Calculation = xlCalculationAutomatic, "자동", _ IIf(Application.Calculation = xlCalculationManual, "수동", "자동(데이터 표 제외)")) _ & vbCrLf & "상태: " & s End Sub 14. 외부 링크·신뢰 센터 설정
보안 정책으로 외부 링크 자동 업데이트가 꺼져 있으면 계산 결과가 오래된 값으로 남는다. 다음을 확인한다.
- 파일 > 옵션 > 신뢰 센터 > 신뢰 센터 설정 > 외부 콘텐츠에서 자동 업데이트 허용한다.
- 파일 열기 시 “콘텐츠 사용”을 승인한다.
- 공유 위치(SharePoint, OneDrive)에서는 체크아웃·버전 동시성으로 인해 값 반영이 지연될 수 있다.
15. 재현·진단 절차 템플릿(실무용)
- 모든 워크북을 닫고 엑셀을 완전히 종료한다.
- 새 빈 통합 문서를 열고 계산 옵션을 자동으로 설정한다.
- 문제 워크북만 단독으로 연다.
Ctrl+Alt+F9로 완전 재계산을 수행한다.- 상태 표시줄에 “순환참조” 알림이 있는지 본다. 있으면 즉시 해소한다.
- 수식 표시(
Ctrl+`)로 텍스트 수식 여부를 확인한다. - 외부 연결 새로 고침을 실행한다.
- 여전히 문제면 모든 애드인을 비활성화하고 테스트한다.
- VBA가 있는 경우 위 “복원” 매크로로 자동 모드로 강제 전환한다.
- 대규모 모델은 휘발성 함수 대체, 범위 축소, 스필 최적화를 적용한다.
16. 애드인·COM 추가 기능으로 인한 간섭
파일 > 옵션 > 추가 기능에서 Excel 추가 기능과 COM 추가 기능을 각각 관리한다. 계산과 충돌하는 흔한 유형이다.
- 사용자 정의 함수(UDF)를 제공하며 내부 캐시 실패 시 무한 재계산을 유발하는 애드인이다.
- 열기 이벤트에서 계산 모드를 수동으로 강제하는 애드인이다.
A/B 진단 절차이다.
- 모든 추가 기능 비활성화 후 문제 재현을 시도한다.
- 하나씩 활성화하여 문제가 다시 발생하는 지점을 특정한다.
17. 동적 배열과 호환성
동적 배열이 없는 구버전에서 만든 통합 문서를 신버전에서 열면 스필 결과가 예상과 달라질 수 있다. 반대로 신버전에서 만든 스필 수식을 구버전에서 열면 @ 암시적 교차가 개입되어 계산 결과가 변형된다. 배포 대상 버전에 맞추어 수식 방식을 고정한다.
18. 대규모 모델 최적화 패턴
- 전체 열 참조(
A:A) 대신 실제 사용 범위로 제한한다. - VLOOKUP은 정확일치와 작은 범위로, 대량일치는 XLOOKUP 또는 INDEX/MATCH로 전환한다.
- 중첩 IF는 IFS·SWITCH 또는 매핑 테이블로 단순화한다.
- 반복 참조가 필요하면 별도의 계산 구역에서 단계별로 수렴을 유도한다.
19. 자동 계산이 계속 수동으로 바뀌는 경우
다음 두 가지가 가장 흔하다.
- 처음 연 워크북이 수동 모드로 저장된 경우이다. 이 파일을 먼저 열지 않도록 하거나 저장 모드를 자동으로 바꾸어 저장한다.
- VBA 또는 애드인이 세션 시작 시 수동으로 강제하는 경우이다. 시작 매크로를 점검하고 원복 루틴을 추가한다.
20. 문제 해결 예시 스크립트 모음
1) 전체 재계산 및 링크 새로 고침 순차 실행이다.
Sub RecalcAndRefreshAll() Application.Calculation = xlCalculationAutomatic Application.CalculateFullRebuild Dim cn As WorkbookConnection For Each cn In ThisWorkbook.Connections On Error Resume Next cn.Refresh On Error GoTo 0 Next cn End Sub 2) 텍스트로 저장된 수식 일괄 복원이다.
Sub FixTextFormulas() Dim r As Range For Each r In Selection If Left$(r.Formula, 1) <> "=" And Left$(r.Text, 1) = "=" Then r.NumberFormat = "General" r.Value = r.Text r.Value = Replace(r.Value, "'", "") r.Formula = r.Value End If Next r End Sub 3) 휘발성 함수 탐지 리포트이다.
Sub ListVolatileFunctions() Dim rng As Range, c As Range, hit As Boolean Set rng = ActiveSheet.UsedRange For Each c In rng.Cells If c.HasFormula Then hit = False hit = hit Or InStr(1, UCase$(c.Formula), "INDIRECT(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "OFFSET(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "NOW(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "TODAY(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "RAND(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "RANDBETWEEN(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "CELL(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "INFO(") > 0 hit = hit Or InStr(1, UCase$(c.Formula), "AREAS(") > 0 If hit Then Debug.Print c.Address(0, 0), c.Formula End If Next c End Sub 21. 운영 환경별 유의점(Windows/Mac/클라우드)
- Windows 데스크톱이다. 추가 기능과 COM이 계산 모드에 개입할 가능성이 크다.
- Mac이다. 일부 단축키가 다르므로 리본 메뉴에서 강제 재계산을 실행한다.
- Excel Online이다. 대규모 모델은 서버 계산 대기열에 따라 지연될 수 있다. 수식 호환성 매트릭스를 사전에 검토한다.
22. 현업용 점검 시트(복사 사용)
| No | 점검 항목 | 상태 | 메모 |
|---|---|---|---|
| 1 | 계산 옵션 자동 여부 확인 | ||
| 2 | Ctrl+Alt+F9 전체 재계산 실행 | ||
| 3 | 텍스트 수식 여부 점검 및 F2→Enter | ||
| 4 | 순환참조 탐지 및 해소 | ||
| 5 | 외부 링크·쿼리 새로 고침 | ||
| 6 | 애드인 및 VBA 영향 제거 | ||
| 7 | 휘발성 함수·대규모 배열 최적화 | ||
| 8 | 멀티스레드 계산 설정 확인 |
FAQ
파일 하나만 열면 자동 계산이 수동으로 바뀐다면 어떻게 하나?
그 파일이 수동 모드로 저장되었을 가능성이 높다. 해당 파일을 열어 자동으로 바꾼 뒤 저장하거나, 그 파일을 첫 번째로 열지 않도록 작업 순서를 바꾼다.
수식이 그대로 표시된다면 어떻게 하나?
셀 서식이 텍스트이거나 작은따옴표로 시작했을 가능성이 높다. 서식을 일반으로 바꾸고 F2→Enter로 재입력한다.
계산이 매우 느린데 오류는 없다면?
휘발성 함수 사용량과 전체 열 참조, 대형 배열 수식, 외부 링크 지연을 점검한다. 가능하면 INDEX/MATCH, XLOOKUP, 동적 배열로 리팩터링한다.
VBA 실행 후 계산이 멈췄다. 복구 방법은?
Immediate 창 또는 매크로에서 Application.Calculation=xlCalculationAutomatic과 Application.CalculateFullRebuild를 실행한다.
“자동(데이터 표 제외)”은 무엇이 다른가?
일반 수식은 자동으로 계산되나 데이터 표는 성능 보호를 위해 사용자가 수동으로 다시 계산해야 한다. 대용량 시뮬레이션에서 유용하다.