Excelでデータを照合するさまざまな方法
Excelでデータを照合するにはさまざまな方法があります。同じ列のデータを照合する場合、たとえば、重複をチェックする場合は、[ホーム]タブから条件付き書式を使用できます。それ以外の場合は、2つまたは2つのデータを照合する必要があります。 if関数のような条件関数を使用できるより多くの異なる列。
- 方法＃1-Vlookup関数の使用
- 方法2–インデックス+一致関数の使用
- 方法3–独自のルックアップ値を作成する
それでは、それぞれの方法について詳しく説明しましょう。
この一致データExcelテンプレートはここからダウンロードできます–一致データExcelテンプレート＃1-VLOOKUP関数を使用してデータを照合する
VLOOKUPは、データテーブルから必要な情報を取得するために使用されるだけでなく、調整ツールとしても使用できます。データの調整または照合に関しては、VLOOKUP式がテーブルをリードします。
例については、以下の表をご覧ください。
ここには2つのデータテーブルがあります。1つはデータ1で、もう1つはデータ2です。
次に、2つのテーブルのデータが一致しているかどうかを調整する必要があります。データを照合する最初の方法は、ExcelのSUM関数を2つのテーブルに変換して、総売上を取得することです。
データ1-表
データ2–表
テーブルのSaleAmount列の両方にSUM関数を適用しました。最初のステップ自体で、値の違いがわかりました。データ1の表は2,16,214の総売上高を示し、データ2の表は2,10,214の総売上高を示しています。
次に、これを詳細に調べる必要があります。それでは、日付ごとにVLOOKUP関数を適用してみましょう。
テーブル配列をデータ1の範囲として選択します。
2番目の列のデータが必要であり、ルックアップの範囲はFALSE、つまり完全一致です。
出力を以下に示します。
次のセルで、到着値から元の値を差し引きます。
差し引いた後、結果はゼロになります。
次に、数式をコピーしてすべてのセルに貼り付け、分散値を取得します。
セルG6とG12では違いがあります。
データ1では2019年3月4日の日付の12104があり、データ2では同じ日付の15104があるため、3000の違いがあります。
同様に、データ1の2019年3月18日の日付では、19351があり、データ2では10351であるため、差は9000です。
＃2 – INDEX + MATCH関数を使用してデータを照合する
同じデータに対して、INDEX + MATCH関数を使用できます。これは、VLOOKUP関数の代わりに使用できます。
指定された行番号に基づいて、選択した列から値を取得するために使用されるINDEX関数。行番号を指定するには、LOOKUP値に基づいてMATCH関数を使用する必要があります。
F3セルでINDEX関数を開きます。
結果の列範囲、つまりB2からB14として配列を選択します。
行番号を取得するには、次の引数としてMATCH関数を開きます。
ルックアップ値をD3セルとして選択します。
次に、データ1の販売日列としてルックアップ配列を選択します。
一致タイプで「0–完全一致」を選択します。
2つの角かっこを閉じ、Enterキーを押して結果を取得します。
これも、VLOOKUPのみと同じ結果になります。同じデータを使用しているので、そのまま数値を取得しました
＃3-独自のルックアップ値を作成する
これで、Excel関数を使用してデータを照合する方法を見てきました。ここで、リアルタイムのさまざまなシナリオを確認します。この例では、以下のデータを見てください。
上記のデータには、上記のようにゾーンごとおよび日付ごとの売上データがあります。データ照合プロセスを再度実行する必要があります。前の例のようにVLOOKUP関数を適用してみましょう。
多くの差異がありました。それぞれのケースをケースバイケースで調べてみましょう。
セルI5では、8300の分散が得られました。メインテーブルを見てみましょう。
メインテーブルの値は12104ですが、VLOOKUP関数から20404の値を取得しました。これは、VLOOKUPが最初に見つかったルックアップ値の値を返すことができるためです。
この場合、ルックアップ値は日付、つまり2019年3月20日です。同じ日付の北ゾーンの上記のセルでは、値が20404であるため、VLOOKUPは東ゾーンでもこの値を返しました。
この問題を解決するには、一意のルックアップ値を作成する必要があります。データ1とデータ2の両方で、ゾーン、日付、販売額を組み合わせます。
データ1-表
データ2–表
これで、ゾーン、販売日、販売額を組み合わせた値を使用して、ゾーンごとに一意の値を作成しました。
これらの一意の値を使用して、VLOOKUP関数を適用できます。
すべてのセルに数式を適用すると、すべてのセルで分散がゼロになります。
このように、Excel関数を使用することで、データを照合し、分散を見つけることができます。式を適用する前に、正確な調整のためにルックアップ値の重複を確認する必要があります。上記の例は、ルックアップ値の重複値の最良の例です。このようなシナリオでは、独自のルックアップ値を作成して結果を取得する必要があります。