以前に書いた記事で使用した「空白を上の行の値で補完する関数」の応用を紹介します。
表の空白行を「上の行の値」で埋めて出力する配列関数
前回のあらすじ
以下の画像の表のように〈同上〉の意味であえて用意された空白の行、
これを補完したものを別の列に出力する配列関数を前回の記事で紹介しました。
=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))))(元データの範囲))
本題
前回行った対応は上記の関数を使用しなくても実は可能です。
以下のようにIF関数で「指定のセルが空白ならばひとつ上のセルの内容を返す」と書き、必要な行数だけオートフィルさせる方法が最もシンプルですし、この方法が今や一般的になっていると思います。
| 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) |
この処理と同じ結果を「配列数式」で導く。という対応を前回行ったわけですが、その最大のメリットは今回の応用のように「 別の関数の内部で使用できる 」という点にあります。
FILTER関数との融合
以下が前回完成させた関数です。
=ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(元データの範囲))
上記関数の末尾の 元データの範囲 に空白行の点在する列の範囲名を記入し、どこか異なるセルに配置すれば以下の画像のように空白を補完した結果が書き出されます。
上記の技術を踏まえた上で、
もし上記の元データの表から『りんご』の生産地とシェア率だけのデータを抽出したい場合にはどうするかを考えます。
１．元データに隣接した任意の列に上記の「空白補完関数」を書き込む。
２．出力された空白補完列を範囲に含めてFILTER関数を使用する。
と言った手順がまず思いつくでしょう。
ですが、実は上記の手順１のように元データに隣接したどこかしらの列をわざわざ用意して書き込む必要はなく、この対応はFILTER関数の内部で処理することが可能です。
FILTER関数の記述ルールを見てみましょう。
=FILTER(配列,含む)
上記の 配列 にフィルターを行う全体の範囲を記入し、 含む に条件を記入します。
条件とは、例えば「検索範囲=検索値」ですね。
=FILTER(配列,検索範囲=検索値)
と言った書き方をすれば、配列に指定した範囲において検索範囲（例えば品名の列）が検索値（例えばりんご）に等しい行だけを抽出できます。
実はこの 検索範囲 に先程の「空白補完関数」を設定することが可能なのです。Amazing！！
=FILTER(配列,
検索範囲
=検索値
)
＋
=ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(元データの範囲))
▼
=FILTER(配列,
ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(検索範囲))
=検索値
)
これでFILTER関数との融合がなされました。
では実際に動作確認です。
配列に表の全体範囲（青い点線）、検索範囲に品名の列（ピンクの点線）、検索値に"りんご"と設定して別のセルに書き込んでみます。
結果以下の画像の右側のように『りんご』の生産地とシェア率だけのデータを抽出することに成功しました。
結論
以下の関数を用いることで、条件に使用したい列に空白の行があってもそれを〈同上〉として処理し、その行を条件一致する行に付随させて抽出することが可能になります。
また、今回は抽出という機能に絞りましが、「=」以外の演算子の使用や、さらなる他の関数との組み合わせにも対応できると思われます。
ワイルドカードの使用やより細かな条件を目指す意味ではQUERY関数との融合も良いと思います。
=FILTER(配列,
ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(検索範囲))
=検索値
)
=FILTER(配列,ARRAYFORMULA(LAMBDA(x,LAMBDA(y,VLOOKUP(y,{y,x},2,0))(SCAN(0,IF(x="",0,1),LAMBDA(a,b,a+b))))(検索範囲))=検索値)
以上です。
最後までお付き合いいただき、ありがとうございます。