以前書いた以下の記事の結果をより簡単に導くアプローチを思いついたため再投稿します。
表の空白を「上の行の値」で埋める配列関数を考える（旧）
他人の作ったデータを取り扱う際に時折直面する「一部の行が省略されている」問題。
人間同士が目を通すだけの資料では「同に上じ」と解釈できるため特に問題はありませんが、データとして取り扱うときは困っちゃいますよね。
本記事では私なりに行った解決策を書き留めます。
本記事の関数手法は簡単なIF関数のオートフィルで実現可能な処理をあえて配列数式化しました。
この対応の最大のメリットである「他の関数との複合」は以下の記事で取り上げています。
条件列の空白行を〈同上〉として扱い、条件一致する行に付随させて抽出を可能にするFILTER関数
結論
以下の関数を使用することで可能でした。
=ARRAYFORMULA(LAMBDA(x,
LAMBDA(y,
VLOOKUP(y,{y,x},2,0)
)(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)))
)(元データの範囲))
=ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(元データの範囲))
上記をコピーしてスプレッドシートの任意のセルに貼り、
末尾の (元データの範囲) の箇所を書き換えるだけで使用可能です。
注意点として、ここに書き込む元データの範囲の幅は１列に限ります。
書式例：
・A列の２行目から8行目までに適応したければ A2:A8
セル参照にする場合は INDIRECT() 形式で記入。
動作確認
例にする以下の表でB2セルに本関数を記入し、
末尾の(元データの範囲) 部分を (A2:A8) と書き換えます。
| A | B | |
|---|---|---|
| 1 | 品名 | 新規カラム |
| 2 | りんご | ココに書く |
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | みかん | |
| 8 |
すると以下のような結果になるでしょう。成功です
| A | B | |
|---|---|---|
| 1 | 品名 | 新規カラム |
| 2 | りんご | りんご |
| 3 | りんご | |
| 4 | りんご | |
| 5 | りんご | |
| 6 | りんご | |
| 7 | みかん | みかん |
| 8 | みかん |
仕組みの解説
本関数では以下の2つの処理を行いました。
・元データの空白を「上の行に同じ値」と扱えるようにナンバリングを行う。
・ナンバリングを元に「空白でない行の値」をVLOOKUPさせた。
噛み砕きながら解説します。
元データの空白を「上の行に同じ値」と扱えるようにする
先に結果をお見せします。
以下の画像のC列に１から始まる連続数列が縦に並んでいると思います。
この数列は、A列の「りんご」から「みかん」に切り替わるまでは１のままであり、「みかん」以降で+１となり２になります。
これは「元データの空白ではない行を通過した時のみ１ずつ増える整数の配列」を出力する関数を置いた結果であり、この結果を利用することで、3行目から6行目を「2行目と同じ値」として扱うことが可能になります。
元データの空白ではない行を通過した時のみ１ずつ増える整数の配列
構造を解説します。
B列：Step１
B2セルには=ARRAYFORMULA(IF(A2:A14="",0,1))と入力しています。
IF関数でA列の元データの空白にあたる行に0、空白でない行に1が返ってくるように指示しています。
これにより、B列には0と1のみで構成された数列が出力されました。
C列：Step２
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ずつ増える整数の配列が出力されました。
関数を最適化
Step１～２の処理を1行にまとめてしまいます。
2つの関数を入れ子にしてしまえばOKです。
（出力結果が配列になるのでARRAYFORMULAは一番外側に置きました）
=ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b)))
上記により「元データの空白ではない行を通過した時のみ１ずつ増える整数の配列」を生む関数を作成できました。
これによって「元データの空白を「上の行に同じ値」と扱えるようにする」が達成できます。
ナンバリングを元に「空白でない行の値」をVLOOKUPさせる
| № | 元データ | VLOOKUP | |
|---|---|---|---|
| 1 | りんご | ▶ | りんご |
| 1 | りんご | ||
| 1 | りんご | ||
| 1 | りんご | ||
| 1 | りんご |
VLOOKUP関数という、表の中から縦方向に一致するものを呼出す関数を使用します。
配列した結果を出すVLOOKUP関数の書き方はこうです。
=ARRAYFORMULA(VLOOKUP(検索値,呼出範囲,列番号,検索の型))
この関数で以下の画像の結果を導く場合
・検索値は「Step1+2」で導いたA列の数列A2:A14
・呼出範囲は検索値のあるA列も含めた元データの範囲のためA2:B14
・列番号は今回は品名だけ呼びたいため呼出範囲内で品名の書かれている列番号の2
・検索の型は完全一致としたいためFALSEまたは0
▼
=ARRAYFORMULA(VLOOKUP(A2:A14,A2:B14,2,0))
と言った書き方になるでしょう。
※VLOOKUP関数の左から右へ検索する性質に合わせ、「Step1+2」の関数は左側に再配置しました。
目的の達成
上記までの方法を利用することで、
「表の空白行を「上の行の値」で埋めて出力する」という目的が達成できました。
より美しくを目指す。
しかし、上記までの方法ですと、
・元データの左にわざわざ列を1つ挿入し「Step1+2」の関数を記入。
・記入した関数の列を含めて範囲指定しVLOOKUP関数を書く。
と、手順にわずらわしさを感じます。
私は誰よりも面倒くさがりなので、このままでは嫌です。
なので、列の挿入もなくし、範囲指定も最低限で済むようにブラッシュアップを行います。
実はそもそも列を足す必要なんてない
今回は解説のためもあり、
以下の画像ではA列にナンバリング、B列に元データという状態を作りました。
「検索値の為に左にナンバー、右に元データ」という状態を表の上に書き出すことで表現しています。
ですが、この状態は配列数式を使うことで、
「表の上に書き出さず」「関数の内部」に構築できます。
配列数式とは ｛ と ｝ の波括弧 で囲うことで複数の範囲を結合することが出来ます。
例えば、E1:F3とJ1:J3それぞれ離れた列に以下のデータがあったとします。
| E | … | J |
|---|---|---|
| 砂糖 | 醤油 | |
| 塩 | 味噌 | |
| 酢 | 酒 |
これを横並び、または縦並びに結合したいときは波括弧で囲い、
{E1:F3,J1:J3}とカンマで区切り記述すると横に結合。
| 砂糖 | 醤油 |
| 塩 | 味噌 |
| 酢 | 酒 |
{E1:F3;J1:J3}とセミコロンで区切り記述すると縦に結合されます。
| 砂糖 |
| 塩 |
| 酢 |
| 醤油 |
| 味噌 |
| 酒 |
では、
関数の内部に「左にナンバー、右に元データ」という状態を作ります
｛ ｝ の波括弧の中で
・左（先）にナンバリングを行う関数
・右（後）に元データの範囲
これを横並びなのでカンマで区切り記述します
={
ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b)))
,
元データの範囲
}
この記述により、
本関数だけで以下の画像のような状態が内部に表現されている形となります。
これを応用することで、
・ナンバリングを元に「空白でない行の値」をVLOOKUPさせる の項で行った
=ARRAYFORMULA(VLOOKUP(検索値,呼出範囲,列番号,検索の型))
┗呼出範囲は検索値のあるA列も含めた元データの範囲のためA2:B14
▼
このA2:B14を上記の配列数式に置き換えることが出来ます。
=ARRAYFORMULA(VLOOKUP(検索値,
{ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b))),元データの範囲}
,列番号,検索の型))
では残る以下の処理も上記の状態に入れ込んでいきましょう
=ARRAYFORMULA(VLOOKUP(検索値,呼出範囲,列番号,検索の型))
┗検索値は「Step1+2」で導いたA列の数列A2:A14
▼
このA2:A14は上記の配列数式の処理で「A列を足す」という作業自体がなくなったため指定が行えなくなります。
ですが、書き込むべき内容（欲しいデータ）は「Step1+2」で導いた数列なので、以下を代入してしまえばOKです。
=ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b)))
▼
=ARRAYFORMULA(VLOOKUP(
ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b))),
{ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b))),元データの範囲}
,列番号,検索の型))
次に
・列番号は今回は品名だけ呼びたいため呼出範囲内で品名の書かれている列番号の2
・検索の型は完全一致としたいためFALSEまたは0
これらも記入します。
列番号の2は配列数式内の列番号をちゃんと数えてくれるため、２のままでOKです。
▼
=ARRAYFORMULA(VLOOKUP(
ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b))),
{ARRAYFORMULA(SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b))),元データの範囲}
,2,0))
はい、なんとこの関数で使用する設定値を「元データの範囲」のみにすることが出来ました。
これだけでもだいぶ楽ですが、最後に不要な記述の整理と最適化を行います。
ARRAYFORMULAは最初の1つだけで十分なので他を削る
・検索値と呼出範囲に代入した関数で使っていたARRAYFORMULAの機能は最初の1つが引き継いでくれるため消してしまって大丈夫です。
▼
=ARRAYFORMULA(VLOOKUP(
SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b)),
{SCAN(0,IF(元データの範囲="",0,1),LAMBDA(a,b,a+b)),元データの範囲}
,2,0))
▼
「元データの範囲」の記述箇所を1つにするため変数化
・LAMBDA関数を使用します。
┗「元データの範囲」を「x」に置き換えます。
・ARRAYFORMULAの内側に書きます。
▼
=ARRAYFORMULA(
LAMBDA(x,
VLOOKUP(
SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)),
{SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)),x}
,2,0))
)(元データの範囲)
)
▼
SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))が重複しているのでこれも変数化
・LAMBDA関数を使用します。
┗「SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))」を「y」に置き換えます。
・「元データの範囲」→「x」の置き換えを維持するため先程のLAMBDA関数の内側に書きます。
▼
=ARRAYFORMULA(
LAMBDA(x,
LAMBDA(y,
VLOOKUP(y,{y,x},2,0)
)(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)))
)(元データの範囲)
)
▼
改行位置などを整えて完成
▼
=ARRAYFORMULA(LAMBDA(x,
LAMBDA(y,
VLOOKUP(y,{y,x},2,0)
)(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b)))
)(元データの範囲))
※ここはもうお好みです。短いので1行にしちゃってもいいと思います。
=ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(元データの範囲))
以上です。
最後までお付き合いいただき、ありがとうございます。