[[20170909231246]]　『SUMIFS関数で複数の合計対象範囲、条件範囲の合計』（柔）　ページの最後に飛ぶ

『SUMIFS関数で複数の合計対象範囲、条件範囲の合計を算出する数式について』（柔）

SUMIFS関数を使って複数ある「合計対象範囲」と「条件範囲」の数式の作り方についてご教授ください。

Sheet2のU7=日付が入っています。
Sheet2のT12=特大、T13=大、T14=中、T15=小、T16=特小を設定しています。
月ごとにFZ列の条件ごとに合計をだしたいです。
以下の数式作成してみました。
=SUMIFS(Sheet1!$GA:$GA,Sheet1!$FZ:$FZ,T16,Sheet1!$A:$A,">="&U$7,Sheet1!$A:$A,"<"&EDATE(U$7,1))
これは、下の図のFZ列の条件ごとにGA列の数値を合計するものです。が・・・これは、JL、JM列まで10個あり、この10個すべてを合計するための数式をどのように構成すればよいか行き詰っております。
出来るだけ、一つの数式でこの10個の合計をだせる数式を作りたいのですが、ご教授のほどよろしくお願いします。

Sheet1

   A・・・FZ    GA・・・GJ    GK・・・JL    JM
1  9/ 9  特大  500     中    300     小    200
2  9/12  中    300     大    400     小    200
3  9/29  特小  100     大    400     中    300
4 10/ 1  大    400     小    200     特大  500
5 10/10  小    200     特小  100     大    400
6 10/25  大    400     中    300　　 小    200
・
・
・

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 揮発性関数(※)であるOFFSETを使ってよければ、

 =SUM(SUMIFS(OFFSET(Sheet1!$GA:$GA,0,{0,1,2,3,4,5,6,7,8,9}*10),OFFSET(Sheet1!$FZ:$FZ,0,{0,1,2,3,4,5,6,7,8,9}*10),T12,Sheet1!$A:$A,">="&U$7,Sheet1!$A:$A,"<"&EDATE(U$7,1)))

 (※)どこかのセルが変更されるたびに、再計算に入らねばならない宿命の関数 
 　  表がすごく大きい様なので、使い物にならないかもです。

（半平太） 2017/09/10(日) 08:04

ご返事ありがとうございます。
基本的にセルを変更することはないと考えており、半平太さんがご教授いただいた関数で見事に結果を出すことができました。誠にありがとうございます。

もう一つお尋ねさせてください。
今度はこの件数を求めるには、SUMIFS関数でよいでしょう？
しかしながら、これも不完全で結果を出せません。
さらなるご教授のほどお願いします。
=SUM(COUNTIFS(OFFSET(DB!$GA:$GA,0,{0,1,2,3,4,5,6,7,8,9}*10),OFFSET(DB!$FZ:$FZ,0,{0,1,2,3,4,5,6,7,8,9}*10),T16,DB!$A:$A,">="&U$7,DB!$A:$A,"<"&EDATE(U$7,1)))
（柔） 2017/09/10(日) 10:53

 件数だけなら・・金額欄(GA:GA) は不要です。

 =SUM(COUNTIFS(OFFSET(DB!$FZ:$FZ,0,{0,1,2,3,4,5,6,7,8,9}*10),T12,DB!$A:$A,">="&U$7,DB!$A:$A,"<"&EDATE(U$7,1)))

 ＞基本的にセルを変更することはないと考えており、

 いえ、セル値の変更ですから、無いなんて事は、まずあり得ません。

 その度に再計算が起きるので、エクセルを使っている時のレスポンスが悪くなるかも知れない、と言うことです。

 実際に何処かのセル値を変更してみて、使い勝手が悪いなぁと感じないなら、問題はないので、
 この件は忘れてください。

（半平太） 2017/09/10(日) 11:22

半平太さん、ありがとうございます。
セル値の変更というのは、、、具体的にどういうことでしょうか？
基本的なことも理解できておらず、申し訳ありません。
（柔） 2017/09/10(日) 11:59

半平太さん、もう一つ教えていただきたいことがあります。

