【超便利】スプレッドシートの『INDIRECT』関数で動的にセルを指定する方法

dynamically-specify-cells

スポンサーリンク

EXCELなんか無くなって、みんなGoogleスプレッドシートに移行すればいいのに」と、いつも本気で思っている、たぬ(@KAivlys)です!

EXCELやスプレッドシートで作ったシートに、後日データを挿入した後、シートの関数を手直す作業を繰り返していませんか?

例)SUM関数を使って、A1行から最終行までの和を求めたい

dynamically-specify-cells1
=SUM(A1:A10)

データをA10の下に挿入する予定がないと確信している場合は、この方法が最適だと思います。

ですが、今後もA10の下にデータを挿入する予定があるのであれば、この方法は最善ではありません。



なぜならデータを挿入した後に数式を手動で直さないといけないからです。

dynamically-specify-cells2 dynamically-specify-cells3

「な〜んだ。データを挿入したら、数式直すだけっしょ。楽勝じゃん。」

とか思ってませんか?そうであれば、その考えは直ちに修正するべきです。

もしデータを挿入して直す数式が100個とか10000個出てきても、同じことが言えますか?

dynamically-specify-cells4

さっきと同じことが言える人は、一生仕事で他人の足を引っ張り続ける無能でしょうね。

繰り返す作業を誇らしげに語る人に、仕事が出来る人いません。

データを挿入したら、用意した関数だけで計算を自動で反映させるのが仕事効率化の理想です。

今回紹介するINDIRECT関数を使えば、データを挿入するだけで、あとは関数がパパっと計算してくれます。

dynamically-specify-cells5

無能がせっせと手動で数式を直しているときに、あなたは一息入れたり、別の作業をすることができるという訳です。

前置きが長くなりましたが、さっそく本題に入りたいと思います。

INDIRECT関数の使い方

構文
=INDIRECT(参照文字列)

INDIRECT関数は参照文字列によって指定されたセルに入力されている文字列を使って、間接的にセルを参照します。

言葉だけだとよく分からないですね。

例を見たら何となくイメージつかめると思います。

『A1』のような実際のセル参照が指定できる

dynamically-specify-cells6

別のセル同士を連結させて参照することもできる

dynamically-specify-cells7
=INDIRECT(A1&B1)
=INDIRECT("A4")
=A4の中身を参照

関数と組み合わせることもできる

dynamically-specify-cells8

参照文字列のところに範囲セルを記入することも出来ます。

INDIRECT関数の応用

INDIRECT関数は、他の関数と組み合わせることで真価を発揮します。

例として、最終行が可変するデータにも柔軟に対応できるSUM関数を作成してみますね。

dynamically-specify-cells8

このままでは、最終行が変わるとINDIRECT関数の中も一緒に変更しなければなりません。

INDIRECT関数の参照文字列『"A2:A5"』を『"A2:A"&5』に分解する

=SUM(INDIRECT("A2:A5"))
=SUM(INDIRECT("A2:A"&5))
dynamically-specify-cells9

文字列として、『"A2:A5"』と『"A2:A"&5』は同等の結果がでます。

となると、答えが見えてきませんか?

そうです。『"A2:A"&5』の『5』の計算結果が常に最終行であればいいのです。

次にやるべきことは、関数を使って最終行の値を求めます。

関数を使って最終行の値を求める

これは、以下の数式で求めることができます。

=MAX(IFERROR(MATCH(MAX(A:A)+1,A:A,1),0),IFERROR(MATCH("",A:A,-1),0))
get-last-row4

数式の詳細は以下の記事を参考にして下さい!

【コピペでOK】スプレッドシートの関数で『動的な』最終行の行番号・内容を取得する方法

2017.12.31

最終行の数式をINDIRECT関数の参照文字列に代入する

=SUM(INDIRECT("A2:A"&5))
=SUM(INDIRECT("A2:A" & MAX(IFERROR(MATCH(MAX(A:A)+1,A:A,1),0),IFERROR(MATCH("",A:A,-1),0))))

これで、A列の最終行が変わっても自動で計算してくれますよ♪

dynamically-specify-cells10 dynamically-specify-cells11

さいごに

SUM関数とINDIRECT関数を組み合わせてデータの修正に強い数式を作成しました。

=SUM(INDIRECT("A2:A" & MAX(IFERROR(MATCH(MAX(A:A)+1,A:A,1),0),IFERROR(MATCH("",A:A,-1),0))))

今回の例では『A2:A』と固定して使っていますが、以下の方法で動的に列のアルファベットを取得して数式に組み込んでも問題なく動作します。

【コピペでOK】スプレッドシートの関数で『列のアルファベット』を取得する方法

2018.01.06

動的なデータに対応できるように、少しでも手動で変更することを減らしていって、最高のスプレッドシートを作ろう(^o^)

スポンサーリンク

dynamically-specify-cells

ABOUTこの記事を書いてる人

たぬ(谷口健太)

25歳社会人。本職は福岡の通販会社でWebマーケッターやっとります。 プログラミング✕マーケティング=最強説を唱えており、非効率的な世の中に革命を起こそうと本ブログを設立。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化

NEW POSTこのブログの最新記事