엑셀에서 수식의 표현 중 순환참조 라는 것이 있다. 순환참조는 오류라고 볼 수도 있겠지만, 어떤 수식에서는 의도적인 순환참조가 쓰이기도 한다. 실무에서는 특정 분야가 아니고서야 그렇게 많이 쓰이지는 않으므로(가끔 쓰인다.) 순환참조 오류를 찾고 수정하는 정도만 알면 된다. 여기서는 순환참조 찾기 및 수정하는 방법과 의도적인 순환참조를 통해서 나타낼 수 있는 수학적 수식을 예를 들어보겠다.
순환참조란?
순환참조는 프로그래밍 기법에서는 재귀(recursive)적 호출을 의미한다. 쉽게 말하면 자기 자신을 반복해서 호출하는 것이다. 엑셀에서는 프로그래밍에서 사용하는 것보다 매우 제한되게 사용된다. 따라서 엑셀에서 의도적 순환참조를 사용하려면 VBA를 통해서 사용하는 것이 좋다고 생각한다.(VBA는 여기서는 안 다룬다.)
점화식이라고 고등학교때 배운 수식이 있다. 점화식 또한 순환참조다. a = a + 1/2 이런 식을 본적이 있을 것이다. 결과값 a 가 다시 수식에 포함되기 때문에 무한 반복 호출이 된다. 다음은 순환참조를 통해 a = a+1/2를 구현한 것이다.
순환참조 경고가 뜨면 확인을 눌러준다. 아무런 설정 없이 순환 참조를 사용하면 결과는 0으로 된다. 순환참조를 의도적으로 사용하기 위해서는 다음 과정이 필요하다.
의도적 순환참조 사용하기
Excel 옵션에 들어가서 수식->반복계산 사용 에 체크를 한다. 이 때 반복횟수를 정할 수 있다.
위와 같이 설정을 하면 다음과 같이 결과 값이 제대로 출력된다. 0.5가 A1에 100번 더해졌으므로 50이 맞다.
순환참조 찾기
의도적이든 의도적이 아니든 순환참조가 되고 있는 위치를 찾는 방법이다. 우선 의도적 순환참조 사용을 하지 말아야 한다. 위에서 설정한 반복계산 사용에 체크를 해제해야 순환참조 찾기를 할 수 있다.(중요)
체크를 해제한 후 수식탭으로 간다.
수식 - 수식분석 - 오류검사
A1이 떠있는데 이 경우 A1의 위치로 가서 직접 순환참조에 대한 문제를 해결한다. 시트내에 순환참조가 여러개가 있더라도 순환참조는 한개씩 검사되므로 (검사->해결)이 작업을 반복해야 한다.
위와 같은 표시는 간접 순환 참조를 나타낸다.
A1셀
=D4
D4셀
=A1
더 복잡한 순환도 가능하다.
순환참조 예제와 주의점
실용적인 예
예를 들어 커피숍을 운영하는데 총 이익이 100이라고 하고 자재비가 30이라고 하자 여기서 순이익이 70이 되는데, 순이익의 1/10을 건물 유지비에 사용해야 한다고 한다. 건물 유지비 또한 비용에 해당하므로 순이익에서 차감을 해야 한다. 이 경우 순환 참조가 발생한다.
B3=B4*0.1
B4=B1-B2-B3
반복 계산 사용
점화식 예
위에서 a=a+0.5의 예에서 반복계산 횟수를 100으로 설정했다. 이 문서를 저장하고 다시 문서를 열면 A1에 50의 결과가 나온다. 여기에 F9(계산하기 단축키)를 누르면 시트내 수식들이 다시 계산이 되는데 A1이 50인 상태에서 시작되므로 100이 된다. 또 F9를 누르면 150 이런 식으로 된다. 반복횟수를 증가시키는 효과가 있다. 그런데 특정 수식에 커서를 위치시키고 엔터를 친다던지 하면 그 셀만 초기화가 되므로 커서를 위치한 셀은 0부터 다시 시작된다. 따라서 두 개 이상의 셀을 가지고 순환참조를 할 경우 결과값에 주의해야 한다.
예)
다음은
a=a + 0.5^n (a=1부터 시작) 의 점화식이다.
C언어로 간단히 작성하여 계산한 결과다. 결과는 2에 무한히 가까워진다.
엑셀로 구현은 다음과 같이 한다.
A1셀
=IF(A1=0,1,A1+B1)
B1셀
=IF(B1=0,0.5,B1*0.5)
IF문을 사용하는 이유는 초기값을 1로 해 주기 위해서다. 각 셀의 기본 초기값은 0이므로 만약 초기값이 0이면 1로 한다는 뜻이다. B1셀은 0.5부터 시작한다.
따라서 1회 반복 시 A1은 1이 되고, B1은 0.5가 된다. 이 이후로는 A1 = A1+B1을 계산하고 B1은 B1*0.5를 계산하여 A1 = A1+B1*0.5를 계산하게 된다. 각 초기값을 대입하면 2회 계산이 된다. 다음 3회째는 A1=1.5, B1=0.25가 대입되고, 이런 식으로 반복 계산이 이루어진다.
100회 계산 후 모습
주의점
F9를 누른 경우 전체 시트의 계산이 이루어진다.
위 결과에서 F9를 누르면 총 200회 반복이 된다.
여기서 A1 셀의 수식을 초기화 시켜보면
결과 값이 이와 같이 엉뚱하게 나온다. 즉 B의 값은 200회 반복된(0.5^200) 상태에서 A값만 1로 초기화 되었기 때문이다. 따라서 엑셀에서 순환참조를 의도적으로 사용하려면 수식을 수정할 때 주의해야 하고, 셀 간에 복잡한 순환 참조 관계를 사용하려면 수식을 수정한 후 문서를 재실행 하는 것이 좋다.
그래도 순환참조를 사용하여 간단한 재귀 방정식을 세워 차트로 나타낼 수도 있기 때문에 이에 관한 내용은 어떤 이에게는 매우 유용하다. 이 내용은 조만간 다루어 보겠다.