[PC小ネタ] Excelとかの絶対参照と相対参照の話

2022年9月3日 11:38

Excel（NumbersやGoogleスプレッドシートも）で、コピって式を使い回したい時に知っていると便利なことです。絶対参照は説明すると長くなるので、ここでは基本的なことを説明します。

Excelは基本的にはセル番地をそのまま参照しているわけじゃない

何言ってんのって感じですが、こういうことです。

左の表は、カップとコーン別になっているので、右側にフレーバーごとの販売個数をまとめたいなということです。
バニラはC2にカップの、C8にコーンの販売個数があります。なのでその二つを足せばよいので、F2セルには　=C2+C8　という式が入っています。図01は入力状態ですが、ここでEnterキーを押せば結果が表示されます。
ここで、F2には　=C2+C8　という式が入っているのですが、Excelはそうは思っていないのです。じゃあどうなっているかというと、

今アクティブになっているセル（F2）から左に3つ移動した先のセル（C2）と、左に3つ移動してそこから下に6つ移動したセル（C8）を足す

としか思っていないのです。なのでこの状態で

図02のように、式を入力しているセルの右下にマウスカーソルを持ってきて、マウスカーソルが黒い十字（白じゃなくて）になった状態でマウスボタンを押しながら下へカーソルをずらす、つまりドラッグすると

図03のように式がコピーされていきます。
これができるのは、F2セルの情報が「=C2+C8」ではなくて、「今アクティブになっているセルから左に3列移動した先のセルと、左に3列移動してそこから下に6行移動したセルを足す」という状態だからです。つまりセルの位置を相対的に参照している、（今ある位置を中心に上下左右で参照している）ということになります。基本的に、Excelは相対参照でセルの位置を把握します。なので、式をコピーやドラッグするとその場に応じた結果が出るようになるのです。

相対参照とドラッグのおかげで式の入力ミスが減る

Excelなどのスプレッドシートは、基本的に相対参照でセルの位置を把握するので、一度入力した式をコピって使い回せます。今回の例でも、各フレーバーごとに式を入れるよりは、バニラの式を入れて、あとはオレンジまでドラッグしてしまえば楽ちんです。
基本的に、Excelを使用するときは式の入力は少ない方がいいです。今回の例でドラッグをしないとフレーバーごとに式を入力することになるのですが、どこかで式を間違えて、例えばグレープのカップとメロンのコーンを足してしまったりしてしまうかもしれません。しかも数字だけみるとなんとなくそれっぽい値になるので、なかなかミスに気づきません。
最初に作った式をドラッグすれば、他の式を間違うことがありません。もし最初の式が違っていたら、全部間違うので一つだけ違っているよりも気づきやすいと思います。

割合とかを出したいときは相対参照だと困る

といって、相対参照だと困ってしまうことがあります。

図04のようにフレーバーごとの割合を出そうと思いました。割合は、各フレーバーの販売個数を合計で割れば出せます。バニラの割合なら、図04のように　=F2/F8　で出せます。
で、これをドラッグすると変なことになります。

この#DIV/0!は、divided by 0（0で除算されました）、つまり「割り算の分母（割る数）が0だからエラーになって計算できないよ！」ということです。

バニラの割合であるG2セルには、表記上は図04のように　=F2/F8　という式が入っていますが、それは表示上のことです。Excelなどのスプレッドシートは、「式が入力されているアクティブセルの1列左のセルを、1列左で5行下のセルで割る」と思っています。なのでバニラの時はうまくいっても、1行下のストロベリーでは、1列左はよいとして、5行下は図06のように空欄となっていますからエラーになってしまいます。他のフレーバーも同様です。

絶対参照の登場

ということで、タイトルにもなっている絶対参照が出てきます。

図07の場合、合計で割るので割る数としてF8セルをクリックします。そのときにF4キーを押すと　F8　が　$F$8　となります。
なお、もう一度F4を押すと　F$8　に、さらにもう一度押すと　$F8　に、さらにもう一度押すと　F8　に戻ります。ここでまたF4キーを押すと　$F$8　になるので、うっかり押しすぎた場合は何回か押してください。
絶対参照には、行列固定、行固定、列固定、固定なし（相対参照）の4種類があります。今回は行列固定を使います。図07の状態をドラッグするとこうなります。

今度はドラッグしても、エラーにならずにちゃんと計算されました。

図09のように、絶対参照がうまくいっているので、エラーになりません。この場合Excelは、「アクティブセルの1列左をF8セルで割る」という理解になります。割る数の方は、どれだけ上下左右という相対的な位置関係ではなく、F8という絶対的な番地（どのセルから指定しても同じ場所）で指定されています。これが絶対参照です。

まとめ

スプレッドシートは、基本的には位置を相対的に参照しています。アクティブセルからどれだけ上下左右の位置にあるかというものです。
そのおかげで式のコピーができますが、動いちゃ困る場合もあります。そういうときに、絶対参照を使ってセル番地で指定を固定します。

他でも書きますが、Excelは相対参照と絶対参照を使い分けることが前提条件となっていて、関数の説明とかで「ここは絶対参照にしましょう」というような説明が一切ありません。「ここは絶対参照にしとかないとうまくいかないよね」ということを理解していることが、使用の前提になっています。Excelを使った計算を面倒と感じるか、楽ちんだと感じるかは絶対参照の理解によるところが大きいと私は思っています。

この記事終わり

この記事が気に入ったらサポートをしてみませんか？