23.2.11. セルの絶対参照

Excel 中で式をコピー & ペーストすると、自動で番号がずらされるのでした。ですが場合によっては「コピー & ペーストした先でも、同じ位置のセルを指定したい」ということがあります。このような場合に使う絶対参照の仕組みを学びましょう。

このページでは 23.2.10. 簡単な統計処理で作ったファイルを使います。まだファイルを作っていない人は score_total_for_23_2_11.xlsx をダウンロードしてください。

絶対参照の方法 #

セル中の式に F4 や G2 のような番地を入力すると、そのセルの値が参照されます。ここで、列番号の前にドル $ 記号をつけて $F4 や $G2 とすると、別のセルにコピー & ペーストする際に、式中の列番号が変化しません。同様に行番号に $ をつけて F$4 や G$2 とすると、今度はコピー & ペーストの際に行番号が変化しないようになります。

実際に試してみましょう。例として、成績表のデータから偏差値の計算をしてみましょう。試験の成績の偏差値は

50 + 10 * ( (得点 – 平均点) / 標準偏差 )

という式で定義されます。既に平均 AVERAGE と標準偏差 STDEV.P の計算の仕方は知っているので、これらを使って偏差値が求められます。

この節では偏差値の計算法を説明します。しかし実際に偏差値を使うときは「偏差値という値に意味があるのか」を考えないといけません。

偏差値は「データの分布が正規分布だと仮定したとき、得点の位置がどの辺か」を測る量で、本質的には、正規分布の標準化スコアと同じものです。分布が正規分布の形から外れてしまえば、偏差値を考える意味はほとんどありません。統計量に踊らされないよう注意しましょう。

まずセル I1, J1 にそれぞれ「数学偏差値」「英語偏差値」と記入しましょう。次に、偏差値の計算で必要な値 (50 と 10) をシートに予め書き込んでおきます。適当な 2×2 セル、たとえば D37, E37, D38, E38 に

| 平均偏差値 | 50 | | 標準偏差 | 10 |

と入力してください。

偏差値を表示させるセルの式に直接 50 や 10 と書いてしまえば、この準備は不要にはなります。ですが**同じ値を繰り返し用いるときは、どこか 1 つのセルに値を書き込んでおいて、常にそのセルを参照すべきです。**この方法を使えば、後で値の修正が必要になった場合でも、修正箇所が 1 つだけですみます。また式中に数字だけが書かれていると、後で「この数字なんだっけ？」と意味を忘れた際に困ります。式だけから意味を推測できないような数値は magic number と呼ばれ、(主にプログラマに) 忌み嫌われています。

これで準備ができたので、偏差値の入力をしましょう。まずセル I2 に

=$E$37+$E$38*((E2-E$32)/E$33)

と入力してください。

$E$37 = 50, $E$38 = 10, E2 = 得点, E$32 = 平均点, E$33 = 標準偏差なので、これが偏差値の計算式になっています。式中で参照される番地と対応するセルが同じ色で強調されるので、目で見て間違いがないか確認すると良いでしょう。

で式を確定すると、値が出力されます。

この式をコピーして、I3 から I31 までペーストします。セル I2 の右下にマウスカーソルを持っていき、フィルハンドルを出した状態でダブルクリックすると、ワンタッチでコピー & ペーストできて楽です。

ここでセル I3 の中を見ると =$E$37+$E$38*((E3-E$32)/E$33) となっています。

元々のセル I2 の式と注意深く見比べてください。E2 は E3 に変わりましたが、$E$37, $E$38, E$32, E$33 はそのままですね。今のコピー & ペーストで**通常の参照をしたセルは行番号がずれたのに対し、$ を付けて参照にしたセルは行番号がずれていません。**これが絶対参照というものです。

さらに、セル I2 の中身をセル J2 にコピー & ペーストしてください。そうすると式が $E$37+$E$38*((F2-F$32)/F$33) となっているはずです。

先ほどの縦方向のコピー & ペーストでは、E$32, E$33 は変化しませんでした。一方、今の横方向のコピー & ペーストでは E$32, E$33 がそれぞれ F$32, F$33 へと変化しています。E$32, E$33 では行番号は絶対参照であり、列番号は通常の参照であることが分かりますね。あとは再びコピー & ペーストすれば、英語の偏差値が計算できます。

以上のように Excel では式中におけるセルの参照時に $ 記号を使うことで、行や列、あるいはその両方を絶対参照にすることができます。式に定数が登場する場合などに、絶対参照は極めて強力です。使いこなせるようになりましょう。

絶対参照で使う $ の入力が面倒な人は、Mac 環境では

t のショートカットを、Windows 環境では F4 キーを使いましょう。たとえば、いま入力したセル I2 をダブルクリックして編集モードにし、左右キーを押して $E$37 にカーソルを合わせてください。この状態で Mac なら環境なら

t を、Windows環境なら F4 キーを押すと、$ がついたり外れたりします。Mac 環境と Windows 環境とで若干順番が違うのですが、いずれにせよ「通常の参照」「列のみ絶対参照」「行のみ絶対参照」「列・行両方絶対参照」が順繰りに入れ替わります。