Excelで数式をコピーしたら参照先がずれる|絶対参照・相対参照とF4の使い分け

IT・プログラミングの仕事と学習

「Excelの計算式を下にコピーしたら税率のセルまで1行ずつずれた」

「F4で固定できると聞いたけれど、どこに$を付ければよいの?」

Excelで数式をコピーしたときに参照先が変わるのは通常の動作です。ところが全行で同じ税率や係数を使いたい場面では、変わってほしくないセルまで移動して計算結果が0になったり金額が間違ったりします。

Excelの数式をコピーすると、既定の相対参照はコピーした方向と距離に合わせて変わります。 全行で共通のセルを参照し続けたい場合は、絶対参照の$記号を使います。

例えば数量がA列、単価がB列、全行で共通の係数がF1にある場合です。C2へ =A2*B2*F1 と入力してそのまま下にコピーすると、F1がF2、F3へずれてしまいます。

正しくは =A2*B2*$F$1 のように係数だけを固定します。A2とB2はそのままにするので、下へコピーしたときにA3・B3へ変わります。

この記事では4種類のセル参照、F4キーの切り替え、縦・横へのコピー、切り取りによる移動との違い、修復後の検算方法を具体例で説明します。

結論|動かしたい参照と固定したい参照を分ける

最初に数式の中のセルを用途別に分けると判断しやすくなります。

使う値 目的 書き方の例
行ごとに異なる数量 コピー先の行に合わせて変える A2
行ごとに異なる単価 コピー先の行に合わせて変える B2
全行で共通の税率・係数 コピーしても変えない $F$1
同じ列の見出しや基準値を使う 列だけ固定する場合 $A2
横にコピーしても同じ行を使う 行だけ固定する場合 B$2

$は固定したい列のアルファベットと行番号の直前に付けます。

$A$1は列Aも行1も固定、$A1は列Aだけ固定、A$1は行1だけ固定です。

コピー後に正しい数式へ変わったかは数式バーで確認してください。

まずは間違いが起きる売上表の例を確認

係数を掛けた金額を計算する小さな表で考えます。

使う表と共通係数

セル 項目 値
A2 商品Aの数量 3
B2 商品Aの単価 1,000
A3 商品Bの数量 2
B3 商品Bの単価 1,500
A4 商品Cの数量 5
B4 商品Cの単価 800
F1 全商品共通の係数 1.1

C列に係数を掛けた金額を表示する例です。ここでの1.1は計算を説明するための架空の係数であり、特定の税務上の扱いを示すものではありません。

C2の期待値は3×1,000×1.1で3,300です。

間違った式を下にコピーした場合

C2へ次のように入力します。

=A2*B2*F1

C3へコピーすると、Excelは相対参照を1行下へ調整します。

=A3*B3*F2

F2は空白なので計算結果が0になる場合があります。C4ではF3を参照します。

計算セル コピーで作られた式 係数の参照先 結果の例
C2 =A2*B2*F1 F1 3,300
C3 =A3*B3*F2 F2 0
C4 =A4*B4*F3 F3 0

この場合は数量や単価の参照が壊れたわけではありません。全商品共通のF1だけが移動したことが原因です。

正しい式は係数だけ絶対参照にする

C2の式を次のように直します。

=A2*B2*$F$1

これを下へコピーした場合です。

計算セル 修正後の式 期待する金額
C2 =A2*B2*$F$1 3,300
C3 =A3*B3*$F$1 3,300
C4 =A4*B4*$F$1 4,400

F1の参照だけは同じまま残り、数量・単価は各行の値に切り替わります。

すべての参照へ$を付けるのではなく、固定する必要があるF1にだけ付けることが重要です。

相対参照とは|A1はコピーに合わせて動く

Excelの数式は相対参照が基本

Microsoft公式サポートでは、セル参照は既定で相対参照になると説明しています。

例えばC2に次の式を入力します。

=A2*B2

この式をC3へコピーすると次のように変わります。

=A3*B3

コピー先が1行下になったため、式の参照先も1行下へ移動したのです。

下へのコピーと右へのコピー

縦だけでなく横へコピーしたときも同じ考え方です。

