Vlookup・Xlookup等でシート参照する時はIndirect関数がおススメ!その理由は?

未分類

今回のテーマはIndirect関数です。
多くの人はあまり使うことのない関数かもしれません。
しかし、この関数を知っておくとVlookup(Index&Match)・Xlookupで検索する時に、とある事にすごく役立ちます。

今回は郵便局から提供されている、郵便番号を住所から抽出する想定で説明していきます。

今回のポイントの画像

どんなところで役立つのか。そのオススメポイントをご紹介いたします。

Indirect関数とは?

指定される文字列への参照を返します。 セル参照はすぐに計算され、結果としてセルの内容が表示されます。 INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。

Microsoft サポート引用

式はINDIRECT(参照文字列, [参照形式])となっています。
重要なのは参照文字列で、参照形式は、A1形式もしくはR1C1形式のどちらかを選択するようになっていますが、ほとんどの方はA1形式と思いますので、ここは入力不要です。

※A1=R1C1でセルを指しています。
Row(行)1 Column(列)1なので=セルA1です。

正直Indirect関数の説明はわかりづらいと思いますので、「Indirect関数が役立つところとは?」の説明で詳しくご紹介いたします。

Indirect関数が役立つところとは?

以下の2つのポイントがあります。

  • 関数がシンプルになりわかりやすくなる
  • 間違えて参照元を消しても復旧が簡単

関数がシンプルになり、わかりやすくなる

例えばシート参照する時であれば「Sheet1!」、テーブル参照であればテーブル1[[#すべて],[項目名]]といったようにセルに設定する必要があります。
これを関数の式内に設定するのではなく、Indirect関数を使うと、別のセルもしくはシートに設定しておくことができます。

下図がサンプルです。
B2セル(郵便番号テーブルの表全体)とC2セル(郵便番号テーブルの住所を連結した項目)に郵便番号マスタのシートから参照したいテーブルの情報をそれぞれ入力しています。

今回のポイントの画像

郵便番号マスタのシートはこちらです。
こちらは日本郵便局さんが提供している、郵便番号のCSVデータをインポートしたものです。
※Column16は私がSheet1の住所をヒットさせるために追記したものです。

郵便番号マスタ

本題に戻りまして、通常郵便番号の情報を取得しようとすると、以下のような式になります。

=INDEX(テーブル_KEN_ALL[#すべて],MATCH([@番地前抜き出し],テーブル_KEN_ALL[[#すべて],[Column16]],0),3)

Indirect関数を使うとこのようになります。

=INDEX(INDIRECT($B$2),MATCH([@番地前抜き出し],INDIRECT($C$2),0),3)

関数の式がシンプルで見やすくなったかと思います。
見やすくなった理由はセルB2とC2にそれぞれ、参照元のテーブルの文字列を設定しています。

ポイント画像

続いてはもう一つのポイント「間違えて参照元を消しても復旧が簡単」をご紹介いたします。

間違えて参照元を消しても復旧が簡単

Index関数・Vlookup・Xlookupなどの別のシートにあるものを検索する関数の大敵は対象のシート、テーブルを削除してしまう事です。

削除すると、#REF!というエラーになります。計算する参照元が削除されたために起こるエラーです。
Indirectを使わないケースですと、仮に参照元のシートを復活させても、式は入力しなおさなくてはなりません。

しかし、Indirect関数を使用していると、その必要がありません。

Indirect使用しないケース

参照元シート削除後

Indirect使用しないケースー参照元シート削除後

参照元シートインポート後

Indirect使用しないケースー参照元シートインポート後

Indirectを使用するケース

参照元シート削除後

Indirect使用するケースー参照元シート削除後

参照元シートインポート後

Indirect使用するケースー参照元シートインポート後

以上2つ目のポイント「間違えて参照元を消しても復旧が簡単」でした。

まとめ

いかがでしたでしょうか。

今回のケースは出力したいデータが検索値よりも左側にあるので、Vlookupは使えませんでしたが、Xlookupなど検索をする関数においては同じような効力を発揮します。特に郵便番号マスタのようにデータがとても多い場合はIndirect関数はおススメです。

ぜひ活用してみて下さい。

コメント