Excel에서 순환 참조(Circular Reference)를 허용하면 셀이 자기 자신을 참조하는 수식을 사용할 수 있습니다. 마이크로소프트의 정의에 따르면, 순환 참조란 Excel 수식이 직접 또는 간접적으로 자신이 입력된 셀을 다시 참조하는 상황을 말합니다. 예를 들어 두 개의 값(B2와 C2)을 더한 결과를 그 값 중 하나인 B2 셀에 표시하고 싶다면 순환 참조가 발생합니다. 처음 ENTER 키를 누르면 Excel은 수식에 하나 이상의 순환 참조가 있다는 경고 메시지를 표시합니다.
순환 참조는 반복 계산(iteration)을 통해 수식 결과를 도출합니다. 따라서 최대 반복 횟수가 너무 크게 설정되면 무한 루프가 발생할 수 있습니다.
이 글에서는 순환 참조의 개념, 발생할 수 있는 문제점, 그리고 실제 활용 방법까지 자세히 살펴보겠습니다.
순환 참조 허용 여부 확인 방법
순환 참조가 무엇인지 알았다면, 이제 가장 궁금한 점은 현재 워크시트에서 순환 참조가 허용되어 있는지 아닌지를 어떻게 확인하느냐일 것입니다.
가장 자연스러운 방법은 직접 참조를 입력해 보는 것입니다. 순환 참조가 비활성화된 상태라면 Excel이 아래 이미지처럼 경고 메시지를 처음 한 번 표시합니다. 반대로 경고 없이 수식이 최대 반복 횟수 1을 기준으로 결과를 반환한다면 순환 참조가 이미 활성화되어 있는 것입니다.
또 다른 방법도 있습니다. 수식에 셀 참조를 입력했는데 별다른 이유 없이 결과가 0(0)으로 표시된다면, 워크시트 하단의 준비(Ready) 상태 표시줄 오른쪽을 확인해 보세요. '순환 참조: 셀 주소'라는 문구가 보인다면 해당 워크시트에서 순환 참조 옵션이 비활성화된 상태입니다.
순환 참조 오류의 위치를 찾으려면 수식 탭 > 수식 검사 섹션의 오류 검사 선택 > 순환 참조 항목을 클릭하면 됩니다. 그러면 순환 참조 오류가 발생한 셀을 바로 확인할 수 있습니다.
파일 리본 메뉴로 순환 참조 허용하기
순환 참조를 허용한다는 것은 일종의 참조 오류를 무시하겠다는 의미입니다. 하지만 이 기능을 활성화하면 수식이 참조 오류를 극복하고 올바른 결과값을 반환할 수 있게 됩니다.
순환 참조를 허용하려면 아래 순서대로 진행하세요.
➤ 워크시트에서 파일(File) 리본 메뉴로 이동합니다.
➤ 파일 메뉴에서 옵션(Options)을 선택합니다(Excel 옵션 창이 열림) > 창 왼쪽 목록에서 수식(Formulas)을 선택 > 반복 계산 사용(Enable iterative calculation) 옵션에 체크합니다. 필요에 따라 최대 반복 횟수(Maximum iterations)와 최대 변경 값(Maximum change)도 입력하세요.
마지막으로 확인(OK)을 클릭합니다.
위 과정을 완료하면 기존의 순환 참조가 포함된 수식도 정상적으로 결과값을 반환하게 됩니다.
최대 반복 횟수 관련 문제점
Excel에서 순환 참조가 처음 발생하면 아래 스크린샷과 같이 경고 메시지가 한 번 표시됩니다.
그런데 이후에는 경고가 다시 나타나지 않아, 수식 종류와 상관없이 결과값이 0(0)으로만 반환되면서 사용자는 원인을 알 수 없어 계산에 어려움을 겪게 됩니다.
이 문제를 해결하려면 반복 계산 사용 옵션을 확인해야 합니다. 아래 이미지의 안내대로 파일 탭으로 이동하세요.
파일 리본을 클릭한 후 옵션을 선택하고(Excel 옵션 창 열림) > 왼쪽 목록에서 수식을 선택 > 반복 계산 사용에 체크한 뒤 확인을 클릭합니다.
이제 수식에서 순환 참조를 사용할 수 있습니다. 다만 최대 반복 횟수에 1을 입력한 경우(예시와 동일), 수식 =B2+C2는 20을 반환합니다(B2가 비어 있고 C2의 값이 20이므로 올바른 결과입니다).
하지만 아래 스크린샷처럼 최대 반복 횟수에 5를 입력하면 어떻게 될까요?
동일한 계산이 5번 반복 실행되어 수식은 100을 반환합니다. 이것이 Excel에서 가장 혼란스러운 오계산 사례 중 하나입니다.
Excel에서 순환 참조의 활용 사례
순환 참조는 특정 상황에서 매우 유용하게 사용됩니다. 값을 더 효과적으로 표현하기 위해 순환 참조가 필요한 경우가 있기 때문입니다. 여기서는 가장 많이 쓰이는 두 가지 활용 예시를 소개합니다.
예시 1: 순환 참조를 활용한 고정 타임스탬프 삽입
예를 들어 제품, 주문 날짜, 배송 상태 데이터가 있다고 가정해 보겠습니다. 제품이 배송 완료된 시점의 타임스탬프를 셀에 자동으로 기록하고 싶습니다. 배송 상태는 Yes 또는 No로 표시됩니다.
즉, 배송 상태가 Yes이면 해당 시점의 날짜와 시간이 타임스탬프로 표시되어야 합니다.
➤ 타임스탬프를 표시할 빈 셀(예: E5)에 아래 수식을 입력합니다.
=IF(D5="Yes",IF(E5="",NOW(),E5),"")이 수식은 논리 검사(logical_test)를 통해 D열의 값이 'Yes'와 일치하는지 확인합니다. 조건이 TRUE인 셀에는 현재 시간을 표시하고, 그렇지 않으면 셀을 비워 둡니다.
➤ ENTER 키를 누른 후 채우기 핸들(Fill Handle)을 드래그하여 배송 완료된 모든 제품에 타임스탬프를 적용합니다.
순환 참조는 자기 자신의 셀을 참조하는 특성 때문에 수식이 제대로 작동하지 않는 것처럼 보일 때가 있습니다. 이런 경우 배송 상태나 조건을 다시 입력한 후 수식을 적용하면 됩니다. 또한 날짜-시간 형식으로 표시하려면 셀 서식을 변경해야 할 수도 있습니다.
예시 2: 순환 참조를 활용한 반복 계산
때로는 반복 계산을 수행해야 하는 경우가 있습니다. 반복 계산을 활용하면 전체 과정을 거치지 않고도 n번째 반복 결과값을 바로 구할 수 있습니다.
예를 들어 초기 투자금($1,500)을 넣고 자산에서 월 단위 분할 상환금을 받고 싶다고 가정해 보겠습니다. 첫 달의 상환금은 아래 수식으로 계산할 수 있습니다.
=$C$4*(1+$C$5)그다음 달부터는 이전 달의 상환금을 참조하여 다음 상환금을 계산합니다. 아래 그림과 같습니다.
➤ 하지만 순환 참조를 활용하면 상환 회차 번호를 최대 반복 횟수로 입력하기만 하면 n번째 상환금을 즉시 구할 수 있습니다. 올바른 결과를 얻으려면 최대 반복 횟수를 5로, 최대 변경 값을 0.001(1000분의 1)로 설정합니다.
6번째 상환금을 계산해야 하므로 최대 반복 횟수를 5로 입력한 것이며, 변경 허용 범위는 1000분의 1로 설정했습니다.
➤ 순환 참조를 허용한 후, 셀 F4에 =F6을 입력하여 셀 F6의 =F4*(1+F5) 수식이 5번 반복 계산된 결과값을 가져옵니다(이때 순환 참조가 생성됨).
F6 셀의 수식은 6번째 상환금을 계산하며, 복잡한 과정 없이 간편하게 결과를 얻을 수 있습니다.
마무리
이 글에서는 Excel 사용자를 가장 당혹스럽게 만드는 현상 중 하나인 순환 참조에 대해 알아보았습니다. 최대 반복 횟수 옵션으로 인해 발생할 수 있는 문제점도 함께 살펴보았고, 순환 참조의 대표적인 활용 사례와 적절한 계산에 적용하는 요령도 소개했습니다. 이 글이 순환 참조에 대한 개념을 명확히 하고 더 효과적으로 다루는 데 도움이 되기를 바랍니다. 추가 질문이나 의견이 있다면 댓글로 남겨주세요. 다음 글에서 다시 만나겠습니다.
함께 읽으면 좋은 글
- Excel에서 순환 참조 제거하는 방법 (2가지)
- 목록에 표시되지 않는 순환 참조 해결 방법 (4가지)