元の参照 コピーする方向 コピー後
A2 1行下 A3
B2 2行下 B4
A2 1列右 B2
A2 2列右 C2
A2 1行下・1列右 B3

これはすべてコピー元の位置を基準にした例です。

「A2」がどんなセルから参照されていても、数式を移す方向と距離に応じて変わるという考え方を押さえましょう。

相対参照が正しい場合もある

売上表で毎行の数量と単価を掛けるなら、下方向に参照が動くことは必要です。

絶対参照を付けすぎるとどの行でも同じ数量・単価を参照するため、別の誤りが起こります。

動いたこと自体が問題なのではなく、動くべきでないセルまで動いたことが問題です。

絶対参照とは|$A$1で列と行を両方固定する

$記号を付ける位置

例えばA1を固定したいなら、次のように記述します。

$A$1

Aの前の$が列A、1の前の$が行1を固定します。

同じ数式を下へコピーしても右へコピーしても参照先はA1のままです。

税率・係数・換算レートに使う

全行で共通の値を使う場面が典型例です。

先ほどの売上表ではF1だけを固定しました。

=A2*B2*$F$1

数量A2と単価B2は各行へ変わり、F1だけが固定されます。

固定したセルの値は変更できる

絶対参照はセルの中の値を変更できないようにする機能ではありません。

例えばF1の係数を1.1から1.05へ変更すると、F1を参照する数式はその値を使って再計算されます。

ここでの固定とはコピーしたときの参照先の位置を固定することです。

シート保護とも違う

絶対参照を付けてもセルが編集禁止になるわけではありません。

他人に税率セルを変更されたくない場合は、別のシート保護や入力権限を検討する必要があります。

絶対参照と編集の保護は分けて考えましょう。

複合参照とは|$A1とA$1は固定する方向が違う

縦横の表に数式を広げる場合は、列か行のどちらか一方だけ固定すると便利です。

$A1は列Aだけ固定する

$A1 はA列を固定し、行番号はコピーに合わせて動きます。

  • 1行下へコピー:$A2
  • 2行下へコピー:$A3
  • 1列右へコピー:$A1

「各行の値は変えたいが必ずA列を参照したい」場合に使います。

A$1は行1だけ固定する

A$1 は行1を固定し、列のアルファベットはコピーに合わせて変わります。

  • 1行下へコピー:A$1
  • 1列右へコピー:B$1
  • 2列右へコピー:C$1

「横へコピーして列ごとの見出し値を使いたいが参照する行は変えたくない」場合に使います。

4種類を同じ移動距離で比較

式の中の参照部分を2行下・2列右へコピーした場合の違いです。

元の参照 固定する部分 2行下・2列右へコピーした結果
A1 なし C3
$A$1 列Aと行1 $A$1
$A1 列A $A3
A$1 行1 C$1

Microsoft公式でもこの4つの参照形を説明しています。

$の位置を覚えると「動いてよい方向」が分かります。

F4キーで参照の種類を切り替える

Windows向けExcelでは数式を編集中にセル参照を選び、F4キーで参照の種類を切り替えられます。

STEP1:数式を入力したセルを選ぶ

例えばC2に =A2*B2*F1 と入力されている場合です。

C2を選択して数式バーをクリックするか、F2キーで編集状態にします。

STEP2:変更したい参照部分へカーソルを置く

式のF1の部分を選択するかカーソルをその参照に置きます。

数量のA2と単価のB2は変えたくないため選びません。

数式全体を一括で固定するのではなく、係数F1だけを指定することがポイントです。

STEP3:F4キーを押して絶対参照へ

F1を選んだ状態でF4キーを押します。

F1 → $F$1

通常はこのように列と行が固定された形へ変わります。

さらにF4を押すと固定する部分が切り替わります。

F4を押した回数と参照形式

通常のWindows版Excelで相対参照A1から始めた例です。

操作 参照の表示 状態
初期状態 A1 相対参照
F4を1回 $A$1 列と行を固定
F4を2回 A$1 行だけ固定
F4を3回 $A1 列だけ固定
F4を4回 A1 相対参照へ戻る

セル参照以外を選択しているとF4が別の動作になる場合があります。

