【Excel】エクセルでセル範囲の固定（関数・数式・一部や選択範囲だけ・ずれるように・掛け算・コピーしても変わらない）

【Excel】エクセルで関数のセル範囲を固定する方法（数式・掛け算・コピーしても変わらない）

エクセルで数式をコピーしたとき、参照するセルが自動的にずれてしまって計算結果がおかしくなった経験はありませんか？

セル参照を固定する「絶対参照」を使いこなすことで、数式をコピーしても常に正しいセルを参照し続けることができます。

この記事では【Excel】エクセルで関数のセル範囲を固定する方法（数式・掛け算・コピーしても変わらない）について解説していきます。

ポイントは

です。

それでは詳しく見ていきましょう。

 

 

エクセルのセル参照の種類と固定の基本

エクセルのセル参照には「相対参照」「絶対参照」「複合参照」の3種類があります。

数式をコピーしたときに参照先がずれてしまう原因は、初期設定が「相対参照」になっているためです。

セルを固定するには「絶対参照」を使い、固定したい行・列の前に$記号を付けることが基本です。

 

相対参照とは何か

相対参照とは、数式をコピーした際にコピー先のセルの位置に合わせて参照先が自動的にずれる参照方式です。

たとえばA1セルに「=B1+C1」と入力してA2セルにコピーすると、自動的に「=B2+C2」に変わります。

この動作は同じ計算を複数行に適用したい場合には便利ですが、特定のセルを常に参照したい場合には問題となります。

下記のような消費税計算の表で考えてみましょう。

商品名	税抜価格（円）	消費税率	税込価格（円）
桜餅	200	10%	=B2*(1+C2)
カボチャ	350	10%	=B3*(1+C3)
マグロ	1,500	10%	=B4*(1+C4)
ハラス	800	10%	=B5*(1+C5)

この場合、消費税率が全商品共通でE1セルに入力されているとすると、C列ではなくE1セルを固定参照する必要があります。

 

絶対参照とは何か・$記号の意味

絶対参照とは、数式をコピーしても参照先のセルが変わらない参照方式です。

セルアドレスの列番号・行番号の前にそれぞれ$記号を付けることで絶対参照になります。

たとえば「$E$1」と記述すると、数式をどこにコピーしてもE1セルを参照し続けます。

$記号は「このアドレスを固定する」という意味を持つ記号であり、列・行それぞれに独立して付けることができます。

「$E$1」は列も行も固定、「$E1」は列のみ固定、「E$1」は行のみ固定となります。

 

複合参照とは何か・使いどころ

複合参照とは、列だけまたは行だけを固定する参照方式です。

「$E1」のように列のみを固定すると、数式を横方向にコピーしてもE列を参照し続けますが、縦方向にコピーすると行番号はずれていきます。

逆に「E$1」のように行のみを固定すると、縦方向のコピーでも1行目を参照し続けますが、横方向のコピーでは列がずれます。

複合参照は掛け算表や九九の表のように、縦横両方向に数式をコピーしたい場面で非常に役立ちます。

エクセルでF4キーを使ってセルを素早く固定する方法

数式を入力する際に$記号を手入力するのは手間がかかります。

F4キーを使えば、絶対参照・複合参照を素早く切り替えることができます。

F4キーの操作方法と切り替えの順序

数式入力中にセルアドレスをクリックまたは入力した直後にF4キーを押すと、参照方式が順番に切り替わります。

切り替わる順序は以下の通りです。

・1回目：「E1」→「$E$1」（列・行ともに固定）

・2回目：「$E$1」→「E$1」（行のみ固定）

・3回目：「E$1」→「$E1」（列のみ固定）

・4回目：「$E1」→「E1」（元の相対参照に戻る）

F4キーを押すたびに参照方式が循環するため、目的の参照方式になるまで繰り返し押せばよく、$を手入力する必要がありません。

既存の数式を絶対参照に変更する方法

すでに入力済みの数式を絶対参照に変更したい場合も、F4キーが使えます。

対象のセルをダブルクリックして編集モードにし、変更したいセルアドレス部分にカーソルを置いてF4キーを押すだけです。

