【Excel】Power Queryで更新時にExcelが固まる時の処理軽量化

Power Queryを使って大量データを定期的に更新していると、Excelが突然フリーズして作業が中断されることがあります。特に会社のPCではメモリ制限やネットワークの遅延が原因で、更新中に応答しなくなるケースが少なくありません。本記事では、Power Queryの更新時にExcelが固まる原因を切り分け、処理を軽量化する具体的な方法を解説します。

Power Query更新でExcelが固まる主な原因

Excelが固まる原因は多岐にわたりますが、多くは以下の3つに集約されます。まずは基本的な原因を理解し、自分の状況に当てはめてみてください。

1. データ量の過大化

Power Queryはワークシートに読み込む前に、内部でデータを圧縮して処理します。しかし、数百万行のデータや数百の列があると、メモリを大量に消費しExcelが応答を失います。特にCSVファイルやデータベースから全件取得するクエリは要注意です。

2. クエリステップの複雑化

Power Queryエディタで追加したステップが増えるほど、更新時に各ステップが逐次実行されます。「行のフィルター」「列の追加」「ヘッダーの昇格」のような基本操作でも、ステップ数が50を超えるとメモリ負荷が急増します。

3. 外部データソースへのアクセス遅延

会社のデータベースやSharePointリストを参照する場合、ネットワーク帯域やサーバーの応答時間が影響します。特にVPN経由でオンプレミスのデータベースに接続する際、タイムアウトや接続断が発生しExcelがフリーズしたように見えることがあります。

原因を切り分けるための確認手順

問題が発生したら、以下の手順で原因を絞り込みます。手順は順番に実行してください。

1. タスクマネージャーでリソースを確認する
   更新中にExcelが固まったら、Ctrl+Shift+Escでタスクマネージャーを開きます。[プロセス]タブでExcelのメモリ使用量がPCの搭載メモリ(例:8GB)の80%以上を占めていないか確認します。また、CPU使用率が100%近くになっていないかもチェックします。
2. 単一クエリだけで更新をテストする
   [データ]タブの[クエリと接続]を開き、すべてのクエリを一度無効にした後、1つだけ有効にして更新を実行します。固まらないクエリと固まるクエリを特定すれば、問題のクエリを絞り込めます。
3. データソースをローカルファイルに変更する
   現在ネットワーク上のファイルやデータベースを参照している場合、一旦そのデータをローカルのCSVにコピーし、Power Queryのソースをローカルに切り替えて更新します。固まらなければ、ネットワークやサーバー側の問題です。
4. Power Queryエディタでステップを確認する
   [データ]→[クエリと接続]→該当クエリを右クリック→[編集]でエディタを開きます。右側の[適用されるステップ]で各ステップのアイコンに警告マーク(黄色い三角)が出ていないか確認します。特に「型の変更」や「結合」のステップでエラーが発生していないかチェックします。
5. Excelのセーフモードで更新する
   Windowsの[ファイル名を指定して実行](Win+R)から「excel /safe」と入力してセーフモードで起動し、クエリを更新します。固まらずに更新できれば、アドインやカスタマイズが原因です。

処理を軽量化する具体的な方法

原因が特定できたら、以下の方法でPower Queryの処理を軽量化します。順番に試すことをおすすめします。

データの絞り込みをPower Query内で行う

Excelのワークシート関数でフィルターするのではなく、Power Queryの段階で必要な行と列だけに絞り込みます。たとえば、データベースからデータを取得する場合、SQLステートメントでWHERE句を使うようソースを設定すると、取得する行数が大幅に減ります。

クエリの結合・追加を最小限にする

複数のテーブルを結合(Merge)する際、すべての列を結合するのではなく、必要なキー列とデータ列だけを指定します。また、結合の代わりに参照(Reference)を使って分割しておき、最終的な出力で必要なデータだけマージする方法も有効です。

バックグラウンド更新を無効にする

[データ]→[クエリと接続]→[クエリのプロパティ]で[バックグラウンドで更新する]のチェックを外します。これにより、更新中はExcelの操作がブロックされますが、リソースを専有するためフリーズが発生しにくくなります。ただし、更新中は他の操作ができなくなる点に注意してください。

増分更新(Incremental Refresh)を検討する

