Excelで数式を別シートにコピーすると参照がずれる・元のブックを参照するときの対処

数式を別のシートやブックに貼り付けると、次のようなことが起きます。

• 違うセルを参照している
• 元のシートではなく、貼り付け先のシートの値が出る
• 数式に [元のブック.xlsx] のようなブック名が付く

どれもExcelのコピーの仕組みで起きる変化です。原因はおおむね3つに分かれます。どれに当たるかを先に見分けてから直します。

まず数式を見て原因を見分ける

コピー先のセルを選び、数式バーで実際の数式を確認します。F2キーで編集状態にすると、参照しているセルが色付きの枠で表示されます。同じシート内の参照なら、位置のずれも目で追えます。

1つのセルで複数の原因が重なることもあります。たとえば、別ブックにコピーした数式が外部参照になり、そのうえ位置もずれている状態です。

原因1：相対参照がコピー先の位置に合わせて動く

$ の付いていない参照（相対参照）は、「数式のあるセルから何行何列離れたセルか」として記録されています。C3の =A1 をE5に貼り付けると、2行下・2列右に動いた分だけ参照も動き、=C3 になります。別シートに貼り付けた場合も同じで、シートが変わっても位置の差はそのまま適用されます。

直し方は、何を固定したいかで選びます。

• 特定のセルを常に参照したい：数式の中で参照部分を選び、F4キーを押して切り替えます。$A$1 は行も列も固定、A$1 は行だけ固定、$A1 は列だけ固定です。
• 1つの数式を同じ文字のまま写したい：コピー元のセルを選び、数式バーで数式の文字列を選択してCtrl+Cでコピーします。Escで編集を抜け、貼り付け先のセルを選んで数式バーに貼り付けます。セルごとではなく文字列をコピーするので、参照は動きません。
• たくさんの数式を同じ文字のまま写したい：作業前にブックを保存しておきます。コピー元の範囲を選んで置換（Ctrl+H）を開き、検索する文字列に =、置換後の文字列に #= を指定して置換します（#= は数式の中に出てこない文字列なら何でもかまいません）。数式が文字列になるので、その範囲をコピーして貼り付けます。最後に、コピー元と貼り付け先の両方で #= を = に置換して戻します。

コピーではなく切り取り（Ctrl+X）で移動した場合、参照は元のセルを指したまま変わりません。ただし、元の場所から数式はなくなります。

原因2：シート名のない参照は貼り付け先のシートを指す

「集計」シートの =A1 は、集計シートのA1を参照しています。これを「報告」シートに貼り付けると、数式は =A1 のままですが、参照先は報告シートのA1になります。シート名のない参照は、常に「その数式が置かれているシート」のセルを指すためです。

貼り付け先から元のシートの値を表示したいなら、参照にシート名が必要です。

• 数式の入力中、= を入力した後に元のシートのタブをクリックしてセルを選ぶと、=集計!A1 のようにシート名付きで入ります。
• シート名に空白や記号が含まれると、='4月 売上'!A1 のように引用符で囲まれます。手で書くときは引用符を忘れないようにします。
• シート名付きでも、相対参照なら位置はずれます。固定したい場合は =集計!$A$1 のように $ も付けます。

逆に、元の数式に =集計!A1 のように自分のシート名まで入っていて、貼り付け先シートのセルを参照させたい場合もあります。このときは、貼り付けた範囲を選んで置換を開き、集計! を空欄に置き換えます。実行前に、置換ダイアログの「オプション」で検索場所が「数式」になっていることを確認します。

原因3：別ブックへのコピーで元のブックへの外部参照になる

別シートを参照している数式（例：=明細!B2）を別のブックに貼り付けると、Excelは元の参照先を保とうとします。その結果、数式は =[元.xlsx]明細!B2 のような外部参照に書き換わります。元のブックを閉じると、数式にフォルダーのパスも表示されます。シートを「移動またはコピー」で別ブックにコピーした場合も同じです。そのシートの数式が、一緒にコピーしなかったシートを参照していれば外部参照になります。

防ぎ方と直し方は次のとおりです。

• 参照し合うシートをまとめてコピーする：Ctrlキーを押しながら必要なシートのタブをすべて選び、まとめて「移動またはコピー」します。一緒にコピーしたシート同士の参照は、コピー先ブックの中の参照として扱われます。
• コピー先ブックの同じ名前のシートを参照させたい：元のブックを開いた状態で、貼り付けた範囲を選び、置換で [元.xlsx] を空欄に置き換えます。元のブックが閉じているとパスも数式に含まれ、置換する文字列が長くなって間違えやすくなります。
• 数式は不要で、値だけ渡したい：貼り付けるときに「値の貼り付け」を使います。数式が残らないので、外部参照も生まれません。

置換の後、'明細'!B2 のように引用符だけが残ることがあります。シート名が正しければ、そのままで問題なく動きます。

「リンクの編集」で参照先のブックを確認・変更する

外部参照がどこに残っているか分からないときは、ブックが参照している外部ファイルの一覧で確認します。

1. 「データ」タブの「クエリと接続」グループにある「リンクの編集」を開きます。Microsoft 365の新しい画面では、「ブックのリンク」という作業ウィンドウの場合があります。外部参照がなければ、ボタンが押せないか一覧が空です。
2. 一覧のファイル名と場所を見て、どのブックを参照しているか確認します。
3. 参照先を別のファイルに切り替えたいときは、そのリンクを選び、「ソースの変更」で正しいファイルを選びます。切り替え先のブックに同じ名前のシートがないと、数式がエラーになります。
4. 外部参照をやめて値として固定したいときは「リンクの解除」を使います。そのブックを参照している数式は、現在の値に置き換わります。元に戻す操作では戻せない場合があるので、実行前にブックを別名で保存しておきます。

ボタン名や配置は、Excelのバージョンや更新状況によって変わります。見当たらない場合は、使っているExcelのヘルプで「ブックのリンク」「外部参照」を検索してください。

リンクが消えないときに探す場所

数式の外部参照をすべて直しても一覧からリンクが消えない場合は、セル以外に参照が残っています。次の場所を確認します。

• セルの数式：Ctrl+Fで検索する文字列に [ または .xls を入れます。「オプション」で検索場所を「数式」、検索対象を「ブック」にして「すべて検索」します。
• 名前の管理：「数式」タブの「名前の管理」で、参照範囲にブック名やパスが入った名前がないか見ます。シートをコピーすると、名前も一緒に持ち込まれることがあります。
• 条件付き書式・データの入力規則：ルールや入力規則の参照先に、元のブック名が入っていないか確認します。
• グラフ・図形：グラフのデータ範囲や、図形に登録されたマクロが元のブックを指していないか確認します。

コピーした後の確認

• 先頭・途中・末尾のセルの数式バーで、シート名・ブック名が意図どおりか
• 「数式」タブの「参照元のトレース」の矢印が、想定したセルや別シートを指しているか
• 合計や件数など、元の表と一致するはずの値が一致しているか
• 保存して開き直したとき、リンクの更新を確認するメッセージが出ないか（出たら外部参照が残っています）

最後の確認は特に見落としやすいところです。自分のパソコンには元のブックがあるので、値は正しく表示されます。しかしファイルを他の人に渡すと参照先が見つからず、更新したときに値が崩れることがあります。