エクセル期間の金額の合計を出したい

質問

御世話になります。
条件としてＡ列に日付がランダムで並んでいます。
Ｂ列にその日付の売上があります。
行いたいことは月毎の売上グラフを作成をしたいのです。

今までは月の末日にフィルターで期間内の行を抜き出し
合計をしてグラフを作成していました。しかし月が変わ
った後で売上が変更になることがあり、その度に同じ作業
して数値を変更していました。ですのでＢ列の値を変更
すると、グラフを自動的に修正が行われるようにしたい
のですが・・・
宜しくお願いします。

回答No.6

DoragonFang · ベストアンサー

#4のDoragonFangです。

年を考慮するのを忘れてましたね。
この場合は、年を判定する式を組み込めば、出来ると思います。
具体的には、

=SUMPRODUCT((YEAR(A2:A8)=2004)*(MONTH(A2:A8)=10)*B2:B8)

とすれば、２００４年の１０月のデータの合計が求まるはずです。

お礼

お答えありがとうございます。
関数が1番シンプルでしたので
使用させて頂きました。
これで効率アップです。
今回は非常ありがとうございました。

回答No.5

KenKen_SP

こんにちは。ユーザー定義関数による解です。

＜使い方＞
引数：日付範囲　日付データがあるセル範囲を指定
引数：始期　集計条件の開始日
引数：終期　集計条件の終了日
引数：集計地オフセット　集計条件の開始日

A1：A100に日付、B列に集計の対象となる金額があるとすれば、

=SUMDATEIF(A1:A100,"2004/11/1","2004/11/30",1)

で2004年11月の合計を求めることができます。また、始期や終期にはセルの値を使うこともできます。
引数：集計値オフセットには、引数：日付範囲で指定した列から何行目の値を集計するのかを指定します。上記の例の場合ですと、A列から1列目＝B列を集計しています。

注意点としては、日付範囲には複数列を指定することはできません。それと、処理スピードの問題でA:Aなどと指定せず、A2:A367など限定した範囲を指定とした方が良いでしょう。なぜなら、A:Aとするといちいち1～65536行を調べるようなつくりになっていますので、非常に重たいです。

========== 次行からコード ==================================

Function SUMDATEIF(日付範囲 As Range, 始期 As Date, 終期 As Date, 集計値オフセット As Byte)

　　Dim rngCell As Range, Result As Double
　　Application.Volatile
　　'エラートラップ
　　If 日付範囲.Columns.Count > 1 Then
　　　　SUMDATEIF = "ERR：日付範囲に複数行が指定されています"
　　　　Exit Function
　　End If
　　'集計
　　For Each rngCell In 日付範囲
　　　　With rngCell
　　　　　　If IsDate(.Value) Then
　　　　　　　　Select Case .Value
　　　　　　　　　　Case 始期 To 終期
　　　　　　　　　　　　Result = Result + .Offset(0, 集計値オフセット)
　　　　　　　　　　Case Else
　　　　　　　　End Select
　　　　　　End If
　　　　End With
　　Next rngCell
　　SUMDATEIF = Result

End Function

回答No.4

DoragonFang

まず表の形式ですが、下記のようになっていると思います。

　　Ａ列　　　　　Ｂ列
1　日付　　　　　売上
2　2004/11/4 　　1234
3　2004/10/29　　5678
4　2004/11/15　　 484
5　2004/10/18　　2334
6　2004/11/6 　　1450
7　2004/12/1 　　 34
8　2004/11/23　　3333

これで、１１月の売上の合計を出すには、
その結果が欲しいセルに、次の式を入れてみてください。

=SUMPRODUCT((MONTH(A2:A8)=10)*B2:B8)

式を見てもらえば分かると思いますが、日付がA2:A8で、
売上がB2:B8にあるとしています。ここは実際に入力されているセルに合わせて、替えてください。
=10の部分が１０月を表しているので、１１月なら、=11、１２月なら=12とすればOKです。もちろん、
＝$C$1などと月の数字が入っているセルを指定しても構いません（ただし、式をコピーする際は絶対番地にしてください）。

