none
マクロ処理速度Excel10とExcel19では8倍異なる RRS feed

  • 質問

  • Excel 10(win7)で作成したマクロをExcel 19(win10)で実行すると従来2分だった処理時間が16分もかかりました。マクロにリンクしたフォームボタンからではなくてコード画面から実行すると従来通り2分でした。試みにExcel 10(win7)で再度動かしても変わらず2分です。

    解決方法ご教示ください。

    2020年6月13日 0:47

すべての返信

  • もう少し詳細なバージョン情報を提示できますか?

    Excel 19 という製品はまだ無いはずです。2019 のことでしょうか。
    もしそうだとすると、Excel 10 というのも 2002 なのか 2010 なのか曖昧です。

    • Excel 9.0 → Excel 2000
    • Excel 10.0 → Excel 2002
    • Excel 11.0 → Excel 2003
    • Excel 12.0 → Excel 2007
    • Excel 14.0 → Excel 2010
    • Excel 15.0 → Excel 2013
    • Excel 16.0 → Excel 2016/Excel 2019/Office 365


    また、漠然と「マクロ」と言われても、マクロシートなのか VBA なのか不明ですし、
    そもそもどのようなコードが呼び出されているのかも示されていないため、
    見直すべき点や調査すべき点を予測することが困難です。

    まずはマクロ内のどの処理が特にボトルネックになっているのかを
    探るところから始めてみては如何でしょうか。

    2020年6月13日 5:10
  • ご検討いただけるのに正確さに欠けて申し訳ありません。Excelを起動したときの表示はExcel 2010とOffice2019です。

    プログラムはA,B2つの商品売上を記載したシートから売上シートに合計するだけです。

    行方向(縦軸)に販売店、列方向(横軸)に前年度売上、予算、今年度売上、その他2つの項目が5列ごと12か月分並んでいる表にて前年度売上、予算、今年度売上の商品A、Bを足している単純なものです。商品のカテゴリーが沢山あるのでシートは50枚ほどあります。

    コードの一例は以下の通りです

    Sub 計算()

    Application.ScreenUpdating = False

    e = Sheets("売上").Cells(Rows.Count, ).End(xlUp).Row

      For i = 3 To e

       For h = 3 To 58 Step 5

         Sheets("売上").Cells(i, h) = Sheets("A売上").Cells(i, h) + Sheets("B売上").Cells(i, h)

         Sheets("売上").Cells(i, h + 1) = Sheets("A売上").Cells(i, h + 1) + Sheets("B売上").Cells(i, h + 1)

         Sheets("売上").Cells(i, h + 2) = Sheets("A売上").Cells(i, h + 2) + Sheets("B売上").Cells(i, h + 2)

         Sheets("売上").Cells(i, h + 3) = Sheets("A売上").Cells(i, h + 3) + Sheets("B売上").Cells(i, h + 3)

       Next h

     Next i

    End Sub

    お願いはExcel 2010では2分で完了するのに同じプログラムでOffice 2019では16分もかかるのをOffice 2019でも2分になる処理時間短縮方法をお教えいただきたいことです。

    ちなみにマクロ登録した図形ボタンで動作させていますが、Office2019でもコード画面(エディター画面)にてSub/ユーザーホームの実行では2分で完了します。

    説明が不十分であればご指摘ください。よろしくご教示お願いいたします。

    2020年6月14日 1:47
  • 処理の最初に

    Application.Calculation = xlCalculationManual

    を、最後に

    Application.Calculation = xlCalculationAutomatic

    を入れると改善しませんか?

    もっと高速化したいなら、値をメモリ配列に格納すればよいと思います。


    • 編集済み minmin312 2020年6月14日 3:50
    2020年6月14日 3:50
  • ご教示ありがとうございます。

    Application.Calculation = xlCalculationManualとApplication.Calculation = xlCalculationAutomaticを入れて速度を見ました。

    Office 2019では入れる前は平均20分でしたが入れると平均14分と確かに処理速度は速くなりました。

    ただしこれをExcel 2010で処理すると入れる前は平均1分30秒、入れると平均38秒で終了します。

    問題にしているOffice 2019とExcel 2010では処理速度に大きな違いは相変わらず残っています。

    何故なのかを時間をかけて調べていきます。思い当たる原因などありましたらご教示よろしくお願いいたします。

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

    2020年6月14日 15:06
  • 「プログラムの処理にかかる時間の80%はコード全体の20%の部分が占める。」パレートの法則という考え方があります。時間のかかっていない部分をどんなに削減しても効果はほとんどありません。最初に魔界の仮面弁士さんが提案されていますが、時間を要している部分を特定しない限り、時間を削減することはできないと考えるべきです。
    2020年6月14日 22:42
  • ご検討いただけるのに正確さに欠けて申し訳ありません。
    Excelを起動したときの表示はExcel 2010とOffice2019です。

    2019 のどのバージョンになるでしょうか。月次の Update によって処理時間が変化するような事象もありえるので、もう少し突っ込んだ情報が欲しいです。(Excel 2019 なら [ファイル]-[アカウント] の所から、より細かいバージョン情報を得られます)

    コードの一例は以下の通りです

    たとえば冒頭の『e = Sheets("売上").Cells(Rows.Count, 1).End(xlUp).Row』ですが:

    1) 「Rows.Count」は、どのシートの行数を表しているのかが曖昧になっています。このコードだと、売上シートの行数となることもあれば、それ以外のシートの行数を返すこともあるわけですが…それは意図した動作でしょうか?
    いずれにせよ、アクティブなシートは変動することがあるので、コード上ではどのシートのセルであるのかを常に明示することをお奨めします。

    2) 変数宣言がされていないように見えます。
    原因箇所の切り分けを正しく行うためにも、Option Explicit を付与して、適切なデータ型の変数を事前に設けておくことをお奨めします。

    お願いはExcel 2010では2分で完了するのに同じプログラムでOffice 2019では16分もかかるのをOffice 2019でも2分になる処理時間短縮方法をお教えいただきたいことです。

    高速化として思いつくのは:

    • Cells プロパティが返す情報は Excel.Range オブジェクトです。既定のプロパティに頼らず、.Value / .Value2 プロパティ等を明示した方が良いかと思います。
    • 配列を使って、複数のセル範囲を一括して処理するようにします。

    当方の Excel 2016 では、今回提示頂いたコードでは低速化する症状は見られませんでした。
    (2019 が手元になかったので、現段階では、本当にエクセルバージョンのみに起因しているのか判断がつきませんが…)

    • 第三者が検証できるよう、実験用に単純化したサンプルを提供していただくことはできますか? (再現性のある情報が提示されると、第三者が追加検証しやすくなります)
    • 既存ファイルを用いず、投稿いただいたコードを新規ブックに貼って実験した場合にも低速化が再現することを確認できていますか? (再現しないなら、先の投稿に書かれていない別の部分に原因があるのかもしれません)
    • フォーラムに投稿したコードではなく、問題が発生しているブックのコードを新規ブックで試した場合はどうですか? (特定のブックでのみ起きる事象なのかの検証です。たとえば、想定していない外部シートへの参照が残っていたりとか、コピペを繰り返しているうちに、条件付き書式が断片化していたりとか…)
    • Excel 2019 を利用している他の PC で実行させた場合も、同様に低速化する事象が見られますか? (たとえば印刷設定などがあるシートにおいて、使用しているプリンタードライバーの差によって、速度差が生じるケースを経験したことがあります)
    • ループ内の特定の処理だけが、異常に時間がかかってしまっている状態なのでしょうか? それとも、極端に遅い処理は無いけれども、それぞれの実行が 2010 よりわずかに長いことで、ループ回数が増えるほど、その時間差が蓄積されて遅くなってしまっているのでしょうか? (たとえば、0.02 秒程度の僅かな差でも、それが 1万回のループ内で行われていれば、累積で 3 分以上の差となります)
    • ループ回数を変化させずに、未使用のワークシートを多数配備すると、処理時間が伸びたりしますか? (シート数が特定の枚数を超えた途端、急激に遅くなる事象を経験したことがあります)
    • シートの総数を減らした実験用のブックにて、ループ回数が変わらないようにして処理させた場合、処理時間が短くなることはなく、やはり同じ程度の時間がかかってしまいますか? (ループ回数の問題なのか、シート数やデータ数の量が問題なのかの切り分け)

    2020年6月15日 2:14
  • それほど時間のかかる処理には見えないのですが・・

    例えば、Worksheet_Change イベントプロシージャにもコードを書いていますか?

    処理の最初に
    Application.EnableEvents = False
    を、最後に
    Application.EnableEvents = True
    を入れてみてはどうでしょうか。



    2020年6月15日 9:56
  • 詳細に具体的にトライ事項をお教えお頂きありがとうございます。なぜかは不明ですが時間のかかっている処理部分がわかってきましたのでお教えいただいた事項について一つひとつ時間をかけて分析してまいります。

    なお2019のバージョンは2019 MSO (16.12827.20200)32ビットです。

    e = Sheets("売上").Cells(Rows.Count, 1).End(xlUp).Rowは何枚もあるシートの最下段(行)は全て同じなので代表のシートで出していたものです。これも調べる事項とします。

    分かった事項内容をもとにまたお尋ねします。お手数をおかけします。ありがとうございました。

    2020年6月17日 1:14
  • 時間がかかる原因がわかりました。

    頂いたご指摘を参考にどの部分で時間がかかるかを調べているうちに、セルの値の取り方に間違いがあるとわかりました。

    すなわち値の入っていないセルを空白「””」と判断し、また計算結果の「0」は表示しないようセルに「””」を入れていました。次が問題の時間がかかった部分です。

    e = Cells(Rows.Count, 2).End(xlUp).Row
    For h = 6 To 61 Step 5
      For i = 3 To e
       If Cells(i, h - 1) = "" Then
         Cells(i, h)=""
       Else: Cells(i, h) = Cells(i, h - 1) - Cells(i, h - 2)
       End If
     Next i
    Next h

    それを次のように上記の演算部分の「””」を「0」に変え、および結果が「0」を表示しないようClearContentsにしました。

    If Cells(i, h - 1) = 0 Then
         Cells(i, h).ClearContents

    結果飛躍的に処理時間が短くなりました。初心者の私を辛抱強くご指導いただいたことに感謝申し上げます。まことにありがとうございました。


    2020年6月18日 1:38