강좌 > 엑셀중급강좌 > 엑셀 수식 분석, 오류 해결 > 엑셀에서 참조 셀 추적하기

엑셀에서 참조 셀 추적하기

다음 그림과 같이 엑셀의 '수식' 탭에는 '수식 분석'이라는 기능이 있습니다.
한 번쯤은 눌러보고 왠지 어려울 것 같고 딱히 필요하지도 않을 것 같아서 잘 안쓰는 기능 중에 하나인 것 같습니다.

엑셀 수식 진단 및 분석

'수식 분석' 기능은 복잡한 수식, 오류가 발생한 수식을 진단하고 분석할 때 상당히 유용한 기능입니다.
약간의 시간을 들여 기본 원리만 이해해도 손쉽게 쓸 수 있는 보물상자와 같은 기능입니다.

이번 글에서 '수식 분석' 기능 중에서 '참조되는 셀 추적', '참조하는 셀 추적' 기능을 알아보겠습니다.

 

1. 참조 셀 추적 기본 개념

1) 참조되는 셀 추적

'참조되는 셀 추적' 기능은 현재 선택된 셀의 값에 영향을 미치는 셀(현재 셀에 참조가 되는)이 어떤 것인지를 확인할 때 사용합니다.

아래 그림의 표와 같이 영업사원별로 판매금액의 5%를 인센티브로 지급한다고 할 때 [D6] 셀에는 판매금액에 인센티브 지급율을 곱하는 수식 =C6*$C$3이 입력되어 있습니다.

[D6] 셀을 선택하고 '참조되는 셀 추적'을 누르면 이 수식에 참조가 되는 셀(즉, 이 수식에서 참조하는)에서 수식이 입력된 셀을 가리키는 연결선이 표시됩니다.

아래 그림과 같이 ① 추적하고자 하는 셀 [D6]를 선택한 후

엑셀 참조되는 셀 추적

 

② 리본 메뉴 [수식] 탭 → [수식 분석]그룹 → [참조되는 셀 추적]을 누르면

엑셀 참조되는 셀 추적

 

③ [D6]셀을 가리키는 파란색 연결선이 그려집니다.

인센티브는 =판매금액 * 인센티브 지급율로 구해지므로 [C6] '판매금액' 셀과 [C3] '인센티브 지급율'셀에서 인센티브가 입력된 [D6] 셀을 가리키는 연결선이 그려집니다.

즉, 판매금액 셀과 인센티브 지급율 셀이 인센티브 셀에 영향을 준다는 것이 연결선으로 표현된 것입니다.

엑셀 참조되는 셀 추적

 

2) 참조하는 셀 추적

'참조하는 셀 추적' 기능은 현재 선택된 셀의 값에 영향을 받는 셀(현재 셀을 참조하는)이 어떤 것인지를 확인할 때 사용합니다.

다음과 같이 영업사원별로 판매금액의 5%를 인센티브로 지급한다고 할 때 [C3] 셀에는 '인센티브 지급율'이 입력되어 있습니다.
[C3] 셀을 선택하고 리본메뉴에서 '참조하는 셀 추적'을 누르면 '[C3]셀'에서 '[C3]셀을 참조하는 셀'을 가리키는 연결선이 표시됩니다.

엑셀 참조하는 셀 추적

 

3) 연결선 제거

'참조되는 셀 추적', '참조하는 셀 추적'을 실행했을 때 표시된 연결선은 '연결선 제거' 기능을 이용하여 제거할 수 있습니다.

엑셀 참조하는 셀 추적 연결선 제거

  • 연결선 제거 : 한번에 전체 연결선을 제거
  • 참조되는 셀 연결선 제거 : 선택된 셀의 연결선만 제거
  • 참조하는 셀 연결선 제거 : 선택된 셀의 연결설만 제거

 

2. 응용하기

이번에는 실무에서 응용할 수 있는 몇 가지 예를 알아보겠습니다.

1) 중간 계산 과정이 있는 수식의 셀 추적하기

아래 그림은 '판매이력' 데이터로부터 '상품별 판매금액 합계'를 구하는 예입니다.

[C25] 셀에 '상품별 판매금액 합계'를 구하는 수식이 다음과 같이 입력되어 있고, 이 수식은 '판매이력'의 '판매금액'을 참조하여 합계를 구합니다.
=SUMIF($C$14:$C$21;B25;$F$14:$F$21)

판매이력의 판매금액은 '판매수량'에 '단가'를 곱해서 구합니다.
 =D14*E14 (=판매수량*단가)

이렇게 중간 계산과정이 있는 경우에는 수식이 복잡해서 이해하기 어려운데 '참조되는 셀 추적' 기능을 이용하면 쉽게 이해할 수 있습니다.

[C25] 셀을 선택하고 리본메뉴에서 '참조되는 셀 추적'을 누르면 아래와 같이 참조되는 셀의 위치가 표시됩니다.

 

이 상태에서 '참조되는 셀 추적'을 한 번 더 누르면 다음과 같이 판매금액 셀을 가리기는 연결선이 추가로 그려집니다. 이렇게 중간 계산과정이 있는 경우에는 '참조되는 셀 추적'을 계속 누르면 이전 단계를 계속 표시해 주므로 전체 계산과정을 차례대로 살펴볼 수 있습니다.

 

2) 참조되는 셀에 오류가 있을 때 추적하기

아래 그림의 [C25] 셀에는 상품별 판매금액 합계 수식 =SUMIF($C$14:$C$21;B25;$F$14:$F$21)이 입력되어 있는데 오류가 발생했습니다.

어떤 이유로 오류가 발생했는지 추적해 보겠습니다.
[C25] 셀을 선택하고 리본메뉴에서 '참조되는 셀 추적'을 누르면...

엑셀 수식 오류 추적하기

 

다음과 같이 참조되는 셀의 위치가 연결선으로 표시됩니다.

엑셀 수식 오류 추적하기

연결선이 2군데 표시되었는데 왼쪽의 연결선은 파란색이고 오른쪽의 연결선은 빨간색입니다.

빨간색 연결선은 참조되는 셀에 오류가 있다는 의미입니다. 오른쪽의 [$F$14:$F$21] 셀 범위를 살펴보면 [F19]셀에 오류가 있습니다.

수식이 =D29*E19로 입력되어야 하는데 =ㅂ29*E19로 입력되어 있어서 오류가 발생한 것입니다.
이렇게 참조하는 셀 범위에 오류가 있을 때 빨간 연결선을 따라가 보면 오류를 손쉽게 찾을 수 있습니다.

 

3) 입력된 수식자체가 오류일 때 추적하기

아래 그림의 [C25] 셀에는 상품별 판매금액 합계 수식 =TUMIF($C$14:$C$21;B25;$F$14:$F$21)이 입력되어 있는데 오류가 발생했습니다.

어떤 이유로 오류가 발생했는지 추적해 보겠습니다.
[C25] 셀을 선택하고 리본메뉴에서 '참조되는 셀 추적'을 누르면 다음과 같이 참조되는 셀의 위치가 연결선으로 표시됩니다.

엑셀 수식 오류 추적하기

위에서 살펴본 경우와 다르게 연결선이 전부 파란색입니다. 즉, 참조되는 셀에는 오류가 없다는 뜻입니다.

그렇다면 수식자체에 문제가 있는 것입니다.
=TUMIF($C$14:$C$21;B25;$F$14:$F$21) 

SUMIF의 첫 글자 S를 T로 잘못 입력해서 TUMIF로 입력하는 바람에 오류가 발생한 것입니다.

 

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

 

 

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: