計算式(「相対参照」と「絶対参照」を理解する。)

計算式では、関数は比較的理解しやすく、簡単に使い始められますが、この「相対参照」、「絶対参照」を理解しないで使っている人が多いと思います。

「相対参照」、「絶対参照」を意識する必要があるのか。「相対参照」とは、一般に使われる「A1参照形式」では「$」を付けないでセル参照する形式です。ですが意味を考えずに「$」を余分に入力するのが面倒で省略しているというような人も多いようです。「$」をつけた式を見たことがあるがその意味は知らないとか、全く知らないとか、「$」は書いても書かなくても良いと思っている方はぜひご一読ください。

なお、初心者の方が「計算式」のブロックを先頭から順にご覧になっているとしたら、最初がこのページなので「何がなんだか解らない」となってしまいます。この最初の２ページは本来なら基本として押さえておいてほしい重要項目なのですが、一旦セルに書き込んだ計算式を他のセルに移動したりコピーしたり、また行列の挿入・削除も一切しないというなら、とりあえずは知らなくてもその場では問題は起こりません。ですが、仕事でExcelを使っているといずれはこういう件が作用して思わぬ結果になるケースに遭遇するので、頭の片隅にでも置いておくと良いと思います。

なぜなら、Excelで作成したものは後から「転用」されます。あなたが他の人が作成したものを少し変更して他の表を作成する、逆に他の方があなたが以前に作成したものを「転用」するかも知れません。その時に式の入っていたセルの行列を変更したら思わぬ結果になってしまうのです。最初の状態で正しかったので問題ないだろうと思ってデザインを変更して提出したら「合計」が全然合っていなかったなどということが起きることがあります。

「相対参照」「絶対参照」の動作を試してみましょう。: 参照関係の話だけなので、演算を含まない単なるセル参照だけの式で説明します。
C3セルにA1セルの値を参照するという計算式「=A1」を入力します。これは「相対参照」の式になります。

すると、このようにC3セルにA1セルの値が表示されます。これは初心者の方でも理解されていることだと思います。

続いてD3セルには同じA1セルの値を参照する「絶対参照」の式を入力します。

すると、このようにD3セルにもA1セルの値が表示されます。

では、4行目にも全く同様にA1セルを参照するようにしたいということで、現在の3行目の式が入ったセル範囲を1行下にコピーしてみます。

すると、このようにC4セルは「0」になってしまいました。数式バーを見ると「=A2」に変わっています。つまり参照しているセルが１行下に移動したということです。これが「相対参照」の特徴です。

D4セルは「12345」です。数式バーも「=$A$1」で変わりありません。
並べて比べているので違いを見つけることができているだけで、単にC3セルを位置を変えるためにC4セルにコピーしたのだとすると結果が変わってしまったことに気がつかないというミスを起こす可能性があります。
「相対参照」が悪者だということではありません。: 上の例で見ると、「すべて絶対参照にしてしまえば良いのでは」と思うかも知れませんが、そうではありません。
たとえば、数量×単価で金額を算出する例で、これが複数行連続するケースを見ると、

(画像をクリックすると、Excelサンプルを収容したZIPファイルがダウンロードできます)
このようにD2セルは「=B2*C2」、これを下へコピーするとD3セルは「=B3*C3」、 D4セルは「=B4*C4」となってくれるので、この方が正しいわけです。
このケースを絶対参照で書いてしまうとどの行も２行目の金額になってしまいます。
（横方向は相対参照にする必要がないので、正確を期するならD2セルは「=$B2*$C2」が正しいでしょう）
次は「相対参照」で書くと明らかに不具合を起こす例です。: 隣の「分類名」の列を見てみましょう。
当然、先頭行(ここでは2行目)に計算式を登録して、後の行にはコピーしていきます。
ここでは「分類」のコード(A列)から「分類」のシート上で参照して、合致した「分類名」をE列に表示させるものとします。

「相対参照」、「絶対参照」を意識しないで式を先頭行に登録すると「=VLOOKUP(A2,分類!A1:B10,2,0)」となります。
この第2引数の「分類!A1:B10」が分類コードから分類名を変換するためのテーブルに当たるセル範囲です。
（このVLOOKUP関数の説明は「コードから名称を表示」になります）

「分類」シートは、分類コードのマスタテーブルです。

このように、分類コードのマスタテーブルは「A1:B10」のセル範囲です。これで上記の式で正しく参照されます。

さて、Sheet1に戻って、E2セルを下の行にコピーしていってみます。

このようになってしまいました。 4行目までは分類名が表示されていますが、そこから下は「#N/A」というエラーになってしまいました。
なぜ、こうなってしまったのかということで、数式バーを見ると、貼り付け先の先頭行だから3行目の式なのですが、最初の方で説明した通りA列の参照セルは第1引数で「A3」になっていて正しいのですが、第2引数も相対参照で記述したので「分類!A2:B11」と1行分下へズレていってしまっています。
このため、分類コードを探すためのマスタテーブルの範囲が移動してしまうので、範囲から外れた分類コードは見つからなくなって「#N/A」というエラーになってしまということなのです。

正しい状態にするために、分類名(E列)に最初に投入したE2セルの式からやり直します。

この画像はE2セルの計算式を正しいものに変更してコピーし、3行目以降に貼り付けてからE2セルに戻った状態です。
分類名(E列)はすべての行に名称が表示されてエラーがなくなっていることが判ります。
第1引数の行方向(数字)の「2」以外は、列方向を含めてすべてに「$」を付けて絶対参照に切り替えているのでマスタテーブルの参照範囲もズレることなく正しく参照できるようになりました。

つまり、計算式を投入するセルの移動（コピー）によって式の中の他セル参照の行列を移動分行列に連動して変更する部分を除いては、本来は「絶対参照」で記述するべきと考えます。

次に説明する「R1C1参照形式」によって「相対参照」「絶対参照」がもっと理解できます。