ご教授いただいた以下の数式部分の検索条件である「T12」部分ですが、これをもう一つ検索値を増やしたいのです。「T12でかつG4」という検索条件にしたいのです。その場合、AND(T12,G4)としてみましたが、検索してくれません。
ご教授の程よろしくお願いいたします。
=SUM(COUNTIFS(OFFSET(DB!$FZ:$FZ,0,{0,1,2,3,4,5,6,7,8,9}*10),T12,DB!$A:$A,">="&U$7,DB!$A:$A,"<"&EDATE(U$7,1)))

（柔） 2017/09/10(日) 17:59

 ＞「T12でかつG4」という検索条件にしたい

 「T12」は、FZ、GJ、・・、JL列の範囲内に在るかどうかですよね？

 まさか、「G4」もそれと同じ列範囲じゃないですよね？（そうだったら、該当無しになっちゃいませんか?）

（半平太） 2017/09/10(日) 18:44

半平太さん、ありがとうございます。
半平太さんがおっしゃる通り、T12とG4は異なる列です。
ちなみに、検索値G4の列は、別の列になります。
（柔） 2017/09/10(日) 18:52

文章が変でした。すいません。
別の列になります。
（柔） 2017/09/10(日) 18:53

 ＞別の列になります。

 ・・で、それはどこの列なんですか？

（半平太） 2017/09/10(日) 18:58

肝心なことをお伝えしてませんでした。すいません。
GB、GL、GV、HF、HP、HZ、IJ、IT、JD、JN
の10列です。
（柔） 2017/09/10(日) 19:03

 ならば、こうです

  =SUM(COUNTIFS(OFFSET(DB!$FZ:$FZ,0,{0,1,2,3,4,5,6,7,8,9}*10),T12,OFFSET(DB!$GB:$GB,0,{0,1,2,3,4,5,6,7,8,9}*10),G4,DB!$A:$A,">="&U$7,DB!$A:$A,"<"&EDATE(U$7,1)))

 ・・けど本当にいいのかなぁ。

 特大とかの「サイズ」 と その「追加条件」を加えると、
 組合せる数がすごく膨らんで来そうな恐れを感じます。

 ・・ここに至っては、私のあずかり知らないことですけども・・

（半平太） 2017/09/10(日) 19:22

半平太さん、有難うございます。
希望どおりの結果をだすことができました。
しかしながら、半平太さんがおっしゃるとおり、エクセルの処理速度が非常に遅くなってしまいました。
今回ご教授いただいたものを活かしたうえで、この状態を改善することは不可能でしょうか？
さらなるご質問で申し訳ありませんが、ご教授の程よろしくお願いします。
（柔） 2017/09/11(月) 09:03

 今日は、これからちょっと忙しくなるので、他の回答者のレスをお待ちください。

 私としては、初めから恐れていたことが現実化したものです。
 それを避ける別案があったら、初めからそれを提示しておりました。

 現時点で妙案は浮かびません。(多分、今後も）

 前提を変えて、作業列を工夫するとか、マクロを使うとか、Powerピボットが使えないかとかが
 浮かびますが、そちらの状況(データ量)も条件の組合せも本当のところが分かっていないので何とも言えません。

 私は取りあえず、降ります。

（半平太） 2017/09/11(月) 10:27

データ量による遅さならば、$A:$A のように、全行を対象にしている箇所全てを、$A$1:$A$9999 のように、確実にデータが存在するであろう範囲指定に変える事で、かなり改善すると思われますが、いかがでしょうか。
（???） 2017/09/11(月) 10:33

半平太さん、???さん、ご指導有難うございます。全行指定を約4000行程度に縮小してみましたが、速度が格段に遅くなる状況は変わりませんでした。これは諦めたいと思います。
ご丁寧に教えていただき、感謝いたします。ありがとうこざいました。またの機会には、よろしくお願いいたします。

（柔） 2017/09/12(火) 21:46

コメント返信：

[ 一覧(最新更新順) ]

[[20170909231246]] 『SUMIFS関数で複数の合計対象範囲、条件範囲の合計』（柔） ページの最後に飛ぶ

[[20170909231246]]　『SUMIFS関数で複数の合計対象範囲、条件範囲の合計』（柔）　ページの最後に飛ぶ