[요점만 집어보는 실무엑셀] 엑셀 수식 분석 및 오류

엑셀에서 수식은 아주 널리 쓰이는 유용한 기능 중 하나다.

 

# 사용자 지정 수식 만들기

[수식]-[이름 정의]를 누르고 지정된 셀을 클릭하면 사용자가 지정한 수식을 만들 수 있다.

예를 들어

기존에 원주율은 =pi를 입력해야지 값이 나왔다. 하지만 위 기능을 이용하여,

'파이'라는 새 이름을 지정해주면 그 후로는 =파이 입력 시 원주율이 뜬다.

 

하나의 값뿐만 아니라 행들에 있는 데이터들을 통째로 지정할 수도 있다.

위 방법으로 A열을 데이터 1로 지정하고, B열을 데이터 2로 지정한 뒤

함수 SUMPRODUCT를 이용면, '데이터 1'이라는 글자 자체를 함수 요소처럼 쓸 수 있게 된다.

이때 SUMPRODUCT함수 특성상 데이터 1과 데이터 2의 행길이는 같아야 한다.

 

위 기능을 이용하여 조금 더 응용해보자.

  - H열의 매출액에 "매출액"이라는 이름을 정의

  - J열의 수익률에 "수익률"이라는 이름을 정의

  - 정의된 이름의 데이터를 이용하여 J열의 총 매출액과 총 수익금 계산

  - G열을 "판매수량"이라 정의하고 J열의 총 판매량 계산

이 방법으로 수식을 작성하게 되면 눈으로 보기에도 훨씬 쉽고 수식도 비교적 간단하게 만들 수 있다.

단 이름은 문자나 밑줄로 시작하고, 숫자, 마침표, 밑줄을 사용할 수 있다. 공백이나 이외의 기호는 사용할 수 없다.

 

# 수식 참조되는 셀 추적하는 법

[수식]-[수식 분석]-[참조되는 셀 추적]

수식이 복잡한 경우 어떤셀에서 수식이 왔는지 확인하기 어렵다.

이로 인해 수식이 과연 맞는지 틀린 지 구분하기 힘들고 수식 특성상, 사소한 작은 실수 하나가,

데이터 전체를 망칠 수도 있다. 위 기능을 사용하면 그림처럼 쉽게 수식에 사용된 셀들을 알 수 있다.

총수익금은 SUM(매출액)*수익률로 구했으므로,

사전에 이름 정의한 매출액에 해당되는 셀들과 수익률 셀을 곱한 값에서 비롯된 것임을 확인할 수 있다.

파란색 작은 동그라미는 선택된 셀을 의미한다.

[참조하는 셀 추적] 기능으로 현재 셀이 어떤 수식에 참조가 되는지도 확인할 수 있다.
[수식 표시] 기능으로는 현제 시트에 있는 모든 셀에 있는 수식들을 표시해준다. 어떤 수식이 사용되었는지 한눈에 보고 싶을 때 유용한 기능이다.

 

#숫자와 문자가 혼합된 셀 숫자만 계산되게 하는 법

SUM함수나 다른 사칙연산 함수 사용 시 만약 셀 안에 '50개'라 고 표시된 경우 '개'라는 문자 때문에 숫자 데이터가 아니게 된다,

그래서 함수에 오류가 생긴다. 하지만 단위를 꼭 표시해야 하는 경우 사용하는 방법이다.

 

[홈]-[표시 형식]에서 표시 형식 글자 옆 작은 화살 표를 누르면 위와 같은 화면이 뜬다.

날짜, 개, 돈 단위 등등 다양한 숫자 수식어를 쓸 수 있고, 이 문자들은 실제로 함수 사용 시 없는 것으로 취급되기 때문에, 함수사용시 오류가 발생하지 않는다. 뿐만 아니라 사용자가 직접 문자를 지정할 수도 있다 예를 들면 '번' 같은 경우이다. 

현재 목록에는 없지만 직접 타이핑하여, 사용할 수 있다.

 

% 오류의 종류

##### : 셀의 너비가 좁아 내용을 모두 표현하지 못함.

#DIV/0! : 0으로 나눗셈

#N/A : 수식 참조 위치에 데이터가 존재하지 않음. 또는 수식의 결괏값이 두 개 이상 발생하는데 어떤 값을 표현해야 할지 모름.

#NAME? : 정의되지 않은 이름을 사용하거나, 범위 참조 시 colon이 누락된 경우

#NULL! : 두 개 이상의 셀 범위 값을 사용하는 수식에서 잘못된 연산자가 사용된 경우

#NUM! : 식에 숫자 인수를 잘못 사용하거나, Excel의 최대 숫자 범위를 초과하는 경우

#REF! : 다른 수식에서 참조하고 있는 셀의 내용이 삭제된 경우 #VALUE! : 잘못된 인수나 값을 사용하는 경우.

 

#다른 수식 옵션

 

[수식]-[수식 분석]-[수식 계산]: 여러 단계의 계산을 수행하는 식의 계산 과정을 단계적으로 볼 수 있다.

[수식]-[계산]-[계산 옵션]

자동(default): 계산에 필요한 데이터가 바뀔 때마다 새로 계산   - 계산량이 많고, 이 계산에 필요한 여러 데이터를 수정해야 한다면 많은 시간이 소요된다.

데이터 표만 수동: 데이터 표 이외의 사항이 바뀔 때는 바로 계산을 update 하지만, 데이터 표 내용이 바뀔 때에는 하지 않는다.

수동: 통합 문서를 저장할 때, 또는 "지금 계산" 버튼을 누를 때 계산을 수행한다.

[수식]-[수식 분석]-[조사식 창] : 수식이 들어 있는 셀들에서 조사식을 추가해 놓으면, 모든 계산 결과와 수식을 조사식 창에 보여줌. 큰 워크시트에서 수식들이 연결되어 있는 경우 편리