[엑셀 실무] 수식에서 순환 참조를 찾아 제거하기

엑셀에서 수식을 작성하다보면 자신도 모르는 사이에 참조가 시작과 끝이 없이 서로 돌고 도는 엑셀 순환참조가 발생할 수 있습니다.

A1 셀에 =B1+5 의 수식이 입력되어 있습니다.
B1 셀의 수식은 =A1+10 입니다.

이런 간단한 예로 순환참조가 생기는 것입니다. 서로가 서로를 참조하고 있기에 시작도 끝도 없는 무한 계산이 진행되는 것입니다.

[그림1] 엑셀 순환 참조 경고창

이를 막기위해 바로 화면에 순환참조가 발생했음을 알리는 경고창이 뜹니다. 이때 의도하지 않은 오류이면 확인을 클릭합니다. 그러면 엑셀이 일단 셀 서로의 참조를 끊어 반복적인 계산을 막습니다. 이후는 사용자가 수정해 주어야 합니다.

1. 엑셀 순환 참조 찾기

이제는 발생한 순환 참조의 위치를 찾아 봅니다. 엑셀은 순환참조 경고 창이 뜨기에 경고창이 뜨기전 마지막 셀의 수식을 확인합니다. 이 수식에서 참조하고 있는 셀들의 수식이 원형으로 회전하고 있지 않은지 확인하는 것입니다.

예를 하나 보여 드립니다. 순환 참조를 찾아 봅니다.

[그림2] 엑셀 수식에 사용된 순환 참조 예제

시트의 왼쪽 아래를 보면 마지막 순환 참조가 발생한 셀의 주소가 표시됩니다.

순환 참조가 발생한 G4 셀을 보면 엑셀 SUM 함수식이 사용되고 있습니다. 그런데 그 인수로 사용된 범위의 종료셀 주소가 함수식이 입력된 G4 입니다. 자신을 참조하고 있는 것이지요. G4 셀의 값이 존재해야 결과를 반환하는 수식이 G4셀에 입력되어 있는 것입니다.

엑셀이 말하는 순환 참조의 한 부류입니다. 순환 참조는 A와 B가 서로를 참조하는 형식과 위의 예제와 같이 자신을 참조하는 형태의 두가지가 있을 수 있다는 것도 알아 두시기 바랍니다.

[그림3] 엑셀에서 순환 참조 셀 찾기

여러개의 순환 참조가 있을 경우는 엑셀의 오류 검사 기능 중 순환 참조를 찾는 기능을 활용하는 것도 좋을 것 같습니다.

[그림4] 엑셀 수식 분석으로 순환참조 보기

엑셀에서 순환 참조를 시각적으로 확인하는 방법이 있습니다. 엑셀 > 수식 탭 > 수식 분석 > 참조되는 셀 추적 입니다.

수식이 입력된 셀을 선택하고 '참조되는 셀 추적' 을 클릭하면 현재 선택 셀에서 참조하고 있는 셀들이 화살표로 표시됩니다.  한번 더 누르면 두개 셀의 순환 참조도 표시됩니다.

2. 엑셀 순환 참조 제거

[그림2] 의 순환 참조를 제거해 봅니다. 자신을 참조하는 순환 참조라고 하였습니다. 자신이 종료셀로 된 범위를 수정하면 되겠지요.

[그림5] 엑셀에서 순환 참조 제거

G4 셀의 인수 범위에서 종료셀에서 자신을 제외하고 F4 로 수정합니다. 대부분의 순환 참조는 실수로 생기는 것이기에 순환 참조가 생긴 수식을 보시면 바로 수정할 부분이 보일 것입니다.

[그림5] 에서는 '총점' 수식을 모두 수정하였습니다. [그림5] 의 왼쪽 아래를 보면 이제까지 보이던 순환참조 셀이 표시되지 않습니다. 시트에 더 이상 순환 참조가 존재하지 않는다는 것입니다.

이것이 엑셀에서 말하는 순환 참조입니다. 경고창이 사용자를 겁주는 오류 아닌 오류입니다. 대부분은 아니지만 순환 참조를 필요로 하는 경우도 있기 때문입니다.

참고1 : 순환 참조를 이용하여야 할 때는 엑셀 옵션 > 수식 > 계산 옵션 파트에서 통합문서 계산을 '수동' 으로 해주고 '반복 계산 사용' 을 체크하고 반복 횟수를 조정해 주면 됩니다.

[엑셀 문답] 엑셀 다른 시트 값을 참조하여 계산하려면
[엑셀 실무] 엑셀의 여러 참조 방법을 살펴봅니다

마늘빵.