엑셀이나 구글시트로 거래처 목록, 상품명, 이메일 주소, 신청자 명단처럼 데이터가 많은 표를 관리하다 보면 같은 값이 두 번 이상 들어가는 경우가 있습니다.
중복된 값을 그대로 두면 사람 수나 상품 수를 잘못 세거나, 같은 거래처나 고객을 두 번 처리하는 문제가 생길 수 있습니다. 데이터를 정리할 때는 중복값이 있는지 먼저 찾고, 필요한 경우 표시하거나 제거하는 과정이 필요합니다.
이번 글에서는 엑셀과 구글시트에서 중복값을 찾는 방법, 색으로 표시하는 방법, 중복 항목을 제거하는 방법을 차례대로 알아보고, COUNTIF 함수를 이용해 중복 여부를 자동으로 표시하는 방법까지 살펴보겠습니다.
중복값은 어떤 경우에 생길까?
중복값은 같은 내용이 두 번 이상 입력된 데이터를 말합니다.
예를 들어 거래처명이 아래처럼 입력되어 있다고 해보겠습니다.
- 가나다상사
- 한빛유통
- 미래상사
- 가나다상사
- 대성유통
여기에서는 가나다상사가 두 번 입력되어 있으므로 중복값입니다.
실제 업무에서는 거래처명뿐 아니라 이메일 주소, 전화번호, 상품코드, 주문번호처럼 같은 값이 반복되면 확인이 필요한 항목에서 중복 여부를 찾는 경우가 많습니다.
엑셀에서 중복값 색으로 표시하기
엑셀에서는 조건부서식을 이용하면 데이터를 삭제하지 않고 중복된 값만 먼저 색으로 표시할 수 있습니다.
예를 들어 A2:A10 범위에 거래처명이 입력되어 있다면 다음 순서로 확인합니다.
- 중복 여부를 확인할 셀 범위를 선택합니다.
- 홈 메뉴에서 조건부 서식을 선택합니다.
- 셀 강조 규칙에서 중복 값을 선택합니다.
- 표시할 색을 선택한 뒤 확인합니다.
같은 값이 두 번 이상 있는 셀에 자동으로 색이 표시됩니다.
중복 데이터를 바로 지우지 않고 어떤 값이 반복되어 있는지 먼저 확인할 때 사용하기 좋은 방법입니다.
구글시트에서 중복값 표시하기
구글시트에서는 조건부서식에 COUNTIF 수식을 넣어 중복값을 색으로 표시할 수 있습니다.
A2:A10에 거래처명이 입력되어 있다면 해당 범위를 선택한 뒤 서식 → 조건부 서식으로 들어갑니다.
조건 형식에서 맞춤 수식을 선택하고 아래 수식을 입력합니다.
=COUNTIF($A$2:$A$10,A2)>1이 수식은 A2:A10 범위 안에서 현재 셀과 같은 값이 몇 번 나오는지 확인합니다. 같은 값이 두 번 이상 있으면 조건에 해당하므로 지정한 색으로 표시됩니다.
예를 들어 가나다상사가 A2와 A5에 입력되어 있다면 두 셀 모두 중복값으로 표시됩니다. 이 방법은 데이터를 지우기 전에 반복된 항목이 어디에 있는지 먼저 확인할 때 유용합니다.
중복된 위치만 확인하면 충분한 경우도 있지만, 표를 계속 관리해야 한다면 별도의 열에 중복 여부를 표시해 두는 방식이 더 편할 수 있습니다.
COUNTIF로 중복 여부를 직접 표시하기
중복된 셀의 색을 바꾸는 대신 별도의 열에 중복이라는 문자를 표시할 수도 있습니다.
A열에 거래처명이 있고 B열에 중복 여부를 표시한다고 가정하면 B2에는 아래 수식을 입력합니다.
=IF(COUNTIF($A$2:$A$10,A2)>1,"중복","")A2의 값이 지정한 범위 안에서 두 번 이상 발견되면 '중복'이라고 표시하고, 한 번만 있으면 빈칸으로 둡니다.
수식을 아래 행까지 복사하면 각 거래처의 중복 여부를 자동으로 확인할 수 있습니다.
IF 함수의 기본 문법이나 여러 조건을 처리하는 방법이 익숙하지 않다면 구글시트·엑셀 IF 함수 사용법｜AND·OR 조건 실무 예제 에서 함께 확인할 수 있습니다.
첫 번째 값은 남기고 두 번째부터 중복 표시하기
앞의 수식은 같은 값이 두 번 있다면 첫 번째 값과 두 번째 값 모두 '중복'으로 표시합니다.
처음 입력된 값은 그대로 두고 두 번째로 등장하는 값부터 중복이라고 표시하고 싶다면 COUNTIF의 범위를 현재 행까지 늘어나도록 설정합니다.
=IF(COUNTIF($A$2:A2,A2)>1,"중복","")A2에서는 A2까지만 확인하고, A3에서는 A2:A3, A4에서는 A2:A4를 확인합니다.
따라서 같은 값이 처음 나타났을 때는 아무것도 표시되지 않고, 같은 값이 다시 등장하는 행부터 '중복'이라고 표시됩니다.
엑셀에서 중복값 제거하기
중복된 항목이 필요 없는 데이터라면 엑셀의 중복된 항목 제거 기능을 사용할 수 있습니다.
- 중복을 제거할 표나 셀 범위를 선택합니다.
- 데이터 메뉴에서 중복된 항목 제거를 선택합니다.
- 중복 여부를 판단할 열을 선택합니다.
- 확인을 눌러 중복 항목을 제거합니다.
예를 들어 거래처명 열만 선택하면 거래처명이 같은 값을 기준으로 중복 여부를 판단합니다.
여러 열을 함께 선택하면 선택한 열들의 값이 모두 같은 행을 중복으로 판단합니다.
구글시트에서 중복값 제거하기
구글시트에서도 메뉴를 이용해 중복 데이터를 삭제할 수 있습니다.
- 중복을 제거할 범위를 선택합니다.
- 데이터 메뉴를 선택합니다.
- 데이터 정리 → 중복 항목 삭제로 들어갑니다.
- 중복 여부를 확인할 열을 선택합니다.
- 중복 항목 삭제를 실행합니다.
중복 제거를 실행하면 처음 입력된 값은 남고 이후 반복되는 데이터가 제거됩니다. 삭제된 항목은 되돌려 확인하기 어려울 수 있으므로 제거할 열과 범위를 다시 확인하는 편이 좋습니다.
특히 여러 열이 있는 표에서는 어느 열을 기준으로 중복을 판단했는지에 따라 삭제되는 행이 달라질 수 있습니다. 같은 거래처명이라도 다른 정보가 함께 입력되어 있다면 한 행 전체를 비교해서 봐야 합니다.
한 열 기준과 여러 열 기준은 무엇이 다를까?
중복 제거에서 가장 주의해야 하는 부분은 어떤 열을 기준으로 같은 데이터라고 판단할지입니다.
예를 들어 아래처럼 데이터가 있다고 해보겠습니다.
| 거래처명 | 담당자 | 전화번호 |
|---|---|---|
| 가나다상사 | 김민수 | 010-1111-1111 |
| 가나다상사 | 이서연 | 010-2222-2222 |
거래처명만 기준으로 보면 두 행은 같은 값입니다. 하지만 담당자와 전화번호까지 함께 보면 서로 다른 데이터입니다.
회사명이나 고객명처럼 같은 값이 여러 번 나올 수 있는 항목은 무조건 삭제하기보다 어떤 항목까지 같아야 실제 중복으로 볼 것인지 먼저 확인해야 합니다.
공백 때문에 다른 값으로 인식될 수도 있습니다
눈으로 보기에는 같은 값인데 중복으로 표시되지 않는 경우도 있습니다.
예를 들어 아래 두 값은 화면에서는 같아 보일 수 있습니다.
가나다상사
가나다상사 두 번째 값의 뒤에 보이지 않는 공백이 들어 있다면 엑셀이나 구글시트에서는 서로 다른 값으로 인식할 수 있습니다.
이럴 때는 TRIM 함수를 이용해 불필요한 앞뒤 공백을 제거할 수 있습니다.
=TRIM(A2)다른 프로그램이나 웹페이지에서 데이터를 복사해 붙여넣은 뒤 중복값이 제대로 잡히지 않는다면 공백이 들어가 있는지도 함께 확인해 볼 수 있습니다.
중복값을 삭제하기 전에 확인할 것
같은 값이 반복되어 있다고 해서 항상 삭제해야 하는 것은 아닙니다.
예를 들어 같은 고객이 여러 번 주문했다면 고객명은 반복되어도 각각 다른 주문 기록입니다. 거래처 역시 한 회사에 담당자가 여러 명이라면 회사명이 여러 번 나올 수 있습니다.
직원명이나 날짜처럼 원래 반복될 수 있는 값도 있습니다. 구글시트·엑셀 직원 근태관리 양식 처럼 한 직원의 기록이 날짜별로 여러 행에 입력되는 표에서는 이름이 같다는 이유만으로 중복 데이터를 삭제하면 안 됩니다.
중복 제거를 실행하기 전에는 중복을 판단할 기준이 무엇인지 먼저 정해야 합니다. 같은 값이 여러 번 있다는 사실과 실제로 삭제해도 되는 중복 데이터라는 것은 서로 다를 수 있습니다.
- 중복을 판단할 기준 열이 맞는지
- 같은 값이 실제로 삭제해도 되는 데이터인지
- 한 열이 아니라 여러 열을 함께 비교해야 하는지
- 삭제 전 원본 데이터가 필요한지
중요한 데이터라면 중복 제거 전에 원본 시트를 복사해 두세요.
중복 제거는 실제 행을 삭제하는 작업이므로, 거래내역이나 고객목록처럼 다시 확인해야 할 가능성이 있는 자료는 원본을 남겨두는 편이 안전합니다.
중복 항목을 삭제하기 전에 조건부서식이나 COUNTIF로 먼저 표시해 보고 실제 데이터와 비교하면 필요한 행까지 지우는 실수를 줄일 수 있습니다.
중복값을 찾는 목적에 따라 조건부서식, COUNTIF 수식, 중복 제거 기능 가운데 필요한 방법만 골라 사용하면 됩니다. 마지막으로 각각의 차이를 다시 정리해 보겠습니다.
중복값 찾기에서 기억할 방법
- 엑셀에서 빠르게 확인 : 조건부서식의 중복 값 기능
- 구글시트에서 색으로 표시 :
=COUNTIF($A$2:$A$10,A2)>1 - 중복이라고 문자 표시 : COUNTIF와 IF 함수 사용
- 두 번째 값부터 중복 표시 :
=COUNTIF($A$2:A2,A2)>1 - 실제 중복 데이터 삭제 : 데이터 메뉴의 중복 제거 기능
데이터가 많을수록 중복 항목을 바로 삭제하기보다 조건부서식이나 COUNTIF로 어떤 값이 반복되어 있는지 먼저 확인한 뒤 필요한 항목만 정리하는 편이 안전합니다.
엑셀과 구글시트 가운데 어떤 프로그램을 사용할지 고민되거나 두 프로그램의 기능 차이가 궁금하다면 엑셀 vs 구글시트 차이 비교 도 함께 확인할 수 있습니다.
