티스토리 뷰
엑셀에서 우리가 계산한 값이 엉뚱하게 나오는 이유는 이렇습니다.
- 입력한 데이터가 어떤 값으로 표시되지만 실제 값은 거기에 표시된 값과 다를 수 있기 때문입니다. 그게 어떻게 가능하냐고요?
- 왜냐하면, 셀 한 칸에는 '입력한 값', '계산된 값', '표시되는 값' 이 3가지가 서로 다를 수 있기 때문입니다.
- 예를 들어 '=Date(1900,1,1)'로 입력한 값의 계산된 값은 '1'이고, 표시되는 값은 '1900-1-1' 이다.
때문에 엑셀에 입력한 숫자들을 잘 다루려면 다음과 같은 사항들에 대한 이해가 필요합니다.
- 지금 눈에 보이는 값들은 모두 '표시형식'의 변환을 한 번 거친 '표시된 값'임을 인지할 것
- 실제로 그 데이터의 값(계산된 값)이 어떤 값인지 확인할 것
차근차근 자세한 내용들에 대해 알아보겠습니다.
1. '입력한 값', '계산된 값', '표시되는 값'
여러분은 엑셀의 한 셀에 위 3가지 값이 모두 다를 수 있다는 것을 알고 계셨나요? 저는 꽤 늦게 깨우친 편이었습니다. 왜냐하면 위 3가지 값은 항상 그런 것이 아니라, 어쩔 때는 서로 같고 어쩔 때는 서로 달라 그 개념을 파악하기가 애초에 어렵게 되어 있기 때문입니다.
따라서 위 3가지 값이 모두 다른 '=Date(1900,1,1)' 함수를 통해 설명 드리겠습니다.
[A1]과 [A2]셀에 =Date(1900,1,1)을 입력했습니다. 그리고 함수를 그대로 보여주는 옵션 [수식 표시(Ctrl+`)] 옵션을 켜 입력된 함수값을 그대로 보이도록 설정해 놓앗습니다. 상단의 함수표시줄에도 역시 '=Date(1900,1,1)' 이라는 문구가 입력되어 있습니다. (참고로 Date함수는 연, 월, 일을 입력하면 1900년 1월 1일을 기준으로 한 날짜값을 반환해주는 함수입니다.)
자, 이제 [수식 표시(Ctrl+`)] 옵션을 꺼보면 [A1]과 [A2] 셀은 각각 어떻게 표시될까요? '1900-1-1'이 아닐까 생각하실지 모르겠습니다. 그러나 제가 미리 설정을 해놓았기 때문에 [A1]과 [A2]는 각각 다음과 같이 표시됩니다.
보시는 바와 같이 [A1]은 '1', [A2]는 '1900년 1월 1일'로 표시되었습니다. 그런데 이때 빨간색으로 표시한 함수표시줄을 보십시오. 입력된 함수는 [A1]과 [A2]가 모두 '=DATE(1900,1,1)' 로 동일합니다. 그런데 표시된 값만 서로 '1'과 '1900년 1월 1일'로 다릅니다.
이과 같은 차이는 무엇 때문에 발생했을까요? 바로 '셀서식'에서 '표시형식'을 서로 다르게 설정해놓았기 때문입니다.
[A1]셀은 [일반]으로 표시형식을 정해놓았습니다. 표시형식을 [일반]으로 정해놓게 되면 데이터를 아무런 가공도 하지 않고 [계산된값] 그대로 표시하게 됩니다. 즉, [A1]셀의 경우에는 [계산된값]과 [표시된값]이 모두 '1'로 같은 경우입니다. 이를 정리하자면 다음과 같습니다.
- [A1]셀의 입력된 값 : =DATE(1900,1,1)
- [A1]셀의 계산된 값 : 1
- [A1]셀의 표시된 값 : 1
그렇다면 [A2]셀은 어떻게 되었을까요?
[A2]셀은 'yyyy년 m월 d일' 형식으로 세팅이 되어 있습니다. 때문에 [A1]셀과 똑같이 '=Date(1900,1,1)'을 입력받아, 숫자 '1'이 계산되었지만, 표시는 '1900년 1월 1일'과 같은 형태로 된 것입니다. 즉, 이 경우에는 이렇습니다.
- [A2]셀의 입력된 값 : =Date(1900,1,1)
- [A2]셀의 계산된 값 : 1
- [A2]셀의 표시된 값 : 1900년 1월 1일
2. 엑셀에서 계산이 잘못되는 이유
이렇듯 한 셀에 '입력된 값'과 '계산된 값', '표시되는 값'은 모두 다를 수 있습니다. 이때 특히, '계산된 값'과 '표시되는 값'을 혼동하면 문제가 생깁니다. 왜냐하면 엑셀에서 더하기 빼기를 할 때 쓰는 값은 사실 '계산된 값'인데 우리 눈에 보이는 것은 실제로 '표시되는 값'이기 때문입니다.
예를 들어보겠습니다.
위 표의 [B열]에 입력된 값들은 모두 표시형식만 다를 뿐 '1.25'라는 숫자값입니다. 다만, 표시형식을 달리 하여 실제 표시되는 값은 '1.25', '1', '1900-01-01', '오전 6:00:00', '125%' 등 제각각 다른 것을 볼 수 있습니다.
그런데, 여기에 [C열]에 표시한 계산식을 사용하여 [D열]에 계산결과를 표시하고 표시형식을 모두 [일반]으로 설정하자 재미있는 일이 발생합니다. 각 셀이 원래 어떻게 표시되어 있든 상관 없이 계산결과는 모두 '2.25'로 동일한 결과가 나오는 것입니다.
애초에 [B열]에 입력되어 계산된 값은 모두 '1.25'였고 여기에 '1'을 더했을 뿐이니 어찌보면 당연한 결과입니다. 그러나 여기서 [회계:5행]만 남기고 다른 행들을 모두 지운다면 어떻게 될까요?
자, 여러분은 이것만 보고 왜 [B2]에 표시된 숫자 1에 다시 1을 더했을 뿐인데 왜 계산결과가 2.25가 되는지 한 눈에 보이십니까? 바로 이처럼 '표시되는 값'에 우리의 눈이 현혹되기 때문에 엑셀을 할 때 계산 실수가 발생하는 것입니다.
3. 엑셀 계산 실수를 방지하는 방법
그렇다면 우리는 어떤 식으로 엑셀에서 계산 실수를 방지할 수 있을까요? 그것은 바로 모든 셀을 선택하고 [표시형식]을 [일반]으로 설정해보는 것입니다. 바로 이렇게 말이죠.
이처럼 모든 셀의 [표시형식]을 [일반]으로 바꾸면 그곳에 [계산된 값]이 무엇인지 한 눈에 알 수 있기 때문에 계산식을 적용함에 있어서 헷갈릴 일이 없습니다. 다만, 이 경우 의도했던 표시형식들이 모두 사라져버리기 때문에 작업은 다음과 같은 순서로 하는 게 좋습니다.
- 잠깐 확인하는 용도라면 [표시형식]을 [일반]으로 바꾸어 데이터를 검토하고 되돌리기(Ctrl+Z)를 한다.
- 오랜 검토가 필요하다면 [시트 복사]를 해서 원본을 보존하고 [표시형식]을 [일반]으로 바꾸어 살펴본다.