実務で使えるExcel入門セミナー コピー&ペーストを前提にするExcelだからこそ、相対参照と絶対参照を使えるようにしておきます

Excelでの作業の一つに、関数や数式を使った分析があります。勘定科目の増減分析のように、項目が多くなると、関数や数式をコピー&ペーストして分析シートを完成させることになりますが、その際には、「相対参照」と「絶対参照」の違いを知っておくと便利です。

スポンサーリンク

Excelの「参照」を機能でおさえる

Excelに関して調べ物をしていると、「参照する」という言葉がよく出てきます。

 
慣れてしまえば大きな問題は無いのですが、一般的に使われる、

 
「さらに詳しく知るために、関連する資料等を見る」

 
という”言葉の意味”から理解しようとすると、違和感があると思います。

 
ですので、言葉にとらわれず、具体的にどのような機能を指しているのかを、おさえておく方がよいでしょう。

 
下の画像を見て下さい。 
カーソルを「B4セル」に合わせていて、数式バーに「B4セル」の入力内容が表示されています。

 

スクリーンショット 2015 03 26 11 06 23

 
数式バーには「=B2」と入力されていて、 
その結果、「B2セル」の入力内容である「2015年5月」が表示されているのが分かります。

 
この仕組みがExcelの「参照」という機能です。

 
つまり、

 
数式や関数の中で、セル番地を指定することで、 
指定されたセルのデータを使うように指示する

 
機能のこと。

 
上の「=B2」は、「B2セルの入力内容を使いなさい」という意味になるので、「2015年5月」が表示されたということです。

 
勘定科目など、たくさんの項目を分析する表を作る時は、この「参照」を使って入力した数式や関数を、コピー&ペーストして表を完成させます。

 
コピー&ペーストで入力された、数式や関数は特に手を加えなくても、自動的に正しい式になっています。

 
これが、「相対参照」の機能です。

 

 

相対参照   参照を含む関数や数式で、コピーするセルからの  ”距離”に応じて、セル番地を調整する

具体的に、数式をコピーしてみます。 
先ほどの、B4セル(「=B2」と入力されている)をコピーして、C4セルにペーストしてみましょう。

 

スクリーンショット 2015 03 26 11 36 34

 
すると、

 

スクリーンショット 2015 03 26 11 40 36

 
コピーしたB4セルには「B2」と入力されていたのに、 
ペースとしたC4セルでは「C2」になっています。

 
「コピー」は、あるセルに入力されているのと同じデータを、別のセルにも入力する機能です。

 
「同じデータ」を入力するのですから、「=B2」をコピーするなら、ペーストしたセルでも「=B2」となるはずです。

 
ですが、実際には「=C2」になっています。

 
これが、相対参照と言う機能で、

 
コピーするセル(B4セル)から、ペーストするセル(C4セル)までの距離に合わせて、 
数式に含まれるセル番地(B2セル)も調整してくれる機能

 
のことです。

 
上の例では、コピーするセルから、ペーストするセルまでの距離が、「右へ1列分」だったので、

 
数式のセル番地「=B2」も、「右へ1列分」調整して、「=C2」になりました。

 
Excelは、次のような、

 

スクリーンショット 2015 03 26 12 16 47

 
たくさんの項目について、数式を入力して完成させる表を想定していて、

 

スクリーンショット 2015 03 26 12 08 17

 
最初のセルに数式を入力して、

 

スクリーンショット 2015 03 26 12 19 30

 
コピーしてしまえば、

 

スクリーンショット 2015 03 26 12 22 23

 
ペーストするセルまでの距離に応じて、セル番地を調整してくれるので、 
正しく数式を入力することができるように、しているのです。

 
この「相対参照」はどうすれば使えるかというと、特に設定は不要。

 
Excelではデフォルトの参照の仕方が「相対参照」になっているためです。

 
「参照を使った関数・数式、なら「相対参照」になっている」

 
と考えて下さい。

 
一方で、場合によっては、コピーした数式や関数について、勝手にセルを調整して欲しくない場合もあります。

 
そのようなときに使うのが絶対参照です。

 

 

絶対参照   参照を含む関数や数式をコピーしても、  セル番地が動かないように固定する

次のような表を完成させることを考えます。

 

スクリーンショット 2015 03 26 12 52 07

 
経費全体に占める、各経費項目の割合を求めます。 
数式は次の通り。

 
割合=各経費項目の金額/経費合計

 
これを実際に入力すると、

 

スクリーンショット 2015 03 26 12 55 42

 
こうなります。

 
同じ式を、各経費項目の行にコピーすれば、割合は求められるので、

 

スクリーンショット 2015 03 26 12 57 04

 
最初の行をコピーしてペーストすると、

 

スクリーンショット 2015 03 26 12 59 13

 
こうなってしまいました。

 
「#DIV/0!」はゼロで割ってしまった時に表示されるエラーです。

 
エラーが出たセルを見てみると、

 

スクリーンショット 2015 03 26 13 02 43

 
分母のセルに「経費の合計額」の一つ下のセルが入力されているのが分かります。

 
他のセルも見てみると、

 

スクリーンショット 2015 03 26 13 06 25

 
同じように、分母のセルに「経費の合計額」から下にズレたセルが入力されています。

 
これは、先ほど出てきた「相対参照」の機能が働いた結果です。

 
コピーしたセルが「C5セル」だったので、そこからの距離に合わせて、数式のセル番地も調整されたのです。

 
コピー&ペーストで正しく表を完成させるためには、

 
分子:ペーストするセルに合わせてセル番地を調整 
分母:「経費の合計額」が入力されているセルに固定

 
のように、分子と分母で扱いを変えなければいけません。

 
分子で必要になる「セル番地の調整」は、「相対参照」でしたから、特に設定は不要です。

 
問題は、固定する場合。 
数式に出てくるセル番地を固定するためには、次のように、”$”をつけます。

 

スクリーンショット 2015 03 26 13 14 21

 
この、
 
数式や関数に含まれるセル番地に”$”をつけて、コピー&ペーストしても、 
セル番地を固定させる機能

 
のことを、「絶対参照」といいます。

 
「相対参照」から「絶対参照」に変えるためには、数式バーで「絶対参照」にしたいセル番地の所にカーソルを合わせて、

 

スクリーンショット 2015 03 26 13 20 02

 
F4キーをタッチすると、表示が変更されて「絶対参照」になります。

 

スクリーンショット 2015 03 26 13 21 38

 
=B5/$B$42

 
にしたセルをコピーして表を埋めると、

 

スクリーンショット 2015 03 26 13 23 56

 
正しく計算されました。

 

 

まとめ

関数・数式をコピーする際は、セル番地の参照方法に注意する必要があります。 
相対参照と絶対参照を使い分けられれば、入力の効率化につながります。
<おまけ>
J-WAVEの改編情報を確認しましたが、かなり動くようです。 
新番組の情報が出ないのがもどかしいです。

タイトルとURLをコピーしました