【Excel】4行ごとの和を集計したいです

質問

基本的なことなのかもしれませんが、方法がわからないので教えてください。

Excelの表で、A1の次はA5、A9というように4行ごとの数字の和を集計したいです。

今は、とても原始的な方法で、「=A1＋A5+A9」という具合に足しているのですが、これですと数が増えてくると大変です。
また、A2から4行ごとに足したいこともあり、とても不便です。
そこで、行番号を取得して、4で割り切れるものだけ指定の数まで足していきますよ、という式を組み立てたいと思いました。

sumifと行番号を取得する関数と割り算のあまりを返す関数を組み合わせればできると思うのですが、どのように書くのが簡単でよいでしょうか？

私が、思うのは

=sumif(A1:A100,MOD(ROW(),4))

としているのですが、これでよいでしょうか？
また、A2から4行おきの場合は

=sumif(A2:A100,MOD(ROW()+1,4))でよいのでしょうか？

ほかによい方法があったり、そもそもこの式が間違っているのかよくわかっておりません。
ご指導をお願いいたします。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

>どうも、sumproductをたくさん使うとエクセルシートの計算が遅くなってしまうように思うのですが、気のせいでしょうか？

キャッシュメモリからスワップが発生して、計算が遅くなってしまう現象だと思います。実際に、だいたい、10～100倍の計算スピードが落ちます。その限界数が、参照される対象計算セルが、5,500個ぐらいのはずです。また、通常の関数は、引数の変更によって、その対象の関数の内部だけが計算するようにできていますが、配列数式の場合は、ひとつの関数の変化が全体に及ぼすので、あまり大量ですと、とても計算に時間が掛かるようになってしまいます。これを参考にしてワークシートを設計する必要があります。
その場合、計算式を分散する、マクロの計算に替える、データベース関数にする、などの措置が行われます。Office 2007　ですと、計算方法が変わりますから、スピードが落ちることは目立たなくなります。

なお、作業列を設けるなら、例えば
H1　～H100　の範囲まで

=MOD(ROW(A1),4)=1
をコピーして、

このような数式で、解を得られます。
=SUMIF(H1:H100,TRUE,A1:A100)

別に、どちらでもよいと思います。

なお、速い遅いの計算速度は、よほど大量にならないと、秒単位にはなりません。一般の数式は、だいたい、10ms （1000分の1秒)以下ですが、100msぐらいまで、あまり気にならないのですが、500msだと、もう認知できるレベルになります。数秒や数10秒になれば、その都度、再計算が発生してしまうので、使用上の支障が出てしまいます。

回答No.5

noname#204879

[回答番号：No.2この回答へのお礼]へのコメント、

》 計算が遅くなってしまうように思うのですが、気のせいでしょうか？

そう思うのなら、他人に頼らず、自分でストップウォッチ片手に計測してみれば済むことです。
案の定だったか、思い込みだったか、参考までに教えてください。

なお、作業列を設けても構わないのなら、固執される SUMIF が使えます。

なお、配列数式 {=SUM((MOD(ROW(A1:A100),4)=1)*(A1:A100))} でもよろしいかと。でも、「計算が遅くなってしまうように思う」のかな？

お礼

ご回答ありがとうございました。
一行のみ利用してみたところ、速度は変わりませんでした。
ただ、ある一定の個数を超えたところから再計算（CPUはCore2Duoの1.6Ghz)で1分を超える時間にかかってしまうようになりました。
セル数で言うと、1万を超えるあたりだと思うのですが、式が複雑担ってくるとある時点で急に遅くなるようです。

回答No.4

hallo-2007

=sumif(A1:A100,MOD(ROW(),4))
は良いところまで来ていると思いますが、SUMIF関数だけではROW関数が、式を入れたセルのみになってしまいます。
作業列を利用するか、配列関数を利用するかになります。
例えば、B列に　=MOD(ROW(),4) と入れて下までコピィしておけば、0,1,2,3と繰り返し表示されるので
後は　SUMIF関数でご希望の集計が出来ると思います。
さて、これを作業列を使わずに処理しようとすると配列関数を使用することになります。
空いているセルに
=SUM((A1:A100)*(MOD(A1:A100,4)=0))　と入れて　Ctrl+Shift+Enter　で決定します。式が{}で囲まれて配列関数をして認識されます。
A1の値　＊　MOD(A1,4)=0　の結果（条件が成り立つ場合は　１、違う場合は　０）の計算、それをA1～A100まで繰り返して、それぞれの結果の合計（SUM)というわけです。

