【Excel】Power Queryの更新を自動化する設定!Excelのブックを開いた時にデータを最新にする

ExcelでPower Queryを使って外部データを取得・加工している場合、毎回手動で更新するのは手間がかかります。特に、ブックを開くたびに最新のデータにしたい場面は多いはずです。この記事では、Excelのブックを開いたときにPower Queryの更新を自動で行う設定方法を解説します。この設定により、常に最新の状態でデータを確認できるようになります。

Power Queryは、様々なデータソースからデータを取得し、整形・加工できる強力な機能です。しかし、データソースが更新されても、Excelブック側で手動更新操作を行わない限り、データは古いままです。この手動更新の手間を省き、効率的に最新のデータを活用するための自動更新設定を学びましょう。

Power Queryの自動更新機能の仕組み

Power Queryの自動更新機能は、Excelブックが読み込まれる際に、設定されたクエリに対して自動的に更新処理を実行する仕組みです。これにより、ユーザーが手動で「すべて更新」ボタンを押す必要がなくなります。

この機能は、主に「クエリのプロパティ」設定を通じて制御されます。各クエリごとに、ブックを開いたときの更新動作や、更新処理のバックグラウンド実行の可否などを細かく設定できます。これにより、データ更新の頻度や重要度に応じた柔軟な運用が可能になります。

Power Queryの更新を自動化する手順

Power Queryで取得したデータの更新を、Excelブックを開いたときに自動で行うための設定手順を説明します。この設定は、各クエリに対して個別に適用する必要があります。

まずは、更新したいクエリを含むExcelブックを開いてください。次に、Power Queryエディターを起動し、対象のクエリを選択した状態にします。

1. Power Queryエディターを開く
   Excelのリボンメニューから「データ」タブを選択します。「データの取得と変換」グループにある「クエリと接続」をクリックします。
2. クエリのプロパティを表示する
   「クエリと接続」ウィンドウが表示されたら、自動更新を設定したいクエリを右クリックします。表示されるメニューから「プロパティ」を選択します。
3. 自動更新の設定を行う
   「クエリのプロパティ」ダイアログボックスが表示されます。ここで、以下の3つのオプションを確認・設定します。
   • 「操作をバックグラウンドで実行する」: このチェックボックスをオンにすると、更新処理がバックグラウンドで実行されます。これにより、更新中でもExcelの他の操作が可能になります。
   • 「完了時にクエリの更新を有効にする」: このチェックボックスをオンにすると、Excelブックを開いたときにこのクエリの更新が自動的に実行されるようになります。これが自動更新の最も重要な設定です。
   • 「「更新」コマンドを有効にする」: このチェックボックスをオンにすると、手動でクエリを更新するための「更新」ボタンなどが有効になります。通常はオンにしておくことを推奨します。
4. 設定を保存する
   必要な設定が完了したら、「OK」ボタンをクリックしてダイアログボックスを閉じます。
5. ブックを保存して再起動する
   Excelブックを保存し、一度閉じてから再度開いてください。ブックを開いたときに、設定したクエリが自動的に更新されることを確認できます。

複数のクエリに設定を適用する場合

複数のクエリに対して自動更新を設定したい場合は、上記の手順を各クエリごとに繰り返します。クエリの数が多い場合でも、一つずつ設定していくことで、ブックを開いたときに全てのデータが最新の状態になります。

また、Excelのバージョンによっては、まとめて設定するオプションが存在する場合もあります。もし、多数のクエリに一括で設定したい場合は、Excelのヘルプや公式ドキュメントで詳細な情報を確認してみてください。ただし、一般的には個別の設定が推奨されます。

自動更新設定の注意点とトラブルシューティング

Power Queryの自動更新設定は非常に便利ですが、いくつかの注意点や、発生しうるトラブルがあります。これらの点を理解しておくことで、よりスムーズに機能を活用できます。

ブックを開くのに時間がかかる場合

自動更新を有効にしたクエリが多い場合や、取得するデータ量が膨大な場合、ブックを開くのに通常より時間がかかることがあります。これは、Excelがバックグラウンドで各クエリの更新処理を実行しているためです。

