엑셀에서 데이터 자동 정리하는 방법 10가지 (초보자도 쉽게 따라하기)

엑셀에서 데이터를 자동으로 정리하는 방법을 찾고 계신가요? 그렇다면 잘 찾아오셨습니다. 실무에서 받은 데이터는 대부분 바로 분석하기 어려운 상태입니다. 이런 경우 몇 가지 단계를 거쳐 데이터를 깔끔하게 다듬어야 합니다. 이 글에서는 엑셀의 다양한 기능과 수식을 활용해 데이터를 자동으로 정리하는 10가지 방법을 단계별로 자세히 소개합니다.

엑셀에서 데이터를 자동으로 정리하는 10가지 방법

예시로 아래와 같이 이름, 생년월일, 직업, 급여 정보가 뒤섞여 있는 지저분한 데이터셋을 준비했습니다. 이 데이터를 활용해 엑셀에서 데이터를 자동으로 정리하는 방법을 하나씩 살펴보겠습니다.

1. 파워 쿼리(Power Query) 기능으로 데이터 정리하기

첫 번째 방법은 파워 쿼리(Power Query) 기능을 활용한 자동 데이터 정리입니다. 아래 단계를 따라 직접 해보세요.

단계:

• 먼저 셀 범위 B4:D10을 선택합니다.
• 그런 다음 데이터 탭으로 이동해 테이블/범위에서(From Table/Range)를 클릭합니다.

• 테이블 만들기(Create Table) 대화상자가 열리고 데이터 범위가 이미 선택되어 있습니다.
• 이후 확인(OK)을 누릅니다.

• 파워 쿼리 편집기(Power Query Editor)가 나타납니다.
• 첫 행을 머리글로 사용(Use First Row as Headers)을 클릭해 제목 행을 설정합니다.

• 텍스트 대소문자를 통일하려면 Name 열을 선택합니다.
• 변환(Transform) 탭 → 텍스트 열(Text Column) → 형식(Format) → 각 단어 첫 글자만 대문자로(Capitalize Each Word)를 차례로 클릭합니다.

• 다음으로 비어 있는 셀이 포함된 행을 제거하려면 아래 버튼을 클릭합니다.
• 이후 빈 항목 제거(Remove Empty)를 선택합니다.

• 닫기 및 로드(Close & Load)를 클릭하고 닫기 및 로드 대상(Close & Load To)을 선택합니다.

• 데이터 가져오기(Import Data) 대화상자가 열립니다.
• 새 워크시트(New worksheet) 옵션을 선택합니다.
• 마지막으로 확인(OK)을 클릭합니다.

• 이렇게 하면 파워 쿼리 편집기를 통해 데이터를 손쉽게 자동 정리할 수 있습니다.

2. 텍스트 나누기(Text to Columns) 기능 적용하기

예시 데이터셋에는 이름(Name)이라는 한 개의 열에 성과 이름이 함께 들어 있습니다. 텍스트 나누기 기능을 사용하면 데이터를 두 개의 열로 자동 분리할 수 있습니다.

단계는 다음과 같습니다.

단계:

• 먼저 셀 범위 B5:B10을 선택합니다.
• 데이터 탭 → 데이터 도구(Data Tools) → 텍스트 나누기(Text to Columns)를 순서대로 클릭합니다.

• 이후 다음(Next)을 클릭합니다.

• 구분 기호(Delimiters)로 세미콜론(Semicolon), 쉼표(Comma), 공백(Space)을 선택하고 기타(Others)에 @를 입력합니다.
• 그다음 다음(Next)을 누릅니다.

• 대상(Destination)에 셀 C5를 입력합니다.
• 마지막으로 마침(Finish)을 클릭합니다.

• 결과적으로 데이터가 이름(First Name)과 성(Last Name) 두 개의 열로 나뉘어진 것을 확인할 수 있습니다.

3. 빠른 채우기(Flash Fill) 기능 활용하기

이번에는 빠른 채우기(Flash Fill) 기능으로 데이터를 자동 정리하는 방법을 알아보겠습니다. 예시 데이터에는 불필요한 특수문자들이 섞여 있습니다. 아래 단계를 따라 이를 제거할 수 있습니다.

단계:

• 먼저 이름(First Name) 열에 Jack을 입력합니다.
• 그런 다음 데이터 탭 → 데이터 도구(Data Tools) → 빠른 채우기(Flash Fill)를 클릭합니다.

