엑셀 순환 참조 오류 해결 방법 총정리: 원인 분석부터 안전한 대체 수식까지

이 글의 목적은 엑셀 순환 참조 오류를 신속히 찾아내고 안전하게 제거하는 표준 절차와 실무형 대체 수식을 제공하여 업무 중단 없이 모델을 안정화하도록 돕는 것이다.

1. 순환 참조 이해: 정의와 증상

순환 참조는 셀이 직접 또는 간접적으로 자기 자신을 참조하여 계산의 시작과 끝이 고리처럼 연결되는 상태를 의미한다. 대표 증상은 다음과 같다.

• 상태 표시줄에 “순환 참조” 메시지가 나타난다.
• 수식 입력줄 왼쪽에 경고 아이콘이 표시된다.
• 계산이 무한 반복되거나 값이 0 또는 이전 값에서 갱신되지 않는다.
• 파일 저장·열기 속도가 급격히 느려진다.

주의 : 반복계산(Iterative Calculation)을 임시로 켜서 문제를 덮으면 값이 수렴하지 않거나 입력 순서에 따라 값이 달라지는 비결정적 모델이 된다. 기본 원칙은 “반복계산 비활성화 후 구조적 원인 제거”이다.

2. 기본 점검 체크리스트

점검 항목	확인 방법	결과 조치
반복계산 사용 여부	파일 → 옵션 → 수식 → 계산 옵션에서 “반복 계산 사용” 체크 해제 여부 확인	해제 후 계산 강제(F9) 및 오류 재현
이름(네임드 범위) 간접 참조	수식 → 이름 관리자에서 INDIRECT · OFFSET · INDEX 조합 확인	고정 참조로 치환 또는 구조 정리
테이블/스필 범위 역참조	구조적 참조([@열])와 스필( A1# )이 상호 참조하는지 추적	보조열 분리 또는 단계 계산
시트 간 상호 호출	수식 추적기로 “전면/후면 시트” 화살표 확인	단방향 흐름으로 리팩터링
사용자 정의 함수(UDF)	VBA 모듈에서 UDF가 워크시트 값을 다시 쓰는지 확인	UDF는 읽기 전용으로 유지

3. 원인별 재현 예시와 안전한 대체 수식

3.1 합계-비율 상호의존 구조

문제 예시 : 총합 셀 B10 이 각 항목 합계이며, 일부 항목은 B10 의 비율로 계산되어 다시 B10 에 포함되는 구조이다.

  
예) B10 = SUM(B2:B9) B3 = B10 * 0.1 ' B3가 B10을 참조하여 다시 B10 합계에 포함됨 → 순환 
  

안전한 설계 : 의존 항목을 합계에서 분리하고, 합계는 “외생(입력)”만 더한다.

  
입력합계 = SUM(B2:B9) - B3 B3 = 입력합계 * 0.1 총합(B10) = 입력합계 + B3 
  

주의 : “합계에 포함된 항목이 합계에 의존”하는 패턴을 먼저 탐지해야 한다. 합계 범위에서 해당 항목을 일시 제외 후 재귀 구조를 끊는다.

3.2 누적합이 자기 자신을 참조

  
문제: C2 = C1 + B2 C1 = C2 * 0.2 ' 또는 다른 셀이 다시 C1을 참조해 역순환 
  

대체 : 누적열과 조정열을 분리한다.

  
조정(D열): D2 = B2 * 0.2 누적(C열): C2 = SUM($B$2:B2) + SUM($D$2:D2) 
  

3.3 할당-한도(캡) 계산의 역참조

총 한도 내 비례배분 시 자기참조가 발생하기 쉽다.

  
문제: 배분율 = 요구량 / MIN(한도, 요구량의 합) ' 분모가 결과 합계에 의존 
  

대체 : 분모를 외생 합으로 고정하고, 결과는 MIN 으로 캡 처리한다.

  
요구합 = SUM(요구범위) 기초배분 = 요구량 / MAX(요구합,
1) * 한도 결과 = MIN(기초배분, 요구량) 
  

3.4 OFFSET/INDIRECT로 생긴 암시적 순환

가변 참조를 사용하는 이름이 다시 자신의 셀을 범위에 포함시키는 경우가 있다.

  
문제: 이름 myRange = OFFSET(A1, 0, 0, ROW(),
1) B5 = SUM(myRange) ' B5 행에서 myRange가 B5를 포함 → 순환 
  

대체 : 동적 범위는 INDEX 를 사용해 종단점만 계산한다.

  
안전한 이름: myRange = A$1:INDEX(A:A, MATCH(1E+99, A:A)) 
  

3.5 테이블 구조적 참조의 자기 행 역참조

