Googleスプレッドシートでは、同じ数式を下方向へコピーする代わりに、ARRAYFORMULAなどを使って1つの数式だけで複数行へ結果を展開することができます。ただし、通常のセル参照をそのまま配列化すると、行ごとに変化する条件判定が正しく動作しない場合があります。
特にCOUNTIFSで「その行までの範囲内で同じ組み合わせが初めて登場した場合だけ表示する」といった処理は、単純なARRAYFORMULAでは対応しにくい処理です。この記事では、H列へ入力していた判定式をスピル化する考え方と、正しく動作する数式について解説します。
元のCOUNTIFS数式が行っている処理を理解する
元の数式は以下のような処理をしています。
=IF(COUNTIFS(D$5:D5,D5,G$5:G5,G5)=1,"残高","")
この数式では、D列とG列の組み合わせが、その行までの範囲で初めて登場した場合にだけ「残高」と表示しています。
例えばD列が商品名、G列が日付や分類などの場合、同じ商品と同じ条件の組み合わせが複数回出てきても、最初の1行だけに印を付けるような使い方ができます。
ポイントは、D$5:D5のように開始行は固定し、終了行だけが下へコピーするたびに変化する範囲になっていることです。
ARRAYFORMULAだけでは元の数式をそのままスピルできない理由
ARRAYFORMULAは複数行へ結果を展開する便利な機能ですが、コピー式で使っていた「現在の行まで」という考え方を自動的に再現することはできません。
例えば以下のような式では正しく動作しません。
=ARRAYFORMULA(IF(COUNTIFS(D$5:D,D5:D,G$5:G,G5:G)=1,"残高",""))
この場合、各行ごとの累積範囲ではなく、列全体を対象にCOUNTIFSを実行するため、元の数式とは異なる結果になります。
スピル化するには、各行ごとに異なる終了位置を作る必要があります。
スピル化する場合はSCAN関数を利用する
Googleスプレッドシートでは、SCAN関数を利用すると、上から順番に処理を行いながら結果を配列として返すことができます。
今回のような「その行までに同じ組み合わせが存在するか確認する」処理には、SCANを使った方法が適しています。
例えば以下のような数式でスピルできます。
=MAP(D5:D,G5:G,LAMBDA(d,g,IF(d="","",IF(COUNTIFS(D$5:D,d,G$5:G,g)-COUNTIFS(D$5:D,d,G$5:G,g,D$5:D&G$5:G,d&g)+1=1,"残高",""))))
ただし、データ量や目的によっては別の書き方のほうが分かりやすい場合があります。
実用的なスピル数式の例
Googleスプレッドシートで現在の行までの重複チェックを行う場合、以下のような形でも作成できます。
=MAP(ROW(D5:D),LAMBDA(r,IF(INDEX(D:D,r)="","",IF(COUNTIFS(D$5:INDEX(D:D,r),INDEX(D:D,r),G$5:INDEX(G:G,r),INDEX(G:G,r))=1,"残高",""))))
この数式ではMAPを使って行番号ごとに処理し、元のD$5:D5やG$5:G5という範囲指定を再現しています。
例えば5行目ではD5:G5だけを確認し、10行目ではD5:G10までを確認するため、元のオートフィル式と同じ動きになります。
元の数式に近い動きをさせるならMAPを使う
今回のようなケースでは、ARRAYFORMULAよりもMAP関数を利用するほうが自然です。
MAPは1行ごとに処理を実行できるため、オートフィルしていた数式をスピル形式へ変更するときに役立ちます。
特に以下のような処理ではMAPが向いています。
- 現在の行までの累計を計算する
- 前の行までのデータを参照する
- 各行ごとに条件範囲が変わる処理をする
一方、単純な合計や平均、条件分岐だけで済む処理ならARRAYFORMULAのほうが簡単です。
まとめ
GoogleスプレッドシートでCOUNTIFSを使ったオートフィル式をスピル化する場合、単純にARRAYFORMULAで囲むだけでは正しく動作しません。
理由は、元の数式が「5行目から現在の行まで」という可変範囲を利用しているためです。
このような処理ではMAPやSCANなどの配列処理関数を利用すると、オートフィルせずに1つの数式だけで同じ結果を作成できます。特に行ごとに参照範囲が変化する数式は、ARRAYFORMULAではなくMAPを使うことで解決できるケースが多くあります。
コメント