• 이제 입력한 패턴을 학습해 나머지 모든 특수문자가 자동으로 제거된 것을 확인할 수 있습니다.

4. SUBSTITUTE 함수로 불필요한 문자 제거하기

다음으로 SUBSTITUTE 함수를 사용해 원치 않는 특수문자를 제거하고 데이터를 자동으로 정리하는 방법을 알려드립니다.

아래 단계를 따라 직접 실행해 보세요.

단계:

• 먼저 셀 D5를 선택합니다.
• 그런 다음 아래 수식을 입력합니다.

SUBSTITUTE 함수에서 첫 번째 인수(text)로 셀 C5, 두 번째 인수(old_text)로 "@", 세 번째 인수(new_text)로 빈 값(" ")을 지정했습니다.

• 이후 Enter 키를 누르고 채우기 핸들(Fill Handle)을 아래로 드래그해 나머지 셀까지 수식을 자동으로 채웁니다(AutoFill).

• 마지막으로 SUBSTITUTE 함수가 D열의 불필요한 특수문자를 모두 제거한 결과를 확인할 수 있습니다.

5. 찾기 및 바꾸기(Find & Replace) 기능 적용하기

다섯 번째 방법은 엑셀의 찾기 및 바꾸기 기능을 활용한 데이터 정리입니다. 예시 데이터에는 "#"가 포함된 값 두 개와 "@", ";"가 포함된 값들이 있습니다. 이러한 문자를 공백으로 바꿔 정리해 보겠습니다.

단계:

• 먼저 셀 범위 B5:D10을 선택합니다.
• 홈 탭 → 편집(Editing) → 찾기 및 선택(Find & Select) → 바꾸기(Replace)를 순서대로 클릭합니다.

• 찾기 및 바꾸기 도구 상자가 열립니다.
• 찾을 내용 상자에 "#"를 입력하고 바꿀 내용 상자는 비워 둡니다.
• 이후 바꾸기(Replace)를 클릭합니다.

• 이제 "#" 기호가 제거된 것을 확인할 수 있습니다.

• 같은 방식으로 찾기 및 바꾸기 기능을 사용해 데이터셋의 "@"와 ";"도 제거할 수 있습니다.

6. 숫자 형식(Number Format) 변경하기

이번에는 숫자 형식(Number Format)을 변경해 데이터를 자동으로 정리하는 방법을 배워보겠습니다. 예시 데이터의 생년월일 중 일부는 날짜 형식으로 되어 있지 않습니다. 아래 단계를 따라 직접 정리해 보세요.

단계:

• 먼저 셀 범위 C5:C10을 선택합니다.
• 홈 탭 → 숫자 형식(Number Format) → 드롭다운 버튼을 클릭합니다.

• 이후 간단한 날짜(Short Date)를 선택합니다.

• 이렇게 하면 숫자 형식을 일관되게 변경해 데이터를 자동으로 정리할 수 있습니다.

7. 중복된 항목 제거(Remove Duplicates) 기능 사용하기

이번에는 중복된 항목 제거 기능을 사용해 데이터셋에서 중복 값을 삭제하겠습니다.

단계:

• 먼저 셀 범위 B4:D11을 선택합니다.
• 데이터 탭 → 데이터 도구(Data Tools) → 중복된 항목 제거(Remove Duplicates)를 순서대로 클릭합니다.

• 중복된 항목 제거 대화상자가 열리면 확인(OK)을 누릅니다.

• 중복 값 처리 결과를 알려주는 새로운 메시지 상자가 나타나면 다시 확인(OK)을 클릭합니다.

• 이제 중복된 항목 제거 기능이 데이터셋에서 중복 값을 깔끔하게 삭제했습니다.

8. 이동 옵션(Go To Special)으로 빈 셀 찾기

이번에는 이동 옵션(Go To Special) 기능을 활용해 데이터셋의 빈 셀을 찾아내고 정리하는 방법을 알아보겠습니다.

단계는 다음과 같습니다.

단계:

• 먼저 셀 범위 B4:D11을 선택합니다.
• 홈 탭 → 편집(Editing) → 찾기 및 선택(Find & Select) → 이동 옵션(Go To Special)을 순서대로 클릭합니다.

• 이동 옵션 대화상자가 나타나면 빈 셀(Blanks)을 선택합니다.
• 그다음 확인(OK)을 클릭합니다.

