Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7439 / 13644 ツリー ←次へ | 前へ→

【39025】データ数のカウント aaa 06/6/16(金) 10:04 質問[未読]
【39032】Re:データ数のカウント Jaka 06/6/16(金) 11:25 回答[未読]
【39142】Re:データ数のカウント aaa 06/6/19(月) 10:02 質問[未読]
【39148】Re:データ数のカウント Jaka 06/6/19(月) 11:09 発言[未読]
【39155】Re:データ数のカウント aaa 06/6/19(月) 11:50 発言[未読]
【39157】Re:データ数のカウント Jaka 06/6/19(月) 12:14 発言[未読]
【39158】Re:データ数のカウント Jaka 06/6/19(月) 13:07 発言[未読]
【39160】Re:データ数のカウント aaa 06/6/19(月) 13:45 質問[未読]
【39161】Re:データ数のカウント Jaka 06/6/19(月) 14:09 発言[未読]
【39174】Re:データ数のカウント Kein 06/6/19(月) 15:50 回答[未読]
【39177】Re:データ数のカウント Kein 06/6/19(月) 15:52 発言[未読]

【39025】データ数のカウント
質問  aaa  - 06/6/16(金) 10:04 -

引用なし
パスワード
   条件を満たすデータのカウントをしたいのですが、途中まで考えたところでどうすればいいのかわからなくなってしまい投稿しました。

データには日付と分類番号があります。
他にも列がありますが、今回はあえて記述していません。

番号 分類番号 日付
1    1   2006/06/01
2    1   2006/06/01
3    2   2006/06/01
4    3   2006/06/01
5    5   2006/06/01
6    3   2006/06/02
7    4   2006/06/02
6    3   2006/06/03
7    4   2006/06/03
8    5   2006/06/03
9    1   2006/06/04
10    3   2006/06/05





上記のデータより例えば6/2から6/4までで、分類番号ごとのデータの数をカウントする場合「COUNTIF」関数を使用すればいいと思い、
(B1セルに6/2とD1セルに6/4というデータが入っています)

COUNTIF(C:C,">="&B1)-COUNTIF(C:C,">"&D1) 

とりあえず6/2から6/4までのデータ数をカウントする方法を考えましたが、それから分類番号ごとで絞り込むところがわかりません。。。
ワークシートのセルに数式を入れていくと処理が遅くなるようなのでVBAで以下のように書いてみましたが、これは初期の段階のもので分類番号のみのカウントをしています。

Dim hai() As Variant
Dim j As Integer
Dim Dai As Integer
Dim cnt As Integer
For j = 1 To 5
Dai = WorksheetFunction.CountIf(ws.Range("C2:C65535"), j)
hai(j) = Dai
Next
For cnt = 1 To j
Worksheets("Sheet1").Cells(cnt, 2).Value = hai(cnt)
Next cnt

最初に書いた日付指定の式を上記に組み込めればいいのですが、ちょっとわかりません。
C2より以降にデータが入っているのですが、何行あるかはわからないので最終行までカウントする方法をとっています。
いいアドバイスをお願いします。

【39032】Re:データ数のカウント
回答  Jaka  - 06/6/16(金) 11:25 -

引用なし
パスワード
   VBA用に直してないけど、こんな感じ。
=SUMPRODUCT((B2:B13=1)*(C2:C13=DATE(2006,6,1)))

【39142】Re:データ数のカウント
質問  aaa  - 06/6/19(月) 10:02 -

引用なし
パスワード
   ▼Jaka さん:

Jakaさんのおっしゃるとおり、SUMPRODUCTを使用してVBAを自分なりに書き換えてみましたがエラーが出ます。(「型が一致しません。」というエラーメッセージです。)
B1に集計開始日、D1に集計終了日が入っているとします。
以下は集計開始日から集計終了日の間で、分類番号に一致するものをカウントしたい場合のマクロです。

Dim hai() As Variant
Dim j As Integer
Dim Dai As Integer
Dim cnt As Integer
Dim ws As Worksheet
Dim onDate as Date
Dim ofDate as Date

onDate=Worksheets("Sheet2").Range("B1").Value
ofDate=Worksheets("Sheet2").Range("C1").Value
Set ws = Worksheets("Sheet1")

For j = 1 To 5
Dai = WorksheetFunction.SumProduct((ws.Range("B2:B65535") = j) * _
          (ws.Range("C2:C65535") <= onDate) * _
            (ws.Range("C2:C65535") <= ofDate))
