엑셀에서 텍스트로 저장된 숫자 계산하기
엑셀에서 텍스트로 저장된 숫자가 당신의 업무를 막는 이유
엑셀을 사용하다 보면 가장 당황스러운 순간 중 하나는 분명히 숫자를 입력하고 계산식을 넣었는데 결과가 제대로 나오지 않을 때입니다. SUM 함수를 썼는데 0이 나오거나 합계가 이상하게 계산되는 경험을 누구나 한 번쯤 해보셨을 것입니다. 이 문제의 대부분은 셀에 입력된 데이터가 숫자가 아니라 텍스트로 인식되고 있기 때문에 발생합니다.
엑셀은 셀의 데이터 형식에 매우 민감합니다. 겉보기에는 똑같은 숫자 100이라도 컴퓨터는 이를 연산이 가능한 숫자로 볼 수도 있고, 단순한 글자 모음인 텍스트로 볼 수도 있습니다. 텍스트로 저장된 숫자는 사칙연산이나 통계 함수를 적용할 수 없기 때문에 업무 효율을 심각하게 떨어뜨립니다. 특히 다른 프로그램에서 데이터를 내보내기 하거나 웹페이지의 표를 복사해서 붙여넣을 때 이런 현상이 빈번하게 일어납니다.
엑셀이 숫자를 텍스트로 오해하는 이유
엑셀이 숫자를 텍스트로 인식하는 데에는 몇 가지 대표적인 원인이 있습니다. 첫째는 데이터가 외부 시스템에서 유입될 때 발생합니다. ERP 프로그램이나 데이터베이스, 웹사이트에서 엑셀로 데이터를 가져올 때 숫자 데이터에 보이지 않는 공백이나 특수문자가 포함되면 엑셀은 이를 안전하게 처리하기 위해 텍스트로 변환해 버립니다.
둘째는 사용자가 의도치 않게 서식을 미리 지정해 둔 경우입니다. 셀의 표시 형식 미리 설정에서 텍스트로 지정된 상태로 숫자를 입력하면 아무리 숫자를 쳐도 텍스트로 입력됩니다. 셋째는 숫자 앞에 싱글 쿼테이션 기호가 붙어 있는 경우입니다. 전화번호나 사번처럼 0으로 시작하는 숫자를 입력할 때 엑셀이 맨 앞의 0을 지우는 것을 방지하기 위해 따옴표를 붙이는데 이 역시 텍스트로 저장되는 주된 원인입니다.
텍스트 숫자를 진짜 숫자로 바꾸는 가장 빠른 방법
문제를 파악했다면 이제 이를 해결할 차례입니다. 엑셀에는 텍스트로 저장된 숫자를 정상적인 숫자 데이터로 변환하는 여러 가지 방법이 존재합니다. 상황과 데이터의 양에 맞춰 가장 적절한 방법을 선택하는 것이 중요합니다.
오류 표시와 초록색 삼각형을 활용하는 방법
텍스트로 저장된 숫자가 있는 셀의 왼쪽 상단을 보면 아주 작은 초록색 삼각형이 표시되어 있는 것을 볼 수 있습니다. 이것은 엑셀이 사용자에게 보내는 경고 신호입니다. 이 셀을 클릭하면 옆에 느낌표 아이콘이 포함된 작은 상자가 나타나는데 이를 클릭하면 메뉴가 펼쳐집니다. 그 메뉴 중에서 숫자로 변환을 선택하기만 하면 단 몇 초 만에 해당 데이터가 숫자로 바뀝니다. 데이터가 많지 않을 때 가장 직관적으로 쓸 수 있는 방법입니다.
곱하기 연산을 이용한 강제 변환 기술
수백 개에서 수만 개에 달하는 대용량 데이터를 처리할 때는 초록색 삼각형을 하나씩 누를 수 없습니다. 이럴 때는 엑셀의 연산 특성을 이용하는 기발한 방법을 씁니다. 빈 셀에 숫자 1을 입력하고 그 셀을 복사한 뒤, 텍스트 숫자가 들어 있는 범위를 모두 지정합니다. 그 후 마우스 우클릭을 하고 선택하여 붙여넣기를 누른 다음 연산 항목에서 곱하기를 선택하고 확인을 누릅니다. 텍스트 상태의 숫자에 1을 곱하는 과정에서 엑셀이 강제로 숫자로 형식을 변환하기 때문에 매우 빠르고 대량의 데이터를 한 번에 바꿀 수 있습니다.
텍스트 줄 나누기 기능을 활용하는 우회법
데이터 형식을 일괄적으로 바꾸는 또 다른 숨겨진 꿀팁은 텍스트 줄 나누기 기능을 활용하는 것입니다. 변환하고자 하는 텍스트 숫자 열 전체를 선택한 후 데이터 탭에 있는 텍스트 줄 나누기를 누릅니다. 마법사 창이 나타나면 아무것도 건드릴 필요 없이 그냥 다음을 두 번 누르고 마지막 단계에서 열 데이터 서식을 일반으로 둔 채 마침을 누르면 됩니다. 이 과정만으로도 엑셀은 해당 열의 데이터를 인식하고 숫자로 재배치합니다.
함수를 사용해 데이터 속성 변환하기
원본 데이터를 직접 수정하는 것이 부담스럽거나 수식을 통해 새로운 열로 결과값을 도출해야 할 때는 엑셀 함수를 활용하는 것이 정석입니다. 텍스트를 숫자로 만들어주는 대표적인 함수는 VALUE 함수입니다. 특정 셀을 참조하여 =VALUE(A1)과 같이 입력하면 텍스트로 되어 있던 숫자를 완벽한 숫자 데이터로 변환하여 새로운 값으로 반환해 줍니다.
만약 함수를 복잡하게 쓰기 싫다면 단순히 사칙연산 기호를 활용해도 됩니다. 텍스트 숫자가 들어 있는 셀에 더하기 0을 하거나 1을 곱해주면 엑셀은 자동으로 이를 숫자로 인식하여 계산을 수행합니다. 예를 들어 =A1*1 이라고 입력하면 결과값은 원래 숫자와 같아지면서 데이터 속성은 숫자로 바뀝니다. 이를 아래로 쭉 복사한 뒤 값으로 복사하여 붙여넣기 하면 완벽하게 숫자로 변환된 데이터를 얻을 수 있습니다.
텍스트 숫자와 관련된 흔한 오해와 진실
많은 사람들이 엑셀 셀의 서식을 일반에서 숫자로 바꾸기만 하면 텍스트 숫자가 바로 해결될 것이라고 생각합니다. 하지만 이는 가장 흔하게 하는 오해 중 하나입니다. 이미 텍스트로 입력되어 저장된 데이터에 대해서는 단순히 셀 서식만 숫자로 변경한다고 해서 데이터의 속성이 바뀌지 않습니다. 셀 서식은 앞으로 입력될 데이터의 형식을 지정하는 것이지 이미 입력된 데이터의 본질을 강제로 바꾸지는 못하기 때문입니다. 반드시 앞서 설명한 변환 작업이나 연산 과정을 거쳐야 비로소 숫자로 인식됩니다.
또 다른 오해는 화면에 보이는 모양이 같으면 계산 결과도 같을 것이라는 생각입니다. 화면에 100이라고 표시되어 있어도 정렬 상태가 왼쪽으로 되어 있다면 이는 텍스트일 확률이 매우 높습니다. 숫자는 기본적으로 오른쪽 정렬이 되지만 텍스트는 왼쪽 정렬이 되기 때문입니다. 정렬 상태만 주의 깊게 살펴보아도 데이터가 텍스트인지 숫자인지 쉽게 구별할 수 있습니다.
업무 효율을 높이는 데이터 관리 습관
텍스트 숫자로 인한 오류를 사전에 방지하기 위해서는 데이터를 입력하거나 가져올 때부터 올바른 습관을 들일 필요가 있습니다. 외부 시스템에서 데이터를 다운로드 받을 때는 가능하면 텍스트 파일 형태보다 엑셀 파일 형태로 직접 내려받는 것이 형식이 깨지는 것을 막는 데 도움이 됩니다.
또한 데이터를 복사해서 붙여넣을 때 그냥 붙여넣기를 하지 말고 값만 붙여넣기를 활용하거나 텍스트 마법사를 거치는 과정을 습관화하면 불필요한 오류를 대폭 줄일 수 있습니다. 정기적으로 사용하는 양식이 있다면 입력하는 칸의 서식을 미리 숫자로 명확하게 지정해 두는 것도 좋은 방법입니다. 작은 관리 습관의 차이가 퇴근 시간을 앞당기는 비결이 됩니다.
자주 묻는 질문
질문: 텍스트 숫자를 숫자로 바꿨는데 앞에 있던 0이 다 사라졌어요. 어떻게 해야 하나요?
답변: 엑셀에서 숫자는 맨 앞의 0을 의미 없는 것으로 판단하여 자동으로 생략합니다. 사번이나 전화번호, 주민등록번호처럼 앞의 0이 유지되어야 하는 데이터는 숫자로 바꾸면 안 되며 애초에 텍스트 형식으로 유지해야 합니다. 만약 꼭 0을 유지하면서 계산이나 정렬을 하고 싶다면 사용자 지정 표시 형식에서 0000과 같이 자릿수를 지정해 주어야 합니다.
질문: 셀 서식을 숫자로 바꿨는데도 왜 계속 계산이 안 되나요?
답변: 앞서 설명했듯이 이미 텍스트로 입력된 데이터는 서식 변경만으로는 숫자로 바뀌지 않습니다. 초록색 삼각형을 이용하거나 선택하여 붙여넣기에서 곱하기 1을 적용하는 등 강제로 데이터를 가공하는 단계를 거쳐야만 정상적인 숫자로 인식을 하게 됩니다.
질문: 함수를 썼는데도 밸류 오류가 계속 발생합니다. 원인이 무엇인가요?
답변: 데이터 안에 숫자 외에 보이지 않는 특수문자나 공백, 혹은 알파벳 같은 문자가 섞여 있을 가능성이 높습니다. 이럴 때는 데이터의 공백을 제거하는 함수나 문자열을 다루는 함수를 함께 사용하여 불순물을 제거한 뒤 숫자로 변환 작업을 진행해야 합니다.
질문: 다른 사람이 보낸 파일인데 너무 복잡해서 어디가 텍스트인지 찾기 힘들어요.
답변: 조건부 서식을 활용하면 아주 쉽게 찾을 수 있습니다. 셀 값이 숫자인지 판별하는 ISNUMBER 함수를 조건부 서식 규칙에 적용하여 텍스트인 셀에만 색상이 들어가도록 설정하면 시트 전체에서 문제의 셀들을 한눈에 파악할 수 있습니다.
댓글 0
첫 댓글을 남겨보세요.