エクセルでVLOOKUP関数を使用すると、データ数が多くてフリーズしてしまいます。

質問

エクセルでVLOOKUP関数を使用すると、データ数が多いくてフリーズしてしまいます。どうにかして解決できないでしょうか。

エクセルでVLOOKUP関数をしようして、データ数が多いとフリーズしてしまうのですが、何か解決方法ありますでしょうか。

エクセルでVlookUpを使い、Sheet1にsheet2から一致したデータを読み込もうと思うのですが、
データが重くなりすぎて、PCがフリーズしてしまいます。
どうにかして、簡単にVlookupと同じように一致したデータを抽出することはできないでしょうか。

sheet1では
B列を入力して、C列を抽出したいです。

A列_____ ___B列_____ _____ _____ _____ ___C列
No_____ 入力項目_____ _____ _____ _____ 保存場所
1_____ #EXTINF:213,08___________ suzuki I:\music\suzuki
2_____ #EXTINF:223,08___________ satou U:\music\satou
2_____ #EXTINF:223,121__________kondou G:\music\kondou
↑
=VLOOKUP(B2,sheet2!$A$2:$B$40000,2,FALSE)
=VLOOKUP(B3,sheet2!$A$2:$B$40000,2,FALSE)
=VLOOKUP(B4,sheet2!$A$2:$B$40000,2,FALSE)

sheet2のデータは
A列_____ _____ _____ _____ ____B列
項目１_____ _____ _____ _____ 保存場所
#EXTINF:213,08_____ #suzuki I:\music\suzuki
#EXTINF:223,08_____ #satou U:\music\satou
#EXTINF:223,121____#kondou G:\music\kondou

回答No.6

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

ファイルサイズだけが計算の重さを表すものではありません

1. Sheet2の「項目１」を昇順に並び替える
2. Sheet2の検索範囲は必要最低限とする
3. Sheet2の「項目１」に重複はない
以上が当てはまること
何列もVLOOKUP関数を使い同じ範囲を検索している
場合も改善の余地があります。

1. 名前の定義
　項目１ =$B$8:$B$10
　保存場所 =$C$8:$C$10
　とします
2. 作業列 D2セルに =MATCH(B2,項目１)
　２分探索による検索の高速化 および位置の特定
3. 保存場所 E2セル
　=IF(INDEX(項目１,$D2)=$B2,INDEX(保存場所,$D2),"")
　検索結果の検証と結果の表示
　※「#」記号に関しては無視しています

お礼

皆様ありがとうございます。
お礼が遅くなり申し訳ありません。

今回はCoalTar様に教えていただいた内容にて、
別シートで処理をして、値のみを元のシートに貼り付けるようにしました。

処理がかなり早くなりました。

ありがとうございます。

回答No.8

hige_082

どうだろう？

Sub test()
Dim aaa As Variant, bbb As Variant, ccc As Variant
Dim i As Long, ii As Long
With Worksheets("sheet2")
aaa = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 2).End(xlUp).Row, 2))
End With
With Worksheets("sheet1")
bbb = .Range(.Cells(2, 2), .Cells(.Cells(Rows.Count, 2).End(xlUp).Row, 2))
End With
ReDim ccc(1 To UBound(bbb), 1 To 1)
For i = 1 To UBound(bbb, 1)
For ii = 1 To UBound(aaa, 1)
If aaa(ii, 1) = bbb(i, 1) Then
ccc(i, 1) = aaa(ii, 2)
Exit For
End If
Next ii
Next i
With Worksheets("sheet1")
.Range(Cells(2, 3), Cells(1 + UBound(bbb, 1), 3)) = ccc
End With
End Sub

コピーしたファイルで試してみて

回答No.7

374649

私も同じようにExcel（但し、マクロ）の処理が重く悩んでいました。
PCは P4 3.0 メモリー　1MB
たびたびフリーズという状況でした。

マザーボードがDualCoreに対応していたので　DualCore2.4G(E2222)に乗せ換え、もう一台はMB,CPU3.0G(E8400)　メモリー4MBに交換して同じファイルを走らせていますが快適に動いております。

唯どちらもOSの再インストールをしましたので時間がかかりました、このようなことを想定してVISTAは使ってません。

この環境で１列から最終列のデータを全て参照して一致するデータを抽出するマクロを走らせましたがかなりの時間がかかりました、関数をシートに貼り付けた場合データがあれば常時計算をするのでフリーズということは処理能力を超えていると思います。

４０万件のデータ処理はEXCLEにはそのままでは不向で、データベース系のソフトが適していると思います。

マクロで処理を工夫するか、シート類を分散して複数処理を考えるか、適切な助言になりませんが自分の経験から書き込んでみました。

回答No.5

web2525