hai(j) = Dai
Next
For cnt = 1 To j
Worksheets("Sheet2").Cells(cnt, 2).Value = hai(cnt)
Next cnt

SUMPRODUCT関数のところをどう書き換えればうまく動作するのでしょうか?
または他の方法があれば教えてください。

実は最初はワークシートにこの関数を貼り付けていたのですが、再計算すると処理が異常に遅くなりフリーズしてしまいます。
実際分類番号は50まであり、そのシートに他の手法でカウントしたりする関数もたくさん貼り付けています。
ですからVBAで対処するのが一番いいのかと思って、複雑になりそうですがこの方法を取りたいと思っています。

よろしくお願い致します。

【39148】Re:データ数のカウント
発言  Jaka  - 06/6/19(月) 11:09 -

引用なし
パスワード
   5年位前の回答を引っ張り出してみました。
これで解ります?

-----------
【439】Re:カウント関数について教えてください。

  Jaka - 02/9/12(木) 11:56 -

MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=""巡回監査"")*(B1:B10=""金太郎"")*(C1:C10=""◎""))")

【39155】Re:データ数のカウント
発言  aaa  - 06/6/19(月) 11:50 -

引用なし
パスワード
   ▼Jaka さん:

>MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=""巡回監査"")*(B1:B10=""金太郎"")*(C1:C10=""◎""))")

わかりません。
SUMPRODUCTについては使用したことがありますので、上記の様な使用方法(文字列に合うものをカウントする方法)はわかるのですが、日付を指定する場合に引数に普通に値を入れただけならば完全一致する日付は取れますが、(例えば文字列としてその指定日付を認識させ、一致するものを取るという形)以上未満などの複雑な条件を設定することができませんでした。
型が一致しないのは、引数の値の型が一致しないのだろうということはわかるのですがそれ以上のことがわかりません。
完全に勉強不足です。
やっぱり他の方法を考えた方が早いでしょうか?

例えばデータの一番上から、1行ずつ条件に合うかどうかを判定し、合えば足し、ループでまわしていく方法とか。
今ふとアイデアが浮かんだだけで、具体的にコードは作成できていませんが・・・
行数が1000行になったりする場合もあるので、やはりこうすると処理は遅くなる気はしますが、フリーズはしないかなと言う感じですかね。。。

【39157】Re:データ数のカウント
発言  Jaka  - 06/6/19(月) 12:14 -

引用なし
パスワード
   ▼aaa さん:
>▼Jaka さん:
>
>>MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=""巡回監査"")*(B1:B10=""金太郎"")*(C1:C10=""◎""))")
>
>わかりません。
>SUMPRODUCTについては使用したことがありますので、上記の様な使用方法(文字列に合うものをカウントする方法)はわかるのですが、日付を指定する場合に引数に普通に値を入れただけならば完全一致する日付は取れますが、(例えば文字列としてその指定日付を認識させ、一致するものを取るという形)以上未満などの複雑な条件を設定することができませんでした。
>型が一致しないのは、引数の値の型が一致しないのだろうということはわかるのですがそれ以上のことがわかりません。
>完全に勉強不足です。
>やっぱり他の方法を考えた方が早いでしょうか?
いや、書いたコードを解析するのでなく、SUMPRODUCTをVBA上で使う方法を書いたつもりだったんですけど....。
MsgBox Application.Evaluate("セルに書くのと同じ文字列を組合わせて書く")

>行数が1000行になったりする場合もあるので、やはりこうすると処理は遅くなる気はしますが、フリーズはしないかなと言う感じですかね。。。
シートに書いた関数と違って、1個1個のセルを再計算していくわけでないので大丈夫だとおもいます。
Countif関数も重い関数の部類に入りますから、数が多いとやたらと重くはなります。

【39158】Re:データ数のカウント
発言  Jaka  - 06/6/19(月) 13:07 -

引用なし
パスワード
   Application.Evaluate ("SumProduct((Sheet1!B2:B65535=" & J & ")*(Sheet1!C2:C65535<=" & _
            onDate & ")*(Sheet1!C2:C65535<=" & ofDate & "))")

【39160】Re:データ数のカウント
質問  aaa  - 06/6/19(月) 13:45 -

引用なし
パスワード
   ▼Jaka さん:

>いや、書いたコードを解析するのでなく、SUMPRODUCTをVBA上で使う方法を書いたつもりだったんですけど....。
>MsgBox Application.Evaluate("セルに書くのと同じ文字列を組合わせて書く")

