Excelで別ブックへ数式をコピーしても元ブックを参照させない方法

Excelで別シートを参照する数式を別ブックへコピーすると、元ブックを参照する外部リンクが付くことがあります。コピー先ブック内のシートを参照させたい場合は、少数セルなら数式文字列をコピーし、複数セルなら対象範囲を限定して参照元のブック名を置換します。

コピー先ブックに参照先のシートと必要なデータがあることが前提です。たとえば=Sheet1!A1に直すと、コピー先のSheet1のA1を参照します。同名シートでも配置が異なれば別の値になるため、置換後は参照先と計算結果を確認してください。数式バーから文字列としてコピーする方法では、通常のセルコピーと異なり、貼り付け位置に合わせて相対参照は自動調整されません。

事象の整理

具体例を用いて事象を整理します。

コピー元ブックに(=Sheet1!A1)という数式があるとします。ここでのSheet1はコピー元ブックのSheet1を指しています。この数式を別のブックにコピー＆ペーストをすると(=[コピー元ブック.xlsx]Sheet1!A1)といったように、コピー元ブックのSheet1を参照する数式になります。まあ、論理的には良いんですが、、

パターン１：数式をコピーする

パターン１は、コピー元ブックの数式バーから数式をコピーしてコピー先ブックに貼り付けます。

１．コピー元ブックの数式バーで数式を選択して、Ctrl+C

２．Escキーを押下

３．コピー先ブックへ移動して、貼り付けたいセルを選択する。

４．数式バーを選択して　Ctrl+V

貼り付けた数式をEnterで確定し、コピー先ブックの意図したシート・セルを参照しているか確認します。参照先がない、または配置が違う場合は、シート名やセル番地を合わせてから計算結果を確認してください。

パターン2：複数セルは参照元のブック名を限定して置換する

複数セルをまとめて移す場合は、先にコピー先ファイルを別名保存してください。次にコピーした範囲だけを選択し、Ctrl + Hで［検索と置換］を開きます。

1. コピー後の数式バーを確認し、[コピー元ブック.xlsx]のような参照元の文字列を正確に控えます。
2. コピー先の対象範囲だけを選択します。シート全体やブック全体は選びません。
3. ［検索する文字列］へ控えたブック名を入力し、［置換後の文字列］は空欄にします。
4. 最初に［すべて検索］で対象を確認し、意図した数式だけなら置換します。

数式にフォルダーのパスまで含まれる場合や、複数の参照元が混在する場合は一括置換せず、数式バーで個別に直してください。リンクを値へ変える［リンクの解除］は数式自体を現在値へ置き換えるため、今回の目的とは異なります。

参照：ブック間リンクの仕組み、数式コピーと相対参照、ブックのリンクを管理する方法。リンク更新エラーが出る場合は、Excelの外部リンク更新エラーを直す手順も確認してください。