【Googleスプレッドシート】表の空白を「上の行の値」で埋める配列関数を考える（旧）

本記事の結果をより簡単に導くアプローチを思いついた別の記事を以下に用意しました。
用途が同じであれば以下の方がオススメです！
表の空白を「上の行の値」で埋める配列関数を考える（新）

他人の作ったデータを取り扱う際に時折直面する「一部の行が省略されている」問題。
人間同士が目を通すだけの資料では「同に上じ」と解釈できるため特に問題はありませんが、データとして取り扱うときは困っちゃいますよね。
本記事では私なりに行った解決策を書き留めます。

こだわらない方法はある

シンプルに解決しようとすれば以下が最も簡単です。
=IF(A2="",B1,A2)
新規の列（ここではBとする）を作成し、上記のIF関数を表の最初の行の右隣（B2）に書き、あとは必要な行数だけ下にオートフィルさせれば解決です。
特にこだわりや不都合がなければこの方法でOKだと思います。

	A	B
1	品名	新規カラム
2	りんご	=IF(A2="",B1,A2)
3		=IF(A3="",B2,A3)
4		=IF(A4="",B3,A4)
5		=IF(A5="",B4,A5)
6		=IF(A6="",B5,A6)
7	みかん	=IF(A7="",B6,A7)
8		=IF(A8="",B7,A8)

不都合の例
上記のIF関数は「もしも左のセルが空白だったら上のセルの値を記入、そうでなければ左のセルの値を記入」といった指示ですが、例えば「りんご」と書かれている行がシート自体の1行目に存在した場合、「上のセル」が存在しないという状況になってしまう。

とは言え、こだわりたかった

こだわりました。
実際不都合に直面したというわけではありませんが、私自身オートフィルの操作にわずらわしさを感じていましたし、配列化できるならそうしたいと感じたためです。
結果的に以下のような関数になりました。

完成した関数（末尾を書き換えるだけで使用可能）

=LAMBDA(x,y,
    LAMBDA(z,
        ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(
            z
        &y,
            COUNTIF(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(z)))
        )),y)))
    )(FILTER(x,x<>""))
)(データ範囲,非登場文字)

一見ごちゃついて見えますが、
実際の使用時に書き換える必要があるのは末尾の (データ範囲,非登場文字) の箇所だけです。
上記をコピーしてセルに貼り、末尾を書き換えるだけですぐに使用できるかと思います。
書き込む内容を (前半,後半) で分けて解説します。

前半「データ範囲」には「空白埋めを適用したい範囲（1列に限る）」を記入します。
　※例：A2:A8　セル参照にする場合は INDIRECT() 形式で記入。

後半「非登場文字」には「今回扱うデータに登場しない文字」を記入します。
　※例："◆" "任意の記号や文字"　セル参照の場合は$E$1のように絶対参照で記入が推奨。

セル参照にする場合の補足です。
以下のように関数外のどこか別のセルに「空白埋めを適用したい範囲」と「登場しない文字」を用意できている場合。
本関数の末尾は (INDIRECT(D1),$E$1) と書く。
※後半を絶対参照にしているのは、本関数を横方向にオートフィルさせる場面が実務で意外と多いため、参照ズレ防止のためにそうしています。なので絶対参照は必須ではなく E1 と書いても何も問題はありません。

	D	E
1	A2:A8	◆

「登場しない文字」について
上記の例の「◆」のように1文字である必要はなく、「abcdefg」や「魑魅魍魎」などと書いてしまっても大丈夫です。
要は取り扱うデータのテキストに干渉しなければ何でも良いです。

ちなみに、関数内にある小文字の「a,b,x,y,z」はLAMBDA関数で使っている変数名ですので、使用される方は各々書き換えていただいても大丈夫です。

さて、
(データ範囲,非登場文字) 部分を (A2:A8,"◆") と書き換え、
上記で組み上げた関数を、例にする以下の表でB2セルに記入してみます。

	A	B
1	品名	新規カラム
2	りんご	ココに書く
3
4
5
6
7	みかん
8

すると以下のような結果になるでしょう。成功です

	A	B
1	品名	新規カラム
2	りんご	りんご
3		りんご
4		りんご
5		りんご
6		りんご
7	みかん	みかん
8		みかん

---

仕組みの解説

