4. 가장 흔하게 발생하는 순환 참조 유형과 해결 사례

엑셀에서 순환 참조 오류는 생각보다 흔하게 발생하며, 주로 수식의 논리적 연결 고리가 잘못 설정되었을 때 나타납니다. 

특히 실무에서는 급하게 작업을 처리하거나 복잡한 계산을 하다 보면 의도치 않게 순환 참조의 덫에 걸리곤 하죠.
실무에서 자주 발생하는 순환 참조 오류 사례들을 살펴보고, 각 상황에 맞는 구체적인 해결 방법을 알아보겠습니다.

 

 

엑셀 순환 참조 오류: 실무에서 흔히 발생하는 유형과 해결책!

엑셀에서 순환 참조 오류가 발생하면 "하나 이상의 순환 참조가 있습니다. 수식이 제대로 계산되지 않을 수 있습니다."와 같은 경고 메시지가 나타납니다. 

이 메시지는 현재 계산에 문제가 있음을 알리는 중요한 신호이므로, 반드시 해결해야 합니다.
가장 흔한 순환 참조 오류 유형과 해결 방법을 함께 알아보겠습니다.

1. SUM 함수 범위 오류 (가장 흔한 유형)

이 오류는 SUM 또는 AVERAGE와 같은 집계 함수를 사용할 때, 계산 범위에 해당 함수가 입력된 셀 자체가 포함되는 경우에 발생합니다.

오류 상황 예시:

아래 그림처럼 총합계를 구하는 SUM 함수를 D5 셀에 입력하는데, 합계 범위에 D5 셀이 포함되어 있습니다.

D5 셀은 D2부터 D5까지의 합계를 구하는데, D5 자신이 이 합계에 포함됩니다. 엑셀은 D5 값을 알아야 D2:D5 합계를 구할 수 있고, 다시 D2:D5 합계를 알아야 D5 값을 알 수 있는 무한 루프에 빠집니다.

 

해결 방법:
합계 범위에서 수식이 입력된 셀을 제외합니다.

해결 예시:
D6 셀의 수식을 다음과 같이 변경합니다.

• 변경 전: =SUM(D2:D5)
• 변경 후: =SUM(D2:D4)

이제 D5 셀은 D2부터 D4까지의 합계만 계산하므로 순환 참조가 발생하지 않습니다.

이 방법을 사용하면 오류 메시지가 사라지고 올바른 총합계가 표시됩니다.

 

2. 간접 참조 오류 (복잡한 연결고리)

이 유형은 두 개 이상의 셀이 서로를 참조하여 순환 고리를 형성할 때 발생합니다. 직접 참조처럼 눈에 확 띄지 않아 찾기 어려울 수 있습니다.

오류 상황 예시:
월별 예산과 실제 지출을 비교하는 시트에서 다음과 같은 상황이 발생했다고 가정해 봅시다.

• B2 (예상 매출): =C2 * 0.8 (순이익의 80%를 예상 매출로 가정)
• C2 (순이익): =B2 - D2 (예상 매출에서 비용을 뺀 값이 순이익)

이 경우, B2는 C2를 참조하고, C2는 다시 B2를 참조하는 간접적인 순환이 발생합니다.

해결 방법:

순환 고리를 끊고, 논리적인 계산 흐름을 다시 설정해야 합니다. 어떤 값을 독립적인 변수로 설정할지, 아니면 다른 수식을 통해 도출할지 결정해야 합니다.

해결 예시:
이 경우, '예상 매출'이 '순이익'에 기반하는 것이 아니라, 독립적으로 먼저 정의되어야 한다고 판단할 수 있습니다.

• 변경 전:
  B2 (예상 매출): =C2 * 0.8
  C2 (순이익): =B2 - D2
• 변경 후:
  B2 (예상 매출): 10,000,000 (직접 숫자 입력 또는 다른 기준에서 가져옴)
  C2 (순이익): =B2 - D2 (비용 D2가 2,000,000이라면 C2는 8,000,000이 됨)

이제 B2는 C2를 참조하지 않고, C2만 B2를 참조하므로 순환 참조가 사라집니다.

 

3. 복잡한 재무/예측 모델 오류

이자율, 세금, 매출액 등 서로 영향을 주고받는 변수가 많은 재무 모델이나 예측 시트에서 순환 참조가 자주 발생합니다.

오류 상황 예시:
법인세 계산 시 세전 이익이 법인세에 영향을 주고, 법인세가 다시 세전 이익(이자 비용 등 제외 후)에 영향을 주는 경우입니다.

• A1 (세전 이익): =B1 - C1 (매출에서 비용을 뺀 값. C1은 법인세)
• C1 (법인세): =A1 * 0.2 (세전 이익의 20%)

여기서 A1은 C1을 참조하고, C1은 다시 A1을 참조합니다.