테이블 tbl 의 [@금액]이 [@배율]을 참조하고, [@배율]이 다시 [@금액]을 참조하는 경우이다.

  
문제: [@금액] = [@기초] * [@배율] [@배율] = [@금액] / [@목표] 
  

대체 : 배율은 외생값으로 분리하거나, 목표 기반 역산은 별도 보조열로 계산한다.

  
보조열[@역산배율] = IF([@목표]>0, [@기초]/[@목표],
0) [@금액] = [@기초] * [@역산배율] 
  

4. 탐지와 추적: 가장 빠른 절차

4.1 상태 표시줄·알림으로 진입점 확보

1. 하단 상태 표시줄의 “순환 참조”를 클릭하면 관련 셀 후보가 표시된다.
2. 후보 셀로 이동하여 수식 추적을 시작한다.

4.2 수식 감사 도구

1. 수식 → 수식 감사 → 순서대로 “선행 셀/종속 셀 추적”을 사용한다.
2. 도형 화살표를 따라가며 역방향·정방향 참조를 지도처럼 확인한다.
3. 시트 간 화살표는 점선 상자 형태로 나타나므로 시트를 전환하며 동일 절차를 반복한다.

4.3 이름 관리자 점검

이름 관리자에서 참조 수식에 INDIRECT , OFFSET , INDEX + ROW / COLUMN , 스필 연산자( # )가 섞인 정의를 집중 확인한다. 동적 범위가 현재 셀을 포괄하면 순환 가능성이 높다.

4.4 VBA로 일괄 탐색

광범위한 모델에서는 VBA로 세계관을 먼저 수집하는 것이 효율적이다.

  
' 순환 참조 후보를 수집하여 시트/주소/수식 출력 Sub FindCirculars() Dim ws As Worksheet, r As Range, i As Long Application.ScreenUpdating = False On Error Resume Next For Each ws In ThisWorkbook.Worksheets If Not ws Is Nothing Then Set r = ws.CircularReference If Not r Is Nothing Then Debug.Print ws.Name, r.Address(0,
0), r.Formula Set r = Nothing End If End If Next ws Application.ScreenUpdating = True End Sub 
  

주의 : Worksheet.CircularReference 는 첫 번째 항목만 반환할 수 있다. 여러 개가 의심되면 수식 감사와 병행한다.

5. 반복계산을 써야 하는 합법적 사례와 안전 설정

목표값 탐색, 재무 상환 스케줄의 잔액-이자 동시결정, 공학 모델의 수렴 계산처럼 반복계산이 불가피한 경우가 있다. 이때는 다음과 같이 설정한다.

1. 파일 → 옵션 → 수식 → 계산 옵션에서 “반복 계산 사용”을 체크한다.
2. 최대 반복 횟수와 최대 변경값(수렴 오차)을 모델 규모에 맞게 설정한다. 예: 최대 반복 100, 최대 변경값 1E-06.
3. 반복을 사용하는 영역을 시각적으로 식별 가능하게 표시하고, 입력과 결과를 엄격히 분리한다.

설정 항목	권장 기본값	설명
최대 반복 횟수	100~500	수렴 속도에 따라 조정한다. 무한대에 가깝게 두면 계산 시간이 폭증한다.
최대 변경값	1E-06	허용 오차이다. 수렴 기준을 너무 크게 두면 결과 정확도가 떨어진다.

주의 : 반복계산을 쓸 때는 “결과 민감도 테스트”를 반드시 수행한다. 초기값(시작값)을 조금 바꾸어도 동일 해에 수렴하는지 확인한다.

6. 실무 시나리오별 리팩터링 패턴

6.1 원가·마진 역산

  
문제: 판매가 = 원가 + 판매가 * 마진율 
  

해결 : 대수적으로 정리한다.

  
판매가 = 원가 / (1 - 마진율) 
  

6.2 KPI 목표 달성률과 보너스

  
문제: 보너스 = MIN(보너스상한, 매출 * 보너스율) 보너스율 = f(달성률) = 매출 / 목표 * 계수 
  

해결 : 보너스율을 외생 함수로 고정하고, 보너스는 MIN 으로만 캡 한다.

6.3 전월 잔액-이자 상호의존

  
문제: 이자 = 잔액 * 이자율 잔액 = 전월잔액 + 이자 - 상환 
  

해결 : 기간별로 순차 계산되도록 “행 기준 전개”로 설계한다.

  
행 t: 이자_t = 잔액_{t-1} * 이자율 잔액_t = 잔액_{t-1} + 이자_t - 상환_t 
  

7. 대체 수식 도구상자

