Excelで数式をコピーしたら参照先がずれて、計算結果までおかしくなった経験はありませんか？これはExcelがコピー先に合わせてセル参照を自動調整するためです。売上表や集計表では便利な機能ですが、税率・単価・固定条件などを参照したい場面では、参照を固定する必要があります。
結論：固定したいセル参照に「$」を付け、F4キーで切り替えます
たとえば、A2の金額にD1の税率を掛ける数式を下へコピーする場合、=A2*$D$1と入力します。A2はコピーに合わせてA3、A4へ変わりますが、$D$1はどこへコピーしてもD1のままです。
- $A$1：列Aと行1を両方固定します。
- $A1：列Aだけを固定し、行番号はコピー先に合わせて変えます。
- A$1：行1だけを固定し、列記号はコピー先に合わせて変えます。
- A1：列・行とも固定しない通常の参照です。
数式コピーでずれないように固定する操作手順
すでに数式を入力している場合は、数式バーで参照を選んでF4キーを使う方法が最も速くて確実です。
- 数式が入っているセルをクリックします。例として、E2に「=C2*D1」と入力されている状態を想定します。
- 数式バー、またはセルをダブルクリックして編集状態にします。
- 固定したい参照部分の「D1」をドラッグして選択します。
- F4キーを押します。「D1」が「$D$1」に変わります。
- Enterキーを押して数式を確定します。
- セル右下の小さな四角形であるフィルハンドルを下へドラッグするか、コピーしたい範囲まで選択してCtrl＋Dを押します。
この例では、E2の数式は=C2*$D$1になります。E3へコピーするとC2はC3に変わりますが、税率が入力されたD1は固定されるため、正しい計算を続けられます。
F4キーを押すたびに固定方法が切り替わります
F4キーは1回だけではなく、押すたびに参照形式を順番に切り替えられます。表の向きに合わせて使い分けましょう。
- A1の状態でF4を1回押す：$A$1になり、列と行を固定します。
- もう1回押す：A$1になり、行だけを固定します。
- さらに1回押す：$A1になり、列だけを固定します。
- もう1回押す：A1に戻り、固定を解除します。
縦方向へコピーするなら「行番号が変わってほしくないか」、横方向へコピーするなら「列記号が変わってほしくないか」を考えると、迷いにくくなります。
ケース別：どこを固定すればよいか
税率・割引率・為替レートを固定して縦にコピーする
商品ごとの金額がB2:B10、税率がF1にある場合、計算式は=B2*$F$1です。下方向へのコピーではB2だけをB3、B4と変化させたいので、税率セルF1を絶対参照にします。
横並びの月別表で見出し行を固定する
たとえばB1:M1に月ごとの係数があり、各月の計算式を右へコピーするなら、行だけを固定する=B2*B$1が便利です。右へコピーするとB2はC2、D2へ変わり、B$1もC$1、D$1へ変わります。ただし行番号1は固定されるため、常に見出し行を参照できます。
左端の単価列を固定して横にコピーする
A列に商品単価があり、B列以降に月別数量がある表では、=$A2*B2のように列Aだけを固定します。右へコピーしても単価の参照先はA列に残り、数量の列だけB列、C列、D列へ切り替わります。
コピー後に数式がずれる主な原因とチェックポイント
「$を付けたのに直らない」「コピーしたらエラーになる」という場合は、次の項目を順番に確認しましょう。
固定したいセルではなく、別の参照に$を付けている
数式内に複数のセル参照があると、意図しない方を固定しがちです。数式バーを見て、固定したいセルだけが$D$1のようになっているか確認してください。計算対象の行まで固定してしまうと、全行が同じ値を参照してしまいます。
コピー方向と固定方法が合っていない
下へコピーするのにA$1のように行だけ固定しても、列のずれは防げません。横へコピーするのに$A1のように列だけ固定しても、行のずれは防げません。縦コピーでは行番号、横コピーでは列記号がどう変化するかを確認しましょう。
数式ではなく値だけを貼り付けている
コピー後に右クリックして「値の貼り付け」を選ぶと、数式ではなく計算結果だけが貼り付けられます。あとから元データを変更しても再計算されません。数式をコピーしたいときは、通常の貼り付け、またはCtrl＋CのあとCtrl＋Vを使ってください。
#REF!エラーが表示される
#REF!は、数式が参照していたセル・行・列・シートが削除されたときによく出るエラーです。$を付けても、参照元そのものを削除するとエラーになります。数式バー内の「#REF!」を確認し、削除したセルを元に戻すか、正しいセル参照へ修正しましょう。
F4キーを押しても$が付かない
セルを選択しているだけでは、F4は直前の操作を繰り返す機能として動くことがあります。必ず数式を編集して、参照部分にカーソルを置くか、参照文字列を選択してから押してください。ノートPCではFn＋F4、またはFn Lockの切り替えが必要な場合もあります。
数式を素早くコピーする時短ワザ
固定設定ができたら、コピー操作もショートカットで短縮できます。特に行数が多い表では、ドラッグよりミスを減らせます。
- Ctrl＋D：選択範囲の最上段にある数式や値を下方向へコピーします。数式セルを含めてコピー先まで選択してから押します。
- Ctrl＋R：選択範囲の左端にある数式や値を右方向へコピーします。
- Ctrl＋C、Ctrl＋V：通常のコピーと貼り付けです。数式と書式をまとめて複製できます。
- フィルハンドルをダブルクリック：隣の列に連続データがある場合、セル右下の四角をダブルクリックすると、そのデータの末尾まで数式を自動入力できます。
なお、数式が正しく固定されているか不安なときは、コピー直後に先頭行・途中の行・最終行の3か所をクリックして数式バーを確認しましょう。参照先のうち変化させたい部分だけが変わり、固定した部分に$が残っていれば成功です。$とF4キーを使いこなせば、Excelの数式コピーは安定し、毎日の集計作業をぐっと楽にできますよ。
