잘못된 데이터를 피하기 위해 Excel에서 순환 참조를 찾는 방법

Excel 시트에서 순환 참조 오류를 보고 워크시트가 손상되었는지 궁금하십니까? 날 믿어! 이 Excel 오류에 직면한 첫 번째 사람은 당신이 아닙니다. 수백, 수천 명의 Excel 사용자가 매일 순환 참조 오류를 경험합니다. 걱정 마세요! Excel에서 순환 참조를 찾고 해결하는 방법은 다음과 같습니다.

열렬한 Excel 사용자이기 때문에 많은 셀에 동일한 데이터를 거의 입력하지 않습니다. 대신 소스에서 데이터 세트를 가져오고 셀 참조를 사용하여 수식을 만듭니다. 따라서 동적 스프레드시트를 만듭니다.

누군가 셀 참조의 셀 값을 변경하면 워크시트가 자동으로 업데이트됩니다. 이를 데이터 분석 자동화라고 하며 데이터 분석가는 셀 참조를 가지고 노는 것을 좋아합니다.

그러나 의도적으로 또는 실수로 현재 계산을 수행 중인 셀 주소를 입력하면 문제가 발생합니다. 또는 워크시트 전체에서 많은 수식을 만들 때 수식이 포함된 셀이 다른 수식에 간접적으로 나타납니다.

이러한 상황 및 기타 유사한 상황에서 Excel에서 순환 참조 오류가 발생합니다. 순환 참조가 무엇인지, 그 이유, 피해야 하는 이유, 그리고 가장 중요한 것은 Excel에서 순환 참조를 찾아 이러한 이상 현상을 없애는 방법을 알아보십시오.

Excel에서 순환 참조란 무엇입니까?

Excel에서 워크시트의 하나 이상의 함수가 해당 함수가 생성된 셀 주소를 다시 참조하는 경우 순환 참조 오류가 발생합니다.

혼란스럽게 들릴 수 있으므로 Excel 앱에 얼마나 심각한 영향을 미칠 수 있는지 생각해 보십시오. 요컨대 셀 주소를 만들고 있는 수식에 셀 주소를 입력하고 싶지는 않습니다. 시각적 설명을 위해 다음 예를 살펴보십시오.

셀 B1 에서 10을 곱하고 그 결과를 셀 B1 자체에 넣으 려고 합니다 .

이제 위의 시나리오에서 확인을 누르기만 하면 Excel에서 오류를 닫고 계산된 결과로 0을 표시합니다. 이것은 간단한 예일 뿐입니다.

Excel의 순환 참조는 무엇입니까

이제 부기 워크시트, 재무 계획 워크시트, 직원 급여, 공급업체 지불 워크시트 등에 수백 개의 공식이 있다고 상상해 보십시오.

순환 참조 오류로 인해 Excel에서 계산된 잘못된 데이터를 신뢰할 수 있으며 이는 비즈니스 또는 전문적인 의사 결정에 영향을 미칩니다.

또한 순환 참조는 Excel 앱 내에서 계산 속도를 크게 저하시킬 수 있습니다. 심각한 경우 즉시 처리하지 않으면 바로 그 오류로 인해 PC 성능이 극도로 느려질 수 있습니다.

그러나 데이터 모델이 긴 함수를 계속 사용하기 위해 순환 참조의 중간 입력을 요구하는 고급 Excel 수식으로 작업하는 경우 이를 사용해야 할 수 있습니다. 실제로 Excel에서는 필요할 때 이 계산 방법을 사용할 수 있습니다.

예를 들어 풍차와 같은 에너지 생산 장치에서는 입력 에너지의 일부에서 재활용 출력 에너지를 계산해야 합니다. 또는 화학 플랜트에서 엔지니어는 출력 줄기를 동일하거나 다른 보일러 또는 공정 용기에 대한 입력으로 사용합니다. 이러한 높은 수준의 엔지니어링 계산에는 실제로 반복 계산을 위한 순환 참조가 필요합니다.

Excel에서 순환 참조 오류가 표시되는 경우는 언제입니까?

분명히 말하면 순환 참조 팝업은 오류 메시지가 아닙니다. 이는 Excel이 하나 이상의 수식에서 순환 참조를 발견했으며 계산 알고리즘과 성능에 영향을 미칠 수 있음을 알리는 방법입니다.