「そもそも最初の =IF(A2="",B1,A2) をARRAYFORMULAで囲うとかじゃダメなの？」
　結論：なんかダメでした。

ARRAYFORMULA関数とは、
通常セル同士で行うような計算を、範囲同士で行えるようにしてくれるような関数です。

希望を胸に抱いて書いた「=ARRAYFORMULA(IF(A2:A8="",B1:B7,A2:A8))」は残念ながら動きません。
なぜなら元になったIF関数は「行ごとに一つの計算結果を出し、次の行ではその戻り値を引数に含めて計算する」といった段階的な動きを行っているためです。ARRAYFORMULA関数はあくまでも範囲同士をまとめて計算するというシンプルな動きをするものなので、こういった順番に計算を完結させるような処理をサクッと対応はしてくれません。まあ、仮にちゃんと動いたとしてもB1:B7の部分を毎回書くのは面倒くさくて私は耐えられないタイプなのですが……

なので別の方法を考える上で運用の楽さも重要な目的でした。

---

この関数の正体

タイトル詐欺になってしまうのですが、
正確にはこの関数は「空白を上の行の値で埋める」という処理を行っている訳ではありません。
結果がそうなっていることに変わりはないのですが、構造の解説のためにどの様な処理をしているかを正しく説明すると「空白でない行の値を(次に空白でない行が登場するまでの距離)数だけ繰り返し記入、その処理を(空白でない行の数)だけ繰り返す」という処理を行っています。

……分かりにくいですよね……

トップの画像にもなっている以下の表を例にて解説します。

A列のデータに対し、B列は何を行っているのか。

実は単純なことでして、
「りんご」「みかん」「パイナップル」「梨」という空白でない行のテキスト4つを取得し、
「りんご」は5回、「みかん」は3回、「パイナップル」は1回、「梨」は4回、B列に記入されるように指示を出したのです。
そうすることでB列の内容は結果的にA列の空白を埋めたような見た目になりました。

この「5,3,1,4」という数字が上で書いた「次に空白でない行が登場するまでの距離」なのです。

「りんご」の次に「みかん」が登場するのは5行後、「パイナップル」が登場するのは3行後ですよね。

※ただし最後の「梨」だけが実は例外で、「梨」に与えられている記入回数の 4 とは、
「空白埋めを適用したい範囲(A2:A14)」の途切れるまでの距離となります。

つまり今回の方法でA列のデータをB列の形に変換するためには、
「りんご/みかん/…」という空白ではない行に存在するテキストと、それに結びつく「5,3,1,4」という数字を何らかの方法でA列側から用意することが最低限必要な準備になります。

テキスト	記入回数
りんご	5
みかん	3
パイナップル	1
梨	4

---

どのように構築したか

上で説明した仕組みを実現するための関数の構築に入ります。
「準備物（テキストと記入回数）の取得」と「目指す形への整形」の順序で進めます。

Step１　テキストを得る

空白ではない行に存在するテキストの一覧を取得します。
これはいたって簡単です。空白埋めを適用したい範囲(A2:A14)に対し、
FILTER関数で空白をフィルタリングすればOKです。

関数①

=FILTER(A2:A14,A2:A14<>"")
これにより、空白ではない行に存在するテキストの一覧が取得できました。

テキスト
りんご
みかん
パイナップル
梨

この関数は後に別の場面でも使用するため、関数①と番号付けしておきます。

---

Step２　記入回数を得る

記入回数
5
3
1
4

上記結果を得るまでの処理を解説するため、数式を分割して進めます。
以下の画像内ではB列、C列、D列それぞれに効果の異なる関数を入れており、最後のD列で「5,3,1,4」を得ることに成功しています。
この結果に至るまでの3つの段階を下記にて説明いたします。

B列：関数②

B2セルには=ARRAYFORMULA(IF(A2:A14="",0,1))と入力しています。
IF関数でA列の元データの空白にあたる行に0、空白でない行に1が返ってくるように指示しています。
これにより、B列には0と1のみで構成された数列が出力されました。

C列：関数③

C2セルには=SCAN(0,B2:B14,LAMBDA(a,b,a+b))と入力しています。
SCAN関数とは、LAMBDA関数とあわせて使う関数であり、
指定した範囲内で一つ上の行までの計算結果を参照し、累計しながら行を下って行く関数です。