Microsoftのキーボードショートカット一覧では、参照選択時のF4は参照種別を切り替え、それ以外の場面では直前の操作を繰り返すことがあると説明されています。

STEP4:Enterで数式を確定する

目的の参照になったらEnterで数式を確定します。

C2の式が次のようになっているか確認しましょう。

=A2*B2*$F$1

係数F1だけに$が付いていることが重要です。

F4で変わらない場合はFnキーも試す

ノートPCによってはF4が音量や画面などの機能キーに割り当てられています。

その場合はFn+F4で参照の切り替えができる場合があります。

キーの設定はPCメーカーや機種によって異なります。

F4が使えない環境なら数式バーで$を直接入力しても構いません。

下方向へコピーする手順

STEP1:先頭の式だけを正しい形にする

C2を修正して =A2*B2*$F$1 とします。

この時点でC2の結果を確認しましょう。

STEP2:フィルハンドルで下にコピー

C2を選択し、セル右下の小さな四角を下の行までドラッグします。

Excelは数式の相対参照をコピー先に合わせて調整します。

Ctrl+CとCtrl+Vでコピーする方法もあります。

STEP3:数式バーで途中と最後の式を比較する

C3を選んだとき =A3*B3*$F$1 になっているか確認します。

C4では =A4*B4*$F$1 になるはずです。

全行のF1だけが変わっていないことを確かめましょう。

STEP4:値まで検算する

数量と単価が次の値なら結果を手計算で比べます。

計算する行 数量 単価 係数 期待値
2行目 3 1,000 1.1 3,300
3行目 2 1,500 1.1 3,300
4行目 5 800 1.1 4,400

重要な金額は1行だけでなく途中と末尾も確認してください。

右方向へコピーする場合の複合参照の例

縦横に計算式をコピーする表では複合参照を使います。

行に商品の金額、列に異なる係数がある場合

例えばA列に元の金額が入っています。

  • A3:1,000
  • A4:2,000
  • B2:0.05
  • C2:0.08
  • D2:0.10

B3・C3・D3には同じ行の金額へ列ごとの係数を掛けたいとします。

B3に入れる式

=$A3*B$2

$A3は列Aだけ固定し、B$2は行2だけ固定しています。

この式を右へコピーすれば係数の列は移動します。

C3とD3の式

C3へコピーすると次の式になります。

=$A3*C$2

D3へコピーすると次の式です。

=$A3*D$2

商品金額はA3のまま、係数はB2→C2→D2と変わります。

下へコピーするとどうなる?

B4へコピーすると次の式です。

=$A4*B$2

商品金額はA4に変わりますが係数の行2は固定されたままです。

右と下のコピー結果を比較

貼り付け先 式 計算結果
B3 =$A3*B$2 50
C3 =$A3*C$2 80
D3 =$A3*D$2 100
B4 =$A4*B$2 100
C4 =$A4*C$2 160
D4 =$A4*D$2 200

$A3とB$2を組み合わせることで、右へも下へも同じ式の構造を保ってコピーできます。

ただし表の設計によって固定する行・列は異なります。意味を確認して使いましょう。

SUM関数でも相対参照と絶対参照を使う

四則演算だけでなくSUMなどの関数でも参照のルールは同じです。

コピーに合わせて集計する列を変えたい

例えば次の式を考えます。

=SUM(B2:B5)

これを1列右へコピーすると通常は次のようになります。

=SUM(C2:C5)

これは列ごとの合計を計算したい場合に便利です。

どこへコピーしても同じ範囲を集計したい

同じB2:B5を参照し続けたいなら次の式にします。

=SUM($B$2:$B$5)

この式を右へコピーしても参照範囲は変わりません。

固定しすぎると問題になる

本来は売上の各列を集計したいのに、すべて$B$2:$B$5に固定するとどの列にも同じ合計が表示されます。

コピー先ごとに参照すべき値が違うのか、常に同じ値なのかを決めましょう。

数式をコピーするときと切り取って移動するときは違う

Microsoft公式の「Excelで数式を移動またはコピーする」では数式のコピーと移動を分けて説明しています。

コピーは相対参照が移動する

