Excel 시트에 수식을 입력하려는데 제대로 작동하지 않고 대신 순환 참조(circular reference) 경고 메시지가 표시되는 경험이 있으신가요? 이는 매일 수많은 사용자가 겪는 문제로, Excel 함수에게 해당 수식이 입력된 셀 자체를 계산하도록 요청했을 때 발생합니다. 이 글에서는 Excel의 순환 참조 오류가 무엇인지, 왜 피해야 하는지 기본기를 알려드리고, 오류를 분석·탐색·해결하는 방법과 함께, 다른 방법으로 해결이 어려울 경우 순환 수식을 활성화하고 활용하는 방법까지 모두 다룹니다.
순환 참조란 무엇일까요?
셀에 수식을 입력했을 때 그 수식이 자신이 입력된 셀을 참조하게 되면 순환 참조가 만들어집니다. 마치 뱀이 자신의 꼬리를 쫓는 것처럼, 계산 과정이 끝없이 되돌아오는 구조입니다.
쉽게 말해, 순환 참조는 수식이 직접적 혹은 간접적으로 자기 자신의 셀을 다시 참조하여 계산이 무한 반복되는 상황을 말합니다. 이 무한 참조 고리가 멈추지 않으면 셀의 값은 계속해서 변경됩니다.
구체적인 예시로 살펴보겠습니다.
다음 표에는 세계 최대 브랜드 중 하나인 Apple의 지난 10년간 매출 데이터가 정리되어 있습니다. 여기서 SUM 함수를 사용해 C16 셀에 총매출을 계산하려 합니다. 그런데 범위를 C5:C16으로 지정했다고 가정해 보겠습니다.
결과값이 입력될 C16 셀이 참조 범위에 포함되어 있기 때문에 무한 루프가 발생합니다. 그 결과 Excel은 새 값을 C16 셀에 계속 더하게 되며, 값은 지수적으로 증가만 하게 됩니다.
이때 Excel은 아래 이미지와 같이 순환 참조 경고를 표시합니다.
📝 참고.
- 순환 참조는 엄밀히 말해 '오류'가 아닙니다. 계산을 중단하거나 사용자에게 수정을 강제하지 않으며, 계산 결과가 잘못될 가능성이 있음을 알려주는 알림입니다.
- 여러 개의 순환 참조 수식을 한 번에 입력할 경우, Excel은 경고 메시지를 매번 표시하지 않는 경우가 많습니다.
반복 계산으로 순환 참조 오류 활성화/비활성화하기
반복 계산(Iterative Calculation)은 Excel의 흥미로운 기능 중 하나입니다. 사용자가 반복 계산을 비활성화하면 Excel은 순환 참조 경고를 표시하고 실제 결과 대신 0을 셀에 표시합니다. 계산이 끝나지 않는 무한 루프이기 때문입니다.
대용량 데이터를 다루면서 순환 참조 경고에 신경 쓰지 않고 작업에 집중하고 싶다면 반복 계산 기능을 활용하는 것이 좋습니다.
반복 계산 활성화/비활성화 방법
아래 스크린샷에서 워크시트 상태 표시줄에 C16 셀에 순환 참조가 있다고 표시된 것을 확인할 수 있습니다. 그 결과 C16 셀에는 0(0)이 나타납니다. 문제없이 작업을 진행하려면 반복 계산을 활성화해야 합니다.
그렇다면 Excel에서 반복 계산을 어떻게 켜고 끌 수 있는지 살펴보겠습니다. 다음 단계를 따라 해 보세요.
단계:
- 파일 탭 ⇨ 옵션 ⇨ 数식(Formulas)으로 이동합니다.
- 반복 계산 사용(Enable iterative calculation) 확인란에 체크합니다.
- Enter 키를 눌러 확인합니다.
설정 후에는 아래 스크린샷처럼 상태 표시줄에 '계산(Calculate)'이라는 문구가 나타나고, C16 셀에도 0(0)이 아닌 실제 값이 표시됩니다.
이제 순환 참조와 연관된 모든 셀들이 정상적으로 계산됩니다.
최대 반복 횟수와 최대 변화량 매개변수
반복 계산에는 두 가지 주요 매개변수가 있습니다.
최대 반복 횟수(Maximum Iterations)
Excel이 최종 결과를 산출하기 위해 반복할 루프의 횟수입니다. 이 값은 사용자가 직접 조정할 수 있습니다. 다만 반복 횟수가 많아질수록 Excel의 작업 부담이 커지므로 더 많은 시스템 리소스와 처리 능력이 필요하고, 소요 시간도 길어진다는 점을 유념하세요.
최대 변화량(Maximum Change)
최대 변화량은 반복 계산을 계속 진행하기 위해 충족되어야 하는 값으로, 결과의 정밀도와 관련이 있습니다. 정확한 결과를 얻으려면 이 값을 최대한 낮게 설정하는 것이 좋습니다. 기본값은 0.001로 설정되어 있습니다.
왜 반복 계산을 의도적으로 활성화하는 것을 피해야 할까요?
Excel에서 순환 참조를 의도적으로 사용하는 것은 좋은 습관이 아닙니다. 반복 계산을 켠 상태에서 순환 참조를 활용하는 방식은 많은 리소스와 컴퓨팅 파워를 소모하는 까다로운 접근법입니다. 특히 나중에 몇 가지를 수정해야 할 때 원치 않는 결과나 부정확한 값이 나오는 경우가 많아 답답함을 유발할 수 있습니다.
Excel에서 순환 참조 오류 찾기
순환 참조 오류가 발생하면 경고 메시지가 표시되지만, 문제가 발생한 셀이 정확히 어디인지는 직접 찾아야 합니다. 오류가 있는 셀의 위치를 파악하면 수정 작업이 훨씬 수월해집니다.
특히 대용량 데이터 세트를 다룰 때 아래 방법들은 매우 유용합니다.
1. 오류 검사 드롭다운 메뉴 활용하기
Excel 리본 메뉴를 통해 순환 참조를 찾는 방법입니다.
1단계:
- 수식(Formulas) 탭으로 이동합니다.
- 오류 검사(Error Checking) 드롭다운 메뉴를 클릭합니다.
2단계:
- 목록에서 순환 참조(Circular References)를 선택합니다.
이 섹션에서는 워크시트 내의 모든 순환 참조 목록을 확인할 수 있습니다.
위 스크린샷에서 볼 수 있듯이, 현재 사용 중인 스프레드시트에는 C16 셀에 하나의 순환 참조가 있습니다.
더 읽어보기: Excel에서 순환 참조 찾는 방법 (2가지 쉬운 방법)
2. 상태 표시줄 활용하기
상태 표시줄(status bar)을 이용하면 순환 참조를 아주 간단하게 찾을 수 있습니다. 워크시트에 순환 참조가 존재한다면, 워크시트 이름 아래쪽 상태 표시줄에서 해당 셀 주소를 확인할 수 있습니다.
📝 참고.
- 반복 계산 옵션이 켜져 있으면 이 기능이 비활성화됩니다. 따라서 통합 문서에서 순환 참조를 검사하기 전에 먼저 반복 계산을 꺼야 합니다.
- 상태 표시줄에는 가장 마지막 순환 참조만 표시되며, 거기서부터 거슬러 올라가며 확인할 수 있습니다.
더 읽어보기: Excel에서 순환 참조 허용하기 (실전 활용 사례 2가지)
Excel에서 순환 참조 오류 수정하기
순환 참조는 클릭 한 번으로 해결되지 않습니다. 하나씩 찾아내 제거해야 하는 것이 원칙입니다.
예를 들어 C16 셀에서 오류를 발견했다면, 올바른 수식을 다시 작성하거나 정확한 범위를 선택해야 합니다.
1단계:
- C16 셀의 오류를 수정하기 위해 올바른 참조 범위를 선택합니다.
2단계:
- C16 셀에서 Enter 키를 눌러 결과를 확인합니다.
더 읽어보기: Excel에서 순환 참조 제거하는 방법 (2가지 방법)
수식과 셀 간의 관계 추적하기
Excel의 순환 참조가 눈에 띄지 않는 경우에는 트레이스 선행 셀(Trace Precedents)과 트레이스 후행 셀(Trace Dependents) 기능을 사용하여, 어떤 셀이 선택된 셀에 영향을 미치거나 영향을 받는지 화살표 선으로 시각화할 수 있습니다.
아래 스크린샷에는 Apple의 2022년 예상 매출이 정리되어 있고, 2011년부터 2022년까지의 총매출을 구하려 합니다. 이를 위해 D10 셀에 다음 수식을 입력했습니다.
그런데 C16 셀에 순환 참조 오류가 있기 때문에 D10 셀에도 오류 값이 나타납니다. 이런 경우 트레이스 선행 셀과 트레이스 후행 셀 기능을 활용해 수식과 셀 간의 관계를 추적하면 됩니다.
트레이스 선행 셀(Trace Precedents)로 순환 참조 추적하기
트레이스 선행 셀은 수식에 데이터를 공급하는 셀들을 추적하여, 어떤 셀들이 선택된 셀에 영향을 주는지 선으로 표시해 줍니다. 아래 단계를 따라 해 보세요.
단계:
- Excel에서 수식(Formulas) 탭으로 이동합니다.
- 트레이스 선행 셀(Trace Precedents)을 선택합니다.
그러면 활성 셀에 영향을 주는 셀들을 가리키는 화살표가 표시됩니다.
📝 참고. 트레이스 선행 셀 단축키: Alt+T, 이후 U T
트레이스 후행 셀(Trace Dependents)로 순환 참조 추적하기
트레이스 후행 셀은 활성 셀에 종속된 셀들을 추적하여, 어떤 셀들이 선택된 셀의 영향을 받는지 선으로 표시해 줍니다. 아래 단계를 따라 해 보세요.
단계:
- Excel에서 수식(Formulas) 탭으로 이동합니다.
- 트레이스 후행 셀(Trace Dependents)을 선택합니다.
그러면 현재 활성 셀을 참조하는 수식이 있는 셀들이 표시됩니다.
📝 참고. 트레이스 후행 셀 단축키: Alt+T, 이후 U D
마무리
지금까지 Excel에서 순환 참조 오류를 수정하는 자세한 방법을 안내해 드렸습니다. 위에서 소개한 모든 절차를 익히고 실제 데이터에 적용해 보세요. 연습 통합 문서를 열어 배운 내용을 직접 테스트해 보는 것도 큰 도움이 됩니다. 여러분의 소중한 관심이 이런 유용한 튜토리얼을 계속 만들어가는 동력이 됩니다.
궁금한 점이 있다면 언제든지 질문해 주세요. 아래 댓글 섹션에 의견을 남겨주셔도 좋습니다.
Exceldemy 팀은 언제나 여러분의 문의에 빠르게 답변드립니다.
함께 배우고 성장하세요!
관련 글
- Excel에서 목록에 표시되지 않는 순환 참조 해결 방법 (4가지 쉬운 방법)