• 이제 데이터셋의 빈 셀들이 한꺼번에 선택됩니다.

• 선택된 셀의 서식을 원하는 대로 변경할 수 있습니다.
• 여기서는 홈 탭 → 채우기 색(Fill Color) → 빨강(Red)을 선택해 빈 셀을 강조했습니다.

• 이처럼 빈 셀을 한 번에 찾아내 데이터를 자동으로 정리할 수 있습니다.

9. 목록과 텍스트 매칭으로 필요 없는 행 찾기

보유한 데이터를 다른 목록과 대조해 확인해야 하는 경우가 있습니다. 아래 예시 화면을 보시면 왼쪽 데이터에서 퇴사한 회원을 찾아내야 하는 상황이며, 오른쪽에 퇴사자 목록이 준비되어 있습니다.

위 화면은 간단한 예시입니다. 데이터는 B5:D22 범위에 있으며, F열의 퇴사 회원(Resigned Members) 목록과 일치하는 행을 찾아내는 것이 목표입니다. 찾아낸 불필요한 행은 나중에 삭제하면 됩니다.

단계:

• 먼저 셀 D5를 선택합니다.
• 그런 다음 아래 수식을 입력합니다.

이 수식에서 COUNTIF 함수(굵은 부분)는 데이터 값(셀 C5)이 목록 값(범위 F5:F11)과 일치하면 1을 반환합니다. 이 값이 1 이상이면 IF 함수가 "Resigned"(퇴사)를 반환하고, 그렇지 않으면 아무것도 반환하지 않습니다.

• 이후 Enter 키를 누르고 채우기 핸들을 아래로 드래그해 나머지 셀에도 수식을 자동으로 채웁니다.

이 수식은 C열의 회원 번호(Member Num)가 퇴사 회원 목록에 있으면 "Resigned"를 표시하고, 없으면 빈 문자열을 반환합니다.

• D열을 기준으로 정렬하면 퇴사 회원 행들이 한곳에 모이므로 빠르게 삭제할 수 있습니다.

D열 기준으로 정렬하려면 셀 D5부터 D22까지 선택한 후,

• 홈 ⇒ 편집 ⇒ 정렬 및 필터 ⇒ Z→A 정렬을 차례로 클릭합니다.

• 정렬 경고(Sort Warning) 대화상자가 열립니다.
• 선택 영역 확장(Expand the selection)을 선택합니다.
• 이후 정렬(Sort)을 클릭합니다.

• 정렬 후 엑셀 시트는 아래 화면과 같이 정리됩니다.

이 기법은 다양한 유형의 목록 대조 작업에 응용할 수 있으니 참고하세요.

10. 맞춤법 검사(Spell Check)로 데이터 정리하기

마지막 방법은 맞춤법 검사 기능을 활용한 자동 데이터 정리입니다. 아래 단계를 따라 직접 실행해 보세요.

단계:

• 먼저 셀 범위 C5:C10을 선택합니다.
• 그런 다음 검토(Review) 탭으로 이동해 맞춤법 검사(Spelling)를 클릭합니다.

• 맞춤법 검사 대화상자가 열리면 올바른 단어인 Manager를 선택합니다.
• 이후 변경(Change)을 클릭합니다.

• 다음으로 Receptionist를 선택하고 변경(Change)을 클릭합니다.

• 이번에는 Clerk를 선택한 뒤 마찬가지로 변경(Change)을 누릅니다.

• 검사가 끝나면 Microsoft Excel 알림 상자가 나타납니다.
• 확인(OK)을 클릭하면 완료됩니다.

• 이처럼 맞춤법 검사 기능으로 철자 오류를 손쉽게 수정해 데이터를 정리할 수 있습니다.

연습 파일

이 글에서 소개한 내용을 직접 연습해 볼 수 있도록 아래 이미지와 같은 엑셀 연습 워크북을 함께 준비했습니다. 다운로드하여 스스로 따라 해 보세요.

마치며

지금까지 엑셀에서 데이터를 자동으로 정리하는 10가지 방법을 살펴보았습니다. 이 글이 여러분의 업무에 도움이 되었기를 바랍니다. 이해하기 어려운 부분이 있다면 댓글로 남겨주세요. 또한 소개하지 못한 다른 유용한 방법이 있다면 알려주시기 바랍니다. 이와 같은 유용한 엑셀 팁은 ExcelDemy에서 계속 만나보실 수 있습니다. 감사합니다!