セルの値を特定の範囲に展開する（セル数指定）【関数】【Googleスプレッドシート】【ARRAYFORMULA】

引き続き、そんな機能いります？

引き続き、そんな機能いるのかという関数です。
前回の記事はこちらです。セルの範囲を指定して、文字列を展開するという機能でした。

今回は、「展開回数」「文字列」をセルで指定して展開させています。
前回のが個別対応なのに対し、今回のは細かな条件が多くあっても対応できるようになっています。

今回の用途は、またもやリース管理でした。

こちらも以前ご紹介した記事ですが、リースの期間にあわせて、支払額を自動で階段表にできる表です。前回はリースの条件をすべて整理すれば、自動集計できるという表です。

今回の関数は、「あと5回、100,000円を反映させる」のような使い方です。

単独で活用できます。結果からどうぞ

【完成】ARRAYFORMULA(if((ROW(indirect(ADDRESS(row(),column(),4)):indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+I2-1))-ROW(indirect(ADDRESS(row(),column(),4)):indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+I2-1)))=0,"","")&I1)

前回の関数、=ARRAYFORMULA(if((ROW(〇:〇)-ROW(〇:〇))=0,"","")&"いいい")を発展させたのですが、なんとも長い長い関数になりました。。
発展のプロセスを記載していきます。

１．「終点」をセルの値で指定する

　　結果的にすごく長い式になってるのですが、やってるのはそういうことです。

上記の画像で表現すると、「D9」がセルの値によって変動させたいです。
今、セルの値の設定は７・起点が３ですので、９＝３＋７－１と表現できます。

２．具体的に「D9」の場合・・・「D」

そのセルの列番号（DとかEとか）を表現するのが意外に大変です。行番号は「=ROW（）」で済みますし、「列数」（3とか4とか）なら、「=COLUMN( )」で表示できます。
セルの列番号は、substitute(ADDRESS(row(),column(),4),row(),"")という関数になります。

３．具体的に「D9」の場合・・・「９」

こちらは比較的簡単：=ROW()+D2-1 ：３＋７－１＝９　です。

４．その二つを、indirectでくくる

indirect(〇&〇)に間違わず入れます。
indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+D2-1)
こうなりますね。

５．できあがったindirect分を投入する

ARRAYFORMULA(if((ROW(D3:【〇】)-ROW(D3:【〇】))=0,"","")&D1)
ここに入れます。間違えないことだけです。

６．ほぼ完成

ARRAYFORMULA(if((ROW(D3:indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+D2-1))-ROW(D3:indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+D2-1)))=0,"","")&D1)
ほぼ完成です。長いですけど。。
最後少し手を加えます。「関数を入力するセル」として想定している「D3」。これがなければ、変数が一つ減ります。「D1」文字列「D2」展開する数。の二つであればきれいです。
ここまで来たらD3：つまり、自分のセルですので、
ADDRESS(row(),column(),4)
を入れるだけです。

７．完成！

ARRAYFORMULA(if((ROW(indirect(ADDRESS(row(),column(),4)):indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+D2-1))-ROW(indirect(ADDRESS(row(),column(),4)):indirect(substitute(ADDRESS(row(),column(),4),row(),"")&row()+D2-1)))=0,"","")&D1)

とてもとても長いですので、使い勝手はよくないですよね。
実用的には「名前付き関数」にするのがよいとおもいます。

まとめ

「一つのセルの値を自動的に展開したい」ないと困るかといわれるとそうでもない関数です。でもスタンダードであれば使うかもしれないかなって思います。いろいろ組み合わせれば、可能性が広がりますね。
今回、文字列での展開となってしまっています。
リース管理で使いたかったので、数値じゃないと困るのですよ。
そのあたりはまた別の機会に。

今回のシートです。
https://docs.google.com/spreadsheets/d/1VVloD-oNsmv8huElYwCtEQEzsJPMfzQ4Y8tavWxf_Wk/edit#gid=749520657