[[20150720115634]]　『SUMIFS関数で　複数シートから　合計する方法』（Y さん）　ページの最後に飛ぶ

『SUMIFS関数で　複数シートから　合計する方法』（Y さん）

こんにちは

初めて質問させていただきます

売上データが2012年度から2015年度まで4枚のシートに分かれている場合で
集計シートに開始月と検索月数と品コードを入力することで固定の集計表に
対象期間の売上実績が表示されるように作ろうと思いますが
たとえば2013年12月から6ヶ月分のデータを表示させようとすると検索データ
が入っているシートが2枚にまたがるのでSUMIFS関数の合計範囲と条件範囲を可変
させないといけない場合どのようにすればよいのでしょうか。

データを一枚にすれば簡単なのですが年間（一シート）100万行近くデータが
入っていて数年分を一枚に結合できないので関数で計算するしかなく困っています

SUMIFS関数とｲﾝﾀﾞｲﾚｸﾄ関数の組み合わせだろうと考えていろいろ試していますが
うまくいきません。どなたか教えてください

SUMIFS関数は条件は４項目
年ごとのデータのシート名はシンプルに 2012.2013.2014.2015 の数字だけです

ちなみに年度をまたがないケースでは普通のSUMIFS関数で計算できています

よろしくお願いいたします

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

ちなみにいまの計算式は

｛=SUM(SUMIFS('2014'!$L$3:$L$1000000,'2014'!$B$3:$B$1000000,R$8,'2014'!$C$3:$C$1000000,R$9,'2014'!$J$3:$J$1000000,$I14,'2014'!$F$3:$F$1000000,($N$3:$N$6)))｝

こんな感じですが
配列式にしないでもできますが＋でつなぐとながーい式になるのでこうやってます
「2014」にあたる部分を「2012」～「2015」まで検索したいのですが・・・

 関数は素人なので具体的なお手伝いはできませんが、感想。

 １年分 100万行、現在のところ 4年分ですが、さらに増えるんですよね。
 これって、エクセルでいいのでしょうか？ブックを開くのも大変そう。

 あぁ、それと、具体的な各シートのレイアウトを提示いただかないと、どこで該当期間を絞るのか、それがわからないので
 回答者さんたちも、お困りなのでは？

（β） 2015/07/20(月) 18:09

アドバイスありがとうございます
当方も質問なれしてなくてどのように書けば伝わるか
よくわかっていませんがとりあえず書き加えてみます

上の配列式は
2014年シートにあるL列から売上金額を合計しますが
　　〃シートのB列から年
　　〃シートのC列から月
　　〃シートのJ列から営業担当コード
　　〃シートのF列から商品（このケースでは３品）
縦軸に営業担当者
横軸に年月を入れて
関数を使って条件に合致した売上をクロス集計しようと考えています

問題は抽出開始月が年度初めと限らない表なので
１年分を横列並びさせると別シートの翌年部分の集計も必要になります
このケースでは2015年データシートを見にいく必要がありますが
集計表中で連続データなので関数を変更せずシートを自動で取得したいんです

対象品１
対象品２
対象品３
対象品４　→　＄N＄3～＄N＄4行に品コードが４つあります

　　　　2014年　　　　　　　2015年
　　　　12月　1月　2月　3月　4月　5月　6月　～
担当A　　〇　〇　〇　〇　〇〇　　〇
担当B 〇　〇　〇　〇　〇〇　　〇
担当C 〇　〇　〇　〇　〇〇　　〇

この「〇」の部分を計算したいための数式なんです

本来ﾋﾟｯﾎﾟｯﾄﾃｰﾌﾞﾙなどでするべき分析は分かっているのですが
この表の後の作業などの事情があってこのようなクロス集計シートを使ってます
↓
たぶん想像では

各　'2014'!$L$3:$L$1000000　などの部分に手を加えればよいかと思うのですが
いろいろ試して全部うまくいきません

どなたかよいアドバイスがあれば　助けてください　
分かりにくくてすみません　　　
（Y さん） 2015/07/20(月) 19:05

 すみません。あいかわらず交通整理だけです。

 N3:N6 の４つの指定商品は、式を拝見する限り、集計シートにあるのですよね？
 さらにいえば、開始月と検索月数を指定するわけですから、これらも、集計シートのどこかにいれるんですよね。
 （それがどこなのかも説明必要じゃないでしょうか。それと、開始月だけでいいのでしょうか？ 9月だとして何年の9月なんでしょうね？）

 で、仮に、2014年9月から40か月分と指定されたら、N列の商品って表の中にはいっちゃいません？
 あと、集計シートの年や月のタイトルって、指定条件によって自動生成なんですかね？
 (あらかじめ記載してあるなら、何も開始月や月数を指定することもないですからね）

（β） 2015/07/20(月) 19:47

 もしかして集計表の担当者はＩ列１０行目から下に、年は８行目のＪ列から右に、月は９行目のＪ列から右にセットされているのでしょうか？
 で、開始月や分析月数を入力するのではなく、あくまで、このタイトルを参照して、Ｊ１０を左上隅にした表に展開すると、そういうことですか？
 それなら　Ｎ列の商品欄は表のレイアウトの外ですから問題はないわけですが。
 もし、そうだったとして、８行目の年は、アップされたサンプルでは、年の最初にしかないのですが、式では、すべての列に年があるような前提じゃないですか？
 （関数素人ですから、間違っているかもしれませんが）

（β） 2015/07/20(月) 20:13

 >>｛=SUM(SUMIFS('2014'!$L$3:$L$1000000,'2014'!$B$3:$B$1000000,R$8,'2014'!$C$3:$C$1000000,R$9,'2014'!$J$3:$J$1000000,$I14,'2014'!$F$3:$F$1000000,($N$3:$N$6)))｝

 コメントしましたように、このなかの R$8 が気になっています。年ですよね、ここは（きっと）。
 で、この式は各列に年（2014　等)があるということを前提にしていません？
 もし、その前提でいいなら　たとえば、'2014'!$L$3:$L$1000000　のところを　INDIRECT(R$8&"!$L$3:$L$1000000") でいいかも・・・・？？？

