こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

SUMIF関数【複数のシートの検索範囲にするには】

初めて質問します。
エクセルのSUMIF関数で、範囲と合計範囲を、複数のシートにまたがってを選択したいのですが、上手くいきません。
それぞれのシートの作り(行・列の数、表示形式など)は同じです。また選択する範囲も同じです。
目的は特定のクライアントの期間別の合計売上を算出することです。
シートごとに月単位の売上が記録してあり、検索条件にクライアント名を入力すれば、さかのぼった一定期間の売上が算出できるようにしたいのです。
SUMIF関数入力に従っていくと、【範囲】shiftを押しながらシートを選び、シート状の範囲を選択、【検索条件】、【合計範囲】shiftを押しながらシートを選び、シート状の範囲を選択、となります。
具体的な数式としては、
=SUMIF('3月売上:1月売上'!B2:B100,'0805'!$H$20,'3月売上:1月売上'!E2:E100)
となりますが、エラーになってしまいます。いろいろ試したのですが、上手くいきません。どなたかお力添えをお願いしまう。

※今回は連続する数ヶ月の範囲でしたので、シートを:でつないだもので問題ないのですが、もし任意の複数シートを選ぶ場合についても、教えていただければ幸いです。ctrlではダメでした。

投稿日時 - 2008-05-26 15:03:21

QNo.4051869

困ってます

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

串刺しのちょっとしたテクニック紹介しておきます。
一枚、空白のシートを追加します。
仮に、このシート名を開始のシートとします。
クライアントの名前のあるシートに合計を出すとして
このシートを集計のシート と名前にしたとします。
合計のSUM関数には =SUM(集計のシート:開始のシート!A1)
といておいて、
シートが 
集計のシート
1月売上
2月売上
3月売上
4月売上
・・・
開始のシート とある場合で
1~3月の合計を計算したい場合は
集計のシートを
集計のシート
1月売上
2月売上
3月売上
開始のシート
4月売上
・・・・
と開始のシートの位置を変更して
串刺しの合計したいシートを集計のシートと開始のシートの間になるように、
開始のシートを移動させます。
関数を変更することなく、合計したいシートを選ぶことが出来ます。

投稿日時 - 2008-05-27 23:02:10

ANo.3

エクセルの関数では「範囲」という引数が、色んな関数で出てくる(ある、指定する)が、SUM以外は、関数式を入れるシートの範囲に限られ、他シートの範囲まで同時に処理対象に出来無いと思ったほうが良いと思う。(そういう式を入れようとするとエラー)
WEBでも調べたが、当然視したか、ズバリの記述が、短時間では見つからなかった。でも事実間違いない。
シートに関してシームレスではないわけだ。他の機能でもそういうのが多い。2007などで改良してくれれば良かった点だが、技術的に大変更になって難しいのかも知れない。
ーーー
(1)だからシートごとに同じ位置に結果を出して、多数シート分を集計
(2)VBAで(1)を自動的にやる
しかないでしょう。
ーーー
VBAなら
Sub test01()
Dim sh As Worksheet
t = 0
For Each sh In Worksheets
'MsgBox sh.Name
x = WorksheetFunction.SumIf(sh.Range("A2:A10"), "a", sh.Range("B2:B10"))
'MsgBox x
t = t + x
Next
MsgBox t
End Sub
短いけれど私に言わせれば、何項目も勉強が必要な事項が含まれて
いると思っている。
(各シートでのA列がaの合計の出し方には色々ある)
ーーー
例データ
Sheet1
A列  B列
a30
b3
c-5
d-8
ーー
Sheet2
A列  B列
a2
b3
c4
a12

Sheet3
A列  B列
a10
b12
c31
a1
a1
ーー
Sheet4
未使用
ーー
実行結果
A列で、aの全シート合計 56

投稿日時 - 2008-05-26 18:10:25

お礼

返信が遅くなって申し訳ありません。
丁寧に教えていただきありがとうございました。
私の力不足で、VBAなどは扱えませんので、今後の課題にしたいと思います。もっと勉強します。

投稿日時 - 2008-05-27 08:03:20

ANo.2

例えば、集計のシートに
  A     B  
1   クライアント名
2 1月売上
3 2月売上
とA列にシート名入れて
B2セルに =SUMIF(INDIRECT(A2&"!B$2:B$100"),B$1,INDIRECT(A2&"!E$2:E$100"))
下フィルして、最後に合計する方法

或いは、シートの1行目にSUMIF関数を挿入して、串刺しで合計する方法
ではいかがでしょうか

投稿日時 - 2008-05-26 15:56:06

お礼

ご指導いただきありがとうございます。また返信が遅れまして、申し訳ございませんでした。
結局、以下の処置にて対応しました。
(1)別シートに広告主を入力する枠を作成
(2)各月のシートの広告主名枠がそれを参照し、それぞれの月のSUMIF集計
(3)その集計値を必要なスパンでSUM集計
それぞれの月ごとに、計算式の参照シートをちょこちょこいじっての対応となりました。美しくないはないですが、とりあえずの用は成しています。
もう少し上手く扱えるよう勉強します。

投稿日時 - 2008-05-27 11:53:28

ANo.1

串刺し集計は対応関数に制限があり、SUMIF関数は対象外です。
よって、実現するにはVBAでないと無理だと思います。
因みに年間のデータ量に左右されますが売上データを1シートに集約されいればワークシート関数などで利用し易くなりますが如何でしょうか。

投稿日時 - 2008-05-26 15:29:27

お礼

アドバイスありがとうございます。
また返信が遅れまして申し訳ございません。
SUMで串刺しができたため、SUMIFも可能だと思っていました。
SUMを使って、直近1ヶ月、3ヶ月、6ヶ月、12ヶ月のスパンで集計し、前年度同時期と比較していました。この場合、月別シートで扱っても問題なく、見やすく管理できていました。
たしかに年間をひとつのシートにまとめるのは、ひとつの手かと思いますが、やはり年度またぎをした場合にはシートの串刺し問題に行き着くので抜本的な解決にはならないです。
VBAしかりワークシート関数しかり、まだ私の力不足で扱えません。
勉強します。

投稿日時 - 2008-05-27 08:14:47

あなたにオススメの質問