Excel에서 중복 항목을 찾아 제거하는 방법

Excel에서 중복된 값은 짜증스러울 수 있지만 다행스럽게도 이를 찾고 제거할 수 있는 여러 가지 방법이 있습니다. 중복 항목을 제거하기 전에 Excel 시트의 백업 복사본을 만드는 것이 좋습니다. Excel에서 중복된 값을 계산하고, 찾고, 제거하는 방법을 살펴보겠습니다.

1. 중복 제거 버튼을 사용하세요

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

스프레드시트에서 데이터가 포함된 셀을 클릭하세요. 중복 항목을 제거하려는 셀, 열 또는 행일 필요는 없습니다.

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

중복 항목을 제거하려는 열을 확인하세요. 모든 열은 기본적으로 선택되어 있습니다. 또한 열에 제목이 없는 경우 첫 번째 행이 포함되도록 ‘내 데이터에 헤더가 있음’ 확인란을 선택 취소하세요. 원하는 열을 선택한 후 “확인”을 클릭하세요.

Excel에서는 삭제된 행 수와 남아 있는 행 수를 알려주는 메시지를 표시합니다. 이 방법은 값뿐만 아니라 전체 행을 제거합니다. 결과를 승인하고 스프레드시트로 돌아가려면 “확인”을 누르세요.

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

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

삭제하기 전에 Excel에서 중복 항목을 검토하려면 조건부 서식을 사용해 보세요 . Excel에서 중복 항목을 찾았지만 삭제하지는 않습니다.

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

‘홈’ 탭을 열고 ‘조건부 서식’ 버튼을 클릭하세요.

“셀 규칙 강조 표시”를 선택하고 “중복 값”을 선택하십시오.

중복 값을 표시할 색상을 선택하십시오. 고유한 값을 강조 표시하려면 “규칙 유형”을 “중복 값”에서 “고유 값”으로 전환할 수도 있습니다. 완료되면 “완료”를 클릭하세요.

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

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

조건부 서식을 사용한 후 Excel에서 중복 항목을 더 쉽게 제거하려면 셀 색상을 기반으로 하는 Excel 필터를 사용하여 중복 항목 또는 고유 값만 표시하세요. 이 옵션을 사용하면 전체 행을 수동으로 제거하거나 중복된 셀만 제거합니다.

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

“홈”탭을 열고 “정렬 및 필터”버튼을 선택한 다음 “필터”를 선택하십시오.

각 열의 헤더에 드롭다운 화살표가 나타납니다. 화살표를 클릭하고 ‘색상별 필터링’을 선택하세요. 해당 셀만 보려면 중복된 셀의 색상을 선택하세요. 또는 “채우기 없음”을 선택하여 고유한 값만 선택합니다.

“채우기 없음”을 선택하면 중복 항목(원래 값 포함)이 표시되지 않습니다. 모든 값을 계속 보려면 대신 “색상별 필터링”을 선택하세요. 이렇게 하면 색상이 지정된 셀이나 채우기가 없는 셀이 목록 상단에 배치됩니다.

제 예에서는 중복 셀의 색상을 기준으로 정렬하고 있습니다. 이를 통해 중복 항목을 검토하고 유지하고 싶지 않은 행이나 값을 삭제할 수 있습니다. 고유한 값만 표시하도록 선택한 경우 표시되는 모든 데이터를 새 시트로 복사하거나 이동할 수 있습니다. 그러면 중복된 항목만 남게 됩니다.

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

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

4. 수식을 사용하여 Excel에서 중복 항목 찾기

Excel 수식은 중복 항목 찾기를 포함하여 스프레드시트에서 대부분의 작업을 수행할 수 있습니다. 보고 싶은 내용에 따라 사용할 수 있는 여러 가지 공식이 있습니다. 이것들은 모두 COUNT 함수를 기반으로 합니다 . 이 수식은 중복을 제거하지 않습니다. 발견되면 수동으로 수행해야 합니다. 어떤 공식을 사용하든 프로세스는 동일합니다.

동일한 시트에 새 열을 만듭니다. 나는 “중복”이라고 라벨을 붙였습니다.

새 열에서 첫 번째 빈 셀을 선택합니다. 함수/수식 표시줄 또는 셀 자체에 원하는 수식(단계 아래의 다양한 수식 참조)을 입력하세요.

수식을 열의 다른 셀로 확장하려면 수식이 포함된 셀 오른쪽 하단에 있는 작은 사각형을 길게 클릭하세요. 원하는 만큼 셀을 채우려면 아래쪽으로 당깁니다.

이제 수식을 삽입하는 방법을 알았으므로 다음과 같은 옵션을 사용할 수 있습니다.

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

