엑셀에서 숫자인데 계산이 안돼요.
분명 수식으로 계산했는데, 결과값이 안뜨고 텍스트로 수식이 뜨거나 #VALUE! 오류가 뜨는 경우를 만날 수 있다.
웹에서 다운로드한 데이터가 변환 과정을 거치면서 이런 오류들이 나타날 수 있는데, 숫자 수식이나 함수 결과가 제대로 나타나지 않는 이유는 ‘숫자가 숫자로 인식 되고있지 않기’ 때문입니다. 숫자로 인식되지 않는 현상은 아래의 이유 때문인 경우가 대부분인데요,
- 형식이 다른 무언가로 되어있거나
- 숫자에 다른 무언가가 껴있거나
이제, 숫자를 숫자로 불러봅시다.
1. 표시형식 오류 : 텍스트 -> 숫자
형식 오류를 해결하는 2가지 방법!
홈 > 표시형식 > 숫자 로 되어있는지 확인 후 > 결과셀 더블클릭 후 엔터.
(여러셀에 적용 해야 한다면 수식 복사)
단순히 표시 형식만 텍스트에서 숫자로 바꿔주었더니, 수식으로 표시되던 부분이 계산 결과값으로 바뀌는 것을 확인할 수 있습니다.
2. 형식 오류 : x1 해서 숫자로 변환하기
위 방법으로 해결이 되지 않는 경우에는, 원래 값에 1을 곱해서 숫자 형식으로 변환해 줄 수 있습니다.
표시 형식만 바꾸어 계산이 작동하지 않는 경우, 새로운 셀에 숫자로 인식되지 않는 열에 1을 곱해주면 숫자로 돌아오는 경우가 있습니다.
유령문자를 지우는 2가지 방법!
3. 유령 문자👻 : Alt + 1,6,0
웹에서 다운로드하거나 복사 붙여넣기를 하는 과정에서 간혹 유령 문자가 침투하는 현상이 생긴다. 셀 내용을 보게 되면 숫자 옆에 공백이 있는 것을 볼 수 있는데, 눈에 보이지 않는 공백을 확인하기위해 모든 셀을 확인할 수 없는 노릇인데요. 유령문자 입력법과 바꾸기 기능(Ctrl + H)으로 유령문자를 지워봅시다.
바꾸기 기능을 이용해서 유령 문자를 지워주면 되는데, 찾을 값에 Alt + 1,6,0을 쳐주면 공백이 생기는 것을 확인할 수 있습니다. 바꿀 값에는 아무것도 없는 상태로 바꾸기를 진행해주면 유령문자가 사라집니다.
위 화면에는 유령문자의 위치를 확인해보려고 유령 이모지로 교체 해보았습니다.
4. 유령 문자👻 : cells.replace ChrW(160),””
alt + 1,6,0이 안 먹히는 경우가 있을 수 있는데, 그럴 땐 Visual Basic을 이용해서 직접 실행을 해주면 됩니다.
- 1) Alt + F11 을 눌러 VBA를 켠다.
- 2) 하단부에 직접 입력 창(보기 > 직접실행 창)에
[cells.replace ChrW(160),”“]를 입력 후 엔터. - 3)그리고 VBA창을 닫아주면 유령문자들이 제거된 것을 확인할 수 있다.
실행이 되지 않고 alert창이 뜬다면 오타를 확인해볼 것!
[cells.replace ChrW(160),””] 는 시트 내 모든 셀에서 ChrW(160)을 ““(아무것도 없음)으로 교체하겠다는 명령입니다. 즉, 유령문자를 없애는 방법입니다. 이렇게 유령 문자들을 없애주면 수식의 결과가 원하는대로 나타나는 것을 확인할 수 있습니다.
대부분의 숫자 인식 오류들은 위 방법으로 해결이 됩니다. 특히 외부 데이터를 받아 사용하는 경우 종종 이런 문제로 시간을 많이 잡아먹곤했는데, 이제 골치 아픈 엑셀 오류 정복하고 얼른 집으로 갑시다.
오늘도 칼퇴를 향해서!
엑셀 숫자 오류 해결 목차 바로가기
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
[엑셀프레소] 엑셀 숫자 오류 단축키로 빠르게 해결하기
엑셀 텍스트 형식의 숫자로 인해 발생하는 오류
엑셀은 셀 안에 입력된 값의 형식을 자동으로 인식합니다. 따라서 셀에 입력된 값이 숫자이지만, 텍스트 형태로 입력된 경우 셀의 왼쪽 위로 초록색 표식이 추가되면서 "텍스트 형식으로 저장된 숫자"라는 오류표시가 추가됩니다. 대표적으로 숫자 앞에 작은따옴표(')가 포함되었거나 오래된 프로그램에서 다운받은 데이터일 경우 이러한 문제가 발생하는데요.
최신 버전의 엑셀은 텍스트 형식으로 저장된 숫자더라도, 해당 셀을 참조하여 계산 할 때에는 자동으로 일반 숫자로 인식하여 문제가 없지만 이전 버전의 엑셀을 사용할 경우 텍스트 형식으로 저장된 숫자를 참조하여 계산시 예상치 못한 문제가 발생할 수 있습니다. 따라서 이러 강의에서는 이러한 텍스트 형식의 숫자를 한 번에 올바른 숫자형태로 바꾸는 방법에 대해 알아보겠습니다.
엑셀 오류표시에 대한 자세한 설명은 아래 기초입문강의 영상을 참고해주세요.
단축키로 엑셀 숫자 오류 1초만에 해결하기
- 시트 위의 알파벳을 클릭하면 전체 열을 선택됩니다. 전체 열을 선택합니다.
- 전체 열이 선택된 상태에서 ALT - A - E - F 키를 하나씩 입력합니다. ALT - A - E - F 단축키를 하나씩 입력합니다.
- 모든 오류표시가 사라지며 숫자 형식으로
변경됩니다. 숫자가 올바른 형태로 한 번에 변경되었습니다.
오빠두Tip : 이 기능은 텍스트 나누기를 활용하는 것으로 여러개 열에는 동시에 적용할 수 없습니다. 따라서 여러개 열의 값을 바꿔야 할 경우, 열을 하나씩 선택해서 단축키를 입력하거나, 아래 방법으로 문제를 해결합니다.
- 만약 숫자로 바꿔야 할 열의 개수가 많을 경우, 아무 비어있는 셀에 1 을 입력한 뒤, 복사합니다. 비어있는 셀에 1을 입력합니다.
- 이후 숫자로 변경할 범위를 우클릭 - 선택하여 붙여넣기로 이동한 후 "곱하기"를 선택 후 [확인] 버튼으로 적용하면 모든 값을 숫자로 한 번에 변경할 수 있습니다. 연산에 곱하기를 선택 후, 확인 버튼을 클릭합니다.
단축키 동작원리 - 텍스트 나누기 바로 실행
이번 강의에서 사용한 ALT - A - E - F 단축키는 텍스트 나누기를 빠르게 실행하는 단축키입니다. 엑셀에서 키보드 ALT 키를 사용하면 대부분의 기능을 단축키로 구현할 수 있는데요.
ALT 키를 누르면 각 탭과 리본메뉴의 단축키가 화면위로 표시되며, 각 알파벳을 눌러 원하는 기능을 빠르게 실행할 수 있습니다.
이번 강의에서 사용한 텍스트 나누기는 셀 안에 입력된 값을 나눈 뒤, 나눠진 값을 셀 안에 다시 입력합니다. 그리고 값이 다시 입력되면서 텍스트 형식의 숫자가 올바른 숫자 형태로 변경됩니다.