엑셀에서 중복 항목 찾기 및 제거하는 방법

엑셀에서 중복 값은 귀찮을 수 있지만, 다행히도 중복을 찾고 제거하는 여러 방법이 있습니다. 중복을 제거하기 전에 엑셀 시트의 백업 복사본을 만드는 것을 권장합니다. 엑셀에서 중복 값을 계산하고 찾고 제거하는 방법을 살펴보겠습니다.

목차

• 1. 중복 제거 버튼 사용
• 2. 조건부 서식을 사용하여 중복 데이터 찾기
• 3. 조건부 필터를 사용하여 중복 제거
• 4. 수식을 사용하여 엑셀에서 중복 찾기
• 5. 수식을 사용하여 중복 수 세기
• 6. 수식을 사용하여 중복 값 제거
• 7. 고급 필터를 사용하여 중복 데이터 제거
• 8. 파워 쿼리를 사용하여 엑셀 중복 제거
• 9. 피벗 테이블 사용

1. 중복 제거 버튼 사용

엑셀에서 중복을 찾고 제거하는 가장 빠른 방법은 엑셀의 “중복 제거” 버튼을 사용하는 것입니다. 이 방법은 하나 이상의 열의 데이터를 기반으로 중복을 검색할 수 있습니다. 중복이 발견되면 전체 행이 제거됩니다.

스프레드시트에서 데이터가 있는 셀을 클릭합니다. 중복을 제거하고자 하는 셀, 열 또는 행일 필요는 없습니다.

“데이터” 탭을 선택하고 도구 모음에서 “중복 제거” 버튼을 클릭합니다.

중복을 제거하고자 하는 열을 체크합니다. 기본적으로 모든 열이 체크되어 있습니다. 또한, 열에 제목이 없는 경우 “내 데이터에 헤더가 있습니다” 상자를 체크 해제하여 첫 번째 행이 포함되도록 합니다. 원하는 열을 선택한 후 “확인”을 클릭합니다.

엑셀은 몇 개의 행이 삭제되었고 몇 개의 행이 남아 있는지에 대한 메시지를 제공합니다. 이 방법은 값만이 아니라 전체 행을 제거합니다. 결과를 수락하고 스프레드시트로 돌아가려면 “확인”을 누릅니다.

결과가 마음에 들지 않으면 Ctrl + Z를 눌러 삭제된 값을 시트로 되돌릴 수 있습니다.

2. 조건부 서식을 사용하여 중복 데이터 찾기

무언가를 삭제하기 전에 엑셀에서 중복을 검토하고 싶다면 조건부 서식을 사용해 보세요. 이 방법은 엑셀에서 중복을 찾지만 삭제하지는 않습니다.

중복 값을 검색할 열 또는 전체 시트를 선택합니다.

“홈” 탭을 열고 “조건부 서식” 버튼을 클릭합니다.

“셀 강조 규칙”을 선택하고 “중복 값”을 선택합니다.

중복 값이 나타날 색상을 선택합니다. 고유 값을 강조 표시하고 싶다면 “규칙 유형”을 “중복 값”에서 “고유 값”으로 변경할 수도 있습니다. 완료되면 “완료”를 클릭합니다.

강조 표시된 셀을 검토하고 원하지 않는 중복을 삭제합니다. 제 예에서는 모든 값이 중복된 행만 삭제하고 싶습니다.

3. 조건부 필터를 사용하여 중복 제거

조건부 서식을 사용한 후 엑셀에서 중복을 더 쉽게 제거하고 싶다면 셀의 색상을 기준으로 엑셀 필터를 사용하여 중복 또는 고유 값만 표시하도록 합니다. 이 옵션을 사용하면 전체 행을 수동으로 제거하거나 중복 셀만 제거할 수 있습니다.

이전 섹션을 기반으로 조건부 서식을 설정합니다.

“홈” 탭을 열고 “정렬 및 필터” 버튼을 선택한 후 “필터”를 선택합니다.

