☰

2 つのスプレッドシートの列に基づいて重複する値を見つけ、行が完全に一致するかどうかを確認する方法

tag-icon • tag-icon microsoft-excel
2 つのスプレッドシートの列に基づいて重複する値を見つけ、行が完全に一致するかどうかを確認する方法

Excelスプレッドシートが2つあります: 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

結果のテーブルに基づいて、asset_id は同じだが別の列が異なるレコードを見つけられるようにしたいと考えています。

前もって感謝します！

答え1

結果を取得するには、Power Query を使用できます。

  1. Power Query に 2 つのテーブルを追加します - Power Query エディターで [ホーム] に移動し、2 つのクエリを追加します。 ここに画像の説明を入力してください

  2. asset_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")

  • そしてマスターシートセルの基準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。

必要に応じて、数式内のセル参照を調整できます。

関連情報