初心者もできるExcelから学ぶ統計学（2/2）

0. 対象となる読者

Ⅰ. 様々なデータを基本的な知識でExcelで分析してみたいと思っている方
Ⅱ. データ分析について勉強をしているが、実際にどのように活用していくのかわからない方
Ⅲ. その他Excelを用いて手を動かしながらデータ分析をしてみたい方
Ⅳ. 統計の知識を学びつつ、その概念をすぐ活用したいと思ってる方
Ⅴ. 資格「エクセル分析ベーシック（基礎レベル）」を取得したいと考えている方
Ⅵ. 前回の記事「初心者もできるExcelから学ぶ統計学（1/2）」を読んだ読者の方 前回の記事はこちら

---

1. はじめに

本記事では、2回に分けてExcelを用いて基本統計量を求める方法について解説します。
基本統計量とはデータの中心傾向や分散を表す指標のことで、データ分析の基礎となる概念です。
具体的には、「平均値」、「中央値」、「最頻値」、「分散」、「標準偏差」、「四分位数」 について説明します。
以下にように2つの記事で分けて説明させていただきたいと思います。

• 平均値、中央値、最頻値
• 分散、標準偏差、四分位数　← 今回の記事のパート

読者によっては知っている単語や概念も出てくると思うので、この統計量なんだっけ...と気になる部分だけ読んでいただいても構いません。

この記事で使用するExcelのバージョンは
Microsoft® Excel® for Microsoft 365 MSO (version 2406 ビルド 16.0.17726.20078) 64bit
ですが、2007年以降のExcelであれば基本的な操作は同じです。

2. 基本統計量の求め方

Ⅰ.分散 (Variance)

①分散の意味と求め方

分散とはなんでしょうか。「高校数学で聞いた覚えがあるぞ！」という方もいるでしょう。
分散は標準偏差と関連深いものであり、標準偏差と共に基本統計量として大事な役割を果たしています。
では改めて分散について復習してみましょう。

分散は一言で 「データのばらつきを表す指標」 です。英語では「Variance」で表記し、その略を使ってこれから分散を$Var(X)$もしくは$V(X)$、記号では ${\sigma}^2$ や $S^2$ という記号で表すことにします。ちなみに括弧の中の$X$は$X$というデータセットを表して、$Var(X)$や$V(X)$は$X=\{x_1,x_2,...,x_n\}$のデータセットに対する分散という意味です。

厳密に言えば分散の${\sigma}^2$や$S^2$のような記号や定義は本やテキストによって異なる場合がありますが、これからの記事では以下の定義で説明したいと思います。
母分散：${\sigma}^2$
標本/不偏分散：$S^2$
※母集団や標準集団については今後別の記事で説明することにして、本の記事では母分散を使うことにします。

では具体的に数式としてどのように表現するのでしょうか。

$$Var(X) = \dfrac{{\sum_{i=1}^n(x_i - {\mu}})^2}{n}$$

​ $x_i$：各データ値, $\mu$：平均値, $n$：データの個数

であり、平均値を$\mu$と表記するのは前回の記事で説明しました。
式をより分かりやすく3つの段階に分けて考えてみましょう。

1. 個々のデータのずれを計算する $:x - {\mu}$
   　→ 実はこれが 「偏差」 の定義です！
   偏差：それぞれのデータとデータ全体の平均値との差
2. 偏差を二乗する $:(x_i - {\mu})^2$
3. その合計をデータの個数で割ることで、平均値を計算する $:\dfrac{{\sum_{i=1}^n(x_i - {\mu}})^2}{n}$

ここであれ、2番目からなんかおかしいぞ？と感じた方がいるかもしれません。
「なんで普通に偏差の合計を求めておいて急に二乗したり、その平均を求めるんだよ」ってなりますよね。
ただの偏差の平均を求めたらダメなんでしょうか。
その理由はばらつきの性質にあります。

