数式がずれる!数式のコピーに欠かせない相対参照の仕組み

解説記事内の画像はExcel 2016のものですが、操作方法は下記のバージョンで同じです。
  • Excel 2016, 2013, 2010
Excel 2016・2013・2010用 練習ファイル
(relative-references.xlsx 12.2KB)

相対参照01 上の図のD2番地には、「みかん」の「金額」を求めるための、
= みかんの単価 × みかんの数量
という意味の数式である、
= B2 * C2
が入力されています。

相対参照28 他の果物の「金額」欄にも、同様の数式を入力しなければいけないのですが、それぞれのセルに1つずつ数式を入力していくのはとっても面倒。
入力したい数式は、
= 単価 × 数量
という式であることは「みかん」と同じなので、ならば手軽に、「みかん」の「金額」を求めたD2番地の数式をコピーしたいと思います。

相対参照02 でもその前に、これから入力したい数式は、どんな式になるのか考えてみることにしましょう。
「みかん」の「金額」欄であるD2番地の数式は、= B2 * C2 だったわけですが、
「りんご」の「金額」欄であるD3番地には、= B3 * C3
「梨」の「金額」欄であるD4番地には、= B4 * C4
・・・という数式を入力したいです。
ここでよ〜く上の図を見てみると、入力したい数式は、「みかん」の数式内で使われているセル番地が、2、3、4、5、6という風に、1つずつズレていった数式を入力したいことが分かります。

そういえば、「1月、2月、3月・・・」や、「月、火、水・・・」のように、データを1つずつずらしながら入力する便利な機能があったなぁ。
そう、オートフィルです!
今回の数式も、元となる「みかん」の数式内で使われているセル番地を、1つずつずらしていった数式を入力したいので、理屈は同じ!
数式のコピーもオートフィルでできちゃいます!

相対参照04 元となる数式が入力されているD2番地を選択すると、そのセルは太枠で囲まれるわけですが、その右下にポッチが付いています。
このポッチにマウスポインタを合わせると、

相対参照03 マウスポインタが黒い十字の形になるので、この状態で下に向かってドラッグすれば、オートフィルができます!

相対参照05 オートフィルで、簡単に数式をコピーすることができました!

相対参照06 コピーした数式が本当に正しいのか確認してみましょう!
「りんご」の「金額」欄であるD3番地を選択すると、そのセルの中身が数式バーに映し出されます。
ちゃんと元の数式から1つズレた、= B3 * C3 になっていることが分かります。

相対参照07 その次の「梨」の「金額」欄であるD4番地を選択し、数式バーを見ると、これまた元の数式から更にもう1つズレた、= B4 * C4 の数式が入力されていることが分かります!

このように、コピー先の数式の内容がうまくズレてくれるのは、元となった「= B2 * C2」という数式の裏で、「(数式が入力されている「金額」のセルから見て、)2つ左のセルと、1つ左のセルを掛け合わせてください」という考え方が働いているから。
だからこそ、コピー先の数式も「2つ左のセルと、1つ左のセルを掛け合わせてください」となるよう、自動的に数式内のセル番地が調整された、というわけです。
Excelさんのこの素晴らしい仕組みを、「相対参照」と言います。
「相対参照」っていう名前は覚えなくていいので、元のセルを基準にして、数式内のセル番地がズレてくれるという仕組みはキッチリ覚えてください。

スポンサーリンク

もちろん、横方向にも相対参照は働きます

相対参照09 練習ファイルを操作しながらご覧いただいている方は、画面左下のシート見出しをクリックして、Sheet2に切り替えてください。

相対参照29 今度も果物の金額を求める表ですが、行方向、列方向の見出しが、前の章とは逆になっているパターンの表です。

相対参照10 「みかん」の「金額」欄であるB4番地を選択して数式バーを見ると、このセルには、
= みかんの単価 × みかんの数量
という意味の数式である、
= B2 * B3
という数式が入力されていることが分かります。

相対参照11 そして、「みかん」の「金額」欄であるB4番地の = B2 * B3 という数式のように、他の果物の「金額」欄にも、同様に = 単価 × 数量 の数式を入力したいです。
ということは、「りんご」の「金額」欄であるC4番地には、= C2 * C3
「梨」の「金額」欄であるD4番地には、= D2 * D3
という数式を入力したい、ということになります。
元となる「みかん」の数式内で使われているセル番地が、B、C、D、E、Fという風に1つずつズレていった数式を入力したいので、これも「1月、2月、3月・・・」や、「月、火、水・・・」のように、データを1つずつずらしながら入力できるオートフィルを使って、数式を入力していきます。

相対参照12 元の数式が入力されているB4番地を選択すると、そのセルは太枠で囲まれ、その右下にポッチが付いています。
このポッチにマウスポインタを合わせると、

相対参照12 マウスポインタが黒い十字の形になるので、この状態で右に向かってドラッグすれば、オートフィルができます!

相対参照14 「みかん」の「金額」欄の数式を元にして、残りのセルにも数式を入力することができました!

相対参照15 オートフィルで入力した数式が本当に正しいのか、確認してみましょう!
「りんご」の「金額」欄であるC4番地を選択すると、そのセルの中身が数式バーに映し出されます。
ちゃんと元の数式から1つズレた、= C2 * C3 になっていることが分かります。

相対参照16 その次の「梨」の「金額」欄であるD4番地を選択し、数式バーを見ると、これまた元の数式から更にもう1つズレた、= D2 * D3 の数式が入力されていることが分かります!

これも、基準となった「みかん」の「金額」欄の「= B2 * B3」という数式の裏で、「(この「金額」のセルから見て、)2つ上のセルと、1つ上のセルを掛け合わせてください」という考え方が働いていたから。
だからこそ、コピー先の数式も「2つ上のセルと、1つ上のセルを掛け合わせてください」となるよう、自動的に数式内のセル番地が調整されたわけです。

引き続き次のページでは、数式をコピーする他の方法についても触れていきます。
でも、Excel 基礎講座 ステージ1からこのページをご覧になっている方は、次ページの内容はもう少し後でご紹介しますので、このままExcel 基礎講座 ステージ1に戻って続きをご覧ください。

スポンサーリンク