【Excel】絶対参照と相対参照の違い | $の付け方とコピー後の確認

コピーしても同じセルを見る部分に$を付ける

数式を下へコピーしたとき、行ごとの数量は次の行へ変え、共通の単価は同じセルを参照したいことがあります。この「変える部分」と「変えない部分」を指定するのが相対参照と絶対参照です。

たとえばB2に数量、E1に共通単価を入れ、C2を=B2*$E$1にすると、C3へコピーした式は=B3*$E$1になります。B2はB3へ進み、E1は固定されます。$はセルの編集や削除を禁止する記号ではありません。

相対参照はコピーした距離に合わせて動く

=A1という式が、常に「左上のセル」を指すわけではありません。B2に入力した場合は1列左・1行上のA1ですが、D5に入力しても参照先は同じA1です。式をコピーしたときに、コピー元とコピー先の距離に応じて番地が変わります。

C2の=B2*10をC3にコピーすると=B3*10です。行ごとに異なる数量を計算したい場合、この動きが必要です。すべてに$を付けると同じ行の値を繰り返し計算してしまいます。

絶対参照は列と行の両方を固定する

$E$1はE列と1行目の両方を固定した参照です。数量がB2=3、B3=5、共通単価がE1=120なら、C2の=B2*$E$1は360、下へコピーしたC3は600になります。

単価を変えるときはE1だけを直します。単価120を式へ直接書いた場合と異なり、参照している各行の結果も更新されます。ただし、E1の前に行や列を挿入すると参照番地が調整されることがあり、参照セルを削除すれば#REF!になる場合もあります。「どんな操作でも住所が変わらない」という意味ではありません。

F4で切り替える方法と手入力

1. Windows版Excelで数式を編集中にし、固定したい参照の中へカーソルを置きます。
2. F4を押し、E1 → $E$1 → E$1 → $E1 → E1の順で必要な形にします。
3. Enterで確定し、まず1セルだけコピーして式を確認します。

ノートPCではFn+F4が必要なことがあります。セルを選んでいるだけの状態では、F4は直前操作の繰り返しなど別の動作になります。キー操作が合わない場合は、半角の$を数式へ直接入力しても同じです。

縦横へ展開する表は複合参照を使う

行だけ固定するE$1、列だけ固定する$E1を複合参照と呼びます。たとえばB1:D1に単価、A2:A4に数量を並べた表では、B2を=$A2*B$1にすると、右へコピーしても数量はA列、下へコピーしても単価は1行目を参照できます。

横へ1セルコピーした式は=$A2*C$1、下へ1セルなら=$A3*B$1です。表の端まで展開する前に、この2方向を確認すると固定箇所の間違いが見つかります。

切り取り移動や名前付き範囲は別に確認する

ここで説明している基本動作は数式のコピーです。切り取り移動、行列の挿入、参照先の削除は同じ結果になるとは限りません。大量に移動した後は、式の参照先を再確認してください。

「単価」などの名前を使う場合も、名前だけで絶対参照だと判断しません。「数式」タブの「名前の管理」で参照範囲を確認します。相対参照で定義された名前もあるため、名前を付ければ必ず固定されるわけではありません。

コピー前後の式で固定箇所を確かめる

結果の数字だけでは、たまたま一致した参照ミスを見逃します。コピー先の数式バーを開き、動くべき行・列だけが変わっていることを確認してから、残りの範囲へ広げましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】