複数のセルアドレスを変更したい場合は、各アドレスにカーソルを移動しながらF4キーを押していきます。

数式が長い場合は、変更したいアドレス全体をドラッグ選択してからF4キーを押すとまとめて変更できます。

F4キーが機能しない場合の対処法

ノートパソコンなどでF4キーを押しても参照方式が切り替わらない場合は、Fnキーと組み合わせて「Fn+F4」を押してみましょう。

ノートパソコンのファンクションキーはメーカーによって動作が異なるため、F4単独で機能しない場合はFnキーとの組み合わせを試すことが基本的な対処法です。

また、数式の編集モードではなく通常の選択状態でF4キーを押しても参照方式は変わりません。必ずセルをダブルクリックして編集モードにしてから操作しましょう。

エクセルで掛け算のセルを固定する方法

掛け算で特定のセル（単価・税率・係数など）を固定して計算する場面は非常に多いです。

絶対参照を使った掛け算の具体的な例を確認していきましょう。

 

 

消費税計算で税率セルを固定する方法

消費税率が入力されたセルを固定して、各商品の税込価格を計算する例を見てみましょう。

E1セルに消費税率「0.1」が入力されているとします。

商品名	税抜価格（円）	税込価格（円）
チョコ	500	=B2*(1+$E$1)
マシュマロ	300	=B3*(1+$E$1)
柏餅	180	=B4*(1+$E$1)
アボカド	250	=B5*(1+$E$1)

C2セルに「=B2*(1+$E$1)」と入力し、C3からC5にコピーしても、$E$1の部分は固定されているためE1セルの消費税率を常に参照し続けます。

 

税率が変わった際もE1セルの値を変えるだけで全商品の税込価格が自動更新されます。

掛け算表で複合参照を活用する方法

複合参照が最も威力を発揮するのが、縦横両方向に数式をコピーする掛け算表です。

A列に数量、1行目に単価が入力されている表で、各セルに「数量×単価」を計算したい場合を考えます。

	ボルト(100円)	ネジ(50円)	マシュマロ(80円)
10個	=($A2*B$1)	=($A2*C$1)	=($A2*D$1)
20個	=($A3*B$1)	=($A3*C$1)	=($A3*D$1)
30個	=($A4*B$1)	=($A4*C$1)	=($A4*D$1)

B2セルに「=$A2*B$1」と入力することで、数量（A列）は列を固定・行は相対、単価（1行目）は行を固定・列は相対となり、縦横どちらにコピーしても正しく計算されます。

係数・割引率など固定値を使った計算への応用

絶対参照は消費税や掛け算表以外にも、割引率・為替レート・係数など固定値を参照するあらゆる計算に応用できます。

たとえば為替レートをE1セルに入力し、各商品のドル建て価格を円換算する場合は「=B2*$E$1」のように記述します。

為替レートが変動した際もE1セルの値を更新するだけで全行の円換算価格が自動的に再計算されます。

変動する可能性がある係数は必ず独立したセルに入力し、そのセルを絶対参照で参照する習慣をつけると、メンテナンスが非常に楽になります。

エクセルで関数のセル範囲を固定する方法

SUM・AVERAGE・COUNTIFなどの関数でもセル範囲を固定することは頻繁に必要になります。

関数の引数に指定する範囲を絶対参照にする方法を確認しましょう。

 

 

SUM・AVERAGE関数で範囲を固定する方法

SUM関数やAVERAGE関数の引数範囲を固定するには、範囲指定部分に$記号を付けます。

たとえば「=SUM($B$2:$B$10)」とすることで、数式をコピーしても常にB2からB10の範囲の合計を計算します。

商品名	売上（円）	構成比
カツオ	25,000	=B2/SUM($B$2:$B$6)
マグロ	38,000	=B3/SUM($B$2:$B$6)
ハラス	18,000	=B4/SUM($B$2:$B$6)
桜餅	12,000	=B5/SUM($B$2:$B$6)
柏餅	9,000	=B6/SUM($B$2:$B$6)

構成比の計算では、分母のSUM範囲を$で固定しておくことで、数式を下にコピーしても合計値の参照範囲がずれません。

 

 

