엑셀 순환 참조 오류 해결: 원인 분석부터 안전한 대안 설계까지

Contents

1. 순환 참조란 무엇인가
2. 경고가 뜨는 전형적 징후
3. 신속 진단: 어디에서 순환이 생겼는가
   1. 메뉴 경로로 확인한다
   2. 추적 화살표로 의존성 맵을 그린다
   3. 네임 관리자에서 간접 순환을 찾는다
4. 즉시 해결 체크리스트
5. 안전한 수식 재설계 패턴
   1. SUM 범위에서 자기 자신 제외
   2. 런닝 합계는 첫행을 절대참조한다
   3. 동적 범위는 INDEX로 끝점을 닫는다
   4. LET로 보조 변수를 분리한다
   5. LAMBDA 모듈화로 참조 경로 고정
6. 반복 계산을 의도적으로 써야 하는 경우
   1. 설정 경로
   2. 수렴 설계 요령
7. 진단에서 복구까지 표준 절차
8. 고급 사례별 처방
   1. 손익 모델: 매출–이익–세금 상호참조
   2. 재고 회전: 누적 입출고와 재고말 수식
   3. 프로젝트 원가 배부: 비율 역산
9. 동적·휘발성 함수 사용 지침
10. 자동화로 위험 구간 탐지
11. 대체 설계: 보조 열·계산 시트 분리
12. 검증 및 회귀 방지
13. 체크리스트(다운타임 최소화용)
14. FAQ

이 글의 목적은 엑셀에서 ‘순환 참조’ 경고가 발생하는 근본 원인을 신속하게 진단하고, 데이터 손실 없이 문제를 해결하며, 재발을 예방하기 위한 설계 원칙과 실무 절차를 전문가 수준으로 정리하는 것이다.

1. 순환 참조란 무엇인가

순환 참조란 하나의 수식이 직접 또는 간접적으로 자기 자신을 참조하여 계산이 닫히지 않는 상태를 의미한다. 예를 들어 A1이 B1을 참조하고 B1이 다시 A1을 참조하면 계산 그래프에 사이클이 생겨 엑셀이 경고를 띄운다. 이런 상황은 합계 셀을 다시 원본 범위에 포함시키거나, OFFSET·INDIRECT 같은 동적 참조가 의도치 않게 범위를 확장할 때 흔히 발생한다.

엑셀은 기본적으로 순환 참조를 오류로 간주하여 계산을 중단하거나 경고를 표시한다. 다만 반복 계산을 허용하고 수렴 조건을 만족한다면 일부 모델에서 의도적으로 순환을 활용할 수 있다. 그러나 다수의 업무 시트에서는 데이터 무결성과 성능을 위해 순환을 제거하는 것이 원칙이다.

2. 경고가 뜨는 전형적 징후

• 상태 표시줄 또는 대화상자에 “순환 참조가 있습니다” 경고가 나타난다.
• 계산이 비정상적으로 느려지고 자동 계산이 멈춘 것처럼 보인다.
• 특정 셀 이동 시 포뮬러 바가 깜빡이며 =GETPIVOTDATA, OFFSET, INDIRECT 등 동적 참조 함수가 포함된 수식이 눈에 띈다.
• 합계 셀(예: A10)이 합산 범위(예: A1:A10)에 포함되어 있다.

엑셀 매크로 오류 '매크로를 사용하여 이 작업을 실행할 수 없습니다' 해결 가이드 →

3. 신속 진단: 어디에서 순환이 생겼는가

3.1 메뉴 경로로 확인한다

Windows 기준 절차는 다음과 같다.

수식 탭 > 오류 검사 > 순환 참조 

Mac 기준 절차는 다음과 같다.

Formulas 탭 > Error Checking > Circular References 

해당 메뉴에는 순환이 발생한 대표 셀 목록이 나타난다. 목록이 비어 있어도 동적 참조나 테이블 변환으로 간접 순환이 남아 있을 수 있으므로 추가 추적이 필요하다.

3.2 추적 화살표로 의존성 맵을 그린다

수식 탭 > 수식 감사 > 선행 식 추적 / 결과 식 추적 

선행 식 추적으로 현재 셀의 입력을 확인하고 결과 식 추적으로 이 셀을 참조하는 후속 셀을 확인한다. 화살표가 원형으로 닫히면 해당 고리가 순환 고리이다.