각 열의 헤더에 드롭다운 화살표가 나타납니다. 화살표를 클릭하고 “색상으로 필터”를 선택합니다. 중복 셀의 색상을 선택하여 해당 셀만 보이도록 합니다. 또는 “채우기 없음”을 선택하여 고유 값만 선택합니다.

“채우기 없음”을 선택하면 중복(원본 값 포함)이 표시되지 않습니다. 모든 값을 보려면 대신 “색상으로 필터”를 선택하세요. 이렇게 하면 색상이 있는 셀 또는 채우기가 없는 셀을 목록의 맨 위에 배치합니다.

제 예에서는 중복 셀의 색상으로 정렬하고 있습니다. 이를 통해 중복을 검토하고 원하지 않는 행이나 값을 삭제할 수 있습니다. 고유 값만 표시하도록 선택하면 모든 보이는 데이터를 새 시트로 복사하거나 이동할 수 있습니다. 이렇게 하면 중복만 남게 됩니다.

데이터 편집이 끝나면 드롭다운 화살표를 다시 클릭하고 “필터 지우기”를 선택합니다.

드롭다운 화살표를 제거하려면 도구 모음에서 “정렬 및 필터”를 선택하고 “필터”를 선택합니다. 이렇게 하면 모든 필터가 지워집니다.

팁: 엑셀 필터로 더 많은 작업을 할 수 있습니다. 예를 들어, 빈 셀을 제거하거나 셀의 여분의 공백을 제거할 수 있습니다.

4. 수식을 사용하여 엑셀에서 중복 찾기

엑셀 수식은 스프레드시트에서 거의 모든 작업을 수행할 수 있으며, 중복을 찾는 것도 포함됩니다. 보고 싶은 내용에 따라 사용할 수 있는 여러 가지 수식이 있습니다. 이 모든 수식은 COUNT 함수에 기반합니다. 이 수식은 중복을 제거하지 않습니다. 발견된 후 수동으로 제거해야 합니다. 어떤 수식을 사용하든 과정은 동일합니다:

같은 시트에 새 열을 만듭니다. 저는 “중복”이라고 레이블을 붙였습니다.

새 열의 첫 번째 빈 셀을 선택합니다. 원하는 수식을 함수/수식 입력줄이나 셀 자체에 입력합니다.

수식을 열의 다른 셀로 확장하려면 수식이 포함된 셀의 오른쪽 하단에 있는 작은 사각형을 클릭하고 드래그하여 원하는 만큼 셀을 채웁니다.

수식을 삽입하는 방법을 알았으니, 사용할 수 있는 최고의 옵션은 다음과 같습니다:

• =COUNTIF(Range, Criteria) >1 – 범위는 열이고, 기준은 가장 위의 셀입니다. 이는 단일 열에서 중복을 찾는 데 잘 작동합니다. 예를 들어, D 열만 기준으로 중복을 보려면 =COUNTIF(D:D, D2) >1을 사용합니다. “True” 결과는 중복을 의미하고, “False”는 고유함을 의미합니다.

• 셀 참조가 드래그할 때 변경되지 않도록 하려면 절대 셀 참조를 사용합니다. 예를 들어, 전체 D 열을 확인하려면 =COUNTIF($D$2:$D$105, $D2) >1을 사용합니다.
• True 또는 False 외의 다른 것을 표시하려면 COUNTIF를 IF 함수로 감싸세요: =IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", " "). 이렇게 하면 중복에 대해 “Duplicate”를 표시하고 고유 값에 대해 빈 셀을 표시합니다. “Duplicate”와 “Unique”와 같은 원하는 단어를 쉽게 사용할 수 있습니다.
• 여러 열을 동시에 비교하려면 절대 셀 참조와 IF 문을 사용해야 합니다. 예: =IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique"). 이 수식은 D, E 및 F의 값이 동일할 때만 “Duplicate”를 표시합니다. 세 개 중 하나라도 행에서 고유하면 “Unique”가 대신 표시됩니다.
• 오직 중복 값만 표시하고 첫 번째 발생은 표시하지 않으려면 대신 다음을 시도하세요: =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique"). 이는 두 번째 또는 그 이상의 발생에 대해서만 “Duplicate”를 표시합니다.

