関数紹介indirect

僕が最も素晴らしい関数だと思っているarrayformula関数の相棒とも言える関数のindirectの紹介記事です。

まずはgoogle先生のリファレンスから

文字列で指定したセル参照を返します。 詳細

相変わらず全く意味のわからない説明ですね(;^_^A ｱｾｱｾ･･･

事例を使って説明するとわかりやすいかと思うので、以下の様な事例を用意しました。

野菜というシートにこのような情報が入力されているとします。

B1:C6までの情報をどこか別の場所に貼り付けたいと考えた時以下のように記述することで、indirectの()の中で指定している範囲の情報を貼り付けることができるのがこの関数の特徴です。

さてこれだけ聞くとコピペするのと何が違うのか？とか=B1としてオートフィルするのと何が違うのか？という話になりそうですよねw

コピペとの違いは関数なので、今回のケースだとB1:C6の内容が書き換わった場合、今回関数を入力したB8:C13の範囲の内容も自動で置き換わるという点です。

=B1からのオートフィルとの違いは？というと別の事例を出して説明したいと思います。

例えば、上記の「野菜」というシートの他に「果物」というシートに商品名と販売単価が入力されているシートがあるとします

かつこれらの販売情報が一つのシートにまとめられているとします。

こんな時単価を入れるならどのように行いますか？

if関数やiferror関数などを使い分岐させるの一つの方法かと思いますが、果物や野菜の他に「肉類」や「魚類」「嗜好品」など項目が増えてきた時ifだと項目が増える度に分岐を増やす必要があり、関数が大変なことになってしまうかと思います。

ピーマンという商品の単価を調べる時は野菜というシート名を参照先として指定したいので、通常通り関数を記述するなら以下のようになります。

=vlookup(B3,'野菜'!B2:C6,2,false)

この'野菜'の部分がA列のシート名ごとに野菜と果物が切り替わってくれるということが今回実現したいことです。

先ほどは""で囲った文字列で範囲を指定しました。

参考：""で囲った内容をspreadsheetは文字列であると認識します

indirectの()の中で""で囲わずにセルを指定するとセルの中の文字列を参照するという機能を持っています。

例えば以下のようにB7セルに果物!B1:C6という情報を入力しておきます。

B8セルには画像のように=indirect(B7)という関数を入力します。

すると=indirect("果物!B1:C6")と入力した時と同じ情報を取得することができます

indirectの()の中では&で繋ぐことで文字列とセルの中身を同時に参照させることができます。

先ほどはB7セルに果物!B1:C6と入力しましたが、B7セルに入力する情報は「果物」だけにしてB8セルには以下の関数を入力します

=indirect(B7&"!B1:C6")

vlookupの二つ目の引数には以下の上記のようにシート名となるA列とセルの範囲を指定してあげることで、先ほどの野菜と果物の切り替えを以下の画像のようにして実現することが可能です。

vlookupの2つ目の引数である参照先はindirectのような範囲を結果として返す関数を利用することができます。

上記のようにindirectで参照先のシート名と参照先のセル範囲を指定することで野菜シートと果物シートを切り替えながら、参照先を指定することができるようになります。

オートフィルした結果の画像は以下の通り

この関数が便利なところは文字列で指定しているので、絶対参照と同じ効果を持つ点です。そのため、$など付け加えてここだけを見てほしいということを恣意的に指定する必要がないということもメリットです。

応用

ちなみに最後に個数と単価ででたら販売価格を出したいですよね！

arrayformulaという関数とindirectを組み合わせることで販売価格を自動で補完してくれるようにすることもできます！

arrayformulaの関数で若干触れはしたのですが、arrayformulaとindirectとcountaのコンボは別記事で丁寧に紹介した方がいいですね…