5. 지저분한 데이터를 AI로 정리하고 표준화하기

현실 업무 상황 제시

공공기관에서 엑셀 파일을 받으면 이런 일이 자주 생깁니다.

• 같은 기관명이 조금씩 다르게 적혀 있습니다. 예: 서울시청, 서울특별시청, 서울 시청

• 날짜 형식이 섞여 있습니다. 예: 2026-03-01, 26.3.1, 2026/03/01

• 전화번호, 우편번호, 부서코드가 제각각입니다.

• 이름 앞뒤에 공백이 붙어 있거나, 셀 안에 줄바꿈이 숨어 있습니다.

• 한 칸에 여러 정보가 같이 들어 있습니다. 예: 복지정책과 / 김민수 / 02-123-4567

이 상태에서 바로 집계를 하거나 차트를 만들면 결과가 틀어집니다. 같은 기관인데 다른 기관으로 계산되거나, 날짜가 문자로 들어가 정렬이 꼬이기도 합니다.

엑셀 분석은 멋진 함수보다 먼저, 데이터 정리에서 승부가 납니다. 지저분한 데이터를 정리하는 일은 마치 회의 전에 책상을 치우는 일과 비슷합니다. 정리가 되면 일이 빨라지고, 실수도 줄어듭니다.

왜 필요한지 설명

AI를 쓰면 데이터 정리 작업이 훨씬 빨라집니다. 특히 초보자가 막히기 쉬운 부분에서 도움이 큽니다.

첫째, 정리 규칙을 빠르게 만들 수 있습니다. 무엇을 기준으로 통일할지 AI에게 설명하면 표준화 기준 초안을 바로 받을 수 있습니다.

둘째, 엑셀 함수나 변환 공식을 자동으로 만들 수 있습니다. TRIM, SUBSTITUTE, TEXTSPLIT, LEFT, RIGHT, MID, XLOOKUP 같은 함수를 직접 떠올리지 못해도 AI가 상황에 맞게 제안합니다.

셋째, 사람이 놓치기 쉬운 예외를 미리 점검할 수 있습니다. 예를 들어 주민센터, 행정복지센터, 동 주민센터처럼 비슷하지만 표현이 다른 항목을 어떻게 묶을지 AI와 함께 규칙을 잡을 수 있습니다.

이 장에서 배우는 내용은 다음 장의 집계, 분석, 시각화, 자동화의 바닥 공사입니다. 바닥이 고르면 건물이 잘 올라갑니다.

핵심 개념

데이터 정리는 크게 다섯 단계로 보면 쉽습니다.

1. 같은 값을 같게 만들기

표기만 다른 같은 대상을 하나의 표현으로 맞추는 단계입니다. 예를 들어 서울시, 서울특별시, 서울 특별시를 서울특별시로 통일합니다.

2. 불필요한 것을 제거하기

앞뒤 공백, 숨은 공백, 특수문자, 줄바꿈, 중복 행처럼 분석에 방해되는 요소를 치웁니다.

3. 형식을 맞추기

날짜는 날짜답게, 전화번호는 전화번호답게, 코드값은 자리수와 규칙에 맞게 통일합니다.

4. 한 셀의 정보를 나누기

한 셀에 이름, 부서, 연락처가 같이 들어 있으면 각각 다른 열로 분리해야 분석과 검색이 쉬워집니다.

5. 규칙표를 만들어 반복 가능하게 하기

한 번 손으로 정리하고 끝내면 다음 달에 다시 고생합니다. 어떤 값을 어떤 기준으로 바꾸는지 표로 남겨야 반복 작업이 쉬워집니다.

실습 1. 중복 제거와 공백 정리

목표

기관명과 담당자명에 섞여 있는 공백을 정리하고, 중복 행을 제거합니다.

실습 상황

아래와 같은 원본 데이터가 있다고 가정해 보겠습니다.

겉으로 보면 비슷하지만, 공백 차이 때문에 엑셀은 다른 값으로 볼 수 있습니다.

STEP BY STEP 실행

1. 원본 데이터는 그대로 둡니다. 작업용 시트에서 정리합니다.

