엑셀 순환참조 문제 해결하기

누군가로부터 엑셀 파일을 받아서 열었는데 다음과 같이 순환참조가 있다는 경고가 나타날 때가 있습니다. 이번 글에서는 왜 순환참조가 발생하고 어떻게 해결하는 지 알아 보겠습니다.

1. 순환참조가 발생하는 이유

순환 참조는 아래 그림과 같이 [B3] 셀은 [B7] 셀을 참조하고, [B7] 셀은 [B3]셀을 참조하고, 다시 [B3] 셀은 [B7]을 참조하고... 순환하면서 무한히 반복 참조하는 것을 말합니다.

2개의 셀이 서로 참조하면서 순환참조가 발생할 수도 있지만 아래와 같이 여러 개의 셀이 참조하면서 순환고리가 만들어지면 순환참조가 발생하는 경우도 있습니다.

순환참조가 발생하면 참조를 무한 반복하면서 엑셀이 멈출 수 있으므로 엑셀에서는 계산을 중지하고 위와 같이 순환참조 표시를 줍니다.

순환참조를 의도적으로 만들지 않았다면 대부분은 복잡한 수식을 작성하면서 실수로 참조의 위치를 잘못 지정해서 순환참조가 발생합니다. 이 때는 순환참조가 발생한 셀에서 참조의 위치를 확인하여 바른 위치를 참조할 수 있도록 바꿔주면 문제가 해결됩니다.

다음과 같이 '대리점 유형별 월별 매출 현황' 집계 자료가 있다고 할 때,

[D5] 셀에는 2월 총합계를 구하는 수식 =SUM(D11,D6) 이 입력되어 있고 이 수식은 [D11], [D6]셀을 참조하고 있습니다.

그런데 실수로 [D11] 셀에 =D5라는 수식을 입력하면 [D5] 셀과 [D11]셀이 서로 참조하면서

다음과 같이 순환참조 경고가 나타납니다.

동시에 아래와 같이 순환참조 위치가 표시됩니다.

[주의!] 작업 중인 엑셀 파일에 또 다른 순환참조가 있는 경우에는 위의 경고메시지가 나타나지 않고 순환참조 연결선만 표시될 수도 있습니다.

[D11] 셀의 수식을 잘못 입력해서 순환참조가 발생했으므로 [D11] 셀의 수식을 =SUM(D12:D15)으로 바꿔주면 문제가 해결됩니다.

누군가로부터 엑셀 파일을 받아서 열었는데 다음과 같이 순환참조가 있다는 경고가 나타날 때 <확인>을 눌러도 순환참조가 발생한 위치로 이동하지 않습니다.

위의 경우가 아니더라도 순환참조가 발생한 파일에 여러 개의 시트가 있어서 순환참조가 발생한 위치를 찾기 어려울 때가 있습니다.

이 때는 리본 메뉴 [수식] 탭 → [수식 분석]그룹 → [오류 검사] 옆의 드롭다운 버튼 → [순환 참조] 를 누르면 순환참조가 발생한 셀의 위치를 표시해 줍니다. 표시된 위치 중 하나를 선택해서 클릭하면 순환참조가 발생한 위치로 이동합니다.

대부분의 경우 거의 사용할 일이 없지만 일부 업무에서 반복 계산을 하기 위해 의도적으로 순환참조를 사용해야 할 때가 있습니다.

이때는 다음과 같이 엑셀 옵션에서 조정하면 됩니다.

[엑셀 옵션] → [수식] → [계산 옵션]에서 '반복 계산 사용'을 체크하고 최대 반복 횟수와 변화 한도값을 필요한 값으로 조정하면 됩니다.

엑셀 수식 강좌

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

온라인 구매처: 예스24 교보문고 알라딘