Excel 3列ごとに集計したい

月　　　　4月　5月　6月　7月　・・・・
りんご　　20　25　30　35　・・・
みかん　　10　15　20　23・・・・

上記のような表を3カ月ごとに集計する関数を教えてほしいです。
→4月～6月分、7月～9月分　のような形

これを3年分、列で推移をだしているため、オートフィルでいれれば3か月分ずつ集計するようなまとめの表をいれたいです。

色々調べて下記のようなものをいれてみたのですが、違う数字が出てしまい・・・混乱しております。
何か良い方法を教えていただければと思います。
=SUMPRODUCT((MOD(COLUMN(K13:V13),3)=0)*(K13:V13))

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2018/10/08 14:30

K13セルからV13セルまでの値を3列おきに合計を出したいということでしょうか。

W13セルからZ13セルまで3カ月おきに合計する場合は、
OFFSET関数を使ったほうが良いと思います。
この場合、まずはオートフィルを使わない場合、どうなるのかを考えます。
W13セル
　＝SUM(OFFSET(K13,0,0,1,3))
X13セル
　＝SUM(OFFSET(K13,0,3,1,3))
Y13セル
　＝SUM(OFFSET(K13,0,6,1,3))
Z13セル
　＝SUM(OFFSET(K13,0,9,1,3))
こんな感じになります。
OFFSET関数内で変わっている数字に注目すればオートフィルで何とかなりそうですね。
あとはこの数字をCOLUMN関数を使って引っ張ればいい。
　COLUMN(A1)*3-3
とでもしましょう。
これならオートフィル後に、
　1*3-3　→ 0
　2*3-3　→ 3
　3*3-3　→ 6
　4*3-3　→ 9
となってくれます。
（本来は 「(COLUMN(A1)-1)*3」 としたほうが、何をしているのか分かりやすいんですけどね）
あとはOFFSET関数の基準のK13セルがずれないように「$K13」と列番号の前に $ を付けるだけ。
すると最終的に
W13セル
　＝SUM(OFFSET($K13,0,COLUMN(A1)*3-3,1,3))
となります。

…のように、1ステップずつ考えるようにしましょう。
いきなり数式を考えるのは慣れてからでよいでしょう。


・・・余談・・・

　MOD(COLUMN(K13:V13),3)=0
これがどんな結果を返しているのかを確認してみましょう。
SUMPRODUCT関数の中で使われているため「(K13:V13)」が配列として認識されています。
　MOD({11,12,13,14,15,16,17,18,19,20,21,22},3)=0
なんですね。
そのため、5月、8月、11月、2月の列のデータを拾うようになっていますよ。

この回答へのお礼

考え方から教えてくださり、本当にありがとうございます。そこの部分が知りたかったんです！

A1を指定するのは、参照先の列設定が０になるからなんですね。
この掛け算したり引き算したりする意味の理解がずっと追いつかなかったので、これでようやくCOLUMNのセットの仕方の意味がわかりました。
おかげさまで応用できそうです！非常に助かりました。

お礼日時：2018/10/14 11:54

No.2 回答者： mike_g 回答日時： 2018/10/09 15:11

添付図参照

Sheet2 において、
１．セル B1、B2 に下記の式を入力
￣￣B1: =OFFSET(Sheet1!$B1,,(COLUMN(A1)-1)*3)&"～"&OFFSET(Sheet1!$D1,,(COLUMN(A1)-1)*3)
￣￣B2: =SUM(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A$2:$A$4,0),1+(COLUMN(A1)-1)*3,,3))
２．セル B2 を下方にオートフィル
３．範囲 B1:B4 を右方にズズーッとオートフィル

この回答へのお礼

図でも示していただきありがとうございます！
範囲名も出てくるようにできるんですね。
MATCHとの組み合わせ方も参考にさせていただきます。

お礼日時：2018/10/14 12:05