7. 순환 참조를 의도적으로 사용해야 할 때 설정 방법

엑셀의 '순환 참조'는 일반적으로 피해야 할 오류이지만, 특정 금융 모델이나 공학 계산에서는 의도적으로 순환 참조를 허용하고 반복 계산을 통해 답을 찾아야 하는 경우가 있습니다. 

엑셀은 이러한 특별한 상황을 위해 '반복 계산'이라는 강력한 기능을 제공합니다.
엑셀의 '반복 계산' 기능을 사용하여 순환 참조를 허용하고 특정 계산을 수행하는 방법과, 이 기능을 사용할 때 반드시 알아야 할 주의사항에 대해 자세히 설명해 드리겠습니다.

 

엑셀 '반복 계산': 의도된 순환 참조를 활용하는 방법 (주의사항 필독!)

엑셀에서 순환 참조는 기본적으로 오류로 간주되어 경고 메시지가 표시됩니다. 

이는 수식이 자신을 참조하거나 다른 셀이 다시 자신을 참조하여 무한 루프에 빠지는 것을 방지하기 위함입니다. 

하지만 재무 모델의 이자 계산, 공학 분야의 수렴 계산 등 일부 복잡한 모델에서는 순환 참조가 불가피하며, 이때 엑셀이 여러 번 계산을 반복하여 수렴하는 값(일정 수준의 오차 범위 내에서 더 이상 변하지 않는 값)을 찾도록 설정할 수 있습니다. 

이것이 바로 '반복 계산(Iterative Calculation)' 기능입니다.

 

1. '반복 계산' 기능의 정의 및 원리

'반복 계산' 기능은 엑셀이 순환 참조 수식을 만났을 때, 계산이 지정된 '최대 반복 횟수'에 도달하거나 '최대 변화' 값보다 작은 오차 범위 내로 수렴할 때까지 반복적으로 계산을 수행하도록 허용하는 기능입니다.

예시:

이자율이 순이익에 영향을 미치고, 다시 순이익이 이자율 계산에 영향을 미치는 복잡한 재무 모델에서, 엑셀은 반복 계산을 통해 최종적인 균형점을 찾아냅니다.

2. '반복 계산' 설정 방법 (단계별 가이드)

'반복 계산' 기능은 엑셀의 옵션에서 활성화할 수 있습니다.

2-1. 파일 탭 클릭:

엑셀 창 왼쪽 상단의 파일 탭을 클릭하여 백스테이지 보기로 이동합니다.

2-2. 옵션 선택:

파일 탭의 왼쪽 메뉴 하단에서 옵션을 클릭합니다.

2-3. 수식 탭 이동:

Excel 옵션 대화 상자가 나타나면, 왼쪽 메뉴에서 수식을 선택합니다.

2-4. '반복 계산 사용' 활성화:

계산 옵션 섹션에서 '반복 계산 사용' 체크박스를 선택합니다.

2-5. '최대 반복 횟수' 및 '최대 변화' 설정:

• 최대 반복 횟수(Maximum Iterations): 엑셀이 계산을 반복할 최대 횟수를 설정합니다. 기본값은 100입니다. 계산의 정확도나 복잡성에 따라 이 값을 늘리거나 줄일 수 있습니다. 값이 클수록 더 많은 계산을 시도합니다.
• 최대 변화(Maximum Change): 연속적인 계산 결과 간의 최대 허용 오차를 설정합니다. 기본값은 0.001입니다. 이 값이 낮을수록 더 정확한 결과를 얻지만, 엑셀이 수렴점을 찾는 데 더 많은 계산 시간을 소비할 수 있습니다. 매우 정밀한 계산이 아니라면 기본값으로도 충분합니다.

 

2-6. 확인 클릭:

설정을 완료한 후 확인 버튼을 클릭하여 변경 사항을 적용합니다. 이제 엑셀은 해당 통합 문서에서 순환 참조를 만나면 설정된 값에 따라 반복 계산을 시도합니다.

3. '반복 계산' 사용 시 주의사항

