セルに次の数式がありますM8:
{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}
この数式を使用すると、M16 から始まる 4 行ごとに、列 M の週あたりの合計時間数を取得できます (m16+m20+m24+m28 など)。新しいデータを常に上部に追加していくので、スプレッドシートを開いたときに、最新の情報が上部に表示されます。新しいデータに 4 つの新しい行を追加すると、数式は次のようになります。
{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}
新しい週の合計時間は M16 になるので、常に M16 から追加を開始するようにするにはどうすればよいですか。
答え1
M16 の値をファイルの下部または移動しないセルに配置します。実際、行を追加するたびに、セルへのすべての参照が自動的に変更されます。
答え2
まず、これをSUMPRODUCT数式に変更する必要があります。配列数式として入力する必要がなく、私見では、その方が使いやすいです。
=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))
行を挿入するときの Excel の動作を回避するには、データをテーブルに変換することをお勧めします。(挿入リボン > テーブル) これにより、「この領域のすべてのデータ」を参照することが非常に簡単になり、テーブルが大きくなったり小さくなったりするたびに、希望どおりに更新されます。その場合、数式は次のようになります。
=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))
何らかの理由でテーブルに変換できない場合は、代わりに名前付き範囲を作成してそれを参照することをお勧めします。名前付き範囲の数式は次のようになります。
=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)
...そしてそれを参照する式は次のようになります。
=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))
...rngHoursは、名前付き範囲に付けた名前です。
本当に名前付き範囲を使用したくない場合は、全体を 1 つの大きな数式にまとめることもできますが、関数ではありますが、煩雑になります。
=SUMPRODUCT(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)*(MOD(ROW(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15))-ROW(OFFSET(Sheet3!$M$1,15,0)),4)=0))