本数式内では初期値設定を0にしており、
初期値の0+B2の結果がC2に書き込まれます。
次は0+B2+B3の結果がC3に書き込まれます。
その次は0+B2+B3+B4の結果がC4に書き込まれます。
︙
連続したこの処理により、C列には元データの空白ではない行を通過した時のみ1ずつ増える整数の配列が出力されました。

D列：関数④

D2セルには=ARRAYFORMULA(COUNTIF(C2:C14,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))と入力しています。
ここがやや複雑なため少し丁寧に解説します。
この関数の機能を一言でいえば「範囲内に存在する“ある数”の個数を数える」です。

難解に感じるかも知れませんが、この関数の前後の値を先に見比べるときっと分かりやすくなるかと思います。
前(C列)「1,1,1,1,1,2,2,2,3,4,4,4,4」
後(D列)「5,3,1,4」
お気づきでしょうか？
D列で出力した「5,3,1,4」とは、C列内にある「数字の1の個数、2の個数、3の個数、4の個数」なのです。

では「C列内の1～4までの各個数を数える」という関数を書きます。
条件に一致するデータを数えるCOUNTIF関数で数える範囲のC2:C14を指定します。
　ARRAYFORMULA(COUNTIF(C2:C14,
この次に記述するのは「何を数えるか」という設定なので今回は「1～4」を指定したい。
「1～4」を普通に書くならSEQUENCE(4)です。
SEQUENCE関数は連続した数値の配列を返せる関数で、デフォルトで1～入力値の配列を返せるため、4と入力した場合はそのまま1～4の連続数列のあつかいになります（厳密には{1;2;3;4}に同じ）
結果として以下が組み上がります。
=ARRAYFORMULA(COUNTIF(C2:C14,SEQUENCE(4)))

ただし、このままではこの関数の運用が大変になります。
なぜなら、この4にあたる入力値の期待値は最初の元データの内容によって変わってしまうからです。
なので、このSEQUENCE関数にわたす数字は元データにあわせて変化するように書かなければなりません。
（そうしないと使用のたびに毎回ここを手動で書き換える必要が出てきます）

ではこの4にあたる数字は何なのか。
B列に書いた関数②で書き出した数列「1,1,1,1,1,2,2,2,3,4,4,4,4」の最大数です。
この関数②は「元データの空白ではない行を通過した時のみ1ずつ増える整数の配列」であるため、返す数列の最大数は「元データの空白ではない行の数」に等しくなります。

つまり、SEQUENCE(元データの空白ではない行の数)となるように記述すれば、
元データの内容に合わせて「1～4」に限定せず「1～10」「1～60」と自動で可変してくれるようになります。

では「元データの空白ではない行の数」を関数化しましょう。
ROWS(FILTER(A2:A14,A2:A14<>""))
ROWS関数は範囲内のデータの行数を数えます。
その中に「元データの空白ではない行」を抽出するFILTER関数を置きました。
空白ではない行に存在するテキストの一覧を出力した関数①と全く同じ式です。

「範囲内の“ある数”の個数を数える」「その数は1～」「元データの空白ではない行の数」
ARRAYFORMULA(COUNTIF(C2:C14,　SEQUENCE(　ROWS(FILTER(A2:A14,A2:A14<>""))
　▼
=ARRAYFORMULA(COUNTIF(C2:C14,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))

処理が統合され、
上記の関数④を入れたD列には「5,3,1,4」出力されました。
これにてテキストの記入回数として使用したかった数の配列を得ることができました。

---

Step３　目指す形への整形

テキスト	記入回数
りんご	5
みかん	3
パイナップル	1
梨	4

関数①
=FILTER(A2:A14,A2:A14<>"")
こちらでテキストの一覧を得ました。

関数②～④
=ARRAYFORMULA(IF(A2:A14="",0,1))
=SCAN(0,B2:B14,LAMBDA(a,b,a+b))
=ARRAYFORMULA(COUNTIF(C2:C14,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))
こちらで記入回数の一覧を得ました。

上記を使って各テキストが各記入回数だけ出力された結果を得るための構築を行います。
ただ、以降の文章に上記の数式をそのまま引用すると非常に見づらくなるため、
関数①（テキストの一覧）を 【テキスト】
関数②～④（記入回数の一覧）を 【記入回数】
と便宜上置き換えて記載します。

---

【テキスト】を【記入回数】だけ複製する

REPT関数という、文字列を指定された回数だけ繰り返す関数を使います。
=ARRAYFORMULA(REPT(【テキスト】,【記入回数】))

テキスト	記入回数	REPT結果
りんご	5	りんごりんごりんごりんごりんご
みかん	3	みかんみかんみかん
パイナップル	1	パイナップル
梨	4	梨梨梨梨

「りんご」を5回、「みかん」を3回、「パイナップル」を1回、「梨」を4回、繰り返し記入した形に出力されました。
ですが、この結果では整形時に扱いづらいです。
単語ごとの区切りとなる目印がありません。

目印を足すために【テキスト】の後ろに 「今回扱うデータに登場しない文字」 を追加しましょう。
仮で記号の「◆」を使用します。
【テキスト】&"◆"
上記を反映し
=ARRAYFORMULA(REPT(【テキスト】&"◆",【記入回数】))

テキスト	記入回数	末尾に◆を足してREPT結果
りんご	5	りんご◆りんご◆りんご◆りんご◆りんご◆
みかん	3	みかん◆みかん◆みかん◆
パイナップル	1	パイナップル◆
梨	4	梨◆梨◆梨◆梨◆

これで後から手を加えやすい形になりました。

---

複製された【テキスト】を一旦ひとつにまとめる

CONCATENATE関数という、複数の文字列を1つの文字列に結合する関数を使います。
本処理は配列関数の中で行うためARRAYFORMULAの内側に CONCATENATE を追加します。
=ARRAYFORMULA(追加箇所REPT(【テキスト】&"◆",【記入回数】))
　▼
=ARRAYFORMULA(CONCATENATE(REPT(【テキスト】&"◆",【記入回数】)))

テキスト	記入回数	末尾に◆を足してREPT結果	CONCATENATE結果
りんご	5	りんご◆りんご◆りんご◆りんご◆りんご◆	りんご◆りんご◆りんご◆りんご◆りんご◆みかん◆みかん◆みかん◆パイナップル◆梨◆梨◆梨◆梨◆
みかん	3	みかん◆みかん◆みかん◆
パイナップル	1	パイナップル◆
梨	4	梨◆梨◆梨◆梨◆

この処理により複製されたテキストが1つのセルに1行のテキストとして書き出されました。

なお、CONCATENATE関数で処理できる引数や、1つのセルに書き込めるテキストには上限が存在するため、扱う元データが巨大な場合は本処理が正常に行なえない可能性があります。

---

1セル内のテキストとなったデータを複数のセルに分割し整形する

SPLIT関数という、指定の区切り文字で文字列をセル分割する関数を使います。
そして区切り文字には【テキスト】の後ろに追加していた 「今回扱うデータに登場しない文字」 を再度指定します。
本処理も配列関数の中で行うためARRAYFORMULAの内側に SPLIT を追加します。
=ARRAYFORMULA(追加箇所CONCATENATE(REPT(【テキスト】&"◆",【記入回数】))追加箇所)
　▼
=ARRAYFORMULA(SPLIT(CONCATENATE(REPT(【テキスト】&"◆",【記入回数】)),"◆"))

「りんご」を5回、「みかん」を3回、「パイナップル」を1回、「梨」を4回、繰り返した横並びのセルの表が作られました。

---

最後にこの表を横並びから縦並びに整形します
TRANSPOSE関数という、行と列を入れ替える関数を使います。
これにより横ならびの表が縦ならびに変換されます。
本処理も配列関数の中で行うためARRAYFORMULAの内側に TRANSPOSE を追加します。
=ARRAYFORMULA(追加箇所SPLIT(CONCATENATE(REPT(【テキスト】&"◆",【記入回数】)),"◆"))
　▼
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(【テキスト】&"◆",【記入回数】)),"◆")))

無事、求める形に落ち着きました。

---

仕上げ：関数の最適化

最後の最後で行わなければならない関数自体の整頓作業。
以下が今回の結果を導くために行った処理の一覧です。これを1文の関数にまとめたいと思います。

【整形】
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(【テキスト】&"◆",【記入回数】)),"◆")))

