EXCEL VBA で データを集計したい。

質問

よろしくお願いします。

添付の資料の通り、データを格納しているシートがあります。
項目は、
　・処理日
　・科目
　・D列
　・E列
　・F列
とあったとし、抽出条件は、
　・処理日の範囲指定
　・科目の指定
としたいと思っています。

VBAで処理したいのですが、まずはEXCEL関数のDSUMを使用し、添付の通り指定して、
抽出されることを確認しました。

これと同様の抽出をVBAで行いたく、VBAでワークシート関数を使用するために、
　Dim x As Variant
x = Application.WorksheetFunction.DSum(Range(Cells(3, "A"), Cells(15, "E")), "D列", Range(Cells(3, "O"), Cells(4, "Q")))
とし、正しく値を取得することができました。

ですが、条件を指定する部分を、セルで指定するのではなく、例えば
　"処理日" >= 20151001 and "処理日" <= 20151002 and "科目" = "01"
のように、式をVBAで生成して作成したいのですが、方法はありませんでしょうか？
ACCESSのDSUM関数のような記述ができませんでしょうか？

お手数をおかけします、よろしくお願いいたします。

回答No.1

imogasi · ベストアンサー

なぜDSUM関数なのか？
余談だが、ＶＢＡでやるなら（泥臭いが）繰り返し加算方法が標準だろう.
参考
Sub test01()
s = 0
dr = ActiveSheet.Range("A10000").End(xlUp).Row
For i = 1 To dr
If Range("A" & i) >= #9/4/2015# And Range("A" & i) <= #9/7/2015# And Range("B" & i) = "b" Then
s = s + Range("C" & i)
End If
Next i
ActiveSheet.Range("G" & dr) = s
End Sub
’－－－
本題に入って、
関数でやるならＳＵＭＩＦＳではないか？
むつかしい関数に注目して分からないからといって大勢の
読者を煩わさなくてもい良いだろう。
例データ
処理日 科目 計数１ 計数２ 計数３
2015/9/1 a 1
2015/9/2 b 2
2015/9/3 b 3
2015/9/4 c 4
2015/9/5 a 1
2015/9/6 a 2
2015/9/7 b 3
以下略
標準モジュールに
Sub test02()
d1 = Format(#9/4/2015#, "yyyy/mm/dd")
MsgBox d1
d2 = Format(#9/7/2015#, "yyyy/mm/dd")
MsgBox d2
Range("G25") = Application.WorksheetFunction.SumIfs(Range("C3:C25"), Range("A3:A25"), ">=" & d1, Range("A3:A25"), "<=" & d2, Range("B3:B25"), "=" & "b")
End Sub
ＶＢＡのWorksheetFunction.では、「セル範囲を Range("B3:B25"),式 で書く」など大まかなルールはあるが、引数の特に日付や比較演算子（文字列で書かせる場合が多い）は、ＶＢＡの達人でないと、やってみないとわからないのであないか。小生もいろいろやって時間を相当使った。
ｄ１とｄ２を滅行にしているのは質問者に取って許されるのか。式の中に組み込むのは質問者はできるだろう。
質問者もいろいろやってみたのか？。
ーー
エクセルでＳＱＬを扱えば、Ｂｅｔｗｅｅｎなんてのも使えるが、それは誰もやらない。
（ＶＢＡにはＢｅｔｗｅｅｎ演算子のようなものはないということ。）
＞ACCESSのDSUM関数のような記述ができませんでしょうか？
エクセルの方からの発想の方が普通だろう。（エクセルのように、Ａｃｃｅｓｓでも
○○はできませんか？）
ーー
（１）関数式をセルに埋め込む方法
（２）Ｅｖａｌｕａｔｅを使う方法（興味があればＷＥＢ照会せよ）
その他もあるかもしれない。

お礼

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

まずは私の質問の仕方が悪く、気分を害したようで申し訳ございません。

>なぜDSUM関数なのか？
>余談だが、ＶＢＡでやるなら（泥臭いが）繰り返し加算方法が標準だろう.
方法がなければ、for文で回すことを検討しましたが、
短い文で表記できるのならばその方が分かりやすいかと考えたことが今回の経緯です。
※EXCELですので、もし私以外の方(EXCELはよく扱っているがVBAはさほど理解していない場合も可能性として考える)が解析にあたった場合、シート関数のような記述であれば、for文で回す処理よりかは分かりやすいかとも考えた結果です。

>関数でやるならＳＵＭＩＦＳではないか？
>むつかしい関数に注目して分からないからといって大勢の
>読者を煩わさなくてもい良いだろう。
これは、私自身がEXCEL関数の知識が乏しく、そもそもSUMIFS関数すらも知らなく、もちろんDSum関数も知らなかったのですが、検索して最初に見つけたのがDSum関数でしたので、この関数について掘り下げて調査を進めていたものです。
「大勢の読者を煩わ」すつもりは全くなく、わざわざ難しい表現をしたいとも思っておりません。
今回SUMIFS関数というものがあるということ、そしてこちらの方がDSum関数よりも一般的に知られているということを教えていただきまして、ありがとうございました。

日付の処理については、おっしゃる通り日付型だと扱いにくいため、
数字8桁で持つようにしています。

>＞ACCESSのDSUM関数のような記述ができませんでしょうか？
>エクセルの方からの発想の方が普通だろう。
普通の発想ができず、申し訳ございませんでした。
ですが私のように、ACCESS(VBA)の方がEXCELよりも多く扱っている人間が、いろんな背景を考慮した上で今回のような質問をさせていただくこともあることをご理解いただきたいと思います。
このことを記載しておけばよかったと反省いたしました。

ご提示していただいた「EVALUATE」メソッドは、質問を出した後に見つけまして、こちらも試してみていたところでした。

ですがやはり、条件の指定の部分を、
「セル範囲を指定」(条件値はセルに入力する必要がある)から
「条件式に変える」(セルに条件値を入力せずに、VBAで生成する)
という方法に変換するのが全く思い当たらず、ずっとWeb検索しており、for文を覚悟したところでした。
教えていただいたSumIfs関数だと条件指定の方法が「セル範囲(条件値が入力されているセルが範囲に含まれるように範囲選択)」ではなく、条件値を指定してできるようでしたので、とりあえず固定値で、

x = Application.SumIfs(Range(Cells(4, "C"), Cells(15, "C")), _
Range(Cells(4, "A"), Cells(15, "A")), ">=20151001", _
Range(Cells(4, "A"), Cells(15, "A")), "<=20151002", _
Range(Cells(4, "B"), Cells(15, "B")), "01")

と指定することで正しく結果が得られましたので、
後は条件値を変数て指定するように手直しして対応したいと思います。

大変ありがとうございました。

回答No.2

imogasi

#1です。
すみません。
今までの知識で、ＶＢＡで日付のリテラルは、日付を＃で囲むなんて、経験知識があって、それが邪魔をして、むつかしく（複雑に）してしまいましたが
Sub test03()
Range("G25") = Application.WorksheetFunction.SumIfs(Range("C3:C25"), Range("A3:A25"), ">=2015/9/4", Range("A3:A25"), "<=2015/9/7", Range("B3:B25"), "=" & "b")
End Sub
の　 "<=2015/9/7"　のように素直に書けばよいようです。
そして、日付の始期と終期は１つにまとめるのはできないとは思います。
データ例１例でてすが、３方法とも、テスト済みです。