googleスプレッドシート A1:A このA1を動的にする方法

スプレッドシート（excel）で A1:A このような範囲指定の
A1部分を、別セルに記載した定数を使って動的にする効率のよい方法を教えてください。
具体的には、他セルB1に 1 や 5 などの数値が記載しており、
A1:A になったり、 A5:A になったりすることを想定しています。

思いついた案①：INDIRECTを使う
=INDIRECT("A" & B1 & ":A")

思いついた案②：OFFSETを使う
=OFFSET(A1, B1 -1, 999999, 0)

※なるべくプログラムは書かず関数のみで成立する方針で検討しています。GASでの独自関数定義も避けたく、理由はこの数式を大量のセルで動作させることで、高速でなくなるためです。

②は最終行を簡易に求める方法が無さそうだったので別途最終行を何らか方法で出力しておくか、マジックナンバーを使うかしかない気がしており、より厳密には最大行数を入れなければいけなさそうな気がしています。

①もあまりきれいではないように思い、もっといいやり方があれば教えてください。

No.1 ベストアンサー 回答者： goomania 回答日時： 2020/10/11 16:02

Googleスプレッドシートで動的に範囲を指定するスマートな方法についてのご質問だと思います。

WEB版以外のEXCELなら「名前定義」を使って、ご質問者のいうA1やA5を指定するのに「開始セル」という名前をつけ、名前定義で参照範囲に「=indirect(sheet1!$B$1」と記述すれば、B1の内容により、開始セルを動的に定義できますが、Googleスプレッドシートの「名前付き範囲」では関数を使って範囲を指定することができないようです。
このため、ご質問者が例示したINDERECTやOFFSETをセルの数式の中に使う方法が一般的なのではないかと思います。
ただ、ご質問者のいう「範囲指定」の数式を単独でセルに記述する場面というのが簡単に思いつきません。しかも「大量のセルで動作させる」とは、実際にどのような場面で使用しているのでしょうか?
たとえば、指定範囲の合計値を求めるのであれば「=SUM(INDIRECT("A" & B1 & ":A")」となるでしょうし、何か数えるのであれば「=COUNTIF(INDIRECT("A" & B1 & ":A"),検索条件)」(B1が数値という制限がなければB1に「セル番地」の「A5」などを直接入力し、「=SUM(INDIRECT(B1 & ":A")」とすればもっとすっきりしますが・・・)というように他の関数と組合せて使用するのが一般的だと思います。
だとすると、「必ずしも範囲指定を返す関数でなくても、開始セルと最終セルを指定できればよく、このうち開始セルを動的に変更する」という考え方ではだめでしょうか?
最大行数は「=ROWS(A:A)」で求められますので、前述の例でいえば、B1に1や5の数値を記述し「=COUNTIF(INDEX(A:A,B1):INDEX(A:A,ROWS(A:A)),検索条件)」というような数式でも対応可能だと思います。
なお、ご質問者の例示された「=OFFSET(A1, B1 -1, 999999, 0)」は最終行の指定を数式に変え「=SUM(OFFSET(A1,B1-1,0,ROWS(A:A)-B1+1,1))」のようにして使用可能です。(EXCELではエラーになる最大行数を超えるであろう「=SUM(OFFSET(A1,B1-1,0,ROWS(A:A),1))」と記述してもGoogleスプレッドシートではエラーにはならないようです)
上記のうち、どれが実行速度が速いのかについては当方は知識がありません。他の回答者さんにお任せします。

この回答へのお礼

お礼遅くなり失礼しました。

ご提示いただいた候補で、いずれも解決できそうでした！
中から、一番最適なものを試してみます。

> =ROWS(A:A)
こちら探してもなかなか辿り着けなかったのですが、やはり数式で簡易に求められたのですね。大変助かります。

丁寧に解説いただき、ありがとうございました。

お礼日時：2020/10/21 04:09