VLOOKUP・INDEX/MATCH関数で参照範囲を固定する方法

VLOOKUP関数やINDEX/MATCH関数では、参照テーブルの範囲を絶対参照で固定することが特に重要です。

「=VLOOKUP(A2,$E$2:$G$10,2,FALSE)」のように参照テーブル範囲を$で固定することで、数式を下にコピーしても参照テーブルの範囲がずれません。

VLOOKUP関数で参照範囲を相対参照のままにしてしまうと、数式をコピーするたびに参照テーブルがずれてエラーや誤った結果になるため、必ず絶対参照にする習慣が重要です。

 

 

COUNTIF・SUMIF関数で条件範囲を固定する方法

COUNTIF関数やSUMIF関数でも、条件範囲を絶対参照で固定することが大切です。

「=COUNTIF($B$2:$B$20,D2)」のように条件範囲を固定することで、D列の条件を変えながら数式をコピーしても、カウント対象のB列範囲が正しく固定されたままになります。

条件セル（D2）は相対参照のままにすることで、コピーに合わせて条件が自動的に変わる仕組みも同時に活用できます。

範囲を固定しながら条件だけを動かすという使い方は、集計表を効率よく作成する上で非常に役立つテクニックです。

 

エクセルでセル範囲の固定に関するよくあるミスと対処法

絶対参照を使う際によくあるミスと、その対処法を確認しておきましょう。

 

$の付け忘れによるエラーの対処法

数式をコピーした後に参照先がずれてしまう場合は、$の付け忘れが原因であることがほとんどです。

対処法としては、数式バーで数式を確認し、固定すべきセルアドレスにカーソルを置いてF4キーを押すことで$を追加できます。

数式を入力する段階で「この参照は固定が必要か？」を意識する習慣をつけることが、ミスを減らす最も効果的な方法です。

 

$を付けすぎて数式が正しく機能しない場合

逆に、すべてのセルに$を付けてしまうと、本来コピーに合わせてずれてほしい参照も固定されてしまい、計算結果がおかしくなることがあります。

相対参照が必要な部分（コピーに合わせてずれてほしい部分）と絶対参照が必要な部分（常に固定したい部分）を明確に区別することが大切です。

迷った場合は、数式を2〜3行下にコピーしてみて、参照先が意図通りに変化しているか確認するとよいでしょう。

 

名前定義を使ってセル範囲に名前を付ける方法

よく使うセルや範囲に名前を定義しておくと、$記号を使わなくても常に固定参照として扱われます。

範囲を選択して数式バー左の「名前ボックス」に名前を入力するか、「数式」タブ→「名前の定義」から設定できます。

たとえば消費税率のセルに「税率」と名前を付ければ、「=B2*(1+税率)」のように直感的でわかりやすい数式が書けます。

名前定義を使うと数式の可読性が大きく上がり、他の人がファイルを見たときにも内容が理解しやすくなります。

まとめ　エクセルで指定範囲の固定（関数・一部や選択範囲だけ・ずれるように・掛け算・コピーしても変わらない）

エクセルで関数のセル範囲を固定する方法をまとめると、以下の通りです。

・セル参照の種類：相対参照（ずれる）・絶対参照（固定）・複合参照（行または列のみ固定）の3種類を使い分ける

・$記号の使い方：列・行の前に$を付けると固定。$E$1で完全固定、$E1で列のみ固定、E$1で行のみ固定

・F4キーの活用：数式入力中にF4キーを押すと参照方式が循環切り替え。手入力より素早く設定できる

・掛け算への応用：消費税率・係数・為替レートなど固定値のセルを絶対参照にしてコピーに対応

・関数での固定：SUM構成比・VLOOKUP参照テーブル・COUNTIF条件範囲は必ず絶対参照で固定する

・名前定義の活用：頻繁に参照するセルに名前を付けると$なしで固定参照でき、数式の可読性も向上する

絶対参照と複合参照を正しく使いこなすことで、数式のコピーミスが大幅に減り作業効率が向上します。

まずはF4キーを使った素早い参照切り替えから習得して、徐々に複合参照や名前定義へと応用していきましょう。