簡単な例で理解してみましょう。
ここに $\set{1,3,5}$ という三つのデータがあるとします。
このデータは$3$という平均を持っていて、$1$と$5$は平均$3$に対して$2$というばらつき、各々$-2, 2$という偏差を持っていますね。
では先程の偏差を二乗せず合計してみるとどうなるでしょうか。
$\sum_{i=1}^3(x_i - {\mu}) = 0$なので平均も$0$になりますよね。
あれれ？ばらつきは$2$で各々偏差が存在するのに$0$という値が得られました。
これじゃ全ての値にばらつきがなく、$\set{3,3,3}$のようになってしまいます。

それに対して分散はどうでしょうか。
$\sum_{i=1}^3(x_i - {\mu})^2 = 8$となり、分散は

$$\dfrac{{\sum_{i=1}^n(x_i - {\mu}})^2}{n} =\dfrac{8}{3}$$

二乗しただけなのになぜこのようになったのでしょうか。
その理由は 偏差は +,-への 「方向性」 を持っていて、偏差を二乗することで方向性をなくし、「大きさ」 に変換したからです。
簡単に理系的に考えるとベクトルからスカラーになったとも言えますね。
だから$1,5$に対する偏差を合わせると0になってしまい、正確なばらつきが計算できなかったのです。

まとめると以上のことから考えると分散は「平均値を基準に値が大きいほどデータのばらつきが大きく、小さいほどばらつきが小さいことを示す指標」という性質を持っていることが分かります。

余談ですが、ここで次のような質問もできますね。
1.ばらつきは$2$だったのに何で分散は$2$じゃないの？同じく2になる必要があるんじゃない？
2.大きさが必要なら普通に偏差に絶対値を取ればいいんじゃないの？二乗する理由は？

この質問に関しては今回の記事の範囲を超えるのでまた別の記事で詳しく説明したいと思います。

では先ず、複雑な式ではなくExcelを用いて分散を理解してみましょう。

②Excelでの計算方法

このようなデータがあるとして、前回の記事で学んだものを活かして基本統計量を求めてみましょう。

• 表2.1 あるYoutuberの1年間の購読者数増加分

月	購読者数（人）
1月	800
2月	1200
3月	1600
4月	500
5月	900
6月	700
7月	1000
8月	600
9月	1400
10月	1300
11月	1500
12月	800
合計	？？
平均	？？
中央値	？？
最頻値	？？
分散	？？
標準偏差	？？

この表をコピーしてExcelのA1セルにペーストすることでデータを入力せずすぐ使えます！

Excelにデータを入力し、様々な関数を用いて基本統計量を求める

前回の記事で学んだSUM関数, AVERAGE関数, MEDIAN関数, MODE関数を用いて簡単に求められました！
上の写真の値と比較して自分が求めた基本統計量が正確に求められたのか確認してくださいね。

では次は分散を求めてみましょう。

VAR.P関数を使い、分散を求める
セルに=VAR.P()を入力し、()の中に合計したいデータを入れます。今回の場合はB2:B13になりますね。

関数を入力する際に自動完成機能によりVAR.Sが表示される場合があります。
しかし今回の記事で扱うのはVAR.Pであり、VAR.Sではないことに注意。

VAR.Pにより分散が求められました

そうすると分散は「$123541.6667$」もしくは上のように「$123542$」のように求められるのでチェックしてください。
Excelのバージョンによって小数点以下が違う場合がありますが、$123541.6...$か$123542$のような値であれば問題ないです。

かなり大きい値でどういうことかまだわからないですが、ここで一旦説明は置いといて標準偏差を求めた後二つの値を比較しながら説明したいと思います。

---

Ⅱ.標準偏差 (Standard Deviation[SD])

①標準偏差の意味と求め方

では分散という概念を学ぶときに必ずペアとして一緒に学ぶことが多い（その逆とも言えますが）標準偏差について理解してみましょう。
先程分散を学ぶ際にも関連深いという話がありましたが、なぜ分散と一緒に学ぶことが多く、いつもペアとして扱うでしょうか。
その理由は 「分散は標準偏差の二乗」 であり、言い方を変えれば 「標準偏差は分散の平方根」 であるからです。

数式で表すと、先程の分散${\sigma}^2$を用いて（先程の母分散を使います）

