究極ガイド - Excel で 2 つのリストまたはデータセットを比較する - Excel Club

Hay Paula, can you compare lists in Excel?

What is the best way to compare two sets of data in Excel?

Very often there is requirement to compare two lists, or two data sets to find missing or match items. これは Excel であるため、データの比較を含め、物事を行う方法は常に 1 つだけではありません。数式や条件付き書式からPower Queryまで。この記事では、Excel で 2 つのリストを比較するいくつかの方法と、データセットの行全体を比較する方法を見ていきます。そうでない場合は、なぜあなたは以下のコメントをドロップし、Excelで2つのリストまたはデータセットを比較するように私たちと共有することはありません。

リスト 1 とリスト 2 を比較したいと思います。

クイック条件付き書式で 2 列のデータを比較する

条件付き書式は、事前に定義した基準に基づいてセルまたは範囲を強調することができるようになります。これらの2つの列を視覚的にすばやく比較する最も迅速で簡単な方法は、定義済みのハイライト重複値ルールを使用することです。

データの2つの列を選択することから始めます。

[ホーム] タブから [条件付き書式] ドロップダウンを選択します。次に、[セルの強調表示ルール]を選択します。

Duplicate Values 設定ボックスが開き、書式設定を定義し、Duplicate または Unique values のいずれかを選択できます。

Duplicateを選択すると、すべての再発するエントリは選択した書式に設定されます。これで、リスト 1 の項目がリスト 2 にあることが、書式設定された項目としてすぐにわかるようになりました。また、リスト 1 にないリスト 2 の項目は、フォーマットが適用されていないため、すぐに確認できます。

ただし、固有の項目をフォーマットすることもできます。これは、[重複値] 設定ボックスから [一意] を選択することで実現できます。

この場合、2 つの異なる条件付き書式を適用しています。赤は重複を、緑はユニークな項目を示しています。

注意：データのあるセルだけを取り出したのではなく、A列からC列のすべてを取り込みました。しかし、これらのセルには条件付き書式が適用されているので、必要なセルだけを選択する方がよいでしょう。

条件付き書式の解除

すべての条件付き書式を解除するには、まず、セルまたは範囲を選択します。次に、「ホーム」リボン上の条件付き書式のドロップダウンを選択します。次に［ルールのクリア］を選択します。最後に［選択したセルのルールをクリア］を選択します。

一度に複数の条件付き書式が適用されていて、そのうちの一つだけを削除したい場合は、条件付き書式のドロップダウンリストから［ルールを管理］を選択してください。削除したいルールを選択し、[ルールの削除]を選択します。

OKボタンを押すと、ルールはルールマネージャから削除され、セルにはもはや書式が含まれません。

だからそれはあなたが2 Excel で表を比較できる最も基本的な方法である。すばやく、簡単に、そして効果的に行えます。また、この記事の後半で見ていきます。

MATCH関数を使用してExcelでデータを一致させる

Excelで2つの範囲またはリストを比較するために使用できる多くのルックアップフォーミュラがあります。最初に見ていくのはMATCH関数です。

MATCH関数は、リスト内の相対的な位置を返します。 MATCH の構文は、

=MATCH (lookup value, lookup array, Match type)

ここで lookup value は、一致するものを見つけたい値である。 lookup arrayは、一致を探すリストです。そして、Match type では、完全一致か近似一致かを選択できます。

リスト 2 のアイテムがリスト 1 にあるかどうかを確認するための一致式を記述したいと思います。

セル E3 に数式

=MATCH(C2, $A$2:$A$21,0)

この数式を入力することにより、Excel が一致を見つけた値についてはその一致位置が返されることになります。一致しない場合、戻り値は #N/A となります。

非常に頻繁に、相対位置または #N/A は私たちにとって価値がないので、これらの値を真または偽に変換する必要があります。これを行うには、論理関数を使用して、Match 数式を簡単に拡張できます。 Match は数値を返すので、ISNUMBER 関数

