Excelで数式を下方向や横方向へコピーしたところ、参照するセルがずれて計算結果が変わってしまうことがあります。
このような場面では、セル番地に＄を付ける絶対参照を使うことで、基準となるセルや範囲を固定できます。
＄は数式のコピー先に合わせて変化する参照先を、必要な部分だけ動かないようにする記号です。
セル参照を固定する主な考え方です。
・＄A＄1は列Aと行1を固定する絶対参照です。
・A＄1は行だけを固定する複合参照です。
・＄A1は列だけを固定する複合参照です。
・F4キーを押すと、参照形式を順番に切り替えられます。
売上表、単価表、税率計算、比率の算出などでは、固定参照を理解するだけで数式の作成ミスを大幅に減らせます。
この記事では、Excel関数で＄を固定する方法と、数式をコピーしてもセル参照をずらさないための使い分けを詳しく解説します。
エクセルで＄を固定する方法【F4キーによる絶対参照】
| A | B | C | D |
|---|---|---|---|
| 1 | 商品名 | 税抜価格 | 税率 |
| 2 | ノート | 500 | 10% |
| 3 | ペン | 200 | 10% |
それではまず、数式内のセル参照をF4キーで固定する基本操作について解説していきます。
最初に結論をいうと、数式をコピーしても同じセルを参照し続けたい場合は、セル番地を＄A＄1の形に変更します。
数式を入力中に参照セルをクリックし、キーボードのF4キーを押すと＄記号を自動入力できます。
F4キーで切り替わる参照形式
Excelでは、数式バーまたはセルの編集状態でセル番地にカーソルを置き、F4キーを押すことで参照形式を切り替えます。
たとえばB2という参照を選択してF4キーを1回押すと、＄B＄2に変わります。
さらにF4キーを押すたびに、B＄2、＄B2、B2の順で切り替わります。
F4キーによる切り替え順です。
B2 → ＄B＄2 → B＄2 → ＄B2 → B2
なお、ノートパソコンではF4キーが音量や画面切り替えなどの機能キーに割り当てられている場合があります。
その場合は、Fnキーを押しながらF4キーを押すと、Excelの参照形式切り替えができることがあります。
数式をマウスで入力するよりも、参照セルを指定してからF4キーを押す方法のほうが入力ミスを防ぎやすい操作です。
税率セルを固定する数式
税抜価格がB列にあり、税率がD2セルに入力されている場合を考えます。
C2セルで税込価格を求めるなら、税抜価格に税率を掛ける数式を入力します。
=B2*(1+＄D＄2)
この数式では、B2は商品ごとに変わる税抜価格なので固定しません。
一方、D2は全商品の計算で共通して使う税率なので、列Dと行2の両方を固定します。
C2セルの数式をC3、C4へコピーしても、B2はB3、B4へ変化しますが、＄D＄2は常にD2を参照します。
コピー先ごとに変わる値と、すべての計算で共通する値を分けて考えることが絶対参照の基本です。
オートフィルで数式をコピーする操作
数式を下へコピーするには、数式を入力したセルの右下にある小さな四角形を利用します。
この四角形はフィルハンドルと呼ばれ、下方向へドラッグするか、隣接データがある場合はダブルクリックで連続入力できます。
先頭の計算セルに正しい数式を作ってからオートフィルを使えば、数十行から数千行の計算でも短時間で処理できます。
数式をコピーした後は、コピー先のセルを一つクリックして数式バーを確認しましょう。
B2がB3へ変わっていることと、＄D＄2が変わっていないことを確認すると、固定の成否を確実に判断できます。
固定したいセルに＄が付いていないままコピーすると、行が下がるごとに税率の参照先もD3、D4へずれてしまいます。
逆に、本来変化すべき売上金額や数量まで固定すると、すべての行が同じ値を使って計算されるため注意が必要です。
【操作のポイント】先頭セルで数式を完成させ、F4キーで固定部分を確認してからフィルハンドルでコピーしましょう。
相対参照と絶対参照の違い
| 参照形式 | 入力例 | 下へコピーした場合 |
|---|---|---|
| 相対参照 | B2 | B3へ変化 |
| 絶対参照 | ＄B＄2 | ＄B＄2のまま |
| 複合参照 | B＄2 | B＄2のまま |
続いては、相対参照と絶対参照の違いを確認していきます。
Excelでは、通常どおりセル番地を入力した場合、相対参照として扱われます。
相対参照はコピー先との位置関係を保ったまま参照先を移動させる仕組みです。
相対参照が向いている計算
相対参照は、各行にある対応データを使って同じ種類の計算を繰り返すときに便利です。
たとえばB列が数量、C列が単価、D列が金額である場合、D2セルには=B2*C2と入力できます。
この数式をD3へコピーすると、自動的に=B3*C3へ変わります。
行ごとの数量と単価を掛けるという計算ルールは同じなので、参照先が変わることが正しい動作です。
相対参照は、コピーに応じて参照先が変化することを利用するための標準的な参照方法です。
月別の売上、社員別の集計、商品別の小計など、表の各行に計算式を展開する場面で活躍します。
絶対参照が必要になる場面
絶対参照は、計算に使う基準値が表の中で一つだけに決まっている場合に使います。
代表例は消費税率、為替レート、目標達成率、割引率、基準日、固定単価です。
たとえばF1セルにドル円の為替レートが入力され、B列のドル建て価格を円換算するとします。
=B2*＄F＄1
この式を下へコピーすると、ドル建て価格のB2だけがB3、B4へ変わり、為替レートのF1は変わりません。
計算式を作る前に、一覧ごとに変化するセルと、表全体で共通するセルを紙に書き出すのも有効です。
基準値が一か所なら、そのセルは絶対参照にする可能性が高いと考えられます。
固定しすぎによる計算ミス
＄を使えば安全になるわけではなく、固定しすぎると誤った集計結果につながります。
たとえばD2セルに=＄B＄2*＄C＄2と入力して下へコピーすると、どの行でもB2とC2だけを掛け算します。
見た目は計算式が入っていても、全行の金額が同じになってしまうため、実務では見落としやすいミスです。
正しい参照の考え方です。
行ごとに変わる数量と単価は相対参照にします。
全行で共通する税率や係数は絶対参照にします。
コピー後に先頭行、中間行、最終行の三か所を確認すると、参照ずれや固定しすぎを早めに発見できます。
【操作のポイント】数式をコピーする前に、行単位で変化する値と表全体で共通する値を分けて考えましょう。
行と列だけを固定する複合参照
| 参照 | 固定される部分 | 用途の例 |
|---|---|---|
| A＄1 | 行1 | 横方向に展開する計算 |
| ＄A1 | 列A | 縦方向に展開する計算 |
| ＄A＄1 | 列Aと行1 | 常に同じ基準セル |
続いては、行または列だけを固定する複合参照について確認していきます。
複合参照は、表を縦横の両方向へコピーする計算で特に役立ちます。
＄を付ける位置によって、固定される対象が列なのか行なのかを判断できます。
行番号を固定するA＄1形式
A＄1形式では、行番号の前にだけ＄が付きます。
この参照を横方向へコピーしても行番号は1のままですが、列はA、B、Cのように変化します。
たとえば1行目に各月の単価があり、2行目以降に数量がある表では、月別単価を参照するときに利用できます。
=B2*B＄1
この式を右へコピーすると、=C2*C＄1、=D2*D＄1のように変化します。
列は月ごとに変わり、単価がある行1だけは常に固定されるため、横方向の計算に適した形です。
行番号を固定したいときは、行番号の直前に＄を置きます。
列記号を固定する＄A1形式
＄A1形式では、列記号の前にだけ＄が付きます。
この参照を下方向へコピーすると行番号は変わりますが、列は常にA列のままです。
たとえばA列に社員名、B列以降に月別の実績がある表で、各社員名を参照しながら横方向に計算する場合に便利です。
=＄A2&B＄1
この数式は、A列の社員名と1行目の月名を結合して、一覧の見出しや管理番号を作る用途にも使えます。
下へコピーすれば＄A3、＄A4と行だけが変わり、右へコピーしてもA列の参照は維持されます。
列記号を固定したいときは、列記号の直前に＄を置くことが重要です。
掛け算表での複合参照
複合参照の理解には、掛け算表を作る例が分かりやすいでしょう。
B1からK1に1から10を入力し、A2からA11にも1から10を入力したとします。
B2セルでは、左端の数値と上端の数値を掛け算する必要があります。
=＄A2*B＄1
＄A2は列Aを固定するため、右へコピーしても左端の数値を参照できます。
B＄1は行1を固定するため、下へコピーしても上端の数値を参照できます。
この式を右下へコピーすると、各位置で必要な縦見出しと横見出しが自動的に選ばれます。
縦の見出しと横の見出しを同時に使う表では、複合参照が最も効率的な選択です。
【操作のポイント】横へコピーしても残したい行、下へコピーしても残したい列を見極めて＄を付けましょう。
関数で＄を使う数式コピー
| A | B | C | D |
|---|---|---|---|
| 1 | 担当者 | 売上 | 目標 |
| 2 | 田中 | 85000 | 100000 |
| 3 | 佐藤 | 92000 | 100000 |
続いては、SUM関数やIF関数などで＄を使い、数式をコピーする方法を確認していきます。
関数内でも通常の四則演算と同じように、固定したいセルや範囲に＄を付けます。
関数の名前ではなく、関数の引数として指定するセル参照が固定対象です。
SUM関数で合計範囲を固定する方法
SUM関数で合計を出す場合、範囲の位置によっては絶対参照を使う必要があります。
たとえばB2からB10までの売上合計を、別の計算式の中で何度も使う場合は、範囲全体を固定します。
=B2/ SUM(＄B＄2:＄B＄10)
この式は、各担当者の売上が売上合計に占める割合を求める数式です。
分子のB2は下へコピーするたびにB3、B4へ変わる必要があります。
分母のSUM(＄B＄2:＄B＄10)は、どの担当者でも同じ総売上を使うため、開始セルと終了セルの両方を固定します。
合計範囲をコピー先でも変えたくない場合は、範囲の左上と右下のセル番地を両方固定します。
範囲指定の途中だけを固定すると、意図しない範囲にずれることがあるため、数式バーで確認しましょう。
IF関数で判定基準を固定する方法
IF関数で目標達成や合否などを判定するときも、共通の基準セルを絶対参照にします。
たとえばD1セルに目標売上が入力され、B列に実績がある場合は、C2セルに次の数式を入力できます。
=IF(B2>=＄D＄1,”達成”,”未達成”)
B2は担当者ごとの実績なので、コピー先に応じて変化させます。
＄D＄1は共通の目標値なので、どの行でも同じセルを参照するように固定します。
この式を下へコピーすれば、一人ずつの実績を共通目標と比較できます。
IF関数の比較条件に使う基準値は、複数行で共通なら絶対参照にするのが基本です。
文字列の達成と未達成はダブルクォーテーションで囲みますが、セル参照の＄は引用符の外側に記載します。
VLOOKUP関数とXLOOKUP関数の検索範囲
検索関数をコピーする場合は、検索値と検索範囲のうち、どちらを固定するかを区別します。
VLOOKUP関数では、通常は検索値を相対参照にし、商品マスタなどの検索範囲を絶対参照にします。
=VLOOKUP(A2,＄G＄2:＄I＄20,2,FALSE)
A2は行ごとの商品コードなので、下へコピーすればA3、A4へ変わる必要があります。
G2からI20の商品マスタは固定したい範囲なので、すべてのセル番地に＄を付けます。
XLOOKUP関数でも、検索値だけを変化させ、検索配列と戻り配列を固定する考え方は同じです。
=XLOOKUP(A2,＄G＄2:＄G＄20,＄H＄2:＄H＄20,”該当なし”)
検索範囲を固定しないと、数式をコピーするたびにマスタの範囲まで下へずれ、検索漏れの原因になります。
【操作のポイント】関数では、行ごとに変わる検索値は相対参照、共通の基準値やマスタ範囲は絶対参照に分けましょう。
数式コピーで参照がずれる原因
| コピー前の数式 | 下へコピー後 | 結果 |
|---|---|---|
| =B2*D2 | =B3*D3 | 両方が移動 |
| =B2*＄D＄2 | =B3*＄D＄2 | 税率だけ固定 |
| =＄B＄2*＄D＄2 | =＄B＄2*＄D＄2 | すべて固定 |
続いては、数式をコピーしたときに参照がずれる主な原因を確認していきます。
参照ずれはExcelの不具合ではなく、相対参照が持つ通常の動作によって発生します。
そのため、ずれた数式を一つずつ手入力で直すより、最初の数式の参照形式を見直すことが大切です。
コピー方向によるセル番地の変化
数式を下へコピーすると行番号が変化し、右へコピーすると列記号が変化します。
たとえば=C2+D2を右へコピーすると、=D2+E2へ変わります。
下へコピーした場合は、=C3+D3へ変わります。
これは各セルから見た相対的な位置関係を維持するための仕様です。
どの方向へコピーするかを先に決めると、固定すべき行と列を判断しやすくなります。
縦方向だけのコピーなら行番号に注目し、横方向だけのコピーなら列記号に注目しましょう。
数式バーによる参照先の確認
参照ずれを見つけるには、計算結果だけでなく数式バーを確認することが重要です。
コピー元のセルとコピー先のセルを順番にクリックし、セル番地がどのように変化しているかを比較します。
数式の一部だけに＄を付け忘れている場合、計算結果がたまたま正しく見えることもあります。
特に空白セルを参照しているケースでは、エラー表示にならず、誤ったゼロや空白の結果が出ることがあります。
確認の順番です。
・コピー元の数式を確認します。
・コピー先の数式を確認します。
・変化すべき参照と変化してはいけない参照を見比べます。
数式表示モードを使う方法もあります。
数式タブの数式の表示を選択するか、CtrlキーとShiftキーと＠キーを同時に押すと、計算結果ではなく数式そのものをシート上で確認できます。
名前定義を利用する方法
固定する基準セルが多い場合は、セルや範囲に名前を付ける方法もあります。
たとえばD2セルの税率に消費税率という名前を定義すると、数式では= B2*(1+消費税率)のように使えます。
名前として定義したセルは数式コピー時に参照がずれないため、絶対参照と同じように利用できます。
長い数式では、＄D＄2よりも意味が分かる名前を使うと、後から確認しやすくなります。
ただし、他の利用者と共有するファイルでは、名前の意味や対象範囲を分かりやすく管理することも必要です。
【操作のポイント】コピー先の計算結果だけで判断せず、数式バーでセル番地の変化を確認しましょう。
まとめ エクセル関数で＄を固定する方法
Excelの数式をコピーしてもセル参照をずらさないためには、＄を使った絶対参照と複合参照を使い分けることが重要です。
列と行を固定する場合は＄A＄1、行だけを固定する場合はA＄1、列だけを固定する場合は＄A1を使用します。
数式を編集している状態でF4キーを押すと、これらの参照形式を順番に切り替えられます。
共通の税率、目標値、検索範囲、合計範囲には絶対参照を使い、行ごとに変わる値には相対参照を残すことが基本です。
SUM関数、IF関数、VLOOKUP関数、XLOOKUP関数でも、固定するセルや範囲を＄で指定すれば、オートフィルによる数式コピーを正確に行えます。
最初の数式を入力したら、コピー前後の数式バーを確認し、意図した参照先になっているかを確かめましょう。
絶対参照を正しく使えるようになると、表計算の作業時間を短縮しながら、参照ずれによる計算ミスも防げます。