$$SD = \sigma = \sqrt{\dfrac{{\sum_{i=1}^n(x_i - {\mu}})^2}{n}}$$

$x_i$：各データ値, $\mu$：平均値, $n$：データの個数

ではなんで標準偏差って必要でしょうか。
「そもそもばらつきの大きさは分散で分かったし、もう十分じゃないか」と考えたことはありませんか？
分散の平方根であるだけの標準偏差のメリットがわからなくておかしくないですよね。
（実は統計学では特定の数学的利点や理論的背景によって標準偏差より分散を求めることが多いですが）

にもかかわらず標準偏差が必要な理由は以下の2つです。
1.解釈のしやすさ
2.比較のしやすさ

先ず1.解釈のしやすさについてです。
実は分散の中で我々が気付かなかったところがあります。
それは 「単位」も二乗になる ことです。
偏差を二乗した分、単位も二乗になってしまい $cm$ のような単位が $\def\sqr#1{#1^2} \sqr{cm}$ になってしまったのです。
何かを計算したり解釈する際には単位を合わせるのが一般的であることを理由にビジネス現場では分散より標準偏差を使うことが多いです。

次の2.比較のしやすさについてです。
分散って偏差を二乗しちゃう分、値が大きくなりがちです。
先の例の場合はデータの数が少なく、値が小さくて違いがないように見えますが、データの数や値が大きくなるにつれ分散が大きくなります。
このようなことから分散ではなく標準偏差としてばらつきを比較することもあります。

ではExcelを用いて標準偏差を求めてみましょう。

②Excelでの計算方法

表に関しては先程の表2.1を再利用しましょう。

STDEV.P関数を使い、標準偏差を求める

分散と同じくセルに=STDEV.P()を入力し、()の中に合計したいデータを入れます。
そうすると標準偏差は「$351.4849451...$」もしくは「$351$」のように表示されます。

Excelを使わず直接分散の平方根を求めてみると

$$\sqrt{123541.6}=351.484...$$

となり標準偏差は分散の平方根ということをもう一度確かめました。

分散を求めて、ルートを付ける必要なく、STDEV.Pという関数で標準偏差だけを求めることもできるのです。

Ⅲ.四分位数 (Quartiles/Quartile Points)

①四分位数の意味と求め方

本記事で紹介する最後の基本統計量、四分位数 (Quartiles) とはなんでしょうか。
これから出てくる$Q_1$, $Q_2$, $Q_3$のような四分位数の「$Q$」はQuartileの略で、四分位数はデータを4つの等しい部分に分割するための数値です。

言葉だけではよく分からないので図と共に例を挙げてもう少し詳しくその意味を考えてみましょう。

第1四分位数 ($Q_1$)：データの下位25%の位置。全データの25%がこの値以下にある。
第2四分位数 ($Q_2$)：データの中央値。全データの50%がこの値以下にある。（中央値と同じ）
第3四分位数 ($Q_3$)：データの上位25%の位置。全データの75%がこの値以下にある。

以下の2つの単純なデータセットがあると仮定してみましょう。

1.データの数が奇数の場合
$X=\set{3,2,11,4,6,5,7,8,9,10,1}$
データセットを小さい順に並べ、その中で特定の割合のデータが存在する位置を表すことでデータの分布や集中具合を簡単に理解できるので小さい順に並び替えると

$$X=\set{1,2,3,4,5,6,7,8,9,10,11}$$

どうすれば良いかわからないけど、先ず前回の記事で学んだ中央値は求められる気がしますね。

中央値とはデータを小さい順に並べた時に真ん中にあるデータのことなので$6$ですね。
ここで少し考えてみると中央値って全データの$50\%$がこの値の下にあるとも言えますよね。（もちろん$50%$が上にあるとも言えますが）
なので先程の説明や図の定義によると、中央値=第2四分位数（Q2） になるのです。
第1四分位数（$Q_1$）と第3四分位数（$Q_3$）に関してはどうでしょうか。
難しく考えることなく、$Q_1$はデータの下位$25\%$（上位$75\%$） ということで$3$となり、
$Q_3$はデータの上位$25\%$（下位$75\%$） ということで$9$になります。