C2からC3へ式をコピーした場合は、A2やB2などの相対参照がA3やB3へ調整されます。

数式を行ごとに展開したいときの動作です。

切り取って移動する場合

C2の数式をCtrl+Xで切り取り、別のセルへCtrl+Vで移動する操作はコピーとは異なります。

Microsoftは移動した数式が元の参照先を指し続けることを説明しています。

コピーと切り取りは同じ結果になるとは限りません。

たとえばC2の =A2*B2 をC3へ切り取って移動すると、元のA2・B2を参照し続ける動作が基本です。

ただし表の構造やほかの数式への参照関係も変わる場合があります。

数式内の文字だけをコピーする操作は別

セルをコピーして貼り付ける場合と、数式バーから数式の文字列をコピーして貼り付ける場合でも扱いが異なる場合があります。

意図した参照になっているかを貼り付け後に確認しましょう。

コピーしたのに参照が動かない場合

式に$が付いているか、値だけ貼り付けていないかを確認します。

また数式をコピーしたのか、セルを切り取って移動したのかも整理してください。

原因を特定できていない状態で大量の数式を置き換えないようにしましょう。

間違った式を安全に修復する手順

STEP1:共有ブックなら修正前に確認する

会社の売上表や見積計算書はほかの担当者と共有している場合があります。

作業用コピーを保存し、どの範囲を修正するか確認しましょう。

OneDriveやSharePointで共同編集している場合はほかの人の修正と競合しないように注意してください。

STEP2:先頭の正しい計算式を作る

まず原因になった共通参照を探します。

例えばC3の式が =A3*B3*F2 なら、F2へ移動してよい理由があるか確認しましょう。

全行共通の係数がF1なら、C2で =A2*B2*$F$1 と修正するのが分かりやすい方法です。

STEP3:1行だけコピーして試す

いきなり数百行へコピーするのではなく、C3までコピーして式を確かめましょう。

C3が =A3*B3*$F$1 なら、動く参照と固定参照を区別できています。

STEP4:必要な範囲だけへ展開

正しいことを確かめたら予定の行数までコピーします。

別の部署の表や集計行まで上書きしないようにしてください。

データ範囲の最終行を確認しましょう。

STEP5:数式と結果の両方を照合する

数式バーで参照先を確認し、元データで期待値を手計算します。

エラーが消えたり0でなくなったりしただけでは正しい計算か確定しません。

正しいセルを参照し、正しい結果が出ていることまで確認しましょう。

よくある失敗と確認項目

症状 原因の候補 見直す箇所
下の行だけ0になる 共通係数の参照が空白へ移動 F1→F2などの変化
コピーした全行が同じ金額になる 数量や単価まで絶対参照にした $A$2や$B$2が必要か
右へコピーすると基準列が変わる 列を固定していない $A3などの複合参照
下へコピーすると見出し行が変わる 行を固定していない B$2などの複合参照
F4を押しても$が付かない 編集位置やFnキーの問題 数式内の参照を選択
数式の移動後に結果が変わらない 切り取りとコピーを混同 操作履歴と参照
#REF! が表示される 削除などで参照先が無効 セル構造と参照先
コピー後に別のシートへ移った シート名や参照の設定 数式バーで参照元を確認

F4を押したら別の操作が実行された

F4は数式を編集して参照を選んでいるときと、通常の操作中では働きが変わる場合があります。

セル内や数式バーで対象参照を選択してください。

Fnキーが必要な機種もあります。

数式が0のままなら計算設定も確認する

参照先を修正しても表示が更新されない場合はExcelの計算方法が手動になっている可能性もあります。

これは参照先がずれる問題とは別です。

数式の中身が正しいのに表示結果が古い場合は計算設定を確認しましょう。

#REF! は別の問題

参照先のセルや列を削除した場合は#REF!が表示されることがあります。

$を付けるだけでは削除済みのセルを復元できません。

まず何を削除したかを確認する必要があります。

行や列の挿入でも絶対参照は固定のまま?

$A$1はコピー時の位置調整を抑える指定です。

表に行や列を挿入したときには、参照していた元のセルを追いかけるように参照が更新される場合があります。

