実績数値をとるときに、日の数値をならして、週ごとの実績をとることがあります。このときに、各セルに同じような式関数を並べていたら大変です。Google Spreadsheet ではひとつの関数で複数のセルに反映させる便利な仕組みがあります。これを使って、週の実績を表現してみましょう。
↑この画像で気づいた人は以降の記事を見る必要はありません。
7番目のセルが開始地点で、そこから上のセルを参照します。
相対位置を辿るのに使われるのが OFFSET 関数です。
offset(C10:C34,0,0)
第1引数で範囲を、第2引数は参照セルからの相対行数、第3引数が相対列数です。なお、後で ArrayFormula 関数を使うので、第4、第5引数は指定しません。
今回は上に7つ分辿るので、相対行として0から-6までのOFFSET関数を足します。SUM関数だとArrayFormulaの前に指定範囲をすべて足してしまうので、数式として足します。
offset(C10:C34,0,0)+offset(C10:C34,-1,0)+offset(C10:C34,-2,0)+offset(C10:C34,-3,0)+offset(C10:C34,-4,0)+offset(C10:C34,-5,0)+offset(C10:C34,-6,0)
ちなみに、OFFSET関数を使わずに、ひとつずつずらした範囲指定でも表現可能です。上の例でいうとC10:C34 + C9:C33 + C8:C32…のような感じです。ただしメンテナンス性がよろしくないので、OFFSET 関数を使ってひとつの範囲をずらしながら参照しています。
これを足したセル数で割り算します。今回は週平均なので7つセルを足してから7で割ります。足し算を優先させるために括弧で括ります。
(offset(C10:C34,0,0)+offset(C10:C34,-1,0)+offset(C10:C34,-2,0)+offset(C10:C34,-3,0)+offset(C10:C34,-4,0)+offset(C10:C34,-5,0)+offset(C10:C34,-6,0))/7
最後に、範囲指定されたすべてのセルに適用させるために ArrayFormula 関数を使って完成です。
ArrayFormula((offset(C10:C34,0,0)+offset(C10:C34,-1,0)+offset(C10:C34,-2,0)+offset(C10:C34,-3,0)+offset(C10:C34,-4,0)+offset(C10:C34,-5,0)+offset(C10:C34,-6,0))/7)
検算してみましょう。1/20から上に7つ分のセルを選択します。
右下の「合計」となっている部分をクリックすると、平均値が出ているので、それと1/20の週平均を比較します。
