教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010:○行分ずつ合計する(MOD,ROW,OFFSET)

 

 

 

 

 


 

 

 

 


 

 

 

 

 

覚えよう!  行ずつ合計する

=SUM(OFFSET(基準セル,ROW(a1)*●-,0,,1)

 

3行ずつ合計する →  SUM(OFFSET(基準,row(a1)*3-3,0,3,1))

 

■ 例:3行分ずつ合計したい   ★サンプルダウンロード(RowOffsetMod.xlsx)

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 は、各学年の最初のセルに合計が表示されるように追加しています。

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows