MENU

Excelの絶対参照の使い方|$で固定するセルとF4の切り替え

方眼ノートの一か所に小さなクリップを留めた木製の机

式を下へコピーしたら、二行目から答えがおかしい。そこで一行ずつ直して回る人がいる。働き者なのは分かるが、同じ失敗を丁寧に増産する必要はない。

共通の単価や率を参照するなら、そのセルを絶対参照にする。全部を固定すればよいのではない。動かす所と、動かしてはいけない所を分ける。

目次

相対参照と絶対参照の違い

A1はコピー先に応じて動く参照、$A$1は行も列も固定する参照である。

たとえばC2の =A2*B2 をC3へコピーすると、通常は =A3*B3 になる。これが相対参照。一方、式の $E$1 は別の行や列へコピーしてもE1を指す。

ここで固定するのは数式中の参照先であり、セルの編集を禁止する保護設定ではない。E1の値が変われば、それを参照する計算結果も変わる。

私は固定という言葉を便利に使いすぎる人が気になる。担当を決めるのと、その人が二度と変わってはいけないのは違う。巨人の打順だって、名前を書いたら永久保存というわけにはいかない。

共通の単価を固定する例

各行の数量は動かし、共通の単価だけを$付きで固定する。

架空の表で、A2から下に数量、E1に共通単価を置く。B2に =A2*$E$1 と入力し、B3へコピーすると =A3*$E$1 になる。数量の行は変わり、単価の参照先は変わらない。

一つ下へコピーした段階で式を確かめてから、必要な範囲へ広げる。表の構造が違う場合は、そのまま式を流用しない。

工藤公康は西武時代、投げ終えた後の一塁カバーを、ベースを見なくても入れるほど繰り返したと語っている。どこへ動くかを頭で説明できることと、プレーの中でそこへ行けることを分けていた。

工藤が覚えた後で、一塁の場所をその日の気分で変えるわけではない。私は表を作る管理職にも、自分が決めた参照先を、教わる側が覚える前に変える資格はないと思う。もっと便利な置き方を思いついたという報告は、その後に聞く。

式も同じ場所へ行かせたいなら、こちらがそう指定する。何も指定しないで「そこは分かるだろう」と叱るのは、指導者の手抜きである。私は細かく言う。その分、聞く方にも細かく見てもらう。

広告

F4で参照形式を切り替える

数式を編集中に対象のセル参照を選び、WindowsではF4で$の組み合わせを切り替える。

数式バーなどで対象のA1を選び、F4を押すと $A$1、A$1、$A1、A1 の組み合わせが切り替わる。通常のセル選択だけで押すと別の操作になる場合がある。キーボードの設定によってFnキーも必要になる。うまくいかなければ$を直接入力してよい。

Mac版では数式編集中のCommand+Tで切り替える案内がある。環境によって異なるため、版に合うショートカットを確認する。

近道を覚えるのは結構だが、何を切り替えたか分からずに押すのは感心しない。F4を四回押して元に戻り、操作だけは完璧だという顔をされても、こちらは褒めようがない。

行か列だけ固定したい場合

$A1は列だけ、A$1は行だけ固定し、コピーする向きに合わせて使う。

  • $A$1:列Aと行1を固定する。
  • $A1:列Aは固定し、行は動く。
  • A$1:行1は固定し、列は動く。
  • A1:列も行も相対的に動く。

下方向だけでなく、右方向にも一つコピーして確認する。参照先を色や式で確かめ、コピー前の一セルだけで正解と判断しない。

私は全部の参照を固定するつもりはない。ただし、表を変える管理職の説明は、教えた先輩の前へ固定しておきたい。自分の式が動いたから、自分への質問まで終わったと思われては困る。

広告

まとめ

コピーで変える参照と残す参照を分け、固定したい列・行の前に$を付ける。

工藤は一塁へ入る場所を繰り返して覚えた。私は、教える側の上司が、まだ教わる者の手が動かないうちから、次の仕組みを発表するのが気に入らない。便利な改善という札を付ければ、教え直しまで若い者の仕事になる。

数式で固定すべき参照先は、表に合わせて決めればよい。ただ、その表を使う管理職には、昔の担当者が教えた位置で一度説明してもらう。新しい配置の方が早いという話は、元の配置を覚えた後で足りる。

変更する権限があるから、覚える順番も飛ばせるわけではない。一塁カバーを言葉で説明しただけで工藤の練習が済んだことにはならなかった。上司が一度正しい答えを出しても、教えた側がもう一度というなら、もう一度でよい。

表は次の行へすぐ渡せる。管理職の合格まで同じ速さで渡す必要はない。私は、参照先を固定する前に、自分が教わる立場だけは移動させないでもらいたい。

あわせて読みたい記事

この記事を書いた人

世の中には、まだ教えることがある。話は変わるが、基本は同じだ。

目次