【EXCEL | INDIRECT関数】VLOOKUP関数の範囲指定を動的に処理する!

スポンサーリンク

VLOOKUP関数の範囲指定を動的に処理する!

2つのプルダウンリストを連携させる時に、入力規則で利用することで有名な、INDIRECT関数。

もともとは引数に指定した名前(文字列)のオブジェクトを返す関数です。

この関数を使うことで、VLOOKUPなど引数に範囲をしていする関数の指定範囲を動的に処理することが出来ます!

INDIRECT関数

まずはINDIRECT関数についてです。

構文

=INDIRECT(参照文字列)

引数には参照文字列を指定します。これはオブジェクトの名前のことです。

オブジェクトとは、「シート」とか「セル」とか「セル範囲」、「テーブル」など、Excelに出てくるパーツの事と思ってくれれば大丈夫です。

このパーツ(オブジェクトのこと)には、それぞれ名前を付けることが出来ます。

次のサンプル動画をご覧ください。

動画の中では、C5セル(オブジェクト)に「指定のセル」という名前をつけ、C3セルに「=指定のセル」と参照して表示させています。

通常は、A1とかB3とかいうように、セルには名前がついていますが、これは左上の「名前ボックス」変更することが出来ます。

覚えやすいように名前を変えることができるんですね。

INDIRECT関数で指定する「参照文字列」も、元々のセル(オブジェクト)の名前、例えば「A1」とか「B3」とかを指定出来ますし、変更して付けた名前(この場合「指定のセル」)も使用することが出来ます。

試しに、先程のサンプルのC8セルに

=INDIRECT("指定のセル")

C11セルに

=INDIRECT("C5")

と入力すると同じ結果になることが分かります。

そして、これは単体セルだけでなく、他のオブジェクト「セル範囲」や「テーブル」でも同じことが可能です。

セル範囲に名前を付ける

セル範囲に名前を付ける場合は、名前を付けたいセル範囲を選択して、左上の「名前ボックス」で名前を付けます。

これをINDIRECT関数で参照します。

B8セルに次の数式を入力します。

=INDIRECT("指定したセル範囲")

名前を付けた範囲「B2:C3」が表示されることが分かります。

テーブルに名前を付ける

テーブルも同じように使えます。名前の付け方が違うので、次をご覧ください。

※テーブの作り方が分からない方は先にこちらの記事の後半を御覧ください!

テーブルの名前を変更するには、テーブルの中を選択して、「テーブルデザイン」タブを表示させて、左の方に出てくる「テーブル名」のボックスの中の名前を変更します。デフォルトでは「テーブル◯◯」みたいな感じになっています。

テーブル名を「商品単価テーブル」としましょう。

これでテーブルの名前が変更されましたので、INDIRECT関数でこの名前が使えるようになります。

E1セルに、次の数式を入力します。

=INDIRECT("商品単価テーブル")

Enterで確定します。

図のように、商品単価テーブルのデータの中身が表示されます。

※見出しは入らないのでご注意ください。

INDIRECT関数でVLOOKUP関数を動的に処理する

それではやっと本題です(笑)

ここからは、ここまで紹介したINDIRECT関数を使うことで、VLOOKUP関数の検索範囲を動的に処理する方法を紹介します!

最終的に、次のようなものを作っていきます。

どのようなものかというと次のようなものです。

B4:C6に、「商品選択」「オプション選択」「金額」の項目があります。

商品の選択項目は、C4セルに商品名テーブル(B9:B13)の項目でプルダウンの入力規則を指定

オプション選択は、C5セルにC4セルで選んだ商品の種類に応じで、ハンバーガーテーブル(D9:E14)、カレーテーブル(G9:H14)、唐揚げテーブル(J9:K14)、カツ丼テーブル(M9:N14)から動的にプルダウンリストを作成するようにしています。

C4とC5のプルダウンリストを連携させるために、各テーブルには、商品名の各名前と同じテーブル名を付けています。

よく分からないという方は、こちらの記事を先にご覧ください。

上の記事にはありませんので、念のためですがプルダウンリストを連動させるときのテーブルが、2列以上のテーブルの場合、INDIRECT関数に指定する参照文字列を工夫する必要があります。

通常は

INDIRECT(テーブル名)

のところを

テーブル名[列名]

とテーブル名に続けて、[]で列名を続ける必要があります。

この場合、テーブル名と列名を同じにしているので、C5セルの入力規則には

=INDIRECT(C4&"["&C4&"]")

と入力しています。

本筋に戻ります。

そして、金額(C6セル)には、各テーブルのオプションの金額を、INDIRECT関数とVLOOKUP関数を組み合わせて動的に取得しています。

やり方

VLOOKUP関数の構文は次のようになっています。

=VLOOKUP(①検索値,②範囲,③列番号,④[検索方法])

今回の場合、C4セルで決めた商品に合わせて、VLOOKUP関数の「②範囲」を動的に変更する必要がありますね。

ここに名前の付けられたテーブルとINDIRECT関数を使います。

C6セルに入る数式は次です。

=VLOOKUP(C5, INDIRECT(C4), 2, 0)

①検索値は、C5で選択されたオプションですね。

②範囲に、「INDIRECT(C4)」を入力します。このようにすることで、C4で選択された商品名が名前についているテーブルが②範囲(テーブル)に指定されることになります。

つまり、C4でハンバーガーが選ばれている場合は、「INDIRECT("ハンバーガー")」と入ってるのと同じになります。

=VLOOKUP(C5, ハンバーガー, 2, 0)

C4でカレーが選ばれている場合は、

=VLOOKUP(C5, カレー, 2, 0)

と同じことですね。

列番号は、各テーブルの表示させたい列なので、左から2番目の金額列です。

このようにすることで、プルダウンの連動に合わせて、VLOOKUP関数の検索範囲を動的に変更することが出来ちゃいます!

選択範囲に合わせて、検索範囲を連動するというような機会は結構あると思いますので、そんなときに使えますね^_^

まとめ

VLOOKUP関数の範囲指定を動的に処理する方法を紹介しました!

ちなみに、サラッと流していますが、VLOOKUPの関数やSUMIF関数など、引数に範囲を指定する関数は、範囲にテーブルを指定することが可能です。これ、初めて知ったときは結構感動ものでした。同じように感動してくれる方がいたら嬉しいです\(^o^)/