[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『同じ月の金額を集計したい』(ふみ)
A7セルに開始日を入力すると、M12セルからM46セルまで
日付が記入されています。
またF12セルからF46セルまで金額が記入されています。
A7セルと同じ月の日付の金額の集計をU6セルに、
A7セルの翌月の日付の金額の集計をAZ6セルに記入したいのですが、
どの様なコードを書けば良いでしょうか?
下記の様に書きかけたのですが、行き詰っています。
よろしくお願いします。
Sub 集計()
Dim s As Long
Dim t As Long
t = Month(Range("A7"))
s = WorksheetFunction.SumIf(Month(Range("M12:M46")), t, Range("F12:F46"))
End Sub
< 使用 Excel:Excel2007、使用 OS:Windows7 >
数式ではダメですか?
2ヶ所だけなら、
=SUMPRODUCT(($M$12:$M$46>=EOMONTH($A$7,-1)+1)*($M$12:$M$46<=EOMONTH($A$7,0)),$F$12:$F$46)
と
=SUMPRODUCT(($M$12:$M$46>=EOMONTH($A$7,0)+1)*($M$12:$M$46<=EOMONTH($A$7,1)),$F$12:$F$46)
位で出来ますけど。
(ウッシ) 2016/02/05(金) 14:59
私も関数で十分だと思いますが、一応…。
Sub 集計()
Dim t As Long
t = Month(Range("A7") * 1)
Range("U6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=" & t & ")*F12:F46)") Range("AZ6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=" & t + 1 & ")*F12:F46)")
End Sub (se_9) 2016/02/05(金) 15:03
se_9さん。
月数+1だと元の月が12月の場合に問題にならないか? (ねむねむ) 2016/02/05(金) 15:11
あ、またやってしまいました・・・。ねむねむさん指摘ありがとうございます。 というわけで関数案です。
U6セル =SUMIFS(F12:F46,M12:M46,">="&EOMONTH(A7,-1)+1,M12:M46,"<="&EOMONTH(A7,0)) AZ6セル =SUMIFS(F12:F46,M12:M46,">="&EOMONTH(A7,0)+1,M12:M46,"<="&EOMONTH(A7,1))
手元にエクセル2007以上がないため、SUMIFSは当てずっぽうです。 (se_9) 2016/02/05(金) 15:15
マクロの方も修正してみました。 Range("AZ6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MOD(" & t & ",12)+1)*F12:F46)") (se_9) 2016/02/05(金) 15:49
Sub 集計()
Dim s As Double
Dim t As Long
t = Month(Range("A7"))
s = WorksheetFunction.SumIf(Range("BY12:BY46"), t, Range("E12:E46"))
Range("U5") = s
s = WorksheetFunction.SumIf(Range("BY12:BY46"), t, Range("F12:F46"))
Range("U6") = s
s = WorksheetFunction.SumIf(Range("BY12:BY46"), t + 1, Range("E12:E46"))
Range("AZ5") = s
s = WorksheetFunction.SumIf(Range("BY12:BY46"), t + 1, Range("F12:F46"))
Range("AZ6") = s
End Sub
というコードで一応目的は達しています。
本当はBYセルに月を関数で書かなくてやりたいのですが。
良い方法はないでしょうか。
よろしくお願いします。
ここまで書いて更新しようとしたら
衝突が起きてse_9さんのコードを見つけたので、今からやってみます。
(ふみ) 2016/02/05(金) 16:01
皆さん、本当にありがとう御座いました。
(ふみ) 2016/02/05(金) 16:47
Sub 集計()
Dim t As Long
t = Month(Range("A7") * 1)
Range("U6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=" & t & ")*F12:F46)")
Range("AL6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MOD(" & t & ",12)+1)*F12:F46)")
Range("BC6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MOD(" & t & ",12)+2)*F12:F46)")
End Sub
としましたが、
A7セルが12月ならAL6は1月、BC6は2月、で問題ありませんが、
A7セルを11月にすると、AL6は12月になりますが、BC6の金額は0になります。
このコード自体を理解出来ていないのでなにを間違えているのかわかりません。
よろしくお願いします。
(ふみ) 2016/02/08(月) 15:25
よく考えてみたらMODじゃなくてEDATEでもよかった。というわけで
Sub 集計()
Dim t As Long
t = Range("A7") * 1
Range("U6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(" & t & "))*F12:F46)") Range("AL6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",1)))*F12:F46)") Range("BC6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",2)))*F12:F46)")
End Sub (se_9) 2016/02/08(月) 16:24
勉強の為に、コードを調べてみたのですが、Evaluateは便利なのに、
あまり知られていないそうですね。
またSUMPRODUCTが調べた例文と違う使い方になっていて、ここから先の意味がわかりませんでした。
とにかく、書きかえた後、もう一度勉強してみます。
ありがとう御座いました。
(ふみ) 2016/02/08(月) 16:36
(ふみ) 2016/02/08(月) 16:47
Range("AL6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",2)))*F12:F46)")
Range("BC6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",3)))*F12:F46)")
に書き換えるとうまくいきました。
でもやっぱり11月、12月にすると0になってしまいます。
(ふみ) 2016/02/08(月) 16:56
若干違うところもあるかもしれませんが、レイアウトが
A F M 7 2015/11/29
12 410 2015/11/30 13 147 2015/12/1 14 218 2015/12/2 15 481 2015/12/3 16 406 2015/12/4 17 260 2015/12/5 18 134 2015/12/6 19 319 2015/12/7 20 584 2015/12/8 21 384 2015/12/9 22 127 2015/12/10 23 340 2015/12/11 24 137 2015/12/12 25 405 2015/12/13 26 267 2015/12/14 27 302 2015/12/15 28 493 2015/12/16 29 178 2015/12/17 30 393 2015/12/18 31 417 2015/12/19 32 392 2015/12/20 33 471 2015/12/21 34 471 2015/12/22 35 332 2015/12/23 36 115 2015/12/24 37 384 2015/12/25 38 328 2015/12/26 39 264 2015/12/27 40 426 2015/12/28 41 184 2015/12/29 42 308 2015/12/30 43 512 2015/12/31 44 358 2016/1/1 45 411 2016/1/2 46 213 2016/1/3
となっていたとします。先ほどのマクロ(16:24のやつです)を実行するとこちらでは U6セル 410 AL6セル 10179 BC6セル 982
になりますが、ふみさんの方ではAL6セル、BC6セルが0になってしまうのですか? (se_9) 2016/02/08(月) 17:00
>A7セルを2月にしても、AL6が2月、BC6が3月になってしまうので
空いているセル(たとえばA1、B1、C1)に =MONTH(A7) =MONTH(EDATE(A7,1)) =MONTH(EDATE(A7,2))
と入れてみてください。A7セルが2月になっていたら2、3、4となるはずです。 (se_9) 2016/02/08(月) 17:03
違いとしては、シート保護をしていて、コードを走らせる時だけ
Unprotectしています。
また表示形式を#,##0.00"H"のユーザー定義にしています。
これらが原因でしょうか?
ここまで書いて更新したら、また衝突してしまいました。
(ふみ) 2016/02/08(月) 17:07
空いているセル(たとえばA1、B1、C1)に =MONTH(A7) =MONTH(EDATE(A7,1)) =MONTH(EDATE(A7,2)) これは大丈夫でした。 A7セルが12月で12、1、2になります。 (ふみ) 2016/02/08(月) 17:13
表示形式#,##0.00"H"はF列ですよね?でしたら問題はありません。
ちなみにA7セルはアクセスのカレンダーコントロールを使っているとのことですが、 私はカレンダーコントロールを使ったことがないのでよくわからないのですが A7セルの表示形式は=MONTH(A7)などの式でちゃんとした答えが返ってきているので 問題なさそうなのです。が、一応お聞きします。表示形式は日付形式になっているか 明日の朝確認してみてください。 (se_9) 2016/02/08(月) 17:26
表示形式#,##0.00"H"はAL5とBC5に使っています。
(実際はAL5とBC5に工数合計、AL6とBC6に金額合計を出していますが、両方0になっています)
A7セルの表示形式は日付形式になっています。
(ふみ) 2016/02/09(火) 09:17
表示形式に関しては問題ありませんでした。 マクロではなく関数だとどうなりますか?
U6セル =SUMIFS(F12:F46,M12:M46,">="&EOMONTH(A7,-1)+1,M12:M46,"<="&EOMONTH(A7,0)) AZ6セル =SUMIFS(F12:F46,M12:M46,">="&EOMONTH(A7,0)+1,M12:M46,"<="&EOMONTH(A7,1)) BC6セル =SUMIFS(F12:F46,M12:M46,">="&EOMONTH(A7,1)+1,M12:M46,"<="&EOMONTH(A7,2)) (se_9) 2016/02/09(火) 09:35
今から関数の方をやってみます。
(ふみ) 2016/02/09(火) 09:41
Sheets("Sheet1").Unprotect Password:="********" で解除してから Range("AL6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",1)))*F12:F46)") となって、また Private Sub Worksheet_Change(ByVal Target As Range) へ飛んでいく、を繰り返しています。 Sheets("Sheet1").Unprotect Password:="********" Range("BC6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MONTH(EDATE(" & t & ",2)))*F12:F46)")
Private Sub Worksheet_Change(ByVal Target As Range)からスタートして
デバッグをする方法はありますか?
(ふみ) 2016/02/09(火) 11:25
Private Sub Worksheet_Change(ByVal Target As Range) にはどのようなマクロが組まれているのですか? (se_9) 2016/02/09(火) 13:02
'//カレンダーの基準が変更された場合 Case Not Intersect(Target, Range("A7")) Is Nothing
'//図面展開の処理 Case Not Intersect(Target, Range("G12:H46")) Is Nothing
'//調達・加工の処理 Case Not Intersect(Target, Range("I12:J46")) Is Nothing
'//組立の処理 Case Not Intersect(Target, Range("K12:L46")) Is Nothing
'//納期の処理 Case Not Intersect(Target, Range("M12:M46")) Is Nothing
End Select
となっていて、A7が変更された場合、納期が変更された場合などに
Call 集計
を入れていました。
また、昨日の確認と結果が違うのですが、
Range("U6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=" & t & ")*F12:F46)")
Range("AL6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MOD(" & t & ",12)+1)*F12:F46)")
Range("BC6").Value = Evaluate("SUMPRODUCT((MONTH(M12:M46)=MOD(" & t & ",12)+2)*F12:F46)")
としても、A7を11月にした時だけBC6は0になってしまいますが、その他はうまくいきます。
ただし、2016年、2017年を認識せず、同じ月を合計してしまいます。
書いて頂いた関数だと年も認識しますので、今回はコードをあきらめて
関数を挿入しました。
お手数をおかけし、もうしわけありませんでした。
(ふみ) 2016/02/09(火) 13:22
>今回はコードをあきらめて >関数を挿入しました。
コード提示したくせに無責任な、と思われるかもしれませんが、それでいいと思います。 マクロはとても便利だけど、関数でできるところは関数で処理した方が楽ですし。 (se_9) 2016/02/09(火) 13:46
ありがとう御座いました。
(ふみ) 2016/02/09(火) 14:52
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.