| HOME | backupに戻る | リンク元に戻る |

偏差値関数の書き方（表と一緒に）

ver80の概要と偏差値関数の書き方について、言葉だけだとセルの指定がわかりにくいので表もつけてみました。

■ver80の概要

ver40台から大きく4回の改造があり、ver80台に突入しました。
使用している関数はver40と大きな変更はありません。しかしなるべく手入力を減らしたかったので、 予想の印を自動で計算するためのシートや計算された結果を自動で分析するシートなどを追加しました。 この結果、データ貼付前の空の１ファイルで、サイズが１０ＭＢを越えてしまいました。

■使用している主要な関数

使用している関数と実際にエクセル上で使用する具体例は次のとおりです

標準偏差：STDEV

ソース実例：
=IF(ISERROR(ROUND((A1-SUM($A1:$R1)/$S$1)/STDEV($A1:$R1)*10,0)),0,ROUND((A1-SUM($A1:$R1)/$S$1)/STDEV($A1:$R1)*10,0))+50

上記の関数式を灰色のセルA2に入力します。すると、黄色のセルA1の値が、A1からR1までの間でどれくらいの偏差値を持っているかが、 灰色のA2セルに表示されます。青いS1セルは、増減する出走頭数に左右されず平均値を計算するの母数のための入力セルです。

偏差値を計算するだけのプレーンな関数は次のとおりです。

=(A1-SUM(A1:R1)/S1)/STDEV(A1:R1)*10+50

「S1」には出走頭数を入力します。手入力の他に自動計算させることも可能です。 私は別のトップシートに入力してここのセルでは引用させるようにしています。

「SUM(A1:R1)/S1」は平均値、 「(A1-SUM(A1:R1)/S1)」は、平均値「SUM(A1:R1)/S1」からのA1の離れ具合を数値化する部分で、 「STDEV(A1:R1)」は、標準偏差です。

「*10+50」は、標準偏差という山のすそ野の広がり・高さや位置を決める部分です。「*10」で山の形状が決まります。 「+50」を無くすと、平均が「0」になって計算上不都合（正負の混じった数の比較はしにくい）が生じるので、あった方が便利です。 「+50」で平均値は50になります。

ただし実際には、１８頭分の手入力をするのは面倒なので、セルをドラッグする場合が多い関係上、 「$」でセルの参照範囲を固定したり、１８頭に満たない場合のエラーを避けるために「ISERROR」関数も使用しています。

「A1:R1」のアルファベットの頭にドル記号を付けることで「$A1:$R1」となり、列の参照範囲（始点列と終点列）が固定されます。 よってB1セルを右側にドラッグしても「$A1:$R1」の調べる範囲は変わりません。「S1」セルは他のどのセルからも常に 決まってデータを利用したいセルなので、「$S$1」として列も行も固定してしまいます。 （数字の前に「$」を記入すると行が固定されます。）

「ISERROR」はif文と組み合わせることが多いです。「ISERROR」では自分で作成した関数の計算結果にエラーがあるかどうかを ○×（true,false）で判定してくれます。単体ではあまり使わず、if文と組んでエラーなら処理せず0やnullを出力させ、 エラーじゃないならその関数を実行して良いよ、と命令させたりします。この方法だと計算結果がエラーで出力されないので、 便利な場合が多いです。

if文とiserrorの組み合わせ：
=IF(ISERROR(偏差値関数),0,偏差値関数)+50

上記関数を全て使い、A1セルを右へドラッグした場合、隣のB2セルの入力値は下記のとおりとなります。 偏差値の母数となる部分や範囲はA2セルと変わらず、調査対象のセルだけがA1からB1に変わっています。

B2セル：
=IF(ISERROR(ROUND((B1-SUM($A1:$R1)/$S$1)/STDEV($A1:$R1)*10,0)),0,ROUND((B1-SUM($A1:$R1)/$S$1)/STDEV($A1:$R1)*10,0))+50

偏差値の関数は個々の関数を組み合わせて作る複合関数なので、事例が少ないので特に取り上げました。 その他の関数は、様々なサイトで紹介されていますので、今のところは省きます。

| HOME | backupに戻る | リンク元に戻る |