해결 방법:
이러한 경우 '반복 계산' 기능을 활용하거나, 별도의 가변 값을 도입하여 순환 고리를 끊는 방법을 사용합니다.

 

해결 예시 1: 반복 계산 설정 (정교한 계산이 필요할 때)
엑셀은 기본적으로 순환 참조를 허용하지 않지만, 일부러 반복 계산을 허용하여 특정 조건이 만족할 때까지 계산을 반복하도록 설정할 수 있습니다. 이는 재무 모델링에서 수렴하는 값을 찾아야 할 때 유용합니다.

• 파일 탭 > 옵션 클릭
• 수식 탭 선택
• '계산 옵션' 섹션에서 '반복 계산 사용' 체크박스 선택
• '최대 반복 횟수'와 '최대 변화' 값 조정 (일반적으로 기본값으로도 충분하며, '최대 변화'를 낮출수록 더 정밀한 계산을 하지만 시간 소요가 길어질 수 있음)
• 확인 클릭

이렇게 설정하면 엑셀은 순환 참조 수식을 지정된 횟수만큼 반복하여 계산하고, 값이 일정 수준(최대 변화 값) 이내로 수렴하면 계산을 멈춥니다.

해결 예시 2: 순환 고리 끊기 (단순화된 모델일 때)
반복 계산이 필요하지 않다면, 순환 고리를 직접 끊는 방법도 있습니다. 예를 들어, 법인세를 계산할 때 '이자 비용' 등을 법인세 계산 전에 미리 확정시키거나, 가변적인 이자 비용을 독립적인 변수로 설정하는 식입니다.

• A1 (조정된 세전 이익): =B1 - (이자 비용 등) - C1
• C1 (법인세): =A1 * 0.2 (여기서 A1은 C1을 참조하기 전에 이미 모든 비용이 반영된 값이어야 함)

가장 좋은 방법은 수식 설계를 처음부터 순환 참조가 발생하지 않도록 논리적으로 구성하는 것입니다.

4. 복사/붙여넣기 실수 또는 자동 채우기 오류

다른 셀의 수식을 복사하여 붙여넣거나, 자동 채우기(Ctrl + D, Ctrl + R)를 사용할 때 상대 참조/절대 참조가 잘못 적용되어 순환 참조가 발생할 수 있습니다.

오류 상황 예시:
B1 셀에 =A1+10이라는 수식이 있다고 가정해 봅시다. 이 수식을 A1 셀에 실수로 복사하여 붙여넣는 경우입니다.

• A1에 =A1+10 (순환 참조 발생)

또는 A1에 10이라는 숫자가 있고, B1에 =A1+B1이라는 잘못된 수식을 입력한 후 B1을 A1으로 채우기 한 경우에도 발생할 수 있습니다.

해결 방법:
붙여넣기 후 수식을 확인하고, 자동 채우기 전 항상 참조 관계를 명확히 이해해야 합니다.

해결 예시:
수식을 붙여넣은 후 반드시 해당 셀의 수식 입력줄을 확인하고, 필요한 경우 F4 키를 눌러 상대 참조/절대 참조를 올바르게 조정해야 합니다.

A1에 10이 있다면, B1에 =A1+C1 (C1이 다른 값을 참조하는 경우) 또는 B1에 단순 계산 수식 =A1 * 2 등을 입력해야 합니다.

 

순환 참조 발생 시 진단 팁

어떤 유형의 순환 참조든, 오류가 발생했다면 다음 단계를 따라 문제를 진단하고 해결하세요.

1. '순환 참조 경고' 대화 상자 확인: 엑셀 파일을 열거나 수식을 입력하자마자 나타나는 경고 메시지를 확인합니다.
2. 상태 표시줄 확인: 엑셀 창 하단의 상태 표시줄에 표시되는 "순환 참조: [셀 주소]" 메시지를 확인합니다. 이 메시지를 클릭하면 순환 참조 추적 메뉴가 나타납니다.
3. '수식' 탭 > '오류 검사' 활용:
   수식 탭 > 수식 분석 그룹 > 오류 검사 드롭다운 메뉴를 클릭합니다.
   '순환 참조' 항목을 선택하면 통합 문서 내의 모든 순환 참조 셀 목록을 확인할 수 있습니다.
4. '참조되는 셀 추적' / '참조하는 셀 추적' 사용: 문제가 되는 셀을 클릭한 후 이 기능을 사용하여 화살표로 참조 관계를 시각적으로 확인합니다. 화살표가 순환 고리를 형성하면 해당 부분이 문제의 원인입니다.

순환 참조는 엑셀 계산의 정확성을 해치는 주범입니다. 

위에서 제시된 유형과 해결 방법을 숙지하여 정확하고 효율적인 엑셀 작업을 하시길 바랍니다.