【Excel】エクセルで絶対参照を設定するやり方（相対参照）

Excelで計算式をコピーした際に、参照するセルまで一緒にずれてしまい、想定どおりの結果にならないことがあります。

このようなときに役立つ機能が絶対参照です。

セル番地にドル記号を付けるだけの操作ですが、相対参照との違い、コピーする方向、行と列の固定範囲を理解していないと、数式が複雑に見えるかもしれません。

この記事では、Excelで絶対参照を設定するやり方と、相対参照、複合参照の使い分けを、売上表のサンプルを使って詳しく解説します。

それでは、セルを固定する具体的な操作から確認していきましょう。

 

エクセルで絶対参照を設定する方法【F4キーとドル記号】

A	B	C	D	E
1	商品名	単価	数量	税率
2	ノート	120	3	10%
3	ペン	150	2

それではまず、数式内の参照先を固定する絶対参照の設定方法について解説していきます。

絶対参照は、数式を別のセルへコピーしても、指定したセル番地を変化させないための指定です。

たとえばE2セルに税率が入力されており、各商品の税込金額を求める場合、税率の参照先だけは常にE2セルのままにする必要があります。

絶対参照では、列記号と行番号の前にドル記号を付けます。

 

数式バーでドル記号を直接入力する操作

数式バーでドル記号を直接入力する操作について解説していきます。

まず、F列に税込金額を表示する例を考えます。

F1セルには税込金額という見出しを入力し、F2セルには税抜き金額と税率を使った計算式を入力します。

B2セルとC2セルは、2行目の商品ごとに変化してよいため相対参照のままです。

一方、E2セルには共通の税率が入っているため、$E$2のように列と行の両方を固定します。

数式を入力するときは、半角英数入力に切り替えたうえで、セル番地の前に半角のドル記号を入力しましょう。

日本語入力がオンの状態では、全角の記号が混ざることがあり、数式エラーの原因になります。

入力後にEnterキーを押すと、F2セルには396といった税込金額が表示されます。

この時点ではドル記号がセル上に表示されないため、数式バーを見て設定を確認するのが確実です。

 

F4キーで参照形式を切り替える操作

続いては、F4キーを使って絶対参照へ切り替える操作を確認していきます。

セル番地を手入力するよりも、数式編集中にF4キーを押す方法が速く、入力ミスも抑えやすい方法です。

F2セルに=B2*C2*(1+E2)と入力し、数式内のE2という文字列の直後または途中にカーソルを置きます。

この状態でキーボードのF4キーを1回押すと、E2が$E$2へ変わります。

セル参照にカーソルがある状態でF4キーを押すことが重要です。

ノートパソコンでは、F4キーだけで機能しない場合があります。

その場合はFnキーを押しながらF4キーを押すと、参照形式の切り替えができることがあります。

F4キーを繰り返し押すと、$E$2、E$2、$E2、E2の順に表示が切り替わります。

目的が列と行の完全固定なら、$E$2が表示された時点でEnterキーを押しましょう。

 

オートフィルで固定状態を確認する操作

続いては、入力した絶対参照が正しく機能するかを、オートフィルで確認していきます。

F2セルを選択し、セル右下にある小さな四角形のフィルハンドルにマウスポインターを合わせます。

マウスポインターが黒い十字に変わったら、下方向へF4セルなど必要な行までドラッグします。

コピー後のF3セルをクリックして数式バーを見ると、=B3*C3*(1+$E$2)のように表示されます。

B2とC2はB3とC3に変わっていますが、税率を示す$E$2だけは変化していません。

この変化の違いが、相対参照と絶対参照を組み合わせる大きな利点です。

 

相対参照と絶対参照の違い

参照形式	入力例	下へコピーした結果
相対参照	E2	E3
絶対参照	$E$2	$E$2
複合参照	$E2	$E3

続いては、相対参照と絶対参照がどのように変化するのかを確認していきます。

Excelでは、特別な指定をせずにセルをクリックして数式を作ると、通常は相対参照になります。

相対参照はコピー先との位置関係に応じて番地が変わるため、行ごとの計算を連続して行う場面で便利です。

 

相対参照が自動でずれる仕組み

相対参照が自動でずれる仕組みについて解説していきます。

たとえばD2セルに=B2*C2という数式を入力すると、単価と数量を掛けた税抜き金額を求められます。

このD2セルをD3セルへコピーすると、数式は自動的に=B3*C3へ変わります。

相対参照は、コピー元から何行何列離れているかという相対的な位置を保つ仕組みです。

そのため、商品一覧や月別集計のように、同じ計算を複数行へ繰り返す場合に向いています。

逆に、コピーしても変わってほしくない条件セルまで相対参照にすると、参照先が空白セルや別の値へ移動します。

税率、基準単価、割引率、目標値など、一か所に入力した共通値を使う場面では注意が必要です。

 

絶対参照がずれない仕組み

続いては、絶対参照がコピー後もずれない仕組みを確認していきます。

