Excel에서 순환 참조(Circular Reference)가 발생했다는 것은 수식이 입력된 셀이 직접 혹은 간접적으로 자기 자신을 참조하고 있다는 의미입니다. 워크시트에 순환 참조가 존재하면 Excel은 자동 계산을 제대로 수행할 수 없습니다. 순환 참조를 그대로 두면 Excel은 이전 반복(iteration)의 값을 기반으로 순환 참조에 포함된 각 셀을 계속 계산하게 되며, 이는 무한 루프를 만들어 스프레드시트의 속도를 크게 저하시킵니다.
이번 글에서는 Excel의 순환 참조가 정확히 무엇인지, 그리고 이를 어떻게 처리할 수 있는지 자세히 살펴보겠습니다.
순환 참조의 종류
수식을 입력하는 셀 자체가 해당 수식의 참조 범위에 어떤 식으로든 포함되어 있다면 이를 순환 참조라고 합니다. 순환 참조는 크게 직접(direct)과 간접(indirect) 두 가지 유형으로 나뉩니다. 수식을 입력 중인 셀 자체를 그 수식의 참조에 포함시키면 직접 순환 참조가 되고, 한 셀이 다른 셀을 참조하는데 그 다른 셀이 다시 첫 번째 셀을 참조하는 구조라면 간접 순환 참조가 됩니다.
1. 직접 순환 참조
다음 데이터 집합에서 D5부터 D11까지의 합계를 셀 D12에 구한다고 가정해 보겠습니다.
이때 실수로 셀 D12의 수식에 D12 자신을 포함하여 합산하면 직접 순환 참조가 발생합니다.
ENTER 키를 누르면 아래와 같이 순환 참조 경고 메시지가 담긴 대화 상자가 나타납니다.
확인(OK)을 클릭하면 수식 결과가 합계 대신 0으로 표시되는 것을 볼 수 있습니다. 이것이 바로 직접 순환 참조 때문에 발생하는 현상입니다.
2. 간접 순환 참조
다음 예제를 살펴보겠습니다. 셀 D6의 수식은 셀 C4를 참조하고, 셀 B8의 수식은 셀 D6를 참조합니다. 여기서 셀 C4의 수식이 셀 B8을 참조하게 되면 순환 참조가 발생합니다. 이 경우 셀 C4의 수식은 자기 자신을 직접 참조하지 않지만, 선행 셀(precedents)을 추적해 보면 어느 시점에는 C4가 참조 사슬에 포함되어 있음을 알 수 있습니다.
ENTER 키를 누르면 셀 C4에 0이 표시되고, 셀들 간의 관계는 파란색 화살표로 나타납니다.
Excel에서 순환 참조 찾고 수정하기
계산 결과를 정확하게 얻으려면 순환 참조를 찾아내고 수정하는 작업이 매우 중요합니다. 워크시트에서 순환 참조를 찾아내는 방법은 여러 가지가 있으며, 지금부터 하나씩 살펴보겠습니다.
1. 수식 표시 옵션으로 순환 참조 찾기
수식을 화면에 표시하면 데이터 시트의 순환 참조를 직접 눈으로 확인할 수 있습니다.
➤ 수식 > 수식 검사 > 수식 표시로 이동합니다.
그러면 워크시트의 모든 수식이 표시됩니다. 이제 각 수식을 하나씩 검토하면서 순환 참조를 쉽게 식별할 수 있습니다.
2. 오류 검사 옵션 활용하기
오류 검사(Error Checking) 기능을 통해서도 워크시트의 순환 참조를 확인할 수 있습니다.
➤ 수식 > 수식 검사 > 오류 검사 > 순환 참조로 이동합니다.
여기에서 순환 참조가 있는 모든 셀의 목록을 확인할 수 있습니다.
이후 수식의 참조를 올바르게 수정하는 등 필요한 조치를 취해 계산 오류를 해결하면 됩니다.
더 읽어보기: Excel 순환 참조 오류 수정 방법 (상세 가이드)
3. 상태 표시줄에서 확인하기
순환 참조를 찾는 가장 간단한 방법은 상태 표시줄을 확인하는 것입니다. 워크시트에 순환 참조가 있다면 상태 표시줄에 관련 정보가 표시됩니다.
순환 참조가 포함된 셀을 찾았다면, 이후에는 필요한 조치를 취해 손쉽게 문제를 해결할 수 있습니다.
순환 참조의 참조 관계 추적하기
순환 참조와 관련된 수식과 셀 간의 관계를 추적할 수 있는 방법에는 두 가지가 있습니다. 바로 선행 셀 추적(Trace Precedents)과 후행 셀 추적(Trace Dependents)입니다. 이 추적 도구들은 순환 참조에 해당하는 셀들 사이에 연결선을 표시해 주므로, 사용자가 순환 참조를 해결하는 데 큰 도움을 받을 수 있습니다.
1. 선행 셀 추적(Trace Precedents)
선행 셀 추적 기능은 현재 셀이 의존하고 있는 셀들을 추적합니다. 즉, 현재 수식이 결과를 계산하기 위해 필요로 하는 데이터가 들어 있는 셀들이 해당됩니다. 이 기능을 사용하면 어떤 셀들이 활성 셀에 영향을 미치는지 선으로 표시해 줍니다.
선행 셀을 추적하려면 먼저,
➤ 순환 참조가 있는 셀을 선택합니다.
➤ 수식 > 수식 검사 > 선행 셀 추적으로 이동하거나 ALT+T+U+T 키를 누릅니다.
그러면 선행 셀에서 활성 셀로 이어지는 파란색 화살표가 나타나는 것을 확인할 수 있습니다.
2. 후행 셀 추적(Trace Dependents)
후행 셀 추적 기능은 활성 셀에 의존하고 있는 셀들을 추적합니다. 즉, 결과를 산출하기 위해 활성 셀의 데이터가 필요한 셀들을 의미합니다. 이 기능은 활성 셀에 의존하는 셀들로 연결되는 선을 그려 줍니다.
후행 셀을 추적하려면 먼저,
➤ 순환 참조가 있는 셀을 선택합니다.
➤ 수식 > 수식 검사 > 후행 셀 추적으로 이동하거나 ALT+T+U+D 키를 누릅니다.
그러면 활성 셀에서 후행 셀로 이어지는 파란색 화살표가 나타나는 것을 확인할 수 있습니다.
반복 계산을 활성화하여 순환 참조 허용하기
반복 계산(Iterative Calculation)은 Excel의 흥미로운 기능 중 하나입니다. 반복 계산이 비활성화된 상태에서는 Excel이 순환 참조 경고를 표시하고 실제 결과 대신 0을 반환합니다. 하지만 워크시트에서 순환 참조를 허용하고 싶다면 반복 계산을 활성화하면 되고, 그러면 계산을 정상적으로 수행할 수 있습니다. 또한 허용할 반복 횟수를 직접 설정할 수 있으므로, 순환 참조로 인한 무한 루프도 막을 수 있습니다.
반복 계산을 켜는 방법은 다음과 같습니다.
➤ 파일 탭으로 이동한 후 옵션을 선택합니다.
그러면 Excel 옵션 창이 열립니다. 이제,
➤ 수식 탭으로 이동하여 반복 계산 사용 확인란에 체크한 뒤 확인을 클릭합니다.
이렇게 하면 반복 계산이 활성화되어 순환 참조를 사용할 수 있게 됩니다.
워크시트로 돌아가 보면, 이번에는 수식 셀이 0이 아닌 실제 값을 표시하고 순환 참조 오류도 발생하지 않습니다. 즉, 이제 Excel이 순환 참조를 허용하고 있는 것입니다.
더 읽어보기: Excel에서 순환 참조 허용하기 및 활용 방법
반복 계산은 왜 비활성화 상태로 두어야 할까?
특별히 필요한 경우가 아니라면 Excel에서 반복 계산을 활성화하는 것은 권장하지 않습니다. 반복 계산이 켜져 있으면 Excel이 많은 시스템 자원을 소모하며, 단순한 계산 하나를 수행하는 데에도 훨씬 더 많은 시간이 걸립니다. 또한 반복 계산이 활성화된 상태에서는 순환 참조로 인한 잘못된 계산 결과가 발생해도 눈치채지 못한 채 넘어갈 수 있습니다. 따라서 꼭 필요한 경우가 아니라면 반복 계산은 비활성화 상태로 유지하는 것이 좋습니다.
마무리
이제 Excel에서 순환 참조가 무엇인지, 그리고 이를 어떻게 처리해야 하는지 이해하셨기를 바랍니다. 궁금한 점이 있다면 언제든지 댓글로 남겨주세요.
관련 글
- Excel에서 순환 참조 제거하는 방법 (2가지)
- Excel에서 순환 참조 찾기 (쉬운 2가지 팁)