Excel 수식의 순환 참조로 인한 손상을 방지하려면 순환 참조가 나타나는 시기와 나타나지 않는 시기를 알아야 합니다. 인스턴스 아래에서 찾으십시오.

• 열려 있는 워크시트나 통합 문서에서 수식을 만들고 처음으로 순환 참조를 입력합니다.
• 귀하 또는 누군가가 Excel 파일에서 모든 순환 참조를 제거하고 저장했습니다. 이제 다시 귀하 또는 다른 사용자가 동일한 파일에 순환 참조를 입력합니다.
• 열려 있는 통합 문서를 모두 닫았습니다. 이제 다른 통합 문서를 연 다음 순환 참조가 포함된 수식을 입력합니다.

Excel에서 순환 참조 오류가 표시되는 경우

• 이전에 하나 이상의 순환 참조가 포함된 통합 문서를 방금 열었습니다. 경고가 표시됩니다.
• Excel에서 순환 참조 경고 메시지를 한 번 표시하고 확인을 클릭하면 더 많은 순환 참조 수식을 계속 만들어도 동일한 세션에서 더 이상 동일한 팝업이 표시되지 않습니다.

따라서 순환 참조 경고를 포착하고 반복 계산을 찾고 있지 않은 경우 이를 해결하려면 위의 인스턴스를 주의 깊게 찾아야 합니다.

Excel의 순환 참조 유형

Excel에서 순환 참조를 찾는 방법을 배우려면 해당 형식을 이해해야 합니다. Excel에서 순환 참조를 찾고 제거하는 것은 자동화된 작업이 아니기 때문입니다. 아래에서 이 오류의 일반적인 유형을 찾으십시오.

순환 참조 사용 예

• 의도된 순환 참조는 반복 계산 목적으로 의도적으로 사용할 때 발생합니다. 예를 들어 누군가가 IF, OR, SUMIF 등과 같은 휘발성 및 논리 함수를 사용하여 참조 셀에 값을 입력하면 다른 셀에 타임스탬프를 입력해야 합니다.

Excel의 의도하지 않은 순환 참조

• Excel 수식에서 더 빠르게 작업하려고 하고 수식이 포함된 셀을 실수로 참조하는 경우 의도하지 않은 순환 참조. 예를 들어 셀 A1 에서 A9 까지 SUM 함수를 적용하고 셀 A10 에서 결과를 가져와야 합니다 . 그러나 실수로 셀 A10에 다음과 같은 수식을 입력합니다.

=SUM(A2:A10)

• 숨겨진 순환 참조는 처음으로 수식을 만들거나 워크시트를 열 때 경고 메시지를 표시하지 않는 참조입니다. Excel 파일에 누군가 반복 계산을 허용했기 때문에 발생합니다.

간접 순환 참조의 예

• 간접 순환 참조도 널리 사용되며 예를 들어 셀 A1 과 같은 셀에서 새 수식을 만들 때 발생하며 이 새 수식은 이미 셀 A1 을 참조로 사용하고 있는 셀 B1 과 같은 다른 셀의 다른 함수를 사용합니다. .

이제 Excel에서 순환 참조의 기본 사항을 이해했으므로 아래 Excel에서 순환 참조를 찾는 방법을 알아보겠습니다.

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

반복 계산 기능이 비활성화된 경우 Excel 상태 표시줄에서 순환 참조에 대한 다음 알림을 볼 수 있습니다.

•  순환 참조: 통합 문서에 순환 수식이 포함된 워크시트에 있는 경우 C3 (또는 다른 모든 셀 참조).

Excel 상태 표시줄에 순환 참조 표시

• 아무 것도 포함하지 않지만 통합 문서에 이러한 순환 수식이 몇 개 있는 워크시트에 있는 경우에만 순환 참조가 표시됩니다 .

Excel에서 순환 참조를 찾는 방법: 오류 검사 사용

통합 문서 또는 워크시트에서 순환 참조를 확인하는 가장 좋은 방법입니다. 알아야 할 단계는 다음과 같습니다.

• 순환 수식이 포함된 것으로 의심되는 워크시트를 엽니다.
• Excel 리본에서 수식 메뉴를 클릭합니다 .

Excel에서 순환 참조를 찾는 방법에 대한 오류 검사 사용

• 수식 감사 명령 그룹 에서 오류 검사  드롭다운 목록을 클릭합니다 .
• 모든 워크시트에서 사용 가능한 모든 인스턴스를 보려면 순환 참조 옵션 위에 커서를 놓습니다 .
• 각 인스턴스를 선택하면 Excel에서 해당 셀을 자동으로 찾아냅니다.

Excel에서 순환 참조를 찾는 방법: 수식 추적에서

오류 검사 방법이 순환 참조가 있는 많은 수식을 감지할 만큼 효율적이지 않은 경우 다음 단계를 시도하여 수식 추적 방법을 사용할 수 있습니다.

• 참조된 셀 또는 수식이 포함된 셀을 선택합니다.
• Excel 리본 메뉴의 수식 탭 으로 이동한 다음 선례 추적 을 클릭합니다 .
• Excel은 현재 셀의 수식 결과에 영향을 미치는 셀을 화살표로 강조 표시합니다.

Excel에서 순환 참조를 찾는 방법에 대한 수식 추적 사용

• 종속 추적을 클릭하여 선택한 셀 참조 값의 영향을 받는 셀을 검색할 수도 있습니다.
• 위의 명령을 클릭한 후 Excel에 오류가 표시되지 않으면 워크시트에 순환 참조가 없는 것입니다.
• 전체 통합 문서를 확인하려면 모든 워크시트에서 단계를 반복합니다.

이 방법은 Excel 워크시트에서 반복 계산 기능이 활성화되어 있을 때 특히 중요합니다.

Excel에서 순환 참조를 찾는 방법: Excel 옵션 사용

• Excel 리본에서 파일 탭을 클릭 한 다음 표시되는 사이드바에서 Excel 옵션을 엽니다.
• Excel 옵션 대화 상자의 사이드바 에서 수식 메뉴를 선택한 다음 반복 계산 사용 확인란의 선택을 취소합니다 .

Excel에서 반복 계산을 비활성화하는 방법

• 확인을 클릭하여 변경 사항을 저장합니다.
• 이제 통합 문서 또는 워크시트에 순환 참조 경고가 자동으로 표시됩니다.
• 즉시 표시되지 않으면 통합 문서를 저장하고 닫았다가 다시 열어 경고를 확인하십시오.
• 경고가 표시되면 Excel 상태 표시줄을 확인하여 어떤 셀이 순환 참조에 있는지 확인하십시오.

Excel에서 순환 참조를 해결하는 방법

Excel에서 하나 이상의 순환 참조 오류를 수정하는 확실한 방법은 포함된 각 수식을 수동으로 편집하는 것입니다. 이러한 방식으로 Excel에서 우발적 및 간접 순환 참조를 모두 해결할 수 있습니다.

Excel에서 순환 참조를 해결하는 방법

또 다른 쉬운 방법은 현재 셀에서 수식을 제거하고 동일한 워크시트 내의 다른 위치에 배치하는 것입니다.

Google 시트에서 순환 참조를 찾는 방법(보너스 방법)

Excel과 마찬가지로 Google 스프레드시트는 반복 계산 기능을 활성화하지 않은 경우 하나 이상의 수식에서 순환 참조를 허용하지 않습니다. 따라서 Google 스프레드시트는 즉시 #REF! 순환 수식이 포함된 셀의 오류입니다.

Google Sheets 순환 참조를 찾는 방법

따라서 #REF!가 포함된 모든 셀을 간단히 선택할 수 있습니다. Google 스프레드시트에서 순환 참조를 찾아 해결하라는 경고입니다.

Excel 순환 참조: 최종 단어

이제 Excel에서 순환 참조를 찾는 방법에 대한 모든 비밀을 알게 되었습니다. Excel 보고서 또는 수학적 대시보드의 신뢰할 수 없는 데이터로 인해 혼동되지 않도록 원하는 방법을 사용하고 모든 순환 참조를 버리십시오.

다음은  연도별 최고의 Excel 달력 템플릿  과  최고의 Excel 예산 템플릿입니다 .