【テキスト】= 関数①
=FILTER(A2:A14,A2:A14<>"")

【記入回数】= 関数②～④
=ARRAYFORMULA(IF(A2:A14="",0,1))
=SCAN(0,B2:B14,LAMBDA(a,b,a+b))
=ARRAYFORMULA(COUNTIF(C2:C14,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))

行うことは各処理を行った順番から入れ子にしていくだけです。
関数①～④までを【整形】の中に入れればよいですが、先に関数②～④を一文にまとめます。
記入回数を出す関数なのでまとめたものを「関数㋖」と呼びます。

入れ子にする箇所は以下。
関数②　=ARRAYFORMULA(IF(A2:A14="",0,1))
関数③　=SCAN(0,関数②,LAMBDA(a,b,a+b))
関数④　=ARRAYFORMULA(COUNTIF(関数③,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))
　▼
関数㋖

=ARRAYFORMULA(COUNTIF(
    SCAN(0,
        IF(A2:A14="",0,1)
    ,LAMBDA(a,b,a+b))
,SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))

1行目の関数④の頭のARRAYFORMULAが働くので、3行目の関数②からはARRAYFORMULAを排しました。

---

次に【整形】の中に関数①と関数㋖を入れ子にします。

【整形】
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(関数①&"◆",関数㋖)),"◆")))

