エクセルで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列
項目1_____ _____ _____ _____ 保存場所
#EXTINF:213,08_____ #suzuki I:\music\suzuki
#EXTINF:223,08_____ #satou U:\music\satou
#EXTINF:223,121____#kondou G:\music\kondou
No.6ベストアンサー
- 回答日時:
ファイルサイズだけが計算の重さを表すものではありません
1. Sheet2の「項目1」を昇順に並び替える
2. Sheet2の検索範囲は必要最低限とする
3. Sheet2の「項目1」に重複はない
以上が当てはまること
何列もVLOOKUP関数を使い同じ範囲を検索している
場合も改善の余地があります。
1. 名前の定義
項目1=$B$8:$B$10
保存場所=$C$8:$C$10
とします
2. 作業列 D2セルに =MATCH(B2,項目1)
2分探索による検索の高速化 および位置の特定
3. 保存場所 E2セル
=IF(INDEX(項目1,$D2)=$B2,INDEX(保存場所,$D2),"")
検索結果の検証と結果の表示
※「#」記号に関しては無視しています
皆様ありがとうございます。
お礼が遅くなり申し訳ありません。
今回はCoalTar様に教えていただいた内容にて、
別シートで処理をして、値のみを元のシートに貼り付けるようにしました。
処理がかなり早くなりました。
ありがとうございます。
No.8
- 回答日時:
どうだろう?
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
- 回答日時:
私も同じようにExcel(但し、マクロ)の処理が重く悩んでいました。
PCは P4 3.0 メモリー 1MB
たびたびフリーズという状況でした。
マザーボードがDualCoreに対応していたので DualCore2.4G(E2222)に乗せ換え、もう一台はMB,CPU3.0G(E8400) メモリー4MBに交換して同じファイルを走らせていますが快適に動いております。
唯どちらもOSの再インストールをしましたので時間がかかりました、このようなことを想定してVISTAは使ってません。
この環境で1列から最終列のデータを全て参照して一致するデータを抽出するマクロを走らせましたがかなりの時間がかかりました、関数をシートに貼り付けた場合データがあれば常時計算をするのでフリーズということは処理能力を超えていると思います。
40万件のデータ処理はEXCLEにはそのままでは不向で、データベース系のソフトが適していると思います。
マクロで処理を工夫するか、シート類を分散して複数処理を考えるか、適切な助言になりませんが自分の経験から書き込んでみました。
No.5
- 回答日時:
4万件のデータを一元管理するのではなく分割する事はできませんか?
規則性が無いものであれば難しいかもしれませんが
項目名が
AAA123,45などの場合
シート名AAAのシートに123と名前付けた範囲から45を検索地としてデータを抽出するとかに変更すると処理はだいぶ軽くなるかと思います。
No.4
- 回答日時:
最初の質問内容であれば、No1 さんが回答していますが そんなに問題ないと思いますよ
シート2のA列を昇順に並べ替えできる場合は 並べ替えを行って
=VLOOKUP(B2,sheet2!$A$2:$B$40000,2)
VLOOKUP関数の第4引数を省略
VLOOKUP関数の第2引数の範囲を必要最小限にの範囲にする
などで、少しは解消できるかな
補足説明より
>最初に書かせて頂きました、Sheet1と同様のシートが、
>同エクセルファイル内に、数シートあり、各シートあわせて、
>合計で20万件ほどのデータになってしまいます。
実際の式はどの様になっているのでしょう
実際の式が分かりませんので、回答しづらいですね
因みに、
シート2のB列 のデータには#がついていますが
シート1のC列 のデータには#がついていませんね
どの様な処理をしているのでしょう
No.3
- 回答日時:
補足ありがとうございます。
スペックに関しては、判断致しかねます。
ちなみに、私はセレロン3.2Ghz 1024MBで、動作は快適とは言えません。
EXCELは計算式が入ると、かなりの負荷が掛かります。これは私も何度も経験しているので、「二次加工で必要なデータ」ならば一次の時は削除するようにしました。
一度ですべてを終わらせるのが楽なのは承知していますが、負荷が掛かりすぎているみたいですね。
画面が白くなったりと。
技術的なところでは、ハードウェアアクセラレータを下げる。
画面のプロパティの詳細設定から二段階ぐらい下げてください。
ディスプレイに使う能力をソフトに廻すということです。
胃が弱ったら肝臓が頑張って直すのと似ています。
式の作り方ではなくて、認識の方法ですね。
20万件もあれば、数式の列にもよりますが、よほどの処理能力がないと難しいです。
考え方としては、「一次加工完成」シートを作成。
抽出したものを、別シートで再計算する方式が良いと思います。
それでもダメなら三分割。
あとは、HDD容量。
Dが空いていたら、Dに全部移してDだけで行うとか。
OSのアップグレードは他の障害が発生する確率もアリ、またvistaは最低2GBのメモリが要るので推奨できません。
周辺機器、USBの余計なものなど外して、PCをシンプルに。
スタンドアロンで動作するならネットワークからも外れる。
そして、キャッシュも削除、履歴も削除。
すべて軽くして、初期化状態で一度動作してみてはどうでしょうか?
1枚のシートが20MB越えたら、動作は結構厳しいです。
私のシートは50MBでしたが、何とか動作しました。
あとは、コミットチャージの量。
万全を期するために、色々下処理して、再起動して見てそれだけを動かしてみてはどうですか?
これ以上は私は思いつくことがありません。
No.2
- 回答日時:
重くてデータ入力が進まない、という悩みでしたら
「ツール」-「オプション」-「計算方法」で
「手動」を選択すれば、カーソル移動して値入力することは
軽くなるかも知れません。
ただし当然その場合、
入力した値にデータを反映させるのは手動になり、
ある程度データを入れた後でまとめてF9キーを押して
計算実行するという手順になります。
計算結果示までを早くしたいということであれば、
メモリを買い足すなどのスペックアップを行うか
Accessなどのデータベースソフトの導入を検討した方がいいかもしれません。
No.1
- 回答日時:
EXCELの問題ではなく、OSやスペックの問題です。
また、ローカルにあるデータと、共有ネットワーク上にあるデータで処理速度も違います。
環境も、EXCELのバージョンも書かれていません。
vlookupで5万行ぐらいのデータを処理しましたが、なんら問題なく動作しますよ。
確かに、検索している時間は長いですが。
メモリの増設と、無駄なメモリ消費を停めることです。
壁紙、自動電源、その他常駐ソフト。
業務重視ならセーフモードで作業するのも一つの方法です。
Kickknock様、ご訓示有難う御座います。
環境ですが、
【PC】
OS:Windows XP
CPU:Pentium® Dual-Core (1.80GHz)
メモリ:1024MB
【Excel】
Microsoft Office Excel 2003
本日再度、Vlookupを試したところ、フリーズではなく
作業はおこなっているが、とても作業時間がかかっているようです。
最初に書かせて頂きました、Sheet1と同様のシートが、
同エクセルファイル内に、数シートあり、各シートあわせて、
合計で20万件ほどのデータになってしまいます。
どのようにかして、作業を効率化する方法はないでしょうか。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) マクロを簡潔にしたい 6 2022/09/16 10:37
- Excel(エクセル) Excelで、別シートの表のステータスに伴った動的な自動転記をしたいです。 2 2023/06/14 15:56
- Excel(エクセル) エクセル 指定セル繰り返しマクロ 4 2022/06/06 17:08
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報