基本的なことなのかもしれませんが、方法がわからないので教えてください。
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ベストアンサー
- 回答日時:
こんにちは。
>どうも、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
- 回答日時:
[回答番号:No.2この回答へのお礼]へのコメント、
》 計算が遅くなってしまうように思うのですが、気のせいでしょうか?
そう思うのなら、他人に頼らず、自分でストップウォッチ片手に計測してみれば済むことです。
案の定だったか、思い込みだったか、参考までに教えてください。
なお、作業列を設けても構わないのなら、固執される SUMIF が使えます。
なお、配列数式 {=SUM((MOD(ROW(A1:A100),4)=1)*(A1:A100))} でもよろしいかと。でも、「計算が遅くなってしまうように思う」のかな?
ご回答ありがとうございました。
一行のみ利用してみたところ、速度は変わりませんでした。
ただ、ある一定の個数を超えたところから再計算(CPUはCore2Duoの1.6Ghz)で1分を超える時間にかかってしまうようになりました。
セル数で言うと、1万を超えるあたりだと思うのですが、式が複雑担ってくるとある時点で急に遅くなるようです。
No.4
- 回答日時:
=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 の結果(条件が成り立つ場合は 1、違う場合は 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
- 回答日時:
こういう問題はエクセル関数の8合目ぐらいの問題にさしかかったといえよう。
時どき出る有名なパターンの質問なんだ。以下は、4行おきを1,4、7行・・を足す問題と捉えてやっています。
3つやり方があって
(1)4行ごとにサイン(コード)をつける。作業列を使う。
空き列を使うが素直な考えで、関数も簡単なものSUMIFが使える。
A。関数で
=IF(MOD(ROW()-1,3)=0,1,"")を入れて、下方向に式複写
B。複写で
例えば空き列D列D1:D3に
1
空白
空白
のデータを入れて、D1:D3をコピーし、D4:D100(データの終わりまで)範囲指定して貼り付け
そしてSUMIF関数でD列の1の行の目的の数のある列の数字をを足せばよい。=SUMIF(D1:D30,1,B1:B30)
===
(2)行が3の倍数ということを条件にして加える。
SUMIFは条件部にMOD(ROW()-1,3)=0は使えないので、SUMPRODUCT関数を使う。条件付き加算にSUMPRODUCT関数を使うのは定石。本来は内積を
出すものだが。
=SUMPRODUCT((MOD(ROW(A1:A30)-1,3)=0)*(B1:B30))
=====
(3)配列数式を使う
=SUM(IF(MOD(ROW(A1:A30)-1,3)=0,B1:B30),0)
と入れてSHIFT,CTRL,ENTERキーを同時に押す。
B列うお足すとして1-30の連続数が入っている例だと、いずれの方法でも145
ーー
ほかにVBAとかでも簡単に(3-4行のコードで)出来る。
No.2
- 回答日時:
=SUMPRODUCT((MOD(ROW(A1:A100),4)=1)*(A1:A100))
これが4行おき A1 からの場合。A2、A3、A4 からの場合は上式中の =1 をそれぞれ =2、=3、=0 に置き換えればOK。
5行おきは上式中の 4 を 5 に置き換え、かつ、A4、A5 からの場合が それぞれ =4、=0 になります。
ご回答ありがとうございました。
sumifではなく、sumproductを利用しないとうまくいかないのでしょうか?
どうも、sumproductをたくさん使うとエクセルシートの計算が遅くなってしまうように思うのですが、気のせいでしょうか?
No.1
- 回答日時:
ツールバーの[Σ](オートSUM)を使うと便利です。
和(合計)の機能です。http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumo …
Excel2007なら
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/e …
A5にA1~4の合計式を作ったら
A5をコピーして合計式を入力するセルに貼り付けてます。
コピー:
A5を選択して「Ctrl」を押しながら「C」のキーボードを押します。
貼り付け(ペースト):
式を入力するセルを選択して「Ctrl」を押しながら「V」
を繰り返します。
今後、質問される時はExcel2007とかExcel2003など
Excelの種類を明記した方が良いでしょう。
(Excel2007とExcel2003以前ではかなり違ってきます。)
書店でご利用の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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
推しミネラルウォーターはありますか?
推しミネラルウォーターがあったら教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
【Excel】4つとばしで合計する方法
Excel(エクセル)
-
EXCELで10行ごとの和を出したい
Excel(エクセル)
-
3列ごとに合計を出したい
Excel(エクセル)
-
-
4
エクセルで連続データから、数個飛ばしのデータを抜き取る方法
Excel(エクセル)
-
5
エクセルVBA 4行飛ばしで転記するループ処理
Excel(エクセル)
-
6
5列ごとに合計したい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで極大値を拾うには
-
エクセルVBAで別BOOKに「名前の...
-
EXCELで2つの数値のうち大きい...
-
エクセルで二つの数字の小さい...
-
Excelで隣のセルと同じ内容に列...
-
エクセル 文字数 多い順 並...
-
PowerPointで表の1つの列だけ...
-
エクセルで時刻(8:00~20:00)...
-
2つのエクセルのデータを同じよ...
-
エクセルでオートフィルタのボ...
-
エクセルのオートフィルタで最...
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセル(勝手に太字になる)
-
エクセルで特定の文字が入って...
-
エクセルで最初のスペースまで...
-
エクセルで、2種類のデータを...
-
EXCELで 一桁の数値を二桁に
-
エクセルの項目軸を左寄せにしたい
-
Excel 文字列を結合するときに...
-
Excel 頭に「0」がついている...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで極大値を拾うには
-
エクセルVBAで別BOOKに「名前の...
-
【Excel】4行ごとの和を集計し...
-
ネットワーク上のエクセルとリ...
-
Excelで数行間隔で離れているデ...
-
条件付のsum,max,min関数の書き方
-
SUMIF関数の合計範囲を広くする...
-
2つの条件を満たした金額を合計...
-
エクセルでの複数条件での平均...
-
EXCEL: 数式を含んだΣは可能で...
-
EXCELで2つの条件に当てはまる...
-
(EXCEL関数)特定範囲内の最大値
-
エクセル初心者です
-
エクセル関数使用時の範囲指定...
-
EXCEL IF関数 AND、OR条件
-
Excel、sumifはありますが、min...
-
EXCELで2つの数値のうち大きい...
-
エクセルで二つの数字の小さい...
-
Excelで隣のセルと同じ内容に列...
-
PowerPointで表の1つの列だけ...
おすすめ情報