【Excel】Power Queryの更新が遅い原因!Excelのバックグラウンド更新設定を見直す方法

ExcelでPower Queryを使ったデータ更新に時間がかかっていませんか。特にデータ量が多い場合や、複数のクエリを扱っている場合に、更新に数分、場合によっては数時間かかることがあります。この遅延は業務のボトルネックとなり、作業効率を著しく低下させます。この記事では、Power Queryの更新が遅くなる主な原因と、Excelのバックグラウンド更新設定を見直すことで、その問題を解決する方法を解説します。

Power Queryの更新が遅くなる主な原因

Power Queryの更新に時間がかかる場合、その原因は一つとは限りません。複数の要因が複合的に影響していることもあります。主な原因を理解することで、より的確な対策を講じることができます。

データソース側の問題、クエリの複雑さ、Excel自体の設定などが考えられます。特に、データソースへの接続が不安定だったり、データ量自体が膨大である場合は、更新に時間がかかるのは避けられません。また、Power Queryエディター内で行う変換処理が複雑すぎると、その処理に時間がかかり、結果として更新時間が長くなります。

さらに、Excelのバックグラウンド更新設定が適切でない場合も、更新中はExcelがフリーズしたように感じられ、作業が一時停止してしまうことがあります。この設定は、更新作業中のExcelの応答性を大きく左右するため、見直しが重要です。

データソース側の要因

Power Queryは外部のデータソースからデータを取得します。そのため、データソース自体の応答速度が遅いと、Power Queryの更新も遅くなります。例えば、Web APIからのデータ取得、データベースへのアクセス、あるいはネットワーク経由でのファイルアクセスなどが遅延の原因となります。

データソースへの接続が頻繁にタイムアウトしたり、応答が不安定な場合も、更新処理が中断・再試行を繰り返すため、時間がかかります。データソース側のサーバー負荷が高い場合も同様の影響が出ます。

クエリの複雑さと変換処理

Power Queryエディターで行う変換処理が複雑であるほど、データの整形・加工に時間がかかります。例えば、多数の列の結合、条件に基づく列の追加、グループ化、ピボット解除などが多数のステップで実行されている場合、それぞれのステップで処理時間が加算されます。

特に、大量の行に対して複雑な条件分岐や計算を行う場合、処理能力が要求されます。また、不要な列や行を削除せずに多くの変換を適用していると、無駄な処理が増え、効率が悪くなります。

Excelのバックグラウンド更新設定

Excelには、Power Queryなどのデータ更新をバックグラウンドで行う設定があります。この設定が有効になっていない場合、更新中はExcelが他の操作を受け付けなくなり、画面が固まったように見えます。これにより、ユーザーは更新が完了するまで待たされることになります。

バックグラウンド更新が有効であれば、更新処理はExcelの裏側で行われ、ユーザーは待機時間中に他のシートの閲覧や編集などの作業を続けることができます。これにより、体感的な待ち時間が短縮され、作業効率が向上します。この設定の有無と状態を確認することが、更新遅延への対策として重要です。

Excelのバックグラウンド更新設定を有効にする方法

Power Queryの更新が遅く感じられる場合、Excelのバックグラウンド更新設定を見直すことで、体感的な待ち時間を短縮し、作業効率を向上させることができます。この設定は、Excelのオプションから簡単に行えます。

バックグラウンド更新を有効にすると、Power Queryがデータを更新している間でも、Excelの他の部分を操作できるようになります。これにより、更新完了をただ待つのではなく、その間に別の作業を進めることが可能になります。以下にその具体的な手順を示します。

1. Excelのオプションを開く
   Excelのリボンメニューにある「ファイル」タブをクリックし、左側のメニューから「オプション」を選択します。
2. 「詳細設定」を選択する
   Excelのオプションダイアログボックスが開いたら、左側のメニューから「詳細設定」をクリックします。
3. 「バックグラウンドで更新する」にチェックを入れる
   右側の詳細設定項目の中から、「データ」セクションを探します。その中に「バックグラウンドで更新する」という項目がありますので、チェックボックスにチェックを入れます。
4. 「OK」をクリックして設定を保存する
   設定を変更したら、ダイアログボックスの右下にある「OK」ボタンをクリックして、設定を保存し、ダイアログボックスを閉じます。

この設定を行うことで、次回以降Power Queryの更新を実行した際に、バックグラウンドで処理が行われるようになります。更新中は、Excelのステータスバーに更新の進捗状況が表示されるようになります。

Power Queryの更新オプションを確認・変更する方法

Excelのオプションでバックグラウンド更新を有効にした後、個々のクエリの更新設定を確認・変更することも重要です。これにより、さらに細かく更新動作を制御できます。

各クエリごとに、更新時の動作や、先述のバックグラウンド更新設定の有効・無効を個別に設定することが可能です。これにより、特定のクエリだけをバックグラウンドで更新したり、逆にフォアグラウンドで更新する必要があるものだけを制御できます。

