【Excel】エクセルでセルを固定する方法（＄マーク・絶対参照）

Excelで数式をコピーしたとき、参照するセルまでずれてしまい、計算結果が合わなくなることがあります。

このようなときに役立つのが、$マークを使って参照先を固定する絶対参照です。

セル固定を使い分けると、単価、税率、割引率、基準値などを一つの数式から正確に計算できます。

サンプルデータでは、1行目に見出しがあり、B列に数量、C列に単価、D列に金額を入力するものとして説明します。

それでは、$マークを含む数式の意味から、Windows版Excelでセルを固定する具体的な操作まで確認していきましょう。

 

エクセルでセルを固定する方法【F4キーで絶対参照へ変更】

A	B	C	D
商品名	数量	単価	金額
ノート	3	120	=B2*C2
ペン	5	80	=B3*C3

それではまず、セル参照に$マークを付ける最も速い方法について解説していきます。

 

数式編集中にF4キーを押す操作

絶対参照にしたいときは、数式を入力または編集している途中で、固定したいセル参照にカーソルを置きます。

たとえば、E2セルに消費税を計算する数式として、=D2*G1を入力する場面を考えます。

このまま下方向へコピーすると、2行目ではG1を参照していても、3行目ではG2、4行目ではG3へと参照先が移動します。

税率をG1セルだけに入力している場合、コピー後の計算式は期待どおりに動きません。

そこで、数式バーまたはセル内のG1部分を選び、キーボードのF4キーを押します。

F4キーを一度押すと、G1は$G$1へ変わり、列Gと行1が同時に固定されます。

ノートパソコンでは、Fnキーを押しながらF4キーを使う必要がある機種もあります。

F4キーを押しても固定形式が変わらない場合は、数式の入力モードになっているか、セル参照部分にカーソルがあるかを確認しましょう。

 

F4キーで切り替わる四つの参照形式

F4キーは一度だけ押して終わりではなく、押すたびに参照形式を順番に切り替えられます。

最初にG1という相対参照がある場合、一回目は$G$1、二回目はG$1、三回目は$G1、四回目はG1へ戻ります。

$G$1は絶対参照であり、横方向にも縦方向にもコピー先に関係なくG1を参照します。

G$1は行番号の1だけを固定する複合参照です。

$G1は列記号のGだけを固定する複合参照です。

表を右方向と下方向の両方へコピーする場合は、どの方向へ数式をコピーするかを先に考えると、必要な$マークを判断しやすくなります。

入力済みの数式を後から直すときも、数式バーをクリックして参照セルを選べば同じように変更できます。

 

数式コピー後に参照先を確認する方法

固定設定が正しいかどうかは、数式をコピーした後に一つ下のセルを選択して確認すると確実です。

たとえばE2に=D2*$G$1を入力してE10までオートフィルした場合、E3は=D3*$G$1になっていれば正常です。

D2だけがD3へ変わり、$G$1がそのまま残ることが重要になります。

数式タブの数式の表示を使うと、計算結果ではなくシート全体の数式を確認できます。

