Excelで数式をコピーしたとき、参照するセルまで一緒にずれてしまい、計算結果が合わなくなることがあります。
このような場面で役立つのが、列番号や行番号の前に＄を付けてセル参照を固定する絶対参照です。
たとえば商品価格に消費税率を掛ける表では、税率が入力されたセルを固定しなければ、下方向へ数式をコピーした際に参照先が空白セルへ移動してしまいます。
セル参照を固定したいときは、列番号・行番号の前に＄を付けます。
A1は相対参照、＄A＄1は絶対参照、＄A1とA＄1は複合参照です。
この記事では、キーボードで＄を付ける方法、数式バーで直接入力する方法、絶対参照と複合参照の使い分けを、Excelのサンプルデータを交えながら解説します。
数式を正しくコピーできるようになれば、売上表、見積書、在庫表、割合計算などの作業を効率よく進められるでしょう。
エクセルで＄をつける方法【F4キーによる絶対参照】
それではまず、Excelでセル参照に＄を付ける最も手軽な方法について解説していきます。
| 商品 | 税抜価格 | 税率 | 税込価格 |
|---|---|---|---|
| ノート | 1200 | 10% | 1320 |
| ペン | 300 | 330 |
この例では、C2に税率10％を入力し、D2には税抜価格へ税率を加算する数式を設定します。
数式を下の行へコピーしても税率セルC2を参照し続けるため、数式は=B2*(1+$C$2)と入力する必要があります。
F4キーで参照形式を切り替える操作
数式を入力中に参照セルを選択し、その参照部分にカーソルを置いてF4キーを押すと、参照形式を順番に切り替えられます。
Windows版Excelでは、セル番地をクリックしてからF4キーを押す方法が基本です。
最初にC2と表示されている状態でF4キーを1回押すと、＄C＄2へ変化します。
この状態は列Cと行2をどちらも固定する絶対参照です。
続けてF4キーを押すたびに、C＄2、＄C2、C2の順で表示が切り替わります。
F4キーを押したときの切り替わり順です。
C2 → ＄C＄2 → C＄2 → ＄C2 → C2
ノートパソコンでは、Fnキーを押しながらF4キーを押さなければ機能しない場合があります。
F4キーで音量や画面の明るさが変わる場合は、Fn＋F4を試してみましょう。
Excelの設定やキーボードによって操作感は異なりますが、数式バーの表示を確認すれば、＄が付いたかどうかを確実に判断できます。
税込価格を計算する数式の入力
D2セルを選択し、イコールから数式の入力を始めます。
まずB2をクリックし、掛け算記号を入力した後、かっこを入力します。
続けて1とプラス記号を入力し、税率があるC2セルをクリックしてください。
このままでは数式は=B2*(1+C2)となり、コピー時にC2の位置が変わる相対参照のままです。
C2の文字列にカーソルを合わせてF4キーを1回押し、＄C＄2に変更してからEnterキーで確定します。
入力する数式は次のとおりです。
=B2*(1+$C$2)
税抜価格が1200円、税率が10％なら、1200×1.1となるため、D2には1320と表示されます。
税率セルの表示形式がパーセンテージであれば、10％は計算上0.1として扱われます。
税率を10ではなく10％として入力することが、正しい計算結果につながる大切なポイントです。
オートフィルで数式をコピーする手順
D2セルに正しい数式を設定できたら、セル右下に表示される小さな四角形へマウスポインターを移動します。
ポインターが黒い十字に変わったら、下の行までドラッグしてください。
これがオートフィルです。
下方向へコピーすると、B2はB3、B4のように行番号が変化します。
一方で＄C＄2は固定されているため、どの行でも同じ税率セルを参照します。
変えてほしい参照は＄を付けず、変えてほしくない参照だけに＄を付けることが、絶対参照を使いこなす基本です。
数式をコピーした後は、任意のセルを選択して数式バーを確認すると安心です。
D3には=B3*(1+$C$2)、D4には=B4*(1+$C$2)と表示されていれば、設定は成功しています。
【操作のポイント】数式内のセル番地を選択してからF4キーを押すと、＄を手入力するよりも速く、入力ミスも減らせます。
絶対参照と相対参照の違い
続いては、＄を付ける前に知っておきたい絶対参照と相対参照の違いを確認していきます。
| 参照形式 | 入力例 | コピー後の変化 |
|---|---|---|
| 相対参照 | A1 | コピー先に応じて列・行が変化 |
| 絶対参照 | ＄A＄1 | 列・行とも固定 |
相対参照の仕組み
Excelでセル番地を指定して数式を作成すると、初期設定では相対参照になります。
たとえばD2セルに=B2+C2と入力し、D3へコピーした場合、コピー後の数式は=B3+C3に変わります。
これは、コピー元から見た参照先の位置関係を保つ仕組みです。
価格と数量を各行で掛け算する表のように、同じ計算パターンを繰り返す場合、相対参照は非常に便利です。
セルをコピーするだけで、各行に合わせた計算式へ自動調整されます。
ただし、固定の単価、割引率、消費税率、為替レートなどを参照する場合には、相対参照だけでは不都合が生じます。
コピー先で参照セルがずれるため、空白や別の数値を計算に使ってしまう可能性があるからです。
絶対参照が必要になる場面
絶対参照は、数式をコピーしても参照先の列と行を変えたくないときに使います。
代表例は、表の端に入力した税率、割引率、手数料率、目標値、基準日です。
たとえばC2に割引率5％を入力し、各商品の割引後価格を計算するなら、数式は=B5*(1-$C$2)のように作成します。
このとき、＄C＄2はどのセルへコピーしても必ずC2を指します。
絶対参照を使う代表的な数式です。
=売上金額*(1-割引率)
セル参照で表す場合は=B2*(1-$C$2)です。
数式そのものは難しくありませんが、どのセルを固定するかを先に考えることが重要です。
表を作り始める段階で、共通条件となる値をまとめて配置しておくと、絶対参照が分かりやすくなります。
参照先がずれたときの確認方法
コピー後の計算結果が0になった、想定より大きい値になったという場合は、数式内のセル番地を確認してください。
問題のセルをクリックすると、数式バーに計算式が表示されます。
さらに数式編集状態にすると、参照しているセルが色付きの枠線で示されます。
税率を参照するはずなのに下の空白セルを指しているなら、絶対参照が設定されていないことが原因です。
誤った数式を修正し、参照セルの前に＄を付けてください。
数式のエラーは、計算記号よりセル参照のずれが原因になっているケースが少なくありません。
修正後は、最初のセルから再度オートフィルを実行し、複数行の結果を見比べるとよいでしょう。
【操作のポイント】コピー後に参照先が動くのは相対参照の正常な動作です。固定値だけを絶対参照に変更します。
複合参照における列固定と行固定
続いては、列または行だけを固定する複合参照について解説していきます。
| 参照形式 | 固定される部分 | 主な用途 |
|---|---|---|
| ＄A1 | 列A | 横方向へコピーする計算 |
| A＄1 | 行1 | 縦方向へコピーする計算 |
列だけを固定する＄A1
＄A1は、列記号Aの前だけに＄を付けた参照形式です。
この数式を横方向へコピーしても、列Aは変化しません。
一方、行番号1には＄がないため、下方向へコピーすれば2、3、4と変化します。
たとえばA列に商品名または基準値があり、B列からE列へ月別の計算結果を並べる場合に利用できます。
横にコピーするたびにA列の基準値を参照し、行だけは各データ行に応じて変化させる構成です。
列を固定したい場合は列番号の前に＄を置くと覚えると分かりやすいでしょう。
＄A1をC5からD5へコピーしても、参照先の列はA列のままです。
ただし、C5からC6へコピーした場合は、行番号が1から2へ変化します。
行だけを固定するA＄1
A＄1は、行番号1の前だけに＄を付けた参照形式です。
この形式では、下方向へコピーしても1行目を参照し続けます。
ただし横方向へコピーすると、列AはB、C、Dのように変わります。
月別の単価が1行目に並んでおり、各商品の数量を掛ける表ではA＄1の考え方が役立ちます。
横へコピーすることで、月ごとの単価セルを順番に参照できます。
その一方で、常に見出しや基準値がある1行目を参照する状態を維持できます。
行を固定したい場合は行番号の前に＄を置くことが判断の基準です。
掛け算表で使う複合参照
複合参照を理解するために、掛け算表を作る場面を考えてみましょう。
B1からE1に1、2、3、4を入力し、A2からA5にも1、2、3、4を入力します。
B2セルに=$A2*B$1と入力して、右方向と下方向へコピーしてください。
掛け算表の左上セルに入力する数式です。
=$A2*B$1
$A2によって左端の数値が常にA列から取得され、B$1によって上端の数値が常に1行目から取得されます。
右へコピーするとB$1はC$1、D$1へ変化し、下へコピーすると$A2は$A3、$A4へ変化します。
このように、コピーする方向ごとに固定の有無を設計するのが複合参照です。
最初は複雑に見えるかもしれませんが、横へコピーしたときに固定したいもの、縦へコピーしたときに固定したいものを分けて考えれば理解しやすくなります。
【操作のポイント】複合参照は、横へコピーする方向と縦へコピーする方向を実際に想像して、＄を付ける位置を決めます。
数式バーとキーボードによる＄の入力
続いては、F4キーが使えない場合にも対応できる、数式バーとキーボードによる入力方法を確認していきます。
| 入力方法 | 特徴 |
|---|---|
| F4キー | 参照形式を素早く切り替えられる |
| 数式バーへの入力 | 任意の位置に直接＄を追加できる |
数式バーで＄を直接入力する方法
セルを選択すると、ワークシート上部の数式バーに、そのセルの数式または値が表示されます。
数式バーをクリックすると、セル内の数式を直接編集できます。
たとえば=B2*(1+C2)と表示されている場合は、C2の前に＄を入力して=B2*(1+$C$2)へ修正します。
日本語キーボードで＄を入力するには、一般にShiftキーを押しながら4キーを押します。
キーボード配列によっては位置が異なるため、入力できない場合はIMEの状態やキーボード設定を確認してください。
＄は半角で入力する必要があります。
全角の＄を入力すると、Excelの数式として認識されず、エラーになる場合があります。
編集モードでのカーソル位置
セルをダブルクリックするか、セルを選択してF2キーを押すと、セル内の数式を編集できます。
数式バーを使わずに、セルの中で直接＄を追加することも可能です。
ただし長い数式では、セル内より数式バーで編集したほうが文字列を見失いにくいでしょう。
参照セルが複数ある数式では、変更したいセル番地だけに＄を付けることが大切です。
すべてのセル参照を絶対参照にすると、コピー先に合わせて変えるべき値まで固定され、別の誤りにつながります。
数式の中で固定する値と変動させる値を区別してから編集すると、修正の精度が上がります。
数式の再計算と表示形式の確認
数式を修正してEnterキーを押すと、Excelは通常、自動的に計算結果を更新します。
修正したのに結果が変わらない場合は、数式が文字列として入力されていないか確認してください。
セルの先頭にアポストロフィが付いていると、数式ではなく文字列として扱われます。
また、数式の表示結果が期待どおりでも、参照先のセルに数値ではなく文字列が入っている場合があります。
特に税率では、10％と10の違いを確認しましょう。
税率10％を入力する場合は、セルに10%と入力するか、0.1を入力して表示形式をパーセンテージに設定します。
セルに10だけを入力すると、計算では10倍として扱われます。
数式の内容、参照先、セルの表示形式を順番に確認することで、多くの計算ミスを防げます。
【操作のポイント】F4キーが利用しづらいときは、数式バーに半角の＄を直接入力して絶対参照へ変更します。
絶対参照を使う計算式の実例
続いては、実務でよく使う絶対参照の計算式を確認していきます。
| 項目 | 数値 | 固定するセル |
|---|---|---|
| 商品Aの売上 | 25000 | 目標売上100000 |
| 商品Bの売上 | 18000 | 目標売上100000 |
構成比を求める数式
各商品の売上が全体に占める割合を求める場合、分母となる合計セルを固定します。
たとえばB2からB6に売上金額があり、B7に合計が入力されているとします。
C2へ=B2/$B$7と入力し、下方向へコピーしてください。
構成比を求める数式です。
=B2/$B$7
分子のB2は各行の売上へ変化する必要があるため、＄を付けません。
分母のB7は常に合計売上を参照する必要があるため、＄B＄7にします。
結果のセルをパーセンテージ表示にすれば、各商品の構成比を見やすく確認できます。
割合計算では、基準となる合計値を絶対参照にするのが基本です。
割引率と消費税率を組み合わせる数式
割引と税率を組み合わせる計算でも、絶対参照は役立ちます。
たとえばB2に税抜価格、C1に割引率、D1に税率が入力されている場合を考えます。
割引後に消費税を加算する数式は、=B2*(1-$C$1)*(1+$D$1)のように作成できます。
割引後の税込価格を求める数式です。
=B2*(1-$C$1)*(1+$D$1)
B2は商品ごとに変化させ、C1とD1は共通の条件として固定します。
かっこを使うことで、割引を適用した後に消費税を加える計算順序が明確になります。
数式が長くなるほど、どのセルを固定したか分かりにくくなるため、条件セルには分かりやすい見出しを付けておくと便利です。
別シートを参照する数式
絶対参照は、同じシート内だけでなく別シートを参照する数式でも使用できます。
たとえば設定シートのB2に為替レートがあり、売上シートで換算計算を行う場合、=B2*設定!$B$2のように入力します。
シート名の後ろに続くセル番地へ＄を付けると、コピー後も設定シートのB2を参照し続けます。
シート名に空白がある場合は、Excelがシングルクォーテーションを付けて表示することがあります。
これは正常な表記です。
別シートの固定値を参照するときも、固定したいセル番地に＄を付けます。
設定値を一か所へ集約しておけば、税率やレートが変わったときにも、対象セルだけを書き換えることで表全体を更新できます。
【操作のポイント】割合、税率、割引率、合計、為替レートのような共通条件は、絶対参照にする候補です。
まとめ エクセルで＄をつける方法（絶対参照のセル固定）
Excelで＄を付ける方法と、セル参照を固定する絶対参照について解説しました。
数式をコピーしたときに参照先を変えたくない場合は、列番号と行番号の前に＄を付けた＄A＄1形式を使います。
最も簡単な操作は、数式内のセル番地を選択してF4キーを押す方法です。
F4キーを押すたびに、相対参照、絶対参照、行固定、列固定を切り替えられます。
F4キーが使えない環境では、数式バーでセル番地の前に半角の＄を直接入力しても問題ありません。
税率や割引率、合計値のように常に同じセルを参照する数値には絶対参照を使い、商品ごとに変わる価格や数量には相対参照を使いましょう。
横方向または縦方向だけで参照を固定したい場合は、＄A1やA＄1の複合参照が便利です。
数式をコピーした後に結果が合わないときは、数式バーを開き、参照先のセル番地が意図どおりか確認してください。
＄の役割を理解すれば、繰り返し計算の作業を大幅に減らし、Excelの表を正確に作成できるようになります。
