おはよう、後輩A君。
エクセルで、こんなことはないかな。
1行目の数式はうまくいった。それを下の行にコピーした瞬間、答えが全部0になった――。
1行目は合っているんだよ。コピーしただけなんだ。なのに2行目から下が全滅なんだよね。
今日はこれを直す話をするよ。覚えるのは記号ひとつだけだからね。
1. まず、何が起きたか見てみようか
お菓子の値段表で説明するね。税率だけ別のセルに置いてある表だよ。
A列 品名 B列 値段
2行目 プリン 250
3行目 シュー 300
4行目 ケーキ 480E1のセル → 税率の「1.1」が入っている
C2に「税込みの値段」を出したいから、こう書くよね。
C2 =B2*E1 → 275 ✓ うまくいった
よし、と思ってC2を下にコピーするんだ。そうすると――
C3 → 0
C4 → 0
全滅だよね。1行目は合っていたのに、なんでだろう。
2. 数式の中を見ると、犯人が分かるんだよ
0になったC3のセルを、ダブルクリックして開いてみようか。中身はこうなっているんだ。
C3 =B3*E2
……あれ？ と思わないかな。
B2がB3になるのは、いいんだよ。3行目にはシュークリームの値段を使ってほしいからね。
でもE1までE2になっているんだ。E2には何も入っていないよね。空のセルを掛けたから、答えが0になったんだよ。
つまりこういうことなんだ。
B2 → B3 ズレてほしかった（各行の値段を使いたい）
E1 → E2 ズレてほしくなかった（税率はずっとE1）
ズレてほしいものと、ズレてほしくないものが、1つの数式に混ざっていたんだよね。
そしてエクセルには、どっちがどっちか区別がつかないんだ。だから全部いっしょにズラしたんだよ。
3. これ、故障じゃなくて親切なんだよ
ここでひとつ、大事なことを言っておくね。
コピーでズレるのは、エクセルの親切なんだ。
考えてみてほしいんだよ。もしズレなかったら、C3もC4も「=B2*E1」のままだよね。全部プリンの値段になってしまうんだ。それこそ困るよね。
エクセルは数式を「B2」という住所ではなくて、「左隣のセル」という位置関係で覚えているんだ。だからコピーすると、行き先でも「左隣」を見てくれるんだよ。
普段はこの親切に助けられているんだよね。合計の数式を100行コピーできるのは、このおかげなんだ。
ただ――今回は親切が効きすぎたんだよ。税率のE1まで「付いてきて」しまったんだね。
4. 直し方：「$」の画鋲でとめるんだよ
やることは1つだけだよ。
動いてほしくないセルに、$を付ける。
❌ =B2*E1
⭕ =B2*$E$1
$は「ここから動かないでね」という画鋲だと思ってほしいんだ。E1を画鋲でとめておくイメージだね。
こうしてからコピーすると、こうなるよ。
C3 =B3*$E$1 → 330 ✓
C4 =B4*$E$1 → 528 ✓
B2はズレて、E1はとまっている。ズレてほしいものだけがズレたんだよね。
――「Eと1の両方に$が付くの？」と思うかもしれないね。細かい意味はあるんだけれど、最初は「両方に付ける」とだけ覚えれば9割の場面で足りるよ。使い分けは、必要になってから知ればいいんだ。
5. $は手で打たなくていいんだよ
$を2つ、毎回手で打つのは面倒だよね。1発で付けるキーがあるんだ。
数式の中の「E1」のところにカーソルを置いて――
Windows → F4キー
Mac → command+T
押すたびに「$E$1 → E$1 → $E1 → E1」と切り替わるよ。1回押して$が2つ付いた形にするのが基本だね。
⚠️ ノートパソコンだと、F4を押すにはFnキーを一緒に押す必要がある機種もあるよ。効かないときは、手で$を打っても同じことだからね。
6. ズレたかどうか、目で確かめる方法
もうひとつ、便利な技を書いておくね。
数式のセルをダブルクリックすると、参照している場所に色付きの枠が出るんだ。
C3をダブルクリックすれば、「いまB3とE2を見ています」と色の枠で教えてくれるんだよ。枠が思っていた場所と違うところにあれば、ズレているということだね。
数式の文字を読むより、ずっと早いんだ。コピーした後におかしいと思ったら、まずダブルクリック――これを癖にしておくといいよ。
7. ついでに：数字を数式に直接書かない方がいいよ
「そもそも税率を =B2*1.1 と書けば、ズレる心配はないのでは？」と思うかもしれないね。
そのとおりなんだ。でも私はセルに置く方をおすすめするよ。
理由は、税率が変わったときなんだ。
数式に直接書いた場合 → 数式を全部直すことになる
セルに置いた場合 → E1を1か所直せば、全部変わる
数字をセルに置いて、$でとめる。この形にしておくと、変更に強い表になるんだよね。
8. このシリーズは、今日で7本目だよ
エクセルの困りごとを並べておくね。
① 指数表示になる
② 先頭の0が消える
③ 「1-2」が日付になる
④ 並べ替えたら行がバラバラ
⑤ 数字なのに計算されない
⑥ エラー表示の意味
⑦ コピーしたらズレる ← 今日
①〜⑥は、エクセルが勝手に判断して困る話が多かったよね。数値だと思われたり、日付にされたり。
今日の⑦は少し違うんだ。こちらから印を付けて教えたんだよね。「ここは動かないで」と$で伝えたんだ。
――エクセルは勝手に判断する。でも伝え方を知っていれば、止められるんだよ。相手の親切の仕組みが分かると、付き合い方も分かるんだよね。
結論：ズレてほしくないセルに、画鋲を刺そうか
今日の話をまとめるとこうだよ。
① 数式のコピーでズレるのは故障ではなく親切
② 困るのはズレてほしくないセルまで付いてくるとき
③ そのセルに$を付ける（=B2*$E$1）
④ $は「動かないでね」の画鋲。最初は両方に付ければ9割足りる
⑤ 付けるのはF4キー（Macはcommand+T）
⑥ おかしいと思ったらダブルクリックで色の枠を見る
⑦ 数字は数式に直接書かず、セルに置いて$でとめる
$の説明って、難しい言葉で書かれていることが多いんだよね。私も最初に調べたとき、専門用語が並んでいて閉じたことがあるよ。
でも実際にやることは、画鋲を1本刺すだけなんだ。
後輩A君も、コピーして変な数字が出たら――ダブルクリックして、枠がどこにあるか見てみようか。ズレていたら、そこに$だよ。それだけで直るんだ。
よくある質問
Q1. $E と $E1 と E の違いは何ですか？
$が付いている側だけが固定されるんだ。$E1は列だけ、E$1は行だけとまるんだよ。使い分けると便利な場面もあるけれど、必要になってから覚えれば十分だと思うよ。まずは両方に付ける$E$1だけでいいんだ。
Q2. F4を押しても$が付きません
ノートパソコンではFnキーを押しながらF4の機種があるよ。あとは数式の編集中に押す必要があるんだ。セルを選んだだけでは効かないんだよね。ダブルクリックか数式バーで、E1の文字のところにカーソルを置いてから押してみようか。それでもダメなら、手で$を打って大丈夫だよ。
Q3. どのセルに$を付けるべきか、迷います
コピーする前に、数式の中のセルを1つずつ見て「これはコピー先でもここを見ていてほしい？」と聞いてみるといいよ。答えがイエスなら$、ノーならそのまま、だね。今回の例なら、税率は「ずっとE1を見ていてほしい」から$なんだ。
Q4. 全部に$を付けておけば安全では？
それだと逆に困るんだ。ズレてほしいものまでとまってしまうからね。今回の例で言えば、B2に$を付けたら全部プリンの値段になるんだよ。画鋲は、動いてほしくないものにだけ刺すんだ。
【本日のミッション：数式をダブルクリックしてみようか】
- ☐ 手元の表で、数式が入っているセルをダブルクリックしてみようか
- ☐ 色付きの枠がどのセルに付いているか、見てみようか
- ☐ （後輩A君へ）その中に「コピーしたら困る場所」があったら、$の画鋲を刺しておこうか
📚 AI活用シリーズ（おすすめ）
コメント