【Excel】別シート・別ブックの値が取れないとき|#REF!とリンク切れの直し方

別シートの値が取れないときは、まず数式が同じブック内のシートを見ているのか、別ファイルを見ているのかを確認します。='売上 9月'!B2は同じブック内、='[売上.xlsx]9月'!B2は別ブックへの参照です。

#REF!が出る場合と、エラーはないのに古い値のままの場合では、調べる場所が異なります。むやみにリンクを解除せず、元の式とファイルのコピーを残してから、参照先と更新状態を順に点検します。

1. シート名・ブック名・セル番地を読み分ける

【要点】同じブックの別シート参照には、通常フォルダーのパスは必要ありません。=Sheet2!A1の!は、シート名とセル番地の区切りです。シート名に空白などがある場合は、='売上 9月'!B2のように引用符で囲みます。

式を手入力して間違えやすい場合は、出力先セルに=を入力し、参照先シートへ移動して目的のセルをクリックし、Enterで確定します。Excelが参照式を作るため、引用符の付け忘れを減らせます。

別ブックを参照する場合は、[売上.xlsx]のようなブック名が加わります。元ブックを閉じると、保存場所を含むパスが式に表示されることがあります。長い式になっただけで壊れたとは限りません。どのファイル、どのシート、どのセルかを順に読んでください。

2. #REF!と古い値の表示では確認先が違う

同じブック内の直接参照では、シート名をExcel上で変更すると通常は式も追従します。一方、参照先のシートやセルを削除すると、参照を維持できず#REF!になることがあります。削除直後なら、まず取り消しで戻せるか確認します。

別ファイルを移動・改名した場合は、以前の保存場所から値を更新できなくなることがあります。ただし移動した瞬間にすべての式が必ず#REF!になるわけではなく、以前取得した値が残って見える場合もあります。数字が表示されているだけで最新とは判断できません。

式自体に#REF!が含まれるときは、正しいシートやセルの参照が残っているかを調べます。元ファイルの場所を直しても、削除によって失われたセル番地までは自動復元されません。コピーや変更履歴を見て本来の参照先を特定します。

3. 参照方法ごとに切り分ける

=INDIRECT("'売上 9月'!B2")では、引用符内の文字から参照を組み立てます。シート名を変えても、その文字列が直接参照と同じように追従するとは限りません。

外部ブックをINDIRECTで参照する場合は、参照先ブックを開く必要があります。閉じたブックから常時集計したい用途では、通常の直接参照など、目的に合った方法へ見直します。Web版では外部参照のINDIRECTはサポートされないため、同じ式が使えると決めつけないでください。

4. 外部ブックの保存場所を「ソースの変更」で直す

現在のWindows版Excelでは「データ」タブの「ブックのリンク」から管理できます。版によっては従来の「リンクの編集」が表示されます。いずれも同じブック内のシート参照を修正するメニューではありません。

1. 参照される側のファイルが、現在どこにあるかを確認します。
2. 参照する側のブックを開き、「データ」からブックのリンクを開きます。
3. 該当する元ブックを選び、「ソースの変更」で正しいファイルを指定します。
4. 更新し、既知のセルの値を元ブックと比較します。
5. 必要なシート名とセル番地が、新しいファイルにも存在するか確認します。

ファイル名が同じだけで選ばないでください。昨年度のコピーを指定すると、エラーは消えても古い実績を集計する恐れがあります。対象期間とシート構成も見ます。また、一つのリンク先の変更は、その元ブックを参照する他の式にも影響するため、主要な合計を確認します。

管理画面に目的のリンクが見当たらない場合は、セルの数式に加え、名前の管理、グラフの系列、図形のリンクも確認対象です。Power Queryの参照先はクエリ側で管理します。メニューが無効というだけで、外部とのつながりがすべてないとは判断しません。

5. 「リンクの解除」と名前定義の役割を知る

リンクを解除すると、外部参照を使う式が、その時点の値へ置き換わります。参照先を修復する操作ではありません。以後も元ファイルに合わせて更新したいなら、「ソースの変更」を使うべき場面です。

確定版として値だけ残す場合でも、元の数式を持つブックを別に保管します。古い値が残っている状態でリンクを解除すると、その古い値で固定してしまいます。必ず確定した数値と照合してから判断してください。

参照範囲に「売上金額」などの名前を付けると、式の意味を読みやすくできます。しかし名前の参照先が#REF!なら、名前を使う式も正常にはなりません。名前定義はファイルの移動やシート削除を無条件で解決する仕組みではないため、「名前の管理」で参照範囲を確認します。

一度に式全体を置換すると、別のファイル名や文字列まで変える危険があります。手動修正は対象のセルに限定し、直した式で期待する値が取れることを確かめてから、同じ原因の箇所に進みます。

6. 共有ファイルは開く人と環境の違いも確認する

自分のPCのCドライブにあるファイルを参照した式は、他の利用者から同じ場所へアクセスできるとは限りません。共有用なら、元ファイルの保存場所とアクセス権をそろえ、受け取る人の環境でも更新できるか確認します。

Web版ではクラウド上のブックのリンクを管理・更新できますが、デスクトップのローカルパスをそのまま読めるとは限りません。更新許可が必要な場合も、出所が分からないリンクを一律に信頼する設定へ変更せず、参照先を確認したブックで操作します。

Power Queryで取り込む設計も選択肢ですが、元ファイルの場所、認証、更新権限への依存は残ります。クエリに変えればどの環境でも自動更新できる、とは言えません。共有方法と更新担当者を決めたうえで、必要なデータをどこから取り込むかを整理します。

定例業務では、誰がいつ元データを更新するかを決め、更新前後の対象期間を表示しておくと、正常に見える古い数値を使い続ける失敗を防ぎやすくなります。

7. まとめ：エラーの消失だけで復旧を判断しない

復旧後は、少なくとも元表の一つの明細と最終合計を照合します。同じブック内なら削除と式の参照先、別ブックなら保存場所、アクセス、更新状態を確認します。

保存して開き直した後も、想定した元ファイルから値を取得できるか確かめてください。現在のリンク管理はMicrosoftのブックのリンクを管理する説明にまとまっています。リンクを消すことではなく、正しい参照先から必要な値を取得することを目標に直しましょう。

📊

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

🏆

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