「EXCELなんか無くなって、みんなGoogleスプレッドシートに移行すればいいのに」と、いつも本気で思っている、たぬ（@tanuhack）です！
EXCELやスプレッドシートで作ったシートに、後日データを挿入した後、シートの関数を手直す作業を繰り返していませんか？
例）SUM関数を使って、A1行から最終行までの和を求めたい
データをA10の下に挿入する予定がないと確信している場合は、この方法が最適だと思います。
ですが、今後もA10の下にデータを挿入する予定があるのであれば、この方法は最善ではありません。
なぜならデータを挿入した後に数式を手動で直さないといけないからです。
「な〜んだ。データを挿入したら、数式直すだけっしょ。楽勝じゃん。」
とか思ってませんか？そうであれば、その考えは直ちに修正するべきです。
もしデータを挿入して直す数式が100個とか10000個出てきても、同じことが言えますか？
さっきと同じことが言える人は、一生仕事で他人の足を引っ張り続ける無能でしょうね。
繰り返す作業を誇らしげに語る人に、仕事が出来る人いません。
データを挿入したら、用意した関数だけで計算を自動で反映させるのが仕事効率化の理想です。
今回紹介するINDIRECT関数を使えば、データを挿入するだけで、あとは関数がパパっと計算してくれます。
無能がせっせと手動で数式を直しているときに、あなたは一息入れたり、別の作業をすることができるという訳です。
前置きが長くなりましたが、さっそく本題に入りたいと思います。
目次
データサイエンティスト / ブロガー
たぬ
tanu
福岡在住のデータサイエンティスト。Pythonでルーチンワークを瞬殺する仕組みを作ることやデータを視覚化することが得意です。
INDIRECT関数の使い方
=INDIRECT(参照文字列)
INDIRECT関数は参照文字列によって指定されたセルに入力されている文字列を使って、間接的にセルを参照します。
言葉だけだとよく分からないですね。
例を見たら何となくイメージつかめると思います。
『A1』のような実際のセル参照が指定できる
別のセル同士を連結させて参照することもできる
=INDIRECT("A4")
=A4の中身を参照
関数と組み合わせることもできる
参照文字列のところに範囲セルを記入することも出来ます。
INDIRECT関数の応用
INDIRECT関数は、他の関数と組み合わせることで真価を発揮します。
例として、最終行が可変するデータにも柔軟に対応できるSUM関数を作成してみますね。
このままでは、最終行が変わるとINDIRECT関数の中も一緒に変更しなければなりません。
INDIRECT関数の参照文字列『"A2:A5"』を『"A2:A"&5』に分解する
=SUM(INDIRECT("A2:A"&5))
文字列として、『"A2:A5"』と『"A2:A"&5』は同等の結果がでます。
となると、答えが見えてきませんか？
…
そうです。『"A2:A"&5』の『5』の計算結果が常に最終行であればいいのです。
次にやるべきことは、関数を使って最終行の値を求めます。
関数を使って最終行の値を求める
これは、以下の数式で求めることができます。
数式の詳細は以下の記事を参考にして下さい！
【コピペでOK】スプレッドシートの関数で『動的な』最終行の行番号・内容を取得する方法
最終行の数式をINDIRECT関数の参照文字列に代入する
=SUM(INDIRECT("A2:A" & MAX(IFERROR(MATCH(MAX(A:A)+1,A:A,1),0),IFERROR(MATCH("",A:A,-1),0))))
これで、A列の最終行が変わっても自動で計算してくれますよ♪
さいごに
SUM関数とINDIRECT関数を組み合わせてデータの修正に強い数式を作成しました。
今回の例では『A2:A』と固定して使っていますが、以下の方法で動的に列のアルファベットを取得して数式に組み込んでも問題なく動作します。
【コピペでOK】スプレッドシートの関数で『列のアルファベット』を取得する方法
動的なデータに対応できるように、少しでも手動で変更することを減らしていって、最高のスプレッドシートを作ろう(^o^)