5. 수식을 사용하여 중복 수 세기

위의 수식을 > 1 텍스트 없이 사용하여 열에서 중복 수를 셀 수 있습니다. 두 수식은 =COUNTIF($D$2:$D$105, $D2) 및 =COUNTIF(D:D, D2)가 됩니다. 새 열에 입력하여 각 항목이 데이터에서 몇 번 나타나는지 표시합니다.

“1” 값은 고유 값을 의미합니다. 다른 값은 모두 중복입니다.

6. 수식을 사용하여 중복 값 제거

중복 값을 찾은 후(4절) 또는 중복 수를 찾은 후(5절) 필터 방법을 사용하여 중복을 제거하고 고유 값을 유지합니다.

중복 값 또는 수가 있는 열의 아무 곳이나 클릭합니다. “홈 탭 -> 정렬 및 필터 -> 필터”로 이동하여 열 헤더에서 필터 드롭다운 상자를 활성화합니다.

중복 열의 드롭다운 화살표를 클릭합니다.

유지할 값은 체크하고, 원하지 않는 값은 체크 해제한 후 “적용”을 클릭합니다. 중복 값 방법에서 고유 값의 레이블에 대해 “고유”, “False” 또는 원하는 레이블만 선택하여 모든 중복을 숨깁니다. 고유 식별자를 체크 해제하여 중복만 표시합니다.

Ctrl + C를 눌러 보이는 행을 선택합니다. 어떤 이유로 모든 행이 선택되면 Alt + ; 단축키를 사용하세요.

고유 값 또는 중복(표시할 것을 선택한 것)을 다른 시트로 복사합니다. 원본 시트에서 해당 값을 삭제합니다.

“정렬 및 필터 -> 필터”로 이동합니다. “필터”를 클릭하면 필터가 제거되고 나머지 중복 또는 고유 값이 모두 표시됩니다.

여기서 중복을 검토하고 더 이상 원하지 않는 셀이나 행을 제거합니다. 원본 발생은 중복 값과 함께 나열됩니다.

7. 고급 필터를 사용하여 중복 데이터 제거

지금까지 기본 필터만 사용했지만, 고급 필터를 사용하여 엑셀에서 중복을 찾고 제거할 수도 있습니다.

필터링할 열을 선택합니다. “데이터” 탭을 열고 “고급”을 클릭합니다.

“고급 필터” 상자에서 “목록 필터, 제자리에서”를 선택합니다. 이렇게 하면 동일한 데이터 세트에서 중복이 숨겨집니다. 나중에 고유 값을 수동으로 복사하여 같은 시트의 다른 위치나 별도의 시트로 붙여넣을 수 있습니다.

아직 열을 선택하지 않았다면 선택합니다. 그러면 “목록 범위” 필드에 자동으로 표시됩니다. “조건 범위”는 비워 둡니다.

“고유 레코드만” 옆의 상자를 체크하고 “확인”을 누릅니다.

이렇게 하면 데이터에서 고유 값이 표시됩니다. Alt + ; 단축키를 사용하여 보이는 행을 선택하여 복사와 같은 작업을 수행할 수 있습니다.

반대로, 고급 필터 상자에서 “다른 위치에 복사”를 선택하면 엑셀이 고유 값을 같은 시트의 다른 위치로 자동으로 복사합니다.

먼저 “목록 범위”를 선택합니다. “조건 범위”는 비워 두어도 됩니다.

“복사할 위치“ 필드에서 클릭하고 고유 데이터를 복사할 시트의 행을 선택합니다.

“고유 레코드만” 옆의 상자가 체크되어 있는지 확인합니다.

