巨大Excelデータの処理方法について

質問

ここのおかげで、無事研究に利用できるようにVLOOKUP処理を行えたのですが、
今度はそれが理由で、再計算したら処理が「１０％」で常時フリーズするようになってしまいました。

元々データ数が５万件あり、列の数も１００件近くあるので
データは200MB近くあるのですが、XPのＰＣを利用してもまともに
動かなくなりました。（再計算したときのフリーズはもちろん、なぜかスタンドアロンなのに、読み取りロックがかかったり、保存そのものができなくなったりします）

ここを見ていて「直接使わないVLOOKUP」の箇所を削除したりして少しずつデータを軽くしているのですが、それでも100MB以上あります。
やはりアクセスに切り替えたほうがいいのでしょうか？
これだけ項目数（縦、横とも）があると、アクセスでは無理でしょうか？

回答No.3

noname#95859 · ベストアンサー

参考です。
小生、業務で、Excel,Accessともに使っています。Access、Excelそれぞれに得意なものがあと思います。Accessは、やはりデータベースですから、そのデータ量（と言っても２Gまでですが）、それと、Excelでいうところの
Vlookup機能相当のものがありがたい。２つのテーブルで、仕事テーブルに担当者名、他方には、担当者名、および属性（例：住所等）があった場合、仕事テーブルに属性情報をもってくるのは、いとも簡単です。これは、リレーショナルデータベースの機能そのものです。
Accessでは、リレーションを組んで処理し、結果をExcelにexportし、グラフ化しています。グラフ化は、やはりExcelです。
貴兄の場合、膨大なデータですので、シート関数を使うことは、やめた方が良いと考えます。ExcelのVBAを使ってやるの方法かと思います。例えば、
dim myArray as variant
myArray=range("A1:z60000").value
で、メモリ上にセルに対応したエリヤを確保できます。値は、セルに入っている値です。メモリ上の2つのエリヤ間で、For loopで繰り返し、vlookup相当を実施すれば、処理時間は、あまり気にならないのではないかと思います。Variantのこの使い方は、結構使えます。
ただし、「データ数が５万件あり、列の数も１００件」ものデータをVariantに持ってきたことはありません。
まとめとして、
（０）大量のデータに対しては、シート関数はやめた方が良い。
（１）VBAを使って、Variantの活用を検討ください。
（２）Accessのリレーションの活用を検討ください。

補足

ありがとうございます。やはりアクセスの方が良いのですね。
自分もＶＢＡは今項目の入力を手抜きするためだけに使っています。

アクセスのリレーション機能の利用を検討してみます。
これから本を買いに行ってこようと思います。
データの移行には時間がかかるのでしょうか？

回答No.4

noname#95859

ANo.3への補足です。
Accessへの移行ですが、データベースの定義が確かに大切です。しかし、実戦的には、EXCELファイルをインポートしてテーブルを作成した方が
早いです。200MBもあると、若干時間は掛かると思いますが、5分もいらないと思います。
ただ、気をつけなければいけないことは、2行目に、きちんとした本来の値が入っていないとまずい（経験的に）。
と言うのは、1行目は、ヘッダーとして取り扱うようにチェックマークを入れる。2行目からデータとしてAccessに認識させる。本来「数値」であるところに、データがないから、或いは覚えのために数値以外「NULL」みたいな
ストリングが入っていると、ACCESSは、この列は「TEXT」なんだと認識してしまい、3行目以降のきちんとしたデータをすべてエラー扱いにしてしまいます。もっとも、インポートのウィザードの何ページ目かで、列の定義を変更できるようになっていますので、致命的ではないですが。
こうして、入力したテーブルを最初のクエリで、データの列を全部持ってきて、すべての列に対し「GroupBy」をすることで、重複削除を施し、2番目のクエリで、レファレンステーブルとのリレーションをとると言うようにしています。100列もあると、時間は掛かるかも知れないです。

お礼

重ねてありがとうございます。本を買って試してみようと思います。

回答No.2

mshr1962

Accessだとテーブルのルックアップ機能か
フォームでのコンボボックスになりますね。
定期的に最適化かければ大丈夫とは思いますけど．．．
一応限界サイズは全体で2Gバイト、1テーブルの限界が1Gバイトになってます。
あとデータベースが壊れたときのことを考えて定期的にバックアップしたほうが良いです。

お礼

ありがとうございます。
意外と容量は大きくてもＯＫなんですね。

回答No.1

Nouble

VLOOKUP関数には他のそれにはない
「閉じているブックに対しても正常に値を参照する」という機能が備わっています

確証を持って言えるわけではないのですが
この機能の分なのでしょうか？
VLOOKUP関数は他のそれに比べ処理が思いように見受けられます
なので他の関数の組み合わせを用いたり
VBAにシフトするなどの対策を講じると
高速化が図れる可能性があります。

また、
処理内容が定かでないので此もはっきりと言えないのですが
「多数数値の演算の相互干渉の中から情報を取り出す」
的なことをもしされているならば、
エクセルより数式処理ソフト系の方が適しているやも知れませんので
此処の過去ログをお調べ頂ければ
私の過去の発言でこの内容に触れたものがありますので
ご参考になるかと存じます。

一方で、
データーベースとしてエクセルをお使いの場合は
やはり仰る通りアクセスへ切り替えた方が良いと言えそうです。

その差は歴然のようなので

如何でしょうか？
お役にたてたなら幸いです。

お礼

ありがとうございます。
処理は統計データですので、抽出処理ばかりです。
やはりアクセスか・・・。