2. 기관명_정리, 담당자_정리 열을 새로 만듭니다.

3. AI에게 공백 정리에 쓸 함수를 요청합니다.

4. 제안받은 함수를 새 열에 넣어 결과를 확인합니다.

5. 필요하면 SUBSTITUTE로 중간 공백까지 조정합니다.

6. 정리된 열 기준으로 데이터 > 중복된 항목 제거를 실행합니다.

복붙 가능한 프롬프트

엑셀에서 기관명과 담당자명 열의 공백을 정리하고 싶습니다.
상황:
- 앞뒤 공백 제거
- 셀 안에 연속 공백이 있으면 하나로 정리
- 줄바꿈 문자도 제거
- 초보자가 바로 복붙할 수 있게 함수만 제시

예시 데이터:
A2 = " 서울시청  "
B2 = "김민수  "
A3 = "서울 시청"

각 열에 넣을 엑셀 함수를 알려주세요.

결과 예시

AI가 보통 이런 식으로 제안합니다.

=TRIM(SUBSTITUTE(A2,CHAR(10)," "))

=TRIM(SUBSTITUTE(B2,CHAR(10)," "))

필요하면 기관명 안의 띄어쓰기까지 통일하는 보조 열을 하나 더 둘 수 있습니다.

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(10)," "))," ","")

이 수식은 서울 시청을 서울시청처럼 붙여 줍니다. 다만 모든 기관명에 무조건 적용하면 뜻이 달라질 수 있으니, 기관명 규칙을 확인한 뒤 사용해야 합니다.

실무 팁

공백 정리는 원본 위에 덮어쓰지 말고 새 열에서 먼저 검수하세요. 한 번에 지우면 무엇이 바뀌었는지 확인하기 어렵습니다.

실습 2. 날짜, 전화번호, 코드 형식 통일하기

목표

날짜 형식, 전화번호 형식, 부서코드 자리수를 일정하게 맞춥니다.

실습 상황

다음처럼 형식이 뒤섞인 경우입니다.

정렬을 하거나 시스템 업로드를 하려면 형식을 통일해야 합니다.

STEP BY STEP 실행

1. 접수일_표준, 연락처_표준, 부서코드_표준 열을 추가합니다.

2. 날짜는 먼저 실제 날짜값인지 확인합니다.

3. 문자 형태 날짜라면 AI에게 변환 공식을 요청합니다.

4. 전화번호는 숫자만 남긴 뒤 형식을 다시 붙이는 방식을 사용합니다.

5. 코드값은 자리수를 정한 뒤 TEXT 함수로 맞춥니다.

복붙 가능한 프롬프트

엑셀에서 형식이 제각각인 데이터를 표준화하려고 합니다.
목표:
- 날짜를 yyyy-mm-dd 형식으로 통일
- 전화번호를 알맞은 하이픈 형식으로 통일
- 부서코드를 3자리로 맞춤(예: 7 -> 007)

예시:
A2 = 2026-3-1
A3 = 26.03.01
A4 = 2026/03/01
B2 = 021234567
B3 = 02-1234-5678
B4 = 01012345678
C2 = 7
C3 = 07
C4 = 007

각 항목별로 바로 사용할 수 있는 엑셀 함수와 사용 설명을 짧게 알려주세요.

결과 예시

부서코드를 3자리로 맞추는 가장 쉬운 방법은 다음과 같습니다.

=TEXT(C2,"000")

날짜는 셀 상태에 따라 다릅니다. 이미 날짜값이라면 셀 서식만 yyyy-mm-dd로 바꾸면 됩니다. 문자인 경우에는 AI가 상황에 맞는 변환 공식을 제안해 줄 수 있습니다.

전화번호는 패턴이 여러 가지라 한 번에 끝나지 않을 수 있습니다. 이럴 때는 AI에게 "02 지역번호와 010 휴대전화 형식을 구분해 달라"고 추가로 요청하면 더 정확한 공식을 받을 수 있습니다.

예를 들어 숫자만 남긴 값이 11자리 휴대전화일 때는 이런 식의 공식을 쓸 수 있습니다.