（β） 2015/07/20(月) 20:42

当方の説明足らずを
補っていただきすみません

　　N3:N6 の４つの指定商品は、式を拝見する限り、集計シートにあるのですよね？

そのとうりでなんです　レイアウトの関係で４品限定で集計シートに表示してます
またお察しのとうり

選択月も15か月以内＝1年と3カ月との制約を設けています
要は一年（12か月を跨いで数字の変遷を見たい）との要望で最長15ヶ月までの制約を設けてます

R8は年です
そう、まだ試していませんが
　INDIRECT(R$8&"!$L$3:$L$1000000")が浮かんでこずに．．．．
試してみます
（Y さん） 2015/07/20(月) 21:11

この式は各列に年（2014　等)があるということを前提にしていません？

　そうですね
　指摘ありがとうございます
　ここ見直してみます
（Y さん） 2015/07/20(月) 21:29

この式は各列に年（2014　等)があるということを前提にしていません？

ここはINDIRECTの式がうまくいかず　2014年分だけの式で仮置きしてました
説明不足重ねてお詫びします

　　INDIRECT(R$8&"!$L$3:$L$1000000")　試してみましたが

一部分のみうまくいきませんでした
もうちょっと頑張ってよく理解してみますね
（Y さん） 2015/07/20(月) 22:19

 （β） 2015/07/20(月) 20:13で推測したレイアウトが間違っておらず、８行目の年がすべての列にあるとして、J10 に以下をShift/Ctrl/Enterで入力し、表全体に下、右にフィルコピーするとなんとなく
 よさげな（？）数字がでませんか？

 =SUM(SUMIFS(INDIRECT(J$8&"!$L$3:$L$1000000"),INDIRECT(J$8&"!$B$3:$B$1000000"),J$8,INDIRECT(J$8&"!$C$3:$C$1000000"),J$9,INDIRECT(J$8&"!$J$3:$J$1000000"),$I10,INDIRECT(J$8&"!$F$3:$F$1000000"),($N$3:$N$6)))

 ところで、たとえば 2012 シートの B列って　すべて 2012 なんですよね。
 であれば、シートをINDIRECTで指定しているわけですから B列のチェックは不要だと思うのですが？

（β） 2015/07/21(火) 08:32

（β）さんありがとうございます

午前中ちょっとタイトなので　時間を見つけて
やってみて　ご返事させていただきます

コメントに感謝します
（Y さん） 2015/07/21(火) 08:52

 コメントしたB列のチェックを省き、かつ、各シートの１行目、２行目は、おそらく「偶然にも」条件の値と同じものということは
 ないでしょうし、加えて、100万行近く　ということですけど、100万1行になったら具合悪いでしょうから
 領域は列指定でもいいかもしれませんね。

 =SUM(SUMIFS(INDIRECT(J$8&"!$L:$L"),INDIRECT(J$8&"!$C:$C"),J$9,INDIRECT(J$8&"!$J:$J"),$I10,INDIRECT(J$8&"!$F:$F"),($N$3:$N$6)))

（β） 2015/07/21(火) 09:14

(β）さん

ご指摘してもらった

 =SUM(SUMIFS(INDIRECT(J$8&"!$L$3:$L$1000000"),INDIRECT(J$8&"!$B$3:$B$1000000"),J$8,INDIRECT(J$8&"!$C$3:$C$1000000"),J$9,INDIRECT(J$8&"!$J$3:$J$1000000"),$I10,INDIRECT(J$8&"!$F$3:$F$1000000"),($N$3:$N$6)))

の式でうまくいきそう
後程合計数字の検証をしますが　ＯＫです

確かに2014にあたる部分をすべて　INDIRECT(J$8&"!$〇列$3:$〇列$1000000")とすれば
合計データが取れました　当然ながら「INDIRECT(J$8&"!」をすべてに入れないとダメですよね

ありがとうございます
ただ、かなりのデータ行数の複数シートから配列式でデータ抽出するので重い重いファイルですが
エクセル関数を使うボリュームを超えている点を痛感しています。

何はともあれ落着です。　ありがとうございました。
今後も応用でいろいろ検討できます

（Y さん） 2015/07/21(火) 20:16

コメント返信：

[ 一覧(最新更新順) ]

[[20150720115634]] 『SUMIFS関数で 複数シートから 合計する方法』（Y さん） ページの最後に飛ぶ

[[20150720115634]]　『SUMIFS関数で　複数シートから　合計する方法』（Y さん）　ページの最後に飛ぶ