'반복 계산'은 강력한 기능이지만, 잘못 사용하면 데이터의 정확성을 해치거나 엑셀의 성능에 문제를 일으킬 수 있습니다. 다음 주의사항들을 반드시 숙지해야 합니다.

3-1. 반드시 필요한 경우에만 사용:

'반복 계산'은 순환 참조를 해결하는 일반적인 방법이 아닙니다. 대부분의 순환 참조는 수식의 논리적 오류에서 비롯되므로, 먼저 '오류 검사' 기능을 통해 원인을 파악하고 수식을 수정하는 것이 우선입니다. 의도적으로 순환 참조가 필요한 특수한 경우에만 사용해야 합니다.

3-2. 수렴성 확인 필수:

가장 중요한 주의사항입니다. 반복 계산이 항상 해답을 찾는 것은 아닙니다. 수식이 수학적으로 수렴하지 않는 경우에는 아무리 반복해도 정확한 값에 도달할 수 없으며, 엑셀은 '최대 반복 횟수'에 도달하거나 이상한 값을 반환하며 계산을 멈출 수 있습니다.

• 수렴하지 않는 예시: A1 = A1 + 1과 같이 계속 값이 커지는 수식은 절대 수렴하지 않습니다.
• '최대 변화'를 통해 수렴 여부를 판단하므로, 원하는 정확도에 도달하지 못하면 계산이 중단될 수 있습니다.

3-3. 데이터 정확성 검증:

반복 계산을 통해 도출된 결과가 논리적으로 타당하고 정확한지 항상 검증해야 합니다. 계산된 값이 상식적인 범위를 벗어나거나, 모델의 목적에 부합하지 않는다면 수식이나 모델 설계 자체를 재검토해야 합니다.

3-4.성능 저하 가능성:

복잡한 모델이나 많은 수의 순환 참조가 있는 경우, 반복 계산은 엑셀의 성능을 저하시켜 계산 속도가 느려지거나 엑셀이 응답하지 않을 수 있습니다. '최대 반복 횟수'와 '최대 변화' 값을 적절히 조절하여 성능과 정확도 사이의 균형을 찾아야 합니다.

3-5. 통합 문서 단위 설정:

'반복 계산 사용' 설정은 현재 작업 중인 엑셀 통합 문서에만 적용됩니다. 다른 통합 문서에서는 이 설정을 다시 활성화해야 합니다. 이 점을 인지하지 못하고 다른 파일에서 순환 참조 오류가 발생했다고 오해할 수 있습니다.

3-6. 다른 사용자와의 공유 시 고려:

'반복 계산'이 활성화된 통합 문서를 다른 사용자와 공유할 때는 반드시 해당 기능의 사용 목적과 설정 내용을 알려주어야 합니다. 기능을 모르는 사용자가 파일을 열었을 때 발생하는 순환 참조 경고에 당황할 수 있으며, 의도치 않게 설정을 변경할 수도 있기 때문입니다.

4. 언제 '반복 계산'을 사용해야 할까요?

• 재무 모델링: 이자 비용, 세금, 순이익 등이 서로 영향을 주고받는 복잡한 재무 모델에서 최종적인 균형점을 찾아야 할 때. (예: 기업의 특정 대출 조건이 이익에 영향을 주고, 그 이익이 다시 대출 상환 능력에 영향을 주는 경우)
• 공학/과학 계산: 수렴하는 값을 찾아야 하는 수치 해석 문제.
• 예측 모델: 특정 조건이 다른 조건에 피드백을 주며 반복적으로 영향을 미치는 예측 모델.

 

 

엑셀의 '반복 계산' 기능은 특정 시나리오에서 매우 유용하게 활용될 수 있는 고급 기능입니다. 순환 참조를 허용하고 반복적으로 계산하여 수렴 값을 찾아내는 이점은 분명하지만, 그 위험성 또한 명확합니다.

따라서 '반복 계산'은 그 원리와 한계를 정확히 이해하고, 수식의 수렴성을 충분히 검토한 후에 반드시 필요한 경우에만 신중하게 사용해야 합니다. 무분별한 사용은 부정확한 데이터와 성능 저하로 이어질 수 있음을 명심해야 합니다.