엑셀(excel) 함수 TIP 30 - (참조하는,되는 셀,오류검사 및 추적,순환참조)
● 수식 표시하기
하단과 같은 자료가 있을 때 [금액] = [수량]*[단가]입니다. 수식입력이 잘 되었는지 안되었는지를 확인하기 위해서는 해당셀을 클릭하고 수식입력줄에서 수식을 하나하나 확인해야 할 것입니다. 이는 시간이 많이 소요되는 작업이고 또한 여간 번거로운일이 아닐 수 없습니다. 엑셀(EXCEL 2007)에서 이러한 수식이 맞게 작성되었는지를 수식표시를 통해서 간단하게 확인 할 수 있습니다.
▷ 엑셀(EXCEL)원본데이터
[수식탭 - 수식분석그룹 - 수식표시]
하단과 같이 수식이 적용되어 있는 부분은 값이 아닌 수식으로 나타납니다. 이 수식에서 수식오류에 대한 부분을 확인할 수 있습니다. 만약 수식에 오류가 있을 경우에는 빨강색으로 표시됩니다. 이렇게 나타난 수식을 값으로 나타내고자 한다면 다시 [수식표시]를 클릭하면 해당수식이 사라지고 원래의 값이 나타납니다.
● 참조되는셀, 참조하는 셀 표시
엑셀의 시트에서 수식을 통하여 값이 계산될 때 [참조되는 셀]과 [참조하는 셀]을 화살표를 통하여 표시할 수 있습니다.
[참조되는 셀 : 분석하고자 하는 셀 선택(E3) - 수식탭 - 수식분석 그룹 - 참조되는 셀 추적]
하단과 같이 해당 셀(E3)의 값이 나오기 위해 참조되는 셀은 [C3]와 [D3]가 됩니다. 화살표를 통하여 연결이 되어 있고 참조되는 두 셀에는 파란 원점이 선과 연결되어 있습니다.
[참조하는 셀 : 분석하고자 하는 셀 선택(E3) - 수식탭 - 수식분석 그룹 - 참조하는 셀 추적]
하단과 같이 해당 셀(E3)의 값을 참조하여 계산되는 값은 [E18]셀이 됩니다. 화살표를 통하여 두 셀이 연결되어 있습니다. 이런 연결선은 문서 저장시자동으로 사라지거나 또는 [수식분석그룹 - 연결설 제거]를 클릭하면 사라집니다.
● 오류검사하기
엑셀에서 작업을 하다보면 하단과 같이 녹색삼각형(빨간동그라미 부분)이 표시되는 경우가 있습니다. 이는 그 부분의 셀에 오류가 있다는 의미입니다. 이번에는 이러한 오류를 찾고 그 오류에 대한 해결을 해 나가는 방법에 대해서 알아보도록 하겠습니다.
▷ 오류표시의 의미
하단과 같이 오류셀을 클릭하고 오류경고아이콘을 클릭하면 6개로 구분이 되어서 나타납니다
첫번째 : 오류에 대한 내용으로 해당 오류의 설명(0으로 나누기 오류)
두번째 : 오류에 대한 도움말(MS OFFICE 검색도움말 창이 나타나며 오류에 대한 설명이 자세히 나옵니다)
세번째 : 계산단계 표시(해당 셀에 적용된 수식이 나타납니다)
네번째 : 오류무시(오류무시를 클릭하면 오류표식(녹색삼각형)이 사라지며 오류로 인식하지 않습니다)
다섯번째 : 수식입력줄에서 편집(수식입력 줄에서 해당 오류를 편집, 수정할 수 있습니다)
여섯번째 : 오류검사옵션
위와 같이 셀에 나타난 오류표식을 클릭하여 하나하나의 오류에 대한 수정작업을 할수 있지만 데이터가 많을 경우에는 많은 시간이 걸릴 것입니다. 이러한 경우에 전체 오류를 표시한 후 단계적으로 오류를 제거하는 방법이 있습니다.
[수식탭 - 수식분석그룹 - 오류검사 - 오류검사]
[오류검사 창 : 이 오류에 대한 도움말, 계산단계표시, 오류무시, 수식입력줄에서 편집]
만약 아래와 같이 [#NAME]오류가 있다면 이를 [오류검사]창에서 수정해 보도록 하겠습니다. [F6셀 오류]는 [잘못된 수식이름 오류]로 [수식에 인식할 수 없는 텍스트가 있습니다]라고 나타납니다. 이를 [수식입력줄에서 편집/클릭]하면
[수식입력 줄]에 그 해당 수식이 나타나며 이곳에서 수정하면 됩니다. 이 오류는 AVERAGE가 AVERAGA로 입력되어서 나타난 오류였습니다.
▷ 오류추적을 통해 오류의 연결선 나타내기
[수식-수식분석그룹 - 오류추적]을 하면 하단과 같이 오류의 해당되는 셀들이 선으로 연결이 됩니다. 이 연결선을 없애기 위해서 [연결설 제거]를 클릭하면 됩니다
● 조사식창
조사식창을 이용하여 셀에대한 정보를 확인할 수 있습니다
[수식탭 -수식분석 - 조사식창 - 조사식추가 - 조사식추가상자 - 추가할 조사식입력 - 드래그해서 입력 - 조사식확인]
하단과 같이 통합문서 이름, 시트이름, 해당 셀, 값, 수식이 나타납니다. 이를 통해서 각각의 해당셀(수식이 입력되어 있는 셀)에 대한 정보를 확인할 수 있습니다.
● 순환참조
오류검사의 한 방법으로 순환참조를 통하여도 검사하고 해당 오류를 수정할 수 있습니다.
[수식탭- 수식분석-오류검사-순환참조 - 오류셀 클릭] 수식입력 줄에서 해당 오류내용 수정]
하단과 같이 [F8]값의 참조되는 범위가 잘못되어서 나타난 결과 입니다. 즉 [=AVERAGE(B8:E8)로 입력이 되어야 하는데 [=AVERAGE(B8:F8)]로 계산되는 자신의 셀이 포함되어서 나타난 오류입니다.
위의 수식입력줄에서 오류의 내용(수식오류)을 수정을 하면 하단과 같이 정확한 계산 값이 나옵니다.
