ＶＢＡ どっちが速い？

質問

配列（要素１万個）の中を探す時間を短縮するために、１回探し見つかったらその要素を削除し、次に探す際はちょっと短時間で済むようにしたいと思ってます。そのために配列をコレクションに変更しようと思ってます。可能でしょうか？

詳細
商品名が１万個、セルに入力されてます。各商品に該当するファイルを開き、処理後閉じ、次の商品に移ります。開くファイル名は配列に入ってます（要素数１万個）。ファイル名の一部に商品名があるので、これを検索し順次ファイルを開きます。

Dim Files(10000) As String
Dim i as Long, j as Long

’Filesにファイル名を代入する処理が途中にあります
.
For i=2 to Cells(Rows.Count, 1).End(xlUp).Row
Name="*" & Cells(i, 4) & ".txt"
For j=1 to Ubound(Files)
If Name Like Files(j) then
'ファイル開き処理
　　End if
Next
Next
雰囲気だけで書いてるので、実際と違います。書き間違いで動作しない可能性が高いです。

配列に収めたファイル名があって、セルの商品名に該当するファイル名を配列から探し、開き、処理、閉じるのです。しかしながら所要時間が１個１秒未満程度ですが、結構な時間になります。

質問
配列をコレクションに変更し、１個処理が終わる度にコレクションの要素から削除していけば、探す時間は短縮されると思ったのですが、どうでしょうか？速くなるでしょうか？

回答No.7

WindFaller · ベストアンサー

こんにちは。

一応、二分木検索方を知らないわけではありませんが、Excel VBA?で、そのアルゴリズムを使ったことはありませんね。あえてやればできるというだけで、それに代わるものを考えてしまいます。

スピードを比較したわけではありませんから、どちらがどうとは言えませんが、1次元配列で１万個程度なら、ワークシート関数のMatch関数を使うのが速いような気がしますね。Match関数の中身はどうなっているかは知りませんが、二分木(Binary Tree)とは同じ働きをします。それから、Quickソートは、再帰があるから、思ったほどに速くはないはずです。ワークシートのコマンドを利用したほうが速そうです。

>配列をコレクションに変更し、１個処理が終わる度にコレクションの要素から削除していけば、
コレクションというのは、入れ物が大きいオブジェクトですから、数が増えれば、その分重くなるはずです。オブジェクト群を直接操作するなら可能ですが、そうでないと使う意味はあまりありません。

なお、
sName="*" & Cells(i, 4) & ".txt" 'Name -> sName :プロパティ名を直接変数にしてはいけません。
>If Name Like Files(j) then
Filter(VBA関数, 戻り値が、見つかららない場合は、-1 になる)
Application.Match (ワークシート関数:Application.からダイレクトなのは、エラー値を排除するため。IsError や Vartype : Long で戻り値の有効性を調べる)

お礼

ありがとうございます。
Matchと他回答のVlookupとあるようですね。両方試してみます。

補足

体感速くなりました。1個0.6秒程度。後はファイル開閉かも知れません。

回答No.6

山田 太郎（@f_a_007）

×　配列に入れたファイル名をセルに入れて・・・・
〇　ファイル名を格納した配列をソートし・・・・

処理の準備１・・・Excelのセルを参照し配列準備
　　(1)商品名も配列に。
　　(2)ファイル名の一部にある商品名を配列に。
　　(3)ファイル名も配列に。
処理の準備２・・・配列に読み込んだデータをソート
処理の準備３・・・その他の準備

本処理

GoodsCounts = UBound(strGoodsNames())
FileCounts = UBound(strFileNames())
For I=1 TO GoodsCounts
　'
' I番目の商品名の処理
'
strGoodsName=strGoodsNames(I)
'
' 商品名に対応するファイル名を検索
'
　’-----------------------
' バイナリーサーチ
　' -----------------------
　　　' P=検索開始位置
　　　' S=二分木探索変数XXX
　　　' L=二分木探索変数XXX
　　　' M=二分木探索変数XXX
　　　'
　　　・・・・・　　
　　　・・・・・　　
　’-----------------------
' ファイル処理
　' -----------------------
　　　・・・・・　　
　　　・・・・・　　
Next I

つまり、EXCEL のセルを参照し配列に読み込むのは準備工程で一回だけです。その後のソートや検索もメモリ上に行います。双方共にソートしておけば、バイナリーサーチの検索ポイントを操作することが可能です。多分、一回の検索に要する時間は１万分の１秒以下だと思いますよ。なお、ファイル処理を上記For-Next文で行うのか、それとも全てを検索後に一括して行うか？これは、後者がお勧めです。まずは、ファイル処理を一括して行うに十分なデータをセルに書き出すか、シーケンシャルファイルとして吐き出すかして成功・不成功を確認。そういうプログラムの単純化がお勧めです。

