ExcelのVLOOKUP関数とCOLUMN関数を組み合わせることにより、 データベースまたはデータテーブルの単一の行から複数の値を返すルックアップ式を作成できます。
上記の図の例では、ルックアップ式により、さまざまなハードウェアに関連するすべての値（価格、部品番号、サプライヤなど）を簡単に返すことができます。
01の10
Excel VLOOKUPで複数の値を返す
以下の手順に従って、単一のデータレコードから複数の値を返す上記の画像に見られる検索式を作成します。
ルックアップ式では、COLUMN関数をVLOOKUPの内部にネストする必要があります。
関数のネストには、最初の関数の引数の 1つとして2番目の関数を入力する必要があります。
このチュートリアルでは、VLOOKUPの列インデックス番号引数としてCOLUMN関数を入力します 。
チュートリアルの最後のステップでは、ルックアップ式を追加の列にコピーして、選択した部品の追加の値を取得します。
チュートリアルの内容
- チュートリアルデータの入力
- データテーブルの名前付き範囲の作成
- VLOOKUP関数の起動
- 絶対セル参照を使用した参照値引数の入力
- テーブル配列引数の入力
- 入れ子になったCOLUMN関数の入力
- VLOOKUP機能の完了
- 塗りつぶしハンドルでルックアップ式をコピーする
- 検索式を使用したデータの取得
02の10
チュートリアルデータを入力
チュートリアルの最初のステップは、Excel ワークシートに データを入力すること です 。
チュートリアルの手順に従うには、上記の画像に示されているデータを次のセルに入力します。
このチュートリアルで作成された検索基準とルックアップ式は、ワークシートの2 行目に入力されます。
このチュートリアルでは画像に表示される書式は含まれていませんが、ルックアップ式の仕組みには影響しません。
上記のような書式設定オプションについては、この基本的なExcel書式チュートリアルを参照してください 。
チュートリアルの手順
- 上記の画像に示されているデータをセルD1〜G10に入力します
03/10
データテーブルの名前付き範囲の作成
名前付き範囲は、数式内のデータの範囲を参照する簡単な方法です。 データのセル参照を入力するのではなく、範囲の名前を入力するだけです。
名前付き範囲を使用する2番目の利点は、ワークシートの他のセルに数式をコピーしても、この範囲のセル参照が変更されないことです。
したがって、範囲名は、 絶対セル参照を使用して式をコピーするときのエラーを防ぐ代わりの方法です。
注：範囲名には、データ（行4）の見出しまたはフィールド名は含まれず、データ自体のみが含まれます。
チュートリアルの手順
- ワークシート内のセルD5〜G10を強調表示して選択します
- 列Aの上にある名前ボックスをクリックします
- 名前ボックスに "Table"（引用符なし）と入力します。
- キーボードのEnterキーを押します
- セルD5からG10に範囲名「テーブル」が追加されました。 このチュートリアルの後半で、VLOOKUP テーブル配列引数の名前を使用します
04/10
VLOOKUPダイアログボックスを開く
ルックアップ式をワークシートのセルに直接入力することは可能ですが、このチュートリアルで使用しているような複雑な式の場合は、 構文をそのまま維持するのが難しいことが多くあります 。
この場合、VLOOKUP ダイアログボックスを使用することもできます 。 ほとんどすべてのExcelの関数には、それぞれの関数の引数を別々の行に入力できるダイアログボックスがあります。
チュートリアルの手順
- ワークシートのセルE2をクリックします - 2次元ルックアップ式の結果が表示される場所
- リボンの 式タブをクリックします。
- リボンのLookup＆Referenceオプションをクリックして、機能ドロップダウンリストを開きます
- リスト内のVLOOKUPをクリックして、関数のダイアログボックスを開きます
05の10
絶対セル参照を使用した参照値引数の入力
通常、 ルックアップ値はデータテーブルの最初の列のデータフィールドと一致します。
この例では、 参照値は、情報を検索するハードウェア部分の名前を参照します 。
ルックアップ値の データの許容タイプは次のとおりです。
この例では、部品名がどこにあるかを示すセル参照を入力します（セルD2）。
絶対セル参照
チュートリアルの後のステップでは、セルE2のルックアップ式をセルF2とG2にコピーします。
通常、Excelで式をコピーすると、セル参照が新しい場所に反映されます。
このような場合、式F2がセルF2とG2にエラーを作成してコピーされると、 参照値のセル参照D2が変更されます。
エラーを防ぐために、セル参照D2を絶対セル参照に変換します 。
数式をコピーすると、絶対セル参照は変更されません。
絶対セル参照は、キーボードのF4キーを押すことによって作成されます。 そうすることで$ D $ 2のようなセル参照の周りにドル記号が追加されます
チュートリアルの手順
- ダイアログボックスの lookup_value行をクリックします。
- このセル参照をlookup_value行に追加するには、セルD2をクリックします。 これは私たちが情報を求めている部品名をタイプするセルです
- 挿入ポイントを移動せずに、キーボードのF4キーを押してD2を絶対セル参照$ D $ 2に変換します
- VLOOKUP関数ダイアログボックスをチュートリアルの次のステップのために開いたままにします
06の10
テーブル配列引数の入力
テーブル配列は、ルックアップ式が検索して必要な情報を見つけるためのデータのテーブルです。
テーブル配列には少なくとも2列のデータが含まれている必要があります 。
- 最初の列にルックアップ値の引数が含まれています（チュートリアルの前のステップ）
- 2番目の列、および追加の列は、ルックアップ式で検索され、指定した情報が検索されます。
テーブル配列の引数は、データテーブルのセル参照を含む範囲または範囲名として入力する必要があります 。
この例では、チュートリアルの手順3で作成した範囲名を使用します。
チュートリアルの手順
- ダイアログボックスの table_array行をクリックします。
- この引数の範囲名を入力するには "Table"（引用符なし）と入力してください
- VLOOKUP関数ダイアログボックスをチュートリアルの次のステップのために開いたままにします
07/10
COLUMN関数のネスト
通常、VLOOKUPはデータテーブルの1つのカラムからデータを返すだけで、このカラムはカラムインデックス番号の引数で設定されます。
しかし、この例では、データを返す3つのカラムがあるので、ルックアップ式を編集することなくカラムインデックス番号を簡単に変更する方法が必要です。
これは、COLUMN関数が入る場所です。 列インデックス番号引数として入力すると、チュートリアルの後半でルックアップ式がセルD2からセルE2およびF2にコピーされると、その値が変更されます。
入れ子関数
したがって、COLUMN関数は、VLOOKUPの列インデックス番号 引数として機能します 。
これは、ダイアログボックスのCol_index_num行にVLOOKUP内のCOLUMN関数をネストすることによって実行されます。
COLUMN機能を手動で入力する
関数を入れ子にすると、Excelは2番目の関数のダイアログボックスを開いて引数を入力することを許可しません。
したがって、COLUMN関数は、 Col_index_num行に手動で入力する必要があります。
COLUMN関数には引数が1つしかありません。 参照引数はセル参照です。
COLUMN関数のリファレンス引数の選択
COLUMN関数の仕事は、 Reference引数として与えられた列の番号を返すことです。
換言すれば、列文字を、列Aが第1列であり、列Bが第2列であるというような数に変換する。
返されるデータの最初のフィールドはアイテムの価格です（データテーブルの第2列にあります）。列Bの任意のセルのセル参照を参照引数として選択して番号2を取得することができますCol_index_num引き数。
チュートリアルの手順
- VLOOKUP関数ダイアログボックスで、 Col_index_num行をクリックします
- 関数名の列に続けて、丸い括弧 " （ "
- ワークシートのセルB1をクリックして、そのセル参照を参照引数として入力します
- 閉じ丸括弧 " ） "を入力してCOLUMN関数を完了します
- VLOOKUP関数ダイアログボックスをチュートリアルの次のステップのために開いたままにします
08の10
VLOOKUP範囲参照引数の入力
VLOOKUPのRange_lookup引数は、VLOOKUPがLookup_valueと正確に一致するか近似したものかを検索するかどうかを示す論理値 （TRUEまたはFALSEのみ）です。
- TRUEの場合、またはこの引数を省略した場合、VLOOKUPはLookup_valueと完全に一致するか、完全一致が見つからない場合はVLOOKUPが次に大きい値を返します。 これを行う数式のために、Table_arrayの最初の列のデータを昇順でソートする必要があります。
- FALSEの場合、VLOOKUPはLookup_valueと完全一致を使用します。 ルックアップ値と一致するTable_arrayの最初の列に2つ以上の値がある場合、最初に見つかった値が使用されます。 完全一致が見つからない場合は、＃N / Aエラーが返されます。
このチュートリアルでは、特定のハードウェアアイテムに関する特定の情報を探しているので、Range_lookupをFalseに設定します。
チュートリアルの手順
- ダイアログボックスの Range_lookup行をクリックします。
- この行にFalseという語を入力すると、VLOOKUPが、探しているデータと完全に一致するようにしたいことを示します
- [OK]をクリックして検索式を完了し、ダイアログボックスを閉じます
- セルD2にルックアップ基準をまだ入力していないので、 ＃N / AエラーはセルE2に存在します
- このエラーは、チュートリアルの最後のステップでルックアップ基準を追加するときに修正されます
09/10
塗りつぶしハンドルでルックアップ式をコピーする
ルックアップ式は、一度にデータテーブルの複数の列からデータを取得するためのものです。
これを行うには、ルックアップ式が情報を必要とするすべてのフィールドに存在する必要があります。
このチュートリアルでは、Lookup_valueとして部品名を入力すると、データテーブルのカラム2,3,4からデータを検索します。これは価格、部品番号、サプライヤの名前です。
データはワークシートの規則的なパターンでレイアウトされているので、 セル E2の参照式をセルF2とG2にコピーできます。
数式がコピーされると、Excelは式の新しい場所を反映するために、COLUMN関数（B1）の相対セル参照を更新します。
同様に、Excelは絶対セル参照 $ D $ 2と名前付き範囲 テーブルを変更しません。数式はコピーされます。
Excelでデータをコピーするには複数の方法がありますが、最も簡単な方法は、 塗りつぶしハンドルを使用することです。
チュートリアルの手順
- ルックアップ式があるセルE2をクリックしてアクティブセルにします
- 右下の黒い四角の上にマウスポインタを置きます。 ポインタがプラス記号 " + "に変わります - これは塗りつぶしハンドルです
- マウスの左ボタンをクリックし、塗りつぶしハンドルをセルG2にドラッグします
- マウスボタンを離すと、セルF3に2次元ルックアップ式が含まれているはずです
- 正しく行われた場合、セルF2とG2には、セルE2に存在する＃N / Aエラーも含まれているはずです
10の10
ルックアップ基準の入力
ルックアップ式が必要なセルにコピーされると、それを使用してデータテーブルから情報を取り出すことができます。
これを行うには、検索する項目の名前をLookup_valueセル（D2）に入力し、キーボードのEnterキーを押します。
検索が完了したら、ルックアップ式を含む各セルに、検索するハードウェア項目に関する別のデータが含まれているはずです。
チュートリアルの手順
- ワークシートのセルD2をクリックします。
- セルD2にウィジェットを入力し、キーボードのEnterキーを押します
- 次の情報は、セルE2〜G2に表示する必要があります。
- E2 - $ 14.76 - ウィジェットの価格
- F2 - PN-98769 - ウィジェットの部品番号
- G2 - Widgets Inc. - ウィジェットのサプライヤの名前
- VLOOKUP配列式をさらにテストするには、セルD2に他のパーツの名前を入力し、セルE2〜G2の結果を観察します
#REF！などのエラーメッセージが表示された場合は、 セルE2、F2、またはG2に表示される場合、このVLOOKUPエラーメッセージのリストは、問題のある場所を特定するのに役立ちます。