Excelで数式をコピーした際に、列は変わってよいものの、参照する行だけは動かしたくない場面があります。
そのようなときに役立つのが、行番号の前にドル記号を付けるB$参照です。
B$2のように書くと、横方向へコピーしたときは列記号が変化し、縦方向へコピーしても2行目だけは固定されます。
この記事では、混合参照の基本、数式の書き方、F4キーでの切り替え、実務で使いやすい例まで順番に解説します。
・B$2は列を相対参照、行を絶対参照にする書き方です。
・横コピーではBがC、Dへ変わります。
・縦コピーでは行番号の2だけが固定されます。
B$参照による行固定の数式入力
| 商品 | 単価 | 数量 | 消費税率 |
|---|---|---|---|
| ノート | 120 | 3 | 10% |
| ペン | 150 | 2 | 10% |
それではまず、B$参照を使って行だけを固定する数式について解説していきます。
B$2は、B列はコピー先に応じて変化させ、2行目は常に参照するという意味です。
Bの記号が示す参照範囲
B$2では、列記号Bの前にドル記号がないため、列は相対参照です。
一方で行番号2の前にはドル記号があるため、行は絶対参照となります。
たとえばB$2を右隣へコピーするとC$2になり、さらに右へコピーするとD$2になります。
しかし下方向へコピーしても、B$2の行番号はB$3やB$4へ変わりません。
この性質は、横方向に並んだ月別データ、商品別データ、評価項目などを、同じ見出し行に対応させながら計算するときに便利です。
B$2の読み方は、B列の2行目ではありません。
正確には、列は移動可能で、2行目だけを固定した参照です。
たとえばC5セルにB$2という参照を含む数式を入力し、その数式をD5へコピーした場合、B$2はC$2へ変化します。
横方向へ移動した分だけ列記号がずれるため、各列の2行目にある基準値を自然に参照できます。
数式バーへの入力手順
行を固定したい数式を入力するセルを選択し、数式バーまたはセルに半角でイコールを入力します。
続けて参照したいセル番地を指定し、固定したい行番号の直前にドル記号を加えます。
=B5*B$2
この数式では、B5は通常どおりコピー先に合わせて動き、B$2は列だけが動く基準セルとして働きます。
ドル記号は列記号の前ではなく、固定したい行番号の直前に付けることが重要です。
入力後にEnterキーを押すと計算結果が表示されます。
数式そのものを確認したいときは、結果が表示されたセルをクリックして数式バーを見ると、B$2が正しく残っているか確認できます。
コピー前後の参照先の変化
数式のコピーでは、コピーする向きによって変化する箇所が異なります。
たとえばC5セルに=B5*C$2を入力し、D5へコピーすると=C5*D$2となります。
左側のB5は相対参照なのでC5へ変わり、C$2も列が相対参照なのでD$2へ変わります。
一方、5行目から6行目へコピーした場合は=B6*C$2となり、C$2の2行目は固定されたままです。
横コピー =B5*C$2 → =C5*D$2
縦コピー =B5*C$2 → =B6*C$2
この変化を最初に紙へ書き出しておくと、複雑な集計表でも参照ミスを減らせます。
【操作のポイント】数式をコピーする前に、右へコピーした場合と下へコピーした場合の参照先を一度確認しましょう。
F4キーによる混合参照の切り替え
| 参照形式 | 横コピー | 縦コピー |
|---|---|---|
| B2 | 列が変化 | 行が変化 |
| B$2 | 列が変化 | 行を固定 |
| $B2 | 列を固定 | 行が変化 |
続いては、F4キーを使ってB$参照へ切り替える操作を確認していきます。
手入力でもドル記号は付けられますが、数式作成中はF4キーで参照形式を循環させる方法が速く、入力ミスも防ぎやすくなります。
F4キーでBを表示する順番
数式を編集している状態で、参照セルの番地部分にカーソルを置きます。
たとえばB2を選択した状態でF4キーを押すと、参照形式が順番に切り替わります。
B2 → $B$2 → B$2 → $B2 → B2
環境によっては、ノートパソコンでFnキーを押しながらF4キーを使う必要があります。
B$2を表示したい場合は、B2から数えてF4キーを3回押すと到達します。
ただし、すでに$B$2になっているときは1回押すとB$2になります。
現在の参照形式によって必要な回数は変わるため、数式バーで表示を見ながら操作するのが確実です。
セル参照をクリックして固定する操作
数式の入力中に、参照先セルをマウスでクリックして指定することもできます。
たとえば売上金額を計算する場合、まずイコールを入力し、単価セルをクリックし、アスタリスクを入力し、数量セルをクリックします。
次に、税率がある2行目のセルをクリックしたあと、数式バー内の該当番地を選んでF4キーを押します。
この手順なら、セル番地を直接打ち込む必要がなく、表が横に長いときにも便利です。
参照先をクリックする途中で別のセルを押してしまった場合は、Escキーで入力を取り消すか、数式バー内のセル番地だけを修正します。
操作に慣れるまでは、確定前に数式バーを見て、行番号の前だけにドル記号があるか確認しましょう。
ショートカットが使えない場合の入力方法
F4キーが画面の明るさや音量調整に割り当てられている場合、Fnキーとの組み合わせでも切り替わらないことがあります。
その場合は、数式バーに表示されたB2の行番号の前へカーソルを置き、半角のドル記号を直接入力します。
日本語入力がオンのままだと全角記号になることがあるため、入力モードは半角英数にしておくと安心です。
正しい入力例 =B5*B$2
誤りやすい入力例 =B5*$B2
$B2は列Bを固定する書き方なので、行だけを固定したい目的とは異なります。
ドル記号の位置だけでコピー結果が大きく変わるため、似た形でも意味を分けて覚えることが大切です。
【操作のポイント】F4キーは参照形式を順番に巡回します。目的のB$2が出た時点でキー操作を止めましょう。
横方向コピーに対応する料金表の計算
| 商品 | 4月 | 5月 | 6月 |
|---|---|---|---|
| 単価 | 100 | 120 | 110 |
| 数量 | 5 | 7 | 6 |
| 売上 | 500 | 840 | 660 |
続いては、横方向に数式をコピーする料金表でB$参照を活用する方法を確認していきます。
月別や部門別に横へ項目が続く表では、同じ行の基準値を追いかけるために行固定が役立ちます。
月別の係数を2行目から取得する数式
1行目に見出しがあり、2行目に月ごとの係数、5行目以降に計算対象のデータがある表を考えます。
C5セルに数量があり、C2セルに4月の係数があるなら、計算セルには=C5*C$2のような数式を入力します。
下方向へコピーしても、係数の参照は常に2行目に残ります。
右方向へコピーしたときは、C$2がD$2、E$2へ変わるため、各月に対応した係数を取得できます。
=C5*C$2
赤枠で示したC$2は、右へコピーするとD$2へ変わります。
このため、月ごとに異なる係数を自動で反映でき、列ごとに数式を入力し直す必要がありません。
オートフィルで右へ数式を広げる操作
最初の数式を入力したセルを選択すると、右下に小さな四角形のフィルハンドルが表示されます。
そのフィルハンドルを右へドラッグすると、数式が隣の列へ連続してコピーされます。
コピー後のセルをクリックし、数式バーで参照が想定どおりに変わったか確認しましょう。
たとえばC$2を使った数式は、D列ではD$2、E列ではE$2となっていれば正常です。
行番号の2がすべてのコピー先で維持されていることが、B$参照を使う計算の確認ポイントです。
売上表での固定行と可変行の組み合わせ
実務では、単価、数量、割引率、税率など複数の参照先を組み合わせることがあります。
たとえば商品ごとの数量が5行目以降にあり、月ごとの割引率が2行目にある場合、数量セルは相対参照、割引率セルは行固定にすると整理しやすくなります。
=C5*(1-C$2)
この式を下へコピーするとC5だけがC6、C7へ変わります。
右へコピーするとC5はD5へ、C$2はD$2へ変わり、それぞれ同じ月の数量と割引率を参照します。
数式の構造をそろえると、表の途中に列を追加した場合にも修正箇所を少なくできます。
【操作のポイント】横方向に異なる条件が並ぶ表では、条件が置かれた行番号だけをB$参照で固定するとコピー作業が安定します。
相対参照と絶対参照の使い分け
| 書き方 | 固定される部分 | 主な用途 |
|---|---|---|
| B2 | なし | 通常の一覧計算 |
| B$2 | 行 | 横並びの条件 |
| $B2 | 列 | 縦並びの条件 |
| $B$2 | 列と行 | 固定の基準値 |
続いては、B$参照とほかのセル参照を使い分ける考え方を確認していきます。
参照形式は見た目が似ていますが、コピーする方向と基準値の置き場所を意識すると判断しやすくなります。
B2とBの違い
B2は列も行も相対参照です。
そのため、右へコピーすればC2へ、下へコピーすればB3へ変化します。
一覧表の同じ位置関係にあるセル同士を計算するときには、B2のような通常の相対参照が向いています。
一方、B$2は下方向へコピーしても2行目を維持したいときの書き方です。
横に並んだ項目の見出し直下や係数行を参照するならB$参照と覚えると、選択が早くなります。
$B2との違い
$B2はB列を固定し、行番号だけを動かす混合参照です。
たとえば、各行に並ぶ数値へ固定のB列にある単価を掛ける場合に使えます。
右へコピーしてもB列を見続けるため、列ごとに計算結果を展開したい表で効果を発揮します。
行だけを固定したいB$2とは、ドル記号の位置が逆です。
B$2は横方向の条件行を追いかける参照です。
$B2は縦方向の基準列を追いかける参照です。
コピー先の表を見て、基準値が横一列に置かれているのか、縦一列に置かれているのかを先に判断しましょう。
$Bを使うべきケース
$B$2は列と行の両方を固定する絶対参照です。
消費税率、為替レート、基準単価など、表のどこへ数式をコピーしても同じ一つのセルを参照したい場合に使います。
たとえば全商品の金額に同じ税率を掛けるなら、=C5*$B$2のように指定できます。
B$2と$B$2を混同すると、横コピーした時点で参照先がずれたり、逆に変わるべき列が固定されたりします。
コピー後にどのセルを参照してほしいかを基準に、相対参照、混合参照、絶対参照を選び分けることが重要です。
【操作のポイント】固定したいのが横方向の見出し行ならB$2、表全体で同じ基準セルなら$B$2を選びましょう。
B$参照で起こりやすい数式エラー
| 症状 | 確認する箇所 |
|---|---|
| コピー後に値がずれる | ドル記号の位置 |
| 同じ値ばかりになる | 列まで固定していないか |
| 計算結果が0になる | 参照元の表示形式と値 |
続いては、B$参照を使うときに起こりやすい数式エラーと確認方法を解説していきます。
数式がエラーでなくても、参照先が意図と違えば集計結果は誤ったものになります。
ドル記号の位置を取り違えるケース
最も多いのは、行を固定したいのに$B2と入力してしまうケースです。
$B2では列Bが固定されるため、右へコピーしても参照列がBのままになります。
月別の列に応じて参照先を変えたい計算では、すべて同じ列を参照してしまうため注意が必要です。
数式バーで、ドル記号が行番号の直前にあるかを確認してください。
行固定ならB$2、列固定なら$B2という位置関係を意識すると誤りを見つけやすくなります。
数式をコピーする方向を誤るケース
B$参照は、横へコピーすることで列だけを変える用途に特に向いています。
下へコピーしても行番号は変化しないため、縦方向に別の条件を追いかけたい表では期待した結果にならないことがあります。
縦方向に条件が並んでいる場合は、$B2のように列を固定する書き方が適しているかもしれません。
コピー前に、データの項目が横並びか縦並びかを確認することが大切です。
横並びの条件はB$2を検討します。
縦並びの条件は$B2を検討します。
表示形式と参照値を見直す方法
数式自体が正しくても、参照元セルが文字列として入力されていると計算結果が期待どおりにならないことがあります。
たとえば10パーセントを10と入力した場合と、10%と入力した場合では、掛け算の結果が異なります。
セルを選択して数式バーを確認し、数値として保存されているか、表示形式がパーセントになっているかを見直しましょう。
また、空白セルを参照していないか、コピー範囲が途中でずれていないかも確認対象です。
数式の文字列だけでなく、参照元セルの内容も確認すると、原因を早く特定できます。
【操作のポイント】計算結果が不自然なときは、コピー先の数式バーを開き、参照先の列と固定された行番号を順に確認しましょう。
まとめ エクセルで行だけを固定するB$参照の書き方
| 目的 | 参照形式 |
|---|---|
| 行だけを固定 | B$2 |
| 列だけを固定 | $B2 |
| 行と列を固定 | $B$2 |
エクセルで行だけを固定したい場合は、行番号の直前にドル記号を付けたB$参照を使います。
たとえばB$2は、下へコピーしても2行目を固定しながら、右へコピーしたときにはB列、C列、D列のように参照列を変えられます。
横方向に条件や係数が並ぶ表では、B$参照が計算式を短く正確に保つ鍵になります。
F4キーを使えば、通常のB2からB$2へ簡単に切り替えられます。
ただし、$B2は列固定、$B$2は行と列の固定なので、ドル記号の位置を必ず確認しましょう。
数式を入力した後は、右へコピーした場合に列だけが変わるか、下へコピーした場合に行番号が固定されるかを数式バーで確認すると安心です。
混合参照を使いこなせるようになると、月別集計、料金表、予算表、売上管理表などで数式を作り直す手間を大幅に減らせます。
