【Excel】ExcelでPower Queryの読み込み先を変更したら既存グラフが壊れる時の修正手順

Power Queryを使って外部データを取り込む際、読み込み先の設定を途中で変更すると、既存のグラフが突然正しく表示されなくなることがあります。グラフの系列が消えたり、データ範囲がエラーになったりする現象に遭遇した方も多いでしょう。これは、グラフが元のテーブルやセル範囲を参照しているのに対し、Power Queryが新しいテーブルにデータを再読み込みしたために参照が切れてしまうことが原因です。本記事では、この問題の根本原因を解説し、実際の修正手順をステップごとに紹介します。特別なアドインやVBAは使わず、標準機能だけで対応できる方法ですので、忙しい業務の中でもすぐに試せます。

なぜグラフが壊れるのか?根本原因を理解する

Power Queryの読み込み先を変更すると、既存のグラフが壊れる理由は、Excelのグラフが「系列式」と呼ばれる数式でデータを参照している点にあります。系列式は通常、次のような構造です。

=SERIES(系列名, カテゴリ範囲, 値範囲, プロット順)

この中で、カテゴリ範囲や値範囲に「Table1[列名]」のような構造化参照が使われている場合、Power Queryが読み込み先を変更すると「Table1」という名前が新しいテーブルに引き継がれず、グラフが正しいデータを見つけられなくなります。例えば、読み込み先を「既存のワークシート」から「新しいワークシート」に変更した場合、テーブルの位置が変わるためグラフの参照が切れます。

また、Power Queryの設定で「読み込み先」だけでなく「テーブル名」も自動的に変更されるケースがあります。既定ではクエリ名がそのままテーブル名になりますが、既存のテーブル名と重複すると自動的に「_2」などが付与され、グラフの参照先が存在しないテーブルを指してしまうのです。

具体例:読み込み先変更前後の変化

例として、元々「Sheet1」のA1セルから始まる「売上テーブル」にデータを読み込んでいたとします。グラフはこのテーブルの列を参照していました。ここでPower Queryの「クエリの設定」から読み込み先を「Sheet2」のA1セルに変更した場合、新しいテーブルがSheet2に作成されます。テーブル名は元の「売上テーブル」のままの場合もあれば、自動的に別の名前に変わることもあります。グラフの系列式は「Sheet1!売上テーブル」を指したままなので、データが表示されなくなります。

状態	グラフの参照式	結果
変更前	=SERIES(,売上テーブル[月],売上テーブル[金額],1)	正常表示
読み込み先変更後	=SERIES(,売上テーブル[月],売上テーブル[金額],1)	#REF!エラー、または系列が空
修正後	=SERIES(,売上テーブル2[月],売上テーブル2[金額],1)	正常表示に戻る

まず確認すべきこと:グラフの元データはどこを参照しているか

修正を始める前に、現在グラフがどのデータを参照しているのかを正確に把握する必要があります。以下の手順で確認してください。

1. 壊れているグラフをクリックして選択します。
2. リボンの「グラフのデザイン」タブを開き、「データの選択」をクリックします。
3. 「データソースの選択」ダイアログが開きます。左側の「系列」リストから各系列を選び、「編集」ボタンをクリックします。
4. 「系列の編集」ダイアログで、「系列値」の入力欄に表示されている数式を確認します。通常は「=SERIES(…,テーブル名[列名],…)」のような形式です。エラーが「#REF!」になっている場所が、修正すべき箇所です。
5. また、カテゴリ範囲が同じテーブルを参照しているかも併せて確認します。「系列の編集」ダイアログの「系列Xの値」や「系列Yの値」に相当する部分は、グラフの種類によって異なりますが、基本的な考え方は同じです。

確認が終わったら、正しいテーブル名と列名をメモしておきましょう。Power Queryで新しく作成されたテーブルは、多くの場合クエリ名と同じです。新しいテーブル名が不明な場合は、数式バーで「=テーブル名」と入力して候補を表示させるか、「数式」タブの「名前の管理」からテーブル名の一覧を確認できます。

読み込み先変更前の事前準備(推奨手順)

トラブルを未然に防ぐために、読み込み先を変更する前に以下の準備を行うことをお勧めします。

既存グラフをバックアップする

グラフを含むワークシート全体をコピーして、新しいシートに貼り付けておきます。これにより、万が一グラフが完全に壊れても元に戻せます。

テーブル名を固定する

Power Queryの読み込み先設定で、「テーブル名」を既存のテーブル名と異なる名前に明示的に指定することで、既存グラフへの影響を最小限にできます。ただし、既存グラフが元のテーブル名を使用している場合は、新しいテーブル名に合わせてグラフの参照を書き換える必要が生じます。そのため、可能であれば読み込み先を変更する前に、グラフの参照を「名前の定義」に置き換えておく方法も有効です。

名前の定義を活用する

