Excelで数式をコピーしたとき、参照先のセルがずれて計算結果が変わってしまうことがあります。
このようなときに役立つのが、セル参照を固定するF4キーと$記号です。
相対参照、絶対参照、複合参照の違いを理解すると、消費税計算、単価表、割引率、集計表などを効率よく作成できます。
セル参照を固定する基本ポイント
・F4キーで参照形式を切り替える
・$A$1は列と行を両方固定する
・A$1と$A1は片方だけを固定する
この記事では、数式をコピーしても参照先が変わらないようにする手順を、サンプルデータと画面イメージを交えて解説します。
最初に覚えたい結論は、固定したいセルを数式内で選択してF4キーを押すことです。
それでは、具体的な使い方を確認していきましょう。
F4キーでセル参照を固定する手順
| 商品 | 税抜価格 | 税率 | 税込価格 |
|---|---|---|---|
| ノート | 500 | 10% | 550 |
| ペン | 200 | 10% | 220 |
それではまず、F4キーを使って数式の参照を固定する操作について解説していきます。
数式入力中にF4キーを押す操作
サンプルでは、B列に税抜価格、F1セルに税率10%を入力し、C列で税込価格を求めるケースを考えます。
1行目は見出しとして使い、データは2行目から入力する前提です。
C2セルに=B2*F1と入力しただけでは、下方向へのコピー時にF1がF2、F3へと移動してしまいます。
このため、数式バーでF1の部分を選択し、キーボードのF4キーを1回押します。
すると参照が$F$1に変わり、数式は=B2*$F$1となります。
F4キーは数式を確定する前でも、数式バーを編集している途中でも使えます。
ノートパソコンでは、F4キーに音量や画面設定の機能が割り当てられている場合があります。
その場合はFnキーを押しながらF4キーを押すと、セル参照の切り替えができることがあります。
C2セルに入力する数式
=B2*$F$1
B2は商品ごとに変わり、$F$1はすべての行で税率セルを参照し続けます。
F4キーを押す回数と参照形式
F4キーは1回だけの機能ではなく、押すたびに参照形式が順番に変わります。
F1を選択した状態で押すと、$F$1、F$1、$F1、F1の順に切り替わります。
最初の$F$1は列Fと行1を固定する絶対参照です。
次のF$1は行1だけを固定し、列はコピー先に応じて変化します。
その次の$F1は列Fだけを固定し、行はコピー先に応じて変化する形式です。
最後のF1は、列と行のどちらも固定しない通常の相対参照に戻ります。
目的の$の位置になったところで入力を確定することが、F4キー操作の要点です。
F4キーによる切り替え順
F1 → $F$1 → F$1 → $F1 → F1
オートフィルで数式をコピーする操作
C2セルの数式を作成したら、セル右下に表示される小さな四角形であるフィルハンドルを使います。
フィルハンドルを下へドラッグするか、隣接データが続いている場合はダブルクリックしましょう。
コピー後のC3セルでは、B2がB3に変わる一方、$F$1は変化しません。
この動きにより、商品ごとの価格だけを変えながら、共通の税率を使った計算ができます。
固定は数式をコピーする前に設定するため、コピー後に一つずつ修正する必要がありません。
【操作のポイント】数式を下へコピーするなら、共通条件が入ったセルだけを$で固定します。
$記号による絶対参照と相対参照の違い
| 参照形式 | 固定される場所 | 主な用途 |
|---|---|---|
| A1 | なし | 通常のコピー計算 |
| $A$1 | 列と行 | 税率や基準値 |
| A$1 | 行のみ | 横方向の計算 |
| $A1 | 列のみ | 縦方向の計算 |
続いては、$記号が表す固定範囲と、相対参照との違いを確認していきます。
相対参照で行と列が移動する仕組み
Excelで通常どおりセル番地を入力すると、相対参照になります。
たとえばC2セルに=A2+B2と入力し、1行下のC3セルへコピーすると、数式は=A3+B3に変わります。
これは、コピー元から見た相対的な位置関係をExcelが維持するためです。
売上、数量、単価のように、各行で対応するデータを計算する表では相対参照が便利です。
一方で、常に同じ税率や目標値を使う場合は、相対参照のままでは意図しないセルを参照します。
コピー先に合わせて変えてよいセルは相対参照のまま残すことが重要です。
すべてのセルを固定する必要はなく、固定が必要な部分だけを見極める考え方が必要です。
絶対参照で同じセルを参照し続ける仕組み
$A$1のように列記号の前と行番号の前に$を付けると、絶対参照になります。
列Aと行1の両方が固定されるため、右へコピーしても下へコピーしても参照先はA1セルのままです。
たとえばF1セルに消費税率、G1セルに為替レート、H1セルに割引率を置く場合に使えます。
数式の中で同じ条件セルを何度も使うなら、$を手入力するよりF4キーで指定する方法が入力ミスを減らします。
絶対参照を使う数式例
=B2*(1+$F$1)
B2は商品ごとに変化し、F1の税率だけを固定します。
複合参照で片方だけを固定する仕組み
A$1と$A1は、行または列だけを固定する複合参照です。
横に月別データ、縦に商品別データを並べた表では、複合参照が特に役立ちます。
A$1は行1を固定するため、下へコピーしても見出し行を参照し続けます。
$A1は列Aを固定するため、右へコピーしても左端の商品名や係数を参照し続けます。
表を縦と横の両方へコピーする場合は、どちらが共通項目なのかを先に判断しましょう。
【操作のポイント】数式をどの方向へコピーするかを確認すると、$を付ける位置を決めやすくなります。
税率計算での絶対参照の活用
| A | B | C | F |
|---|---|---|---|
| 商品 | 税抜価格 | 税込価格 | 税率 |
| ノート | 500 | =B2*(1+$F$1) | 10% |
続いては、絶対参照を利用した税率計算の数式を詳しく解説していきます。
税込価格を求める基本数式
税抜価格から税込価格を計算する場合は、税抜価格に1と税率を加えた値を掛けます。
B2セルが税抜価格、F1セルが税率であれば、C2セルの数式は=B2*(1+$F$1)です。
税込価格の計算式
税抜価格 × （1 ＋ 税率）
Excelの数式では =B2*(1+$F$1) と入力します。
税率が10%なら、1+10%は1.1となり、500円は550円になります。
F1を固定しなければ、C3セル以降で参照先がずれて空白セルを参照する可能性があります。
税率や係数のように表内で共通の値は、絶対参照にする代表例です。
消費税額だけを求める数式
税込価格ではなく、消費税額だけを求めたい場合は、税抜価格に税率を掛けます。
D2セルへ=B2*$F$1と入力すると、税抜価格500円に対する消費税50円が表示されます。
端数を四捨五入したい場合は、=ROUND(B2*$F$1,0)のようにROUND関数を組み合わせます。
消費税額を整数に丸める数式
=ROUND(B2*$F$1,0)
0は小数点以下を表示しない指定です。
切り捨てならROUNDDOWN関数、切り上げならROUNDUP関数を使います。
会社や店舗の端数処理ルールに応じて関数を選ぶ場面です。
税率セルを変更したときの確認方法
F1セルの税率を10%から8%へ変更すると、$F$1を使うすべての数式結果が自動更新されます。
数式を何十行コピーしていても、修正する場所は税率セル1か所だけです。
条件を一元管理できることは、絶対参照を使う大きなメリットです。
ただし、軽減税率の商品と標準税率の商品が混在する場合は、商品ごとに税率列を用意する方法もあります。
すべての行に同一税率を適用する表かどうかを確認してから、固定セルを設計しましょう。
【操作のポイント】税率の変更予定がある表では、税率を1つのセルに集約して絶対参照します。
複合参照を使った掛け算表の作成
| B | C | D | |
|---|---|---|---|
| 1 | 1 | 2 | 3 |
| 2 | 1 | 2 | 3 |
| 3 | 2 | 4 | 6 |
続いては、行と列を片方ずつ固定する複合参照の活用方法を確認していきます。
行見出しと列見出しを参照する数式
掛け算表では、左端の行見出しと上端の列見出しを掛け合わせます。
B2セルに計算結果を表示する場合、左側のA2と上側のB1を参照します。
このときに使う数式は=$A2*B$1です。
$A2は列Aを固定し、B$1は行1を固定しています。
下方向へコピーしても左端の数値を参照し、右方向へコピーしても上端の数値を参照する仕組みです。
複合参照は、縦方向と横方向の両方に数式をコピーする表で真価を発揮します。
掛け算表の先頭セルに入力する数式
=$A2*B$1
列Aは固定、行1は固定として、計算部分だけを自動的に変化させます。
F4キーで複合参照に切り替える操作
=$A2*B$1を手入力する必要はありません。
最初に=A2*B1と入力し、A2の部分を選択してF4キーを3回押すと$A2になります。
次にB1の部分を選択してF4キーを2回押すとB$1になります。
F4キーの押下回数は、最終的に残したい$の位置によって変わります。
数式バーで参照部分だけをクリックして選択すると、意図しない部分を変更せずに済みます。
数式を確定後、B2セルのフィルハンドルを右へ、さらに下へドラッグします。
Excelのバージョンによっては、範囲を選択してからCtrlキーとDキー、またはCtrlキーとRキーでコピーする方法もあります。
複合参照が役立つ実務表
複合参照は掛け算表だけでなく、単価表、予算配分表、評価シート、月別実績表にも活用できます。
たとえば、列に月別の達成率、行に担当者別の基準額を置く場合、基準額の列と達成率の行を固定して計算できます。
同じ形の計算式を広い範囲にコピーする作業ほど、複合参照による効率化を実感しやすいでしょう。
表の左端と上端にある条件を掛け合わせる構造を見つけたら、複合参照を検討します。
【操作のポイント】先頭の計算セルで正しく複合参照を設定してから、右方向と下方向へコピーします。
セル参照の固定で起こりやすいエラー
| 状況 | 主な原因 | 確認する場所 |
|---|---|---|
| コピー後に値が同じ | 計算対象まで固定 | $の位置 |
| 結果が空白やエラー | 条件セルを未固定 | 参照先 |
続いては、セル参照の固定で起こりやすいミスと確認方法を解説していきます。
計算対象セルまで固定してしまうケース
税込価格の数式で=B2*$F$1とすべきところを、=$B$2*$F$1としてしまうことがあります。
この数式を下へコピーすると、すべての行がB2セルの価格を使って計算されます。
結果がすべて同じ値になる場合は、商品ごとに変わるべきセルまで固定していないか確認しましょう。
固定するのは共通条件であり、行ごとのデータそのものではありません。
数式バーをクリックし、コピー先のセルごとに参照番地がどのように変化しているかを見ると原因を特定できます。
F4キーで画面機能が動くケース
ノートパソコンではF4キーを押しても参照形式が変わらず、音量や明るさなどの機能が動く場合があります。
キーボードのFnキーとF4キーを同時に押す方法を試しましょう。
機種によってはFn Lockの状態を切り替える必要があることもあります。
また、日本語入力中やセルの編集状態でないと、期待どおりに参照を切り替えられないことがあります。
数式バーでセル番地を選択した状態にしてからF4キーを押すと、操作対象が明確になります。
数式エラーを確認する手順
#REF!と表示される場合は、参照していたセルや列が削除されている可能性があります。
#VALUE!が表示される場合は、計算に使うセルへ文字列が入力されていないかを確認します。
固定参照自体は正しくても、参照先のデータ形式が違えば計算結果は正しくなりません。
数式タブの数式の検証や、参照元のトレース機能を使うと、どのセルを参照しているかを視覚的に確認できます。
確認の順番
数式バーで$の位置を確認します。
次に参照先セルの値と表示形式を確認します。
【操作のポイント】コピー先の数式を1つ選び、意図した行と列だけが変化しているか確認します。
まとめ エクセルでF4キーと$を使ったセル参照固定の手順
Excelで数式をコピーするときは、共通して使う条件セルをF4キーで固定します。
$A$1は列と行を固定する絶対参照であり、税率、割引率、為替レート、基準値などに適しています。
A$1は行だけを固定し、$A1は列だけを固定する複合参照です。
横と縦の両方へ数式をコピーする掛け算表や集計表では、複合参照が便利です。
数式のコピー前に、どのセルが変化し、どのセルが共通なのかを整理することが正確な表作成につながります。
まずは税率計算で=B2*$F$1のような数式を試し、F4キーを押す回数と$の位置に慣れていきましょう。
