|
覚えよう！
●行ずつ合計する
＝SUM(OFFSET(基準セル,ROW(a1)*●-●,0,●,1)
3行ずつ合計する →
SUM(OFFSET(基準,row(a1)*3-3,0,3,1))
F列に各チーム（3行ずつ）の合計を出してみましょう。基点を3ずつ移動して合計します。3行ずつ基点を移動するには、0,3,6…のように3の倍数になるようにするため、3をかけます。順に3×0、3×1、3×2…としたいので、そこはROW関数を使ってa1,a2,a3を指定します（a列の内容は何でもよい）
= SUM(OFFSET(参照,ROW(a1)*3-3,列,高さ,幅）
これで行の移動が常に3行ずつ移動します。
row(a1)*3-3 → 0
row(a2)*3-3 → 3
row(a3)*3-3 → 6
全てこの方法で出せます。4行ずつなら0,4,8,…となるのでrow(a1)*4-4…
◆
解説 ◆
-
3の倍数（3×0, 3×1,3×2,3×3…）→
ROW関数を使います（オートフィルで行番号増える＝1ずつ増える）
-
行番号を使うのでa1,a2…を使います（a列の内容は無関係）
■ 5行分ずつ合計したい（各範囲末尾に合計）
MOD関数はある数値で割った余りがでます。ぴったり数値で割ると余り0なのでこれを条件にします。
末尾に合計を出すのでOFFSET関数で−セルを指定します（↑方向が合計範囲）
●行分ずつ合計を出す（※範囲末尾に合計）
=IF(MOD(ROW(A1),●)=0,SUM(OFFSET(数値セル,0,0,-●,1)),"")
B1に
IF(MOD(ROW(A1),5)=0,SUM(OFFSET(B1,0,0,-5,1)),"") と入力しオートフィル
◆
解説 ◆
数字は倍数で割れば必ず割り切れる（つまり除数0になる）ので、これを条件に5の倍数の行なら、そこからマイナス5行分の範囲を合計します。
IF(MOD(ROW(A1),5)=0,SUM(OFFSET(B1,0,0,-5,1)),"")
もし行番号を5で割った除数が0なら（5,10,15,20…の行番号が合致）、その場所からマイナス5行分の範囲を合計する
◆
補足 ◆
下図のように、○行分が同じ名称（1年、2年、3年）であればSUMPRODUCT関数やSUM(IF…)の配列数式が使えます。
D2に IF(COUNTIF($A$2:A2,A2)=1,SUMPRODUCT((A2=$A$2:$A$10)*1,($B$2:$B$10)),"")
または
D2に {=IF(COUNTIF($A$2:A2,A2)=1,SUM(IF(A2=$A$2:$A$10,$B$2:$B$10)),"")}
※青字のIF(COUNTIF($A$2:A2,A2)=1
は、各学年の最初のセルに合計が表示されるように追加しています。
|