グラフの系列式に直接テーブル名を書くのではなく、名前定義(名前付き範囲)を経由させる方法です。これにより、読み込み先が変わっても名前定義の参照先を更新するだけでグラフが追従します。設定は少し複雑ですが、長期的な保守性が向上します。

読み込み先変更後にグラフを修正する具体的な手順

すでにグラフが壊れてしまった場合、以下の手順で修正できます。

1. 新しいテーブルの名前と列名を確認する。 数式バーに「=」と入力し、ドロップダウンリストからテーブル名を探します。または「数式」タブの「名前の管理」でテーブル一覧を表示します。
2. グラフを右クリックし、「データの選択」を選ぶ。 またはリボンの「グラフのデザイン」→「データの選択」でも構いません。
3. 各系列の「編集」をクリックし、「系列値」の式を新しいテーブル参照に書き換える。 例えば、元の式が「=SERIES(,売上テーブル[月],売上テーブル[金額],1)」で、新しいテーブル名が「売上テーブル_2」の場合、「=SERIES(,売上テーブル_2[月],売上テーブル_2[金額],1)」に変更します。
4. 同様に「系列Xの値」(カテゴリ範囲)も修正する。 系列が複数ある場合はすべての系列に対して修正を行います。
5. 「OK」をクリックしてダイアログを閉じ、グラフが正しく表示されるか確認する。 もしもエラーが残る場合は、修正したテーブル名や列名に誤りがないか再チェックします。

これらの手順でほとんどのケースは解決します。ただし、グラフの種類によっては「系列の編集」ダイアログの構成が異なる場合がありますので、その際は表示に従って対応してください。

失敗しやすいパターンと回避策

修正作業中によく遭遇する失敗パターンを紹介します。これらを事前に知っておくことで、手戻りを防げます。

テーブル名の重複

Power Queryで読み込み先を変更する際、既存のテーブルと同名のテーブルを作成すると、Excel自動的に「_2」などのサフィックスを付加します。気づかずに元のテーブル名を使い続けるとエラーになります。修正時は必ず「名前の管理」で現在のテーブル名を確認しましょう。

列名の変更を見落とす

読み込み先変更時に、Power Query内で列名が変換されている可能性があります。特にスペースや特殊文字の扱いが変わると、グラフの系列式で「[[列名]]」のような二重角括弧が必要になる場合があります。系列式を直接編集する方法が不安な場合は、新しいデータを基にグラフを新規作成するのも一つの手段です。

「データの選択」で系列が全く表示されない

元のデータが完全に失われている場合、「データの選択」を開いても系列リストが空になっていることがあります。この場合は、グラフの再作成が必要です。ただし、元データが残っていて参照だけが切れているなら、系列の追加機能を使って新しい範囲を指定し直すことも可能です。

管理者に確認すべき項目

会社のPCで作業している場合、以下の点をIT管理者やファイルのオーナーに確認してから変更を行うことを推奨します。

• 共有ブックの編集権限: 複数人で編集しているブックの場合、読み込み先の変更は他のユーザーにも影響します。管理者の許可なく変更しないでください。
• Power Queryのバージョン: 古いバージョンのExcelでは、読み込み先変更時の挙動が異なる場合があります。最新の更新プログラムを適用しているか確認しましょう。
• データソースの接続設定: Power Queryが外部データベースに接続している場合、読み込み先の変更が権限設定に影響する可能性があります。管理者に問い合わせてください。

よくある質問(FAQ)

Q1. グラフが壊れた原因が読み込み先変更以外にある場合もありますか?
A1. はい。例えば、元データのテーブルを手動で削除したり、列名を変更した場合も同様の現象が起こります。また、Power Queryのクエリ自体を削除するとデータが消えるため、グラフも壊れます。

Q2. 修正後もグラフが更新されない場合はどうすればいいですか?
A2. グラフが正しいデータを参照していても、キャッシュが古い場合があります。グラフを右クリックして「グラフの種類の変更」を選択し、同じ種類を選び直すと強制的に再描画されることがあります。

Q3. 系列式を直接編集する方法はありますか?
A3. 数式バーでグラフを選択した状態で、系列式を直接書き換えることもできますが、誤りやすいため「データの選択」ダイアログから編集することをお勧めします。

Q4. 新しいグラフを作り直す方が早いですか?
A4. 系列が少なく、データ構造が単純な場合は、グラフを新規作成する方が確実で早いこともあります。ただし、書式や設定を引き継ぎたい場合は修正を選びましょう。

まとめ

Power Queryの読み込み先変更によってグラフが壊れる原因は、系列式が古いテーブル参照のまま残ることです。修正は「データの選択」から系列の参照先を新しいテーブル名に書き換えるだけです。事前にテーブル名を確認し、すべての系列を漏れなく更新することが重要です。また、変更前にグラフのバックアップを取っておくと安心です。この手順を覚えておけば、今後同様の問題が発生した際も迅速に対応できるでしょう。

📊

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

🏆

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