이 방법은 값만이 아니라 전체 중복 행을 숨깁니다. 중복 값의 원본 발생도 숨깁니다. 중복을 보려면 원본 값을 추출해야 합니다.

8. 파워 쿼리를 사용하여 엑셀 중복 제거

파워 쿼리는 아래와 같이 엑셀에서 중복 값을 제거할 수 있습니다. 이 방법은 전체 중복 행을 제거합니다.

“데이터” 탭을 열고 “테이블/범위에서”를 선택합니다.

모든 데이터가 자동으로 선택되어야 합니다. 그렇지 않으면 “테이블 만들기” 상자에 전체 시트의 범위를 입력합니다. 완료되면 “확인”을 클릭합니다.

“파워 쿼리” 편집기가 열립니다. 열을 선택하고 선택한 열 헤더를 마우스 오른쪽 버튼으로 클릭합니다. 메뉴에서 “중복 제거”를 선택합니다. 이렇게 하면 파워 쿼리 편집기에서 모든 중복이 제거됩니다. 이 도구에서는 원본 발생이 목록에 남아 있습니다.

전체 테이블에서 중복을 제거하려면 왼쪽 상단의 “테이블“ 버튼을 클릭하고 “중복 유지“를 선택하여 중복 항목만 표시하고 나머지를 제거합니다.

이렇게 하면 원본 항목을 포함한 모든 중복이 표시됩니다. 추가 항목을 제거하고 원본 항목만 남기려면 다시 “테이블“을 클릭하고 “중복 제거”를 선택합니다.

상단의 “닫기 및 로드”를 클릭하여 같은 통합 문서의 새 탭에서 테이블을 엽니다. 쿼리 결과만 로드됩니다. 원본 시트에서 아무것도 제거하지 않습니다.

9. 피벗 테이블 사용

피벗 테이블을 사용하여 데이터에서 고유 값만 표시하여 중복 항목을 제거합니다. 이는 실제로 원본 데이터에서 행이나 값을 제거하지 않고 고유 값만 보여줍니다.

“삽입” 탭을 열고 “피벗 테이블“을 선택합니다. “테이블/범위에서”를 선택합니다.

중복 값을 숨기고자 하는 테이블 또는 범위를 선택합니다. 기본적으로 전체 값 세트가 선택되어야 합니다. 그렇지 않으면 Ctrl + A를 눌러 모든 것을 선택하거나 마우스를 사용하여 사용자 정의 범위를 선택합니다.

피벗 테이블을 동일한 워크시트에 배치할지 새 워크시트에 배치할지를 선택합니다. 기존 워크시트인 경우 “위치“ 상자에 사용할 셀을 입력하거나 선택합니다. “확인”을 클릭합니다.

피벗 테이블 사이드바에서 고유 값을 추출할 열을 “행” 섹션으로 체크하거나 드래그합니다.

피벗 테이블을 표 형식으로 표시하려면 서식을 지정해야 합니다. 이를 위해 “디자인” 탭으로 이동하고 다음 단계를 수행합니다:

“보고서 레이아웃 -> 표 형식으로 표시”를 선택합니다.

“소계 -> 소계 표시 안 함”을 선택합니다.

“보고서 레이아웃 -> 모든 항목 레이블 반복”을 선택합니다.

“총계 -> 행 및 열에 대해 끄기”를 선택합니다.

이렇게 하면 표 형식으로 고유 값이 있는 피벗 테이블이 생성됩니다.

엑셀에서 중복을 찾고 제거하는 방법이 이렇게 많으니, 다시 수동으로 할 이유가 없습니다. 좋아하는 방법을 선택하고 실행하세요. 중복 문제로 시간을 절약하는 동안, 이러한 Microsoft Excel 팁과 요령을 시도하여 더 많은 시간을 절약하세요. 또한 엑셀에서 데이터를 더 정리하는 방법을 배우세요.

이미지 출처: Pixabay. 모든 스크린샷은 Crystal Crowder에 의해 촬영되었습니다.