Power Queryで作った表は、元データが変わっても自動では書き換わりません。デスクトップ版Excelでは、手動で「すべて更新」を押す代わりに、クエリごとに「ファイルを開いたときに更新」「一定間隔で更新」を設定できます。ただし、どちらもExcelでそのブックを開いている間しか動きません。この記事では、設定の手順、更新されない条件、更新されたかの確かめ方をまとめます。
設定できる更新の種類
| 設定 | 動くタイミング | 向いている使い方 | 注意点 |
|---|---|---|---|
| ファイルを開くときにデータを更新する | ブックを開いた直後に1回 | 毎朝開いて見る集計表、月次の取り込み表 | 開いた後に元データが変わっても、もう一度は更新されない |
| 定期的に更新する（○分ごと） | ブックを開いている間、指定した間隔ごと | 共有フォルダのCSVなど、日中に何度も変わる元データを追う表 | 閉じている間は動かない。作業中にも更新が始まる |
| バックグラウンドで更新する | 更新の進め方の設定 | 更新中もExcelを操作したいとき | 更新が終わるのを待たずに次の処理が進むことがある |
3つ目はタイミングの設定ではありません。更新している間にExcelを操作できるようにするかどうかを決める設定です。
設定画面を開く
- 対象のブックをデスクトップ版Excelで開きます。
- 「データ」タブの「クエリと接続」を押し、右側に一覧を表示します。
- 設定したいクエリを右クリックし、「プロパティ」を選びます。
- 「クエリのプロパティ」画面の「使用状況」タブで、「更新コントロール」の項目を設定し、「OK」を押します。
画面や項目の名前は、Excelのバージョンや更新の状況によって少し違うことがあります。見つからない場合は、クエリの結果が出ているセルを選び、表示される「クエリ」タブの「プロパティ」から開けるか試してください。
設定はクエリごとに保存されます。ブックに複数のクエリがあるとき、1つに設定しても、ほかのクエリは自動では更新されません。ほかのクエリから参照されるだけの途中のクエリ（「接続専用」と表示されるもの）は、最終的な結果を出すクエリを更新するときに一緒に読み込まれます。そのため、通常は結果を出すクエリに設定すれば足ります。
ファイルを開いたときに更新する
「ファイルを開くときにデータを更新する」にチェックを入れると、ブックを開くたびにクエリが1回更新されます。
この近くに「保存する前に外部データ範囲からデータを削除する」といった項目が出ることがあります。チェックを入れると、保存したファイルには結果の表が残りません。開いたときの更新に失敗すると、表は空のままになります。元データにいつでもアクセスできる環境でなければ、チェックは外しておくのが無難です。
ピボットテーブルもある場合
クエリの結果の表からピボットテーブルを作っている場合、ピボットテーブルはクエリとは別に更新しなければなりません。ピボットテーブルを右クリックして「ピボットテーブル オプション」を開き、「データ」タブの「ファイルを開くときにデータを更新する」を確認します。
このとき、クエリ側の「バックグラウンドで更新する」がオンになっていると、クエリの更新が終わる前に、ピボットテーブルが古い表のまま更新されることがあります。開いた直後の数字が1回前のままになる場合は、クエリのバックグラウンド更新をオフにしてください。
一定間隔で更新する
「定期的に更新する」にチェックを入れ、間隔を分単位で入力します。ブックを開いている間、その間隔ごとにクエリが更新されます。
間隔を決めるときは、次の点を考えます。
- 元データが変わる頻度：元データが1日1回しか変わらないのに数分ごとに更新しても、読み込みの負荷が増えるだけです。
- 1回の更新にかかる時間：更新に数分かかるクエリを短い間隔にすると、ほとんどずっと更新している状態になります。先に手動で「すべて更新」を実行してかかる時間を見てから、間隔を決めます。
- 作業中にも更新が始まること：入力の途中や別の操作中でも更新が始まります。結果の表と同じシート、特に表のすぐ下や横に手入力の欄を置くと、行数が増えたときに位置がずれる原因になります。入力欄は別のシートに分けてください。
- 保存の確認：更新で表が書き換わるとブックが変更された扱いになり、閉じるときに保存の確認が出ることがあります。
バックグラウンドで更新するかを決める
| 状態 | 更新中の動き | 向いている場面 |
|---|---|---|
| オン | 更新中もセルの選択や入力ができる。「すべて更新」では、複数のクエリが前の完了を待たずに進むことがある | 結果を見るだけの表、更新に時間がかかる表 |
| オフ | 更新が終わるまでExcelが待つ | ピボットテーブルや数式が結果の表を参照していて、更新の順番をそろえたい表 |
データモデルに読み込んでいるクエリなど、接続の種類によっては、この項目を変えられない場合があります。
自動で更新されない条件
設定したのに更新されないときは、次のどれかに当てはまっていないか確認します。
| 状況 | 起きること | 確認すること |
|---|---|---|
| ブックを閉じている、Excelを起動していない | 開いたときの更新も、定期的な更新も動かない | この設定ではExcelの外で定時に動かすことはできない。閉じたまま更新したい場合は、Excelの外で動く別の仕組みが必要 |
| メールの添付やダウンロードしたファイルを開いた | 保護ビューで開かれ、「編集を有効にする」を押すまで更新されない | 画面上部のメッセージバーを見る |
| 外部データ接続が無効になっている旨のセキュリティ警告が出た | 「コンテンツの有効化」を押すまで更新されない | 警告を出さないように設定を緩めたい場合は、先に社内のルールを確認する |
| 元データが見つからない | 更新がエラーになり、表は前回の内容のまま残る | ファイルのパスやフォルダ名が変わっていないか。他の人のPCでも同じパスで見えるか |
| 資格情報やプライバシーレベルの確認が出ている | 確認画面に答えるまで更新が進まない | 初めて開いたときや、別のPCで開いたときに出やすい |
| 設定したのが別のクエリだった | 目的の表は更新されない | 「クエリと接続」で、表の元になっているクエリの名前を確認する |
| ブックの計算方法が「手動」 | クエリの表は更新されても、それを参照する数式の結果は変わらない | 「数式」タブの「計算方法の設定」を確認する |
| Web版やMac版のExcelで開いた | 設定項目や動き方がデスクトップ版と違うことがある | それぞれの画面に出る項目を確認する。デスクトップ版の設定がそのまま動くと決めつけない |
更新されたかを確かめる
「クエリと接続」で状態を見る
「クエリと接続」の一覧でクエリにマウスを合わせると、プレビューと一緒に、最後に更新した日時や読み込んだ行数が出ることがあります。エラーのあるクエリには警告やエラーの件数が出るので、まずはここを見ます。
結果の表に取得日時の列を追加する
確実に記録したいなら、クエリに「取得日時」の列を追加します。Power Queryエディターで対象のクエリを開き、最後のステップを選んだ状態で「列の追加」→「カスタム列」を選びます。列名に「取得日時」、式に DateTime.LocalNow() を入れると、数式バーに次のような式が作られます。#"変更された型" の部分は、直前のステップの名前になります。
= Table.AddColumn(#"変更された型", "取得日時", each DateTime.LocalNow())
追加した列のデータ型は「日付/時刻」にしておきます。この列の時刻は更新のたびに変わるので、開いたときの更新や定期的な更新が実際に動いたかを、表の上で確かめられます。
ブック全体の更新時刻だけ分かればよい場合は、1行だけのクエリを作ってシートに読み込む方法もあります。「すべて更新」で更新しているブックで、最後に更新した日時を表示しておくのに向いています。
let
Source = #table(type table [更新日時 = datetime], {{DateTime.LocalNow()}})
in
Source
ただし、このクエリが示すのは、このクエリ自体が更新された時刻だけです。特定のクエリの定期的な更新を確かめたいときは、そのクエリに取得日時の列を追加してください。
元データを変えて試す
設定した直後は、元データに1行追加して保存します。そのうえでブックを開き直すか、定期更新の間隔だけ待ち、行数が増えるかで動きを確かめられます。本番の元データを書き換えたくない場合は、ブックと元データの両方をテスト用のフォルダにコピーし、クエリの参照先をテスト用に変えてから試します。
使い方別の設定例
| 使い方 | 開いたときに更新 | 定期的に更新 | バックグラウンド更新 |
|---|---|---|---|
| 毎朝開いて前日分を確認する集計表（ピボットテーブルあり） | オン | オフ | オフ |
| 日中は開いたままにして、共有フォルダのCSVの変化を見る一覧 | オン | オン（元データが変わる頻度に合わせる） | 作業の内容に応じて選ぶ |
| 月に1回だけ、自分の決めた時期に取り込む表 | オフ | オフ | どちらでもよい |
自動更新を設定しない表は、ショートカットで手動更新できます。選んでいるクエリの表だけを更新するときは Alt+F5、ブック内をすべて更新するときは Ctrl+Alt+F5 です。
