사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 순환참조를 이용한 누계 기록하기

 

 

 

 

순환 참조는 일반적인 방법으로 처리할 수 없는 다양한 수식을 작성할 수 있어 편리하게 사용할 수 있다. 예를 들어 제품을 관리할 때 입력한 수량이 특정 셀에 계속해서 누적되어야 한다면 순환 참조를 이용하는 수식을 작성해 누계를 구할 수 있다. 다만 순환 참조는 몇 가지 단점도 있으므로 필요에 따라서는 매크로를 사용하기도 한다.

 

누계기록.xlsx

0.01MB

 

 

첨부 파일을 불러오면 다음과 같은 표를 확인할 수 있다.

 

 

순환 참조를 사용하는 수식을 작성하기 위해서 옵션 변경하는 작업을 진행해야 한다. 메뉴표시줄에서 [파일] - [옵션]을 클릭한다. 엑셀 옵션 대화상자가 나타나면 [수식] 범주를 선택한다. [반복 계산 사용]에서 체크표시를 한 후 [최대 반복 횟수]를 '1'로 변경하고 [확인] 단추를 클릭한다.

 

 

입고누계가 기록될 [D3] 셀로 셀포인터를 옮긴후 다음과 같이 입력한다.

 

= D3+C3

 

 

[C3] 셀에 수량을 입력할 때마다 [D3]셀에 해당 값이 누적해서 대해진다. 입고수량인 [C3]셀에 각각 100, 200, 300을 입력해 보자.

 

 

여기서 만약 값을 잘못 입력하여 빠른 실행 도구 모음의 [실행 취소]를 누르면 입력하기 전의 값으로 돌아가는 것이 아니라 취소된 값이 계속 더해진다. 값을 잘못 입력했다면 입력한 값을 음수로 다시 입력해야 한다. 예를 들어 '300'을 잘못 입력했다면 '-300'을 입력하여 그만큼 빼주어야 누계가 정확하게 계산된다. 순환 참조를 이용한 계산 방법은 언뜻 좋아 보일 수도 있지만, 다른 셀에 값을 입력해도 [C3] 셀의 값이 입력된 상태라면 [C3]셀에서 계속 누적되는 문제가 있으므로 실무에서 사용하기는 부적합하다. 순환 참조는 간단한 누적 값을 확인할 때 유용하며, 실무에서 처리하려면 VBA를 이용해야 한다. 

 

Private Sub Worksheet_Change(ByVal Target As Range)

 

     IF Not Intersect(Target, Range("C3")) Is Nothing Then

         Range("D3").Value=Range("D3").Value + Range("C3").Value

     End If

 

End sub

 

시트 탭에서 마우스 오른쪽 버튼을 클릭한 다음 [코드보기]를 선택한다.

 

 

VB편집기 창이 열리면 상기 코드를 입력한다.