そうでしたか。。。
私が使用していた

>WorksheetFunction.SumProduct((ws.Range("C2:C65535") = j) * _
>          (ws.Range("F2:F65535") <= " & onDate & ") * (ws.Range _
>            ("F2:F65535") <= " & ofDate & "))

とはただ記述の仕方が違うというだけでしょうか?


>シートに書いた関数と違って、1個1個のセルを再計算していくわけでないので大>丈夫だとおもいます。
>Countif関数も重い関数の部類に入りますから、数が多いとやたらと重くはなりま
>す。


それではこの方法に変えてみようと思います。
考えるのにしばらくかかりそうですが・・・


>Application.Evaluate ("SumProduct((Sheet1!B2:B65535=" & J & ")* _
>   (Sheet1!C2:C65535<=" & onDate & ")*(Sheet1!C2:C65535<=" &
>      _ ofDate & "))")

上記のコードを試してみました。
エラーは出ませんが、正しくカウントできませんでした。
「" & onDate & "」この部分が不思議なんですが、どういったことをしているのでしょうか?

お手数かけますが宜しくお願いします。

【39161】Re:データ数のカウント
発言  Jaka  - 06/6/19(月) 14:09 -

引用なし
パスワード
   ▼aaa さん:
>>Application.Evaluate ("SumProduct((Sheet1!B2:B65535=" & J & ")* _
>>   (Sheet1!C2:C65535<=" & onDate & ")*(Sheet1!C2:C65535<=" &
>>      _ ofDate & "))")
>
>上記のコードを試してみました。
>エラーは出ませんが、正しくカウントできませんでした。
>「" & onDate & "」この部分が不思議なんですが、どういったことをしているのでしょうか?
知らないです。
自分で変数として使ったんじゃないんですか?

そもそもこれを文字列にしてみただけだから....。
>Dai = WorksheetFunction.SumProduct((ws.Range("B2:B65535") = j) * _
>          (ws.Range("C2:C65535") <= onDate) * _
>            (ws.Range("C2:C65535") <= ofDate))

【39174】Re:データ数のカウント
回答  Kein  - 06/6/19(月) 15:50 -

引用なし
パスワード
   これでどうでしょーか ?

Sub MyData_Count()
  Dim Sd As Date, Fd As Date
  Dim FR As Range, FR2 As Range
  Dim MyR As Range, C As Range
  Dim Dic As Object
  Dim MyKey As Variant, MyItem As Variant
  Dim i As Long
   
  With Application
   Sd = .InputBox("取得開始の年月日を入力して下さい", Type:=1)
   Fd = .InputBox("取得終了の年月日を入力して下さい", Type:=1)
  End With
  If Sd = False Or Fd = False Then Exit Sub
  With Range("C:C")
   Set FR = .Find(Sd, , xlFormulas, xlWhole)
   Set FR2 = .Find(Fd, , xlFormulas, xlWhole, , xlPrevious)
  End With
  If FR Is Nothing Or FR2 Is Nothing Then
   MsgBox "入力された日付は見つかりません", 48
   Exit Sub
  End If
  Set Dic = CreateObject("Scripting.Dictionary")
  Set MyR = Range(FR, FR2).Offset(, -1)
  For Each C In Range(FR, FR2).Offset(, -1)
   If Dic.Exists(C.Value) = False Then
     Dic.Add C.Value, WorksheetFunction.CountIf(MyR, C.Value)
   End If
  Next
  MyKey = Dic.Keys: MyItem = Dic.Items
  For i = 0 To Dic.Count - 1
   Debug.Print MyKey(i) & " : " & MyItem(i) & "件"
  Next i
  Set FR = Nothing: Set FR2 = Nothing
  Set MyR = Nothing: Set Dic = Nothing
  Erase MyKey, MyItem
  With Application.VBE.MainWindow
   .Visible = True
   .SetFocus
  End With
  SendKeys "^(g)"
End Sub

*Excelのバージョンによっては、VBEを操作するコードが使えないケースが
あるかも知れません。そのときは With Application.VBE.MainWindow 以下
を削除し、手作業でイミディエイトウィンドウを開いて下さい。

【39177】Re:データ数のカウント
発言  Kein  - 06/6/19(月) 15:52 -

引用なし
パスワード
   訂正。処理に問題はありませんが、いちおう
>For Each C In Range(FR, FR2).Offset(, -1)


For Each C In MyR

と、変更しておいて下さい。

7439 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free