=ISNUMBER(MATCH(C2, $A$2:$A$21,0))

Excel 365 で MATCH または動的配列関数としての XMATCH で 2 つのリストを比較

Excel 365 を使ってリストまたはデータを比較するには、MATCH にさらに別の選択肢があります。 Excel 365 は配列で考えるので、MATCH の検索値として配列を渡すことができ、結果がこぼれてしまいます。

MATCHと動的配列で2つのリストを比較する

動的配列にまだ慣れていない方は、こちらの記事をご覧いただくとよいかと思います。 Excel 動的配列 – Excel スプレッドシートをモデル化する新しい方法: 動的配列がどのように動作し、範囲をこぼしたかについて、より深く理解するために… 続きを読む

Match 数式の唯一の変更は、セル C2 を検索値として選択する代わりに、範囲 C2:12

=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))

ご覧のように、1 つの数式が列 E に結果を流出させるということです。

2つのリストを比較するXMATCH Excel 365

Excel 365では、新しい関数XMATCHも導入されました。 MATCH関数と同じように、XMATCHはリスト内の相対位置を返します。 XLOOKUPは、古いVLOOKUP関数に代わるものですが、XLOOKUPにはさらなるパワーがあることはご存じでしょう。これは、検索モードやマッチタイプなど、式の構文に新しい条件を追加する形で提供されます。 XMATCHも前身であるMATCHに比べ、このようなパワーアップを果たしています。

XMATCH (検索値、検索配列、)

ここで、

Lookup Value は相対位置を検索する値

Lookup Array は検索値を含む行または列

マッチモードは任意です。古い MATCH 関数とは異なり、デフォルトは完全一致です。

完全一致または次の最小値
完全一致または次の最大値
ワイルドカード一致

検索モードも選択可能です。デフォルト（かつ、古い MATCH 関数の唯一のオプション）は、上から下に向かって検索することです。また、最後から最初とバイナリ検索を選択することもできます。バイナリ検索を使用する場合。 XMATCH では、MATCH で見てきたように、動的配列またはセル参照を使用して数式を作成することができます。この例では、動的配列を使用します。数式は MATCH で使用したものと非常によく似ています。ただし、XMATCH ではデフォルト設定である完全一致のために 0 を選択する必要はありません。

この場合、

=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))

ここで、not は真を偽に、偽を真に変換することになります。

表 – 範囲のサイズが変化する可能性がある Excel でリストを比較する

これまで見てきた各式では、動的ではないセルの範囲を Match 関数に選択しています。つまり、リストの 1 つに新しいデータを追加する場合、新しいデータを含めるために数式を更新する手動手順があります。これは、表に変換するためのキーボードショートカットです。セルの範囲でヘッダーを選択した場合、テーブルにヘッダーがあることを確認するためにボックスにチェックを入れます。

テーブルは、その性質上、構造化した命名法を使用します。したがって、数式を記述しているときに、テーブルから列を選択すると、セル参照ではなく、列の名前が表示されます。

リスト 1 にないリスト 2 のアイテムを見つけるために XMATCH を使用した以前の数式を見ると、テーブル参照を使用してこの関数を書き直すことができます

=NOT (ISNUMBER(XMATCH(list2,list1)))

ここで、テーブルを使用したので、いずれかのテーブルに新しい行を追加すると、新しいデータを含めるために流出範囲も拡大されるでしょう。

カスタム条件付き書式を使用してリストの違いを強調する

この記事の前半では、重複に対する定義済みのルールを使用してこれら 2 つのリストを比較する非常に迅速な方法について見てきました。しかし、カスタム条件付き書式を使用することもできます。カスタム条件付き書式についてよく知らない方は、こちらの記事を参照してください。 Excelの動的な条件付き書式設定のコツ。

