☰

두 스프레드시트의 열을 기반으로 중복 값을 찾고 행이 정확히 일치하는지 확인하는 방법

tag-icon • tag-icon microsoft-excel
두 스프레드시트의 열을 기반으로 중복 값을 찾고 행이 정확히 일치하는지 확인하는 방법

두 개의 Excel 스프레드시트가 있습니다: Header2018 및 Header2019

둘 다 동일한 열 머리글을 포함하지만 레코드 항목은 다릅니다. 이 항목 중 일부는 동일하며 어떤 항목을 찾고 싶습니다.

모든 중복 레코드(asset_id 열 기준)를 새 스프레드시트에 병합한 다음 나머지 열 사이에 차이점이 있는지 확인하고 싶습니다.

예시 헤더 2018:

asset_id    Name    Country

2          Trent      CAN
3          Alex       CAN
4          Derek      CAN
5          Louis      CAN
6          Teresa     CAN

헤더 2019:

asset_id    Name    Country
4          Derek      CAN
5          Louis      USA
1          Kate       CAN
7          Pat        CAN
8          Steven     CAN
9          Tom        CAN

결과 테이블:

asset_id    Name    Country
4          Derek      CAN
5          Louis      CAN
4          Derek      CAN
5          Louis      USA

결과 테이블을 기반으로 자산 ID는 동일하지만 다른 열이 다른 레코드를 찾을 수 있기를 원합니다.

미리 감사드립니다!

답변1

파워 쿼리를 사용하여 결과를 얻을 수 있습니다.

  1. 파워 쿼리 편집기에서 파워 쿼리에 테이블 2개를 추가합니다. 홈으로 이동하여 쿼리 2개를 추가합니다. 여기에 이미지 설명을 입력하세요

  2. 자산 ID 열 선택 - 변환으로 이동 - 그룹화 기준 선택 - 고급 선택 - 집계 추가 - 새 열의 이름을 입력하고 모든 행을 선택합니다. 여기에 이미지 설명을 입력하세요

  3. 테이블을 확장하고 이름 및 국가 - 필터 개수 열 >2를 선택하고 개수 열을 제거하고 닫고 로드합니다. 여기에 이미지 설명을 입력하세요

답변2

제안 방법을 원합니다. 두 시트에서 공통 행을 추출하려면 도우미 열 및 배열 수식이 필요합니다.

시트 1:

여기에 이미지 설명을 입력하세요

  • 셀의 A2수식도우미 값:

=COUNTIF(Sheet2!$B$2:$B$7, B2)

시트 2:

여기에 이미지 설명을 입력하세요

  • 셀의 수식 A2:

=COUNTIF(Sheet1!$B$2:$B$6, B2)

주의

  • 두 시트 모두에 중복 ID 수가 있는 경우(2개 이상이다) 그런 다음 대신카운티, 이 공식을 사용하여 찾는 것이 좋습니다중복.

    =IF(ISERROR(MATCH(B2,Sheet2!B$2:B$7,0)),"","Duplicate")

  • 그리고마스터 시트Cell의 기준은 E59다음과 같습니다.복제하다대신에1.

마스터 시트:

여기에 이미지 설명을 입력하세요

  • 셀의 배열 수식 G60:

    {=IFERROR(INDEX(Sheet1!$B$2:$D$6, SMALL(IF(COUNTIF($E$59, Sheet1!$A$2:$A$6), ROW(Sheet1!$B$2:$D$6)-MIN(ROW(Sheet1!$B$2:$D$6))+1), ROW(A1)), COLUMN(A1)),IFERROR(INDEX(Sheet2!$B$2:$D$7, SMALL(IF(COUNTIF($E$59, Sheet2!$A$2:$A$7), ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1), ABS(ROW(A1)-2)), COLUMN(A1)),""))}

  • 다음으로 수식 완성Ctrl+Shift+Enter, 바로 채우고 아래로 채웁니다.

  • 배열 수식을 ABS(ROW(A1)-2))사용하여 조정했습니다.행 번호Sheet 2을 위해 1.

필요에 따라 수식에서 셀 참조를 조정할 수 있습니다.

관련 정보