2.データの数が偶数の場合
$Y=\set{11,6,3,4,2,9,7,12,5,10,1,8}$
このデータセットも先のように小さい順に並び替えると

$$Y=\set{1,2,3,4,5,6,7,8,9,10,11,12}$$

この場合は6と7番目の間である$6.5$が中央値になります。
第1四分位数（$Q_1$）と第3四分位数（$Q_3$）に関してはどうでしょうか。
第1四分位数（$Q_1$）は第2四分位数（$Q_2$）の半分である下位$25/%$ということなのでデータの下位$50/%$である$\set{1,2,3,4,5,6}$からの中央値、$3.5$なります。
そうすると、第3四分位数も（$Q_3$）は第1四分位数（$Q_1$）のように$\set{7,8,9,10,11,12}$の中央値、$9.5$になりますね。

以上のことをまとめて式で表現すると
1.第1四分位数 ($Q_1$)：$Q_1$は$Q_2$より小さいデータ群の中央値
2.第2四分位数 ($Q_2$)：データ群の中央値
3.第3四分位数 ($Q_3$)：$Q_3$は$Q_2$より大きいデータ群の中央値
と簡単にまとめることができます。

中央値の性質から四分位数も データ間の位置にある場合は、その位置にある2つのデータの平均を取ることを忘れないでください。

では最後に四分位数はどのような統計学的意義を持っていて、どうして我々に必要でしょうか。
実は四分位数はデータのばらつきを理解するのに役立ちます。
四分位範囲：$Q_3$-$Q_1$
四分位偏差：$\dfrac{(Q_3-Q_1)}{2}$
例えば、$Q_1$と$Q_3$の差を四分位範囲 (InterQuartile Range, IQR) と呼び、この値が大きければデータのばらつきが大きいことを意味します。
また、IQRを使って外れ値（極端に離れた値）を検出することもできます。
四分位偏差については四分位範囲の半分で四分位範囲と同じく、中心付近のデータがどのくらい散らばっているかの目安として使えるのです。

②Excelでの計算方法

• 表2.3 あるクラスの数学テスト結果

生徒の番号	数学テストの点数
1	38
2	98
3	72
4	46
5	49
6	81
7	20
8	84
9	97
10	54
11	67
12	35
13	80
14	83
15	68
16	45
最小値	？？
Q1：第1四分位数	？？
Q2：第2四分位数（中央値）	？？
Q3：第3四分位数	？？
最大値	？？

今までのようにこの表をコピーしてExcelのA1セルにペーストすることでデータを入力せずすぐ使えます！

今回はQUARTILE.INCという関数を用いて四分位数を求めてみましょう

今回のQUARTILE.INC関数は他の関数と違い、()の中に範囲だけを入力しても値がでません。
上の画像の右上にある戻り値を指定する必要があります。
QUARTILE.INC関数で戻り値とは
最小値（$Q_0$）、$Q_1$、$Q_2（中央値）$、$Q_3$そして最大値（$Q_4$）を指定する時に必要な値です。
画像の右上を参考にすると0は最小値、1は第1四分位数、2は中位数（中央値）、3は第3四分位数、4は最大値になることが分かります。

では直接求めてみましょう。

今回の場合四分位数はQUARTILE.INC関数で求めています。
QUARTILE.EXCやQUARTILEではないことに注意しましょう。
QUARTILE.EXCは、四分位数を計算する際にデータセットの最小値と最大値を除外して計算する関数です。
つまり、四分位数の計算において最小値と最大値が計算に直接含まれません。

QUARTILE.INCを用いて戻り値を入力して四分位数を求める

上の画像のように値が求められたら正解です！

これで全てが終わりました！めでたしめでたし！
.
.
.
なのでしょうか？

直接手で検証してみましょう。
先程の表を小さい順にしてみると
$\set{20,35,38,45,46,49,54,67,68,72,80,81,83,84,97,98}$ですね。
先ずは中央値から求めてみましょう。

データの数が偶数の16個なので
8番目$\set{67}$と9番目$\set{68}$の平均である$67.5$になりますね。

