이 글에서는 엑셀의 '순환 참조' 메뉴에 목록으로 표시되지 않는 순환 참조 오류를 찾아 수정하는 방법을 소개합니다. 엑셀 작업 중 순환 참조 오류를 만나면 상당히 당황스러울 수 있습니다. 특히 수천 개의 셀이 포함된 대용량 데이터를 다룰 때는 각 셀을 하나씩 확인하며 순환 참조 오류가 있는 셀을 찾는 것이 거의 불가능에 가깝습니다. 따라서 이 튜토리얼에서는 데이터 크기와 상관없이 순환 참조 오류를 손쉽게 찾아내고 해결하는 방법을 단계별로 설명해 드리겠습니다.
순환 참조란 무엇인가?
순환 참조(Circular Reference)란 수식이 계산 과정에서 자기 자신 또는 연결된 다른 셀을 반복적으로 참조하여 무한 루프가 발생하는 현상을 말합니다. 이로 인해 스프레드시트의 처리 속도가 심각하게 느려질 수 있습니다.
순환 참조를 더 명확하게 이해하기 위해 아래 데이터 예시를 살펴보겠습니다. 이 데이터는 6개월간의 판매액(Sales Amount)으로 구성되어 있으며, C11 셀에 총 판매액을 계산하려고 합니다.
SUM 수식으로 결과를 얻으려면 범위 (C6:C10)을 선택해야 합니다. 그런데 실수로 범위를 (C6:C11)로 선택하면 예상과 다른 결과가 나타날 수 있습니다.
이렇게 되면 C11 셀의 수식이 경고 메시지와 함께 순환 참조 오류를 발생시킵니다. 그 이유는 C11 셀의 수식이 자기 자신까지 계산 범위에 포함시키려 하기 때문입니다.
순환 참조 오류는 두 가지 유형으로 나눌 수 있습니다.
1. 직접 순환 참조(Direct Circular Reference):
수식이 자신이 입력된 셀을 직접 참조할 때 발생하는 오류입니다.
2. 간접 순환 참조(Indirect Circular Reference):
수식이 자신이 입력된 셀을 직접 참조하지는 않지만, 다른 셀을 거쳐 간접적으로 자기 자신을 참조하게 될 때 발생하는 오류입니다.
목록에 표시되지 않는 엑셀 순환 참조를 수정하는 4가지 쉬운 방법
계산 중 순환 참조 오류가 발생하면 즉시 수정해야 합니다. 오류를 수정하려면 먼저 해당 위치를 찾아야 하는데요. 이 글에서는 순환 참조 오류를 찾아내는 4가지 방법을 소개하고, 이어서 수식을 수정하여 오류를 해결하는 과정까지 안내합니다.
1. 리본 메뉴의 '오류 검사' 도구로 순환 참조 수정하기
첫 번째 방법은 엑셀 리본 메뉴의 '오류 검사(Error Checking)' 도구를 활용하는 것입니다. 설명을 위해 C11 셀에 순환 참조 오류가 있는 아래 데이터를 사용하겠습니다. 실제 작업 환경에서는 수천 개의 셀 사이에서 순환 참조를 찾아내야 할 수도 있습니다.
'오류 검사' 도구로 순환 참조 오류를 찾는 단계를 살펴보겠습니다.
실행 단계:
- 먼저 수식(Formulas) 탭으로 이동합니다.
- 다음으로 수식 탭 아래 리본 메뉴에서 '오류 검사' 드롭다운 버튼을 클릭한 뒤, '순환 참조(Circular References)' 옵션을 선택합니다.
- 그러면 워크시트 왼쪽에 사이드바가 열리고, C11 셀에서 순환 참조가 발생했음을 표시해 줍니다.
- 요약: 수식 > 오류 검사 > 순환 참조
- 표시된 C11 셀을 선택합니다. 이 셀의 수식은 현재 자기 자신까지 계산하려 하고 있습니다.
- 이제 C11 셀의 수식을 아래와 같이 수정합니다.
=SUM(C5:C10)
- Enter 키를 누릅니다.
- 마지막으로 C11 셀에서 더 이상 순환 참조 오류가 발생하지 않으며, 총 판매액 $17,000이 정상적으로 표시됩니다.
2. 상태 표시줄로 순환 참조 찾아 수정하기
'상태 표시줄(Status Bar)'을 이용하면 순환 참조 오류를 가장 간편하게 찾을 수 있습니다. 앞선 예제와 동일한 데이터를 사용해 상태 표시줄로 순환 참조를 찾고 수정하는 방법을 살펴보겠습니다.
실행 단계:
- 먼저 순환 참조 오류가 있는 워크시트를 엽니다.
- 다음으로 워크시트 이름 아래에 있는 '상태 표시줄'을 확인합니다.
- 상태 표시줄에 C11 셀에 순환 참조 오류가 있다는 정보가 표시됩니다.
- 이후 C11 셀의 수식에서 범위를 (C5:C11)에서 (C5:C10)으로 변경합니다.
=SUM(C5:C10)
- Enter 키를 누릅니다.
- 마지막으로 위의 조치로 C11 셀의 순환 참조 오류가 해결되고, 정확한 합계가 반환됩니다.
참고:
워크시트에 순환 참조가 있는 셀이 두 개 이상 있는 경우, 상태 표시줄에는 가장 마지막에 발생한 하나만 표시됩니다.
3. 반복 계산 기능으로 순환 참조 해결하기
반복 계산(Iterative Calculation) 기능을 활성화하면 순환 참조 오류를 제거하지 않고도 계산이 가능합니다. 워크시트에서 반복 계산을 허용하면 순환 참조가 있어도 지정한 횟수만큼 계산을 반복해 결과값을 반환합니다. 역시 앞선 예제의 데이터를 사용해 설명하겠습니다.
실행 단계:
- 먼저 파일(File) 탭으로 이동합니다.
- 다음으로 옵션(Options)을 선택합니다.
- 그러면 'Excel 옵션' 대화 상자가 나타납니다.
- 여기서 수식 항목을 선택하고 '반복 계산 사용(Enable iterative calculation)' 옵션에 체크합니다. 그리고 '최대 반복 횟수(Maximum iterations)' 값을 1로 설정합니다. 값이 1이라는 것은 수식이 C5부터 C10까지 한 번만 반복 계산한다는 의미입니다.
- 이제 확인(OK)을 누릅니다.
- 마지막으로 C11 셀에서 더 이상 순환 참조 오류가 발생하지 않고, 총 판매액이 정상적으로 계산되어 표시됩니다.
4. 수식 추적 기능으로 순환 참조 찾아 수정하기
순환 참조는 클릭 한 번으로 바로 찾아 수정할 수 없습니다. 따라서 추적 기능을 사용해 하나씩 찾아낸 후, 원래 수식을 수정하여 오류를 해결해야 합니다. 이번 섹션에서 사용할 추적 방법은 '선행 대상 추적(Trace Precedents)'과 '후행 대상 추적(Trace Dependents)'입니다.
4.1 '선행 대상 추적' 기능으로 순환 참조 수정하기
'선행 대상 추적' 기능은 현재 선택한 셀에 영향을 주는 셀들을 파악할 때 사용합니다. 이 기능은 화살표 선을 그려 어떤 셀들이 활성 셀에 영향을 미치는지 시각적으로 보여줍니다. 아래 데이터에서 C11 셀에는 (C5:C10) 범위의 합계가 반환되므로, (C5:C10) 셀들이 C11 셀에 영향을 주는 관계입니다.
'선행 대상 추적'의 사용 단계를 하나씩 살펴보겠습니다.
실행 단계:
- 먼저 C11 셀을 선택합니다.
- 다음으로 수식 탭으로 이동합니다.
- 그런 다음 '선행 대상 추적' 옵션을 선택합니다.
- 화살표 선이 그려지면서 (C5:C11) 셀들이 C11 셀에 영향을 주고 있음을 보여줍니다. C11 셀이 자기 자신을 계산하려 하기 때문에 순환 참조 오류가 반환되는 것입니다.
- 세 번째로 C11 셀의 수식에서 범위를 (C5:C11)에서 (C5:C10)으로 변경합니다. 수정된 수식은 다음과 같습니다.
=SUM(C5:C10)
- 이후 Enter 키를 누르면 해당 셀에서 순환 참조가 제거됩니다.
- 마지막으로 C11 셀에서 다시 '선행 대상 추적' 옵션을 실행해 보면, 이번에는 (C5:C10) 셀들만 C11 셀에 영향을 주는 것을 확인할 수 있습니다. 이전 단계에서는 (C5:C11)이 영향을 주고 있었죠.
참고:
선행 대상 추적의 단축키: 'Alt + T U T'
4.2 '후행 대상 추적' 기능으로 순환 참조 수정하기
'후행 대상 추적' 기능은 활성 셀에 종속된, 즉 활성 셀의 값을 참조하고 있는 셀들을 찾을 때 사용합니다. 이 기능 역시 화살표 선을 그려 활성 셀에 의존하는 셀들을 보여줍니다. 아래 데이터에서 '후행 대상 추적' 옵션으로 순환 참조 오류를 찾아보겠습니다.
'후행 대상 추적'으로 순환 참조를 찾는 단계를 살펴보겠습니다.
실행 단계:
- 먼저 C11 셀을 선택합니다.
- 다음으로 수식 탭으로 이동합니다.
- 리본 메뉴에서 '후행 대상 추적' 옵션을 선택합니다.
- 그러면 화살표 선을 통해 (C5:C10) 셀들이 C11 셀에 종속되어 있음을 보여줍니다.
- 이후 C11 셀의 수식에서 범위를 (C5:C11)에서 (C5:C10)으로 변경합니다. 수정된 수식은 다음과 같습니다.
=SUM(C5:C10)
- Enter 키를 누릅니다.
- 마지막으로 C11 셀에서 더 이상 순환 참조가 발생하지 않는 것을 확인할 수 있습니다.
참고:
후행 대상 추적의 단축키: 'Alt + T U D'
마무리
지금까지 엑셀에서 목록에 표시되지 않는 순환 참조를 찾아 수정하는 다양한 방법을 살펴보았습니다. 오류 검사 도구, 상태 표시줄, 반복 계산, 수식 추적 기능까지 상황에 맞게 활용하면 수천 개의 셀에서도 순환 참조를 빠르게 해결할 수 있습니다. 이 글에 포함된 연습용 워크북을 다운로드해 직접 실습해 보세요. 궁금한 점이 있다면 아래 댓글로 남겨주시면 최대한 빠르게 답변드리겠습니다. 앞으로도 유용한 Microsoft Excel 활용 팁을 계속 소개해 드리겠습니다.
함께 보면 좋은 글
- 엑셀에서 순환 참조 제거하는 2가지 방법
- 엑셀에서 순환 참조 허용 설정 및 2가지 활용 사례