1. 「データの取得」グループの「すべて更新」をクリック
   Excelのリボンメニュー「データ」タブにある「すべて更新」ボタンの▼部分をクリックし、「接続プロパティ」を選択します。
2. 「接続プロパティ」ダイアログボックスで設定を確認・変更する
   選択したクエリ(接続)の「接続プロパティ」ダイアログボックスが表示されます。
3. 「使用」タブの「バックグラウンドで更新する」を確認・設定する
   ダイアログボックス内の「使用」タブを選択します。ここに「バックグラウンドで更新する」というチェックボックスがあります。
4. 必要に応じてチェックボックスを操作する
   このチェックボックスがオンになっていると、そのクエリはバックグラウンドで更新されます。オフになっている場合は、フォアグラウンドで更新されます。
5. 「OK」をクリックして設定を保存する
   設定を変更したら、「OK」ボタンをクリックしてダイアログボックスを閉じます。

「すべて更新」ボタンの▼から「接続プロパティ」を選択することで、各クエリ(接続)ごとにバックグラウンド更新のオン・オフを個別に設定できます。これにより、更新処理の全体的なパフォーマンスを最適化できます。

更新が遅い場合のその他の対策

バックグラウンド更新の設定を見直しても、依然としてPower Queryの更新が遅い場合は、他の原因も考えられます。クエリの最適化やデータソースの改善など、多角的なアプローチが必要です。

ここでは、バックグラウンド更新以外で試せる、更新速度を改善するための具体的な対策をいくつか紹介します。これらの対策を組み合わせることで、より効果的に更新時間の短縮を図ることができます。

クエリの最適化

Power Queryエディターで行う変換処理を見直し、不要なステップを削除したり、処理を効率化することが重要です。例えば、

• 不要な列や行の削除: 変換処理の早い段階で、使用しない列や行を削除することで、以降の処理対象データ量を減らせます。
• データ型の最適化: 各列のデータ型を適切に設定します。例えば、数値データにテキスト型が設定されていると、意図しない変換処理が発生する可能性があります。
• 複雑な関数の代替: 複雑なカスタム関数やM言語の記述は、可能であればよりシンプルな関数やステップに置き換えることを検討します。
• クエリの折りたたみ: データソース側で集計やフィルタリングが可能な場合は、Power Queryのクエリの折りたたみ機能を利用して、データソース側で処理を完結させます。

データソースへの接続改善

データソースへの接続が不安定な場合、更新に時間がかかります。以下の点を確認してください。

• ネットワーク環境の確認: 安定したネットワーク接続を確保します。特に、Wi-Fiが不安定な場合は、有線LAN接続を検討します。
• データソースへのアクセス権: データソースへのアクセス権限が適切か確認します。
• データソース側のパフォーマンス: データベースやサーバーの応答速度が遅い場合は、データソースの管理者と協力して改善策を検討します。

Excelのバージョンとリソース

使用しているExcelのバージョンや、PCのリソース状況も更新速度に影響します。最新バージョンのExcel(Microsoft 365)は、パフォーマンスが改善されている場合があります。また、PCのメモリ(RAM)やCPU性能が低い場合、複雑なクエリの処理に時間がかかることがあります。

不要なアプリケーションを終了するなど、PCのリソースを解放することで、Excelの処理速度が向上する可能性があります。

Power Queryの更新オプションの比較

Power Queryの更新動作には、バックグラウンド更新の有無によって、ユーザー体験が大きく異なります。それぞれの設定がどのような影響を与えるかを比較します。

バックグラウンド更新を有効にするかしないかで、更新中のExcelの操作性が変わります。これにより、作業効率に直接的な影響が出ます。以下に、両者の主な違いをまとめました。

項目	バックグラウンド更新が無効な場合	バックグラウンド更新が有効な場合
更新中のExcel操作	Excel全体が応答しなくなり、操作不可	Excelの他の部分を操作可能(更新中のクエリ以外)
体感的な待ち時間	長く感じる、作業が中断される	短く感じる、他の作業を進められる
ユーザーの生産性	低下する	向上する
設定箇所	Excelのオプションで「無効」になっている	Excelのオプションで「有効」になっている
リソース使用	更新処理にCPU・メモリを専有	更新処理と他の操作でリソースを共有

バックグラウンド更新を有効にすることで、Power Queryの更新作業中でもExcelを快適に操作できるようになります。これにより、データ更新にかかる時間を有効活用し、全体の作業効率を高めることが期待できます。

まとめ

この記事では、ExcelのPower Queryの更新が遅い問題に対し、Excelのバックグラウンド更新設定を見直す方法を解説しました。バックグラウンド更新を有効にすることで、更新中のExcelの操作性が向上し、作業効率が大幅に改善されます。

まずExcelのオプションから「バックグラウンドで更新する」にチェックを入れ、さらに各クエリの「接続プロパティ」で設定を確認・変更してください。それでも遅い場合は、クエリの最適化やデータソースの改善も検討しましょう。これらの設定と対策により、Power Queryの更新時間を有効活用できるようになります。

📊

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

🏆

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