【PC小ネタ】Excelなどのスプレッドシートは、絶対参照の理解が使用の前提条件になっているという話
長いタイトルですが、まあそういうことです。ついでにRANK関数についても少し説明しています。
相対参照・絶対参照とは
Excelなどのスプレッドシートの絶対参照については、まあいろいろ思うところがあるので、基本的な話はこちらとか、こちらの記事も読んでいただければと思います。
で、スプレッドシートは、セル位置の把握を相対参照で行っています。今アクティブになっているセルから上下左右にどれだけ離れているか、というかたちで他のセルの位置を把握しています。
これを、例えば「いつでも絶対にA3のセルの内容が欲しい」という場合は位置関係（上下左右にいくつ）ではなくて、セル番地で直接指定します。ただ、普段の相対参照の時も、上下左右にいくつって書くのでは無くてセル番地で指定しています。それが多分、理解が難しい原因の一つのように思いますが。
で、絶対参照の時は、同じようにセル番地で書くんだけど＄（ダラー）をつけようということになっています。付け方で、行列固定($A$3)、行固定(A$3)、列固定($A3)があります。
関数利用では、絶対参照を知っていることが前提条件
例えば、こんなデータがあったとしましょう。
このデータで、D列に売り上げの順位を出すことにしたとします。順位を出すには、=rank.eq という関数があるのでそれを使います。
=rank.eq関数は以下のような指定方法になります。
=rank.eq（順位を出したい数値,順位を判定する範囲,［順序］）
図02で表示されている指定（関数では引数（ひきすう）といいます）の最初の「数値」が、順位を出したい数値の指定です。図02では、C2のバニラのカップの販売個数（616）を指定しています。次の引数の「範囲」がどの範囲の順位かということになるので、図02ではC2からC13までの販売個数全体を指定しています。最後の引数［順序］は、0ならデータの降順（大きい順）の順位、1なら昇順（小さい順）の順位です。省略すると0扱い、つまり大きい順の順位となります。ちなみにExcel関数の引数で、ガイドで［］がついているものは省略可能な引数です。
さて、この状態でドラッグすると、変なことになります。
なんか、2位や1位がいっぱいあります。どう見てもおかしいです。
図04のようにC8をみてみると、2つめの引数である範囲が、C8からC19となっています。このデータはC14より下は空欄なので、実質6つのデータの順位を出していることになってしまいます。
図02のところで指定した式は
=rank.eq(C2,C2:C13)
となっていました。この式が入力されているD2セルからすると、「1列左のセルの内容が、1列左から1列左の11行下までの範囲の中で大きい順で何番か出せ」という命令が入っていることになります。それをそのままドラッグすれば、「1列左の内容」はいいとしても、「1列左から1列左の11行下までの範囲」は、図04にあるようにどんどん下にずれていくことになります。
これを防ぐには絶対参照を使って、
=rank.eq(C2,$C$2:$C$13)
と指定してやれば解決します（絶対参照の指定方法については、こちらやこちらの記事を読んでみてください）。
これで一件落着なのですが、こういうときに絶対参照を使って引数の「参照」を指定しましょうという説明は、少なくともExcelのヘルプ等には出てきません。Web等での解説には、書いてあることもありますが、Excelのヘルプ文章ではお目にかかった記憶がありません。これはExcelだけで無く、NumbersやGoogleスプレッドシートでも同じです。
まとめ
Excelに限らず、NumbersやGoogleスプレッドシートなどのスプレッドシートは、ほとんどどの関数でも絶対参照の指定が使えます。実際に全部試したわけではないですが、使えない関数にお目にかかったことがありません。状況によって絶対参照を使う意味が無い場合はもちろんありますが。
しかし、ヘルプ等の文章で「ここは絶対参照で指定しましょう」という指示は、どのアプリでもみたことがありません。つまりスプレッドシートアプリを使う場合は、絶対参照を理解していて、相対参照にするか絶対参照にするか判断できるというのが前提条件になっているということです。なのでいちいち説明がないのでしょう。
関数の使用時に、ヘルプ等で「ここは絶対参照」とか出てくることはないので、絶対参照の概念を理解しておくと、スプレッドシートがよりうまく使えると思います。
おまけ =rank.eq関数と=rank.avg関数
rank関数は以前は=rank関数だけだったのですが、Excel2010から=rank.eqと=rank.avgという2つの関数が登場しました。=rank関数は=rank.eq関数と同じ機能です。なお、Excel2010よりも前のExcelで=rank.eqや=rank.avgを使ったファイルを開くとエラーになります。
=rank.eqと=rank.avgの違いは、同順位の扱いです。
図06は、男子ソフトボール投げのデータだと思ってください。
左側のデータでは、22.1メートルの児童が2人（No.02、No.06）います。=rank.eq関数だと、順位は1位、2位、3位、4位、5位、5位、7位･･･となります。=rank.avg関数だと、順位は1位、2位、3位、4位、5.5位、5.5位、7位･･･となります。
eqはequal、avgはaverageの略です。=rank.eqは同順位の一番上のものを順位とします。この場合5位と6位が同順位なので、一番上の5位ということになります。=rank.avgは同順位の平均を順位とします。この場合5位と6位が同順位なので、平均の5.5位ということになります。
左側のデータでは、22.1メートルの児童が3人（No.02、No.06、No.07）います。=rank.eq関数だと、1位、2位、3位、4位、5位、5位、5位、8位･･･となります。5位、6位、7位が同順位なので、一番上の5位ということになります。
=rank.avg関数だと、1位、2位、3位、4位、6位、6位、6位、8位･･･となります。5位、6位、7位が同順位なので、平均の6位ということになります。
感覚的には、=rank.eq関数を使うことが多いと思いますが、用途によって使い分けてください。
この記事終わり
この記事が気に入ったらサポートをしてみませんか？