$はシートの構造変更まで一切動かさないという意味ではありません。

コピーの固定と、行・列の挿入や削除の影響は分けて考えましょう。

右方向・下方向の検算チェックリスト

  • コピー元の式を確認した
  • 行ごとに変わる数量・単価は相対参照である
  • 全行で共通の係数や税率は絶対参照である
  • 横にコピーして残す列は$A3などで固定した
  • 縦にコピーして残す行はB$2などで固定した
  • F4を押すときは目的の参照部分を選んだ
  • 数式のコピーと切り取りによる移動を区別した
  • コピー先の途中と最終行の式を確認した
  • 見積・売上の元データから金額を計算し直した
  • 数式が0になった原因を確認した
  • 共有の原本を変更する前に作業用コピーを保管した
  • ほかの部署や合計行を上書きしていない
  • 変更前後で元の計算が意図せず変わっていない

たとえば100行の表なら2行目・50行目・100行目などを抜き出し、式の参照と計算結果を確認する方法があります。

ただし検算する行数や抽出方法は重要度に応じて増やしてください。

よくある質問

Q. Excelの数式をコピーすると参照が勝手に変わるのはなぜ?

相対参照がExcelの標準だからです。

行や列の方向に合わせて参照が移動します。

共通のセルを固定したい場合は$で絶対参照にします。

Q. セル参照を変えずにコピーするには?

特定のセルを固定するなら$F$1のように列と行を固定してください。

ただし数式内のすべてを固定すると行ごとの数量まで同じセルを参照してしまう場合があります。

Q. F4キーは何回押せばよい?

通常は最初の1回でA1から$A$1になります。

さらに押すとA$1、$A1、A1の順に切り替わります。

使用する参照の種類になったところで止めてください。

Q. ノートPCでF4が効かない場合は?

Fn+F4が必要な機種があります。

数式内の対象参照を選んでいるかも確認しましょう。

必要なら$を直接入力できます。

Q. $A1とA$1はどちらが列固定?

$A1は列Aを固定します。

A$1は行1を固定します。

$が付いている文字・数字が固定されると考えましょう。

Q. コピー先が0になるのはなぜ?

係数や税率など共通の参照が空白セルへ移った可能性があります。

元の式とコピー先の式を数式バーで比較してください。

ただし0は別の原因でも起こるため実際の参照先を確認しましょう。

Q. Ctrl+CとCtrl+Xで参照は同じ動きですか?

同じではありません。

数式のセルをコピーすると相対参照は調整されます。

切り取りによる移動は元の参照先を保つ動作が基本です。

Q. コピーしたすべての行が同じ値になります

数量や単価まで$で固定している可能性があります。

行ごとに変えるべき参照がA2・B2などの相対参照になっているか確認してください。

Q. SUMでも絶対参照を使えますか?

使えます。

SUM($B$2:$B$5)のように範囲を固定すればコピーしても同じ範囲を参照できます。

列ごとの合計を出すなら相対参照のほうが適切な場合があります。

Q. Excel for the webやMacでもF4の手順は同じですか?

環境によってキー操作が異なる場合があります。

この記事はWindows版Excelを中心に説明しています。

必要なら利用中のExcelの公式ショートカットを確認してください。

まとめ|コピーで動かすセルと固定セルを分ける

Excelで数式をコピーして参照先が変わるのは通常の動作です。

数量・単価など行ごとに変える値はA2・B2という相対参照にし、全行共通の係数は$F$1のような絶対参照にします。

=A2*B2*$F$1 とすれば下へコピーしたときに数量・単価だけが次の行へ移動します。

横へコピーする表では$A3の列固定とB$2の行固定を組み合わせる方法もあります。

数式編集時はF4で参照種別を切り替えられます。ノートPCではFn+F4が必要なこともあります。

最後に元の式とコピー後の式を比較し、代表的な行を手計算で検算しましょう。

$を付けたことではなく、意図した参照先と正しい計算結果を確認できたことを修正完了の条件にしてください。

参考資料・Microsoft公式サポート

※Microsoft公式資料の確認日:2026年10月28日。画面・ショートカット・キー割り当てはExcelの版やPC機種によって異なる場合があります。