【Excel】VLOOKUP関数の検索範囲を外部参照にする

Excel

今回はExcel関数のなかでも使用頻度が高いと思われる、VLOOKUP関数をさらに使いやする方法を紹介します。

INDIRECT関数を使ってセルの値を参照する

VLOOKUP関数の検索範囲を外部参照にするために欠かせないのが、INDIRECT関数です。

INDIRECT関数は、引数で与えた文字列をセル番地として認識し、そのセル番地の持つ値を返します。

INDIRECT関数とは

《INDIRECT関数書式》
 INDIRECT(参照文字列参照形式

参照文字列には参照するセルを指定し、参照形式は参照の形式(A1形式かR1C1形式)を指定します。

参照形式はTRUEの場合がA1形式、FALSEの場合がR1C1形式となります。また、省略することも可能で、省略した場合はA1形式となります。

例えば、3行目・4列目のセルをそれぞれの方法で示す場合次のように指定します。

《参照形式による表記方法の違い》
A1形式  :D3
R1C1形式:R3C4

VLOOKUP関数とINDIRECT関数を組み合わせる

VLOOKUP関数の検索範囲をINDIRECT関数を用いて外部参照とする方法です。

手順は下記の通りです。

①VLOOKUP関数の検索範囲を任意のセルへ書き込む
②VLOOKUP関数の検索範囲の引数に①のセル番地を参照文字列としたINDIRECT関数を組み込む

これでVLOOKUP関数の検索範囲が外部参照となりました。

【応用編】便利?な使い方。

上記の方法で、VLOOKUP関数の検索範囲を外部参照とすることができました。しかし、セル内の一部分だけを書き換えて検索範囲を変更する場合には少し不便です。

そこで、検索範囲を下図のように指定するようにすると便利です。

このようにすると、検索範囲を細かく変更できるので便利です。また、シート名を検索範囲に追加しておけば異なるシートからも簡単に検索をできます。

実際の使用例

使用例です。下図のように異なるシートにそれぞれ表が作成されており、それらの表から対象の金額を検索し、一つの表にまとめるケースを想定します。

検索結果を取りまとめる表は、下のようになります。

それでは、手順を説明します。

まず、検索結果を取りまとめるシートにVLOOKUP関数の検索範囲を記載した表と、検索結果を取りまとめる表を作成します。

つぎに、検索結果を取りまとめる表に検索対象と検索対象が記載されたシート名を記入します。

VLOOKUP関数の検索結果を表示する列に、数式を記入します。

ポイントは、上記で記入した「検索対象があるシート名」を利用してVLOOKUP関数の検索範囲の表から検索範囲を参照し、それをVLOOKUP関数の検索範囲とすることです。

イメージとしては下図の通りです。

これを実行するために、表「検索結果」の値段列に記載する数式は次のようにします。

=VLOOKUP(C14,INDIRECT(VLOOKUP(B14,$B$7:$G$9,6,FALSE)),2,FALSE)

VLOOKUP関数の検索範囲の引数に、INDIRECT関数とVLOOKUP関数を組み合わせたものを指定します。

引数として与えたVLOOKUP関数は、検索範囲の表からシート名に対応した検索範囲を検索し、INDIRECT関数によって値としてもとのVLOOKUP関数にわたされます。

このようにすることで各シートの検索範囲を簡単に変更することができ、さらに取りまとめる表で使用する関数もすべて同じ数式を使用することができます。(シートごとに変更する必要なし)

デメリットとしては、検索範囲を指定する表の存在によって見た目が少し悪くなることです。見た目については、表の位置を変更するなど工夫すればすっきりさせることも可能だと思います。

まとめ

INDIRECT関数を用いてVLOOKUP関数の検索範囲を外部参照にする方法を紹介しました。使い方を覚えれば、結構便利ですので参考にしてみてください。

コメント

タイトルとURLをコピーしました