相対参照について
ここから3レッスン続けて、「参照」 という、Excelの中で最も重要な概念を学びます。
参照には3種類あります。
- 相対参照 (今回のレッスン)
- 絶対参照 (レッスン036)
- 複合参照 (レッスン037)
Excelで関数を使っていくうえで、ここは必ず通る道です。少し抽象的な話ですが、丁寧に追っていきましょう。
相対参照とは
普段、何気なく数式を書くときに使っているのが 相対参照 です。実は、これまでのレッスンで組んできた数式は、全部この相対参照でした。
少し回りくどい言い方ですが、セル参照でセル番地を取得した際、デフォルトで相対参照の状態になっている、というのが正しい説明です。
たとえば、A1セルに「=B1+C1」と入力したとします。
:::excel
A1==B1+C1 -> 30 *
B1=10
C1=20
:::
これを A2 にコピーすると、自動で「=B2+C2」に変わります。A3 にコピーすると「=B3+C3」。
:::excel
A1==B1+C1 -> 30
B1=10
C1=20
A2==B2+C2 -> 70 *
B2=30
C2=40
A3==B3+C3 -> 110 *
B3=50
C3=60
:::
数式をコピーしただけなのに、A2は「B2+C2」、A3は「B3+C3」と、参照先が1行ずつ下にズレています。手で書き換えてはいません。
このように、コピー先のセルに合わせて、参照されるセルが「相対的に」ズレていく のが、相対参照です。
なぜ「相対」なのか
Excelの内部では、「=B1+C1」という数式を、こんなふうに解釈しています。
「自分の左隣のセルと、その左隣のセルを足してね」
「B1」とか「C1」という具体的な番地ではなく、「自分から見て、左隣」「自分から見て、2つ左」という 位置関係 で記憶しているのです。
だから、その数式を A2 にコピーすると、A2から見た「自分の左隣(B2)」と「2つ左(C2)」を足す、と解釈し直されます。番地そのものではなく、「自分からの距離と方向」で覚えている、とイメージしてください。
これが「相対」参照という名前の由来です。
ちょっとした実務シーン
商品リストで、各行の 金額 = 単価 × 数量 を計算したい、というケース。
- B列:単価
- C列:数量
- D列:金額 (ここを計算する)
D2 に「*=B2C2**」と入力すると、商品Aの金額が計算されます。
:::excel
B1=単価
C1=数量
D1=金額
B2=100
C2=3
D2==B2C2 -> 300
B3=200
C3=5
D3=
B4=150
C4=4
D4=
:::
次に、D2 のフィルハンドル(セル右下の小さな四角)を 下にダブルクリック(または下にドラッグ)すると、こうなります。
:::excel
B1=単価
C1=数量
D1=金額
B2=100
C2=3
D2==B2*C2 -> 300
B3=200
C3=5
D3==B3C3 -> 1000
B4=150
C4=4
D4==B4C4 -> 600
:::
D3は「=B3C3」、D4は「=B4C4」。それぞれの行に合わせて、参照が自動で調整 されました。1つ数式を書けば、コピー操作で何百行でも一瞬で計算 できる。これが相対参照です。
相対参照の本質
「自分から見て、どこを参照しているか」を、Excelは記憶しています。
数式を別のセルにコピーすると、その「自分から見て」の起点が、コピー先に移動します。だから、参照先も自動でズレるのです。
難しく考えず、「数式は、引っ越し先でも同じ景色を見る」とイメージするとつかみやすいです。
縦にコピー / 横にコピー
相対参照は、コピーする方向によって、ズレ方が変わります。
縦にコピー(下にコピー)
行番号が変わります。「=B1+C1」を縦にコピーすると、「=B2+C2」「=B3+C3」と、行だけが変化します。
横にコピー(右にコピー)
列番号が変わります。「=A1+A2」を横にコピーすると、「=B1+B2」「=C1+C2」と、列だけが変化します。
:::excel
A1=10
A2=20
A3==A1+A2 -> 30 *
B1=100
B2=200
B3==B1+B2 -> 300 *
C1=1000
C2=2000
C3==C1+C2 -> 3000 *
:::
A3の「=A1+A2」を右にコピーすると、B3は「=B1+B2」、C3は「=C1+C2」。今度は列だけがズレています。
斜めにコピー
斜めにオートフィルはできませんが、コピー＆ペーストの操作で行も列も、両方 相対的に 変わります。
なぜ「相対参照」を学ぶ必要があるのか
実は、これだけで困らないケースも多いです。「=B2＊C2 を下にコピーして、金額を計算する」だけなら、相対参照だけで完結します。
ただ、もう少し複雑な計算をしようとすると、「ある特定のセルだけ、ズレてほしくない」 というシーンが出てきます。
たとえば、B1セルに「消費税率 10%(0.1)」と書いてあり、C列に各商品の金額、D列に「金額 × 消費税率」を計算したいとします。D2 に「=C2＊B1」と入力するとうまくいきます。
:::excel
B1=0.1
C1=金額
D1=税額
C2=300
D2==C2B1 -> 30
C3=1000
D3=
:::
ところが、これを D3 にコピーすると、こうなってしまいます。
:::excel
B1=0.1
C1=金額
D1=税額
C2=300
D2==C2*B1 -> 30
C3=1000
D3==C3B2 -> 0
:::
D3は「=C3＊B2」。B1(消費税率)を参照したかったのに、相対参照のルール通り、B1も1つ下のB2にズレてしまいました。B2は空っぽなので、税額は0。これは困ります。
「B1だけはズレないでほしい」── その答えが、次のレッスンで学ぶ 絶対参照 です。Excel初心者が最初につまずくポイントでもあります。相対参照の「便利さ」と「困りどころ」、両方を押さえておくと、つまずきを回避できます。
では、このまま次のレッスン、絶対参照に進んでください。
まとめ
- 相対参照は、コピー先に合わせて参照がズレる 動き
- 「自分から見て、どこを参照するか」という位置関係で記憶している
- 縦にコピーすれば行番号、横にコピーすれば列番号がズレる
- 1つの数式を書いて、何百行・何百列も一気に計算できる
- ただし、「ズレてほしくないセル」もある → 次のレッスンの絶対参照へ
ショートカット
※このレッスンに対応するショートカットはありません。