지저분한 데이터 정리 규칙 만드는 프롬프트

1. 샘플에서 확인된 문제

2. 실행 순서

0. 원본 보존

원본 시트를 복사하고, 작업용 시트에서 진행합니다.

이후 원본 열은 보존하고 정제 고객명, 정제 전화번호, 정제 가입일, 정제 이메일 같은 보조 열을 만드는 것이 안전합니다.

1. 앞뒤 공백 제거 — 수식

고객명에 다음과 같은 수식을 사용합니다.

=TRIM(A2)

TRIM은 앞뒤 공백을 제거하고, 연속된 일반 공백도 정리합니다.

전화번호와 이메일에도 우선 TRIM을 적용합니다. 공백 제거를 나중에 하면 중복 판정이나 빈칸 판정이 잘못될 수 있습니다.

2. 전화번호 표준화 — 수식

전화번호의 하이픈, 마침표, 공백을 제거한 뒤 목표 형식으로 표시합니다.

=LET(
 x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"-",""),".","")," ",""),
 IF(AND(LEN(x)=11,LEFT(x,3)="010"),
    TEXT(--x,"000-0000-0000"),
    "검토 필요"
 )
)

결과 예시:

• 010-1234-5678 → 010-1234-5678
• 01098765432 → 010-9876-5432
• 010.1111.2222 → 010-1111-2222

전화번호는 숫자로 저장하면 앞의 0이 사라질 수 있으므로, 최종적으로는 텍스트로 보존하는 것이 안전합니다.

3. 가입일 표준화 — 날짜 변환 기능 또는 수식

날짜는 찾기 및 바꾸기만으로 처리하면 위험합니다. 예를 들어 25.1.15를 문자열로 바꾸는 것과 실제 날짜 값으로 변환하는 것은 다릅니다.

먼저 날짜 열의 값들을 날짜 형식으로 변환한 뒤, 셀 표시 형식을 다음으로 지정합니다.

yyyy-mm-dd

스프레드시트의 날짜 변환 기능을 사용하거나, 원본 형식이 명확하다는 전제에서 수식으로 변환합니다.

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(C2,".","-"),"/","-"))

그 다음 셀 표시 형식을 yyyy-mm-dd로 지정합니다.

25.1.15를 2025-01-15로 해석하는 규칙이 확실해야 합니다. 연도 25의 세기를 자동 추정하게 두면 다른 데이터에서는 잘못된 날짜가 될 수 있습니다.

4. 빈 이메일 표시 — 수식

공백 제거 후 이메일이 비어 있으면 미기재로 표시합니다.

=IF(TRIM(D2)="","미기재",TRIM(D2))

이 단계는 실제 이메일 주소와 빈 값을 구분하기 위한 것이므로, 빈칸을 임의의 이메일 주소로 채우는 것과는 다릅니다.

5. 중복 행 식별 — 중복 제거 기능 또는 보조 수식

먼저 중복 여부를 표시합니다.

=COUNTIFS(정제고객명열,정제고객명셀,
          정제전화번호열,정제전화번호셀)

결과가 2 이상이면 중복 후보입니다.

그 후 이름과 전화번호 등 신뢰할 수 있는 기준으로 중복 제거 기능을 사용합니다. 이메일은 빈 값이 미기재로 바뀌므로, 중복 판정의 핵심 기준으로 사용하기 전에 주의해야 합니다.

중복 행 삭제는 [copy the original first]입니다.

중복 제거 전에 전화번호와 고객명의 공백·표기를 먼저 통일해야 합니다. 순서를 바꾸면 같은 고객이 서로 다른 문자열로 남아 중복으로 잡히지 않을 수 있습니다.

6. 최종 열로 확정

정제 결과를 검토한 뒤 필요하면 보조 열을 값으로 붙여넣어 최종 열로 확정합니다.

수식을 값으로 변환하는 작업은 되돌리기 어려우므로 [copy the original first]입니다.

3. 유형별 도구 선택

찾기 및 바꾸기는 전화번호의 단순한 기호 제거에는 사용할 수 있지만, 전체 표준화 방법으로는 권장하지 않습니다. 날짜나 전화번호의 유효성을 검증하지 못하기 때문입니다.

4. 자동으로 확정하지 말고 사람의 판단이 필요한 것

• 동일 고객의 두 행에 전화번호나 가입일이 서로 다르면 어느 값을 남길지 결정해야 합니다.
• 25.1.15 같은 두 자리 연도의 세기를 결정해야 합니다. 이 샘플에서는 2025로 볼 수 있지만, 일반 규칙을 정해야 합니다.
• 이름과 전화번호가 같은 두 행이 정말 중복인지, 가족·공용 전화번호인지 확인해야 합니다.
• 중복 행에서 이메일처럼 한쪽만 값이 있는 경우, 값을 합칠지 한 행을 삭제할지 결정해야 합니다.
• 전화번호가 010으로 시작하지 않거나 자릿수가 맞지 않아 검토 필요가 된 값은 원본 확인이 필요합니다.

5. 정제 후 확인할 것

• 원본 행 수와 정제 후 행 수를 비교합니다.
  • 감소한 행 수가 실제 중복 제거 행 수와 일치해야 합니다.
• 원본 고객명·전화번호를 기준으로 정제 결과에 대응 행이 모두 있는지 확인합니다.
• 전화번호가 모두 010-0000-0000 패턴인지 확인합니다.
• 가입일이 모두 실제 날짜이며 YYYY-MM-DD로 표시되는지 확인합니다.
• 이메일 빈칸이 의도한 대로 모두 미기재가 되었는지 확인합니다.
• 정제 후에도 동일 고객 후보가 남아 있는지 다시 중복 검사합니다.
• 검토 필요, 빈 문자열, 오류값이 남아 있지 않은지 필터링합니다.
• 집계용 고객 수가 “원본 행 수 − 실제 삭제한 중복 행 수”와 일치하는지 확인합니다.