무결성을 유지하면서 Excel에서 중복 데이터를 제거하는 8가지 입증된 방법

Excel에서 정확한 데이터 분석을 위해서는 중복 레코드를 관리하는 것이 중요합니다. Excel에 중복된 데이터가 있으면 계산이 부정확해지고 파일 크기가 커지며 혼란이 생길 ​​수 있습니다. Excel에서는 중복 항목을 제거하는 다양한 방법을 제공합니다.

이 튜토리얼에서는 데이터 손실 없이 Excel에서 중복 항목을 제거하는 8가지 방법을 보여 드리겠습니다.

1. 중복 제거 도구

Excel에 내장된 중복 제거 기능은 사용자 친화적인 인터페이스로 빠른 솔루션을 제공합니다.

단계:

• 헤더를 포함한 데이터 범위를 선택하세요.
• 데이터로 이동 탭>> 데이터 도구에서 그룹>> 중복 제거를 선택합니다. .
• 중복을 생성하는 조합을 결정하려면 열을 선택/선택 해제하세요.
• 확인을 클릭하세요. .

사례:

• 모든 열을 선택하면 행 6(OrderID 1002)만 제거됩니다. 행 1과 정확히 중복되기 때문입니다.
• Excel에는 1개의 중복 값이 발견되어 제거되었다는 메시지가 표시됩니다.

• OrderID 를 선택 해제하면 다른 모든 항목을 확인하세요.

• 5, 6, 8행은 중복 항목으로 삭제됩니다.

장점:

• 시각적 인터페이스로 사용이 간편합니다.
• 데이터에 직접 적용됩니다.
• 검사할 열을 지정할 수 있습니다.

고려사항:

• 중복 행을 영구적으로 삭제합니다.
• 각 레코드의 첫 번째 인스턴스만 유지합니다.
• 백업을 하지 않으면 원본 데이터를 복구할 수 없습니다.

전문가의 팁: 원본 데이터세트를 보존해야 하는 경우 이 도구를 사용하기 전에 항상 데이터를 다른 시트에 복사하세요.

2. 고급 필터(고유 기록만 해당)

고급 필터는 고유한 기록을 새 위치에 복사할 수 있도록 하여 더 많은 제어 기능을 제공합니다.

단계:

• 헤더를 사용하여 데이터를 정리하세요.
• 데이터로 이동 탭>> 정렬 및 필터에서 그룹>> 고급을 선택합니다. .
• 다른 위치로 복사를 선택합니다. .
• 목록 범위:에서 데이터 범위를 선택하세요. A1:G12 .
• 복사 대상:J1 .
• 고유 레코드만 확인 .
• 확인을 클릭하세요. .

중복된 행 6(OrderID 1002)은 결과에서 제외됩니다. 중복된 행을 제외하고 데이터가 J1 셀 이하에 복사됩니다.

고급 예: OrderID와 관계없이 중복된 제품 정보가 있는 거래를 식별하려면 다음을 수행하세요.

• 헤더(B1:G1)로 기준 범위를 설정합니다.
• 고급 필터 적용 이 기준 범위를 사용하여 동일한 거래를 찾습니다.

장점:

• 원본 데이터를 보존합니다.
• 고유한 레코드만 다른 위치에 복사합니다.
• 필요한 경우 복잡한 기준을 사용하여 작업합니다.

고려사항:

• 필터링된 결과를 위해 사용 가능한 공간이 필요합니다.
• 소스 데이터가 변경되면 수동으로 새로고침이 필요합니다.

3. 파워 쿼리(가져오기 및 변환)

파워 쿼리는 원본 데이터를 보존하고 대규모 데이터 세트를 처리할 수 있는 강력한 솔루션을 제공합니다.

단계:

• 데이터를 선택하세요.
• 데이터로 이동 탭>> 데이터 가져오기 및 변환에서 그룹>> 표/범위에서 선택 .

• 비교할 열을 선택하세요(모든 열을 선택하거나 특정 열만 선택할 수 있음).
• 홈으로 이동 탭>> 행 제거 그룹>> 중복 항목 제거 .
• 닫기 및 로드를 클릭하세요. 결과를 새 시트로 가져옵니다.

예:

• 모든 열을 기준으로 중복 항목을 제거하면 행 6(OrderID 1002)만 제거됩니다.

• 고객, 제품, 수량, 가격, 날짜(OrderID 제외)만을 기준으로 중복 항목을 제거하면 4행과 8행 모두 중복 항목으로 식별됩니다.

