Excelでシートをコピーすると数式が元のブックを参照する・反映されない原因と直し方

スポンサーリンク

先月のブックから「集計」シートだけを、今月のブックへコピーした。ところが数式バーには ='[4月売上.xlsx]明細'!D10 と表示されている。Excelでシートをコピーすると、こんなふうに数式が元のブックを参照したままになることがあります。

困るのは、見た目の数字がそれらしく見えることです。今月の明細を入力しても、集計は先月の金額のまま動かない。ブックを開くたびにリンクの更新に関する警告も出て、元ファイルを消してよいのか判断がつきませんよね。

この記事では、まず数式バーの表示で症状を見分けます。そのうえで、元のブックへの参照を自ブックに付け替える3つの方法を紹介します。コピーしたのに結果が変わらない原因と、参照を壊さないコピーの順番もまとめました。

対応バージョン: Windows のデスクトップ版 Excel(Microsoft 365・2021 など)が前提です。Microsoft 365 の新しいビルドでは、「リンクの編集」が「ブックのリンク」に置き換わる場合があります。どちらもデータタブの「クエリと接続」グループにあります。Excel for the web には、別のブックへシートを移動・コピーするコマンドがありません。

最初に確認:Excelのシートコピーで数式が元のブックを参照しているか判定する

直し方は、数式バーに何が表示されているかで決まります。この記事では、次のブックを例に説明しましょう。

元ブック「4月売上.xlsx」には「明細」と「集計」の2シートがあります。「明細」シートのD10には4月の売上合計 1,250,000 が入っていて、「集計」シートの数式は次のとおりです。

集計!B2  =明細!D10   ← 明細シートの売上合計(1,250,000)
集計!B3  =B2*0.1     ← 消費税(125,000)
集計!B4  =B2+B3      ← 税込合計(1,375,000)

B2 は別のシートを見る「他シート参照」、B3 と B4 は同じシートの中だけを見る「自シート内参照」です。シートをコピーしたときに崩れやすいのは、B2 のような他シート参照のほう。

判定表:数式バーの表示で症状を見分ける

結果がおかしいセルを1つ選び、数式バーの表示を下の判定表と見比べてください。

数式バーの表示(集計!B2)状態該当する節
='[4月売上.xlsx]明細'!D10開いている元ブックを参照している症状1
='C:売上[4月売上.xlsx]明細'!D10閉じている元ブックをパス付きで参照している症状1
=明細!D10(複製した「明細 (2)」を見てほしいのに)同じブックの元シートを見続けている症状2の1つ目
参照先は正しいのに値が古い・変わらない手動計算か、リンクが未更新症状2の2つ目以降

見分けるコツは、数式の中に [ ] で囲まれたファイル名があるかどうか。[ ] があれば別のブックへの外部参照、なければ同じブックの中の参照です。

コピーのパターン別:コピー後の数式が何を指すか

同じ「集計」シートでも、どうコピーしたかで数式の行き先が変わります。B2(他シート参照)と B3(自シート内参照)を、コピーのしかた別に並べました。

コピーのしかたB2(他シート参照)B3(自シート内参照)
「集計」だけを別ブックへコピー='[4月売上.xlsx]明細'!D10=B2*0.1(コピー先のB2)
「集計」を同じブック内で複製=明細!D10(元の「明細」のまま)=B2*0.1(「集計 (2)」のB2)
「明細」と「集計」を一緒に別ブックへ=明細!D10(新ブックの「明細」を指すことが多い)=B2*0.1(コピー先のB2)

この表の3パターンでは、B3 はどれもコピー先のシートの中を指しています。行き先が変わるのは B2 の列だけです。

