Excel VBA 質問スレッド No.158 (未解決)

countifsをvbaで使いたい

投稿者 : うかい     投稿日時 : 2021/01/08(Fri) 19:20:46     OS : Windows 10     EXCEL : Excel 2016
はじめまして。
countifsをvbaで使いたく思っています。
worksheetfunction関数レベルでいいのですが、絶対参照やら別シートの参照やらがあり、私の知識では太刀打ちできず、お力を貸していただきたいです。

まず、計算にかかわるシートは「履歴シート」、「発話時間シート」の2シートになります。
履歴シートに「データ」という表があり、その中に
・発話日
・発話時間
・分類
という項目があります。

発話時間シートには分類ごとに日付、時間(1分ごと)にカウントした表が4行あけて、分類1〜20ごとに作成されています。

普通のcountifs関数の式は以下の通りです。
=COUNTIFS(データ[発話日],$A15,データ[発話時間],B$14,データ[分類],$A$14)

ちなみになぜ関数でやらずvbaでやろうとしているかというと、実はもう1枚シートがあり、それがほぼ発話時間シートと同じ作りのため、関数を1セルずつのベタばりだと3時間かけても計算が終わらず、保存もできないためです。

ほかに質問の際になにか不足点があれば教えて下さい。
本当に時間がかかり、さらに時間をかけても成果が得られず、弱っております。よろしくお願いいたします。

[返信 1] Re : countifsをvbaで使いたい
投稿者 : EUNO     投稿日時 : 2021/01/08(Fri) 21:59:01
ブックをこちらで再現するのが難しいですし(参照元のテーブルのデータ数がどれくらいあるのかなどわからない)、なぜそんなに時間がかかるのかわかりませんが、多分VBAでworksheetfunction使っても早くならないように思います。
元のデータというかレコードが大きすぎるんじゃないでしょうか。
ワークシート関数や構造を根本的に見直した方がよいような気がします。
VBAを使うにしてもcountifs関数をそのそのまま使うのでは効果はないかも。推測ですが。

[返信 2] Re : countifsをvbaで使いたい
投稿者 : うかい     投稿日時 : 2021/01/10(Sun) 15:57:51
function関数でやると変わるかも、と相談した方に言われてやってみたいと思ったのですが、変わらないですか…。わたしが来る前からずっとこのフォーマットでやっていたので、根本的にフォーマットを変えるのは難しいのです。
ちなみに少し別件ですが、後学のためにチャレンジしてみたところ、

=COUNTIFS(データ[発話日],$A15,データ[発話時間],B$14,データ[分類],$A$14)

上記式を下記式にVBA用に直してみました。

Range("B21:BCK35") = _ worksheetfunction.Coutifs(sheets("通信履歴ログ").Range("A1:A8000"),"$A21",sheets("通信履歴ログ").Range("B1:B8000"),"B$20", _
sheets("通信履歴ログ").Range("Q1:Q8000"),"$A$70")

ただし、これをセットして実行するとカウントされるべき場所も含めて、すべて答えが0になってしまいます。
これをきちんとカウントさせるにはどう修正すべきでしょうか。

実際使用してみるかはともかく、後学のために教えていただければ幸いです。再度よろしくお願いいたします。

[返信 3] Re : countifsをvbaで使いたい
投稿者 : EUNO     投稿日時 : 2021/01/10(Sun) 20:19:59
データ量が大きくデバッグしにくい(=理解しにくい)ので、設定をシンプルにします。
A1セルにA、B1セルにB、C1セルにC、G1セルにA、H1セルにB、I1セルにC、と文字列を入れて下記を実行してみます。
Sub test()
Range("D1:D10") = WorksheetFunction.CountIfs(Sheets("通信履歴ログ").Range("A1:A10"), "$G1", Sheets("通信履歴ログ").Range("B1:B10"), "$H1", Sheets("通信履歴ログ").Range("C1:C10"), "$I1")
End Sub

D1~D10に0が表示されると思います。

次にA1セルに$G1、B1に$H1、C1に$I1 と文字列を入力して同じプロシージャを実行してみます。
すると、D1からD10に1が表示されます。

つまりこれは、2つの点で意図どおり動いていません。
(1)$G1、$H1、$I1という文字列を、countifsしている
(2)その結果をD1~D10セルに(すべて同じ値を)入力している

(1)を解決するのは簡単です、$G1などをRange("$G1").ValueとすればOKです。
(2)を解決するのはちょっと難しそうだな、と思います。
・Formulaプロパティを使って計算式を入力する(これはvba使う意味ない)
・ループ処理をする
これくらいしか思いつかないけど、他に良い方法があればどなたか教えてください。

当掲示板について
返信入力フォーム
お 名 前  :
内  容   :
ステータス  : この質問を解決済みにする

認証コード  :
        キャプチャ画像


( 処理日時 : 2021-01-27 13:43:53 )

Page
Top