Excelで数十万件の表から、XLOOKUPやVLOOKUPを使って必要な情報を取り出すことがあります。検索するキーが何十件、何百件と増えると、計算自体は終わっても、保存したところでExcelが固まったように動かなくなる場合があります。
最近のパソコンは以前より処理性能が上がり、少し大きな表を扱っただけで固まることは少なくなりました。それでも、数十万件のデータを何度も検索する処理では、パソコンの性能だけでは吸収しきれない負荷がかかることがあります。
先に結論をお伝えすると、検索結果を今後更新する必要がない場合は、XLOOKUPやVLOOKUPの計算結果を「値」に置き換えてから保存する方法が有効です。
ただし、バックアップを作るタイミングには注意が必要です。大量の数式を入れた後でバックアップ保存をすると、その保存処理でも固まる可能性があります。元ファイルの複製は、数式を入れる前に行いましょう。
1. なぜ計算できたのに保存で固まるのか
（1）検索回数が増えると処理量も増える
たとえば、30万件の商品マスタから200個の商品コードを探すとします。
XLOOKUPやVLOOKUPの完全一致検索では、検索する値ごとに大きな範囲を調べます。数式を1つ入力したときは問題がなくても、何十件、何百件とコピーすると、全体の処理量は大きくなります。
最近のExcelでは検索処理の改善も進んでいますが、検索元と検索するキーの両方が多いと、処理量そのものは小さくありません。パソコンが新しいから必ず大丈夫とは言い切れない部分です。
XLOOKUPの基本的な書き方を確認したい場合は、「XLOOKUP関数」で検索をもっと楽に、正確に！も参考にしてください。
（2）保存時に再計算されることがある
Excelは数式の計算順序や参照関係を管理しています。計算方法の設定によっては、ブックを保存するときにも数式の再計算が行われます。
そのため、画面上では検索結果が表示されていても、保存をきっかけに大きな計算処理が動き、Excelが応答しなくなったように見えることがあります。
ただし、保存時に固まる原因が必ず再計算とは限りません。ファイル容量、余分な書式、ネットワーク上の保存先、外部リンク、ファイルの破損などが影響する場合もあります。
2. 一番確実な対処は、計算結果を値にすること
（1）値にすると検索の数式がなくなる
XLOOKUPやVLOOKUPで必要な結果を取得し、その結果が変わる必要がないのであれば、数式を残しておく必要はありません。
数式を値に置き換えると、セルには商品名や金額などの結果だけが残ります。検索数式がなくなるため、その部分は再計算されません。
Microsoftも、多数または複雑な数式を含むブックでは、数式を計算結果に置き換えて静的なデータにすることがパフォーマンス改善に役立つと案内しています。
（2）値貼り付けの操作手順
- XLOOKUPまたはVLOOKUPの結果が正しいことを確認します。
- 数式が入っている結果範囲を選択してコピーします。
- 選択範囲を変えずに、「ホーム」タブの「貼り付け」から「値」を選びます。
- セルを選択し、数式バーに
=から始まる数式ではなく、結果の文字や数字が表示されることを確認します。 - 値に変わったことを確認してから保存します。
「値貼り付け」の画面やほかの使い方は、「形式を選択して貼り付け」でExcel作業を時短しよう！で詳しく紹介しています。
3. バックアップは数式を入れる前に作る
（1）安全な作業の順番
大量の数式を入力した後に「名前を付けて保存」でバックアップを作ろうとすると、その保存処理で固まる可能性があります。
安全なのは、元のファイルを閉じた状態で複製し、その複製を作業用ファイルとして使う方法です。
- 数式を入れる前の元ファイルを閉じます。
- エクスプローラーやFinderで元ファイルを複製します。
- 複製した作業用ファイルを開きます。
- 数件だけ数式を入れ、検索結果が正しいか確認します。
- 必要な範囲まで数式をコピーして計算します。
- 結果を確認し、保存する前に値へ置き換えます。
- 値に変わった作業用ファイルを保存します。
これなら、作業用ファイルに問題が起きても、数式を入れる前の元ファイルは残ります。
（2）数式そのものも残したい場合
次回も同じ処理をする場合は、大量にコピーした数式をすべて残すのではなく、見本となる数式を1セルだけ別シートやメモに保存する方法があります。
また、繰り返し使う処理では、次のようにファイルの役割を分けると管理しやすくなります。
- 処理用ファイル：数式やPower Queryを残す
- 提出・保管用ファイル：計算結果を値にして保存する
検索に使うマスタを長く運用する場合は、メンテナンスしやすいマスタデータの作り方もあわせて確認しておくと、範囲ずれや更新ミスを防ぎやすくなります。
4. すでに大量の数式を入れてしまった場合
（1）まだExcelが操作できる場合
計算結果が表示され、セルを操作できる状態なら、保存より先に結果範囲をコピーして値貼り付けします。その後、数式が値に変わったことを確認して保存します。
数式を後で再利用したい場合は、代表となる1セルの数式だけをメモ帳などへコピーしておきます。大量の数式を残すために別名保存すると、そこで再び固まる可能性があります。
（2）「応答なし」になったら、まず少し待つ
「応答なし」と表示されても、Excelが計算や保存を続けている場合があります。すぐに何度もクリックせず、パソコンの動作やExcelの状態を少し待って確認します。
特に、保存を開始した直後は処理中の可能性があります。強制終了すると保存中のファイルや未保存の変更を失うおそれがあるため、すぐに終了しないようにします。
しばらく待っても画面が変わらず、セルの選択やメニュー操作もできない場合は、最後の手段としてExcelを強制終了します。
（3）WindowsでタスクマネージャーからExcelを終了する
Windowsでは、動かなくなったアプリを終了するために「タスクマネージャー」を使います。
- キーボードの
Ctrl、Shift、Escを同時に押します。 - 「タスクマネージャー」が開いたら、「プロセス」の一覧を表示します。
- 一覧から「Microsoft Excel」を探して選択します。
- 「タスクの終了」または「タスクを終了する」をクリックします。
- Excelの画面が閉じたことを確認し、タスクマネージャーを閉じます。
Ctrl＋Shift＋Escで開かない場合は、Ctrl＋Alt＋Deleteを同時に押し、表示された画面から「タスクマネージャー」を選びます。
注意：タスクを終了すると、開いているExcelの未保存データは失われる可能性があります。 複数のExcelファイルを開いている場合は、それらも一緒に閉じる可能性があるため、強制終了は本当に操作できないときだけ行います。
Excelを起動し直したときに「ドキュメントの回復」が表示された場合は、回復されたファイルの内容を確認し、すぐに別名で保存します。ただし、必ず復元できるとは限りません。
Macを使用している場合は、Option＋Command＋Escを同時に押し、「Microsoft Excel」を選んで「強制終了」をクリックします。Macの場合も、保存していない変更は失われる可能性があります。
5. 値貼り付け以外にできる軽量化
（1）計算方法の「手動」は補助的に使う
計算方法を一時的に「手動」にすると、入力のたびに再計算されることを防げます。ただし、結果が自動更新されなくなり、設定によっては保存時に再計算されます。
計算漏れによる誤った結果を残す危険もあるため、初心者には値貼り付けを基本の対処としておすすめします。
（2）繰り返し照合するならPower Queryも検討する
同じ形式の大量データを定期的に照合する場合は、Power Queryの「クエリのマージ」も選択肢です。共通するキー列を使って2つの表を結合できるため、ワークシートに大量の検索数式を並べずに処理できます。
一度だけの作業ならXLOOKUPと値貼り付け、繰り返す定型作業ならPower Queryというように使い分けるとよいでしょう。
6. まとめ
最近のパソコンは性能が上がり、Excelが固まる場面は以前より少なくなりました。それでも、数十万件の表を何十回、何百回と検索する処理では注意が必要です。
今回のポイントは次の3つです。
- 元ファイルの複製は、数式を入れる前に作る
- 検索結果を更新しないなら、保存前に数式を値へ置き換える
- 繰り返す処理なら、処理用ファイルやPower Queryの利用も検討する
便利な関数でも、数式を残し続けることが最適とは限りません。計算結果を確定できる作業では、「確認したら値にして保存する」ことを習慣にすると、大量データを扱うときのトラブルを減らせます。