この式であれば、A列、B列どちらのデータを書き換えても、合計が変更されます。

補足

お答えありがとうございます。貴殿のお答えだと
確かに合計はされるのですが、年数が区別されずに
合計されてしまいます。2004年1月と2003年1月の
区別がされません。何か良い方法はございませんで
しょうか。何度もお手数をお掛けしてすいません。

回答No.3

s_yoshi_6

＃２です。
書式が「日付」であれば、「2004/12/21」となっていても「平成16年12月21日」となっていても計算はできます。もし「文字列」となっていたら、計算できませんが、フィルタを使われているということですので、それはなさそうですね。

確認ですが、Sheet2のB2に数式を入力した後、「Shift＋Ctrl＋Enterキー」で確定されましたでしょうか。通常のEnterキーのみでは正しい結果にはなりません。配列数式の場合、数式の両端に{ }が付きますので確認してみて下さい。もし付いていなければ、セルをダブルクリック、もしくは数式バーにカーソルを置いて入力状態にした後、再度「Shift＋Ctrl＋Enterキー」で確定してみて下さい。

もし、既に配列数式になっていたとしたら、どのような結果となるのか
・○○○となるはずが別の計算結果（△△△）となる
または
・＃○○○のエラーが表示される
などを補足していただけますでしょうか。

回答No.2

s_yoshi_6

そのグラフの元になっている月ごとの売上表は、毎日のデータがあるシート、もしくは別のシートに作成されていて、これまでは、そこにコピー貼り付けか何かで値を貼り付けられていた、ということでしょうか。

以下、その前提で、

毎日のデータはSheet1のB2:B366、月ごとのデータはSheet2のB2:B13に記入するものとして、Sheet2のB2に
=SUM(IF(MONTH(Sheet1!$A$2:$A$366)=ROW()-1,Sheet1!$B$2:$B$366,0))
として「Shift＋Ctrl＋Enterキー」で確定すると、１月の合計が出ます。

以下コピーとすれば、１～１２月までの各月の合計が出ると思います。

上記の式には、そのセルの行番号を表示させる　ROW()　を使っていますので、セルがA2ではなくほかのセルだったら　ROW()-*　の　*　の部分で答えが１になるように調整して下さい。

なお上記の数式は配列数式になります。
配列数式については下記ページなどを参考にして下さい。
http://pc21.nikkeibp.co.jp/special/hr/hr1.shtml

参考URL：

http://pc21.nikkeibp.co.jp/special/hr/hr1.shtml

補足

御世話になります。早々のご回答
有難うございます。
貴殿のIF文章をそのまま使用しましたが
出来ませんでした。日付の形態が
2004/12/21となっていますがそれが
影響をしているのでしょうか？

回答No.1

melgirl

ツールバーのツール→オプションを開いて下さい。
ここに計算方法というタブがあるのでそこをクリックしてみると、一番上に"計算方法"があると思います。
グラフが自動で変更されないのであればここが手動になっていませんか？"自動"を選択しておくと表の値の変更に連動してグラフも変更されます。

補足

お答えありがとうございます。
私の言葉が足らずすいません。
まずＳｈｅｅｔ１にある
いま私の行っていることは
バラバラである日付(2004/1/1形式）を
フィルターによりある期間を抜き出します。
例えば11月分だけ抜き出せましたので
ＳＵＭを使い合計の値を出します。
その値をコピーしてＳｈｅｅｔ２に
ペーストします。それをグラフの
データーとして活用します。
人の手が仲介してますので自動計算では対応
できません。手動の部分は
(1)フィルターにて期間を抜き出す。
(2)期間内のＢ列にある売上の値の合計を算出。
(3)それを指定の場所にコピーペーストする。
その３点を自動で行いたいのです。