Excel에서 순환 참조를 찾는 방법

Microsoft Excel 로 작업할 때 때때로 순환 참조라고 알려진 것을 접할 수 있습니다. 셀의 수식이 자신을 다시 참조하여 계산 과정에서 무한 루프가 발생하는 경우입니다.

Excel에서 순환 참조를 찾으려면 "수식" 탭 아래의 "오류 검사" 도구를 사용하여 순환 종속성이 있는 셀을 식별합니다.

이 문서에서는 Excel에서 순환 참조를 찾아 수정하는 방법을 보여줍니다. 크거나 복잡한 워크시트로 작업할 때 이를 방지하는 방법도 배웁니다.

목차

• 순환 참조란?
• 순환 참조 오류 메시지 창
• Excel에서 순환 참조를 찾는 방법
• Excel 상태 표시줄에서 순환 참조를 찾는 방법
• Excel에서 숨겨진 순환 참조를 찾는 방법
  • 추적 종속 도구를 사용하는 방법
  • 선례 추적 도구
• Excel에서 순환 참조를 제거하는 2가지 방법
  • 2. 대체 수식 사용
• 순환 참조를 방지하기 위한 5가지 팁
  • 1. 범위 선택 시 주의
  • 2. 공식을 신중하게 계획하십시오
  • 3. 대체 수식 사용
  • 4. 복잡한 수식을 더 작은 부분으로 나누기
  • 5. 진행하면서 문제 해결
• 반복 계산을 활성화하는 방법과 귀찮게 하는 이유는 무엇입니까?

순환 참조란?

셀의 수식 계산이 셀 자체 값에 따라 달라지는 경우 Excel에서 순환 참조가 발생합니다. 이로 인해 루프가 생성되고 부정확하거나 예측할 수 없는 결과가 발생합니다.

이것은 간단한 예를 들어 이해하기 쉽습니다.

셀 A1과 B1에 두 개의 숫자가 있다고 가정합니다. 두 숫자를 더하는 셀 A3의 수식을 사용하려고 합니다. 셀 A3의 수식은 다음과 같아야 합니다.

=A1 + B1

그러나 실수로 계산에 C1을 포함하면 이제 순환 참조가 생깁니다.

=A1 + B1 + C1

이것은 두 개의 셀을 다룰 때 피해야 할 명백한 문제일 수 있습니다. 그러나 넓은 범위의 셀로 작업할 때는 실수할 가능성이 더 커집니다.

고맙게도 Excel은 순환 참조가 감지되면 경고 메시지를 표시하여 이러한 상황을 처리하도록 설계되었습니다.

그러나 경우에 따라 경고를 받지 않고 순환 참조가 발생할 수 있습니다. 따라서 이러한 문제를 수동으로 찾아 해결하는 방법을 아는 것이 중요합니다.

순환 참조 오류 메시지 창

기본적으로 Excel은 셀에 수식을 입력할 때 순환 참조를 모니터링합니다. 문제가 있는 셀에서 Enter 키를 누르면 순환 참조 오류 메시지가 팝업 창에 표시됩니다.

다음은 설명 텍스트입니다.

수식이 자체 셀을 직접 또는 간접적으로 참조하는 하나 이상의 순환 참조가 있습니다. 이로 인해 잘못 계산할 수 있습니다. 이러한 참조를 제거 또는 변경하거나 수식을 다른 셀로 이동해 보십시오.

이 대화 상자가 표시되면 "확인"을 클릭하고 이 문서의 방법을 사용하여 문제를 해결해야 합니다.

그러나 오류를 즉시 수정할 필요는 없습니다. 나중에 문제를 해결할 목적으로 스프레드시트 작업을 계속할 수 있습니다.

이 경우 경고 상자를 해제한 후 Excel에서 순환 참조를 찾는 방법을 알아야 합니다.

Excel에서 순환 참조를 찾는 방법

Excel에는 순환 참조를 포함하여 여러 종류의 오류를 찾는 기본 제공 오류 검사 도구가 있습니다. 수식 탭에서 도구에 액세스합니다.

이 그림은 Formula Auditing 그룹 내의 위치를 ​​보여줍니다.

이제 도구를 찾는 방법을 알았습니다. 다음은 도구를 사용하는 자세한 단계입니다.

1. 수식 탭으로 이동합니다.

1. 수식 감사 그룹에서 오류 검사 드롭다운을 클릭합니다.

2. 드롭다운 목록에서 "순환 참조"를 선택합니다.

3. 문제가 있는 첫 번째 셀로 커서를 이동하려면 링크를 클릭하십시오.

아래 그림은 도구가 셀 G1에서 순환 오류를 발견했음을 보여줍니다. 표시된 셀은 하이퍼링크입니다.

링크를 클릭하면 커서가 셀로 이동합니다. 이것은 많은 양의 데이터에 매우 유용합니다.

첫 번째 순환 참조를 해결했으면 오류 검사 도구로 돌아가서 시트에서 다음 참조를 찾을 수 있습니다.

