§ 5. 統計の初歩の初歩
Excel 関数には平均、標準偏差、相関など多くの統計関数が用意されており、 それらをうまく利用すれば、簡単に結果を求めることが出来る。しかし、原理 を理解せずに関数だけを利用しているととんでもない過ちを犯す可能性がある。 まずは基礎の数式を用いて計算し、Excel 関数を用いた結果と比較し、結果が一 致することを確かめた後「安心して」Excel 関数を利用することをお勧めする。 例題17．右の表は 30 名の英語と数学の試験の成績である。この試験の平均、標 準偏差を求めよう。 Excel 関数を利用すれば簡単に求めら れる。英語の平均は70.0 点、標準偏差は 14.7 点、数学の平均は 68.0 点、標準偏差 は19.5 点である。 Excel 関数を用いないで、平均と標準 偏差の定義に基づいて計算する方法も 試みておいてほしい。平均は点数の合計 を人数で割ればよい。Excel 関数を使わ ずに和を求める方法は数値積分の際に 説明したので、そちらを参照してほしい。 「学生番号」がA1 に書かれていると して話を進めよう。英語の最高点と最低 点は =MAX(B2:B31)、=MIN(B2:B31)で 求められる。B2:B31 はシート上で範囲を選択すれば自動的に書き込まれる。 右下の表は数学の得点順に書き直した表である。 並べ替えはA1 から C31 の範囲を選択した後、Excel のメニ ューから「データ」を選択し、続いて「並び替え」を選択 する。列として「数学」選択し順序は「最大から最小へ」 を選ぶ。 次に数学に関して得点ごとに人数を記入した表を作って みよう。数学の点数は10 点刻みであるからもっと簡単な方
法もあるだろうけれど、後に度数分布表を作ることも視野に入れて表を作るこ とにする。完成した表をここに掲げておく。 人数を数えるのに、80 点は何人、90 点は何人と数える方法もあるが、度数分 布表の一般的な考え方として、80 点以上 90 点未満何人、90 点以上 100 点未満 何人という数え方をする。ただし、この方法だと、100 点は特別扱いにしなけれ ばならない点が取り扱い上の問題として残るので適宜対応しなければならない。
Excel 関数として COUNTIF を使う方法と FREQUENCY を使う方法がある。
まず COUNTIF を使う方法から説明しよう。この関数の方が汎用性が高く、基 本的だと思う。 数学のデータはC2 から C31 に入っている。この中から 80 点のものを選び出 すのは =COUNTIF(C2:C31, 80) とすればよい。それでは 80 点以上の人数を数 えるにはどうしたらよいだろうか。COUNTIF(C2:C31,≥=80)と書き込んでも Excel は理解してくれない。COUNTIF(C2:C31,”>=”&80)と書き込むと「≥=80」 のことだと理解してくれる。 「80 点以上 90 点未満」の人数は =COUNTIF(C2:C31,”>=”&80) - COUNTIF(C2:C31,”>=”&90) で求められる。 左の図を用いて作り方を説明しよう。 E 列には 100 点から 0 点まで 10 点刻み に区切りの点数を入れる。0 は 0 点以上 10 点未満、90 は 90 点以上 100 点未満 を示す。100 だけは 100 点を示す。（100 点以上といってもいいが、意味がある のは100 点だけである。） F2 に=COUNTIF(C$2:C$31,”>=”&E2) と書き込む。Excel は”>=”&E2 を≥100 と解釈する。=COUNTIF(C$2:C$31,100) としても構わない。100 点は特別だから このセルへの書き込みは他のセルの場 合とは異なる。＄もなくてもよい。 F3 には=COUNTIF(C$2:C$31,”>=”&E3) - COUNTIF(C$2:C$31,”>=”&E2)と書き
込む。これをF12 までドラッグする。（C の前に＄をつけても構わないが、この 場合はなくても構わない。） この表をもとに平均と標準偏差を求める方法を示しておく。G 列には点数と その点数をとった人数の積を書き入れる。それを加え合わせた結果が G14 に示 されている。G14 の値を人数（F14）で割った値が G15 に示される平均である。 G14 の値を求めるには=SUMPRODUCT(E2:E12,F2:F12)で直接計算することも 可能である。 標準偏差を求めるために H 列を用いる。計算するのは点数と平均点の差の２ 乗に人数をかけた値である。これを加え（H14）受験者数（30）から１を引いた 値（29）で割り、それをルートで開いた値が標準偏差になる（H16）。元のデー タから直接計算した平均、標準偏差（C32,C33）と比べて、値が等しいことを確 認してほしい。 学生番号順のデータ、数学の高得点順に並べたデータ、点数ごとの得点者人 数を表したデータのいずれを元にしても、当然のことだが、まったく同じ平均 値、標準偏差が求められた。この様子を表したのが下の図である。 一人一人の得点が底面が 10mm 10mm の正方形、高 さが得点と同じ mm の柱で 示されている。A は学生番号 順に並べたもの、B は得点順 に並べたもの、C は得点ごと に整列させたものである。点 数の書いてあるプラカード の後ろに整列したところを 想像すればよいだろう。柱を 並べ替えただけだから、柱全体の体積は同じ。これが得点合計になる。平均は これを人数で割ればよい。図では人数を底面積と考えればよい。この柱が土で 出来ているとするなら、敷地の上に平らになるようにならしたら高さはいくら になるかということである。 C の場合底面の奥行きが度数 N でこれは得点（P）が決まれば値が決まる。つ まりN は P の関数である。学生数は底面積であるから !N(P)である。 試験の点数なら値はとびとびである離散分布であるから面積の計算は∑でよ
いが、分割が細かくなりついに連続分布になれば面積計算は積分になる。分割 の幅が無限小になればデータ数は0 になってしまうが、微小な幅 x x+∆x に属するデータ数はf(x)∆x で表される。f(x)を分布密度関数と呼ぶ。底面積は f(x)∆x を全て加え合わせればよいのだから、 f x dx で求められる。x=x における高さ はx であるから立体の体積は x f(x)∆x を加え合わせればよい。分割が無限小にな ればこの和（立体の体積）は積分になり !" ! !"であたえられる。平均は体積 を底面積で割れば得られるのだから !" ! !" ! ! !"で求められることに なる。 例えば放射性核種の存在量は初期値をN0とすれば ! ! = !!!!!"で表される。 ! = !!!!!!!"!" !! !!!!!"!" ! !! が平均寿命である。 例題 18．重み付き平均の計算を行おう。 N 個の観測を行った時、全てのデータが同じ精度でない 場合がある。平均や標準偏差といった統計値を得る場合、 精度の悪いデータと精度の良いデータを同等に扱うのは 問題であろう。このような場合、データに重みをつけて計 算する。重みの付け方は必ずしも機械的ではないが、ここ では誤差の二乗に反比例した重みがつくとするモデルを 採用しよう。重み付きデータでは簡単にExcel 関数を使う ことは出来ないので、基本に返って計算しよう。 まず誤差の次の列に誤差の二乗の逆数 1 !!!を計算しよう。その和（S）で1 ! !! を割ったものが各測定の重み(wi)である。∑wi で割ったので、重みの合計は１に なる。例えば1 !!!は1/2.32 =0.435 であり、S は 3.64 になる。これより w1は0.12 になる。重み付き平均は∑PHi*wiで求められ、この測定では236.3mV になる。 標準偏差は ∑(PHi – PHave)2wiで求められる。この測定では4.4mV になる。重 みを考慮しない平均と標準偏差はそれぞれ236.0mV、4.6mV である。 この測定表と数学の点数の表を比べてみよう。100 点の学生が１人、90 点の 学生が5 人というのは 90 点という「測定値」に 5 倍の重みがあると考えること が出来る。ある得点を取った人数を総人数で割ったものが重み（wi）であるから、 90 点に対する重みは 5/30=1/6、100 点に対する重みは 1/30 となる。
例題 19．度数分布表を元に平均、標準偏差を求めよう。 現在なら、コンピュータを利用して簡単に平均や標準偏 差を求めることが出来るが、コンピュータがないときはデ ータ数が多い場合には全データを"電卓"のような計算器に 打ち込んで統計計算を行うのは大変な労力を要した。度数 分布表にデータを整理すると大まかな傾向がつかめるだけ でなく、多少の誤差を容認すれば平均値、標準誤差を求め る労力もかなり削減される。英語の得点の度数分布表を作 り、この表をもとに平均値、標準偏差を求めてみよう。 右上に完成した度数分布表が示してあります。この表を見ると細かな点数は 分かりません。この例ではデータの数が少ないので問題はありますが、各階級 の中では均等に分布していると考えて作業をします。60 点台の人の平均点は 64.5 点（60 点から 69 点まで均等に分布していると考えるため）とし、64.5 点の 人が8 人いるという風に計算します。標準偏差は 各階級の人数 [（階級の代表 値- 平均点）の２乗] の和を総人数から１引いた数（29）で割れば得られます。 重み付き平均の考え方では 29 でなく 30 で割ることになりますが、データ数 が多くなり、かつ正確な値を初めから求めるつもりがない方法なら、普通の重 みの考え方でも差は気にしなくていいでしょう。
度数分布表を作るための関数 -- COUNTIF と FREQUENCY 度数分布表を作るのにCOUNTIF という関数を用いた。基本形は = COUNTIF(データ範囲, 判定数値) である。=COUNTIF(C2:C31,60)と書けば、「C2 から C31 までのデータの中から、 60 に等しいデータの数を数えなさい」という意味である。しかし判定条件は「ち ょうど等しい」という場合はまれで、「50 以上」とか「60 以下」とかある範囲 で判定することが多い。特に度数分布表を作る場合は「50 以上 60 未満」といっ た条件のデータ数を数える必要がある。 本文にも書いたが、「値が 50 以上のデータ数を求めよ」というのを、 =COUNTIF(C2:C31,>=50)と書いても Excel は計算してくれない。「>=」は文字 記号であり、50 は数値であることを理解させなければならない。そこで文字列 を示すときに一般的に使われる” ”で囲む方法を試してみる。「”>=50”」として もだめ。それではと「”>=”50」を試してみるが、これもだめ。「>=」に「50」 を加えればいいのだからと「”>=” + ”50”」にしてみてもだめ。結局「”>=”&50」 なら計算してくれることが分かる。 度数分布表を作る際に判定数値をいちいち式に書き込むのも面倒である。判 定数値を縦に並べて書いておいて、そのセルの値を引用できればもっと簡単だ ろう。前ページの表ではE2 から E12 に階級の区切り（つまり判別のための数値） を書いてある。F2 に =COUNTIF(C2:C31,”>=”&E2)と書き込むと数学の点数デ ータの中から 100 点以上のデータ数を書き出す。ここで F2 を F3 にドラッグし てみよう。F3 には=COUNTIF(C3:C32,”>=”&E3)と書き込まれ、6 という数値は 示される。90 点以上は 6 人だから正しいと判断してはならない。このまま F12 までドラッグすれば数値は合わなくなる。ドラッグするに伴いデータの範囲が 変わっていることに気づいてほしい。F2 に =COUNTIF($C$2:$C$31,”>=”&E2) と書き込んでデータの範囲が動かないようにすれば正しい値が求められる。デ ータが縦１列に並んでいて、下方にドラッグする場合はC の前の$は省略可能で ある。データが横１行に並んでいる場合、下方にドラッグするならC の前の$は 省けない（数字の前の＄は省ける）。 正しく計算されれば F12 には全学生数 30 が示されるはずである。このようなチェックを気にして行う習慣は大切である。 求められるのは例えば「50 点以上の人数」だから、「50 点以上 60 点未満」の 人数を求めるには「50 点以上の人数から 60 点以上の人数を引け」ばよい。F3
に =COUNTIF($C$2:$C$31,”>=”&E3) - COUNTIF($C$2:$C$31,”>=”&E2)と書き
込み、F12 までドラッグすれば度数分布表が完成する。この場合、F2 から F12
までの合計を計算し全学生数と等しいことを確認しておくことは大切である。
「度数分布」のことを英語で「Frequency Distribution」 という。FREQUENCY
という統計関数は度数分布の計算に特化した関数である。
Excel にこの関数に関する説明を求めると下のような説明が現れます。
配列数式とか、Control+U とか、⌘+Z+Return とか（Mac の場合です）結構 面倒くさそうです。もっと普通に扱う方法を紹介しましょう。
英語と数学の点数の表を使って話を続けましょう。 F2 に =FREQUENCY(C2:C31,E2)と書き込んで下方にドラッグします。 結果は(1)のようになります。F３に入っている数式を確認すると =FREQUENCY(C3:C32,E3)となっています。28 というのは C3 からC31（C32 は空白！）までで、90 点以下の人数ということ です。29 人の中に 100 点が１人いますので、28 人になります。 データが変わらないように$を付けデータ範囲を固定します。(1) と同じようにした結果が(2)です。100 点以下の人数、90 点以下 の人数と読めば正しい答えになっています。H2 には(2)の式を用い E2 の代わり にE2:E12 を入れてドラッグしてみました。正しい答えが得られたようです。た だし、H3 に入っている式を確認すると、=FREQUENCY(C$2:C$31,E3:E13)にな っていて、意味のないE13 が入っています。 詳しく検討する前に(3)と同じやり方を英語に対して行ってみ ます。英語の度数分布の正解も示しておきます。 数学の(3) と同じやり方では正しい答えになりません。いない はずの100 点が 3 人もいます。３人いるのは 90 点台だというこ とでG2 に書き込んだ式を H3 に移してみましたが、正解とは違 った結果になります。 正解の度数分布の区切りは例えば「80 点以上 90 点未満」となっているのに対 して、FREQUENCY を使った場合は「80 点を超え 90 点以下」を計算すること になります。数学の場合は10 点ごとの分布なので、「80 点以上 90 点未満」とい うのと「70 点を超え 80 点以下」というのがたまたま一致しているだけなのです。 また、ドラッグするに従い階級の区切りを示した範囲も下方に移り空白欄が 入ってくるのも問題です。結局は最初の２つの区切りが示されていればいいら しいと気づきました。そこで、H2 に=FREQUENCY(C$2:C$31,E2:E3)と書き込ん でドラッグすれば正しい答えが得られます。 FREQUENCY を用いて度数分布を作るには、関数の性質をよく知っていなけ ればなりません。整数データなら「70 点以上 80 点未満」と「69.9 点を超え 79.9 以下」は同じ結果を与えます。工夫をすれば「70 点以上 80 点未満」のような下 限境界に等号が入った形にも対応は可能です。しかし、私としてはあまりにブ ラックボックス化したFREQUENCY 関数より COUNTIF で条件を理解しながら 計算する方がいいような気がします。
Excel の利用法を紹介するだけなら、うまくいかない部分は書く必要がありま
せん。しかしいろいろ試してみるうちにExcel の使い方も理解してゆきます。そ
こがまさに、Excel de Asobo の極意です。そう思って、あえて試行錯誤の様子を
紹介しました。