対処法:

1. 不要なクエリの自動更新を無効にする
   すべてのクエリで自動更新が必要ない場合は、一部のクエリのプロパティで「完了時にクエリの更新を有効にする」のチェックを外してください。
2. 「操作をバックグラウンドで実行する」を有効にする
   このオプションをオンにすることで、更新中でもExcelの操作が可能になり、体感的な待ち時間を減らせます。
3. データソースの最適化
   取得するデータソース側で不要な列を削除したり、フィルターを適用したりして、データ量を減らすことも有効です。

更新に失敗する場合

ブックを開いてもクエリが更新されず、「#N/A」などのエラーが表示される場合があります。これは、データソースへの接続に問題がある、データソースの形式が変更された、などの原因が考えられます。

対処法:

1. 手動で更新を試す
   「データ」タブの「すべて更新」ボタンをクリックして、手動で更新を試みてください。エラーメッセージが表示される場合は、その内容を確認して原因を特定します。
2. データソースの接続を確認する
   Power Queryエディターで、データソースへの接続情報が正しいか、パスやURLが変更されていないかを確認してください。
3. クエリを再作成する
   問題が解決しない場合は、一度クエリを削除し、再度データソースから取得し直すことで解決する場合があります。

Excelのバージョンによる違い

Power Queryの自動更新機能は、Excel 2016以降で利用可能です。Excel 2016では「Power Query」という名称で提供されていましたが、Excel 2019以降は「データの取得と変換」機能として統合され、さらに機能が強化されています。

Excel 2013以前のバージョンでは、Power Queryアドインを別途インストールする必要があり、機能や操作方法が異なる場合があります。この記事で紹介した設定手順は、主にExcel for Microsoft 365、Excel 2019、Excel 2021を対象としています。お使いのExcelのバージョンに合わせて、操作方法を確認してください。

Power QueryとVBAによる自動化の比較

Power Queryの自動更新機能は、Excelブックを開いたときにクエリを自動更新する便利な機能です。しかし、より複雑な自動化や、特定の条件に基づいた更新処理を行いたい場合は、VBA(Visual Basic for Applications)を使用することも選択肢となります。

Power Queryの自動更新は、主に「ブックを開いたとき」というイベントに紐づいたシンプルな自動化に適しています。一方、VBAを使えば、特定のボタンをクリックしたとき、特定のセルに値が入力されたとき、あるいは特定の時間になったときに更新を実行するなど、より柔軟で高度な自動化が可能です。

項目	Power Queryの自動更新	VBAによる自動化
主なトリガー	Excelブックを開いたとき	ボタンクリック、セルの変更、特定の時間など(任意に設定可能)
設定の容易さ	プロパティ設定のみで容易	VBAコードの記述が必要(初心者には難易度高)
柔軟性・拡張性	限定的(ブック開放時の更新に特化)	非常に高い(複雑な処理や条件分岐も可能)
セキュリティ	マクロ有効化などの追加セキュリティ設定不要	マクロ有効化が必要な場合があり、セキュリティリスクを考慮する必要あり
適した用途	常に最新のデータを確認したい場合、手動更新の手間を省きたい場合	特定のタイミングで更新したい、更新処理に条件を付けたい、複数の処理を連携させたい場合

どちらの方法が適しているかは、実現したい自動化のレベルや、ユーザーのスキルによります。まずはPower Queryの自動更新機能で実現できるか検討し、より高度な要件がある場合にVBAの利用を検討するのが良いでしょう。

Power Queryの自動更新設定を理解し、適切に活用することで、Excelでのデータ管理業務の効率が大幅に向上します。常に最新のデータを参照できる環境を整えることは、迅速な意思決定にも繋がります。

今回解説した「クエリのプロパティ」設定は、Power Queryの基本的ながらも非常に重要な機能の一つです。この設定を適用することで、ブックを開くたびに手動で更新ボタンを押す手間が省けます。

今後は、この自動更新設定を基本としつつ、必要に応じてVBAによるより高度な自動化も検討してみてください。これにより、さらに効率的なデータ活用が可能になります。

📊

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

🏆

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