Home > 강좌 > 엑셀수식강좌 > 04. 엑셀을 제대로 쓰는 데이터처리 > 엑셀 오류데이터 처리하기

엑셀 오류데이터 처리하기

  • by

엑셀을 이용하여 업무 처리시 엑셀이 아닌 다른 프로그램에서 만든 자료(PDF, 웹, HWP, Word, PowerPoint등)에서 복사하여 붙여넣거나 시스템에서 다운로드 받은 자료를 가져와서 처리하는 경우가 많습니다.

이런 데이터는 엑셀에서 만든 것이 아니라서 숫자인것 같지만 텍스트 형식으로 되어 있거나 눈에 보이지 않는 문자를 포함하고 있는 등 오류를 일으킬 수 있는 가능성이 있습니다. 오류가 있는 데이터는 수식에서 처리되지 않거나 처리시 예상하지 못한 문제를 일으킬 수 있으므로 사전에 데이터를 엑셀에 맞는 형식으로 바꾸어 주어야 합니다.

대표적인 오류 데이터 유형에는 다음과 같은 것들이 있습니다.

  • 숫자, 날짜인것 같지만 텍스트 형식으로 저장된 경우
  • 값 앞에 아포스트로피(')를 포함한 경우
  • 값 앞에 공백을 포함한 경우
  • 값 앞, 뒤, 중간에 보이지 않는 문자를 포함한 경우
실습용 엑셀파일 다운로드 : 데이터처리-오류데이터처리.xlsx

 

1.  '텍스트 나누기' 기능으로 데이터를 변환하기

1) 텍스트로 저장된 날짜, 숫자 변환하기

아래에 보이는 데이터는 외부에서 가져온 데이터인데 눈으로 보면 분명히 정상적인 데이터로 보이는데 수식에서 참조해서 계산해 보면 계산이 되지 않습니다.

리본메뉴 [홈] 탭 → [표시 형식] 그룹에서 확인해보면 '텍스트'로 되어 있어서 계산이 되지 않는 것입니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

 

위의 텍스트 데이터를 '텍스트 나누기' 기능으로 날짜유형으로 바꿔보겠습니다.  원래 텍스트 나누기 기능은 텍스트로 된 열을 나누는 데 사용하지만,  이렇게 텍스트가 있는 열을 변환하는 데 사용할 수도 있습니다.

먼저 ① 바꿀 열을 선택하고,  리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [텍스트 나누기]를 누르면

아래와 같이 [텍스트 마법사] 대화상자가 나타납니다.

다른 추가작업은 필요없습니다. 바로 ② <마침> 버튼을 누르면

텍스트 형식으로 입력된 날짜,숫자를 변환하기

다음과 같이 텍스트 형식이 날짜 형식으로 변환됩니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

나머지 '스캔시간'과 '단가' 필드도 위에서 처리한 방법과 동일하게 처리하면 각각 시간과 숫자형태로 변환됩니다.

[주의!] 텍스트 나누기를 할 때 1단계에서 바로 <마침> 버튼을 누르면 대부분의 경우 숫자 값은 숫자로, 날짜 값은 날짜로, 나머지는 텍스트로 변환됩니다. 가끔 엑셀에서 인식할 수 없는 형태의 데이터가 입력된 경우에는 자동으로 변환되지 않습니다. 예를 들면 날짜가 '2021.10.05'의 형태로 중간에 하이픈이나 슬래시가 아닌 '점'이 들어가 있을 때는 날짜로 변환되지 않습니다. 이때는 <다음> 버튼을 눌러서 옵션에서 직접 '날짜'를 선택해 주어야 날짜로 변환됩니다.

 

2) 아포스트로피(')가 붙은 날짜, 숫자 변환하기

외부에서 데이터를 가져올 때 아포스트로피(')가 붙어 있는 경우가 있습니다. 일일이 이것을 제거해야 할까요? 위에서 배운대로 '텍스트 나누기'로 원하는 형태로 변환할 수 있습니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

텍스트 나누기 기능을 이용하면 다음과 같이 아포스트로피가 사라지고 날짜 형식으로 변환된 것을 확인할 수 있습니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

 

 

2.  기타 다른 방법으로 변환하기

1) 오류표시 메뉴에서 '숫자로 변환'

다음과 같이 숫자가 텍스트 형식으로 저장된 경우에는 셀의 왼쪽에 오류를 뜻하는 초록색 삼각형이 표시됩니다.

텍스트 형식으로 저장된 숫자를 '숫자'로 변환하기 위해 ① 초록색 삼각형이 표시된 셀을 전부 선택하고 <드롭다운> 버튼을 누르면 나타나는 메뉴에서 ② <숫자로 변환>을 선택합니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

다음과 같이 텍스트 형식이 숫자로 변환된 것을 확인할 수 있습니다.

텍스트 형식으로 입력된 날짜,숫자를 변환하기

 

2) 연산해서 붙여넣기

원래의 값이 텍스트 형식으로 된 숫자일 경우에 1을 곱해서 붙여넣으면 원래의 값이 숫자로 변환됩니다.

자세한 방법은 다음의 링크의 ' 연산해서 붙여넣기' 부분을 참고하세요.

[엑셀에서 복사하고 붙여넣기의 모든 것]

 

 

3.  오류 표시를 무시하면?

엑셀에서 숫자 데이터를 처리할 때 아래 그림과 같이 오류표시를 볼 수 있는데 '오류 무시'를 선택해서 오류가 표시되지 않도록 할 수 있습니다.

엑셀 숫자 오류를 무시하면 안됩니다

이것은 오류를 해결한 것이 아니라 그냥 오류가 안보이도록 했을 뿐입니다.

오류 표시를 무시하면 나중에 오류 값을 참조하는 수식에서 오류가 나도 찾기가 어렵고 생각하지 못한 많은 문제를 일으킬 수 있으므로 특별한 이유가 없다면 '오류 무시' 옵션을 사용하지 말고 '숫자로 변환'작업을 해 주는 것이 바람직합니다.

아래 그림과 같이 [Excel 옵션]에서 '다른 작업을 수행하면서 오류 검사' 옵션을 끄면 아예 오류표시가 되지 않도록 할 수 있습니다.

위의 '오류 무시'보다 더 많은 문제를 일으킬 수 있으므로 가능하면 사용하지 않는 것이 좋습니다.

엑셀 숫자 오류를 무시하면 안됩니다

 

 

[ 엑셀 수식 강좌  - 엑셀 데이터 처리]

 

답글 남기기

이메일 주소는 공개되지 않습니다.