이 문서의 뒷부분에 있는 단계를 사용하여 문제를 해결하고 도구를 사용하여 다음 문제로 이동합니다.

더 이상 순환 문제가 없으면 드롭다운 메뉴의 "순환 참조" 메뉴 항목이 회색으로 표시됩니다. 더 이상 할 일이 없습니다.

Excel 상태 표시줄에서 순환 참조를 찾는 방법

순환 참조를 찾는 또 다른 방법은 Excel 워크시트 아래쪽에 있는 상태 표시줄을 보는 것입니다.

워크시트에 하나 이상의 문제가 있는 경우 상태 표시줄에 하나의 문제 셀 참조 위치가 표시됩니다.

예를 들어 C1 셀에 순환 참조가 있고 스프레드시트에 다른 문제가 없는 경우 상태 표시줄에 "순환 참조: C1"이 표시됩니다.

이 그림은 디스플레이를 볼 수 있는 위치를 보여줍니다.

Excel에서 숨겨진 순환 참조를 찾는 방법

Excel 의 숨겨진 순환 참조는 내장된 오류 기능으로 즉시 감지되지 않는 순환 참조입니다. 즉, Excel은 표준 경고를 표시하지 않습니다.

셀이 일련의 중간 셀을 통해 간접적으로 다른 셀을 참조하고 결국 원래 셀을 다시 참조할 때 발생합니다.

Excel에는 관계를 추적하고 이러한 문제를 조사하는 데 도움이 되는 두 가지 도구가 있습니다.

1. 종속 추적 도구

2. 선례 추적 도구

추적 종속 도구를 사용하는 방법

숙련된 Excel 사용자는 Trace Dependents 도구를 활용하여 이러한 문제를 조사하고 순환 참조를 수정합니다. 하나 이상의 셀을 선택하고 이 도구를 사용하면 Excel에서 종속 셀을 표시합니다.

이 도구는 수식 탭의 수식 감사 섹션에서 사용할 수 있습니다.

도구를 사용하려면 다음 단계를 따르십시오.

1. 종속 항목을 추적하려는 셀을 클릭합니다.

2. Excel 리본의 '수식' 탭으로 이동합니다.

3. 'Formula Auditing' 그룹에서 'Trace Dependents' 버튼을 클릭합니다.

4. 종속성을 표시하는 추적 화살표 또는 화살표를 검토합니다.

Excel은 활성 셀 값에서 종속된 모든 셀로 화살표를 표시합니다. 아래 그림의 파란색 화살표는 D6 셀이 B2 셀에 종속되어 있음을 나타냅니다.

선례 추적 도구

선례는 부양 가족의 반대입니다. 선례 추적 도구를 사용하면 수식이 있는 셀을 선택하고 해당 셀이 의존하는 모든 셀, 즉 선례를 볼 수 있습니다.

이 도구는 수식 탭의 수식 감사 섹션에서 사용할 수 있습니다.

도구를 사용하려면 다음 단계를 따르십시오.

1. 조사하려는 수식이 포함된 셀을 클릭합니다.

2. Excel 리본의 수식 탭으로 이동합니다.

3. 'Formula Auditing' 그룹에서 'Trace Precedents' 버튼을 클릭합니다.

4. Excel이 표시하는 추적 화살표를 검토합니다.

아래 그림의 파란색 화살표는 D6 셀이 A2:B5 셀 범위에 종속되어 있음을 보여줍니다.

두 도구를 모두 사용하면 즉시 명확하지 않은 Excel 순환 참조를 찾는 데 도움이 됩니다. 다음 단계는 선택한 셀의 문제를 수정하는 것입니다. 그런 다음 다른 셀에서 동일한 문제를 계속 검색할 수 있습니다.

Excel에서 순환 참조를 제거하는 2가지 방법

순환 참조를 해결하는 두 가지 주요 방법이 있습니다.

• 순환 참조 끊기

• 대체 공식 사용

두 가지 방법을 모두 설명하겠습니다.

1. 순환 참조 끊기

Excel에서 순환 참조를 끊는 것은 문제를 해결하는 효율적인 방법입니다. 이렇게 하려면 순환 참조와 관련된 셀을 식별하고 그에 따라 수식을 수정해야 합니다.

이렇게 하려면 앞에서 설명한 단계에 따라 셀 주소를 찾으십시오.

그런 다음 수식 입력줄에서 수식을 검사해야 합니다. 셀 자체에 대한 참조를 찾으십시오.

순환 참조의 첫 번째 예에서 셀 C1의 더하기 수식에는 셀 자체에 대한 참조가 포함되어 있습니다.

=A1 + B1 + C1

최종 추가를 제거하여 해결할 수 있습니다. 이 문제가 해결되면 계속해서 다른 순환 참조를 찾아 해결합니다.

2. 대체 수식 사용

