Excelで数式をコピーした際に、参照するセルまでずれてしまい、想定した計算結果にならなかった経験はないでしょうか。
その原因の多くは、相対参照と絶対参照の設定の違いにあります。
売上表、単価表、消費税計算、予算管理表などでは、数式を下方向や横方向へコピーする操作が頻繁に必要です。
参照方法を正しく使い分ければ、数式を何度も入力し直す手間を減らし、計算ミスの防止にもつながります。
相対参照はコピー先に合わせて参照先が移動する指定です。
絶対参照はコピーしても特定の行や列を固定する指定です。
F4キーを使うと、参照形式を素早く切り替えられます。
この記事では、エクセルの絶対参照と相対参照の違い、ドルマークによる固定、数式の切り替え、一括変換の考え方を順番に解説していきます。
エクセルの絶対参照と相対参照の使い分け
それではまず、絶対参照と相対参照を使い分ける基本について解説していきます。
| 商品 | 数量 | 単価 | 金額 |
|---|---|---|---|
| りんご | 3 | 120 | =B2*C2 |
| みかん | 5 | 80 | =B3*C3 |
相対参照で変化するセル番地
相対参照とは、数式をコピーした距離に応じて、参照先のセル番地が自動で変化する参照方法です。
Excelでセル番地をそのまま入力した場合は、基本的に相対参照として扱われます。
たとえばD2セルに=B2*C2と入力し、D3セルへコピーすると、数式は=B3*C3へ変化します。
これは、D2から見て左へ2列、左へ1列という位置関係を保ったまま、数式が下へ移動するためです。
明細行ごとに数量と単価を掛ける計算では、相対参照が最も自然で使いやすい設定になります。
1行目を見出し行として使う表では、データが始まる2行目に最初の数式を入力し、以降の行へコピーする流れが基本です。
数式をコピーする前に、どのセルを基準に計算したいのかを確認すると、参照ずれを避けやすくなります。
絶対参照で固定されるセル番地
絶対参照とは、コピー先が変わっても、参照する列や行を固定したままにする指定です。
セル番地の列記号と行番号の前にドルマークを付けて、$A$1のように記載します。
たとえばB1セルに消費税率を入力し、各商品の税込金額を求める場合、税率のセルはコピー後も変わらないように固定する必要があります。
数式をD2セルへ入力するなら、=B2*C2*$B$1のように設定できます。
この数式を下方向へコピーしても、B2とC2は各行に合わせて変わり、$B$1だけは常に同じセルを参照します。
税率、割引率、為替レート、目標値、基準単価のように、表全体で共通して使う値は絶対参照に向いています。
参照形式を判断する考え方
相対参照と絶対参照の選択では、コピー後にも参照先を動かしたいかどうかを基準に考えます。
行ごとに異なるデータを計算するなら相対参照を使います。
表の上部や別シートにある共通設定値を参照するなら、絶対参照を使う場面です。
横方向へコピーする数式では、列方向に参照が変わることも意識しましょう。
数式をコピーする方向と、固定したい方向を分けて考えることが、参照設定を理解する近道です。
【操作のポイント】数式を入力した後にコピーする予定がある場合は、コピー前に参照先のセル番地を確認します。
ドルマークによるセル固定とF4キー切り替え
続いては、ドルマークを使った固定とF4キーによる切り替えを確認していきます。
| 参照形式 | 入力例 | 固定される部分 |
|---|---|---|
| 相対参照 | A1 | なし |
| 絶対参照 | $A$1 | 列と行 |
| 複合参照 | $A1 または A$1 | 列または行 |
列と行を固定する絶対参照
$A$1は、列Aと1行目の両方を固定する完全な絶対参照です。
下方向、右方向、左方向のどこへ数式をコピーしても、参照先はA1セルのままです。
たとえば、B1セルに目標達成率の係数1.1を入力し、各行の売上へ掛ける場合を考えます。
=C2*$B$1
この式を下へコピーすると、C2はC3、C4へ変わりますが、$B$1は変化しません。
共通の係数を使う計算では、固定したセルを表の上部や端にまとめておくと、数式の内容も読み取りやすくなります。
列または行だけを固定する複合参照
複合参照は、列だけ、または行だけを固定する参照方法です。
$A1は列Aを固定し、行番号はコピー先に応じて変化します。
A$1は1行目を固定し、列記号はコピー先に応じて変化します。
たとえば、縦に商品名、横に月名を配置した売上表では、行見出しや列見出しを固定して計算する場面があります。
掛け算表や単価表では、複合参照が役立つケースが多いでしょう。
縦方向へコピーするときは行の固定、横方向へコピーするときは列の固定に注目すると判断しやすくなります。
F4キーで切り替える参照形式
数式の編集中にセル番地を選択し、F4キーを押すと参照形式を順番に切り替えられます。
一般的なWindows版Excelでは、A1、$A$1、A$1、$A1の順で切り替わります。
ノートパソコンでは、Fnキーを押しながらF4キーを使う必要がある機種もあります。
手入力でドルマークを追加することもできますが、F4キーを使うと入力ミスを抑えながら短時間で切り替えられます。
【操作のポイント】セル番地だけを選んだ状態でF4キーを押すと、変更したい参照形式を見つけやすくなります。
数式コピーでの参照先確認
続いては、数式をコピーしたときに参照先を確認する方法を解説していきます。
| A | B | C | D |
|---|---|---|---|
| 1 | 税率 | 10% | |
| 2 | 税抜金額 | 1000 | =C2*(1+$C$1) |
オートフィルで数式を複製する操作
数式を入力したセルの右下にある小さな四角は、フィルハンドルと呼ばれます。
フィルハンドルを下方向へドラッグすると、数式を連続した行へコピーできます。
隣接列にデータが連続している場合は、フィルハンドルをダブルクリックして、データ末尾まで自動で数式を入力することも可能です。
最初のセルへ=C2*(1+$C$1)を入力した場合、D3セル以降ではC列の行番号だけが変化します。
固定すべき税率セルにドルマークが付いているかを、オートフィルの前に確認しましょう。
数式バーで確認するセル参照
計算結果だけでは、どのセルを参照しているか判断しにくいことがあります。
対象セルをクリックし、数式バーを確認すると、実際に入力されている数式を見られます。
セルをダブルクリックするか、F2キーを押して編集状態にすると、参照先のセルが色付きの枠で表示されます。
相対参照と絶対参照が混在する数式では、ドルマークの有無を一つずつ確認することが大切です。
数式バーは計算ミスを見つけるための基本的な確認場所です。
コピー後の数式を比較する確認
数式を複数行へコピーした後は、先頭行、中間行、最終行の数式を比較すると安心です。
たとえばD2、D10、D20の数式を見て、変化してよいセル番地だけが変わっているかを確認します。
本来固定するべきセルがD2からD10のように移動していれば、絶対参照の指定が不足しています。
逆に各行で変えるべき数量セルまで固定されていれば、ドルマークを外す必要があります。
【操作のポイント】コピー直後に数式を数件確認すれば、大量データへ展開する前にミスを修正できます。
横方向と縦方向の複合参照
続いては、横方向と縦方向にコピーする複合参照を確認していきます。
| 商品 | 1月 | 2月 | 3月 |
|---|---|---|---|
| 目標係数 | 1.1 | 1.2 | 1.0 |
| 商品A | = $A3*B$2 | = $A3*C$2 | = $A3*D$2 |
行見出しを固定する列固定
$A3のような参照は、列Aだけを固定する指定です。
数式を右へコピーしても、参照する列はAのまま維持されます。
一方で行番号の3は固定されないため、下へコピーすると$A4、$A5へ変わります。
商品の基準値がA列にあり、月別の計算結果を横方向へ展開する表では、列固定が役立ちます。
横にコピーしても商品別の基準セルを変えたくない場合は、列記号の前にドルマークを付けます。
列見出しを固定する行固定
B$2のような参照は、2行目だけを固定する指定です。
数式を下へコピーしても、行番号は2のままです。
右へコピーした場合は列記号がC、Dへ変わるため、各月の係数や単価を順に参照できます。
月別、部門別、担当者別などの見出しが横に並ぶ集計表では、行固定を使う機会があります。
=$A3*B$2
この数式は、商品名側の列を固定しながら、月別係数側の行を固定する組み合わせです。
掛け算表で使う参照の組み合わせ
複合参照を理解するには、縦横に数式をコピーする掛け算表を作る方法が分かりやすいでしょう。
左端の列に数量、上端の行に単価を配置し、交差するセルに計算式を入力します。
最初の計算セルでは、数量の列を固定し、単価の行を固定する数式に設定します。
コピー方向が二つある表ほど、複合参照の効果が大きくなります。
【操作のポイント】横へコピーするなら列固定、下へコピーするなら行固定が必要かを先に検討します。
絶対参照への一括変換と数式変更
続いては、既存数式を絶対参照へ変更する考え方と一括変換を確認していきます。
| 変更前 | 変更後 | 目的 |
|---|---|---|
| =C2*B1 | =C2*$B$1 | 税率セルの固定 |
| =A2*B2 | =$A2*B$1 | 縦横コピーへの対応 |
検索と置換を使う前の注意点
すでに入力済みの数式をまとめて変更したい場合、検索と置換を利用できることがあります。
ただし、セル番地の一部を単純に置き換えると、意図しない数式まで変更される可能性があります。
たとえばB1を$B$1へ置換する場合、B10やAB1まで影響しないかを慎重に確認する必要があります。
検索と置換による一括変換は、必ずコピーしたファイルや限定範囲で試すことが重要です。
数式が複雑なブックでは、対象セルを選択して個別にF4キーで修正する方が安全な場合もあります。
数式の編集モードで変更する方法
セルを選択してF2キーを押すと、セル内の数式を直接編集できます。
変更したいセル番地へカーソルを置き、F4キーを押せば、相対参照から絶対参照へ切り替えられます。
数式バーをクリックして編集する方法でも、同じように参照形式を変更できます。
数式内に同じセル番地が複数回出てくる場合は、それぞれが固定対象かどうかを見極めましょう。
変更前の数式 =C2*B1
変更後の数式 =C2*$B$1
複数セルへ修正結果を反映する方法
先頭セルの数式を正しく修正したら、フィルハンドルやコピー貼り付けで下の行へ反映できます。
すでに結果が入力されている範囲では、修正した先頭セルをコピーし、対象範囲を選択して貼り付ける方法もあります。
貼り付け後は、各行に応じて相対参照が変化し、絶対参照は固定されたままになります。
修正後の数式を一度だけ下へコピーして確認すると、広い範囲へ反映する際の不安を減らせます。
【操作のポイント】一括変換の前には、対象範囲と数式のバックアップを確認してから進めます。
参照エラーと計算ミスの対処
続いては、絶対参照と相対参照で起こりやすいエラーや計算ミスを確認していきます。
| 表示や状況 | 主な原因 | 確認方法 |
|---|---|---|
| 値が行ごとに不自然 | 固定不足 | ドルマークの確認 |
| 全行が同じ計算結果 | 固定しすぎ | 相対参照の確認 |
| #REF!表示 | 参照先の削除 | 数式バーの確認 |
固定不足による参照ずれ
共通の税率や係数を相対参照のままコピーすると、行が変わるたびに参照先もずれてしまいます。
税率セルが空白セルや別の数値へ移ると、金額がゼロになったり、不自然な結果になったりします。
このような場合は、共通設定値のセルに$を付けて絶対参照へ変更します。
コピー後に計算結果が急に変わる場合は、固定不足を疑うとよいでしょう。
固定しすぎによる同一計算
すべてのセル番地を$で固定すると、コピー後も全行が同じデータを参照します。
明細ごとに異なる数量や単価を計算する式で$B$2*$C$2をコピーした場合、どの行も2行目だけを計算します。
この状態では、一見すると数式が入っていても、明細別の集計としては正しくありません。
固定するのは共通値だけに絞ることが、絶対参照を使う基本です。
参照先削除による#REF!エラー
数式が参照している行、列、またはシートを削除すると、#REF!エラーが表示されることがあります。
絶対参照であっても、参照先そのものが削除されれば計算は維持できません。
エラーが出たセルを選択し、数式バーで#REF!になっている部分を確認します。
必要に応じて正しいセルを指定し直すか、削除前の構成へ戻して参照を修正します。
【操作のポイント】表の行や列を削除する前に、重要な数式がどこを参照しているか確認します。
まとめ エクセルの相対参照と絶対参照の違いと使い方
エクセルの絶対参照と相対参照は、数式をコピーしたときの参照先の動きを決める大切な機能です。
相対参照はコピー先に応じてセル番地が変化するため、明細ごとの計算や連続データの処理に適しています。
絶対参照は$A$1のように列と行を固定でき、税率、係数、基準値などの共通セルを参照するときに便利です。
相対参照はコピー先に合わせて変化します。
絶対参照はコピー後も参照先を固定します。
複合参照は列または行だけを固定します。
F4キーを使えば、相対参照、絶対参照、列固定、行固定を効率よく切り替えられます。
数式をコピーする方向と、変えてはいけないセルを意識することが、エクセルの計算式を正確に作るポイントです。
コピー後には数式バーで参照先を確認し、先頭行と末尾行の計算結果に不自然な差がないかを見直しましょう。
絶対参照と相対参照を使い分ければ、表作成のスピードと正確さを高められます。
