EXCEL の絶対参照と相対参照について理解する
EXCEL で関数を使って様々な計算をするようになってくると、あれ？なんか計算した結果が違うんだけど・・・という場面に遭遇することがあります。計算結果が合わないのは様々な理由がありますが、よくある間違いとしては「絶対参照(ぜったいさんしょう)」と「相対参照(そうたいさんしょう)」を理解していないことに起因する場合があります。正しく「絶対参照」と「相対参照」を理解することで、様々な計算間違いを防ぐことが出来るようになりますので、実際にデータ見ながらどうやって間違いを防いでいくか見ていきましょう。
絶対参照と相対参照を理解していないと計算結果が間違ってしまう例
それでは実際に計算結果が間違ってしまう例を見ることで、どういうことになるのかを理解すると、絶対参照と相対参照の必要性を理解できるようになります。ここでは以下のような、くだものの割引後の合計金額を算出するデータを用意しました。全てのくだものの合計金額から１０％割引をして計算する例です。実際に EXCEL を起動して同じようにデータを入力してみてください(色は説明のために使っているだけですので、色はつけなくて問題ありません)。
それでは「割引後合計金額」をそれぞれのくだもの毎に計算してみましょう。最初にリンゴの割引後合計金額を求める EXCEL の計算式を入力します。計算式は「合計金額ー（合計金額×１０％）」となります。
下図のように「割引後合計金額」と「リンゴ」の交わる "E2" というセル番号に「=D2-(D2*H1)」と入力します。入力したらエンターキーを押して計算式を完成させます。
「=」を最初に入力したセルは、そこに計算式があるよ！という指示になり、その後に続く計算式の結果がセルに表示されるようになります。リンゴの場合は計算式が「400-(400*0.1)=360」となりますので、リンゴの割引後合計金額は 360 円となります。
リンゴの計算が正しく出来ましたので、同じ計算式をそのままバナナ、イチゴ、パイン、メロンに適用していくだけです。同じ計算式を他のセルにも適用するには次のように操作します。割引ご合計金額のリンゴのセル (E2 セル)を選択し、セルの右下端を左クリックしたままドラッグしてメロンのセルまで移動させます。
しかしバナナ、イチゴ、パイン、メロンの割引後合計金額を見ると明らかに１０％割引が適用されておらず、計算結果がおかしくなっていますね。
これが絶対参照と相対参照を理解していない場合に発生する問題です。それでは以降で正しい計算結果を算出できる方法を見ていきましょう。
何故正しい計算結果が表示されなかったのか
リンゴは正しく割引後合計金額が算出されていたにもかかわらず、何故バナナの割引後合計金額が算出されなかったのか見てみましょう。それではバナナの値引後合計金額のセルを選択し、下図の計算式を見てみると「=D3-(D3*H2)」となっています(赤枠内)。この式の中の H2 (青枠内) は、下図の通り何も値が設定されていません。
これは計算式をリンゴ～メロンまでマウスをドラッグしてコピーした際に、割引率を参照していたセル番号がズレてしまったことが原因です。それでは常に割引率のセル(H1)を参照できるようにするにはどうしたらいいのでしょうか？
絶対参照を使って、常に同じセルの値を参照できるようにする
「割引後合計金額」と「リンゴ」の交わる "E2" というセル番号に「=D2-(D2*H1)」という計算式が入っていました。この計算式の中の H1 というセルだけを常に参照させたい場合には、セルの「絶対参照」という方法を使う必要があります。「=D2-(D2*H1)」という式は「相対参照」と呼ばれるものですが、これだと計算式をコピーしていくと H1 が H2 になってしまっていました。この式を「=D2-(D2*$H$1)」というように、H1 から $H$1 に変更します。$を付けることにより、そのセルを参照している情報は常に固定されるようになります。これが絶対参照と呼ばれるものです。
つまり絶対参照を使うと下記画像の赤枠内のように「=D2-(D2*$H$1)」となります。
そしてリンゴの割引後合計金額の計算式を、下記動画のように他のくだものにも適用していくと、常に割引率１０％のセル(H1)が参照されて正しい計算結果が反映されるようになります。
間違った計算結果が表示されていた時の「バナナの割引後合計金額」の式は「=D3-(D3*H2)」となっていましたが、絶対参照を使うことによって「バナナの割引後合計金額」の計算式は「=D3-(D3*$H$1)」となっており、正しく割引率１０％を参照できています(下図赤枠内)。
まとめ
つまり何も指定しない状態で計算式を書いた場合には、計算式は「相対参照」という状態になり、式をコピーするとセルの位置がズレてしまいます。しかし「絶対参照」を使えば、式をコピーしても常に同じセルの値を参照できるようになります。必ずあるセルの値を参照したい！という場合には絶対参照を使わなければいけない、ということを覚えておきましょう。
