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

そんな機能いります？

いや、そういわれると。。。セルのコピーでも、初めから＝B1を全範囲に入れとけばいいと言われたりもしますし。。。
でもまあ、それだと、一つが消えたりすることってあるじゃないですか。
ARRAYFORMULAの機能は体系立てて理解できてないです。
でも、メリットとしては、「一つのセルに入力するだけで展開できる」というところだと思います。
すごく必要ではないんですけど、ようやく希望通りのイメージできたので公開させていただいています。バグがありましたらご指摘くださいませ。

どんな時に必要なのか？経理の伝票インポートには使えると思います？

経理業務で、会計ソフトに伝票をインポートするとき、弥生会計・freee会計などでは、同じ数字や日付などを複数行に入力しないといけないケースがあります。

こんな時に、一つのセルの入力で、特定の範囲に展開したいなという用途の想定でした。
段階を追って解説していきます。

１．ARRAYFORMULA関数を使って特定のセルから、通し番号を表示する

３行目から、一定の範囲に通し番号を振る数式です。完成イメージは下の図で「B」列です。

A3:=ARRAYFORMULA(ROW(A3:A15))

まず、A３には、こちらの関数が入っています。
ROW（）という関数は、そのセルが入力されたセルの行を返す関数です。
　A1　に「=ROW（）」と入力すれば、「１」が
　A1　に「=ROW（B2）」と入力すれば、「２」が表示されます。
　単純に範囲指定しても、起点のセルの行が返されます。
A1　に「=ROW（B2:B10）」と入力すれば、「２」と表示されるということです。
これをARRAYFORMULAでくくると、図のように、A３を起点に３，４，５，６・・・と数字が続いていきます。「３行目から１５行目まで、ROW（）を展開する」という数式になります。しかし、それだと通し番号になりません。でもよく見ると、２多いだけじゃないか。２引いたら通し番号になるんじゃないの？という感じです。

B3:=ARRAYFORMULA(ROW(B3:B15)-2)

というわけで、２を引いてみました。希望通りの表示になりましたね。
Ｂ４を起点とするときは、-３、Ｂ５を起点とするときは、-4と調整値を入れれば、どこからの開始にも対応できます。
=ARRAYFORMULA(ROW(B3:B15)-row(B3)+1) という表現にすれば調整値の自動処理もできますね！

C3:=ARRAYFORMULA(ROW(C3:C15)-2&"月")

最後はおまけです。＆で連結すれば、文字列追加にも対応できますよ、という例でした。

２．ARRAYFORMULA関数を使って同じ情報を一定の範囲に展開する

先ほどは通し番号を振ってみました。
通し番号なので、同じ数字はありません。
同じ数字にするのはどうしたらいいか。。。
あ、おなじ数字を引けばどうだろうか。
ということで、こちらです。

A3:=ARRAYFORMULA((ROW(A3:A15)-ROW(A3:A15)))

先ほどの通し番号にたいして、同じ数字を引き算させて展開させてみました。結果は、全範囲が「0」の表示になります。
同じ数字が展開できただけで、なんだかうれしいです。
では、それをテキストにするためには次のステップです。
とりあえず、テキストを追加してみましょう

B3:=ARRAYFORMULA((ROW(B3:B15)-ROW(B3:B15))&"あああ")

先ほどと同様、テキストを加えてみました。
お！なんかイメージに近くなりましたけど、0が邪魔ですよね。
0を除いてしまいましょう。
すごく単純に、「値がゼロだったら表示しない」＋「展開したい文字列」で何とかなりそうです。

【完成】C3:=ARRAYFORMULA(if((ROW(C3:C15)-ROW(C3:C15))=0,"","")&"いいい")

イメージ通りになりました！

おまけです。ARRAYFORMULAは横への展開もできます。
ROWではなく、COLUMを使い、横展開にすればいいです。

=ARRAYFORMULA(if((COLUMN(B1:F1)-COLUMN(B1:F1))=0,"","")&"えええ")

３．他の列の情報に依存する方法
この展開、ほかの列の入力情報に依存する方法でしたら
=ARRAYFORMULA(if(G3:G<>"","ううう",""))
このような書き方があります。

こちらも活用用途はあるのですが、あえて、単独で展開できる計算式を考えてみました。

３．「名前付き関数」に応用が利きそう

関数の構成を見直しますと、
=ARRAYFORMULA(if((ROW(【範囲】)-ROW(【範囲】)=0,"","")&【展開したい文字列】)
と、実用化のためには、名前付き関数との相性もよさそうですね。早速作ってみます。

=TENKAI(M6:M10,"ああ") という関数で展開できたのを確認できました。
名前付き関数については、過去記事に詳細を書いております。よろしければご参考ください。

では今回のシートです。
https://docs.google.com/spreadsheets/d/17vmWXuTVnA9TcUEOKRiGQo-2sOzuUbS8KpkrxsMRpjA/edit#gid=0