엑셀 수식과 함수를 적용하기 직전에 마주치는 가장 큰 걸림돌은 의외로 '지저분하게 엉켜 있는 원본 데이터'입니다. 타 부서에서 공유받았거나 웹 시스템에서 다운로드한 파일의 데이터를 보면, 이름과 직급이 한 셀에 뭉쳐 있거나 중복된 데이터가 수십 개씩 섞여 있는 경우가 다반사입니다.
저 역시 입사 초기에 수천 건의 고객 명단을 정돈하면서, 셀 하나하나를 더블 클릭해 이름과 전화번호를 손으로 분리하고 중복된 이름을 Ctrl + F로 찾아 지우는 노동을 하느라 눈물겨운 야근을 한 적이 있습니다. 하지만 엑셀이 제공하는 기본 데이터 정돈 기능인 '텍스트 나누기'와 '중복된 항목 제거'를 익힌 후에는 수천 줄의 가공 작업도 클릭 단 몇 번으로 1분 만에 끝낼 수 있게 되었습니다.
잘 정돈된 깔끔한 데이터는 함수 작업과 데이터 분석의 출발점입니다. 칼퇴를 현실로 만들어 줄 데이터 가공 필수 기술 2가지와 주의사항을 알려드립니다.
1. 뭉쳐 있는 데이터를 깔끔하게 가르는 '텍스트 나누기'
시스템에서 추출한 데이터나 주소, 성명/직급 데이터는 하나의 셀에 콤마(,)나 띄어쓰기, 슬래시(/) 등으로 묶여 있는 경우가 많습니다. 이를 각각의 열로 깔끔하게 분리하는 방법입니다.
기호(구분 기호)로 분리하는 법:
분리할 데이터가 포함된 열 전체를 선택합니다.
상단 메뉴의 [데이터] -> [텍스트 나누기]를 클릭합니다.
'구분 기호로 분리됨'을 선택하고 [다음]을 누릅니다.
데이터 사이에 들어있는 기호(탭, 쉼표, 공백, 기타 기호 등)를 체크하면 하단 미리보기에서 데이터가 깔끔하게 갈라지는 것을 확인할 수 있습니다.
너비가 일정할 때 분리하는 법: 주민등록번호 앞자리나 사원번호처럼 일정한 글자 수로 나누어야 할 때는 '너비가 일정함'을 선택한 뒤, 원하는 위치에 클릭으로 구분선을 만들어 분리합니다.
실수를 막는 필수 팁: 텍스트 나누기를 실행하면 기존 셀의 오른쪽 열 데이터를 덮어쓰면서 나누어진 데이터가 들어갑니다. 따라서 반드시 작업 전에 오른쪽에 빈 열을 2~3개 이상 추가해 두고 작업을 진행해야 기존 데이터가 날아가는 비극을 막을 수 있습니다.
2. 수식 없이 한 번에 지우는 '중복된 항목 제거'
데이터를 수집하다 보면 동일한 주문 건이나 중복 등록된 회원 정보가 섞여 들어옵니다. 이를 눈으로 일일이 찾아 지우는 것은 시간 낭비일 뿐만 아니라 사람의 눈이라 오차가 발생하기 쉽습니다.
실행 순서:
정리할 데이터 표 내부의 셀을 하나 클릭합니다.
[데이터] -> [중복된 항목 제거]를 클릭합니다.
중복 여부를 판단할 기준 열을 선택하는 창이 팝업됩니다.
기준 열 설정의 중요성:
전체 열 선택: 이름, 연락처, 주소 등 모든 항목이 100% 똑같은 완전 중복 데이터만 지우고 싶을 때 사용합니다.
단일 열 선택 (예: 고객ID 또는 주문번호): 다른 정보는 조금 달라도 '주문번호'가 같으면 중복으로 간주해 1건만 남기고 지우고 싶을 때 해당 열만 체크합니다.
실행 결과 확인: 작업이 끝나면 엑셀이 "○○개의 중복된 값이 찾아 제거되었으며, ○○개의 고유한 값은 그대로 유지됩니다"라는 안내 문구를 보여주므로 최종 수량을 검증하기 매우 유용합니다.
3. 보이지 않는 유령 공간, '공백(Space)'제거하기
텍스트 나누기와 중복 제거를 했는데도 엑셀이 동일한 데이터로 인식하지 못하거나 VLOOKUP 함수에서 오류가 난다면, 십중팔구 글자 앞뒤에 보이지 않는 '공백'이 들어가 있기 때문입니다.
눈에 안 보이는 공백의 위험성:
"홍길동"과"홍길동 "은 눈으로 보기엔 똑같아 보이지만, 엑셀은 완전히 다른 텍스트로 인식합니다. 이 때문에 중복 제거가 적용되지 않거나 수식에서 찾지 못하는 문제가 발생합니다.TRIM 함수로 공백 깔끔하게 지우기:
=TRIM(A2)TRIM 함수는 텍스트의 앞뒤에 들어간 불필요한 공백을 한 번에 제거해 줍니다. 원본 데이터 옆에 TRIM 함수 열을 하나 만들어 데이터를 정돈한 뒤, '값 붙여넣기'로 원본을 교체해주면 데이터 깨끗함의 차원이 달라집니다.
4. 데이터 정돈 작업 전 반드시 챙겨야 할 안심 습관
데이터 가공 및 삭제 작업을 진행할 때는 원본 데이터 손상에 대비하는 습관이 무엇보다 중요합니다.
원본 시트 복사해두기: [데이터] 작업 메뉴는 실행 취소(
Ctrl + Z)가 완벽하게 적용되지 않거나 작업량이 많으면 이전 상태로 돌아가지 못할 수 있습니다. 작업 전 시트를 하나 더 복사(Ctrl + 마우스 드래그)하여 '원본_백업' 시트를 만들어두고 작업하는 것이 실무자의 안전장치입니다.
핵심 요약
뭉쳐 있는 데이터는 [데이터] -> [텍스트 나누기]를 활용하되, 우측 열이 덮어씌워지지 않도록 미리 빈 열을 만들어 둡니다.
중복 데이터는 [중복된 항목 제거] 기능으로 기준 열을 설정해 정확하고 빠르게 1건만 남겨 정리합니다.
데이터 정돈 후 원인 모를 수식 오류가 난다면 TRIM 함수를 활용해 글자 앞뒤의 숨은 공백을 제거해야 합니다.
다음 편 예고: 데이터를 깔끔하게 정돈했다면, 이제 수만 줄의 데이터를 클릭 몇 번으로 요약할 차례입니다. 다음 4편에서는 "피벗 테이블 초보 탈출: 클릭 몇 번으로 대용량 데이터를 한눈에 요약하기"에 대해 알아보겠습니다.
데이터를 정리하다가 글자 앞뒤 공백이나 원본이 덮어씌워져 당황했던 경험이 있으신가요? 댓글로 공유해 주세요!
0 댓글