3.3 네임 관리자에서 간접 순환을 찾는다

수식 탭 > 이름 관리자 

동적 이름 정의가 기존 이름을 범위로 포함하는지 확인한다. 특히 OFFSET, INDEX+SEQUENCE, INDIRECT 조합은 간접 순환의 빈도가 높다.

주의 : 테이블 구조참조(예: Table1[금액])에서 합계 행이 본문 열을 다시 참조하거나, 본문 수식이 합계 행을 범위로 포함하면 테이블 전체가 순환에 빠질 수 있다.

4. 즉시 해결 체크리스트

증상/패턴	원인	해결 방법	재발 방지 팁
합계 셀이 범위에 포함	SUM 범위가 합계 셀까지 확장됨	합계 셀 제외 범위 지정(예: =SUM(A1:A9))	테이블 합계 행은 별도 수식 사용 또는 TOTALSROW 사용
누적 합계가 자기 자신 포함	런닝 합계 수식의 시작 참조 오류	=SUM($A$2:A2) 형태로 첫행 고정	첫행 템플릿 고정 및 채우기 방향 검증
동적 범위 이름이 자기 참조	OFFSET가 해당 이름을 다시 포함	동적 범위를 INDEX 기반으로 재작성	이름 정의에 이름 자신 사용 금지 원칙
테이블 합계–본문 상호 참조	합계가 본문 참조, 본문이 합계 참조	합계는 계산 전용 시트로 분리	구조참조에서 [@]와 Table[컬럼] 구분
원가/이익률 역산	이익이 매출과 비용 모두를 참조하며 다시 매출이 이익을 참조	목표값 찾기 또는 데이터 테이블 사용	역산은 반복계산 대신 도구 기능 활용
INDIRECT로 교차 시트 집계	대상 주소가 현재 시트 합계 영역 포함	범위 고정하고 결과를 별도 시트로 출력	INDIRECT 남용 금지, 정규화된 모델 설계

5. 안전한 수식 재설계 패턴

5.1 SUM 범위에서 자기 자신 제외