4万件のデータを一元管理するのではなく分割する事はできませんか?
規則性が無いものであれば難しいかもしれませんが

項目名が
AAA123,45などの場合
シート名AAAのシートに123と名前付けた範囲から45を検索地としてデータを抽出するとかに変更すると処理はだいぶ軽くなるかと思います。

回答No.4

sige1701

最初の質問内容であれば、No1　さんが回答していますが そんなに問題ないと思いますよ
シート2のA列を昇順に並べ替えできる場合は　並べ替えを行って
=VLOOKUP(B2,sheet2!$A$2:$B$40000,2)
VLOOKUP関数の第4引数を省略

VLOOKUP関数の第2引数の範囲を必要最小限にの範囲にする

などで、少しは解消できるかな

補足説明より
>最初に書かせて頂きました、Sheet１と同様のシートが、
>同エクセルファイル内に、数シートあり、各シートあわせて、
>合計で20万件ほどのデータになってしまいます。
実際の式はどの様になっているのでしょう

実際の式が分かりませんので、回答しづらいですね

因みに、
シート2のB列 のデータには#がついていますが
シート1のC列 のデータには#がついていませんね
どの様な処理をしているのでしょう

回答No.3

kickknock

補足ありがとうございます。
スペックに関しては、判断致しかねます。　
ちなみに、私はセレロン3.2Ghz　1024MBで、動作は快適とは言えません。

EXCELは計算式が入ると、かなりの負荷が掛かります。これは私も何度も経験しているので、「二次加工で必要なデータ」ならば一次の時は削除するようにしました。

一度ですべてを終わらせるのが楽なのは承知していますが、負荷が掛かりすぎているみたいですね。

画面が白くなったりと。

技術的なところでは、ハードウェアアクセラレータを下げる。
画面のプロパティの詳細設定から二段階ぐらい下げてください。

ディスプレイに使う能力をソフトに廻すということです。
胃が弱ったら肝臓が頑張って直すのと似ています。

式の作り方ではなくて、認識の方法ですね。

20万件もあれば、数式の列にもよりますが、よほどの処理能力がないと難しいです。

考え方としては、「一次加工完成」シートを作成。

抽出したものを、別シートで再計算する方式が良いと思います。
それでもダメなら三分割。

あとは、HDD容量。
Dが空いていたら、Dに全部移してDだけで行うとか。

OSのアップグレードは他の障害が発生する確率もアリ、またvistaは最低2GBのメモリが要るので推奨できません。

周辺機器、USBの余計なものなど外して、PCをシンプルに。
スタンドアロンで動作するならネットワークからも外れる。

そして、キャッシュも削除、履歴も削除。
すべて軽くして、初期化状態で一度動作してみてはどうでしょうか？

1枚のシートが20MB越えたら、動作は結構厳しいです。
私のシートは50MBでしたが、何とか動作しました。

あとは、コミットチャージの量。

万全を期するために、色々下処理して、再起動して見てそれだけを動かしてみてはどうですか？

これ以上は私は思いつくことがありません。

回答No.2

mako_sea

重くてデータ入力が進まない、という悩みでしたら
「ツール」－「オプション」－「計算方法」で
「手動」を選択すれば、カーソル移動して値入力することは
軽くなるかも知れません。
ただし当然その場合、
入力した値にデータを反映させるのは手動になり、
ある程度データを入れた後でまとめてF9キーを押して
計算実行するという手順になります。

計算結果示までを早くしたいということであれば、
メモリを買い足すなどのスペックアップを行うか
Accessなどのデータベースソフトの導入を検討した方がいいかもしれません。

回答No.1

kickknock

EXCELの問題ではなく、OSやスペックの問題です。
また、ローカルにあるデータと、共有ネットワーク上にあるデータで処理速度も違います。

環境も、EXCELのバージョンも書かれていません。
vlookupで5万行ぐらいのデータを処理しましたが、なんら問題なく動作しますよ。

確かに、検索している時間は長いですが。

メモリの増設と、無駄なメモリ消費を停めることです。
壁紙、自動電源、その他常駐ソフト。

業務重視ならセーフモードで作業するのも一つの方法です。

お礼

Kickknock様、ご訓示有難う御座います。

環境ですが、
【PC】
OS:Windows XP
CPU:Pentium® Dual-Core (1.80GHz)
メモリ：1024MB

【Excel】
Microsoft Office Excel 2003

本日再度、Vlookupを試したところ、フリーズではなく
作業はおこなっているが、とても作業時間がかかっているようです。

最初に書かせて頂きました、Sheet１と同様のシートが、
同エクセルファイル内に、数シートあり、各シートあわせて、
合計で20万件ほどのデータになってしまいます。

どのようにかして、作業を効率化する方法はないでしょうか。

よろしくお願いします。