3行目の一括コピーは、環境によって結果が変わる可能性も残ります。コピーのあとは、数式バーに [ が付いていないかを必ず確かめてください。

症状1:別ブックへコピーしたら数式が元のブックを参照する

ここでは、5月分のブック「5月売上.xlsx」を例にします。このブックにはすでに5月の「明細」シートがあり、D10 の合計は 1,180,000 です。そこへ「4月売上.xlsx」から「集計」シートだけをコピーした場面で考えます。

原因:参照先シートが移動先になく、元ブックを指し続ける

集計!B2 の =明細!D10 は、「このブックの明細シート」を指す参照です。シートを別ブックへコピーしても、Excel は「4月売上.xlsx の明細」を見ていたという情報を手放しません。その結果、コピー後の数式は元ブック名の付いた外部参照になります。

コピー前(4月売上.xlsx の集計!B2)
=明細!D10

コピー後(5月売上.xlsx の集計!B2)
='[4月売上.xlsx]明細'!D10

4月売上.xlsx を閉じた後の表示(例)
='C:売上[4月売上.xlsx]明細'!D10

移動先に同じ名前の「明細」シートがあっても、そちらへは切り替わらないのが普通です。このままだと B2 は4月の 1,250,000 を拾い、B4 も 1,375,000 のまま。本来なら B2 は 1,180,000、B4 は 1,298,000 になるはずです。

Microsoft のサポート情報も、シートを別ブックへ移すと予期しない結果になることがあると注意しています。そのシートを参照する数式やグラフを確認するよう案内されているので、コピー後の点検は欠かせません。

直し方①:リンク元の変更で自ブックへ付け替える

自ブックに同じ名前の参照先シート(ここでは「明細」)があるなら、リンク元の変更が手早い方法です。ブック内にある「4月売上.xlsx」への参照を、まとめて自ブックへ向け直せます。

  1. 「5月売上.xlsx」を開きます。
  2. 「データ」タブの「クエリと接続」グループにある「リンクの編集」を選びます。
  3. 一覧から「4月売上.xlsx」を選び、「リンク元の変更」を押しましょう。
  4. ファイルを選ぶ画面で、いま開いている「5月売上.xlsx」自身を選んで「OK」を押します。
  5. 集計!B2 を選び、数式バーが =明細!D10 になったか確認します。

「ブックのリンク」作業ウィンドウが開いた場合は、「4月売上.xlsx」の横にある「その他のコマンド(…)」を開きます。参照元を変更するメニューを選び、最近使ったファイルか参照ボタンから自ブックを指定してください。

注意したいのは、自ブックを選ぶと「4月売上.xlsx」への数式リンクがすべて切れることです。4月のブックを見続けたいセルが残っているなら、この方法は向きません。1か所だけ変えたいときは、数式バーで直接書き換えるか、次の置換を使いましょう。

付け替え先に「明細」シートがないと、正しい参照にはなりません。参照先のシートを先に用意してから実行してください。

付け替えたのに一覧から元ブックが消えないなら、名前の定義やグラフなど、セル以外の場所にリンクが残っています。残ったリンクの探し方と、更新の警告を止める方法は「Excelで外部リンクの更新が毎回出る原因と解消法」で詳しく解説しています。

直し方②:Ctrl+Hの置換で[元.xlsx]を消す

置換は、特定のシートだけを直したいときに便利な方法です。数式の中の [4月売上.xlsx] という文字列を消すと、自ブックのシートへの参照に戻ります。

置換は一度に多くの数式を書き換えます。実行前に「名前を付けて保存」でブックを別名保存しておきましょう。失敗しても元に戻せます。

  1. 直したいシート(ここでは「集計」)を開きます。
  2. Ctrl+H を押して「検索と置換」の「置換」タブを開きます。
  3. 「オプション」を押し、「検索場所」を「シート」(ブック全体を直すなら「ブック」)にしてください。
  4. 「検索する文字列」に、数式バーに出ている表記をそのまま入れます。
  5. 「置換後の文字列」は空欄のまま、「すべて置換」を押します。

検索する文字列は、元ブックを開いているか閉じているかで変わります。

■ 4月売上.xlsx を開いているとき
検索する文字列: [4月売上.xlsx]
置換後の文字列: (空欄)
置換前: ='[4月売上.xlsx]明細'!D10
置換後: ='明細'!D10

■ 4月売上.xlsx を閉じているとき
検索する文字列: C:売上[4月売上.xlsx]
置換後の文字列: (空欄)
置換前: ='C:売上[4月売上.xlsx]明細'!D10
置換後: ='明細'!D10

置換後はシート名を囲む ' が残ることもありますが、正しい書き方なのでそのままで大丈夫。大事なのは、検索する文字列を数式バーの表記と1文字も違わずに合わせることです。パスの部分は環境ごとに異なるので、検索前に数式バーで確かめておきましょう。

直し方③:参照し合うシートをまとめてコピーし直す

元ブックがまだ手元にあり、コピーをやり直せるなら、この方法がすっきりします。参照し合っている「明細」と「集計」を一度に選んでコピーすれば、参照がコピー先のブックの中で完結しやすくなります。

まず、外部参照になってしまった「集計」シートをコピー先から削除します。そのうえで、元ブックの「明細」と「集計」を Ctrl キーで同時に選び、一度の「移動またはコピー」で送り直してください。具体的な手順は、後半の「参照を壊さないコピーの順番チェックリスト」で紹介します。

この方法は、移動先にまだ「明細」シートがない場合に向いています。同じ名前のシートがあるブックへ送ると、コピーした側は「明細 (2)」のような別名になるためです。今回の「5月売上.xlsx」のように同名シートがあるなら、直し方①か②を選びましょう。

元ファイルを消す前の注意と、直し方の選び方早見表

外部参照が残ったまま「4月売上.xlsx」を削除・移動・名前変更すると、リンク元が見つからなくなります。セルには保存済みの値が残っていても、それ以降は更新できません。元ファイルを整理する前に、「リンクの編集」(または「ブックのリンク」)の一覧が空になったかを確認してください。

同じブックの中でも、参照先のシートを削除すると数式は #REF! になります。#REF! の原因と戻し方は「Excelの#REF!エラーの原因と直し方」にまとめています。

どの直し方を選ぶかは、次の早見表で決めてください。

状況向いている直し方注意点
自ブックに同名の参照先シートがあり、数式を残したい① リンク元の変更元ブックへの数式リンクがすべて切れる
一部のシートだけ直したい・元ブックへの参照を一部残したい② 置換(検索場所「シート」)検索する文字列を数式バーの表記に合わせる
元ブックが手元にあり、移動先に同名シートがない③ まとめてコピーし直すコピー後に [ が付いていないか確認する
元ファイルがもうない、または値だけでよい値で貼り付ける・リンクの解除数式が値に置き換わり、元には戻せない

症状2:コピーしたのに結果が変わらない・反映されない

数式バーに [ ] は出ていないのに、データを変えても結果が動かない。この場合に考えられる原因は4つです。上から順に確認すると、早く切り分けられます。

原因:同じブックで複製しても他シート参照は元シートを見続ける

5月分を作るために、「4月売上.xlsx」の中で「明細」と「集計」を1枚ずつ複製したとします。できあがるのは「明細 (2)」と「集計 (2)」。ところが「明細 (2)」を5月のデータに書き換えても、「集計 (2)」の B2 は 1,250,000 のまま変わりません。

集計 (2)!B2  =明細!D10   ← 複製元の「明細」(4月分)を見ている
集計 (2)!B3  =B2*0.1     ← 「集計 (2)」のB2を見ている

シートを1枚ずつ複製すると、他シートへの参照は元のシートを指したままになります。一方で B3 のような自シート内参照は、複製先の「集計 (2)」の中を指すのが特徴です。

直すには、先に「明細 (2)」の名前を「5月明細」に変えておきます。次に「集計 (2)」を開いて Ctrl+H を押し、「明細!」を「’5月明細’!」に置換してください。

置換前: =明細!D10        → 1,250,000(4月分)
置換後: ='5月明細'!D10   → 1,180,000(5月分)
B4(税込合計)は 1,375,000 → 1,298,000 に変わる

このとき「検索場所」は必ず「シート」にします。「ブック」にすると、4月の「集計」シートの数式まで書き換わってしまうからです。

数字で始まるシート名や、空白・かっこを含むシート名は、数式の中で ' で囲む決まりがあります。「明細 (2)」のまま使う場合も、置換後の文字列は '明細 (2)'! のように囲みましょう。

なお、複数シートをまとめて同じブック内にコピーしたときの参照先は、環境によって異なる可能性があります。複製したあとは、数式バーで参照先のシート名を確かめましょう。

原因:手動計算モードになっている

参照先は正しいのに数値が変わらないなら、計算方法が「手動」になっていないか確認します。「数式」タブの「計算方法の設定」を開き、「自動」になっていなければ切り替えてください。

手動のままでも、F9 キーを押せばその場で再計算できます。手動になる原因や、設定が戻ってしまうときの対処は「Excelの計算式が自動計算されない原因と直し方」で解説しています。

原因:リンクが未更新で古い値のまま

症状1の外部参照を残したまま使っている場合は、リンクの更新状態を疑いましょう。元ブックを閉じたままコピー先のブックを開くと、リンクの自動更新が無効にされている旨の警告が出ることがあります。ここで更新しないと、前回保存したときの値が表示されたままです。

「リンクの編集」の「状態の確認」を押すと、リンクごとの状態がわかります。値が更新されていなければ「値の更新」を押してください。元ブック側が再計算されていない状態なら、元ブックを開いて F9 で再計算してから更新します。

更新するときは、元ブックをすべて閉じておくのが無難です。一部の元ブックだけを開いていると、更新結果がそろわないことがあります。

原因:新しいブックの計算方法を引き継ぐ場合(環境により)

「移動またはコピー」で新しいブックを作った直後は、計算方法の設定にも注意が必要です。デスクトップ版の Excel では、計算方法の変更は開いているすべてのブックに影響するとされています。さらに、最初に開いたブックに保存された設定が使われる、とも説明されています。

たとえば、手動計算にしてある重いブックを先に開いていたとしましょう。その状態でシートを新しいブックへコピーすると、新しいブックも手動計算で動くことがあります。コピー後に「計算方法の設定」を一度確認しておくと安心です。

参照を壊さないコピーの順番チェックリスト

シートを別のブックへ移すときは、次の順番で進めると外部参照を作りにくくなります。

順番やること理由
1移動先のブックを開いておく「移動先ブック名」の一覧に出すため
2元ブックで名前の管理と外部参照を確認する隠れたリンクや名前の重複を持ち込まないため
3参照し合うシートを Ctrl キーで同時に選ぶ参照をコピー先の中で完結させるため
4「移動またはコピー」で一度に送る1枚ずつ送ると他シート参照が外部参照になるため
5コピー先で数式バーに [ がないか確認する外部参照が残っていたら置換で直すため
6別のシート見出しをクリックして選択を解除する複数選択のままだと入力が全シートに入るため
7「計算方法の設定」が「自動」か確認する手動計算を引き継いでいないか確かめるため

Ctrlを押しながら複数シートを選び一括移動する

「明細」と「集計」を新しいブックへまとめてコピーする手順です。

  1. 元ブック「4月売上.xlsx」で「明細」のシート見出しをクリックします。
  2. Ctrl キーを押しながら「集計」のシート見出しをクリックします。
  3. 選んだ見出しのどちらかを右クリックし、「移動またはコピー」を選びます。
  4. 「移動先ブック名」で「(新しいブック)」か、開いておいたブックを選んでください。
  5. 「挿入先」でシートを入れる位置を選びます。
  6. 「コピーを作成する」にチェックを入れて「OK」を押します。

隣り合うシートなら、先頭の見出しをクリックしたあと Shift キーを押しながら最後の見出しをクリックしても選べます。チェックを入れずに「OK」を押すと、コピーではなく移動になるので気をつけてください。

コピーが終わったら、集計!B2 の数式バーが =明細!D10 になっているかを確認します。[4月売上.xlsx] が付いていたら、直し方②の置換で消しましょう。

コピー前に名前の管理と外部リンクを確認する

数式以外の場所に元ブックへの参照が隠れていると、コピー先にもそのまま持ち込まれます。名前の定義、グラフの系列、条件付き書式、データの入力規則などが代表的な場所です。

コピー前に Ctrl+F3 で「名前の管理」を開き、「参照範囲」の列を確かめましょう。[ ] 付きのファイル名や #REF! があれば要注意です。不要な名前を消しておくと、コピー時の名前の重複も防ぎやすくなります。

コピーの途中で「名前は既に存在します」という確認が出たら、「Excelでシートをコピーすると「名前は既に存在します」と出る原因と消し方」を参考にしてください。

元ブックで Ctrl+F を押して [ を検索すると、外部参照を含む数式を探せます。「検索場所」は「ブック」、「検索対象」は「数式」にするのがコツです。

よくある質問(FAQ)

Web版でも同じ操作ができる?

Excel for the web には、デスクトップ版のように別のブックへシートを移動・コピーするコマンドがありません。Microsoft のヘルプでは、データをすべてコピーし、別ブックの新しいシートに貼り付ける手動の方法が案内されています。この方法では条件付き書式が失われる点にも注意が必要です。

同じブック内なら、シート見出しの右クリックから「複製」を使えます。ただし、グラフや図形を含むシートではエラーになることがあるとされています。

Web 版のブックリンクは、「ブック リンクの信頼」を選ばないと更新されません。選ぶまでは、保存済みの値が表示され続けます。

値だけ欲しいときは?

数式を残す必要がないなら、値で貼り付けるのがいちばん簡単です。コピーしたいセル範囲を選んで Ctrl+C を押し、貼り付け先で「形式を選択して貼り付け」から「値」を選びます。

値には参照が含まれないので、元ブックを消しても数字は変わりません。そのかわり、元のデータを直しても自動では反映されなくなります。月次の締めのように、数字を確定させたい場面に向いた方法です。

元ブックを開かずに更新できる?

できます。元ブックを閉じたまま、警告の更新ボタンや「リンクの編集」の「値の更新」で最新の値を取り込めます。

ただし、元ブックが手動計算のまま保存されていると、再計算されていない値を拾うことがあります。その場合は元ブックを開いて F9 を押し、保存してから更新してください。

INDIRECT 関数で別ブックを参照している場合は話が別です。元ブックを開いていないと値を取得できず、#REF! になります。INDIRECT の仕組みは「ExcelのINDIRECT関数|別シート参照と#REF!の5原因」を参照してください。

まとめ

Excel でシートをコピーして数式が元のブックを参照するのは、他シート参照の行き先がコピー先にないためです。数式バーに [4月売上.xlsx] のようなファイル名が出ていたら、症状1の直し方で自ブックへ付け替えましょう。

  • 自ブックに同名の参照先シートがあるなら、リンク元の変更でまとめて付け替える
  • 一部のシートだけ直すなら、Ctrl+H で [元のブック名.xlsx] を空欄に置換する
  • やり直せるなら、参照し合うシートを Ctrl で同時に選び、一度にコピーし直す
  • 元ファイルを消す前に、「リンクの編集」の一覧が空になったか確認する

数式バーに [ ] がないのに結果が変わらないときは、同じブックで複製した参照が元シートを見ていないか確認します。続いて手動計算、リンクの未更新の順に疑ってください。

次にシートを移すときは、参照し合うシートをまとめて選ぶことから始めてみてください。コピー後に数式バーを1回見るだけで、先月の数字を拾い続けるミスはぐっと減らせます。

タイトルとURLをコピーしました