[[20100212181353]]　『５０万件のエクセルデータ』（mito）　ページの最後に飛ぶ

『５０万件のエクセルデータ』（mito）

エクセル２００７で５０万件くらいのデータで、
ＶＬＯＯＫＵＰで他のエクセルファイルから
データを読み込むとかなり時間がかかりますが、
素早く出来る方法はないでしょうか。
＊アクセスは全くの初心者です

 >ＶＬＯＯＫＵＰで他のエクセルファイルからデータを読み込むとかなり時間がかかりますが、

 他のエクセルファイルのデータが昇順に並べてあれば（または、並べても良ければ）、
 ここで、サジェスチョンがあります。
 　↓
 Excel 2007 におけるパフォーマンスの改善 
http://msdn.microsoft.com/ja-jp/library/aa730921.aspx

 <以下抜粋>

 >処理が遅くなるため、並べ替えられていないデータを対象に検索を実行することは、できるだけ避けてください。
 　；　　　　；
 >複数行にわたる範囲を検索する場合、完全一致検索 1 つを実行するよりも近似値を含めた検索
 >2 つを実行する方がはるかに高速に処理されます
 　:　　　　　　 　；
 >並べ替えられたデータを対象に、完全一致の検索を実行したい場合は、・・この式で（←勝手に半平太が挿入）・・

 >IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, VLOOKUP(lookup_val, lookup_array, colnum, True), "notexist")

 ※ どれくらい改善するのか私は分かりません。
 ※ あとは、VBAでなんとかするかですが、これもどれくらい速くなるか分かりません。
 　 腕自慢の人が、何とかしてくれるかも知れません。
 ※ PCのスペックも書いておいた方がいいと思います。
 ※ 今のVLOOKUPを使った数式も、ここに掲示すると協力者が増えると思います。

 (半平太) 2010/02/12 18:59

 ５０万件？　迷わず、アクセス又は、あるいはそれ以上のデータベースソフトへの移行を
 考えた方がよいです。私なら、必ずそうしますし、データベースソフト初心者でもこっちを
 検討した方がよいですよ！！

 Excel2007は、持っていませんが、クエリあたりでどの程度の処理時間になるんでしょうかねえ！！
 これの興味はありますけど・・・。

 ichinose

 質問と関係ありませんが、おもしろそうなので試してみました。

 A列にキー(１～5000000)、B列にデータを50万件入力したファイルで、
 C1:C100 に =RANDBETWEEN(1,5000000)
 D1:D100 に =VLOOKUP(C1,A:B,2)
 としてみました。
 意外や1秒以内で結果が出ました[＠Core2Duo 1.8GHz]

 C1:C1000 に拡張すると 7 秒くらいでした。
 もっともこれは、乱数のほうの影響かもしれません。

 EXCEL2007、使いようによっては案外大きいデータでも使えるかも。
 (Mook)

　50万件だとして

 Vlookup関数が最後の引数がFalseの場合、逐次検索ロジックだとすると、
 平均検索回数は、（50万+1）/2＝250000.5
 整列されていた場合、おそらくバイナリーサーチのロジックを使っていると思います。
 この場合の平均検索回数は、Lon2（50万）＝18.93　なるらしいですが・・・。

 >意外や1秒以内で結果が出ました
 へえ、速いですね、でも、他にもいろんな検索をする可能性があるならば、本物のデータベースの方が
 よさそうですけどねえ、Excel2007を使ってないですから、Excel2007の実力を他にあったら見せてください。

 ichinose

 同じ様なテストをしてみました。

 上のサイトで紹介されているマクロで時間を計測してみました。
 （手動計算モードにして、シート再計算に要するタイムの計測）

 使った数式は、
 　　　　↓
  =IF(VLOOKUP( A2,[氏名5.xlsx]氏名!$A:$A,1,TRUE)=A2, VLOOKUP(A2, [氏名5.xlsx]氏名!$A:$B, 2, TRUE), "notexist")

 500,000件を相手に、1000件検索で、平均 0.02秒以下でした。

 ちょっと、速すぎの感もあります。(^^ゞ

 （10回ループさせて平均を取り、さらにそれを5回テストしているので、）
 （都合50回の計測ですから、間違ってはいないと思いますが、余り自信はないです）

 データがきれいに並び過ぎているせいもあるかも知れません。
 途中に空白セルがあったりすると、そうはいかないかもです。

 ついでに、FALSE型で、中間の250,000行目がヒットする検索値にしたら、30秒掛りました。
 使った数式は、
 　　　　↓
  B2セル =IFERROR(VLOOKUP(A2, [氏名5.xlsx]氏名!$A:$B, 2, FALSE), "notexist")

 しかし、色々やっていたら、フリーズされてしまった。
 ファイルのサイズは8MBを超えていますから、計算が速いとしても、使いにくいことは確かです。

 (半平太) 2010/02/12 21:36

コメント返信：

[ 一覧(最新更新順) ]

[[20100212181353]] 『５０万件のエクセルデータ』（mito） ページの最後に飛ぶ

[[20100212181353]]　『５０万件のエクセルデータ』（mito）　ページの最後に飛ぶ