2つ以上の基準列でのVLOOKUP-ExcelUniversity

2つ以上の基準列でVLOOKUP関数を使用しようとしたことがある場合は、すぐに次のことがわかります。その目的のために構築されたのではありません。幸い、何を返したいかによっては、VLOOKUPの代わりに機能する関数がもう1つあります。

複数列のルックアップの目的

まず、目的を確認してみましょう。サンプルワークブック。会計システムからいくつかの情報をエクスポートしました。これは基本的に、クラス別およびアカウント別の月のトランザクション合計を要約したものです。エクスポートのサンプルを以下に示します。

このエクスポートされたデータから、に基づいて選択した金額を取得します。クラスとアカウントの列。金額を取得して、下の図のような小さなレポートに配置します。

VLOOKUPに精通している場合結局のところ、これはルックアップタスクであるため、この関数を使用してレポートを作成しようとするのは自然なことです。そして、ルックアップタスクは従来のルックアップ関数で最もよく解決されます…そうですか？まあ、それは異なります。取得しようとしているものによって異なります。

ルックアップの条件付き合計

金額などの数値を取得しようとしている場合、従来のルックアップ関数では取得できない場合があります。あなたの最善の策になります。これが理由です。 Excel 2007以降、Microsoftには条件付き加算関数SUMIFSが含まれていました。この複数条件の合計関数は、数値の列を合計するように設計されており、1つ以上の条件を満たす行のみが含まれます。ドットはまだ接続され始めていますか？

このアイデアを目前のタスクに適用すると、この条件付き合計関数を使用してレポート値を取得できることがすぐにわかります。

SUMIFS関数の最初の引数は、合計範囲、つまり追加する数値の列です。この場合、返したい値を持つ列。残りの引数は、基準範囲と基準値のペアで提供されます。

関数について次の用語で考えると役立ちます。この列（引数1）を合計し、この列が含まれる行のみを含めます。 （引数2）はこの値（引数3）に等しく、この列（引数4）はこの値（引数5）に等しく、ここで…など、最大127ペアです。

したがって、レポートにデータを入力するために、エクスポートから金額の値を取得し、次に示すようにクラスとアカウントの列を照合します。

同じクラスとアカウントを持つ複数の行がある場合、SUMIFS関数は一致するすべてのアイテムの合計を返します。

ご覧のとおり、値がが数値を返そうとしている場合、SUMIFS関数を使用すると、複数列のルックアップを簡単に実行できます。しかし、返そうとしている値が数値でない場合はどうなるでしょうか。次に、以下で説明する従来のルックアップ関数を使用する必要があります。

SUMIFSメソッドでのVLOOKUPの使用

1つの方法は、単一の数式でVLOOKUPとSUMIFSを使用することです。基本的に、VLOOKUPの最初の引数としてSUMIFSを使用します。このメソッドについては、ExcelUniversityのこの投稿で詳しく説明しています。

CONCATENATEメソッドでのVLOOKUPの使用

数値ではなくテキスト文字列を返そうとしている場合、または次のバージョンを使用している場合SUMIFSを備えていないExcelの場合、おそらくVLOOKUPなどの従来のルックアップ関数をCONCATENATE関数と一緒に使用して、単一の一意のルックアップ列を生成することに固執します。このアプローチはかなりよく文書化されていますが、基本的な考え方は次のようになります。最初に単一のルックアップ列を作成してから、VLOOKUPを使用します。

次に示すように、例は従業員リストです。

以下に示す小さなレポートの従業員リストから状態を取得する必要があります：

返そうとしている値、状態は数値ではなくテキスト文字列であるため、SUMIFS関数を使用できません。したがって、VLOOKUPとCONCATENATEを使用する必要があります。

基本的に、結合されたルックアップ値を作成するヘルパー列を作成することから始めます。これは、CONCATENATE関数または連結演算子（&）を使用して簡単に実行できます。この新しいルックアップ列は、以下の列Bに示されています。

これで、で使用できる単一のルックアップ列ができました。 VLOOKUPなどの従来のルックアップ関数。以下に示すように、新しいルックアップ範囲内で結合された名前をルックアップすることで、レポートにデータを入力できます。

これと同じアプローチ2つ、3つ、またはそれ以上のルックアップ列を考慮する必要がある場合に使用できます。

結論

戻り値が数値の場合に複数列のルックアップを実行できることに加えて、SUMIFS関数には従来のルックアップ関数と比較して追加の利点があります。 たとえば、一致する値が見つからない場合はゼロを返し、すべての一致の合計を返し、比較演算子をサポートし、ルックアップ列と戻り列の間に新しい列が挿入されても壊れません。

したがって、ルックアップタスクを実行するためにVLOOKUP関数を無効にしようとしている場合は、代わりにSUMIFSの使用を検討してください。 信じられないかもしれませんが、SUMIFS関数はすばらしいルックアップ関数になります。

複数列のルックアップに他の好ましいアプローチがある場合は、もっと聞きたいです…以下にコメントを投稿してください。

サンプルファイル

上記のスクリーンショットの生成に使用したワークブックを試してみたい場合は、サンプルファイルをダウンロードしてください：

MultColumnLookup