XNUMX つのリストを重複せずに結合

内容

古典的な状況: XNUMX つにマージする必要がある XNUMX つのリストがあります。さらに、最初のリストには一意の要素と一致する要素 (リスト間と内部の両方) の両方が存在する可能性がありますが、出力では重複 (繰り返し) のないリストを取得する必要があります。

このような一般的な問題を解決するための伝統的な方法をいくつか見てみましょう - 原始的な「額」からより複雑だがエレガントなものまで。

方法 1: 重複を削除する

最も簡単な方法で問題を解決できます。手動で両方のリストの要素を XNUMX つにコピーし、結果のセットにツールを適用します。 重複を削除する タブから 且つ (データ — 重複を削除):

重複のない XNUMX つのリストのマージ

もちろん、ソースリストのデータが頻繁に変更される場合、この方法は機能しません。変更するたびに手順全体を繰り返す必要があります。

方法 1a. ピボットテーブル

実際、この方法は前の方法の論理的な続きです。リストがそれほど大きくなく、リスト内の要素の最大数が事前にわかっている場合 (たとえば、10 以下)、直接リンクによって XNUMX つのテーブルを XNUMX つに結合し、右側にある列を追加し、結果のテーブルに基づいて要約テーブルを作成します。

重複のない XNUMX つのリストのマージ

ご存知のように、ピボットテーブルは繰り返しを無視するため、出力では重複のない結合リストが得られます。 1 の補助列が必要なのは、Excel が少なくとも XNUMX つの列を含む集計テーブルを作成できるためです。

元のリストが変更されると、新しいデータは直接リンクを介して結合されたテーブルに移動しますが、ピボットテーブルは手動で更新する必要があります (右クリック – 更新して保存）。その場で再計算する必要がない場合は、他のオプションを使用することをお勧めします。

方法 2: 配列数式

数式で問題を解くことができます。この場合、元のリストが変更された直後に、結果の再計算と更新が自動的かつ即座に行われます。便宜上、簡潔にするために、リストに名前を付けましょう。 一覧1 и 一覧2 名前管理者 タブ 式 (数式 — 名前管理 — 作成):

重複のない XNUMX つのリストのマージ

名前を付けた後、必要な式は次のようになります。

重複のない XNUMX つのリストのマージ

一見、不気味に見えますが、実際にはすべてがそれほど怖くありません。 Alt+Enter キーの組み合わせを使用してこの数式を複数行に展開し、スペースでインデントします。たとえば、次のようにします。

重複のない XNUMX つのリストのマージ

ここでのロジックは次のとおりです。

数式 INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) は、最初のリストからすべての一意の要素を選択します。それらがなくなるとすぐに、#N/A エラーが発生し始めます。
式 INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) は、同じ方法で XNUMX 番目のリストから一意の要素を抽出します。
互いにネストされた 1 つの IFERROR 関数は、最初にリスト 2 からの一意の出力を実装し、次にリスト XNUMX から次々に出力を実装します。

これは配列数式であることに注意してください。つまり、入力後、通常とは異なるセルに入力する必要があります。入力します、ただしキーボードショートカットを使用 Ctrlキー+シフト+入力します次に、余白を付けて子セルにコピー (ドラッグ) します。

英語版の Excel では、この数式は次のようになります。

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) )、"")

このアプローチの欠点は、ソーステーブルに多数の (数百以上の) 要素がある場合、配列数式によってファイルの処理が著しく遅くなることです。

方法 3. パワークエリ

ソースリストに多数の要素 (数百または数千など) がある場合は、遅い配列数式ではなく、根本的に異なるアプローチ、つまり Power Query アドインツールを使用することをお勧めします。このアドインは、既定で Excel 2016 に組み込まれています。 Excel 2010 または 2013 をお持ちの場合は、個別にダウンロードしてインストールできます (無料)。

アクションのアルゴリズムは次のとおりです。

インストールされたアドオンの別のタブを開く パワークエリ (Excel 2010-2013 を使用している場合) または単にタブに移動します。且つ (Excel 2016 をお持ちの場合)。
最初のリストを選択してボタンを押します テーブル/範囲から (範囲/テーブルから). リストから「スマートテーブル」を作成することについて尋ねられたとき、私たちは同意します：
クエリエディターウィンドウが開き、読み込まれたデータとクエリ名が表示されます。 テーブル1 （必要に応じて独自のものに変更できます）。
テーブルヘッダーをダブルクリックします (word 一覧1）、名前を他の名前に変更します（たとえば、 のワークプ）。正確に何に名前を付けるかは重要ではありませんが、発明された名前を覚えておく必要があります。後で XNUMX 番目のテーブルをインポートするときに再度使用する必要があります。今後 XNUMX つのテーブルをマージするには、列見出しが一致する場合にのみ機能します。
左上隅のドロップダウンリストを展開します 閉じてダウンロード 選択して 閉じてロード… (クローズ&ロード…):
次のダイアログボックス (少し異なるように見えるかもしれませんが、心配しないでください) で、 接続を作成するだけ (接続のみ作成):
2 番目のリストについて、手順全体 (ポイント 6 ～ XNUMX) を繰り返します。列見出しの名前を変更するときは、前のクエリと同じ名前 (People) を使用することが重要です。
タブの Excel ウィンドウで且つまたはタブで パワークエリ 選択する データの取得 – リクエストの結合 – 追加 (データの取得 — クエリのマージ — 追加):
表示されるダイアログボックスで、ドロップダウンリストからリクエストを選択します。
その結果、XNUMX つのリストが相互に接続される新しいクエリが得られます。ボタンで重複を削除する必要があります 行の削除 – 重複を削除 (行を削除 — 重複を削除):
完成したクエリは、オプションパネルの右側で名前を変更し、適切な名前を付けて (これは実際には結果テーブルの名前になります)、コマンドを使用してすべてをシートにアップロードできます。 閉じてダウンロード (クローズ&ロード):