23.2.11 セルの絶対参照

Excel 中で式をコピー & ペーストすると，自動で番号がずらされるのでした．ですが場合によっては「コピー & ペーストした先でも，同じ位置のセルを指定したい」ということがあります．このような場合に使う絶対参照の仕組みを学びましょう．
このページでは hwb23.2.10 簡単な統計処理 で作ったファイルを使います．まだファイルを作っていない人は score_total_for_23_2_11.xlsx をダウンロードしてください．

絶対参照の方法

セル中の式に F4 や G2 のような番地を入力すると，そのセルの値が参照されます．ここで，列番号の前にドル $ 記号をつけて $F4 や $G2 とすると，別のセルにコピー & ペーストする際に，式中の列番号が変化しません．同様に行番号に $ をつけて F$4 や G$2 とすると，今度はコピー & ペーストの際に行番号が変化しないようになります．
実際に試してみましょう．例として，成績表のデータから偏差値の計算をしてみましょう．試験の成績の偏差値は

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

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

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

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

平均偏差値50
標準偏差10

と入力してください．
23_2_11_a

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

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

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

と入力してください．
23_2_11_b
$E$37 = 50, $E$38 = 10, E2 = 得点, E$32 = 平均点, E$33 = 標準偏差なので，これが偏差値の計算式になっています．式中で参照される番地と対応するセルが同じ色で強調されるので，目で見て間違いがないか確認すると良いでしょう．
23_2_11_c
return で式を確定すると，値が出力されます．
23_2_11_d
この式をコピーして，I3 から I31 までペーストします．セル I2 の右下にマウスカーソルを持っていき，フィルハンドルを出した状態でダブルクリックすると，ワンタッチでコピー & ペーストできて楽です．
23_2_11_e
ここでセル I3 の中を見ると =$E$37+$E$38*((E3-E$32)/E$33) となっています．
23_2_11_f
元々のセル I2 の式と注意深く見比べてください．E2 は E3 に変わりましたが，$E$37, $E$38, E$32, E$33 はそのままですね．今のコピー & ペーストで通常の参照をしたセルは行番号がずれたのに対し，$ を付けて参照にしたセルは行番号がずれていません．これが絶対参照というものです．
さらに，セル I2 の中身を セル J2 にコピー & ペーストしてください．そうすると式が $E$37+$E$38*((F2-F$32)/F$33) となっているはずです．
23_2_11_g
先ほどの縦方向のコピー & ペーストでは，E$32, E$33 は変化しませんでした．一方，今の横方向のコピー & ペーストでは E$32, E$33 がそれぞれ F$32, F$33 へと変化しています．E$32, E$33 では行番号は絶対参照であり，列番号は通常の参照であることが分かりますね．あとは再びコピー & ペーストすれば，英語の偏差値が計算できます．
23_2_11_h
以上のように Excel では式中におけるセルの参照時に $ 記号を使うことで，行や列，あるいはその両方を絶対参照にすることができます．式に定数が登場する場合などに，絶対参照は極めて強力です．使いこなせるようになりましょう．

tips絶対参照で使う $ の入力が面倒な人は，Mac 環境では t のショートカットを，Windows 環境では F4 キーを使いましょう．たとえば，いま入力したセル I2 をダブルクリックして編集モードにし，左右キーを押して $E$37 にカーソルを合わせてください．この状態で Mac なら環境なら t を，Windows環境 なら F4 キーを押すと，$ がついたり外れたりします．Mac 環境と Windows 環境とで若干順番が違うのですが，いずれにせよ「通常の参照」「列のみ絶対参照」「行のみ絶対参照」「列・行両方絶対参照」が順繰りに入れ替わります．