순환 참조의 가장 일반적인 원인 중 일부는 셀을 합산하거나 평균화할 때입니다. 실수는 수식에 결과 셀을 포함하는 것입니다.

이전 예에서 수식은 더하기 연산자를 사용하여 셀 범위의 합계를 계산했습니다. 다른 방법은 SUM Excel 기능을 사용하는 것입니다.

예를 수정하려면 수식을 다음으로 바꿉니다.

=합계(A1:B1)

마찬가지로 =(A1+B1)/2와 같은 공식을 사용하여 범위를 평균화할 수 있습니다. 그러나 AVERAGE 함수는 실수로 이어질 가능성이 적습니다.

=평균(A1:B1)

순환 참조를 방지하기 위한 5가지 팁

Microsoft Excel에서 순환 참조를 방지하려면 Excel 시트에서 수식이 서로 상호 작용하는 방식을 명확하게 이해하는 것이 중요합니다.

셀 간의 관계를 식별하고 이러한 연결이 함께 작동하는 방식을 인식하면 순환 수식이 생성되는 것을 방지할 수 있습니다.

다음은 순환 참조를 방지하는 데 도움이 되는 5가지 최고의 팁입니다.

1. 범위 선택 시 주의

내 경험상 순환 참조의 가장 일반적인 원인 중 하나는 사용자가 수식에 포함할 범위를 빠르게 선택하는 경우입니다.

셀을 그룹화하기 위해 수직 및/또는 수평으로 스와이프할 때 올바른 범위에 인접한 하나 이상의 셀을 쉽게 선택할 수 있습니다. 해당 셀이 결과 셀이기도 하면 실수로 순환 참조를 만든 것입니다.

이 실수는 일반적으로 카이제곱 테스트 실행과 같이 대규모 데이터 세트로 작업할 때 발생합니다. 해결책은 범위 선택에 주의하는 것입니다.

2. 공식을 신중하게 계획하십시오

수식을 입력하기 전에 계산 이면의 논리와 계산이 셀에서 어떻게 흘러야 하는지 생각해 보십시오.

다이어그램 작성 도구나 화이트보드를 사용하여 계산을 미리 계획할 수 있습니다.

이렇게 하면 순환 참조로 이어질 수 있는 가능한 루프를 식별하는 데 도움이 될 수 있습니다.

3. 대체 수식 사용

INDIRECT 또는 OFFSET 과 같은 특정 함수는 부적절하게 사용될 때 실수로 순환 참조를 생성할 수 있습니다.

순환 참조의 위험 없이 유사한 기능을 제공하는 대체 함수를 사용하는 것이 좋습니다.

4. 복잡한 수식을 더 작은 부분으로 나누기

중첩된 함수가 여러 개인 복잡한 단일 수식을 만드는 대신 계산을 더 작고 관리하기 쉬운 부분으로 분할하는 것이 좋습니다.

이렇게 하면 통합 문서를 더 쉽게 이해하고 유지 관리할 수 있을 뿐만 아니라 순환 참조를 도입할 가능성도 줄어듭니다.

5. 진행하면서 문제 해결

크거나 복잡한 통합 문서로 작업할 때 순환 참조를 모니터링하는 것도 필수적입니다. 많은 양의 데이터와 공식이 모일 때까지 문제를 무시하는 대신 오류가 발생할 때마다 수정하는 것이 좋습니다.

잠시 동안 순환 참조 경고 메시지를 무시하도록 선택할 수 있습니다. 그러나 머지않아 오류 검사 기능을 사용하여 기존 순환 참조를 따라잡아야 합니다.

이렇게 하면 통합 문서에 미치는 영향을 쉽게 수정하고 방지할 수 있습니다.

반복 계산을 활성화하는 방법과 귀찮게 하는 이유는 무엇입니까?

일반적으로 워크시트에서 순환 참조를 피하려고 합니다. 그러나 필요한 몇 가지 시나리오가 있습니다.

예를 들어 일부 공학 또는 과학 계산에서는 이전 계산 결과를 사용해야 합니다. 이를 반복 솔루션이라고 합니다.

각 루프는 특정 임계값이나 목표에 도달할 때까지 계산 결과를 구체화합니다.

이러한 경우 Excel 설정에서 반복 계산을 활성화하여 의도한 순환 참조를 허용하는 것이 중요합니다. 이를 통해 소프트웨어는 지정된 횟수만큼 계산을 반복하여 솔루션으로 수렴할 수 있습니다.

Excel 파일에서 순환 참조가 작동하도록 하려면 다음 단계를 따르십시오.

1. 파일 탭을 클릭합니다.

2. 옵션을 선택합니다.

3. 수식 범주를 선택합니다.

4. '계산 옵션' 섹션에서 '반복 계산 사용' 확인란을 선택합니다.

5. 최대 반복 상자에서 숫자를 변경하거나 기본값인 100을 그대로 둡니다.

6. 최대 변경 상자를 사용하여 요구 사항에 따라 반복을 줄입니다.