출력:

장점:

• 새로 고칠 수 있는 별도의 결과 세트를 생성합니다.
• 대규모 데이터세트를 효율적으로 처리합니다.
• 원본 데이터를 보존합니다.
• 반복 가능한 프로세스의 일부일 수 있습니다.
• 고급 변환 기능.

고려사항:

• 파워 쿼리에 대한 기본적인 이해가 필요합니다.
• 매우 큰 데이터세트에는 더 많은 리소스를 사용합니다.
• 복잡한 중복 제거 논리에는 추가 단계가 필요합니다.

4. 고유 기능(Excel 365/2021)

최신 Excel 버전의 경우 UNIQUE 함수는 고유한 값을 동적으로 추출합니다.

중복 행 제거:

• I1 셀을 선택하고 다음 수식을 삽입하세요.

수식:

모든 고유 행이 포함된 유출 범위를 얻습니다. 소스 데이터를 변경하면 이 목록이 업데이트됩니다. 그러면 중복된 행 6을 제외하고 데이터 세트의 모든 고유 행이 반환됩니다.

카테고리별 고유 제품: 제품과 카테고리의 고유한 조합을 추출합니다.

• 셀을 선택하고 다음 수식을 삽입하세요.

수식:

이 수식은 모든 고유 제품 목록이 포함된 유출 범위를 반환합니다.

고유 고객 목록: 고유한 고객 목록을 얻으려면.

• 셀을 선택하고 다음 수식을 삽입하세요.

수식:

이 수식은 모든 고유한 고객 이름이 포함된 유출 범위를 반환합니다.

장점:

• 소스 데이터가 변경되면 자동으로 업데이트되는 동적 결과를 생성합니다.
• 소스 데이터를 파괴하지 않습니다.
• 다른 기능과 결합될 수 있습니다.
• 여러 열에 걸쳐 고유한 조합을 반환합니다.

고려사항:

• Excel 365 및 Excel 2021에서만 사용할 수 있습니다.
• 분할된 배열 수식을 만듭니다(자동으로 확장됨).
• 작업 공간 계획이 필요할 수 있습니다.
• 매우 복잡한 중복 제거 논리를 처리할 수 없습니다.

5. 조건부 서식 + 필터

이러한 시각적 접근 방식은 중복 항목을 강조 표시하고 필터링하는 데 도움이 됩니다.

단계:

• 데이터 범위를 선택하세요.
• 홈으로 이동 탭>> 조건부 서식에서>> 셀 규칙 강조 표시를 선택합니다.>> 중복 값을 선택하세요. .

• 서식 스타일 선택:짙은 빨간색 텍스트로 밝은 빨간색 채우기 .
• 확인을 클릭하세요. .
• Excel은 중복된 셀을 열별로 강조 표시합니다.

• 데이터로 이동 탭>> 필터 선택 .
• 중복되거나 고유한 값을 표시하려면 셀 색상으로 필터링하세요.
• 고유한 경우 자동을 선택하세요. 중복하려면 색상을 선택하세요. .

고유:

중복:

전체 중복 행을 강조 표시하려면 다음과 같은 수식 규칙을 사용하세요.

• 홈으로 이동 탭>> 조건부 서식에서>> 선택>> 새 규칙 .
• 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. .
• 다음 수식을 삽입하세요:

=COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2)>1

• 채우기 색상을 선택하세요.
• 확인을 클릭하세요. .

장점:

• 삭제하기 전에 중복 항목을 시각적으로 식별합니다.
• 모든 데이터를 보존합니다.
• 선택적 삭제를 허용합니다.
• 모든 Excel 버전에서 작동합니다.
• 중복 패턴을 이해하는 데 도움이 됩니다.

고려사항:

• 다단계 프로세스.
• 수동 필터링이 필요합니다.
• 자동으로 업데이트되지 않습니다.
• 데이터가 변경되면 필터를 다시 적용해야 합니다.

6. 피벗 테이블 방법

피벗 테이블은 자연스럽게 데이터를 집계하여 그 과정에서 중복 항목을 효과적으로 제거합니다.

단계:

• 데이터를 선택하세요.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 기존 워크시트 선택 그리고 위치.
• 확인을 클릭하세요. .

• 피벗 테이블 필드 목록에서;
• 이 필드를 행으로 드래그하세요. :
  • 주문 ID, 고객, 제품, 카테고리, 날짜 .
• 이 필드를 값으로 드래그하세요. :
  • 수량, 가격 .

