Excel에서 사용자가 접하는 가장 일반적인 오류 경고 중 하나는 '순환 참조'입니다. 수천 명의 사용자가 동일한 문제를 겪고 있으며 수식이 자체 셀을 직간접적으로 참조할 때 발생하여 끝없는 계산 루프가 발생합니다.
예를 들어, 셀 B1과 B2에 두 개의 값이 있습니다. 수식 =B1+B2를 B2에 입력하면 순환 참조가 생성됩니다. B2의 수식은 계산할 때마다 B2 값이 변경되기 때문에 반복적으로 자신을 다시 계산합니다.
대부분의 순환 참조는 의도하지 않은 실수입니다. Excel은 이에 대해 경고합니다. 그러나 반복 계산을 수행하는 데 사용되는 순환 참조도 있습니다. 워크시트의 의도하지 않은 순환 참조로 인해 수식이 잘못 계산될 수 있습니다.
따라서 이 기사에서는 순환 참조에 대해 알아야 할 모든 것과 Excel에서 순환 참조를 찾고 수정하고 제거하고 사용하는 방법에 대해 설명합니다.
Excel에서 순환 참조를 찾고 처리하는 방법
Excel로 작업할 때 수식이 있는 셀이 포함된 수식을 입력할 때 발생하는 순환 참조 오류가 발생하는 경우가 있습니다. 기본적으로 수식이 자체 계산을 시도할 때 발생합니다.
예를 들어, A1:A4 셀에 숫자 열이 있고 A5 셀에서 SUM 함수(=SUM(A1:A5))를 사용하고 있습니다. A5 셀은 자신의 셀을 직접 참조하는데 이는 잘못된 것입니다. 따라서 다음 순환 참조 경고가 표시됩니다.
위의 경고 메시지가 나타나면 '도움말'버튼을 클릭하여 오류에 대해 자세히 알아보거나 '확인'또는 'X'버튼을 클릭하여 오류 메시지 창을 닫고 결과로 '0'을 얻을 수 있습니다.
때때로 순환 참조 루프로 인해 계산이 중단되거나 워크시트 성능이 저하될 수 있습니다. 순환 참조는 즉시 분명하지 않은 여러 다른 문제로 이어질 수도 있습니다. 따라서 이러한 것들은 피하는 것이 좋습니다.
직접 및 간접 순환 참조
회람 참조는 직접 회람 참조와 간접 회람 참조의 두 가지 유형으로 분류할 수 있습니다.
직접 참조
직접 순환 참조는 매우 간단합니다. 수식이 자체 셀을 직접 참조할 때 직접 순환 참조 경고 메시지가 나타납니다.
아래 예에서 셀 A2의 수식은 자체 셀(A2)을 직접 참조합니다.
경고 메시지가 나타나면 '확인'을 클릭할 수 있지만 결과는 '0'뿐입니다.
간접 순환 참조
Excel의 간접 순환 참조는 수식의 값이 자체 셀을 다시 참조하지만 직접 참조하지 않을 때 발생합니다. 즉, 순환 참조는 서로 참조하는 두 개의 셀에 의해 형성될 수 있습니다.
이 간단한 예를 들어 설명하겠습니다.
이제 값은 값이 20인 A1에서 시작합니다.
다음으로 셀 C3은 셀 A1을 참조합니다.
그런 다음 셀 A5는 셀 C3을 참조합니다.
이제 A1 셀의 값 20을 아래와 같이 수식으로 바꿉니다. 다른 모든 셀은 A1 셀에 종속됩니다. A1에서 다른 이전 수식 셀의 참조를 사용하면 순환 참조 경고가 발생합니다. A1의 수식은 C3을 참조하는 셀 A5를 참조하고 셀 C3은 A1을 다시 참조하므로 순환 참조입니다.
'확인'을 클릭하면 A1 셀에 값이 0이 되고 Excel은 아래와 같이 선행 추적 및 종속 추적을 표시하는 연결된 선을 만듭니다. 이 기능을 사용하여 순환 참조를 쉽게 찾고 수정/제거할 수 있습니다.
Excel에서 순환 참조를 활성화/비활성화하는 방법
기본적으로 반복 계산은 Excel에서 해제(비활성화)되어 있습니다. 반복 계산은 특정 조건을 충족할 때까지 반복 계산입니다. 비활성화되면 Excel은 순환 참조 메시지를 표시하고 결과로 0을 반환합니다.
그러나 때때로 루프를 계산하기 위해 순환 참조가 필요합니다. 순환 참조를 사용하려면 Excel에서 반복 계산을 활성화해야 하며 이를 통해 계산을 수행할 수 있습니다. 이제 반복 계산을 활성화하거나 비활성화하는 방법을 보여드리겠습니다.
Excel 2010, Excel 2013, Excel 2016, Excel 2019 및 Microsoft 365에서 Excel의 왼쪽 상단 모서리에 있는 '파일' 탭으로 이동한 다음 왼쪽 창에서 '옵션'을 클릭합니다.
Excel 옵션 창에서 '수식' 탭으로 이동하여 '계산 옵션' 섹션에서 '반복 계산 활성화' 확인란을 선택합니다. 그런 다음 '확인'을 클릭하여 변경 사항을 저장합니다.
이것은 반복적인 계산을 가능하게 하고 순환 참조를 허용합니다.
이전 버전의 Excel에서 이 작업을 수행하려면 다음 단계를 따르세요.
- Excel 2007에서 Office 버튼 > Excel 옵션 > 수식 > 반복 영역을 클릭합니다.
- Excel 2003 및 이전 버전에서는 메뉴 > 도구 > 옵션 > 계산 탭으로 이동해야 합니다.
최대 반복 및 최대 변경 매개변수
반복 계산을 활성화하면 아래 스크린샷과 같이 반복 계산 활성화 섹션에서 사용 가능한 두 가지 옵션을 지정하여 반복 계산을 제어할 수 있습니다.
- 최대 반복 – 이 숫자는 최종 결과를 제공하기 전에 공식을 다시 계산해야 하는 횟수를 지정합니다. 기본값은 100입니다. '50'으로 변경하면 Excel은 최종 결과를 제공하기 전에 계산을 50번 반복합니다. 반복 횟수가 많을수록 계산에 더 많은 리소스와 시간이 소요된다는 점을 기억하십시오.
- 최대 변화 – 계산 결과 간의 최대 변화량을 결정합니다. 이 값은 결과의 정확도를 결정합니다. 숫자가 작을수록 결과가 더 정확하고 워크시트를 계산하는 데 더 오래 걸립니다.
반복 계산 옵션이 활성화되어 있으면 워크시트에 순환 참조가 있을 때마다 경고가 표시되지 않습니다. 절대적으로 필요한 경우에만 대화식 계산을 활성화하십시오.
Excel에서 순환 참조 찾기
큰 데이터 세트가 있고 순환 참조 경고가 있다고 가정하면 오류를 수정하기 위해 오류가 발생한 위치(어느 셀에서)를 찾아야 합니다. Excel에서 순환 참조를 찾으려면 다음 단계를 따르세요.
오류 검사 도구 사용
먼저 순환 참조가 발생한 워크시트를 엽니다. '수식' 탭으로 이동하여 '오류 검사' 도구 옆에 있는 화살표를 클릭합니다. 그런 다음 '순환 참조' 옵션 위에 커서를 올리면 Excel에 아래와 같이 순환 참조와 관련된 모든 셀 목록이 표시됩니다.
목록에서 원하는 셀 주소를 클릭하면 해당 셀 주소로 이동하여 문제를 해결할 수 있습니다.
상태 표시줄 사용
상태 표시줄에서 순환 참조를 찾을 수도 있습니다. Excel의 상태 표시줄에는 '순환 참조: B6'과 같은 순환 참조가 있는 최신 셀 주소가 표시됩니다(아래 스크린샷 참조).
순환 참조를 처리할 때 알아야 할 몇 가지 사항이 있습니다.
- 반복 계산 옵션이 활성화되어 있으면 상태 표시줄에 순환 참조 셀 주소가 표시되지 않으므로 통합 문서에서 순환 참조 보기를 시작하기 전에 비활성화해야 합니다.
- 활성 시트에서 순환 참조를 찾을 수 없는 경우 상태 표시줄에는 셀 주소 없이 '순환 참조'만 표시됩니다.
- 순환 참조 프롬프트는 한 번만 표시되며 '확인'을 클릭하면 다음에 프롬프트가 다시 표시되지 않습니다.
- 통합 문서에 순환 참조가 있는 경우 순환 참조를 해결하거나 반복 계산을 켤 때까지 통합 문서를 열 때마다 프롬프트가 표시됩니다.
Excel에서 순환 참조 제거
순환 참조를 찾는 것은 쉽지만 수정하는 것은 그리 간단하지 않습니다. 불행히도 Excel에는 모든 순환 참조를 한 번에 제거할 수 있는 옵션이 없습니다.
순환 참조를 수정하려면 각 순환 참조를 개별적으로 찾아 수정하거나 순환 수식을 완전히 제거하거나 다른 순환 참조로 바꿔야 합니다.
때로는 간단한 수식에서 수식의 매개변수를 다시 조정하여 자신을 다시 참조하지 않도록 하기만 하면 됩니다. 예를 들어 B6의 수식을 =SUM(B1:B5)*A5로 변경합니다(B6을 B5로 변경).
계산 결과를 '756'으로 반환합니다.
Excel 순환 참조를 찾기 어려운 경우 선행 추적 및 종속 추적 기능을 사용하여 소스를 역추적하고 하나씩 해결할 수 있습니다. 화살표는 활성 셀의 영향을 받는 셀을 보여줍니다.
수식과 셀 간의 관계를 표시하여 순환 참조를 삭제하는 데 도움이 되는 두 가지 추적 방법이 있습니다.
추적 방법에 액세스하려면 '수식' 탭으로 이동한 다음 수식 감사 그룹에서 '전례 추적' 또는 '종속 추적'을 클릭합니다.
추적 선례
이 옵션을 선택하면 활성 셀의 값에 영향을 주는 셀을 추적합니다. 현재 셀에 영향을 주는 셀을 나타내는 파란색 선을 그립니다. 추적 선례를 사용하는 단축키는 Alt + T U T.
아래 예에서 파란색 화살표는 B6 값에 영향을 미치는 셀이 B1:B6 및 A5임을 나타냅니다. 아래에서 볼 수 있듯이 B6 셀도 수식의 일부이므로 순환 참조가 되고 수식이 결과로 '0'을 반환합니다.
이것은 SUM의 인수 =SUM(B1:B5)에서 B6을 B5로 바꾸면 쉽게 고칠 수 있습니다.
추적 종속
추적 종속 기능은 선택한 셀에 종속된 셀을 추적합니다. 이 기능은 선택한 셀의 영향을 받는 셀을 나타내는 파란색 선을 그립니다. 즉, 활성 셀을 참조하는 수식이 포함된 셀을 표시합니다. 종속 항목을 사용하는 바로 가기 키는 다음과 같습니다. Alt + T U D.
다음 예에서 셀 D3은 B4의 영향을 받습니다. 결과를 산출하는 가치는 B4에 의존합니다. 따라서 추적 종속은 B4에서 D3까지 파란색 선을 그려 D3이 B4에 종속됨을 나타냅니다.
Excel에서 의도적으로 순환 참조 사용
의도적으로 순환 참조를 사용하는 것은 권장되지 않지만 원하는 출력을 얻기 위해 순환 참조가 필요한 드문 경우가 있을 수 있습니다.
예를 들어 설명하겠습니다.
시작하려면 Excel 통합 문서에서 '반복 계산'을 활성화하십시오. 반복 계산을 활성화하면 순환 참조를 유리하게 사용할 수 있습니다.
집을 구입하고 집값의 2% 커미션을 에이전트에게 제공한다고 가정해 보겠습니다. 총 비용은 셀 B6에서 계산되고 수수료 비율(에이전트 수수료)은 B4에서 계산됩니다. 수수료는 총 비용에서 계산되며 총 비용에는 수수료가 포함됩니다. 셀 B4와 B6은 서로 종속되어 있으므로 순환 참조가 생성됩니다.
B6 셀에 총 비용을 계산하는 수식을 입력합니다.
=합(B1:B4)
총 비용에는 에이전트 수수료가 포함되어 있으므로 위 공식에 B4를 포함했습니다.
2%의 에이전트 수수료를 계산하려면 다음 공식을 B4에 삽입하십시오.
=B6*2%이제 셀 B4의 수식은 B6 값에 따라 총 수수료의 2%를 계산하고 B6의 수식은 B4에 따라 총 비용(에이전트 수수료 포함)을 계산하므로 순환 참조입니다.
반복 계산이 활성화된 경우 Excel은 결과에 경고나 0을 표시하지 않습니다. 대신 셀 B6과 B4의 결과는 위와 같이 계산됩니다.
반복 계산 옵션은 일반적으로 기본적으로 비활성화되어 있습니다. 켜지지 않고 B4에 수식을 입력하면 순환 참조가 생성됩니다. Excel에서 경고를 표시하고 '확인'을 클릭하면 추적 화살표가 표시됩니다.
그게 다야 이것이 Excel의 순환 참조에 대해 알아야 할 전부였습니다.