質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

3123閲覧

VBAの配列について質問

cd987456

総合スコア33

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2017/04/29 07:29

VBAの配列について質問があります。
処理速度のバラツキがあり、教えてほしいです。

Dictionaryオブジェクトを使ってVLOOKUPみたいな割り付けをしています。
2つのシートがあります。
『抽出結果』シートと『条件』シートです。
『条件』シートのA列、B列にデータが入っています。
『抽出結果』シートのA列にデータが入っています。

やりたいことは、
『抽出結果』シートのA列に『条件』シートのA列のデータが一致したら
『条件』シートのB列のデータを『抽出結果』シートのB列に記載する。

コードを書いてみました。

Public Sub dic_03() Dim mydic As Object, mykey Dim c, myval Dim i As Long Application.ScreenUpdating = False Application.EnableEvents = False Set mydic = CreateObject("Scripting.Dictionary") With Sheets("条件") For i = 2 To 6000 If Not mydic.exists(.Cells(i, 1).Value) Then mydic.Add .Cells(i, 1).Value, .Cells(i, 2).Value End If Next i End With With Sheets("抽出結果") For i = 2 To 180000 .Cells(i, 2).Value = mydic.Item(.Cells(i, 1).Value) Next i End With Set mydic = Nothing Application.EnableEvents = True Application.ScreenUpdating = True MsgBox ("終了しました") End Sub

処理は正しくできるのですが、20秒くらいで終了する時のあれば、1分以上かかる時もあります。
処理しているパソコンは同じパソコンで、他に開いているファイルもありません。
配列の初期化のコードなど入れた方が良いのでしょうか?

教えて下さい。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

処理は正しくできるのですが、20秒くらいで終了する時のあれば、1分以上かかる時もあります。

同じデータで処理速度に差がでるということでしょうか。
それとも、異なるデータで差がでるのでしょうか。

後者なら、

mydic.exists で存在してないかチェックして登録しているということは、『条件』シートのA列には重複があるのですよね。だとしたら、データによってDictionaryに格納される件数が異なるので、処理速度にバラツキがでる可能性はありますね。

登録ループの後に下記のコードを挿入して、処理速度のことなるデータで違いがあるか確認してみてはどうでしょうか。

Debug.Print mydic.Count

配列の初期化のコードなど入れた方が良いのでしょうか?

Dictionaryはスコープを抜ければ破棄されますし、
CreateObject で生成した時点で初期化されていますので、
初期化コードなどは不要です。

蛇足

あと、処理速度の改善を検討する場合、まずは、どの処理に時間がかかっているのか計測する必用があります。
例えば、処理1 と 処理2 があって、処理1に秒、処理2に10秒かかっている場合、処理2の速度改善を優先して取り組むべきと判断できます。

下記のようなコードでそれぞれの処理にかかる時間が計測できます。

Dim t As Single t = Timer 'セルからDictionaryへの登録処理 Debug.Print Format(Timer - T,"0.00秒") t = Timer 'Dictionaryからセルへの書き込み処理 Debug.Print Format(Timer - T,"0.00秒")

蛇足の蛇足

サンプルを作成して当方の環境で処理速度を計測してみました。
"条件"シートに6000件のデータ、
重複を排除してDictionaryに登録すると 3000件
"抽出結果"シートには180000件のデータ
質問のコードで3秒ぐらい。
同じデータなら、誤差範囲の差しかでませんでした。

でコードのチューンナップしてみました。
セル範囲からの読み込み、書き込みを配列を介して一括処理に変更してみました。
これで、処理時間が1/3に短縮できました。

Public Sub dic_04() Dim mydic As Object Dim i As Long Dim ary1() Dim ary2(2 To 180000, 0) As Long Application.ScreenUpdating = False Application.EnableEvents = False Set mydic = CreateObject("Scripting.Dictionary") With Sheets("条件") ary1 = .Range("A2:B6000").Value For i = 6000 To 2 Step -1 mydic(ary1(i - 1, 1)) = ary1(i - 1, 2) Next i End With With Sheets("抽出結果") For i = 2 To 180000 ary2(i, 0) = mydic.Item(.Cells(i, 1).Value) Next .Range("B2:B180000").Value = ary2 End With Set mydic = Nothing Application.EnableEvents = True Application.ScreenUpdating = True End Sub

投稿2017/04/29 20:17

編集2017/05/01 23:11
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

cd987456

2017/05/01 00:22

回答ありがとうございます。 実際のデータは『条件』シートも『抽出結果』シートもデータ量は都度違います。 なので、実際のコードは最終行を取得して都度処理するようにしています。 質問に挙げたデータ量はMAXを想定しました。 テストデータはループ回数と同じで条件に6000件、抽出結果に180000件あり、データの内容は 変わりません。 アドバイス頂いたようにタイマーを入れてみました。 すると、1回目は4分37秒でした。2回目は24秒でした。 早く処理される時は1回目の実行後、転記したデータを消して、再度実行すると、早く処理されているようです。ただ、それは、毎回ではないです。 チューンナップして頂いたコードは約3秒くらいで処理しています。 すごく早いです。しかも安定しています。 教えて頂いたコードを基に自分のコードを書き換えて処理します。 ありがとうございました。
guest

0

20秒くらいで終了する時のあれば、1分以上かかる時もあります。

これは、同じデータで何度も実行した場合の話でしょうか?
それとも異なるデータで処理速度が異なる?
いずれにしても不可解ではありますが、後者の場合なら.Item(key)で探査できるということは、まさか線形探査ではないと思いますので、連想配列はソートデータを持っているのだと思います。(もし線形探査なら、結果側のキーの並びによって処理時間が劇的に変わるでしょう。)
では、いつソートしているのか?ADDしたときでは無いでしょうか?もしくはkeyの存在チェックをしているのでそのときかも。
とすると条件側に重複データが少ないと(つまり登録が多いと)ソートが何度も発生して遅いのかも知れません。

以下は、データによって時間が変わることとは関係の無い話です。
登録時に6000回都度cellを見ていますが、それは遅いそうです。
office TANAKA セルを配列に入れる

またExcel備え付けのワークシート関数が結構早いみたいなので、条件に合うなら積極的に使ったほうが良いと思います。

投稿2017/04/29 13:41

編集2017/04/29 13:43
hirohiro

総合スコア2068

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

Excelでやっている限りある程度時間がかかるのは仕方ないですが、なぜ処理時間にバラつきが出るのかはちょっと実際の実行環境を見てみないと分かりませんね。
この関数の問題ではなくて、他の要因は何か考えられませんか?たとえば「条件」シートのB列が数式の場合がある。他のシートでオブジェクトが死ぬほどたくさん使われている場合がある。実はバックグラウンドで何か処理が動いている場合がある。など。
完全に同じ条件であれば、あまりこの関数で処理時に実行時間の差が出るようには思えません。

どうしても原因追求したいのであれば、ループ内でログを出力するようにし、早い場合と遅い場合でどこに差が出ているか、というところから追っていくしかないと思います。

少しでも早くするという観点であれば、条件シートは6000回、抽出結果シートは180000回問答無用でループしますが、セルの内容が無ければそこでループ終了するように変更してみるというのはどうでしょうか。

投稿2017/04/29 10:38

akabee

総合スコア1947

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問