=LEFT(B2,3)&"-"&MID(B2,4,4)&"-"&RIGHT(B2,4)

결과 예시 표

마지막 예시는 자리수가 애매하므로 자동 변환 뒤 사람이 확인해야 합니다. AI가 만능은 아니고, 애매한 값은 검수 목록으로 빼는 것이 안전합니다.

실습 3. 한 셀에 들어 있는 정보 나누기

목표

한 셀에 함께 들어 있는 부서, 이름, 전화번호를 각각 다른 열로 분리합니다.

실습 상황

다음처럼 한 셀에 여러 정보가 섞여 있다고 해보겠습니다.

이 상태로는 이름별 건수, 부서별 통계, 연락처 검색이 어렵습니다.

STEP BY STEP 실행

1. 분리 기준 문자를 찾습니다. 여기서는 /입니다.

2. AI에게 현재 엑셀 버전에 맞는 방법을 요청합니다.

3. Microsoft 365라면 TEXTSPLIT을 우선 사용합니다.

4. 구버전이라면 LEFT, MID, RIGHT 또는 텍스트 나누기 기능을 사용합니다.

5. 분리 후 각 열 이름을 부서, 담당자명, 연락처로 지정합니다.

복붙 가능한 프롬프트

엑셀 한 셀에 '부서 / 이름 / 전화번호' 형태로 들어 있는 데이터를 열 3개로 나누고 싶습니다.
예시:
A2 = "복지정책과 / 김민수 / 02-123-4567"
A3 = "민원여권과 / 이지연 / 010-2222-3333"

조건:
- Microsoft 365에서 쓸 수 있는 함수 우선
- 앞뒤 공백도 함께 정리
- 결과는 부서, 이름, 전화번호 열로 분리
- 함수 예시를 바로 복붙 가능하게 제공

결과 예시

Microsoft 365라면 다음처럼 사용할 수 있습니다.

=TEXTSPLIT(A2,"/")

다만 이렇게 하면 각 항목 앞뒤에 공백이 남을 수 있습니다. 그래서 AI는 보통 공백 정리까지 함께 제안합니다. 예를 들어 분리한 뒤 각 결과 열에서 TRIM을 한 번 더 적용하면 깔끔합니다.

분리 후 결과는 아래처럼 됩니다.

실무 팁

구분자가 항상 같은지 먼저 확인하세요. 어떤 행은 /, 어떤 행은 -, 어떤 행은 줄바꿈으로 나뉘어 있으면 한 번에 분리되지 않습니다. 이럴 때는 먼저 구분자를 하나로 통일한 뒤 나누는 것이 빠릅니다.

실습 4. 기관명과 사업명 표준화 규칙 만들기

목표

비슷하지만 제각각 적힌 기관명과 사업명을 표준 표현으로 통일합니다.

실습 상황

아래처럼 같은 의미인데 표기가 다를 수 있습니다.

이럴 때 무작정 함수를 쓰기보다, 먼저 표준화 사전을 만드는 것이 좋습니다.

STEP BY STEP 실행

1. 자주 반복되는 다른 표현을 모읍니다.

2. 표준화사전 시트를 하나 만듭니다.

3. 왼쪽에는 원본 표현, 오른쪽에는 표준 표현을 적습니다.

4. AI에게 사전 초안을 만들어 달라고 요청합니다.

5. 검토 후 확정한 사전을 기준으로 XLOOKUP 또는 VLOOKUP을 적용합니다.

복붙 가능한 프롬프트

공공기관 엑셀 데이터를 표준화하려고 합니다.
아래처럼 같은 의미인데 표현이 다른 값들을 표준 표현으로 묶고 싶습니다.

원본 예시:
- 서울시청
- 서울특별시청
- 서울 시청
- 부산시청
- 부산광역시청
- 청년 지원
- 청년지원
- 청년지원사업
- 노인 돌봄
- 노인돌봄