では第1四分位数はどうでしょうか。
前のデータの半分$\set{20,35,38,45,46,49,54,67}$の真ん中にある4番目 $\set{45}$と5番目
$\set{46}$の平均である$45.5$です。
第3四分位数も同様で後のデータの半分$\set{68,72,80,81,83,84,97,98}$の真ん中にある$\set{81}$と$\set{83}$の平均である$82$になります。

でも少し変ですよね。
Excelで求めた$Q_1$は45.75であり、$Q_3$は$81.5$でした。
一体どういうことでしょうか。

その理由は四分位数の計算の仕方が違うからです。

我々は今まで四分位数を求める際にデータの個数に注目し、データの個数が偶数である場合にはその平均を取って計算しました。
このような計算の仕方を 「ヒンジ」 と言います。
このヒンジは手計算でも簡単に計算できる簡易的な定義ですね。

ではExcelで使われていた 「パーセンタイル」 という計算の仕方はどうでしょうか。
実はこの 「パーセンタイル」の四分位数が、一般的な四分位数の定義 になります。
パーセンタイルはデータの幅を厳密に4等分して、内分点を求める要領で四分位数を求めていきます。
先の例で考えるとデータの数が16個でしたよね。これはデータの幅は15ということと同じですね。

パーセンタイルの求め方を視覚化した概念図

ということでデータの幅である15を4で分けます。
そうすると$3.75$という値が得られますね。それが正確な$25%$パーセンタイルになります。
最初のデータが$1$番だとするとそれに$3.75$という幅を持っているデータが第1四分位数になります。

つまり4.75番目のデータが第1四分位数になり、8.5番目のデータが第2四分位数（中央値）、そして12.25番目のデータが第3四分位数になります。
$4.75$番目とは先のデータで考えると4番目のデータ$45$と5番目のデータ$46$の間、1:3の割合で存在するところで$45.75$になります。
第2四分位数や第3四分位数も同様に計算すると$67.5$、$81.5$のように計算できてExcelの計算結果と一致します。

特に四分位数の場合、他の記事やテキストではざっくり説明したりスキップするところが多かったのでヒンジやパーセンタイルの違いが分かるようにヒンジの方を先に学び、Excelでパーセンタイルを学ぶ流れに説明してみました。
かなり混乱しやすいところだと思いますが、パーセンタイル計算方法であれば問題ないです！

3.まとめ

0.偏差

• 偏差は各データから平均を引くことでデータのずれを表す

1.母集団の分散を求めたい場合ははVAR.P関数

• =VAR.P(データ)
• 分散は標準偏差の二乗
• ばらつきを表す指標

2.母集団の標準偏差を求めたい場合はSTDEV.P関数

• =STDEV.P(データ)
• 標準偏差は分散の平方根
• 分散に比べて解釈や比較しやすい

3.四分位数を求めたい場合はQUARTILE.INC関数

• =QUARTILE.INC(データ,戻り値)
• $Q_0$：最小値
• $Q_1$：第1四分位数（下位25%）
• $Q_2$：第2四分位数（中央値）
• $Q_3$：第3四分位数（上位25%）
• $Q_4$：最大値
• ヒンジ：データセットの特定の位置にあるデータ値がそのまま四分位数になる
• パーセンタイル：データの分布全体に基づいて線形補間が行われるため、四分位数の位置が必ずしもデータの中間にあるとは限らない
• 四分位数を求める際には「パーセンタイル」の概念を用いて計算するのが一般的

いかがでしょうか。
前回の記事よりかなり難しくなっているところが多くありますが、読者の対象を高校生以上の大人の方々にしているので昔学んだ内容を復習しつつ、誤解しやすいポイントや疑問に思いやすいところを中心に執筆しました。
この記事を通じて少しは基本統計量を理解し「面白い」、「難しくない」イメージを持っていただけたらなと思います。
次の記事では前回の記事で軽く説明した「平均の種類や定義」について学んでみましょう。
その他の「分散に対する深い理解」や「母集団と標本」についてもお楽しみに。

お疲れさまでした。