ショートカットではCtrlキーと`キーで数式表示を切り替えられますが、キーボード配列によって位置が異なるため、リボンから操作しても問題ありません。

数式をコピーしてから間違いに気付いた場合も、元の数式を修正して再度オートフィルすれば整えられます。

計算結果だけで判断せず、数式バーで参照先を確認する習慣がミスの早期発見につながります。

 

＄マークによる絶対参照の意味

セル	数式	下へコピー後	参照の特徴
E2	=D2*G1	=D3*G2	両方が移動
E2	=D2*$G$1	=D3*$G$1	G1を固定

続いては、$マークが数式で果たす役割を確認していきます。

 

相対参照との違い

Excelでは、通常のセル参照であるA1やB2は相対参照として扱われます。

相対参照は、数式をコピーした距離に応じて参照先も同じ分だけ移動する仕組みです。

たとえばD2に=B2*C2と入力して下方向にコピーすると、D3では=B3*C3となります。

同じ行にある数量と単価を掛ける計算では、この変化が必要です。

一方、全行で共通の税率や為替レートを使う計算では、参照先まで移動してはいけません。

このような共通条件を固定するために、$マークを列記号または行番号の前へ付けます。

絶対参照は数値そのものを固定する機能ではなく、数式が参照するセル番地を固定する機能です。

そのため、$G$1を参照している間にG1の税率を変更すれば、絶対参照を使うすべての計算結果が更新されます。

 

絶対参照が必要になる代表的な計算

絶対参照が活躍する代表例は、消費税率、値引率、手数料率、目標値、換算レートなどです。

たとえば、G1セルに10パーセントを入力し、E2で税込金額を計算するなら、=D2*(1+$G$1)とします。

この式を下へコピーすると、商品金額のD2はD3、D4へ変わります。

しかし税率の$G$1は変わらず、すべての行で同じ税率が使われます。

また、別シートにマスターデータを置く場合にも絶対参照は便利です。

たとえば、設定シートのB2に為替レートがあるなら、=C2*設定!$B$2のように書けます。

シート名を含む参照でも、固定したいセル番地に$マークを付ける考え方は同じです。

 

＄マークを直接入力する方法

F4キーを使えない環境でも、$マークを手入力すれば絶対参照を作成できます。

たとえば、G1を固定したいなら、数式中に$G$1と直接入力します。

日本語キーボードでは、一般的にShiftキーを押しながら4キーを押すと$マークを入力できます。

数式を作る途中でマウスによりセルをクリックした場合、Excelは通常の相対参照としてG1を自動入力します。

その後にGと1の前へ$マークを足しても、F4キーで切り替えても結果は同じです。

ただし、$の位置を間違えると列固定と行固定が入れ替わるため注意が必要です。

$G1は列Gだけを固定し、G$1は行1だけを固定する表記です。

 

行と列を固定する複合参照の使い分け

参照形式	列	行	使用例
A1	変化	変化	通常の行別計算
$A$1	固定	固定	共通税率
A$1	変化	固定	横展開の見出し
$A1	固定	変化	縦展開の見出し

続いては、行または列だけを固定する複合参照の考え方を確認していきます。

 

行だけを固定するA$1の活用

A$1は、行番号の1だけを固定し、列記号Aはコピー先に応じて変化させる形式です。

主に、月別や年度別の数値を横方向へ計算する表で使います。

たとえば、B1からM1に月ごとの係数が並び、A2からA10に基準値が並ぶ表を考えます。

B2に=$A2*B$1と入力すると、左端の基準値と上端の係数を掛け合わせる計算式になります。

このうちB$1は、下方向へコピーしても1行目を参照し続けます。

右方向へコピーすると、B$1はC$1、D$1へ移り、各月の係数を自然に参照できます。

横方向へコピーする際に上段の見出しや係数を使うなら、行固定のA$1形式が候補です。

 

列だけを固定する$A1の活用

$A1は、列Aだけを固定し、行番号はコピー先に合わせて変化させる形式です。

表を右へコピーしても、常に左端列の同じ行を参照したい場合に適しています。

たとえば、A列に商品別の基準価格があり、B列からD列に異なる割引率での価格を出す場合があります。

B2の数式を=$A2*(1-B$1)とすると、基準価格の参照は常にA列に固定されます。

右側へコピーしたC2では=$A2*(1-C$1)となり、基準価格はA2のまま、割引率だけがC1へ移動します。

下方向へコピーすれば、$A2は$A3、$A4となるため、各商品の基準価格を使えます。

列固定は、商品名、基準価格、担当者名など、左端に配置した項目を横展開の計算に使うときに便利です。

 

縦横にコピーする表での参照設計

複合参照を理解する近道は、数式をどの方向へコピーするかを紙に書き出すことです。

下方向へコピーするときに変わってほしくない要素は行番号です。

そのため、行番号の前に$マークを付けます。

右方向へコピーするときに変わってほしくない要素は列記号です。

そのため、列記号の前に$マークを付けます。

縦横の両方向にコピーする場合、左端の値には$A2、上端の値にはB$1というように役割を分けるのが基本です。

数式を一つ完成させたら、まず右へ一つ、次に下へ一つコピーし、参照先が想定どおりか確認しましょう。

 

絶対参照を使った数式コピーとオートフィル

A	B	C	D	G
商品名	数量	単価	税抜金額	税率
ノート	3	120	=B2*C2	10%
ペン	5	80	=B3*C3

続いては、固定した数式をオートフィルで展開する操作を確認していきます。

 

税込金額を計算する数式の作成

ここでは、D列に税抜金額、G2セルに税率10パーセントを入力している例を使います。

E1に税込金額という見出しを入力し、E2セルを選択します。

E2には、=D2*(1+$G$2)と入力します。

D2は商品ごとに異なる税抜金額なので相対参照のままにします。

$G$2は税率を入力した共通セルなので、列と行を固定します。

Enterキーを押すと、税抜金額360円に対して税込金額396円が表示されます。

税率セルがパーセント表示なら、10%をそのまま数式内で利用できるため、100で割る必要はありません。

 

オートフィルハンドルによる下方向コピー

E2に数式を入力したら、セル右下に表示される小さな四角形へマウスポインターを合わせます。

ポインターが黒い十字に変わった部分がオートフィルハンドルです。

そのまま下へドラッグすると、数式がE3、E4以降へコピーされます。

隣接列に連続したデータがある場合は、オートフィルハンドルをダブルクリックしても、データの終わりまで数式を入力できます。

コピー後のE3では=D3*(1+$G$2)となり、商品ごとの金額だけが変化します。

税率の$G$2は変わらないため、すべての商品に同じ税率を適用できます。

数式を多数の行へ展開する場合ほど、先頭セルの固定設定を確認してからコピーすることが大切です。

 

Excel画面で確認する固定セルと数式バー

この画面では、数式バー内の$G$2と、シート上の税率セルG2が赤枠で示されています。

先頭セルのE2に正しい数式を入れてから、右下のフィルハンドルを下方向へ引く流れです。

数式バーで$の有無を見れば、見た目の計算結果だけでは分かりにくい参照ミスも確認できます。

 

セル固定で起こりやすいエラーと対処法

症状	主な原因	確認箇所
コピー後に計算結果が0になる	税率セルがずれた	$G$2の有無
横コピーで同じ列を参照する	列を固定しすぎた	$の位置
F4キーが反応しない	数式編集モード外	カーソル位置

続いては、セル固定でよくあるつまずきと修正方法を確認していきます。

 

コピー先で参照セルがずれるケース

最も多い失敗は、共通の条件セルを相対参照のままコピーしてしまうことです。

たとえば、=D2*G2を下へコピーすると、次の行は=D3*G3になります。

G3が空白なら計算結果は0になり、別の数値が入っていれば意図しない金額になるかもしれません。

この場合は、先頭の数式を=D2*$G$2へ修正します。

修正後に下へコピーし直せば、すべての行がG2の税率を参照します。

数式の途中にある参照セルを一つずつ読み、共通値なのか行ごとの値なのかを判定することが重要です。

 

固定しすぎて数式が変化しないケース

絶対参照は便利ですが、すべての参照先に$マークを付けると、必要な部分まで固定されます。

たとえば、商品別の金額を計算したいのに=$B$2*$C$2として下へコピーすると、どの行でも2行目の値だけを計算します。

下へコピーして各行の数量と単価を掛けるなら、=B2*C2のような相対参照が必要です。

固定すべきなのは、全行共通で使う税率や係数などに限られます。

複合参照の場合も同じで、$を付ける場所はコピー方向によって変わります。

計算対象のデータまで固定しないよう、変化させたいセルと固定したいセルを分けて考えましょう。

 

F4キーで参照形式を変更できないケース

F4キーを押しても$マークが付かないときは、数式の編集状態を確認します。

セルを一度選んだだけの状態では、F4キーは直前の操作を繰り返す機能として動作することがあります。

数式バーをクリックするか、セルをダブルクリックして、参照セルの文字列にカーソルを置いてください。

その状態でF4キーを押すと、参照形式が切り替わります。

ノートパソコンでは、ファンクションキーが画面の明るさや音量に割り当てられている場合があります。

その場合はFnキーとF4キーを同時に押すか、キーボード設定でファンクションキーの動作を切り替えます。

どうしても操作できない場合でも、$マークを直接入力すれば数式の作成は可能です。

 

まとめ エクセルでセルを固定する方法（絶対参照・＄マーク）

目的	使う参照形式	例
税率セルを完全に固定	絶対参照	$G$2
上端の行だけを固定	行固定	B$1
左端の列だけを固定	列固定	$A2

最後に、エクセルでセルを固定する方法をまとめます。

Excelで数式をコピーする際、参照先を変えたくないセルには$マークを付けます。

列と行の両方を固定する絶対参照は、$G$2のように記述します。

数式の入力中に参照セルを選択してF4キーを押せば、相対参照、絶対参照、行固定、列固定を順番に切り替えられます。

税率、割引率、手数料率、為替レートのように全行で共通する値は、絶対参照にする代表的な対象です。

横方向と下方向の両方へ数式をコピーする表では、A$1や$A1の複合参照を使い分けます。

下へコピーしても上段を参照したいなら行番号を固定し、右へコピーしても左端列を参照したいなら列記号を固定します。

固定の設定後は、コピー先の数式バーを見て、変わるべき参照と変わらない参照が想定どおりか確認しましょう。

絶対参照を正しく使えるようになると、数式のオートフィルが速くなり、表計算の入力ミスも減らせます。