• 드래그할 때 셀 참조가 변경되지 않도록 하려면 대신 절대 셀 참조를 사용하세요. =COUNTIF($D$2:$D$105, $D2) >1예를 들어 D 열 전체를 확인하는 데 사용합니다 .
• True 또는 False 이외의 항목을 표시하려면 IF 함수 내에 COUNTIF를 포함하세요 =IF(COUNTIF($D$2:$D$105, $D2) > 1, "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의 값이 동일한 경우에만 “중복”을 표시합니다. 세 가지 중 하나라도 연속해서 고유한 경우 “고유”가 대신 표시됩니다.
• 첫 번째 값이 아닌 중복 값만 표시하려면 대신 다음을 시도해 보십시오. =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique")이렇게 하면 두 번째 이상 발생하는 경우에만 “중복”이 표시됩니다.

5. 수식을 사용하여 중복 횟수 계산

텍스트 없이 위 수식을 사용하여 > 1열의 중복 항목 수를 계산할 수 있습니다. =COUNTIF($D$2:$D$105, $D2)두 수식은 과 가 됩니다 =COUNTIF(D:D, D2). 각 항목이 데이터에 나타나는 횟수를 표시하려면 새 열을 입력하십시오.

값 “1”은 고유한 값을 의미합니다. 다른 것은 중복과 같습니다.

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

중복 값(섹션 4) 또는 중복 개수(섹션 5)를 찾은 후에는 필터 메서드를 사용하여 중복을 제거하고 고유 값을 유지합니다.

중복된 값이나 개수가 있는 열의 아무 곳이나 클릭하세요. 열 머리글에서 필터 드롭다운 상자를 활성화하려면 “홈 탭 -> 정렬 및 필터 -> 필터”로 이동하세요.

중복된 열에서 드롭다운 화살표를 클릭하세요.

유지하려는 값을 선택하고 원하지 않는 값은 선택 취소한 다음 “적용”을 클릭하세요. “고유”, “거짓” 또는 고유 값에 대한 레이블이 중복에 대한 것인 것만 선택하여 모든 중복을 숨깁니다. 값 방법, 중복 횟수 계산의 경우 “1”입니다. 고유 식별자를 선택 취소하여 중복 항목만 봅니다.

Ctrl+ 를 눌러 보이는 행을 선택합니다 C. 어떤 이유로든 숨겨진 행을 포함하여 모든 행이 선택되는 경우 Alt+ ;단축키를 대신 사용하세요.

고유한 값이나 중복 항목(표시하도록 선택한 항목)을 다른 시트에 복사합니다. 원본 시트에서 해당 값을 삭제합니다.

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

여기에서 중복 항목을 살펴보고 더 이상 원하지 않는 셀이나 행을 제거하세요. 원래 발생은 중복 값과 함께 나열됩니다.

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

지금까지는 기본 필터만 사용했지만 고급 필터를 사용하면 Excel에서 중복 항목을 찾아 제거할 수도 있습니다.

필터링하려는 열을 선택합니다. “데이터”탭을 열고 “고급”을 클릭하십시오.

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

열을 아직 선택하지 않았다면 선택하세요. ‘목록 범위’ 필드에 자동으로 표시됩니다. ‘기준 범위’를 비워 둡니다.

“고유 레코드만” 옆의 확인란을 선택하고 “확인”을 누르십시오.

그러면 데이터에 고유한 값이 표시됩니다. 다른 위치로 복사하는 등의 작업을 수행하려는 경우에만 Alt+ 단축키를 사용하여 표시되는 행을 선택하세요.;

반대로 Excel에서 고유한 값을 동일한 시트의 다른 위치에 자동으로 복사하도록 하려면 고급 필터 상자에서 “다른 위치에 복사”를 선택하세요.

먼저 “목록 범위”를 선택하세요. ‘기준 범위’는 비워 둘 수 있습니다.

“복사 대상” 필드를 한 번 클릭하고 고유 데이터를 복사하려는 시트의 행을 선택합니다.

‘고유 레코드만’ 옆의 확인란이 선택되어 있는지 확인하세요.

이 방법은 값뿐만 아니라 전체 중복 행을 숨깁니다. 또한 중복 버전뿐만 아니라 중복 값의 원래 발생도 숨깁니다. 원래 값을 추출하려면 중복 항목을 보고 싶을 것입니다.

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

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

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

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

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

전체 표에서 중복 항목을 제거하려면 왼쪽 상단 모서리에 있는 “표”버튼을 클릭하고 “중복 항목 유지”를 선택하여 중복 항목만 표시하고 나머지는 제거하십시오.

원본 항목을 포함하여 모든 중복 항목이 표시됩니다. 추가 항목을 제거하고 원래 항목만 남겨두려면 ‘표’를 다시 클릭하고 ‘중복 항목 제거’를 선택하세요.

상단의 “닫기 및 로드”를 클릭하면 동일한 통합 문서의 새 탭에 테이블이 열립니다. 쿼리 결과만 로드됩니다. 이렇게 해도 원본 시트에서는 아무것도 제거되지 않습니다.

9. 피벗 테이블 사용

피벗 테이블을 사용하면 데이터의 고유한 값만 표시하여 중복 항목을 제거할 수 있습니다. 실제로 원본 데이터에서 행이나 값이 제거되지는 않습니다. 단지 고유한 값을 보여줄 뿐입니다.

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

중복 값을 숨기려는 테이블이나 범위를 선택합니다. 기본적으로 전체 값 집합이 선택되어야 합니다. 그렇지 않은 경우 Ctrl+를 눌러 A모든 항목을 선택하세요. 또는 마우스를 사용하여 사용자 정의 범위를 선택하십시오.

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

피벗 테이블 사이드바에서 고유한 값을 추출하려는 열을 선택하거나 ‘행’ 섹션으로 드래그하세요.

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

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

‘소계 -> 소계 표시 안함’을 선택하세요.

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

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

표 형식의 고유한 값이 포함된 피벗 테이블을 얻게 됩니다.

Excel에서 중복 항목을 찾고 제거하는 방법이 너무 많기 때문에 수동으로 다시 수행할 이유가 없습니다. 가장 좋아하는 방법을 선택하고 실행해 보세요. 중복된 문제로 인해 시간을 절약하는 동안 Microsoft Excel 팁과 요령을 활용하여 더 많은 시간을 절약해 보세요. 또한 Excel에서 데이터를 추가로 정리하는 방법도 알아보세요 .

이미지 출처: Pixabay . Crystal Crowder의 모든 스크린샷.