別のセルに　=SUM((A1:A100)*(MOD(A1:A100,4)=1))　で同様
=SUM((A1:A100)*(MOD(A1:A100,4)=2))
=SUM((A1:A100)*(MOD(A1:A100,4)=3))　でも同様にします。
SUMPRODUCT関数も配列関数の一種ですので同様の考え方です。
作業列に相当する部分をパソコンの内部で処理しますので沢山使用するとある時点で急に作動が遅くなることを感じると思います。
今回の場合、どうしても作業列が必要なので、式を四つ入れて遅くなるようであれば、作業列を準備した方法にしてみてください。

お礼

ご回答ありがとうございました。
配列関数というものをはじめて教えていただきましたので、勉強したいと思います。

回答No.3

imogasi

こういう問題はエクセル関数の８合目ぐらいの問題にさしかかったといえよう。時どき出る有名なパターンの質問なんだ。
以下は、４行おきを１，４、７行・・を足す問題と捉えてやっています。
３つやり方があって
（１）４行ごとにサイン（コード）をつける。作業列を使う。
　　　空き列を使うが素直な考えで、関数も簡単なものSUMIFが使える。
　　　A。関数で
　　　　=IF(MOD(ROW()-1,3)=0,1,"")を入れて、下方向に式複写
　　　B。複写で
　　　　例えば空き列D列D1:D3に
　　　　１
　　　　空白
　　　　空白
のデータを入れて、D1:D3をコピーし、D4：D100（データの終わりまで）範囲指定して貼り付け
そしてSUMIF関数でD列の１の行の目的の数のある列の数字をを足せばよい。=SUMIF(D1:D30,1,B1:B30)
＝＝＝
（２）行が３の倍数ということを条件にして加える。
SUMIFは条件部にMOD(ROW()-1,3)=0は使えないので、SUMPRODUCT関数を使う。条件付き加算にSUMPRODUCT関数を使うのは定石。本来は内積を
出すものだが。
=SUMPRODUCT((MOD(ROW(A1:A30)-1,3)=0)*(B1:B30))
＝＝＝＝＝
（３）配列数式を使う
=SUM(IF(MOD(ROW(A1:A30)-1,3)=0,B1:B30),0)
と入れてSHIFT,CTRL,ENTERキーを同時に押す。
B列うお足すとして１－３０の連続数が入っている例だと、いずれの方法でも１４５
ーー
ほかにVBAとかでも簡単に（３－４行のコードで）出来る。

お礼

大変わかりやすいご回答ありがとうございました。
参考にして、勉強いたします。

回答No.2

noname#204879

=SUMPRODUCT((MOD(ROW(A1:A100),4)=1)*(A1:A100))
これが４行おき A1 からの場合。A2、A3、A4 からの場合は上式中の =1 をそれぞれ =2、=3、=0 に置き換えればＯＫ。
５行おきは上式中の 4 を 5 に置き換え、かつ、A4、A5 からの場合が それぞれ =4、=0 になります。

お礼

ご回答ありがとうございました。
sumifではなく、sumproductを利用しないとうまくいかないのでしょうか？
どうも、sumproductをたくさん使うとエクセルシートの計算が遅くなってしまうように思うのですが、気のせいでしょうか？

回答No.1

hayasi456

ツールバーの[Σ]（オートSUM）を使うと便利です。和（合計）の機能です。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumon/seiseki/sei-n03.htm#goukei
Excel２００７なら
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-input3.html#auto_sum

A5にA1～４の合計式を作ったら
A５をコピーして合計式を入力するセルに貼り付けてます。

コピー：
A5を選択して「Ctrl」を押しながら「Ｃ」のキーボードを押します。
貼り付け（ペースト）：
式を入力するセルを選択して「Ctrl」を押しながら「Ｖ」
を繰り返します。

今後、質問される時はExcel２００７とかExcel２００３など
Excelの種類を明記した方が良いでしょう。
（Excel２００７とExcel２００３以前ではかなり違ってきます。）

書店でご利用のExcelの解りやすそうな図の多い入門書を購入することをお勧めします。
Web上にも
http://www.eurus.dti.ne.jp/~yoneyama/
http://www.aoten.jp/snack/index.html
http://kokoro.kir.jp/excel/index.html
http://www.geocities.jp/office_inoue/main.htm

お礼

ご回答ありがとうございました。
利用しているのは2003です。