구글 스프레드시트로 학생 명단이나 고객 목록, 연락처, 매출 자료 등을 관리하다 보면 동일한 데이터가 여러 번 입력되는 경우가 있습니다. 처음에는 데이터가 많지 않아 쉽게 확인할 수 있지만, 자료가 수백 개 이상 쌓이면 중복된 항목을 일일이 찾아내기가 쉽지 않습니다.
특히 여러 사람이 하나의 스프레드시트를 함께 사용하거나 여러 파일의 데이터를 취합하는 경우에는 같은 이름이나 연락처가 반복해서 입력될 가능성이 높습니다. 중복된 데이터가 많아지면 전체 인원이나 합계가 실제보다 크게 집계되는 등 자료의 정확성에도 영향을 줄 수 있습니다.
이럴 때 구글 스프레드시트의 중복 데이터 확인 및 제거 기능을 활용하면 편리합니다. 별도의 프로그램을 설치하지 않아도 조건부 서식과 기본 메뉴, 간단한 함수만으로 중복값을 찾아 정리할 수 있습니다.
오늘은 구글 스프레드시트에서 중복 데이터를 찾는 방법부터 중복 항목을 한 번에 삭제하는 방법, 그리고 UNIQUE 함수를 활용해 중복 없는 목록을 자동으로 만드는 방법까지 알아보겠습니다.
조건부 서식으로 중복 데이터 찾는 방법
중복 데이터를 정리하기 전에 먼저 어떤 항목이 중복되었는지 확인하는 것이 좋습니다. 이때 유용하게 사용할 수 있는 기능이 바로 조건부 서식입니다.
조건부 서식은 셀에 입력된 값이 특정 조건에 해당하면 배경색이나 글자색을 자동으로 변경해 주는 기능입니다. 중복된 이름이나 연락처를 색상으로 표시하면 많은 데이터 중에서도 중복 항목을 쉽게 구분할 수 있습니다.
① COUNTIF 함수로 중복값 찾기
예를 들어 A열에 학생 이름이 입력되어 있다고 가정해 보겠습니다. 같은 이름이 두 번 이상 입력되었는지 확인하려면 COUNTIF 함수를 활용할 수 있습니다.
COUNTIF는 지정한 범위에서 특정 조건에 해당하는 셀의 개수를 세어주는 함수입니다.
중복 여부를 확인하는 기본 수식은 다음과 같습니다.
=COUNTIF($A$2:$A,A2)>1
이 수식은 A2부터 A열의 마지막 데이터까지 확인해 현재 A2 셀과 같은 값이 몇 번 입력되어 있는지 계산합니다. 같은 값이 2개 이상이면 TRUE가 되어 중복 데이터로 판단합니다.
② 조건부 서식으로 중복값 색상 표시하기
위 수식을 조건부 서식에 적용하면 중복된 셀을 자동으로 색칠할 수 있습니다.
설정 방법은 다음과 같습니다.
중복 여부를 확인할 범위를 선택합니다. 예를 들어 A2을 선택합니다.
상단 메뉴에서 '서식'을 클릭합니다.
'조건부 서식'을 선택합니다.
오른쪽에 나타나는 규칙 설정에서 '형식 규칙'을 선택합니다.
'맞춤 수식'을 선택합니다.
다음 수식을 입력합니다.
=COUNTIF($A$2:$A,$A2)>1
원하는 배경색이나 글자색을 지정합니다.
'완료'를 클릭합니다.
이제 A열에서 같은 이름이 두 번 이상 입력된 셀은 지정한 색상으로 표시됩니다.
중복된 값이 여러 개라면 해당 값이 입력된 셀 전체가 색칠되므로 어떤 항목을 정리해야 하는지 한눈에 확인할 수 있습니다.
③ 여러 열을 기준으로 중복값 찾기
중복 여부를 판단할 때 반드시 한 개의 열만 기준으로 삼아야 하는 것은 아닙니다.
예를 들어 학생 명단에 이름과 학교명이 함께 입력되어 있다면 이름만으로 중복을 판단해서는 안 됩니다. 이름이 같더라도 학교가 다르면 서로 다른 학생일 수 있기 때문입니다.
이럴 때는 COUNTIFS 함수를 활용하면 됩니다.
=COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1
위 수식은 A열의 이름과 B열의 학교명이 모두 동일한 데이터가 2개 이상 있는지 확인합니다.
이름과 연락처, 상품명과 상품번호처럼 여러 항목을 함께 비교해야 할 때 유용합니다.
구글 스프레드시트에서 중복 데이터 한 번에 제거하기
중복된 데이터를 확인했다면 이제 불필요한 항목을 삭제할 차례입니다.
구글 스프레드시트에는 별도의 수식을 입력하지 않아도 중복 항목을 자동으로 찾아 제거하는 기능이 있습니다. 데이터가 많을 때 일일이 삭제할 필요 없이 몇 번의 클릭만으로 정리할 수 있습니다.
① 중복 항목 삭제 기능 사용하기
예를 들어 A열에는 학생 이름, B열에는 학교명, C열에는 연락처가 입력되어 있다고 가정하겠습니다.
전체 항목을 기준으로 완전히 동일한 행을 제거하려면 다음 순서로 진행하면 됩니다.
중복을 제거할 데이터 범위를 선택합니다.
상단 메뉴에서 '데이터'를 클릭합니다.
'데이터 정리'를 선택합니다.
'중복 항목 삭제'를 클릭합니다.
첫 번째 행이 제목이라면 '헤더가 포함된 데이터' 항목을 선택합니다.
중복 여부를 판단할 열을 선택합니다.
'중복 항목 삭제'를 클릭합니다.
작업이 완료되면 중복된 데이터가 몇 개 제거되었는지 결과를 확인할 수 있습니다.
② 특정 열을 기준으로 중복 제거하기
중복 데이터 제거 기능의 중요한 특징은 어떤 열을 기준으로 중복을 판단할지 직접 선택할 수 있다는 점입니다.
예를 들어 학생 명단에서 이름을 기준으로 중복 데이터를 제거하고 싶다면 A열만 선택하면 됩니다.
반면 이름과 학교명이 모두 같은 경우에만 중복으로 판단하고 싶다면 A열과 B열을 함께 선택해야 합니다.
다음과 같은 데이터가 있다고 가정해 보겠습니다.
이름
학교명
김민수
A고등학교
이지은
B고등학교
김민수
A고등학교
김민수
C고등학교
이름만 기준으로 중복 제거를 실행하면 김민수라는 이름이 한 번만 남습니다. 하지만 이름과 학교명을 함께 기준으로 설정하면 A고등학교의 김민수만 중복으로 제거되고 C고등학교의 김민수는 그대로 남습니다.
따라서 중복 제거를 실행하기 전에는 어떤 항목을 기준으로 동일한 데이터라고 판단할 것인지 먼저 결정해야 합니다.
③ 중복 데이터 삭제 전 주의할 점
중복 항목 삭제 기능은 실제 데이터를 삭제하는 작업이므로 실행 전에 반드시 확인해야 할 사항이 있습니다.
첫째, 원본 데이터를 복사해 별도의 시트에 보관하는 것이 좋습니다. 중복 제거 과정에서 필요한 데이터까지 삭제했다면 원본을 다시 확인해야 하기 때문입니다.
둘째, 데이터 범위를 정확하게 선택해야 합니다. 일부 열만 선택한 상태에서 중복을 제거하면 다른 열의 정보와 연결 관계가 어긋날 수 있습니다.
셋째, 같은 이름이라고 해서 무조건 중복 데이터는 아닙니다. 이름이 같은 사람도 있을 수 있고, 상품명이 같더라도 상품번호가 다를 수 있습니다.
중복 제거 기능을 사용하기 전에는 데이터의 특성을 파악하고, 이름·연락처·고유번호 등 적절한 기준을 선택하는 것이 중요합니다.
UNIQUE 함수로 중복 없는 데이터 자동으로 만들기
중복 데이터를 삭제하지 않고 별도의 영역에 중복이 제거된 목록을 만들고 싶다면 UNIQUE 함수를 활용하면 됩니다.
UNIQUE 함수는 지정한 범위에서 중복된 항목을 제외하고 고유한 값만 추출해 주는 함수입니다. 원본 데이터는 그대로 유지하면서 중복 없는 목록을 만들 수 있다는 장점이 있습니다.
① UNIQUE 함수 기본 사용법
예를 들어 A2부터 A100까지 학생 이름이 입력되어 있다면 다음과 같이 입력합니다.
=UNIQUE(A2:A100)
수식을 입력한 셀 아래쪽으로 중복되지 않은 이름 목록이 자동으로 표시됩니다.
원본 데이터에 같은 이름이 여러 번 입력되어 있더라도 결과에는 한 번만 나타납니다.
이름뿐만 아니라 학교명, 상품명, 지역명 등 다양한 데이터에 활용할 수 있습니다.
② 중복 제거된 데이터를 가나다순으로 정렬하기
UNIQUE 함수와 SORT 함수를 함께 사용하면 중복을 제거하면서 가나다순으로 정렬할 수 있습니다.
=SORT(UNIQUE(A2:A100))
위 수식은 A2부터 A100까지의 데이터에서 중복 항목을 제거한 뒤 가나다순으로 정렬해 보여줍니다.
숫자 데이터라면 숫자 크기순으로 정렬할 수 있어 명단이나 상품 목록을 정리할 때 편리합니다.
③ 여러 열의 중복 데이터 정리하기
UNIQUE 함수는 여러 열을 함께 지정할 수도 있습니다.
예를 들어 A열에는 학생 이름, B열에는 학교명이 입력되어 있다면 다음과 같이 작성합니다.
=UNIQUE(A2:B100)
이 수식은 이름과 학교명이 모두 동일한 행을 중복으로 판단해 한 번만 표시합니다.
이름이 같더라도 학교명이 다르면 서로 다른 데이터로 인식합니다.
UNIQUE 함수의 또 다른 장점은 원본 데이터가 변경되면 결과 목록도 자동으로 갱신된다는 점입니다. 새로운 데이터를 추가하거나 기존 데이터를 수정하면 중복이 제거된 목록에도 변경 사항이 반영됩니다.
다만 함수 결과가 표시될 영역에 다른 데이터가 있으면 결과가 펼쳐지지 않을 수 있으므로 수식을 입력하기 전에 아래쪽 셀을 비워두는 것이 좋습니다.
또한 UNIQUE 함수는 원본 데이터를 실제로 삭제하는 기능이 아니라 중복 없는 결과 목록을 별도로 보여주는 기능이라는 점도 기억해 두세요.
구글 스프레드시트에서 중복 데이터를 찾고 정리하는 방법은 생각보다 간단합니다.
중복된 데이터를 눈으로 확인하고 싶다면 조건부 서식을 활용하고, 원본에서 불필요한 항목을 실제로 삭제하려면 중복 항목 삭제 기능을 사용하면 됩니다. 원본 데이터는 유지하면서 중복 없는 목록을 별도로 만들고 싶다면 UNIQUE 함수를 활용하는 것이 좋습니다.
특히 여러 사람이 함께 사용하는 학생 명단이나 고객 관리표, 업무 자료에서는 중복 데이터가 쌓이지 않도록 주기적으로 확인하는 습관이 중요합니다.
오늘 소개한 세 가지 방법을 상황에 맞게 활용해 보세요. 반복적으로 입력된 데이터를 빠르게 찾아 정리할 수 있을 뿐 아니라, 보다 정확하고 효율적으로 스프레드시트를 관리할 수 있을 것입니다.