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 環境とで若干順番が違うのですが,いずれにせよ「通常の参照」「列のみ絶対参照」「行のみ絶対参照」「列・行両方絶対参照」が順繰りに入れ替わります.