엑셀을 이용하여 업무 처리시 엑셀이 아닌 다른 프로그램에서 만든 자료(PDF, 웹, HWP, Word, PowerPoint등)에서 복사하여 붙여넣거나 시스템에서 다운로드 받은 자료를 가져와서 처리하는 경우가 많습니다.
이런 데이터는 엑셀에서 만든 것이 아니라서 숫자인것 같지만 텍스트 형식으로 되어 있거나 눈에 보이지 않는 문자를 포함하고 있는 등 오류를 일으킬 수 있는 가능성이 있습니다. 오류가 있는 데이터는 수식에서 처리되지 않거나 처리시 예상하지 못한 문제를 일으킬 수 있으므로 사전에 데이터를 엑셀에 맞는 형식으로 바꾸어 주어야 합니다.
대표적인 오류 데이터 유형에는 다음과 같은 것들이 있습니다.
- 숫자, 날짜인것 같지만 텍스트 형식으로 저장된 경우
- 값 앞에 아포스트로피(')를 포함한 경우
- 값 앞에 공백을 포함한 경우
- 값 앞, 뒤, 중간에 보이지 않는 문자를 포함한 경우
1. '텍스트 나누기' 기능으로 데이터를 변환하기
1) 텍스트로 저장된 날짜, 숫자 변환하기
실습 시트: [텍스트 데이터]
아래에 보이는 데이터는 외부에서 가져온 데이터인데 눈으로 보면 분명히 정상적인 데이터로 보이는데 수식에서 참조해서 계산해 보면 계산이 되지 않습니다.
[홈] 탭 → [표시 형식] 그룹에서 확인해보면 '텍스트'로 되어 있어서 계산이 되지 않는 것입니다.
위의 텍스트 데이터를 '텍스트 나누기' 기능으로 날짜 유형으로 바꿔보겠습니다. 원래 텍스트 나누기 기능은 텍스트로 된 열을 나누는 데 사용하지만, 이렇게 텍스트가 있는 열을 변환하는 데 사용할 수도 있습니다.
01 먼저 ① 변환할 열을 선택하고 [데이터] 탭 → [데이터 도구] 그룹 → [텍스트 나누기]를 선택해 [텍스트 마법사] 대화상자가 나타나면 ② 바로 <마침> 버튼을 누릅니다.
02 텍스트 형식이 날짜 형식으로 변환됩니다.
나머지 '스캔시간'과 '단가' 필드도 위에서 처리한 방법과 동일하게 처리하면 각각 시간과 숫자 형태로 변환됩니다.
[알아 두면 좋아요] 텍스트 나누기를 할 때 1단계에서 바로 <마침>을 누르면 대부분의 경우 숫자 값은 숫자로, 날짜 값은 날짜로, 나머지는 텍스트로 변환됩니다. 가끔 엑셀에서 인식할 수 없는 형태의 데이터가 입력된 경우에는 자동으로 변환되지 않습니다. 예를 들면 날짜가 '2021.10.05'의 형태로 중간에 하이픈이나 슬래시가 아닌 '점'이 들어가 있을 때는 날짜로 변환되지 않습니다. 이때는 <다음>을 눌러서 옵션에서 직접 '날짜'를 선택해 주어야 날짜로 변환됩니다.
2) 아포스트로피(')가 붙은 날짜, 숫자 변환하기
실습 시트: [아포스트로피 데이터]
외부에서 데이터를 가져올 때 아포스트로피(')가 붙어 있는 경우가 있습니다. 일일이 이것을 제거해야 할까요? 위에서 배운대로 '텍스트 나누기'로 원하는 형태로 변환할 수 있습니다.
01 ① 바꿀 열을 선택하고 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [텍스트 나누기]를 선택해 [텍스트 마법사] 대화상자가 나타나면 바로 ② <마침>을 누릅니다.
02 텍스트 형식이 날짜 형식으로 변환됩니다.
2. 기타 다른 방법으로 변환하기
1) 오류표시 메뉴에서 '숫자로 변환'
실습 시트: [텍스트 데이터]
다음과 같이 숫자가 텍스트 형식으로 저장된 경우에는 셀의 왼쪽에 오류를 뜻하는 초록색 삼각형이 표시됩니다.
01 텍스트 형식으로 저장된 숫자를 '숫자'로 변환하기 위해 ① 초록색 삼각형이 표시된 셀을 전부 선택하고 <드롭다운> 버튼을 누르면 나타나는 메뉴에서 ② <숫자로 변환>을 선택합니다.
02 텍스트 형식이 숫자로 변환된 것을 확인할 수 있습니다.
2) 연산해서 붙여넣기
원래의 값이 텍스트 형식으로 된 숫자일 경우에 1을 곱해서 붙여넣으면 원래의 값이 숫자로 변환됩니다.
자세한 방법은 다음의 링크의 ' 연산해서 붙여넣기' 부분을 참고하세요.
3. 오류 표시를 무시하면?
엑셀에서 숫자 데이터를 처리할 때 아래 그림과 같이 오류표시를 볼 수 있는데 '오류 무시'를 선택해서 오류가 표시되지 않도록 할 수 있습니다.
이것은 오류를 해결한 것이 아니라 그냥 오류가 안보이도록 했을 뿐입니다.
오류 표시를 무시하면 나중에 오류 값을 참조하는 수식에서 오류가 나도 찾기가 어렵고 생각하지 못한 많은 문제를 일으킬 수 있으므로 특별한 이유가 없다면 '오류 무시' 옵션을 사용하지 말고 '숫자로 변환'작업을 해 주는 것이 바람직합니다.
아래 그림과 같이 [Excel 옵션]에서 '다른 작업을 수행하면서 오류 검사' 옵션을 끄면 아예 오류표시가 되지 않도록 할 수 있습니다.
위의 '오류 무시'보다 더 많은 문제를 일으킬 수 있으므로 가능하면 사용하지 않는 것이 좋습니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