お礼

ありがとうございます。
バイナリサーチという機能があるのかと思いましたが、二分木検索の事を指すようですね。

回答No.5

nishi6

keyを指定したCollectionオブジェクトを作れば、
　「For j=1 to Ubound(Files)
　　　If Name Like Files(j) then

の判定がなくなるでしょう。当然、Loopやクイックソートやバイナリーサーチも不要でしょう。Likeの意味がよく分かりませんが、Itemに重複があるということでしょうか。

また、
「For i=2 to Cells(Rows.Count, 1).End(xlUp).Row」
については、セル範囲をVariant型の変数に一括代入すれば、以降はメモリでの処理になり、シートとのI/Oが減ることでこちらは速くなると期待されます。（数十万件の処理で劇的に速くなったこともあります）

Collectionにすると、処理速度は若干遅くなるはずで、Variant型の変数との比較はやってみないと分からないと思います。

＞配列をコレクションに変更し、１個処理が終わる度にコレクションの要素から削除していけば、探す時間は短縮されると思ったのですが

よく試すことがありますが、処理が先頭から順番に行われる場合、逆に末尾から行われる場合。末尾から行われる場合は削除して意味がある？　逆に先頭からの場合は削除にコストがかからないか考える必要があるでしょう。実際のデータを見ないと何とも言えませんね。感覚的にはコレクションならほとんど差がないのではと思います。

実は、質問にある「コレクション」の意味が、「Collectionオブジェクト」なのか「Dictionaryコレクション」なのか判断付きませんでした。個人的には「Dictionaryコレクション」を使っています。当然、ソート不要で削除も可能です（処理効果は分かりませんが）。実際、行ってみるのが一番です。ご参考に。

お礼

ありがとうございます。
Likeを使う理由はファイル名がまちまちで、商品名を含むのは確実ですが他の部分が一定してないからです。likeならワイルドカードを使えるからです。重複はありません。
コレクションは過去使ったことが無いので、おそらくオブジェクトだと思います。

まずは商品名の配列への代入を試してみます。そういえば一括で変数代入出来たような記憶があります。

回答No.4

keithin

Ｄ列に商品一覧を準備
Ｆ列にファイル名一覧を（マクロで？その必要があるのかは？）準備

sub macro1()
　dim i as long
　dim myFile as string

　range("F:F").sort key1:=range("F1"), order1:=xlascending, header:=xlno
　for i = 2 to range("A65536").end(xlup).row
　　myfile = application.vlookup("*" & cells(i, "D") & ".txt", range("F:F"),1)
　’ファイル開き処理
　next i
end sub

まぁ騙されたと思って試してみて下さい。
現実にはブックをイチイチ開く、閉じるの方が遥かにボトルネックになりそうですが。

＃あぁ、あと実際の具体的なデータの中身とかナイショなので、カンジだけでこのマクロも書いてるので悪しからず。

お礼

ありがとうございます。
ファイル名をソートしてVlookupするのが肝ですね。エクセル関数をVBA上で使用出来るのは初めて知りました。

回答No.3

山田 太郎（@f_a_007）

訂正：

×バイナリソートで探索。
〇バイナリサーチ（二分木探索）で探索。

回答No.2

maiko0318

速くなりますが、もっと早い方法があります。
要素を商品名順にソートしておきます。

であれば、２分木探索が可能です。
＜２分木探索＞
１万個の真ん中（５０００番）と比較。
大きければ前半の前半（２５００番）と比較。
・・・と順にやっていけばめっちゃ速くなります。

プログラムは少し複雑になります。
解説本も売られているかと思います。

お礼

ありがとうございます。
2分木探索、原理は分かりました。ただ、毎回作ってたら時間が掛かりそうなのでサブプロシージャに分けられたら使えそうですね。

回答No.1

山田 太郎（@f_a_007）

Q、速くなるでしょうか？
A、速くなると思いますが・・・。

劇的とは言えないと思います。
劇的な改善を実現するには・・・。

１、事前に配列に呼び込む。
２、クイックソートで昇順に並び変える。
３、その後、バイナリソートで探索。

この場合、相当な高速探索ですので削除する必要はありません。

お礼

ありがとうございます。
配列に入れたファイル名をセルに入れて、昇順にソート、クイックサーチ、でいいのでしょうか？調べてみます。