중복 행(3과 6)이 결합되어 수량(5+5=10)과 가격(129.5+129.5=259)의 합계가 표시됩니다.

중복된 제품 및 고객을 표시하기 위해 OrderID를 제외하려는 경우:

• 주문 ID 삭제 행에서 먼저 지역을 선택하세요.
• 결과 피벗 테이블에는 OrderID를 제외하고 모든 것이 유사한 거래만 표시됩니다.

장점:

• 중복 데이터를 단순히 제거하는 대신 요약할 수 있습니다.
• 대규모 데이터세트를 효율적으로 처리합니다.
• 숫자 값을 자동으로 집계합니다.

고려사항:

• 피벗 테이블에 대한 기본적인 이해가 필요합니다.
• 추출 후 추가 포맷이 필요할 수 있습니다.
• 기본적으로 숫자 값을 집계합니다(일부 필드에서는 바람직하지 않을 수 있음).

7. COUNTIF 도우미 칼럼

이 메소드는 각 레코드의 첫 번째 항목을 식별하는 열을 추가합니다.

단계:

• '중복 확인' 헤더가 있는 도우미 열(H열)을 추가합니다.
• 공식을 사용하여 고유한 행을 식별하세요.
• 고유한 기록만 보려면 해당 값을 필터링하세요.

완전한 중복 식별:

• H2 셀을 선택하고 다음 수식을 입력하세요.

수식:

=IF((COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")

이 수식은 전체 행이 중복되는 경우 중복을 표시합니다.

OrderID 무시: OrderID에 관계없이 거래 세부정보를 기반으로 중복을 식별하려면:

=IF((COUNTIFS($B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")

이 수식은 거래 세부정보가 중복되므로 2,3,5,6,10행을 '중복'으로 표시합니다.

장점:

• 어떤 기록이 중복되었는지 표시하고 원본 데이터를 유지합니다.
• 복잡한 조건에 맞게 맞춤설정할 수 있습니다.
• 중복된 행을 식별합니다.

고려사항:

• 추가 열이 필요합니다.
• 열이 여러 개인 경우 수식이 복잡해질 수 있습니다.
• 데이터가 변경되면 조정이 필요합니다.
• 새 데이터를 위해 복사해야 합니다.

8. 수식 기반 추출(INDEX/MATCH 또는 FILTER)

고급 사용자의 경우 INDEX, MATCH 및 기타 기능을 조합하여 고유한 값을 추출할 수 있습니다.

INDEX/MATCH 사용(이전 Excel 버전): 별도의 위치에 대한 고유한 고객 이름을 추출합니다.

• 셀을 선택하고 다음 수식을 삽입하세요.

수식:

=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($I$1:I1,$B$2:$B$12),0)),"")

필터 사용(Excel 365/2021): 모든 열을 유지하면서 고유한 레코드를 추출합니다.

• 셀을 선택하고 다음 수식을 삽입하세요.

수식:

=FILTER(A2:G12, MATCH(A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, 0)=ROW(A2:A12)-ROW(A2)+1)

장점:

• 맞춤설정이 가능합니다.
• 다른 방법이 실패할 때 작동합니다.
• 복잡한 논리를 통합할 수 있습니다.
• 소스 데이터를 파괴하지 않습니다.
• 소스 데이터 변경에 따라 동적으로 업데이트됩니다.

고려사항:

• 고급 Excel 지식이 필요합니다.
• 구현 및 유지 관리가 더 복잡합니다.
• 이전 Excel 버전에서는 배열 수식이 필요할 수 있습니다.
• 대규모 데이터 세트의 경우 리소스 집약적일 수 있습니다.

결론

Excel에서 중복 항목을 제거하는 것은 위험하거나 복잡할 필요가 없습니다. 소규모 보고서로 작업하든 대규모 데이터 세트로 작업하든 Excel은 중복 항목을 식별하고 제거할 수 있는 안전하고 유연한 여러 가지 방법을 제공합니다. 데이터 유형에 따라 다음 8가지 방법을 사용하여 데이터 손실 없이 Excel에서 중복 항목을 제거할 수 있습니다. 가장 좋은 방법은 Excel 버전, 데이터 크기, 수식 또는 도구 선호 여부에 따라 다릅니다. 이 8가지 기술을 익히면 중복 청소 문제에 대비할 수 있습니다. 중복 항목을 제거하기 전에 항상 데이터를 백업하세요. 특히 원본 데이터세트를 직접 수정하는 방법을 사용할 때는 더욱 그렇습니다.