Power Queryには標準機能として増分更新が用意されています(Excel for Microsoft 365またはPower BI Desktop)。日付範囲を指定して、前回更新以降のデータだけを追加で取得する設定にすると、更新時間が劇的に短縮されます。会社で利用する場合は管理者と相談の上、Power BIのライセンスが必要になる場合があります。

よくある失敗パターンと対策

実際の現場でよく見られる失敗例を紹介します。同じような状況に当てはまる場合は、すぐに対策を試してください。

失敗パターン1: 全データを一度に取り込む

「すべてのデータをPower Queryで取得してからExcelでフィルターしよう」という考えは危険です。数十万行のデータをワークシートに読み込むと、その後の手動フィルターも重くなります。Power Query内でできるだけデータを絞ってから読み込むように習慣づけましょう。

失敗パターン2: 複数のクエリを同じワークシートに出力する

複数のクエリを同じシートの異なる範囲に出力している場合、更新時にシート全体が再計算されるため、重くなることがあります。可能なら各クエリを別シートに出力し、必要な範囲だけを参照するように変更します。

失敗パターン3: 不要な列や行を削除せずに読み込む

データソースに数百列ある場合、Power Queryのホームタブにある[列の管理]→[他の列を削除]で必要な列だけ残すことが重要です。同様に、最初の数行をプレビューで確認し、不要な行は[行のフィルター]で事前に除外します。

管理者へ確認すべき設定

会社のPCでPower Queryの更新が固まる場合、以下の点をシステム管理者に確認してください。設定変更が必要なケースがあります。

• Excelのバージョンと更新プログラム: 古いバージョンのExcelではPower Queryの性能改善が含まれていないことがあります。最新の更新プログラムが適用されているか確認します。
• データゲートウェイの構成: オンプレミスのデータソースにアクセスする場合、Power BIゲートウェイやオンプレミスデータゲートウェイが正しく設定されているか確認します。ゲートウェイのメモリ割り当てが不足していると更新が遅くなります。
• グループポリシーによる制限: 企業のセキュリティポリシーで、Power Queryの外部データ接続が制限されている可能性があります。特にインターネット上のデータソースへのアクセスがブロックされていないか確認します。
• メモリ増設の検討: 日常的に大きなデータを扱う部署では、PCのメモリを16GB以上に増設することで改善する場合があります。IT部門に相談してください。

比較表: 最適化による効果の例

設定	改善前(秒)	改善後(秒)	メモリ使用量(MB)
全データ読み込み(100万行)	45	12	1200→350
結合クエリ(5テーブル)	60	18	800→220
増分更新(日次)	120	8	1500→150

よくある質問(FAQ)

Q1. Power Queryの更新中にExcelが「応答なし」になるのを防ぐ方法は?

更新中に他の操作をしないようにするのが基本です。さらに、[クエリのプロパティ]で[バックグラウンドで更新する]をオフにすると、更新処理にリソースが集中し、フリーズが軽減される可能性があります。ただし、更新中はExcelが操作できなくなるため、実行タイミングを選んでください。

Q2. クエリのステップ数が多いと必ず固まりますか?

必ずしも固まるわけではありませんが、ステップ数が多いとメモリ消費が増加し、特にデータ量が多い場合にリスクが高まります。一般的な目安として、30ステップを超えたら見直しを検討してください。不要なステップは削除し、複数のステップを1つにまとめる方法も有効です。

Q3. 会社のPCでPower Queryの更新が遅いのですが、ネットワークが原因でしょうか?

可能性は高いです。まず、データソースがネットワーク上の共有フォルダやデータベースの場合、ローカルにデータをコピーして更新テストを行ってください。ローカルでも遅い場合はPower Queryのクエリ設計が問題です。ローカルで速くなったら、ネットワーク帯域やサーバー負荷を管理者に相談してください。

まとめ

Power Queryの更新時にExcelが固まる問題は、データ量の削減、クエリステップの最適化、外部データソースの見直しによって大幅に改善できます。まずはタスクマネージャーでリソース状況を確認し、原因を切り分けてから適切な軽量化手法を選んでください。また、会社の環境では管理者の許可が必要な設定もあるため、事前に相談しながら進めることが重要です。定期的なメンテナンスとして、クエリのステップ数を定期的に見直し、増分更新の導入を検討すると、長期的に安定した運用が可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】