구글 스프레드시트 순환 종속성, 반복 계산 전에 자기 참조부터 찾으세요

‘순환 종속성이 감지되었습니다’가 뜨면 반복 계산부터 켜지 말고, 오류가 난 수식에서 출발해 결과 셀이 다시 자기 자신을 읽는 경로를 찾아 끊어야 한다. 오류 셀의 주소와 수식 안의 참조 범위를 적고, 참조한 셀의 수식이 다시 오류 셀이나 그 출력 범위를 읽는지 추적한다. 직접 참조인지, 복사 중 기준 참조가 밀렸는지, 배열 수식의 입력과 출력이 겹쳤는지를 이 순서로 가르면 된다.

오류 셀에서 경로를 끊는 순서

오류가 난 셀을 클릭해 셀 주소와 수식 안의 참조 범위를 적는다.
그 범위가 오류 셀 자신을 포함하는지 먼저 확인한다.
포함하지 않으면 참조한 셀의 수식으로 한 단계 이동한다. 그 수식이 오류 셀이나 오류 셀이 펼치는 출력 범위를 읽으면 순환 경로를 찾은 것이다.
경로가 닫히는 지점에서 입력 범위, 기준 참조, 결과 위치 가운데 하나를 바꿔 참조가 한 방향으로만 흐르게 한다.
경고가 사라진 뒤 입력값 하나를 바꿔 결과가 예상한 방향으로 변하는지 확인한다. 경고만 없어졌다고 의도까지 맞았다고 볼 수는 없다.

수식이 길면 원본을 보존한 사본 시트에서 참조를 작은 부분으로 나눠 본다. 어느 참조를 다시 넣을 때 경고가 돌아오는지 확인하면, 수식을 처음부터 다시 쓰지 않고 되돌아오는 경로를 좁힐 수 있다. Google은 반복되는 부분식을 별도 셀로 옮기고 배열 계산을 보조 열로 나눌 수 있다고 안내한다. Google의 Sheets 계산 방식 안내를 이 오류에 적용하면 중간 결과를 드러내는 일이 경로를 확인하는 방법이 된다.

오류 셀에서 참조를 따라가 다시 같은 셀이나 출력 범위로 돌아오면 순환 경로다.

경로 모양별 수정

되돌아오는 지점	최소 형태	바꿀 곳
결과 셀 자신	A2에 `=A2+2`	입력값과 결과 셀을 분리한다.
복사 뒤 이동한 기준 참조	여러 행이 같은 기준값 대신 앞선 결과를 거쳐 읽음	기준 셀을 직접 가리키고, 복사해도 움직이면 안 되는 행·열은 `$`로 고정한다.
배열 수식의 출력 범위	A열에 결과를 펼치며 `A:A`를 읽음	입력 열과 출력 열을 분리한다.

결과 셀이 자기 자신을 읽는 경우

A2의 =A2+2는 A2의 새 결과를 만들기 위해 A2를 다시 읽는다. ‘기존 값에 2를 더한다’는 의도라면 한 셀에 이전 값과 결과를 함께 둘 수 없다. 원래 값이 B2에 있다면 결과 셀을 A2로 두고 =B2+2처럼 입력과 결과를 나눈다. 누적값은 첫 행에 시작값을 두고 다음 행부터 바로 앞 행만 읽게 하면 참조가 아래 방향으로만 흐른다.

복사 뒤 생긴 오류는 기준 셀을 비교한다

수식을 채운 뒤 오류가 생겼다면 처음 수식과 오류 수식을 나란히 보며 계속 같아야 할 기준 셀이 이동했는지 확인한다. 모든 행이 B1을 기준으로 계산해야 한다면 B1이 아니라 $B$1을 쓴다. Google은 여러 행이 공통 기준값을 쓸 때 절대참조로 기준 셀을 직접 가리켜 긴 참조 사슬을 피하라고 설명한다. Google의 참조 최적화 안내처럼 이전 결과를 거치지 않는 직접 참조는 오류 경로도 더 쉽게 드러낸다.

배열 수식은 읽는 열과 쓰는 열을 나눈다

배열 수식은 한 수식이 여러 셀에 결과를 펼친다. 따라서 A열에 결과를 쓰면서 A:A를 입력으로 읽으면 출력 범위가 다시 입력이 될 수 있다. 원본이 B열이면 A열의 수식은 B열만 읽게 하고, 원본과 결과를 같은 열에 둘 필요가 있으면 보조 열을 만든다. 전체 열 참조가 꼭 필요하지 않을 때 실제 데이터 끝 행까지 범위를 닫으면 입력과 결과의 경계도 분명해진다. 다만 범위를 닫는 것만으로 이미 닫힌 순환 경로가 풀리지는 않는다.

반복 계산은 예외로만 남긴다

수식이 의도적으로 순환 참조를 허용해야 하는 모델이라면 반복 계산을 검토할 수 있다. Google의 기능 설명에 따르면 이 설정은 최대 반복 횟수와 연속된 계산값 차이가 충분히 작아졌을 때 멈추는 수렴 기준을 함께 둔다. 반복 계산 설정의 두 종료 조건이 필요한 이유다.

켜기 전에는 어느 값에 가까워져야 하는지와 수렴하지 않으면 무엇을 점검할지를 적어 둔다. 이 답이 없다면 직접 자기 참조, 이동한 상대참조, 배열의 입력·출력 겹침부터 고친다. 의도한 반복 모델이라면 종료 조건을 정한 뒤 시작값을 바꿔도 결과가 허용한 범위에 머무는지 따로 확인한다.

오류를 고친 뒤 계산이 느리다면 열 전체 참조와 긴 참조 사슬을 줄이는 방법을 점검할 수 있다.