ExcelマクロVBA技術解説 | 大量データで処理時間がかかる関数の対処方法(WorksheetFunction) | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2016-09-08

大量データで処理時間がかかる関数の対処方法(WorksheetFunction)


大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、
そういう場合でも、多くの場合は何らかの対策があるものです、
個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ−ドについて解説します。

以下の例で解説します。




A列にコード、B列に数量、これが10万行あります。
コードは、A1〜A1000まであります。
そして、
E列にユニーク化したコードA1〜A1000があります。
F列に各コードの数量合計を求めます。

F2セルに
=SUMIF(A:A,E2,B:B)
と入れて下にコピーすれば求められるものです。
実際にやってみると、コピー後に「再計算」がしばらく出るのが確認できると思います。
E列が1000程度なので、再計算できますが、
E列が数万行になるような場合は、「再計算」がいつまでも終わらない状態になります。
俗にいう、計算式が重いという状態です。
そこで、
この処理をまマクロにしようと考えたと仮定してください。
以下の計測は、Corei5、メモリ8G、Excel2013でのものです。
たの要因を排除する意味と、比較のしやすさの意味で、行数等は固定値にしています。


まずは、普通にマクロVBAコ−ドを書いてみると、

Sub sample1()
  Dim i As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 1001
    Cells(i, 6) = WorksheetFunction.SumIf(Columns(1), Cells(i, 5), Columns(2))
  Next
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

シートでの再計算より、やや時間がかかてしまっています。

改善点はあるでしょうか、
データ範囲が列全体になっていますが、範囲を絞ったらどうでしょうか。

Sub sample2()
  Dim i As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 1001
    Cells(i, 6) = WorksheetFunction.SumIf(Range("A2:A100001"), Cells(i, 5), Range("B2:B100001"))
  Next
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

全く変わりません、ワークシート関数は良くできています。

マクロを覚えて、少したったくらいの方に多いように思いますが、
「配列を使うと早くなる」
これも盲信している人がいるようです。
では、配列を使って書いてみましょう。

Sub sample3()
  Dim i As Long
  Dim ix As Long
  Dim ary
  Application.ScreenUpdating = False
  Debug.Print Timer
  ary = Range("E2:F1001")
  For i = 1 To 1000
    ary(i, 2) = WorksheetFunction.SumIf(Range("A2:A100001"), ary(i, 1), Range("B2:B100001"))
  Next
  Range("E2:F101") = ary
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これで処理時間は
13.6秒〜13.8秒

全く変わりませんでした、実際には違いがあるはずなのですが、計測できる差がないという事です。
つまり、1000行程度の出力では、配列にしてもあまり意味がないのです。
もちろん、行数がもっと多いとか、計算する列数が多ければ、配列にすることで早くなります。

では、どうしようもないのでしょうか・・・
それでは、この記事の意味がなくなってしまいますね。
考えを変えてみます。
SumIf関数が無かったとして、これを集計することを考えてください。

あなたならどうしますか・・・

E2セルのA1を、A列で探してB列を足し上げる・・・
E列の1データについて、10万行の中から探しますか・・・

A1だけでもとんでもない時間がかかってしまいますね。
そんな非効率事はしないはずです。

A列で並べ替えれば、A1から順に並ぶので、A1だけなら、簡単に求められます。
これを1000回繰り返せばよいのです。


これをマクロVBAコ−ドにしてみましょう。

Sub sample4()
  Dim i As Long
  Dim i1 As Long
  Dim i2 As Long
  Dim total As Long
  Application.ScreenUpdating = False
  Debug.Print Timer
  For i = 2 To 100001
    Cells(i, 3) = i
  Next
  For i = 2 To 1001
    Cells(i, 7) = i
  Next
  Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
  Range("E1").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlYes
  i1 = 2
  i2 = 2
  Do Until i2 > 1001
    total = 0
    Do Until Cells(i1, 1) > Cells(i2, 5) Or i1 > 100001
      total = total + Cells(i1, 2)
      i1 = i1 + 1
    Loop
    Cells(i2, 6) = total
    i2 = i2 + 1
  Loop
  Range("A1").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
  Range("E1").Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes
  Columns(3).ClearContents
  Columns(7).ClearContents
  Debug.Print Timer
  Application.ScreenUpdating = True
End Sub


これなら処理時間は
2.5秒

格段に速いのがお分かりいただけると思います。
処理内容を書き出すと、
・C列にA:Bの今の順序を出力
・G列にE:Fの今の順序を出力
・A列で昇順に並べ替え
・E列で昇順に並べ替え
・A列とE列を順に比較しつつ同じならB列を足し上げる
・A列>E列 or A列の最終になったら、F列に合計を出力し、E列の次に移る
・C列で昇順に並べ替え、元の順に戻す
・G列で昇順に並べ替え、元の順に戻す
・C列をクリア
・G列をクリア
このようになります。
注意点としては、
E列も並べ替える必要があります。
先のサンプル画像では昇順に並んでいるように見えますが、
文字列の大小比較をする場合、
A1,A2,A3,・・・A9,A10,A11
ではありません、
A1,A10,A100,・・・A2,・・・
となります、これは実際に並べ替えてみればわかると思います。

このような、シーケンシャル処理ロジックは、
バッチ処理で大量データを扱ったことがあれば、ごく普通のロジックになります。
そして、大量データ同士の比較・集計においては、これが最も早いのです。
つまり、無駄が一切ないのです。
A列もE列も、上から下に向かって1回ずつしかループしていません。
1回のループなので、これが最も早いという事です。
sample1〜sample3もVBAコードとしては1回のループですが、
SumIf関数の中で、A列の上から下に向かってループしていることは想像に難くないはずです。

結論として、
大量データ処理において処理時間を短縮しようと思ったら、
まずは、データを並べ替えてみる事です。
そうすれば、データの特質が見えてきますので、
それから適切な処理ロジックを考えてみて下さい。


今回はSumIf関数を例にしましたが、
CountIf関数でも、VlookUp関数でも、同様の考え方で出来るのがご理解できますでしょうか。
もし、これらの関数で処理時間がかかっているようでしたら、ぜひ試してみて下さい。


今回の事例と同じような考え方として、
【奥義】大量データでの高速VLOOKUP
この記事は、マクロVBAではなく、ワークシート関数についてですが、
考え方として、非常に参考になると思います。

また、そもそもVBAの最低限の速度対策は必須です。
以下を参考にしてください。、
エクセルVBAのパフォーマンス・処理速度に関するレポート
マクロVBAの高速化・速度対策の具体的手順と検証
速度比較決定版【Range,Cells,Do,For,For Each】
大量データにおける処理方法の速度王決定戦




同じテーマ「ExcelマクロVBA技術解説」の記事

大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
配列の使い方について
最終行の判定、Rangeオブジェクトと配列、高速化の為に
記述による処理速度の違い
速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



  • >
  • >
  • >
  • 大量データで処理時間がかかる関数の対処方法(WorksheetFunction)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP