Microsoft ExcelでVLOOKUP()を使用して不足しているレコードを検索する方法 - TechRepublic

Microsoft Excelのグループ分け方法 — 画像: monticellllo/Adobe Stock

Microsoft Excelでは、リストを比較するために様々なソリューションを使ってきました。1つまたは複数の列にまたがる重複を検出するソリューションもあれば、一意の値を見つけることに重点を置くソリューションもあります。しかし、Excelで2つのデータセットを比較し、一方のデータセットにレコードが欠落していないかどうかを確認したい場合はどうすればよいでしょうか？

参照: Microsoft Excel: このオンライントレーニングでエキスパートになりましょう (TechRepublic Academy)

例えば、Excelで、入庫した発注書のセットと、配布された発注書の包括的なリストを比較したいとします。幸いなことに、これは思ったほど難しくありません。このチュートリアルでは、ExcelでVLOOKUP()を使用して、包括的なリストと比較した際に、あるデータセット内の欠落レコードを検索する方法を説明します。

私はWindows 10 64ビット版でMicrosoft 365デスクトップを使用していますが、どのバージョンでも使用できます。また、Web版ExcelはVLOOKUP()をサポートしています。ご参考までに、デモ用の.xlsxファイルと.xlsファイルをダウンロードしていただけます。

Excel で解決しようとしている問題は何でしょうか?

継続的なサポートを提供している会社であれば、おそらく発注システムについてよくご存知でしょう。顧客から電話がかかってきてリクエストがあった場合、空白の発注書を受け取り、詳細を記入してリクエストの処理に進みます。従業員がリクエストを処理すると、発注書に記入してシステムに返送します。ここでは、発注書が処理中の場合は「オープン」、完了した場合は「クローズ」という言葉を使用します。クローズリストはオープンリストのサブセットと考えることができます。現在ではほとんどのシステムがオンライン化されているため、紙の発注書は過去のものとなっていますが、簡単な例として挙げることができます。

図Aには2つのテーブルオブジェクトが示されています。左側のPODistributedは、スタッフに配布された発注書（未処理の発注書）を追跡します。右側のPOReceivedは、発注書とその返却日（ジョブが完了したことを示す）のリストです。これらは完了した発注書です。

図A

These two data sets in Excel represent a simple purchase order system. — Excel のこれら 2 つのデータセットは、単純な注文システムを表しています。

現状では、2つのテーブル間の照合は行われていません。完了リストに一致する番号がない場合、つまり発注書と顧客からのリクエストがまだ処理中である場合でも、未完了の発注書をハイライト表示する手段が必要です。リストが短い場合でも、視覚的に適当に入力するとミスを犯す可能性が高くなります。

必要なことがわかったので、Excel に VLOOKUP() 関数を追加して、未処理の注文書を公開してみましょう。

参照: すべてのユーザーが習得すべき Excel のヒント 98 選 (TechRepublic)

Microsoft ExcelでVLOOKUP()を使用して2つのデータセットを比較する方法

VLOOKUP() 関数は、多くの Excel アプリの基盤となっています。Tables は新しい XLOOKUP() などの動的配列関数をサポートしていないため、このソリューションでは古い VLOOKUP() を使用します。

Excel の VLOOKUP() 関数は次の構文を使用します。

VLOOKUP(lookupvalue, tablearray, colindex, [rangelookup])

lookupvalue 引数は一致させる値を指定し、tablearray 引数は lookupvalue を検索する範囲を指定します。colindex 引数は関数が一致を見つけた場合の戻り値を指定します。また、省略可能な rangelookup 引数は完全一致を指定します。この省略可能な引数に FALSE を指定すると、lookupvalue に一致する値がない場合、VLOOKUP() は #N/A 値を返します。これが期待値です。

続行するには、開いているリストの PO Distributed シートの D3 に次のいずれかの関数を入力し、残りのセルにコピーします。

=VLOOKUP([@[PO Number]],'PO Received'!$B$3:$B$13,1,FALSE)

=VLOOKUP('PO Distributed'!B3,'PO Received'!$B$3:$B$13,1,FALSE)

Tableオブジェクトを使用していない場合は後者を使用してください。どちらの場合も、!$B$3:$B$13 参照は絶対参照である必要があります。

Excelでは、Tableオブジェクトが自動的に展開されます（図B）。関数が完了済みの発注書リストとメインの発注書リストに一致するレコードを見つけられなかったレコードがいくつかあります。これらのレコードはまだ開いています。どの発注書が開いているかを把握することは、特に長期間開いている場合は重要です。

図B

The VLOOKUP() function returns an error message when it finds no match to the corresponding PO Number. — VLOOKUP() 関数は、対応する PO 番号に一致するものが見つからない場合にエラーメッセージを返します。

この関数は少し複雑なので、4 行目のレコードを使用してどのように評価されるかを見てみましょう。

=VLOOKUP('発注書配布済み'!B3,'発注書受領済み'!$B$3:$B$13,1,FALSE)

=VLOOKUP(102,{103;101;106;104;110;109;107;108;113;115;111},1,FALSE)

=VLOOKUP(102,{103;101;106;104;110;109;107;108;113;115;111},102,FALSE)

完了した発注書の配列には値102が含まれていないため、FALSE引数により関数は#N/Aを返します。もし配列に値102が含まれていれば、関数は102を返します。

#N/A 値の意味がわかっていれば、ここで作業を中断するのは簡単です。しかし、Excel の VLOOKUP() 関数の結果に基づいて条件付き書式ルールを追加することで、VLOOKUP() 関数がこのエラーを返すレコードを強調表示することができます。条件付き書式ルールを追加するには、以下の手順に従ってください。