데이터가 조금만 쌓여도 띄어쓰기·대소문자·특수문자 때문에 “같은 값인데 다르게 보이는” 문제가 터집니다. 이 글은 구글시트에서 텍스트를 먼저 ‘정규화’(깨끗하게)한 뒤, 상황별로 중복을 찾고 막는 방법을 한 번에 정리했습니다. 실무에 바로 쓰는 복붙용 포뮬러도 끝에 모아놨어요.
1) 먼저, 텍스트 “정규화” 5분 컷
중복 검출 전에 입력값을 깔끔하게 맞추면, 이후 작업이 빠르고 정확해집니다.
자주 쓰는 정리 포뮬러
- 불필요한 공백 제거
=TRIM(A2) → 앞뒤/이중 공백 제거
=REGEXREPLACE(A2,"\s+"," ") → 줄바꿈 포함 연속 공백을 하나로 - 숨은 특수공백 제거(CHAR(160) 등)
=REGEXREPLACE(A2,"[\u00A0]","") - 대소문자 통일
=LOWER(A2) / =UPPER(A2) / =PROPER(A2) - 특수문자 통일·삭제
하이픈 통일: =SUBSTITUTE(A2,"–","-")
숫자만 남기기(전화번호): =REGEXREPLACE(A2,"[^0-9]","")
한/영·숫자·공백만 남기기: =REGEXREPLACE(A2,"[^가-힣a-zA-Z0-9 ]","")
팁: 실무에선 정규화용 보조열(예: Z열)을 하나 두고, 이후 모든 비교·COUNTIF는 그 보조열 대상으로 처리하면 안정적입니다.
2) 텍스트 비교(포함/일치/패턴) 한방 정리
예) “FB 또는 FB-123”이 들어간 행 찾기
=REGEXMATCH(A2,"FB(\b|-)?[0-9]*")
3) 중복 “찾기” (표시/하이라이트/리스트)
A. 단일 열에서 중복 하이라이트
조건부 서식 → 사용자 지정 수식
범위: $A:$A
수식: =COUNTIF($A:$A,$A1)>1
- 첫 등장만 OK, 이후만 중복으로 칠할 때:
=COUNTIF($A$1:$A1,$A1)>1
B. 다중 열 조합(예: 이름+전화) 중복
보조열 없이:
=COUNTIFS($A:$A,$A2,$B:$B,$B2)>1
여러 열을 합쳐 키로 만들기(파이프 결합 권장):
보조열(예: Z2): =A2&"|"&B2&"|"&C2
다음 행에서 검사: =COUNTIF($Z:$Z,$Z2)>1
C. 공백/대소문자 무시한 중복
정규화된 범위를 만들어 COUNTIF:
D. 중복 목록만 따로 뽑기 (UNIQUE/FILTER)
- 고유값만: =UNIQUE(A2:A)
- 중복만:
-
php복사편집=UNIQUE( FILTER(A2:A, COUNTIF(A2:A, A2:A)>1) )
4) 중복 “제거” (메뉴/함수)
- 메뉴: 데이터 → 데이터 정리 → 중복 항목 제거
(헤더 포함 여부 확인!) - 함수로 동적 고유목록: =UNIQUE(A2:C)
- QUERY로 대표 행만 남기기 (예: 같은 ID에서 가장 최신 날짜):
-
makefile복사편집=QUERY(A2:C, "select A, max(C) where A is not null group by A label max(C) ''", 0)
5) “중복을 아예 못 쓰게” 막는 방법 (입력 단계 차단)
A. 데이터 유효성 검사로 차단
- 범위: A열
- 조건 → 사용자 지정 수식: =COUNTIF($A:$A, A1)=1
- 잘못된 데이터 처리: “입력 거부” 선택
→ 이미 존재하면 입력 자체가 막힙니다.
B. onEdit 스크립트로 경고·되돌리기(선택)
확장 프로그램 → 앱스 스크립트에 아래 삽입:
const sh = e.range.getSheet();
const col = e.range.getColumn();
// A열만 검사 (필요 시 시트명·범위 제한)
if (col !== 1 || !e.value) return;
const values = sh.getRange(2, 1, sh.getLastRow()-1, 1).getValues().flat();
const dupCount = values.filter(v => String(v).trim().toLowerCase() === String(e.value).trim().toLowerCase()).length;
if (dupCount > 1) {
e.range.setBackground('#ffe9e9').setNote('중복 값입니다.');
SpreadsheetApp.getUi().alert('이미 존재하는 값입니다.');
// 입력 되돌리기 (원복)
e.range.setValue(e.oldValue || '');
} else {
e.range.setBackground(null).setNote('');
}
}
팁: 큰 시트에서는 전체 열 대신 “A2:A10000”처럼 유효 범위를 제한하세요.
6) 실무 예제 3종
① 송장번호 중복 방지(대시/공백 무시)
보조열(Z2): =REGEXREPLACE(A2,"[^0-9]","")
조건부 서식(중복):
=COUNTIF($Z:$Z,$Z2)>1
유효성 검사(입력 차단):
=COUNTIF($Z:$Z, REGEXREPLACE(A1,"[^0-9]","") )=1
② 이름+휴대폰 조합 중복
보조열(Z2): =TRIM(LOWER(B2))&"|"®EXREPLACE(C2,"[^0-9]","")
조건부 서식: =COUNTIF($Z:$Z,$Z2)>1
고유명단: =UNIQUE(B2:C)
③ 키워드 포함 행만 별도 목록
“FB”가 들어간 행만 추출:
=FILTER(A2:D, REGEXMATCH(A2:A,"(^| )FB( |-|$)"))
7) 성능 팁 (느려질 때)
- 전열(A:A) 대신 **필요 범위(A2:A20000)**로 제한
- 무거운 REGEX는 보조열 1번만 계산하고 나머지는 COUNTIF로 재사용
- UNIQUE, FILTER, QUERY 중 하나의 파이프라인으로 처리해 중복 계산 줄이기
- 대용량은 시트 탭을 입력/정규화/리포트로 분리
8) 복붙용 포뮬러 모음
- 공백·대소문자 무시 중복:
-
sql복사편집=COUNTIF(ARRAYFORMULA(TRIM(LOWER($A$2:$A))), TRIM(LOWER($A2)))>1
- 다중 열 조합 중복(이름 B, 전화 C):
-
bash복사편집=COUNTIFS($B:$B,$B2,$C:$C,$C2)>1
- 중복만 추출:
-
php복사편집=UNIQUE(FILTER(A2:A, COUNTIF(A2:A, A2:A)>1))
- 전화번호 정규화:
-
arduino복사편집=REGEXREPLACE(C2,"[^0-9]","")
- 키워드 포함 행 필터:
-
php복사편집=FILTER(A2:D, REGEXMATCH(A2:A,"키워드1|키워드2"))
- 조건부 서식(단일 열 중복 하이라이트):
-
bash복사편집=COUNTIF($A:$A,$A1)>1
마무리
- 정규화 → 2) 조건부서식/COUNTIF → 3) UNIQUE/QUERY로 정리 → 4) 유효성 검사/스크립트로 차단
이 4단계를 루틴으로 잡아두면, 중복 이슈가 크게 줄어듭니다.
원하시면 여러 열 조합·특정 패턴 기준으로 당신의 시트 구조에 맞춘 포뮬러/스크립트로 바로 커스터마이징해드릴게요. 사용 중인 열 이름과 예시 몇 줄만 알려주세요!
댓글