【Excel2003】複数Sheetを対象にしたSUMIFの使い方につ

質問

【Excel2003】複数Sheetを対象にしたSUMIFの使い方について

実際は添付図と違い、表がSheet別になっています。
日付ごとに数量と件数（=金額の入ったシート数で計算）を出したいのです。
計算式は普通にSUMIFで括れば良いと思います。

しかし、実際は検索先となるSheetが30枚ほどあるのです。
=SUMIF(Sheet1!XXX,XXX,Sheet1!XXX)+SUMIF(Sheet2!XXX,XXX,Sheet2!XXX)+・・・・・
とかなり長い計算式となり「長すぎるよ！」みたいなエラーが出ます。

これを回避する方法はありませんか？

回答No.5

KURUMITO · ベストアンサー

回答Ｎｏ4です。
試験をしていただきありがとうございます。
同じ日付がシートでまたがることがあるということでその対応策ですが次のようにすればよいでしょう。
作業列としてＧ，Ｈ列は変わりませんが、Ｈ列のとなりにＩ1セルには日付の最高値とでも入力します。
Ｉ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($G2="","",MAX(INDIRECT("'"&$G2&"'!A:A")))

そこでＢ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR($A2="",$A2<$H$2),"",IF(AND(COUNTIF($H:$H,$A2)=1,COUNTIF($I:$I,$A2)=1), SUMIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,0))&"'!A:A"),$A2,INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,0))&"'!D:D"))+SUMIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$I:$I,0))&"'!A:A"),$A2,INDIRECT("'"&INDEX($G:$G,MATCH($A2,$I:$I,0))&"'!D:D")),SUMIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!A:A"),$A2,INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!D:D"))))

Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR($A2="",$A2<$H$2),"",IF(AND(COUNTIF($H:$H,$A2)=1,COUNTIF($I:$I,$A2)=1), COUNTIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,0))&"'!A:A"),$A2)+COUNTIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$I:$I,0))&"'!A:A"),$A2),COUNTIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!A:A"),$A2)))

お礼

あまりの多忙にお返事が遅れてしまいました。

試してみたところ無事目的を果たせました。
私のような者のために貴重な時間を割いていただき感謝いたします。
機会があればまたよろしくお願いいたします。

回答No.4

KURUMITO

次のようにしてはどうでしょう。
シートの数がたくさんに増しても容易に対応できる方法です。
各シートは1行目にお示しの項目名があり、2行目から下方にそれぞれのデータが入力されているとします。
そのけまとめのためのシートですがＡ1セルに日付、Ｂ1セルに個数、Ｃ1セルに件数と入力します。
これらのデータを求めるために作業列を作ります。
例えばＧ1セルはシート名と入力し下方にはSheet1 Sheet2 Sheet3などと実際の日付のデータが入ったシート名を入力します。この際必ず日付が最も古いデータのシートを2行目にいかだんだんと新しい日付を含むシート名を並べるようにします。シートとシートの間で同じ日付のデータが無いことが条件です。
Ｈ1セルには日付の最小値と入力ます。Ｈ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(G2="","",MIN(INDIRECT("'"&$G2&"'!A:A")))

セルの書式は日付にしてください。これで各シートに入力されている日付の最小値が表示されます。

そこでＡ2セルから下方には5月1日、5月2日、5月3日などの日付を入力します。
Ｂ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR($A2="",$A2<$H$2),"",SUMIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!A:A"),$A2,INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!D:D")))

Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR($A2="",$A2<$H$2),"",COUNTIF(INDIRECT("'"&INDEX($G:$G,MATCH($A2,$H:$H,1))&"'!A:A"),$A2))

これでどんなにシート数が増えても、また途中でデータが新たに入力された場合でも問題なく対応できるでしょう。一度是非お試しください。ご参考になりましたら幸いです。

補足

ご回答ありがとうございます。

＞シートとシートの間で同じ日付のデータが無いことが条件です。
とても良い方法で件数まで出せるのですが、同じ日付がシートにまたがることがありまして・・・

回答No.3

tom04

こんにちは！
参考になるかどうか判りませんが・・・
一つの案です。

各Sheetに同じセル配置で、SUMUIFで日付ごとの集計表を作れば、
串刺し計算の方法が使えると思います。

それぞれのSheetに数式を入力するのは大変ですので、
全てのSheetを作業グループ化します。

データが入力されている最初のSheetを開き、Shiftキーを押しながら集計したい最後のSheet見出しをクリックします。
これで作業グループ化されましたので
データとはまったく関係ない空いている場所にSUMUIF関数で日付ごとの集計を行います。
その後Sheet見出し上を右クリックし、「作業グループ解除」を選択すれば
各Sheetの同じセル番地にそれぞれのSheetの日付ごとの集計が出来ているはずです。

集計用Sheetの「5/1」のセルをアクティブにし
オートSUMのアイコンをクリックします。
そして先ほどの最初のSheetの「5/1」の集計セルを選択し、Shiftキーを押しながら
最後のSheetの「5/1」の集計セルを選択します。
そして、もう一度オートSUMのアイコンをクリック（又はCtrlキーを押しながら、Enterで確定）すれば
全てのSheetの「5/1」の合計が表示されますので
それをオートフィルでコピーしてみてはどうでしょうか？

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.2

hallo-2007

無理してひとつのセルで結果をだすのでなく、作業列を使ったほうがわかりやすいですし、今後シートが増えたり
名前の変更があった場合も対応がしやすいですよ。
A　　　B　　　C　　　　D・・・・
日付　合計　Sheet1　Sheet2・・・
5/1
・・・と、C1から右へ集計したいシート名を30個入れておきます。
C2セルに　=SUMIF(INDIRECT(C1 & "!A:A"),$A2,INDIRECT( C1 & "!B:B"))　右へ30コピィ
それおぞれのシートの集計がでますので、B列にSUMで合計します。

補足

件数の方はどう書けばいいのでしょう？

回答No.1

keithin

各シートのシート名が規則正しくSheet1,2,3…30になっているなら，
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(A1:A30)&"!A:A"),A2,INDIRECT("Sheet"&ROW(A1:A30)&"!D:D")))
のように出来ます。(式中の「A2」にはSUMIFで検索したい日付が入っている。「ROW(A1:A3)」はシート名を作るのに使っているので，変えてはいけない。）

またデータメニューの「統合」を使い，シート１～３０の表範囲を追加，左端列と上端行にチェックを入れて，A列のリストアップから一気に集計まで出させる事も出来ます。