みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

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

解決済みの質問

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関数のような記述ができませんでしょうか?

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

投稿日時 - 2015-10-29 17:00:32

QNo.9071816

困ってます

質問者が選んだベストアンサー

なぜDSUM関数なのか?
余談だが、VBAでやるなら(泥臭いが)繰り返し加算方法が標準だろう.
参考
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
’---
本題に入って、
関数でやるならSUMIFSではないか?
むつかしい関数に注目して分からないからといって大勢の
読者を煩わさなくてもい良いだろう。
例データ
処理日科目計数1計数2計数3
2015/9/1a1
2015/9/2b2
2015/9/3b3
2015/9/4c4
2015/9/5a1
2015/9/6a2
2015/9/7b3
以下略
標準モジュールに
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
VBAのWorksheetFunction.では、「セル範囲を Range("B3:B25"),式 で書く」など大まかなルールはあるが、引数の特に日付や比較演算子(文字列で書かせる場合が多い)は、VBAの達人でないと、やってみないとわからないのであないか。小生もいろいろやって時間を相当使った。
d1とd2を滅行にしているのは質問者に取って許されるのか。式の中に組み込むのは質問者はできるだろう。
質問者もいろいろやってみたのか?。
ーー
エクセルでSQLを扱えば、Betweenなんてのも使えるが、それは誰もやらない。
(VBAにはBetween演算子のようなものはないということ。)
>ACCESSのDSUM関数のような記述ができませんでしょうか?
エクセルの方からの発想の方が普通だろう。(エクセルのように、Accessでも
○○はできませんか?)
ーー
(1)関数式をセルに埋め込む方法
(2)Evaluateを使う方法(興味があればWEB照会せよ)
その他もあるかもしれない。

投稿日時 - 2015-10-29 19:54:45

お礼

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

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

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

>関数でやるならSUMIFSではないか?
>むつかしい関数に注目して分からないからといって大勢の
>読者を煩わさなくてもい良いだろう。
これは、私自身が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")

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

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

投稿日時 - 2015-10-29 23:19:17

ANo.1

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

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

回答(2)

ANo.2

#1です。
すみません。
今までの知識で、VBAで日付のリテラルは、日付を#で囲むなんて、経験知識があって、それが邪魔をして、むつかしく(複雑に)してしまいましたが
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" のように素直に書けばよいようです。
そして、日付の始期と終期は1つにまとめるのはできないとは思います。
データ例1例でてすが、3方法とも、テスト済みです。

投稿日時 - 2015-10-29 20:39:01

あなたにオススメの質問