GoogleスプレッドシートのSCAN関数で行数が増えすぎる問題と対策

GoogleスプレッドシートでSCAN関数やLAMBDAを使ったスピル式は便利ですが、想定以上の行数が生成されることがあります。この記事では、SCAN関数の仕組みと、行数を制御する方法を解説します。

SCAN関数とスピル式の仕組み

SCAN関数は累積計算を行い、入力範囲に対して結果をスピルして表示します。たとえばY5セルに入力した場合、下方向に結果が展開されます。

入力範囲に大量の空白や条件に合わないセルが含まれると、想定以上の行が生成されることがあります。これが莫大な行数が追加される原因の一つです。

LAMBDA関数をSCANと組み合わせると、計算内容は自由度が高くなりますが、条件を正確に指定しないと不要なセルまで計算対象になります。

特にFILTER関数で条件指定を行う場合、範囲の末尾に空白セルが含まれるとスピル範囲が大きくなりやすいです。

不要な行がスピルしないようにするには、FILTER関数で範囲を明確に限定したり、IF関数で空セルを除外する方法があります。

例えば、Y5セルに=SCAN(0,FILTER(X5:X,W5:W>0),LAMBDA(x,y,x+y))の代わりに、FILTER範囲をX5:X100やW5:W100のように限定するとスピルが制御できます。

莫大な行数を避けつつ累積計算を行いたい場合、ARRAYFORMULAとIF関数を組み合わせる方法も有効です。例: =ARRAYFORMULA(IF(W5:W100>0,CUMSUM(X5:X100* (W5:W100>0)),””))

この方法では、条件を満たさないセルは空白となり、不要なスピルが発生しません。

SCAN関数とLAMBDAを使ったスピル式では、入力範囲や条件設定に注意することが重要です。範囲を限定したり、ARRAYFORMULAとIF関数を組み合わせることで、想定以上の行数が追加される問題を避けることができます。