1行目では正しく計算できていたのに、下へコピーした途端に0がずらりと並ぶ。あるいは#N/Aや#DIV/0!が途中から出始める。数式をのぞいても同じ形に見えるので、どこが悪いのか見当がつかない――。
この症状のほとんどは、コピーしたときに「参照先までいっしょに動いてしまった」ことが原因です。直し方そのものは簡単で、動いてほしくない場所に「$」を付けるだけ。ただし、付ける位置を間違えると今度は別の形で答えがずれます。しかもエラーが出ないずれ方もあるので、気づかないまま報告資料に使ってしまうこともあります。
ここでは、参照がずれる仕組みを先に押さえたうえで、症状ごとに$を付ける位置の決め方、F4キーでの入れ方、コピー後に正しく固定できているかを確かめる手順まで、順番に見ていきます。
まずは症状を確かめる：コピーした数式を2か所だけ開く
説明のために、次のような請求明細の表を用意しました（数字は説明用に作ったものです）。
- A列：品名（20行）
- B列：税抜金額（2行目は24,300円、20行の合計は486,000円）
- C列：消費税額を計算する列
- E1セル：税率「10%」を1つだけ入力
C2に =B2*E1 と入れると、結果は2,430円。ここまでは正しく計算できています。この式をC21までコピーすると、C3から下はすべて0になりました。税抜金額の合計が486,000円なので、税額の合計は48,600円になるはずですが、C列を合計すると2,430円しかありません。
原因を確かめるには、コピーした先のセルをダブルクリック（またはF2キー）して数式を開きます。C3を開くと =B3*E2、C21を開くと =B21*E20 になっていました。E2からE20は空欄なので、掛け算の答えは0です。もしE列のどこかに文字が入っていれば、0ではなく#VALUE!が出ていたはずです。
確認するのは、コピー元のすぐ下の行と、一番下の行の2か所で十分です。数式を開くと参照しているセルに色付きの枠が表示されるので、その枠が狙ったセルに乗っているかを見ます。表全体の式を一度に見たいときは、「数式」タブの「数式の表示」を押すと、すべてのセルが計算結果ではなく式で表示されます。確認が終わったら、もう一度押せば元に戻ります。
参照がずれる仕組み：Excelは「場所」ではなく「距離」で覚えている
なぜE1がE2に変わってしまうのか。それは、Excelが数式の参照先を「E1というセル」としてではなく、「この数式のセルから見て、どれだけ離れた位置か」として覚えているからです。
C2に入れた =B2*E1 は、Excelの内部では次のように記録されています。
- B2：同じ行の、1列左のセル
- E1：1行上の、2列右のセル
この式を1行下のC3にコピーすると、Excelは「同じ行の1列左」と「1行上の2列右」という距離をそのまま保ちます。その結果、B2はB3に、E1はE2に変わります。B列のほうは行ごとに変わってくれないと困るので、この動きで正解です。困るのは、全行で同じセルを見てほしいE1まで一緒に動いてしまう点です。
この「距離で覚える」参照を相対参照といいます。そして、距離ではなく場所で固定したいときに付ける目印が「$」です。$は、すぐ後ろにある列記号や行番号を固定します。
| 書き方 | 呼び方 | 下へコピー | 右へコピー |
|---|---|---|---|
| E1 | 相対参照 | E2、E3…と動く | F1、G1…と動く |
| $E$1 | 絶対参照 | 動かない | 動かない |
| $E1 | 複合参照（列を固定） | E2、E3…と動く | 動かない |
| E$1 | 複合参照（行を固定） | 動かない | F1、G1…と動く |
覚え方は「$の後ろにあるものが止まる」です。$Eなら列のE、$1なら行の1が止まります。
F4キーで$を入れる：押す回数で4種類を切り替える
$は手で打ち込んでもかまいませんが、F4キーを使うほうが速く、打ち間違いもありません。
- 数式を直したいセルを選び、F2キーを押すか数式バーをクリックして編集状態にする
- 固定したい参照（ここではE1）の文字の中か、すぐ後ろにカーソルを置く
- F4キーを押す
F4キーを押すたびに、$E$1 → E$1 → $E1 → E1 の順に切り替わります。行も列も固定したいなら1回、行だけなら2回、列だけなら3回です。
範囲を固定したいときは、数式バーで F2:H51 の部分をドラッグして選んでからF4キーを押すと、$F$2:$H$51 と両端にまとめて$が付きます。
ノートパソコンでF4キーを押しても切り替わらない場合は、Fnキーを押しながらF4キーを押してください。MacのExcelでは、Commandキー＋Tキーで同じ切り替えができます。
ひとつ注意があります。F4キーが$の切り替えとして働くのは、数式を編集している最中だけです。セルを選んだだけの状態でF4キーを押すと、「直前の操作の繰り返し」になり、さっき付けた塗りつぶしや削除がもう一度実行されてしまいます。押す前に、カーソルが数式の中で点滅しているかを確かめましょう。
症状別：$を付ける場所の決め方
どこに$を付けるかは、「コピーしても変わってほしくない参照はどれか」で決まります。よく出る5つの場面で見ていきます。
税率・単価など、1つのセルを全行で使うとき
最初の請求明細のように、全行で同じ1つのセルを見たいときは、そのセルに行も列も固定する$を付けます。
=B2*$E$1
これをC21までコピーすると、C3は =B3*$E$1、C21は =B21*$E$1 となり、税額の合計は48,600円になりました。B2には$を付けていないので、行ごとに税抜金額のほうはきちんと変わっています。
VLOOKUPやSUMIFの「範囲」を使うとき
もうひとつの作例として、商品マスタ50件（F2:H51）から単価を引く、300行の受注明細を考えます。最初の行に =VLOOKUP(A2,F2:H51,3,FALSE) と入れて下までコピーすると、2行目では範囲が F3:H52、3行目では F4:H53 と、1行ずつ下へずれていきます。
範囲が下へずれると、マスタの上のほうに載っている商品から順に検索範囲の外へ押し出されます。そのため、下の行に行くほど#N/Aが増え、50行を過ぎたあたりからは、ほぼ全部が#N/Aになります。上のほうの行は正しく表示されているので、「データの入力ミスかな」と勘違いしやすいずれ方です。
直し方は、範囲の両端に$を付けることです。
=VLOOKUP(A2,$F$2:$H$51,3,FALSE)
ここで、検索値のA2には$を付けません。行ごとに違う商品コードを探してほしいからです。列番号や検索方法の決め方を見直したい場合は、VLOOKUPの4つの引数の基本もあわせて確認してください。$を付けても一部に#N/Aが残るなら、範囲以外に原因があります。#N/Aの原因を順に探す方法で、見えないスペースや数値と文字列の違いを確かめましょう。
SUMIF・COUNTIF・SUMIFSでも考え方は同じです。支店別の集計表を下へコピーするなら、条件範囲と合計範囲を固定し、条件のセルだけ動かします。
=SUMIF($A$2:$A$301,E2,$C$2:$C$301)
構成比の「分母」を使うとき
5支店の売上から構成比を出す表で、B7に合計の12,500,000円があるとします（数字は作例です）。C2に =B2/B7 と入れて下へコピーすると、C3は =B3/B8 になります。B8は空欄なので、0で割ったことになり#DIV/0!が出ます。
分母の合計セルだけを固定します。
=B2/$B$7
これで、東京支店の売上が4,000,000円なら32.0%と表示され、5支店の構成比を足すとちょうど100%になります。
縦にも横にもコピーする早見表をつくるとき
単価と数量の早見表のように、縦と横の両方へコピーする式では、片方だけに$を付ける複合参照を使います。
- A2:A6：単価（100円、150円、200円、250円、300円）
- B1:F1：数量（1、5、10、20、50）
B2に =$A2*B$1 と入れて、B2:F6全体にコピーします。
- 右へコピーしても、単価は常にA列を見てほしい → 列を固定して
$A2 - 下へコピーしても、数量は常に1行目を見てほしい → 行を固定して
B$1
F6は =$A6*F$1 となり、300×50で15,000円です。ここで両方を $A$2*$B$1 にすると、表全体が100円で埋まります。逆にどちらにも$を付けないと、B3が =A3*B2 になり、上のセルの答えを掛ける意味のない数字が並びます。
迷ったら「縦にコピーしたときに動いては困るのは列か行か」「横にコピーしたときはどうか」を1つずつ口に出してみると、付ける位置が決まります。
上から順に足していく累計をつくるとき
日ごとの売上の累計のように、始まりは固定して終わりだけ伸ばしたいときは、範囲の片側だけに$を付けます。
=SUM($B$2:B2)
下へコピーすると =SUM($B$2:B3)、=SUM($B$2:B4) と、始点のB2はそのままで終点だけが1行ずつ伸びていきます。
付けすぎても答えはずれる
$は付ければ付けるほど安全、というわけではありません。全部の参照を固定してしまうと、行ごとに変わってほしい参照まで止まってしまいます。
たとえば先ほどの受注明細で =VLOOKUP($A$2,$F$2:$H$51,3,FALSE) としてしまうと、300行すべてが「1行目の商品の単価」になります。厄介なのは、この場合エラーが一つも出ないことです。数字はきちんと並んでいるので、見た目では気づけません。
コピーした結果の上から3行が全部同じ値になっていたら、付けすぎを疑ってください。もともと同じ商品が続く表でない限り、同じ値が続くのは不自然です。
反対に、縦にしかコピーしない式なら、$E$1 でも E$1 でも結果は同じです。どちらでも動きますが、あとで表を横に広げたときにずれる心配がない $E$1 にしておくほうが無難です。1つのセルを固定するときは、迷ったら行も列も固定する、と覚えておくとよいでしょう。
コピーしたあとに固定漏れを見つける3つの確認
$を付けたつもりでも、範囲の片側だけ付け忘れる、別の参照に付けてしまう、といったミスは起こります。コピーしたあとは、次の3つを確認する習慣をつけると、ほとんどの固定漏れは見つかります。
- 2行目を開く：コピー元のすぐ下のセルをF2キーで開き、色枠が狙ったセルに乗っているかを見る
- 最終行を開く：一番下のセルも開く。範囲のずれは下に行くほど大きくなるので、ここで見つかりやすい
- 別の計算で検算する：請求明細なら、空いているセルに
=SUM(B2:B21)*$E$1と入れ、税額列の合計48,600円と一致するかを確かめる
検算の式は、1行ずつ計算した結果を合計したものと、合計してからまとめて計算したものを比べるのがコツです。計算の順番を変えても同じ答えになるはずのところで差が出れば、どこかの行の参照がずれています。
数式が参照しているセルを矢印で見たいときは、「数式」タブの「参照元のトレース」も便利です。矢印がとんでもない方向へ伸びていれば、そのセルの式がずれています。確認が終わったら「トレース矢印の削除」で消せます。
$を書かずに済ませる方法：テーブルと名前
毎回$の位置を考えるのが面倒なら、そもそもずれない書き方にしてしまう方法もあります。
ひとつはテーブルです。表の中を選んでCtrl＋Tキーでテーブルにすると、数式は =[@税抜金額]*0.1 のように列の名前で書かれ、列全体に自動で入ります。セルの番地ではなく列名で指定しているので、行がずれる心配がありません。行を追加しても数式が自動で広がるため、SUMIFSの範囲が古いまま残る失敗のような範囲の取りこぼしも防げます。
もうひとつは、セルに名前を付ける方法です。E1を選び、数式バーの左にある名前ボックスに「税率」と入力してEnterキーを押します。すると数式を =B2*税率 と書けるようになります。名前で指定したセルは、$を付けたのと同じように、どこへコピーしても動きません。
名前を付けておくと、あとから表を開いた人にも「このセルは税率」と一目で伝わります。ほかの人に渡す表なら、入力・計算・集計を分けて引き継げる形にする方法とあわせて整えておくと、修正のたびに式が壊れることも減ります。
$を付けたのに思った動きにならないとき
最後に、$を正しく付けていても戸惑いやすい動きを整理しておきます。
- 行や列を挿入すると、$付きの参照も動く：E1の上に行を挿入すると、
$E$1は$E$2に変わります。これはExcelが税率のセルの引っ越しに合わせて追いかけてくれている動きなので、正しい結果です。$が止めるのは「コピーしたときの動き」です。 - 別のシートを参照するとき：
=B2*設定!$B$1のように、シート名の後ろの番地に$を付けます。シート名には付けません。 - 切り取りと貼り付けでは参照はずれない：Ctrl＋Xで切り取って移動した数式は、元と同じセルを見続けます。ずれるのはコピー（Ctrl＋Cやオートフィル）のときだけです。
- 式をそのまま別のセルに写したいとき：数式バーで式の文字だけを選んでCtrl＋Cでコピーし、Escキーで編集を抜けてから、貼りたいセルの数式バーに貼り付けます。文字として写すので、参照は一切ずれません。
コピーする前に「動かしたくないもの」を1つ決める
参照ずれは、Excelが「距離」で数式を覚えているという仕組みを知っていれば、怖いものではありません。式を入れたら、コピーする前に「この式の中で、どの行・どの列にコピーしても変わってほしくないものはどれか」を1つずつ確かめ、その参照にだけF4キーで$を付けます。コピーしたら2行目と最終行を開き、別の計算で検算する。この流れを一度身につければ、0や#N/Aが並ぶ表に慌てることはなくなります。
長い数式になって、どの参照を固定すべきか自分でも追いきれないときは、式を作業列に分けてしまうのも一つの手です。考え方は1セルに詰め込んだ数式を分ける手順で紹介しています。
関数の組み合わせや、合計が合わないときの診断フローなど、もう一歩踏み込んだ使い方を知りたい方に向けて、noteで応用テンプレートや深掘り記事を公開しています。手元の表で試しながら読みたい方は、あわせてのぞいてみてください。
コメント