엑셀 순환참조 제거, 찾기 | 순환참조 오류 찾아서 없애기

엑셀에서 일하다 보면 가끔 계산이 멈춘다거나 "수식이 해당 자체 셀을 직접 또는 간접적으로 순환 참조가 하나 이상 있습니다. 이로 인해 순환 참조를 올바르게 계산하지 못할 수 있습니다. 해당 참조를 제거 또는 변경하거나 수식을 다른 셀로 이동하세요."라는 오류 창이 나타날 때가 있습니다.

 

 

순환참조는 수식이 자기 자신을 참조하는 형태로, 엑셀에서 무한 계산이 발생하여 수식이 기능을 못하는 경우인데요.

오늘은 순환참조 오류를 찾아서 제거하는 방법에 대해서 알아보겠습니다.

 

엑셀 순환참조 오류 발생 원리

순환참조는 말 그대로 셀 하나가 다른 셀을 참조하고, 그 셀이 다시 원래 셀을 참조하는 형태입니다. 

 

예를 들어보면, 각각 수식이 아래와 같이 입력되어 있다고 합시다.

• A1 셀 = B1 + 10
• B1 셀 = A1 - 5

그럼 A1과 B1이 서로 참조하게 되기 때문에 엑셀이 계산을 무한으로 실행하게 되어 오류가 생기게 되는 거죠.

 

엑셀 순환참조 찾기 & 오류 없애기

예시를 하나 들어서, 순환참조를 찾아서 제거해 보겠습니다.

 

직원들의 보너스 성과급을 계산할 때 엑셀을 사용한다고 예시를 들겠습니다.

 

 

직원들의 성과급은 "근로 수익의 5%"로 계산하고, 총 급여는 "기본급 + 성과급"으로 계산합니다. 마지막으로 근로 수익은 10만 원에서 총 급여를 더하고 성과급을 뺀 값이라고 할 때 성과급은 근로 수익을 참조하고, 근로 수익은 성과급을 참조하여 순환 참조가 발생하게 됩니다.

 

 

• 성과급 = 근로 수익 x 0.05
• 총 급여 = 기본급 + 성과급
• 근로 수익 = 100000 + 총 급여 - 성과급

이렇게 서로 참조하여서 수식이 꼬이는 상태가 됩니다. 

 

엑셀 순환 참조 찾기

 

순환 참조는 엑셀에 있는 기본 기능으로 쉽게 찾을 수 있습니다.

 

수식 > 수식 분석 > 오류 검사 > 순환 참조

 

 

위 방법으로 순환 참조가 되는 부분의 셀을 찾아낼 수 있습니다.

 

엑셀 순환 참조 오류 없애기

 

순환 참조를 찾았으면 이제 오류를 없애야 하는데요. 

 

크게 오류는 2가지 방법으로 수정할 수 있습니다. 

 

1. 직접 셀 수정하기

 

첫 번째로 수식을 직접 계산해서 순환 참조가 일어나는 셀 하나에 입력하는 방법입니다.

 

• 근로 수익 = 100000 + 총 급여 - 성과급
• 총 급여 = 기본급 + 성과급

 

엑셀 사진에서 근로 수익은 위와 같은 수식인데 총 급여에 대입해 보면

 

• 근로 수익 = 100000 + (기본급 + 성과급) - 성과급 = 100000 + 기본급

결국에는 근로 수익은 100000 + 기본급이 됩니다. 이렇게 각 직원의 기본급을 직접 계산해서 넣어주는 방식도 가능합니다. 

 

2. 다른 참조 이용하기

 

 

만약 위 같은 상황에서 다른 참조를 이용할 수 있다면 근로 수익 = 100000 + 기본급에서 총 급여 - 성과급이 아닌 기본급으로 참조해 주면 됩니다.

 

이런 방식을 통해서 순환 참조를 찾고 오류를 고칠 수 있습니다.