잘못된 예: =SUM(A1:A10) 'A10이 합계 셀인 경우 올바른 예: =SUM(A1:A9) 테이블 예: =SUM(Table1[금액]) '합계 행은 Table1[[#Data],[금액]] 사용 

5.2 런닝 합계는 첫행을 절대참조한다

=SUM($B$2:B2) 

첫행을 고정하면 채우기 시에도 자기 참조가 발생하지 않는다.

5.3 동적 범위는 INDEX로 끝점을 닫는다

OFFSET 기반(권장하지 않음): =SUM(OFFSET(A1,0,0,COUNTA(A:A),1)) INDEX 기반(권장): =SUM(A1:INDEX(A:A,COUNTA(A:A))) 

INDEX는 비휘발성이며 자기 범위를 다시 참조하지 않는다.

5.4 LET로 보조 변수를 분리한다

=LET( 데이터, A2:A1000, 유효개수, COUNT(데이터), 합계, SUM(INDEX(데이터,1):INDEX(데이터,유효개수)), 합계 ) 

LET을 사용하면 중간 결과를 명확히 분리하여 간접 순환을 방지한다.

5.5 LAMBDA 모듈화로 참조 경로 고정

=LAMBDA(rng, SUM(rng))(A2:A9) 

함수형 인터페이스로 범위가 명시되므로 동일 시트에서의 무의식적 역참조를 피한다.

6. 반복 계산을 의도적으로 써야 하는 경우

재무 모델의 이자비용–부채 상호의존처럼 수렴 가능한 고정점 계산이 필요한 경우가 있다. 이때는 반복 계산과 허용 오차를 엄격히 설정해야 한다.

6.1 설정 경로

파일 > 옵션 > 수식 > 계산 옵션 > 반복 계산 사용 최대 반복 횟수: 50~200 최대 변경값: 1E-6 등 수렴 허용 오차 

주의 : 반복 계산을 켜면 숨은 순환 참조까지 함께 계산되어 결과가 임의 값에 수렴하거나 발산할 수 있다. 반복 계산은 모델별로 최소화하고, 시트 공용 설정을 지양한다.

6.2 수렴 설계 요령

• 순환 루프에 감쇠를 도입한다(예: 새값 = 0.9*이전값 + 0.1*목표 등).
• 필요한 루프만 별도 시트로 분리하고 초기값 셀을 명시한다.
• 결과 검증을 위한 수렴 잔차 셀을 두고 조건부 서식으로 임계 초과 시 경고한다.

엑셀 UDF #NAME? 오류 해결 가이드 - 실무자 필독 팁 및 FAQ →

7. 진단에서 복구까지 표준 절차

1. 백업을 만든다. 현재 파일을 다른 이름으로 저장한다.
2. 계산 모드 수동으로 전환한다.

   수식 탭 > 계산 옵션 > 수동

3. 순환 참조 목록을 열어 대표 셀을 파악한다.
4. 추적 화살표로 폐루프를 확인하고 스크린샷을 남긴다.
5. 가설 설정 후 한 번에 한 지점만 수정한다(예: 합계 범위 축소).
6. 수식 재설계 패턴을 적용한다(5장 참조).
7. 자동 계산 재개 후 결과를 검증한다.
8. 반복 계산이 필요한 모델만 별도 파일·시트로 격리한다.

8. 고급 사례별 처방

8.1 손익 모델: 매출–이익–세금 상호참조

문제 패턴은 매출이 이익률을 참조하고, 이익이 세금을 참조하며, 세금이 다시 매출에 영향을 주는 구조이다. 해결은 의존 방향을 단방향으로 설계하거나, 목표값 찾기로 역산 구간을 대체한다.

데이터 탭 > 가상 분석 > 목표값 찾기 - 설정 셀: 목표 결과 셀 - 찾는 값: 목표값 - 변경할 셀: 역산할 입력 

8.2 재고 회전: 누적 입출고와 재고말 수식

잘못된 설계는 재고말을 주간 합계가 포함된 테이블 합계와 상호 참조하는 것이다. 해결은 기간별 재고말을 이전 기간 재고말과 당기 입출고로만 정의하고, 총계는 별도 집계 시트에서 모은다.

올바른 예: B2: =B1 + 입고!B2 - 출고!B2 합계 시트: =SUM(일반시트[재고말]) '합계는 결과만 참조 

8.3 프로젝트 원가 배부: 비율 역산

배부율을 총원가/합계 기준으로 정의하면서 합계가 다시 배부율을 사용하면 순환이 생긴다. 해결은 배부율을 고정 입력으로 전환하거나, 데이터 테이블 기능으로 시나리오를 그린다.

데이터 탭 > 가상 분석 > 데이터 표 행 입력 셀 / 열 입력 셀에 매개변수를 배치 

9. 동적·휘발성 함수 사용 지침

• OFFSET, INDIRECT, TODAY, RAND 등 휘발성 함수는 계산 그래프를 복잡하게 만들어 순환 탐지를 어렵게 한다. 가능하면 INDEX, 명시 범위, 파라미터 셀로 대체한다.
• 동적 배열 함수(FILTER, UNIQUE, SORT)는 결과가 흘러나오는 범위를 명확히 분리하고, 그 결과를 다시 원본에 합치지 않는다.
• 피벗테이블 집계를 원본 수식에 다시 참조하지 않는다. 분석 결과는 리포트 전용 시트로 분리한다.

엑셀 시트 이동/복사 중 발생하는 이름 중복 오류 해결 가이드 →

10. 자동화로 위험 구간 탐지

다음 VBA 매크로는 순환 참조 후보를 신속하게 스캔하여 수식 중 위험 패턴을 찾아 목록화한다. 보안 정책에 맞게 서명 후 사용한다.

Sub FindCircularRisks() Dim ws As Worksheet, c As Range, r As Long Dim riskFns As Variant: riskFns = Array("OFFSET(", "INDIRECT(", "TODAY(", "RAND(", "RANDBETWEEN(") Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RiskScan" r = 1: With Sheets("RiskScan"): .Cells(r, 1) = "Sheet": .Cells(r, 2) = "Cell": .Cells(r, 3) = "Formula": r = 2: End With For Each ws In ActiveWorkbook.Worksheets For Each c In ws.UsedRange.Cells If c.HasFormula Then Dim f As String: f = UCase(c.Formula) Dim i As Integer For i = LBound(riskFns) To UBound(riskFns) If InStr(1, f, UCase(riskFns(i))) > 0 Then Sheets("RiskScan").Cells(r, 1) = ws.Name Sheets("RiskScan").Cells(r, 2) = c.Address(False, False) Sheets("RiskScan").Cells(r, 3) = c.Formula r = r + 1 Exit For End If Next i End If Next c Next ws End Sub 

주의 : 본 매크로는 순환 참조를 직접 검출하는 것이 아니라 위험 패턴을 스캔한다. 순환 여부는 3장의 절차로 확증한다.

11. 대체 설계: 보조 열·계산 시트 분리

• 보조 열을 도입해 중간 계산을 명시한다. 예를 들어 이익률 역산을 목표이익 셀로 분리하고, 매출 계산식에는 이익률이 아닌 목표이익만 참조한다.
• 원본 데이터, 계산, 보고의 3층 구조를 지킨다. 계산층은 입력과 보고층을 단방향으로만 참조한다.
• 이름 정의는 읽기 전용 상수와 범위를 구분하고, 이름이 이름을 다시 참조하지 않도록 한다.

12. 검증 및 회귀 방지

1. 결과 검증 셀을 둔다. 예: 총액 차이(=보고총액-원본총액)가 0이 아니면 조건부 서식으로 붉은색 표시한다.
2. 시트 보호로 합계·지표 셀의 직접 편집을 금지한다.
3. 버전 관리 규칙을 마련한다. “입력 변경 시점–담당–변경 내용–검증 결과”를 변경 이력 시트에 기록한다.
4. 복잡한 모델은 계산 시간 측정 셀을 두고, 계산 시간이 급증하면 위험 신호로 간주한다.

13. 체크리스트(다운타임 최소화용)

1. 파일 사본 저장 완료 확인한다.
2. 계산 옵션 수동 전환한다.
3. 수식 탭 > 순환 참조 목록 확인한다.
4. 추적 화살표로 폐루프 시각화한다.
5. 합계·런닝합계·동적 범위부터 분리한다.
6. INDEX 기반 동적 범위로 교체한다.
7. 반복 계산 해제하고 재검산한다.
8. 필요 시 목표값 찾기·데이터 표로 역산 구현한다.
9. 테스트 케이스 3종 이상으로 회귀 검사한다.
10. 자동 계산 복귀 후 성능 확인한다.

FAQ

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

일시적으로 경고가 사라질 수 있으나 근본 해결이 아니다. 수렴 보장이 없고 다른 숨은 순환까지 계산되어 결과 왜곡이 생길 수 있다. 수렴이 필요한 명확한 모델에서만 제한적으로 사용한다.

어떤 함수가 순환을 유발하기 쉬운가?

OFFSET, INDIRECT 같은 주소 기반 동적 참조와 TODAY, RAND 같은 휘발성 함수가 위험도가 높다. 피벗테이블 결과를 다시 원본 계산에 연결하는 구조도 흔한 원인이다.

테이블 구조참조에서 안전하게 합계를 내는 방법은?

합계 행이 있는 테이블에서는 본문 데이터만 대상으로 하는 [[#Data],[열명]] 구문을 사용한다. 합계를 다시 본문 수식에 포함하지 않는다.

수식 없이 해결하는 방법은?

목표값 찾기, 데이터 표, 파워쿼리 집계 등 도구를 사용하면 역산과 집계를 수식 루프 없이 구현할 수 있다. 구조를 단방향 데이터 흐름으로 유지한다.

대규모 시트에서 추적이 너무 느리다. 어떻게 하나?

계산 모드를 수동으로 전환하고 문제 범위를 별도 파일로 추출한다. 위험 패턴 스캐너 매크로로 후보를 좁힌 뒤 핵심 범위만 추적한다.

추천·관련글

• 엑셀에서 텍스트를 숫자로 변환하는 방법
• 엑셀 차트 동적 범위 문제 해결과 예방을 위한 완벽 가이드
• 변수를 선언하는 Dim 키워드
• 엑셀 VBA For Each 루프 오류 해결 방법 | 범위 인식 문제 완전 분석
• 엑셀 VBA 재귀 호출 오류 해결: 스택 오버플로 문제와 예방 및 대응 방법
• 엑셀에서 대소문자 변환(UPPER/LOWER 함수) 사용하는 법