「別シートのデータを参照するときにいちいち関数を変更するのがめんどくさい」と思っている方も少なくないと思います。
そんな時に役立つのが「INDIRECT関数」です。
今回はこのINDIRECT関数についてご紹介していきたいと思います。
INDIRECT関数とは？
INDIRECT関数とは、直接セルの値を取ってくるのではなく、「文字列で指定された」セルの値を参照する関数です。
どういうことかというと、通常「A1」の値を取ってこようとすると「=A1」と書きますが、「=INDIRECT(“A1”)」と文字列で指定することで「A1」セルの値を取ってきます。
他にも、C1セルに「A1」と記載した上で「=INDIRECT(C1)」と関数を書くと、C1に書かれた「A1」という文字列を参照して「A1」セルの値を取ってくる事ができます。つまり間接的にセルの値を取ってくるということになります。
必ず同じ場所を指定して値を取ってきたい場合に便利です。スプレッドシートを編集していると参照セルがずれていってしまう場合がありますよね。そんな時でも必ず同じセルを指定するのに使えると思います。
また、VLOOKUP関数と組み合わせて、別シートにある複数のデータ範囲を切り替えて指定したい時に威力を発揮します。「名前付き範囲」を使うとさらに便利です。
これは後ほど使い方の具体例にてご紹介します。
INDIRECT関数の使い方
=INDIRECT（参照セルの文字列）
=INDIRECT（”A1”）
=INDIRECT（”B1:B5”）
引数について説明していきたいと思います。
・参照セルの文字列
参照セルの文字列は、”A1”のように参照したいセルを文字列で表したものです。”B1:B5”のようにレンジを指定することもできます。
INDIRECT関数の使い方の具体例
それでは、実際にINDIRECT関数使い方についてご説明していきます。
今回は、INDIRECT関数の基本的な使い方と、VLOOKUP関数・名前付き範囲と組み合わせて利用する高度な使い方についてご紹介します。
＜基本的な使い方＞
- シートを用意する
東京・大阪・名古屋の各店舗の売り上げをINDIRECT関数を使って、集計したいと思います。
- 参照セルの文字列を指定する
まず、東京店の6月の売り上げを参照します。
東京店の6月の売り上げは、「H8」セルに入力してあるので、以下のようにします。
これを繰り返すことで、表を完成させることができます。
＜VLOOKUP関数と組み合わせてデータ範囲を切り替える方法＞
- シートを用意する
今回の例では、別シートに記載された各店舗の商品別売上から6月度のみを集計シートにまとめます。
その際、VLOOKUP関数で指定するデータ範囲をINDIRECT関数を使って切り替えることで関数をコピペするだけ表を完成させることができます。
- 参照セルの文字列を指定する
まず、東京店の6月の鉛筆の売上をVLOOKUP関数を使って取得します。
東京店の6月の鉛筆売上は、「東京」シートの2列目にあるので、VLOOKUP関数で以下のように指定します。
このように、別シートを参照するには、「シート名!セル名」を入力するので、覚えておきましょう。
次に、「’東京’!A:D」と指定されたデータ範囲の部分をINDIRECT関数を使って指定します。
B3セルに「東京」と記載されているので、それを使って関数を隣にコピーしても自動的にシート名の部分が切り替わるようにしています。
参照セルと文字列を「&」で繋ぐことでここでは「’東京’!A:D」を表現しています。INDIRECT関数はこの文字列を使ってデータ範囲を参照している形になります。
これを縦横にコピーしていくのですが、セル指定が相対的なので部分的に絶対参照にすることで、セルの指定を固定することができます。
ここでは検索値のあるA列を「$A」で列固定、シート名である3行目を「B$3」で行固定しています。
これで関数をコピーする準備が整いました。これを縦横にコピーして表を完成させます。
さらにおまけとして、INDIRECT関数の中で指定したデータ範囲を「名前付き範囲」に置き換えることによってもっとシンプルにすることもできます。
あらかじめ各店舗シートの表の部分に「名前付き範囲」を定義しておきます。
例えば、「東京」シートの表を選択した状態で右クリックすると「名前付き範囲を定義」という選択肢が出てくるので、「東京」という名前をつけて完了します。
集計シートに戻り、INDIRECT関数で指定していたデータ範囲の部分を「”東京”」に変えると同じ結果を得られます。よりシンプルにデータ範囲を指定できましたね。
詳しくは、Googleヘルプページでもご確認いただけます。
https://support.google.com/docs/answer/3093377?hl=ja
INDIRECT関数のまとめ
いかがでしたか？
今回ご紹介したINDIRECT関数は間接的にセルの値を取得してくる方法です。特に、VLOOKUP関数などと組み合わせ使うことで効果を発揮します。
是非試してみてください。
