엑셀 순환 참조와 반복 계산 설정의 모든 것 :: 엑셀위키

엑셀 순환 참조 반복 계산 설정의 모든 것 :: 엑셀 위키

엑셀 순환 참조 반복 계산 목차 바로가기

엑셀 순환참조란 무엇인가요?

순환참조란 쉽게말해 '계산이 무한대로 반복 되도록 수식이 입력되는 것'을 이야기 합니다. 엑셀에서는 아래 2가지 상황으로 인해 순환참조가 발생될 수 있습니다.

수식이 자기 자신을 참조하는 경우

셀 안에 입력된 함수가 자기 자신을 참조할 경우 순환참조 오류가 발생합니다. 아래 예제를 살펴보겠습니다.

위와 같이 A1셀에 입력된 수식이 자기자신인 A1셀을 참조하게 되면, A1=A1+1 → A1=A1+1..의 형태로 무한대의 계산이 실행됩니다. 따라서 수식이 자기 자신을 참조하는 경우에 순환참조 오류가 발생하게 됩니다.

서로 다른 두 수식이 서로를 참조하는 경우

서로 다른 두 수식이 각각 서로를 참조할 경우에도 순환 참조 오류가 발생하는데요. 아래 예제를 살펴보겠습니다.

A1 셀에 =B1+1 을 입력하겠습니다. 그러면 A1 셀에는 결과값으로 1이 출력됩니다.

이번에는 B1셀을 선택한 뒤, =A1+1 을 입력해보겠습니다. 그러면 A1셀과 B1셀이 서로를 참조하게 되므로 순환참조로 인해 올바르게 계산할 수 없다는 오류메시지가 출력됩니다.

자기자신을 참조하는 것과 동일한 원리로, 서로 다른 두 셀이 서로를 참조하는 경우에도 A1=B1+1 → B1=A1+1 → A1=B1+A... 의 형태로 무한대의 계산이 실행됩니다.

엑셀은 왜 순환참조 오류메시지를 출력하나요?

사실 순환참조(=재귀호출) 자체는 사용자가 의도한 경우라면 전혀 문제되지 않는 참조 방식입니다. 우리가 고등학교 수학시간에 배운 점화식(=재귀식) 또한 이러한 순환참조의 대표적인 예제인데요.

엑셀에서 이러한 순환참조가 발생할 경우에 오류메시지를 출력하는 이유는 이러한 순환참조가 '무한대의 계산을 일으키면서 엑셀 처리속도에 영향'을 주기 때문입니다. 따라서 엑셀은 자체적으로 이 순환참조를 검사하고, 시트 내 수식 중 순환참조가 일어나는 셀이 하나라도 있을 경우 순환참조가 발생했다는 오류메시지를 출력합니다.

그리고 순환참조가 발생한 셀의 결과값은 영(0) 또는 마지막 결과값을 반환하며 더 이상 계산되지 않습니다. 그리고 이러한 오류메시지는 순환참조가 발생한 셀이 여러개 있을 경우 반복적으로 메시지를 출력하지 않고, 한 번의 경고메시지만 출력합니다.

엑셀 순환참조를 피해야 하는 이유

엑셀에서 순환참조를 피해야 하는 이유는 여러가지가 있겠지만, 대표적으로 3가지 이유를 꼽을 수 있습니다.

순환참조가 발생한 셀을 찾는 방법

엑셀에서 순환참조가 발생한 셀을 찾는 방법은 매우 간단합니다. [수식] - [오류 검사] - [순환 참조]로 이동하면 현재 순환참조가 발생한 셀 주소를 모두 확인할 수 있습니다.

하지만 여러 개의 순환참조가 동시에 경우라도, '현재 발생한 순환참조에 관련된 셀의 목록' , 즉 하나의 순환참조에 대한 목록만 한 번씩 돌아가며 확인가능합니다.

엑셀 반복계산을 활성화/비활성화 하는 방법

이미 잘 알고 계시듯이, 엑셀의 반복계산은 기본 설정으로 비활성화 되어 있습니다. 하지만 반복계산이 반드시 필요한 상황이라면, 엑셀 환경설정에서 반복계산을 활성화 할 수 있습니다. (만약 순환참조가 반드시 필요한 상황이라면, 반복계산을 활성화 하는 것 보다는 VBA 매크로를 사용하는 것을 권장드립니다.)

반복계산을 활성화 할 경우 아래 2가지 값을 반드시 설정해주셔야 합니다. 대부분의 경우 엑셀에서 설정한 기본 값으로 사용해도 무방합니다.

엑셀 순환 참조 및 반복 계산을 사용하는 예제

사용자가 의도하여 순환참조를 사용해야 할 경우가 있습니다. 주로 자기자신을 참조하여, 셀이 비어있을 경우에는 특정 값으로 업데이트 하고 그렇지 않을 경우에는 기존 값을 유지하는 형식으로 많이 사용되는데요.

이와 관련하여 IF함수와 TODAY 함수를 사용한 오늘 날짜를 자동으로 입력하는 방법에 대하여 영상강의를 준비해드렸으니, 궁금하신 분은 아래 링크를 참고해보시길 바랍니다.

이상으로 엑셀 순환 참조 모든 것 포스트를 마치겠습니다. 🙂