요청:
1) 원본값과 표준값으로 된 표준화 사전 초안을 만들어 주세요.
2) 엑셀에서 XLOOKUP으로 적용하는 예시 함수도 알려주세요.
3) 사람이 검토해야 할 주의사항을 3가지 이내로 짧게 적어주세요.

결과 예시

표준화 사전은 이런 식으로 만들 수 있습니다.

적용 함수 예시는 다음과 같습니다.

=XLOOKUP(A2,표준화사전!A:A,표준화사전!B:B,A2)

이 함수는 원본값이 사전에 있으면 표준값으로 바꾸고, 없으면 원래 값을 그대로 둡니다.

실무 팁

표준화 사전은 한 번 만들면 끝이 아닙니다. 새로운 표현이 들어오면 계속 추가해야 합니다. 그래서 표준화 작업은 함수보다 사전 관리가 핵심입니다.

실습 5. 이상값 찾기와 검수 목록 만들기

목표

자동 정리 후에도 사람이 다시 봐야 할 값을 골라냅니다.

실습 상황

AI와 함수로 대부분 정리했는데도 이런 값은 남을 수 있습니다.

• 날짜가 비정상적으로 짧거나 긴 값

• 전화번호 자리수가 맞지 않는 값

• 사전에 없는 새로운 기관명

• 코드 길이가 규칙과 다른 값

이런 값은 억지로 자동 변환하지 말고 검수 목록으로 빼는 것이 좋습니다.

STEP BY STEP 실행

1. 검수여부 열을 추가합니다.

2. 조건을 정합니다. 예를 들어 부서코드는 3자리, 전화번호 숫자는 9~11자리 등입니다.

3. AI에게 조건 검사용 수식을 요청합니다.

4. 검수필요로 표시된 행만 필터링합니다.

5. 사람 검토 후 사전 또는 규칙을 보완합니다.

복붙 가능한 프롬프트

엑셀 데이터 정리 후 사람이 다시 확인해야 할 값만 골라내고 싶습니다.
조건:
- 부서코드는 3자리여야 함
- 전화번호는 숫자만 기준으로 9~11자리여야 함
- 표준화 사전에 없는 기관명은 검수 필요

열 예시:
A열 기관명
B열 전화번호
C열 부서코드

'정상' 또는 '검수필요'를 반환하는 엑셀 수식을 만들어 주세요.
초보자가 이해할 수 있게 짧게 설명도 붙여주세요.

결과 예시

실제 수식은 데이터 구조에 따라 달라지지만, AI는 보통 조건을 조합한 IF, LEN, ISNA, XLOOKUP 형태의 공식을 제안합니다.

핵심은 자동 정리의 마지막 줄에 검수필요를 남기는 것입니다. 이 한 열이 있으면 실수가 크게 줄어듭니다.

실습 6. 한 번에 요청하는 전처리 프롬프트 만들기

목표

중복 제거, 공백 정리, 형식 통일, 텍스트 분리, 코드 표준화를 한 번에 요청하는 실무형 프롬프트를 만듭니다.

STEP BY STEP 실행

1. 현재 데이터의 열 이름과 문제점을 먼저 적습니다.

2. 무엇을 기준으로 표준화할지 적습니다.

3. 원하는 결과 열 이름을 구체적으로 적습니다.

4. 함수가 필요한지, 규칙표가 필요한지 함께 요청합니다.

5. 마지막에 검수 포인트도 알려달라고 덧붙입니다.

복붙 가능한 프롬프트

당신은 엑셀 데이터 정리 도우미입니다.
다음 표를 분석 가능한 형태로 정리하려고 합니다.

열 구성:
- 기관명
- 담당자정보
- 접수일
- 연락처
- 부서코드
- 사업명

현재 문제:
- 기관명 표기가 제각각임
- 담당자정보 한 셀에 부서/이름/전화번호가 함께 들어 있음
- 날짜 형식이 섞여 있음
- 연락처 형식이 다름
- 부서코드 자리수가 다름
- 공백, 줄바꿈, 중복 행이 있음