【テキスト】= 関数①
=FILTER(A2:A14,A2:A14<>"")

【記入回数】= 関数㋖
=ARRAYFORMULA(COUNTIF(SCAN(0,IF(A2:A14="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>"")))))

=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(
    FILTER(A2:A14,A2:A14<>"")
&"◆",
    COUNTIF(SCAN(0,IF(A2:A14="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>""))))
)),"◆")))

1行目の【整形】の頭のARRAYFORMULAが働くので、4行目の関数㋖からはARRAYFORMULAを排しました。
こちらの状態で関数を1文にまとめることが出来ました。
一応このままでも動きます。

が、使用するたびに書き換える場所が多いことと、同じ記述が重複している箇所が目立つため私はここから更に最適化を行いました。

---

変数を使う

関数内の重複する記述を省略するためにLAMBDA関数を使用します。

LAMBDA関数の機能例
「=SUM(A1:A10)」+「=SUM(B1:B10)」+「=SUM(C1:C10)」÷「=SUM(A1:A10)」
上記のような計算をする場合
=SUM(A1:A10)+SUM(B1:B10)+SUM(C1:C10)/SUM(A1:A10)
と書けばよいが、演算子の位置が見にくくSUM(A1:A10)も2回使用しているので省略したい。
▼
=LAMBDA(a,b,c,a+b+c/a)(SUM(A1:A10),SUM(B1:B10),SUM(C1:C10))
SUM(A1:A10)を「a」、SUM(B1:B10)を「b」、SUM(C1:C10)を「c」に置き換えて数式を組み立てることが出来る。
この置き換える処理を変数化と言います。

組み上がった以下の関数内で重複して使用している箇所を探す。

=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(
    FILTER(A2:A14,A2:A14<>"")
&"◆",
    COUNTIF(SCAN(0,IF(A2:A14="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(FILTER(A2:A14,A2:A14<>""))))
)),"◆")))

・空白埋めを適用する元データの範囲を示すA2:A14
・整形時の処理で2回使用した"◆"
上記が複数回登場するためLAMBDA関数で変数化します。
　▼　
以下でA2:A14を「x」、"◆"を「y」に置き換えました。

=LAMBDA(x,y,
    ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(
        FILTER(x,x<>"")
    &y,
        COUNTIF(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(FILTER(x,x<>""))))
    )),y)))
)(A2:A14,"◆")

FILTER(x,x<>"")も2回使われているので「z」に置き換えたいと思います。

FILTER(x,x<>"")ってなんだっけ？
「どのように構築したか」の
・Step１で「テキストの一覧」を導き出した関数①
・Step２の関数④の解説内で「元データの空白ではない行の数」を数えるために使ったROWS(関数①)の中身
用途の異なる関数ですが一文にまとめたことで省略可能です。

　▼
なお、FILTER(x,x<>"")内の「x」の省略状態を維持するため、記述位置はLAMBDA(x,yの内側です。

最終形態

=LAMBDA(x,y,
    LAMBDA(z,
        ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(REPT(
            z
        &y,
            COUNTIF(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)),SEQUENCE(ROWS(z)))
        )),y)))
    )(FILTER(x,x<>""))
)(A2:A14,"◆")

省略できそうなところがなくなりました。

これにて最適化完了です。
最後までお付き合いいただき、ありがとうございます。

---