MS Access Tips/Sample and VBA and Blog customize etc...

テキストマッチングしたセルの背景色を変更する処理の高速化にチャレンジ

ExcelのVBAで、参照リストと部分一致するセルの背景色を変更するという処理です。参照リストも対象リストも件数の多いので処理に時間がかかる。それを短縮化したいという要件です。

何も対策していないベタのコードから、いろいろなテクニックを駆使してどこまで高速化できるかチャレンジしてみました。

タイトル画像

難易度:

要件、サンプルデータ生成

元ネタは下記の質問。

VBA - ドメインのリスト一覧から配列に含まれるドメインのみを着色したい(75331)|teratail

それを少し改変。

Sheet1 にドメインと日付のリストが2万件。
Sheet2 にNGドメインのリストが3000件。
ドメインに、NGドメインが部分一致したらそのセルの背景色を赤にする。

実際のドメインのサンプルを作成するのは難しいので、Rnd関数でランダムなアルファベットの文字列を自動生成してサンプルデータを作成する。

サンプルデータ生成関数

上記のコードでは、検索対象20000件、検索語3000件を生成するが、最初は、最初の定数の件数を200件/30件ぐらいから始めて、正しく動作するか、結果は正しいか、確認して、少しずつ増やしていく。いきなり大きな件数で始めるとなかなか終わらなくて、強制終了するしかなくなる場合があるので。

あと、生成される文字列の桁数を調節して、ヒット率が極端に高かったり低かったりしないようにする。

サンプルデータ

高速化対策なしのベタなコード

Do Loop を2重にして、それぞれ先頭から1セルずつ参照して、2つの値を Like演算子で比較して、部分一致していたらセルの背景色を変更、という何の工夫もないコードです。

実行結果(イミディエイトウィンドウ)

4分を超えました。これだけのために4分待つのはきついですね。ただ、何も対策しない割には思ったより速い印象です。

4504件というのは背景色変更処理をした件数ですが、重複があるので実際の該当件数とは異なります。

高速化対策1

Activate でシート選択を切り替えない
同じセル参照を繰り返さない、

前のコードとよく見比べてください。
シートを With あるいは 変数に格納することで参照するようにして、Activateを排除しました。
Cells(i, 1) というセル参照も、変数に格納、With を利用して1回だけで済ませるようにしました。

実行結果(イミディエイトウィンドウ)

前回より1分近く短縮できました。

高速化対策2 画面更新、イベント発生、自動計算の停止

前のコードに、処理前に更新関係を抑制するコード3行、処理後に元に戻すコードを3行、挿入しただけです。

実行結果(イミディエイトウィンドウ)

1秒ぐらいしか短縮できません。これは期待はずれの結果ですね。
今回のシートはシンプルなものなので、あまり影響を受けないのかもしれません。
計算式が埋め込んであったり、イベント処理が仕込んであるものだったら効果はあると思います。

高速化対策3 セル範囲を配列に格納

セル参照を繰り返さないというのは既に書きました。ところが、ループしながら参照しているので、結局、Sheet2 は3000回、Sheet1は20000✕300回分はセルを参照していることになります。

配列を用意しておいて、そこにデータのあるセル範囲の値をまるごと代入することができます。あとは、配列を参照することでデータを取り出せます。セルを参照するより、配列を参照する方が遥かに高速です。

バリアント型の動的配列に格納できます。上記のように1列のみ代入しても二次元配列になるので注意してください。また、インデックスは1から始まります。

これを組み込んだのが下記のコードです。For Each で回すことができます。また、インデックスが必要な時は、For i = 1 to ... で回します。

実行結果(イミディエイトウィンドウ)

一気に短縮できました。ほぼ20倍の高速化です。今回のようにループでセル参照が多い処理には効果バツグンですね。

もう少し短縮できないでしょうか。背景色を変更するために4504回参照しています。変更する処理も重そうです。

s.Cells(i + 1, 1).Interior.Color = RGB(242, 221, 220)

これをなんとかできないでしょうか。

高速化対策4 AutoFillterで背景色変換処理をまとめる

AutoFilteメソッドで、Criteria1 に "*" & ngDomain & "*"
を指定して実行すると条件に一致した行のみに絞り込まれます。
表示されてい範囲のみに背景色を設定します。
ループはNGドメインの3000回だけですので、短縮が期待できそうです。

実行結果(イミディエイトウィンドウ)

駄目でした。前回より時間がかかってしまいました。
3000回のオートフィルターは結構重い処理になるようです。

今回はここまで。
次回は、AutoFilteを使う方法の高速化にチャレンジします。
配列でループする方法を超えることができるか、乞うご期待。

実行結果について

実験した環境(Win10 64bit, Excel2016 32bit, CPU Core i7, RAM 16GB)
一つのコードで3回以上実行して結果に大きな差異がないことを確認してます。
あくまで、サンプルデータでの結果のですので、データによっては結果が異なる場合もありえますので、あくまで参考程度に。

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/511-506ed631
該当の記事は見つかりませんでした。