엑셀 순환참조 오류 없애기 – 순환 참조 찾기 및 제거
엑셀을 사용하다 보면
예상치 못한 오류가 발생할 때가 있습니다.
그중 하나가 바로 “순환참조 오류”인데요.
처음 이 오류를 접하면 어떤 부분이 잘못되었는지
확인하기 어려울 때가 있습니다.
특히, 복잡하게 수식을 쓴 셀을 많이 사용하고 참조하면
결국에는 꼬이는 상황이 만들어지는데요.
이번 글에서는 제가 순환참조 오류를 해결해봤던 경험으로
순환참조 오류를 찾고 제거해 보겠습니다.
엑셀 순환참조 오류 없애기
순환참조란?
순환참조 오류는 엑셀에서 한 셀이 자기 자신을 직접적이거나,
간접적으로 참조할 때 발생합니다.
예를 들어 A1 셀의 값이 B1을 참조하고,
B1 셀에서 다시 A1셀을 참조하면 무한하게 참조가 되면서
엑셀에서 오류가 발생하게 되는 겁니다.
엑셀 순환 참조 찾기 및 제거
엑셀에서 순환참조 오류를 찾는 방법은
순환 참조된 셀의 수가 얼마 없으면 시각으로도 확인할 수 있지만
만약 복잡하게 많이 사용했을 때는 찾기 어려워져서
엑셀 기능을 이용해서 찾는 것이 좋습니다.
수식 → 오류 검사 → 순환 참조 → 셀 찾기 및 제거, 수정
엑셀 “수식” 탭을 클릭합니다.
“오류 검사”에서 “순환 참조”를 눌러주면
해당 오류가 있는 셀의 위치를 확인할 수 있습니다.
표시된 셀을 클릭하면 자동으로
해당 위치로 포인트가 변경됩니다.
이 방법이 순환참조를 찾을 때 가장 빠른 방법인 것 같습니다.
엑셀 순환 참조 예시 (상품 개발 비용 계산 오류)
실제 업무에서 발생할 수 있는 순환참조 오류 예시를 만들었습니다.
순환 참조 오류 원인
- 상품 A의 추가 개발비(C2)는 팀 C의 매출(D4)의 10%로 계산됩니다.
- 상품 B의 추가 개발비(C3)는 팀 A의 매출(D2)의 15%로 계산됩니다.
- 상품 C의 추가 개발비(C4)는 팀 B의 매출(D3)의 20%로 계산됩니다.
- 결과적으로 C2 → D4 → C4 → D3 → C3 → D2 → C2로
순환 참조 오류가 발생해서 수식이 꼬이게 됩니다.
이런 식으로 개발비가 서로 참조되면서 연쇄적으로
계산되면 엑셀에서 무한 루프에 빠지게 됩니다.
순환 참조 해결 방법
이 예시에서 순환 참조를 해결하려면
개발비 배분 방식을 조정해야 합니다.
- 개발비 할당 기준을 상품마다 각각 다른 방식으로 변경하는 방법이 있습니다.
서로 순환되지 않도록 설정해줍니다. - 다음으로는, 순환 참조가 나타나는 셀에 직접 값을 입력하여,
수동으로 순환 참조를 제거하는 방법입니다.
이렇게 하면 순환 참조 문제를 해결할 수 있습니다.
되도록 셀 간의 참조 관계를 이해하면서
불필요한 상호 참조를 피하는 것이 좋습니다.