サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
別のスプレッドシートを参照したらリンクが切れた・値が古いまま
記事IT・テクノロジー
昨日まで正しく表示されていた参照が、今日開いたら #REF! になっている。
あるいはエラーは出ていないのに、参照元を更新したはずの値が
古いまま変わらない。IMPORTRANGEの許可設定は確認済みなのに直らない――
という場合、原因はIMPORTRANGE自体ではなく、もっと基本的なところに
あることが多いです。ここでは、それ以外の原因に絞って見ていきます。
■ 「参照」は、実は書き方が2種類ある
別シート・別ファイルの値を参照する方法には、大きく分けて
シート参照（同一ファイル内、または関数経由の別ファイル参照）と
INDIRECT関数による参照があります。この2つは挙動が根本的に違います。
=集計!A1
このような `=シート名!セル番地` の書き方は、数式を入力した時点で
「どのシートのどのセルか」が固定され、参照先として記憶されます。
参照先のシート名が後から変わっても、Googleスプレッドシートは
できる限り追従しようとしますが、追従できないケースがあります
（後述）。
=INDIRECT("集計!A1")
一方INDIRECTは、カッコの中の文字列を、計算するたびに
毎回その場で組み立てて評価します。 シート名をセルの値から
組み立てて動的に参照先を変えたいときに便利な反面、
文字列として書いたシート名が実際のシート名と1文字でも
違えばエラーになり、しかも数式を見ただけでは
どこが違うのか分かりにくいという弱点があります。
INDIRECTを使った参照が急に効かなくなった場合、
まずシート名の完全一致を疑ってください。
■ 参照元のシート名を変更すると、シート参照は追従するがINDIRECTは追従しない
`=集計!A1` のような通常のシート参照は、参照先のシート名を
スプレッドシート側の「シート名の変更」機能で変えた場合、
数式側の表記も自動的に新しいシート名へ書き換わります。
これは同一ファイル内の参照であれば、ほぼ確実に効きます。
ところがINDIRECTの場合、シート名は文字列として書かれているため、
シート名を変更しても、INDIRECT内の文字列は自動更新されません。
「シート名を変えたらINDIRECTを使っている数式だけ壊れた」という
症状は、この違いによるものです。INDIRECTを使っている箇所を
洗い出し、シート名を変更したら手作業で文字列も直す必要があります。
■ ファイルを共有ドライブに移動すると、参照が切れることがある
マイドライブにあったファイルを共有ドライブに移動した、
あるいはその逆を行った場合、ファイルのID自体は変わらないため
関数側の参照は通常そのまま動きます。 ただし、共有ドライブへの
移動に伴って、そのファイルにアクセスできるメンバーの範囲が
変わることがあり、参照元ファイルに対する閲覧権限を失ったユーザーの
画面では、値が更新されずエラーや古い値のまま表示されることがあります。
この場合、数式自体は壊れていません。値が更新されない・エラーになる
ユーザーについて、参照元ファイルへのアクセス権限が移動の前後で
変わっていないかを確認してください。
■ ファイルをコピーすると、参照は「コピー先」ではなく「元ファイル」を向いたまま
テンプレートとして使っていたファイルを複製し、新しい案件用に
使い始めたところ、別シートを参照している数式の値が、
新しいファイルの中身ではなく、コピー元だった古いファイルの値を
表示し続けることがあります。
これは、コピー操作によって数式のテキスト自体（`=集計!A1` のような
同一ファイル内の参照）は正しく複製されているものの、
別ファイルを参照する関数（IMPORTRANGEなど）に埋め込まれた
ファイルIDやURLは、コピーしても書き換わらず、元ファイルを指したまま
残るためです。テンプレートを複製して使う運用をしている場合、
複製のたびに別ファイル参照の関数だけは参照先を貼り直す必要があります。
■ 同一ファイル内でシートを複製すると、参照が複製元を向いたまま残る
別ファイルへの移動だけでなく、同じファイルの中でシートを複製した場合にも
似た混乱が起きます。「集計」シートを右クリックして複製すると
「集計のコピー」というシートができますが、複製されたシートの中にある
`=元データ!A1` のような数式は、複製時にシート名が自動で書き換わる
わけではなく、複製元と同じ参照先（この例なら元データシート）を
指したままです。これは仕様であり壊れているわけではありませんが、
「複製したシートを別集計用に使い始めたのに、なぜか元の集計と
同じ値が出る」という症状の多くはこれが原因です。
複製後に参照先を変えたい場合は、検索と置換（Ctrl+H）で
数式内のシート名部分だけを一括置換するか、対象のセルを
選び直して手動で参照を張り直してください。ただし検索と置換は
数式のテキストそのものを書き換えるため、関係ない文字列
（たとえばシート名と同じ文字列がラベルとして入力されているセル）
まで置換してしまわないよう、対象範囲を絞ってから実行すると安全です。
■ 値がエラーにはならないが、古いまま変わらないとき
エラーは出ていないのに値が更新されない場合、
再計算のタイミングの問題であることがあります。
Googleスプレッドシートの既定の再計算頻度は「変更時」ですが、
ファイルの設定で「変更時と1時間ごと」のような頻度に変えていると、
参照元を更新してから最大でその間隔ぶん、参照先の表示が
追いつかないことがあります。
急いで最新の値を反映させたい場合、参照している数式が入った
セルを選択し、少しだけ内容を変えて元に戻す（例えばセルの末尾に
スペースを入れて消す）と、そのセルの再計算が強制され、
最新の値に更新されることがあります。ファイル全体を再読み込みする
方法でも同様の効果が期待できます。
■ やってはいけないこと
#REF! やエラー値を見た瞬間に、数式を消して同じ内容を打ち直すこと。
シート名の変更や権限の問題が原因の場合、打ち直しても同じ場所を
参照する限り同じエラーになります。まず「同一ファイル内のシート参照か、
別ファイルへの参照か」「シート参照かINDIRECTか」を切り分けてから、
該当する原因（シート名・権限・コピー元参照）を確認してください。
■ それでも解決しないとき
・シート名も権限も問題なさそうなのに、値が更新されない
・INDIRECTと通常のシート参照が混在していて、どちらが原因か
切り分けられない
・テンプレートの複製元をたどれず、どこを参照しているか
自分では特定できない
このあたりまで来たら、数式と参照先を1つずつ突き合わせた方が早いです。
私はスプレッドシートの参照まわりの調査と修正を承っています。
どの数式がどこを向いていたかを整理してお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