• LET 으로 중간값을 이름으로 고정하여 재참조를 통제한다.
• LAMBDA 로 순수 함수화하여 상태 의존성을 제거한다.
• SUMIFS / XLOOKUP / FILTER 로 간접 루프를 끊고 단방향 데이터 흐름을 만든다.
• 동적 배열은 스필된 결과( # )를 다시 원본 범위에 합산하지 않도록 별도 영역으로 분리한다.

  
=LET( 입력합, SUM(B2:B9) - B3, 배분, 입력합 * 0.1, 입력합 + 배분 ) 
  

8. 모델 구조 점검 규칙(표준)

규칙	설명	효과
입력-계산-출력 3구역 분리	색상 또는 시트로 구분한다.	역참조 가능성 감소
단방향 참조	왼→오른쪽, 위→아래로만 흐르게 한다.	추적 용이
동적 범위는 INDEX 종단점	OFFSET/INDIRECT 남용 금지	암시적 순환 예방
보조열 적극 사용	한 셀에 한 역할 원칙	디버깅 단순화
이름의 가시성 관리	의미 있는 네이밍과 주석 유지	의도 파악 용이

9. 자동화: 의심 패턴 빠르게 색출

조건부 서식으로 “자기 참조 가능성”을 표시한다.

  
수식(예: B열 계산영역): =ISNUMBER(SEARCH(ADDRESS(ROW(), COLUMN(),
4), FORMULATEXT(B1))) 적용 범위: B:B 
  

간접 참조 탐지용 사용자 정의 함수 예시이다.

  
' 수식에 INDIRECT 또는 OFFSET이 포함되면 TRUE Function HasVolatileRef(r As Range) As Boolean On Error Resume Next HasVolatileRef = (InStr(1, r.Formula, "INDIRECT", vbTextCompare) >
0) _ Or (InStr(1, r.Formula, "OFFSET", vbTextCompare) >
0) End Function 
  

10. 파워쿼리 활용으로 구조적 루프 차단

데이터 정제·집계는 파워쿼리로 전처리하고, 결과만 워크시트로 로드하면 계산 그래프가 단방향이 된다. 동일 파일 내 테이블 간 상호 참조를 끊을 수 있어 대규모 모델에서 유리하다.

1. 데이터 → 데이터 가져오기 → 파일/데이터베이스/웹 선택
2. 필요한 변환 수행 후 “연결만 만들기” 또는 “테이블로 로드”
3. 워크시트 수식은 파워쿼리 출력 테이블만 참조

11. 성능과 안정성: 추가 권장 설정

• 자동 계산 대신 “데이터 입력 시 F9 수동 재계산”으로 변경하여 루프 발생 즉시 감지한다.
• 대용량 파일은 수식 복잡도와 휘발성 함수( NOW , TODAY , RAND , OFFSET , INDIRECT ) 사용량을 감축한다.
• 버전 관리로 변경 이력을 남기고, 순환 발생 시 직전 버전과 비교하여 원인 커밋을 추적한다.

12. 단계별 해결 가이드(요약)

1. 반복계산 해제, 전체 재계산(F9)으로 오류 상태를 명확히 한다.
2. 상태 표시줄 후보 셀 이동 후 수식 감사로 선행·종속을 시각화한다.
3. 이름 관리자에서 동적 범위를 정적 또는 INDEX기반으로 치환한다.
4. 합계-비율·누적·캡 구조는 보조열로 분리하고 단방향 흐름으로 재설계한다.
5. 필요 시 VBA로 의심 지점을 일괄 수집한다.
6. 반복계산이 불가피한 모델만 한정적으로 사용하고 수렴 기준을 명시한다.

FAQ

반복 계산을 켜면 문제가 해결되는가?

일시적으로 값이 나오지만 근본 해결은 아니다. 수렴 보장이 없고 입력 순서에 따라 결과가 달라질 수 있다. 설계 단순화와 단방향 흐름 확보가 우선이다.

INDIRECT를 반드시 써야 한다면?

가능하면 피한다. 불가피할 경우 참조 대상을 보조 표로 매핑하고, 선택 결과만 고정 주소로 반환하도록 설계한다. 동적 범위는 INDEX로 종단점을 계산한다.

테이블 스필과 합계가 얽혀 순환이 난다.

스필 범위는 출력 전용으로 두고, 합계는 원본 데이터만 집계한다. 필요 시 스냅샷 시트로 값 붙여넣기를 사용한다.

UDF 때문에 순환이 생길 수 있는가?

UDF가 셀 값을 변경하거나, 다시 워크시트 계산을 트리거하면 순환 또는 재계산 폭증이 발생한다. UDF는 순수 함수로 유지한다.

수식 감사 화살표가 너무 많아 따라가기 어렵다.

영역을 분리하여 시트 단위로 추적하고, 이름 관리자에서 큰 가지부터 끊는다. 복잡 노드는 보조열로 분해한다.