絶対参照は、ドル記号を付けた列または行を、数式のコピー先に関係なく固定する指定です。

F2セルに=B2*C2*(1+$E$2)と入力し、F3セルへコピーした場合を考えます。

コピー先の数式は=B3*C3*(1+$E$2)となり、商品データのセルだけが3行目へ移ります。

税率セルは常にE2セルを参照するため、すべての商品に同じ税率を適用できます。

絶対参照は、計算の基準となるセルを一つに保ちたいときに使います。

たとえば為替レート、消費税率、手数料率、割引率、予算上限、評価基準なども、絶対参照を使う代表的な項目です。

設定値を別セルにまとめておけば、数値を一度修正するだけで、参照している計算結果をまとめて更新できます。

 

参照形式を判断する考え方

続いては、どの参照形式を選ぶべきかを確認していきます。

判断の基準は、数式をコピーしたときに、そのセル番地が動いてよいかどうかです。

行ごとの単価や数量のように、コピー先の行に合わせて変わるべき値には相対参照を使います。

一方で、全行に共通する税率や係数には絶対参照を使いましょう。

数式を作る前に、変えるセルと固定するセルを分けて考えることがミス防止につながります。

複数の計算式に同じ条件を使う場合、条件値を表の端や上部に集めると、参照先が分かりやすくなります。

また、セルに税率や割引率といったラベルを付ければ、後から数式を見直す際にも意味を把握しやすくなります。

 

複合参照の設定方法【行固定と列固定】

A	B	C	D
1		1月	2月
2	東京	120	130
3	大阪	110	125

続いては、列だけまたは行だけを固定する複合参照について解説していきます。

複合参照は、縦方向と横方向の両方へ数式をコピーする表で特に便利な指定です。

$A1は列Aだけを固定し、A$1は1行目だけを固定します。

 

列だけを固定するドル記号の位置

それではまず、列だけを固定する設定について解説していきます。

$A2のように、列記号Aの前だけにドル記号を付けると、列Aが固定されます。

行番号の2にはドル記号がないため、数式を下方向へコピーした場合は$A3、$A4のように行だけが変わります。

$A2は、同じ列にある項目を参照し続けながら、行ごとのデータを取り出したい場合に有効です。

たとえばA列に商品名や基準値があり、右方向に複数の計算列を作る場合、列Aを固定すると参照が安定します。

数式バーでA2を選択し、F4キーを3回押すと$A2へ切り替えられます。

F4キーの押下回数は初期状態によって意識しにくいため、最後に数式バーの表示を見て確認しましょう。

 

行だけを固定するドル記号の位置

続いては、行だけを固定する設定について確認していきます。

A$1のように、行番号1の前だけにドル記号を付けると、1行目が固定されます。

列記号Aにはドル記号がないため、右方向にコピーするとB$1、C$1のように列だけが変わります。

A$1は、上端の見出し行や月別の係数を、横方向に参照したいときに使う形式です。

たとえば1行目に月ごとの掛け率があり、各商品の基準値に掛ける表では、行1を固定すると便利です。

数式内のA1にカーソルを置き、F4キーを2回押すとA$1になります。

複合参照では、列と行のどちらを固定するかを、コピーする方向から逆算して決めると理解しやすくなります。

 

縦横コピーで複合参照を活用する計算式

続いては、縦横へ数式をコピーする計算で複合参照を活用する方法を確認していきます。

たとえばA列に基準額、1行目に月ごとの係数がある場合、B2セルに=$A2*B$1と入力します。

この数式を右と下へオートフィルすると、A列の基準額と1行目の係数を交差させた計算表を作成できます。

縦横にコピーする表では、列固定と行固定を組み合わせると一つの数式で表全体を完成させられます。

コピーする前に、基準値が並ぶ方向と係数が並ぶ方向を観察しましょう。

左端に並ぶ値は列固定、最上部に並ぶ値は行固定になるケースが多い傾向です。

 

絶対参照を使った税込金額の計算

A	B	C	D	E
1	単価	数量	税抜金額	税率
2	120	3	360	10%
3	150	2	300

続いては、絶対参照を使って税込金額を求める実例について解説していきます。

実務では、税率や手数料率を各行に重複入力するより、専用のセルに一度だけ入力して参照するほうが管理しやすくなります。

 

税率セルを固定した基本数式

税率セルを固定した基本数式について解説していきます。

D列に税抜金額、E2セルに税率がある場合、F2セルで税込金額を出す数式は=D2*(1+$E$2)です。

D2は商品ごとに変化するため相対参照、E2は全商品で共通のため絶対参照です。

1を足しているのは、税抜金額そのものと消費税分を合わせた税込金額にするためです。

税額だけを別列に求めたい場合は、=D2*$E$2という数式を使用します。

この場合、税抜金額に税率だけを掛けるため、税額36が計算されます。

 

オートフィルで数式を下までコピーする操作

