다음 그림과 같이 엑셀의 '수식' 탭에는 '수식 분석'이라는 기능이 있습니다.
한 번쯤은 눌러보고 왠지 어려울 것 같고 딱히 필요하지도 않을 것 같아서 잘 안쓰는 기능 중에 하나인 것 같습니다.
'수식 분석' 기능은 복잡한 수식, 오류가 발생한 수식을 진단하고 분석할 때 상당히 유용한 기능입니다.
약간의 시간을 들여 기본 원리만 이해해도 손쉽게 쓸 수 있는 보물상자와 같은 기능입니다.
이번 글에서 '수식 분석' 기능 중에서 '참조되는 셀 추적', '참조하는 셀 추적' 기능을 알아보겠습니다.
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로 입력하는 바람에 오류가 발생한 것입니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
