相対参照は、数式をコピーした距離に応じて参照先が動く書き方です。絶対参照は、列や行に付けた $ で参照位置を固定します。行ごとに変えたい数量と、全行で使う共通条件を分けると、どこを固定するか判断しやすくなります。
この記事は、同じシート内で数式をコピーする場合を扱います。切り取りによる移動や、行・列の挿入は別の操作なので、ここで示す変化をそのまま当てはめないでください。例の数量・時間・セル配置はすべて架空です。
相対参照と絶対参照は、コピー時の動きで分ける
B2 は列B・行2のセルを示す相対参照です。数式を1行下へコピーすると、参照は B3 へ動きます。一方、$B$2 は列Bと行2を固定する絶対参照なので、このコピーでも $B$2 のままです。
Microsoftの相対参照と絶対参照の切り替えの説明でも、コピーに応じて動く参照と、列・行を固定する参照を区別しています。「大切な値だから固定」ではなく、コピー後も同じセルを参照したいかを考えます。
行ごとの数量と、共通の時間を分けて参照する
各行の処理時間を求める、独自の架空例です。B列に数量、C列に合計時間を置き、E1に1個あたりの共通時間を入力する設定にします。
- B2:12個。B3:8個。
- E1:5分/個。両方の行で使う共通条件。
- E2:別用途の数値7。今回の共通条件には使わない。
C2を =B2*$E$1 とし、C3へ1行下にコピーします。期待する式は =B3*$E$1 です。数量の参照はB2からB3へ動き、共通時間はE1を参照し続けます。計算は12×5=60分、8×5=40分です。
誤ってC2を =B2*E1 とすると、C2の結果は同じ60でも、下にコピーしたC3は =B3*E2 になります。8×7で数値56となり、期待した40分と一致しません。E1ではなく別セルE2を参照したためで、最初の1行が合うことだけではコピー後の式を確かめたことになりません。
$はセルの値を保存・保護する記号ではない
正しい式 =B2*$E$1 と =B3*$E$1 を保ち、共通条件E1を5から6分/個へ変えて計算し直すと、12×6=72分、8×6=48分になります。固定したのは「E1を参照する位置」で、E1の中身を5のまま凍結したわけではありません。
$ はセルの編集を禁止する指定でもありません。過去の条件や計算結果を保存すること、編集権限を制限することとは分けて考えます。ここでは参照先と計算の関係を示しており、実際の再計算設定やセル保護を操作した例ではありません。
行だけ・列だけを固定する複合参照もある
列と行の一方だけに $ を付ける書き方が複合参照です。次は前のC列の計算とは独立した、記法の確認例です。D4の数式をE5へ、1列右・1行下にコピーしたときの参照を比べます。
B2→C3:列も行も動く。$B$2→$B$2:列Bと行2を固定する。B$2→C$2:行2を固定し、列は動く。$B2→$B3:列Bを固定し、行は動く。
$B は列B、$2 は行2を固定する指定です。列だけ固定したいのに B$2 と書くと、固定する方向が逆になります。MicrosoftのExcelの数式の概要も、相対・絶対・複合参照を説明しています。
コピー前後は、結果と参照先を合わせて確認する
- コピー前:行や列ごとに変える入力と、共通で使う条件を分け、コピー元とコピー先を決める。
- コピー後:意図した方向へ参照が動いたか、固定した列・行が残っているかを式で確認する。
- 計算の照合:少なくとも確認する行の入力と期待結果を別に求め、数値が合う理由まで確かめる。
例えば今回のC3なら、「B3の8個とE1の5分/個で40分」と期待値を先に置けば、E2へずれた式を見つけやすくなります。結果が偶然同じになる場合もあるため、数字だけでなく参照セルも確認します。
この参照の考え方を条件付き集計に使う例は、担当者別の未完了件数をCOUNTIFSで数える方法で確認できます。本記事の例は参照記法と計算の机上点検であり、実Excelへの入力・コピー・再計算や、版別の操作を実測したものではありません。
