Excel約5分で読めます

Excelの数式をコピーしたら参照がずれる|$の付け方を結果で確かめる

Excelの数式をコピーしたら参照がずれるを表したイメージ
記事の内容を表したイメージ(AI生成)

数式をコピーしたとたんに結果がおかしくなる、あるいはエラーが出る。原因のほとんどは「参照がずれてはいけないセルに$が付いていないこと」です。$を付ける位置は列・行それぞれに決まりがあり、付ける場所を間違えると別の方向にずれが起きます。

この記事では、$を付ける三つの形(絶対参照・複合参照・相対参照)を比較し、コピー後の結果を架空のデータで確かめながら、どのセルにどの形を使えばよいかの判断基準を示します。数式を入れ直す前に、まずコピー前後の参照がどう変わっているかをセルの選択で確認してください。


参照が「ずれる」とはどういうことか

Excelは数式をコピーすると、参照先のセルアドレスを移動した分だけ自動で書き換えます。この仕組みを「相対参照」と呼びます。たとえばD2に =B2*C2 と入れてD3にコピーすると、自動的に =B3*C3 になります。縦にコピーすれば行番号が、横にコピーすれば列番号が変わります。

この仕組みは便利ですが、「ずらしたくないセル」が混在しているときに問題が起きます。消費税率を一か所に入れたセルや、表の見出し行を参照するセルは、コピーしても同じアドレスを指し続けなければなりません。そこに$を付けないと、コピー先で意図しないセルを参照してしまいます。

コピー後に結果がおかしいと気づいたら、まず問題のセルをクリックして数式バーを見てください。参照先のアドレスが想定と違っていれば、$の付け方を修正するだけで直ります。数式全体を書き直す必要はありません。


$を付ける三つの形と意味

画面の表示は版や設定により異なります。以下の説明は一般的なExcel・Googleスプレッドシートの動作を前提にしています。

相対参照($なし):B2

コピー先の移動量に合わせて、行も列も変わります。同じ計算を連続した行・列に繰り返す場合に使います。

絶対参照(行列とも$):$B$2

コピーしても行も列も変わりません。消費税率や単位換算の係数など、一か所に置いた固定値を参照するときに使います。

複合参照(行か列のどちらかに$):$B2 または B$2

$B2は列Bを固定し、行は変わります。横方向にコピーしても列がずれません。縦方向にコピーすると行は変わります。 B$2は行2を固定し、列は変わります。縦方向にコピーしても行がずれません。

複合参照は九九表のような縦横クロスの計算表を作るときに出番があります。縦のコピーで変えたい軸・固定したい軸を整理してから付けるとミスが減ります。


架空の例で結果を比較する

以下は架空の例です。

架空の在庫リスト(B列:数量、C列:単価、F1:消費税率)

表は横にスクロールできます

セル値
B210
B35
C2200
C3800
F10.1(消費税率10%)

D列に「税込金額(数量×単価×(1+消費税率))」を求めます。

$なしの場合(よくある失敗)

D2に =B2*C2*(1+F1) と入れてD3にコピーすると、D3は =B3*C3*(1+F2) になります。F2が空白なら (1+0)=1 と解釈され、消費税が0%になります。F2に別の値が入っていれば、そちらを参照してしまいます。

$を正しく付けた場合

D2に =B2*C2*(1+$F$1) と入れてD3にコピーすると、D3は =B3*C3*(1+$F$1) になります。B・C列の行番号はコピー先に合わせて変わり、F1だけ固定されます。D2の結果は 10×200×1.1=2,200、D3の結果は 5×800×1.1=4,400 になります。


$を付ける位置の判断手順

  1. 数式を入れるセルと、参照先セルの位置関係を確かめる。

参照先が「一か所の固定値(係数・税率・基準日など)」なら絶対参照($列$行)を使います。参照先が「同じ行の別列」や「同じ列の別行」なら相対参照を使い、コピー後に行・列が追随するようにします。

  1. コピーする方向を決める。

縦にコピーするとき、固定したい行には$を付け、追随させたい行には付けません。横にコピーするとき、固定したい列には$を付け、追随させたい列には付けません。

  1. F4キーで$を切り替える。

数式バーで参照アドレスの文字列(B2など)を選択した状態でF4を押すと、$B$2→B$2→$B2→B2の順に切り替わります。マウスで$を手打ちするより速く、付け忘れも減ります。Macの場合はCommand+Tで同様の操作ができますが、キーの割り当ては公式のヘルプで確認してください。

  1. コピー後に一つ目と最後のセルを選んで数式バーを確認する。

コピー直後に最初のセルと最後のセルを選択し、数式バーのアドレスが想定どおりかを目で確かめます。固定したかったアドレスが変わっていれば、元のセルに戻って$を修正してから再コピーします。


修正前にコピーを残す

$の位置を直すために数式を書き換える前に、シートをコピーして元の状態を別シートとして保存してください。Excelではシートタブを右クリックして「移動またはコピー」からコピーを作れます。修正が意図どおりでなかったときに、元のシートと見比べながら確認できます。

外部サービスや生成AIを使って数式の修正を試みる場合は、個人情報が含まれる名簿や顧客データを貼り付けないよう注意してください。架空のサンプルデータに置き換えてから相談する方法が安全です。


迷いやすい例外:複合参照が必要な場面

「縦にも横にもコピーする計算表」では、絶対参照も相対参照も合わない場面があります。

架空の例:行2〜6に担当者名、列B〜Eに月名を並べた勤務日数集計表(架空の例です)。F列に「各担当者の4か月合計」を求める数式ではなく、G2に「月ごと・担当者ごとの達成率(実績÷目標)」を計算し、縦横に展開したい場合を考えます。

目標値を1行目(B1〜E1)に置き、実績値をB2〜E6に置いたとします。G2に =B2/B$1 と入れると、縦にコピーしたとき行1が固定されたまま行2〜6の実績を割り算できます。横にコピーしたとき列Bが追随してC・D・Eに変わります。ここを =B2/$B$1 にすると横コピーでも列Bを固定したままになり、C〜E列の目標値を参照できなくなります。

このように「縦にコピーしたときに固定したい行」と「横にコピーしたときに固定したい列」が別々に存在するとき、複合参照を使います。混乱したら「コピーする方向に対して、追随させたい軸の$を外す」と覚えておくと整理しやすいです。


次の行動

まず、おかしくなっているセルを一つクリックして数式バーのアドレスを確認してください。固定したいはずのアドレスが変わっていれば、元のセルでそのアドレスを選択してF4を押し、$の形を変えてから再コピーします。修正前にシートをコピーして元の状態を残しておくと、比較しながら確認できます。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