ここで、違いを強調するために、2 つの異なるアプローチを見てみる必要があります。表を使用せず、違いを識別するための真/偽の数式を作成した場合、その数式のコピーを取り、これをカスタム書式設定に追加することができます。しかし、表を使用する場合は、セル参照を強制的に使用する必要があります。

数式を条件付きカスタム書式にコピーする

まず、数式のコピーを取ります。スプレッドシートで数式をテストしているので、条件付き書式で使用する前に、その数式が動作することを確認することができます。これは、セルの相対参照と絶対参照では、数式を正しく表示するのが難しい場合が非常に多いので、ベストプラクティスです。次に、「ホーム」リボンから条件付き書式設定のドロップダウンを選択し、「新しいルール」

新しい書式設定のルール設定ボックスが開き、「数式を使って書式設定するセルを決定します」を選択します。次に、数式を貼り付けて、書式の種類

これにより、両方のリストにあるすべてのセルは、選択したフォーマットにフォーマットされます。

我々はこの条件付きフォーマットを適用するセルの範囲を選択したとそれは動的ではないことを心に留めておいてください。テーブルを使用する場合、これは何も変更する必要なく更新されます！

Excel で 2 つのリストを比較するために使用する他の数式

Excel で 2 つのリストを比較するために使用できる数式はたくさんあります。 MATCHとXMATCHについてはすでに見てきましたが、ここではさらにいくつかの式について見ていきます。ルックアップ関数のどれでも、他のいくつかの関数と一緒に本当に動作します！

Excelで2つのリストを比較するVLOOKUP

VLOOKUPに慣れていない場合は、ここでそれについて読むことができます。簡単に言うと、VLOOKUPはセルから対応する値を返し、対応する値がない場合は#N/Aエラーが返されます。この例では、テキストを操作しています。そこで、VLOOKUPを実行して、テキストが返されるかどうかをテストすることができます。もし数字を使うなら、ISTEXTをISNUMBERに置き換えることができます。

関数 =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))

あるいは Excel 365 で動的配列を使っているなら、関数

=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))

XLOOKUP で Excel で 2 つのリストを比較

XLOOKUP は Excel 365 で導入されており、こちらにも詳細が掲載されています。 VLOOKUPと非常によく似ているXLOOKUPは、セルから対応する値を返し、値が見つからなかった場合の結果を定義することができます。動的配列を使用すると、関数は次のようになります。

=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))

COUNTIF で Excel で 2 つのリストを比較

COUNTIF 関数は値またはテキストが範囲内に含まれる回数をカウントします。値が見つからなければ0が返されます。

=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)

How to compare two data sets in Excel

Comparing two lists is easy enough and we have looked at several ways to do this.Now to compare COUNTIF (IF).とIFを組み合わせて真の値、偽の値を返せる。しかし、2 つのデータセットを比較することは少し難しいかもしれません。

例を見てみましょう。 2 つのデータテーブルがあり、それぞれ同じ列ヘッダーを含んでいます。画像を見ると、これらの 2 つのテーブルを一致させるには、複数の列を調べる必要があることがわかります。

複数の列を調べる必要がある場合、解決策は、データを 1 つの列に組み合わせた複合列を作成することでしょう。これにより、各行に一意の列が作成され、一致する列として使用できます

この例では、Name と DoB を組み合わせて、各テーブルに一意の ID を与えることができます

セルのコンテンツを結合する方法はたくさんありますが、この場合は単純な結合を行います。テーブルを使用しているため、数式はテーブルの命名形式を使用します。

= &”-“&

2番目のテーブルで手順を繰り返します。

ここで、データのこれらの新しい2列を一致させるには、上記の例のいずれかを使用できます。 7237>

Power Query を使用してリストまたはデータセットを比較する

また、Excel Power Query を使用してリストおよびデータセットを比較することも可能です。このビデオでは、Excels Power Query を使用して、2 つの異なるデータセットを比較または調整する方法を学習します。

究極ガイド – Excel で 2 つのリストまたはデータセットを比較する – Excel Club

目次