【第2位】エクセルVBA高速化ランキング
前回の第2号では、高速化ランキングの第1位ということで、初回の第1号で
ご紹介した「画面更新の非表示」について、さらにもう一歩踏み込んだ「半
非表示」というテクニックをご紹介しました。
それでは早速、今回は続くランキングの第2位の発表です。
【CSVデータ】をエクセルシートへ取り込む際の高速化
データベースやその他のシステム等々から得たデータを、エクセルのシート に落として、集計やその他色々なデータ加工をするということは実に多いで すよね。読者の皆さんでそのようなことを頻繁にやられているという方も多 いことと思います。
そこで、今回の高速化ランキングの第2位としてご紹介するのは、大容量の CSVデータをVBAでエクセルシートへ取り込む際の高速化 についてです。
まずは、もっともポピュラーな方法（物理的にテキストファイルとしてCSV データを読み込んでからエクセルシートに落としていく）を用いた場合で （サンプル１）ですが、これはおのずと大変に時間が掛かります。
サンプル１（※宣言文や関数の中身等は省略しています。）
―――――――――――――――――――――――――＋
Open "C:\data.csv" For Input As #5
j = 1
Do While Not EOF(5)
Line Input #5, bufline(j)
j = j + 1
Loop
Close #5
n = j - 1
Application.ScreenUpdating = False
For j = 1 To n
Call CsvBuf1(bufline(j), a, m)
For i = 1 To m
Cells(j, i).Select
ActiveCell.FormulaR1C1 = a(i)
Next i
Next j
―――――――――――――――――――――――――＋
このサンプル１での処理時間は 110秒です。※今回のベンチマークでは、2万行程度のCSVデータ (ファイルサイズにして2.5MB程度のもの)を用いて 計測しています。
一般的に、言わばこの方法が王道でありますのでプログラマーとしてはどう してもこの方法でやりたいと思うのが人情であるわけなのですが、これをや ってしまっているが為に、ものすごーーく遅いエクセルのツールというのを 私はこれまでに何度も目にしたことがあります。
とにかく、シートへの書き出しに非常な時間がかかりますので、前回紹介し た「更新の非表示」を使った場合でも、この方法ではかなり重たくなってし まうことは避けられません。
なので、もっと速くなる方法としてまず次のサンプル２の方法があります。
このプログラムは、簡単に「マクロの記録」を使ってファイルを開く操作で CSVファイルを開いて自動記録させれば得られる最も簡単なプログラムです。
サンプル２
―――――――――――――――――――――――――＋
Workbooks.Open Filename:= "C:\data.csv"
―――――――――――――――――――――――――＋
このサンプル２では 37秒でした。これであれば、先ほどのサンプル１の場合に比べて約３倍速くなるという結 果が得られたことになります。
2万行のCSVデータをエクセルシートに取り込むのに37秒という数字は、（こ れが速いと言えるか、遅いと言うべきなのか？）実に微妙な数字ですね。
で、何とかもう少し速くする方法はないものか？と言うと、次のサンプル３ の様なファイル形式を変えてから取り込む方法というものがあります。
一文が長いので途中省略して書いていますが、このプログラムも「マクロの 記録」を使ってファイルを開く操作でtxtファイルを開き、区切り文字の設 定等して自動記録させれば簡単に得ることのできるプログラムです。
サンプル３
―――――――――――――――――――――――――＋
Workbooks.OpenText Filename:= "C:\data.txt" _
, Origin:=932, StartRow:=1, DataType:=xlDelimited, ・・・ _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=T ・・・ _
Comma:=True, Space:=False, Other:=False, FieldInfo ・・・ _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1) ・・・ _
TrailingMinusNumbers:=True
―――――――――――――――――――――――――＋
このサンプル３では 27秒でした。この結果、先ほどのWorkbooks.OpenよりもOpenTextを使った方が30％程度 速くなるということが分かります。
これは、事前にCSVファイルの拡張子.csvを、ただ.txtに変えておきさえす ればよいだけなので、(FileCopy等を使えばプログラム内でも簡単にできま すし)至って簡単な話です。
実は、もう一つ、画期的に速くする裏わざというのがあります。
これは、まさしく裏わざというべきものなので、この方法を用いることを 特におすすめするものではありませんが、あくまで参考までにということ でご紹介します。
サンプル４
―――――――――――――――――――――――――＋
MyFile = "text;" & "C:\data.csv"
With ActiveSheet.QueryTables.Add(Connection:=MyFile, ・・・
.Name = "link1"
.TextFileCommaDelimiter = True
.TextFilePlatform = 932
.Refresh
End With
ActiveWorkbook.Names("link1").Delete
―――――――――――――――――――――――――＋
このサンプル４の結果は 6秒でした。まぁ、2万行で6秒程度であれば、誰からも文句は出ない十分なスピードで はないかと思います。
ちょっと、サンプル４について解説しますと、
これは、「外部データの取り込み」や「クエリテーブル」といったExcelの 既存の機能を使ってやる方法なんですが、ちょっとこの難点は、これらの Excelの機能を使うと必ず外部リンクが張られてしまうという所にあります。
なので、サンプルの最後の行でわざわざその外部リンクを削除する処理を入 れてやる必要があります。
断っておきますが、これはExcelの既存の機能をその本来の目的とはまった く違う目的で、しかもプログラムで用いるということの危険性が大いにあり ますので、あくまでもご参考までです。
最後に、今回のベンチマークテストの結果をまとめますと、
Workbooks.Openで3倍速くなる。
Workbooks.OpenTextなら更に30％速くなる。
裏わざを使えば2～3万行が10秒以下も可能に。
ということです。
NEXT >>
・第3位 大量データのソート
・高速化ランキングの目次へ戻る
■ 無料メールマガジンの登録(まぐまぐより配信中！)
Go!Go! エクセルマクロをはじめよう！ (マガジンID：0000135169)
|
マクロ作りのヒントやノウハウ、IT業界の動向や話題などなど、この道35年のベテラン SEが楽しくわかり易く直伝します。（完全まぐまぐ配信なので登録/解除も安心です！） |