내 요청:
1) 정리 순서를 알려주세요.
2) 각 문제를 해결할 엑셀 함수 예시를 열별로 제시해주세요.
3) 표준화 사전이 필요한 항목을 구분해주세요.
4) 자동 처리 후 사람이 검토해야 할 항목도 알려주세요.
5) 결과 열 구조 예시도 표 형태로 보여주세요.

설명은 짧고, 복붙 가능한 함수 중심으로 답해주세요.

결과 예시

좋은 답변은 보통 아래 흐름으로 나옵니다.

1. 공백과 줄바꿈 정리

2. 담당자정보 분리

3. 날짜와 연락처 형식 통일

4. 코드 자리수 통일

5. 기관명·사업명 표준화 사전 적용

6. 중복 제거

7. 검수필요 행 표시

이 순서대로 하면 중간에 꼬일 일이 적습니다. 예를 들어 먼저 중복 제거를 해버리면, 공백만 다른 같은 행이 중복으로 잡히지 않을 수 있습니다.

실전 미니 프로젝트

목표

하나의 원본 표를 정리 가능한 분석용 표로 바꿉니다.

원본 예시

STEP BY STEP 실행

1. 새 열을 만들어 원본은 그대로 둡니다.

2. 기관명 공백을 정리합니다.

3. 담당자정보를 부서, 이름, 전화번호로 분리합니다.

4. 접수일 형식을 통일합니다.

5. 연락처 형식을 통일합니다.

6. 부서코드를 3자리로 맞춥니다.

7. 사업명을 표준화합니다.

8. 기관명과 사업명에 표준화 사전을 적용합니다.

9. 마지막으로 중복과 검수필요 행을 확인합니다.

복붙 가능한 프롬프트

아래 원본 데이터를 분석용 표로 바꾸고 싶습니다.

원본 열:
기관명 / 담당자정보 / 접수일 / 연락처 / 부서코드 / 사업명

원본 예시:
1) 서울 시청 / 복지정책과 / 김민수 / 02-123-4567 / 26.03.01 / 021234567 / 7 / 청년 지원
2) 서울시청 / 복지정책과 / 김민수 / 02-123-4567 / 2026/03/01 / 02-123-4567 / 07 / 청년지원사업
3) 부산시청 / 노인복지과 / 이지연 / 010-2222-3333 / 2026-3-2 / 01022223333 / 9 / 노인 돌봄

요청:
- 정리된 최종 열 구조를 제안해주세요.
- 각 열별 엑셀 함수 예시를 주세요.
- 기관명과 사업명 표준화 사전 초안도 만들어 주세요.
- 마지막에 검수포인트를 5개 이내로 정리해주세요.

결과 예시

정리 후 표는 아래처럼 바뀔 수 있습니다.

이제 이 데이터는 피벗 테이블, 조건 분석, 차트, 대시보드로 바로 이어질 준비가 된 상태입니다.

자주 하는 실수

원본 데이터를 바로 수정하는 실수

처음부터 원본에 덮어쓰면 되돌리기 어렵습니다. 원본, 정리본, 검수본을 나누는 습관이 중요합니다.

규칙 없이 AI에게 한 번에 맡기는 실수

AI가 그럴듯하게 정리해도 기준이 없으면 다음 파일에서 다시 흔들립니다. 무엇을 표준값으로 볼지 먼저 정해야 합니다.

애매한 값을 억지로 자동 변환하는 실수

특히 기관명, 부서명, 전화번호는 예외가 많습니다. 확신이 없는 값은 검수필요로 빼는 편이 더 안전합니다.

이 장의 핵심 정리

데이터 정리는 분석의 준비운동이 아니라 본 경기의 일부입니다. 공백 하나, 날짜 형식 하나, 기관명 표기 하나가 집계 결과를 바꿉니다.

이 장에서 기억할 것은 세 가지입니다.

• 먼저 정리 규칙을 정하고, 그다음 함수를 쓴다.

• 표준화 사전을 만들면 반복 업무가 쉬워진다.

• 자동 처리 뒤에는 반드시 검수 목록을 남긴다.

다음 장에서는 이렇게 정리된 데이터를 바탕으로 표, 집계, 조건 분석을 AI와 함께 자동화해 보겠습니다.