続いては、税込金額の数式を下の行までコピーする操作を確認していきます。

F2セルに正しい数式を入力したら、セルを選択してフィルハンドルを下方向へドラッグします。

データが連続している場合は、フィルハンドルをダブルクリックして、隣接列の最終行まで自動入力する方法も便利です。

コピー後のF3セルでは、D2がD3へ変わり、$E$2はそのまま残ります。

最初の数式だけを丁寧に作れば、以降の行はオートフィルで効率よく計算できます。

なお、金額に小数が発生する場合は、表示形式を通貨や数値へ設定し、小数点以下の表示桁数を確認しましょう。

端数処理が必要な請求書では、ROUND関数、ROUNDUP関数、ROUNDDOWN関数と絶対参照を組み合わせる場面もあります。

 

税率変更に対応する表の作り方

続いては、税率変更や条件変更に対応しやすい表の作り方を確認していきます。

税率を数式へ直接書き込んで=D2*1.1とすることもできますが、後から税率を変更する場合に多数の数式を修正する必要があります。

そこで、税率はE2セルのような設定セルにまとめ、数式では$E$2を参照する設計にします。

設定値を一か所に集約すると、計算式の保守性が高まります。

たとえば軽減税率と標準税率を扱う表なら、別の場所に8パーセントと10パーセントを入力し、商品区分に応じて参照先を分けることもできます。

表の上部や右側に条件エリアを設け、背景色を変えて入力セルだと分かるようにすると、誤入力の防止にも役立ちます。

 

絶対参照で起こりやすいエラーと確認方法

確認項目	正しい例	見直す例
税率参照	$E$2	E2
下方向コピー	D3*(1+$E$2)	D3*(1+E3)
文字列確認	半角の$	全角の＄

続いては、絶対参照で起こりやすいエラーと確認方法について解説していきます。

数式の結果が途中からおかしい場合は、セルの値だけを見るのではなく、コピー先の数式そのものを確認することが大切です。

 

ドル記号を付け忘れた場合のずれ

ドル記号を付け忘れた場合のずれについて解説していきます。

F2セルに=D2*(1+E2)と入力し、下へコピーすると、F3セルでは=D3*(1+E3)へ変化します。

E3セルが空白なら、F3セルは税抜金額と同じ数値になってしまいます。

空白セルを参照してもエラー表示にならず、0として扱われる場合があるため、気付きにくい点が注意点です。

最初の行だけ正しく、2行目以降の計算結果が不自然な場合は、絶対参照の付け忘れを疑いましょう。

修正するときは、コピー元の数式を$E$2へ直してから、改めて下のセルへコピーし直すと確実です。

 

固定しすぎた場合のずれ

続いては、必要以上にセルを固定した場合のずれを確認していきます。

たとえばD2セルの計算式を=$B$2*$C$2としてしまうと、下へコピーしてもすべての行が2行目の単価と数量を使います。

その結果、どの行にも同じ計算結果が表示されることになります。

商品ごとに変化する値まで絶対参照にすると、コピー先のデータを反映できません。

固定する対象は、共通の条件セルだけです。

明細データは相対参照に残すという役割分担を意識すると、過剰な固定を防げます。

 

数式バーとトレース機能による確認

続いては、参照先を視覚的に確認する方法を確認していきます。

対象セルをクリックすると、数式バーに実際の数式が表示されます。

ここでドル記号の位置を確認すれば、列固定、行固定、完全固定のいずれかを判断できます。

さらに、数式タブの参照元のトレースを使うと、数式が参照しているセルを矢印で表示できます。

複雑な表では、数式バーと参照元のトレースを併用すると、参照ミスを見つけやすくなります。

数式の表示をオンにするショートカットとして、CtrlキーとShiftキーと@キーの同時押しもあります。

数値の代わりに数式全体をワークシート上へ表示できるため、複数行の参照形式を比較したいときに便利です。

 

まとめ エクセルで相対参照と絶対参照を設定する方法

用途	参照形式	記述例
行ごとの明細計算	相対参照	B2
共通の税率や係数	絶対参照	$E$2
縦横に広がる計算表	複合参照	$A2 または A$1

Excelで絶対参照を設定するには、固定したいセル番地にドル記号を付けます。

列と行の両方を固定する場合は$E$2、列だけを固定する場合は$E2、行だけを固定する場合はE$2を使います。

数式を編集中にF4キーを押すと、これらの参照形式を順番に切り替えられます。

共通の税率、手数料率、割引率、基準値には絶対参照を使い、明細ごとに変化するセルには相対参照を使うのが基本です。

縦方向と横方向へ数式をコピーする表では、列固定と行固定を組み合わせた複合参照が役立ちます。

計算結果がおかしいと感じたら、コピー先セルの数式バーを開き、ドル記号が正しい位置にあるか確認しましょう。

相対参照と絶対参照の違いを理解すれば、オートフィルを使った集計表や請求書、売上管理表を効率よく作成できます。