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

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

ただいまの
回答率

88.92%

VBAの配列について質問

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,835

cd987456

score 33

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分以上かかる時もあります。
処理しているパソコンは同じパソコンで、他に開いているファイルもありません。
配列の初期化のコードなど入れた方が良いのでしょうか?

教えて下さい。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

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/05/01 09:22

    回答ありがとうございます。
    実際のデータは『条件』シートも『抽出結果』シートもデータ量は都度違います。
    なので、実際のコードは最終行を取得して都度処理するようにしています。

    質問に挙げたデータ量はMAXを想定しました。
    テストデータはループ回数と同じで条件に6000件、抽出結果に180000件あり、データの内容は
    変わりません。

    アドバイス頂いたようにタイマーを入れてみました。
    すると、1回目は4分37秒でした。2回目は24秒でした。
    早く処理される時は1回目の実行後、転記したデータを消して、再度実行すると、早く処理されているようです。ただ、それは、毎回ではないです。

    チューンナップして頂いたコードは約3秒くらいで処理しています。
    すごく早いです。しかも安定しています。

    教えて頂いたコードを基に自分のコードを書き換えて処理します。
    ありがとうございました。


    キャンセル

0

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 88.92%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る