Excel,Rによる分散の計算

→ 携帯用は別頁

◇この教材は，高校生が表計算ソフトなどを使って，数学Ⅰの「データ分析」レベルの内容を扱うときの「演習の手引き」として書いたものです．（筆者自身用の備忘録でもある）
◇●1～●4のソフトについて操作方法を解説していますが，全部読む必要はありません．自分のパソコンで使えるソフトを選んで，読んでください．

　使い方を調べるソフトは，次の４種類

●1　「Microsoft Excel 2021」などのインストール型
●2　無料で使える「Excel for the web」
●3　無料で使える「Google スプレッドシート」
●4　無料でインストールできる統計用ソフト「R」

　各々のソフトの導入方法はこのページ参照

1. 合計（総和）

-この項目の要約-
	●1～●3 表計算ソフト =SUM(セル範囲)	●4 「R」 sum(ベクトル)
全部数値のとき	合計が得られる	合計が得られる
空欄があるとき	空欄以外の合計になる	NAが返される
文字データがあるとき	空欄以外の合計になる	エラーになる

※表計算ソフトの関数を英大文字で書いていますが，結果的にソフトの表示がそうなるからで，各自が書き込むのは小文字でも同じです．このページの，以下の部分も同様です．

-表1-
	A	B	C	D
1	整理番号	項目1	項目2	項目3
2	No.1	10	10	10
3	No.2	10		10
4	No.3	10	10	⑩
5	No.4	10	10	10
6	No.5	10	10	10
7	合計	50	40	40
8	人数	5	4	4
9	平均値	10	10	10

　右の表1のデータでの，●1～●3での計算方法
　ただし，背景色が桃色のセルC3は空白，D4は入力ミスで文字データ⑩が入力されているものとする．
　合計を求めるには，関数 SUM(範囲) を使う．
セルB7に =SUM(B2:B6)
セルC7に =SUM(C2:C6)
･･･
（B7に書き込んで，右にドラッグすればよい）

【結果】●1～●3共通
1)　項目1のように「全部が数値データ」である場合，数値の合計が表示される．
2)　項目2のように「空白セルがあり，他は全部数値データ」である場合，数値の部分だけの合計が表示される．（空白は合計には入らないが，他の数値データの合計は有効となる）
3)　項目3のように「文字データがあり，他は全部数値データ」である場合，数値の部分だけの合計が表示される．（文字データは合計には入らないが，他の数値データの合計は有効となる）

●4での計算方法

　　　　-表2-
d1<-c(10,10,10,10,10)
d2<-c(10,NA,10,10,10)
d3<-c(10,10,'⑩',10,10)

　右のように「R」のコマンドプロンプトから，３つのベクトルd1, d2, d3が入力されているものとする．なお，「R」ではNA（Not Available）で欠損値を，NaN（Not a Number）で非数値(0/0 など）を表す．

「R」で合計を求めるには，sum(ベクトル名)を使う．
　次の結果が得られる．

sum(d1)
　[1] 50
sum(d2)
　[1] NA
sum(d3)
　 sum(d3)でエラー:引数 'type' (character)が不正です

【結果】
1)　d1「全部が数値データ」である場合，数値の合計が表示される．
2)　d2「空白セルがあり，他は全部数値データ」である場合，NAが表示される．（合計の計算は全部が無効となる）
3)　d3「文字データがあり，他は全部数値データ」である場合，エラーが返される．（合計の計算は全部が無効となる）

　なお，欠損値（NA）が含まれる場合は，その項目を取り除く(remove)パラメータとして， na.rmを指定することができる．この場合，欠損値（NA）以外の合計が得られる．

d2<-c(10,NA,10,10,10)
sum(d2, na.rm=TRUE)
　[1] 40

2. 個数

-この項目の要約-
	●1～●3 表計算ソフト =COUNT(セル範囲)	●4 「R」 length(ベクトル)
全部数値のとき	データ個数が得られる	データ個数が得られる
空欄があるとき（Rでは欠損値）	空欄以外の個数（数値の個数）になる	欠損値を含む個数（ベクトルの要素数）が返される
文字データがあるとき	文字欄以外の個数（数値の個数）になる	文字欄を含む個数（ベクトルの要素数）が返される

-表1（再掲）-
	A	B	C	D
1	整理番号	項目1	項目2	項目3
2	No.1	10	10	10
3	No.2	10		10
4	No.3	10	10	⑩
5	No.4	10	10	10
6	No.5	10	10	10
7	合計	50	40	40
8	人数	5	4	4
9	平均値	10	10	10

　●1～●3での計算方法
　「数値データの個数」を求めるには，
関数 COUNT(範囲) を使う．

=COUNTA()
は空白以外のセルの個数
=COUNTBLANK()
は空白セルの個数

セルB8に =COUNT(B2:B6)
セルC8に =COUNT(C2:C6)
･･･
（B8に書き込んで，右にドラッグすればよい）

【結果】●1～●3共通
1)　項目1のように「全部が数値データ」である場合，数値のセルの個数が表示される．
2)　項目2のように「空白セルがあり，他は全部数値データ」である場合，数値のセルの個数だけが表示される．
3)　項目3のように「文字データがあり，他は全部数値データ」である場合，数値のセルの個数だけが表示される．

●4での計算方法

　　　　-表2（再掲）-
d1<-c(10,10,10,10,10)
d2<-c(10,NA,10,10,10)
d3<-c(10,10,'⑩',10,10)

　右のように「R」のコマンドプロンプトから，３つのベクトルd1, d2, d3が入力されているものとする．

「R」でデータの個数（ベクトルの要素数）を求めるには，length(ベクトル名)を使う．
　次の結果が得られる．

length(d1)
　[1] 5
length(d2)
　[1] 5
length(d2)
　[1] 5

【結果】
d1, d2, d3とも同じで，空白，文字データも含めた要素数が返される

3. 平均値

-この項目の要約-
	●1～●3 表計算ソフト =AVERAGE(セル範囲)	●4 「R」 mean(ベクトル)
全部数値のとき	平均値が得られる	平均値が得られる
空欄があるとき（Rでは欠損値）	空欄以外の平均値（数値の平均）になる	NA(Not Available) が返される
文字データがあるとき	文字欄以外の平均値（数値の平均）になる	NA(Not Available) が返される

-表1（三掲）-
	A	B	C	D
1	整理番号	項目1	項目2	項目3
2	No.1	10	10	10
3	No.2	10		10
4	No.3	10	10	⑩
5	No.4	10	10	10
6	No.5	10	10	10
7	合計	50	40	40
8	人数	5	4	4
9	平均値	10	10	10

　●1～●3での計算方法
　「平均値」を求めるには，
関数 =AVERAGE(範囲) を使う．
セルB9に =AVERAGE(B2:B6)
セルC9に =AVERAGE(C2:C6)
･･･
（B9に書き込んで，右にドラッグすればよい）

【結果】●1～●3共通
1)　項目1のように「全部が数値データ」である場合，数値のセルの平均値が表示される．
2)　項目2のように「空白セルがあり，他は全部数値データ」である場合，「数値のセルの合計」を「数値のセルの個数」で割ったものが表示される．（個数は4として数えている）
3)　項目3のように「文字データがあり，他は全部数値データ」である場合，「数値のセルの合計」を「数値のセルの個数」で割ったものが表示される．（個数は4として数えている）

●4での計算方法

　　　　-表2（三掲）-
d1<-c(10,10,10,10,10)
d2<-c(10,NA,10,10,10)
d3<-c(10,10,'⑩',10,10)

　右のように「R」のコマンドプロンプトから，３つのベクトルd1, d2, d3が入力されているものとする．

「R」で平均値を求めるには，mean(ベクトル名)を使う．
　次の結果が得られる．

mean(d1)
　[1] 10
mean(d2)
　[1] NA
mean(d3)
　[1] NA
　 警告メッセージ

【結果】
d1は平均値が返されるが, d2, d3は欠損値NA（Not Available）が返される．

4. 分散，標準偏差

この項目の要約

(1)　組込み関数を使う場合


全部数値のとき	●1～●3 表計算ソフト	●4 「R」
分散	=VAR.P(セルの範囲)	var(ベクトル)*(n−1)/n
標準偏差	=STDEV.P(セルの範囲) =SQRT(VAR.P(セル範囲)) でも同じ	sd(ベクトル)sqrt((n−1)/n) sqrt(var(data1)(n−1)/n)でも同じ

(2)　V(x)=x2−x2から求める場合

※この公式の証明は ⇒ このページ
※あれもこれも覚えなさい！などとは言っていません．１つできればＯＫですが，何かの事情で計算が合わないとき「別ルート検算」で確かめられると「助かる」ことがあるのです！使えるチャンネルが多いほど安心！（イザというときのために保険をかける）ということです．


全部数値のとき	●1～●3 表計算ソフト	●4 「R」
分散	=SUMSQ(…)/個数　　−(AVERAGE(…))^2	mean((…)^2) 　　−(mean(…))^2
標準偏差	=SQRT(上記の値)	sqrt(上記の値)

-表3-
	A	B	C
1	整理番号	項目1	偏差＾2
2	No.1	7	4
3	No.2	6	1
4	No.3	3	4
5	No.4	4	1
6	No.5	5	0
7	合計	25	10
8	人数	5
9	平均値	5
10	分散	2	2

(1)

　右の表3について，No.1～No.5の5個のデータの分散を求めるには，

　●1～●3での計算方法
分散を求めるには，
関数 =VAR.P(範囲) を使う．
セルB10に =VAR.P(B2:B6)
と書き込む．

よく似た関数
=VAR(), =VARP(), =VARA(), =VARPA()は旧バージョンのExcelとの互換性を維持するためのもの．新しいExcelでは使わなくてもよい
=VAR.S() は，与えられたものを標本と見なした場合の母集団の分散を求めるもの
=VAR.P() が記述統計用の関数 ⇔ 高校まではこれを使う
=VAR.S() は推測統計用の関数

【結果】●1～●3共通
No.1～の値をx1, x2, ･･･xnとするとき
$V(x)=\frac{1}{n}\{(x_1-\bar{x})^2+(x_2-\bar{x})^2+\cdots+(x_n-\bar{x})^2\}$
に対応する値が表示されている．

　なお，推測統計用の関数 VAR.S()は
$V(x)=\frac{1}{n-1}\{(x_1-\bar{x})^2+(x_2-\bar{x})^2+\cdots+(x_n-\bar{x})^2\}$
を表し，記述統計で計算した分散よりも少し大きい．
　次の関係がある
$\rm{VAR.S(\cdot\cdot\cdot)=\frac{n}{n-1}VAR.P(\cdot\cdot\cdot)}$
$\rm{VAR.P(\cdot\cdot\cdot)=\frac{n-1}{n}VAR.S(\cdot\cdot\cdot)}$

　●1～●3で，定義に従って，表を組み立てて計算する方法
　この計算を行うためには，データの「合計」「個数」は必ずしも必要ないが「平均値」を先に求めておかなければならない．（表3のようにB9が平均値だとする）
(1)　C2の欄に，=(B2-$B$9)^2と書き込む
(2)　C2のセルの右下の■をポイントすると＋というマークが出るので，これをC6までドラッグする．
(3)　C7のセルをポイントして，「ΣオートSUM」
(4)　C10のセルに =C7/B9と書き込む

●4での計算方法

　　　　-表4-
data1<-c(7,6,3,4,5)

　右のように「R」のコマンドプロンプトから，ベクトルdata1が入力されているものとする．

「R」で平均値を求めるには，var(ベクトル名)を使う．
　ただし，「R」では推測統計がよく使われるので，準備されている関数は推測統計用のものが多い．ここでも，高校で習う分散（記述統計の分散）に直すためには
　　=var(…)*(n−1)/n

　　　　-表4（続き）-
data1<-c(7,6,3,4,5)
var(data1)*4/5

で変換する．
次の結果が得られる．

[1] 2

　●1～●3で標準偏差を求めるには，
関数 =STDEV.P(範囲) を使う．
　標準偏差は，分散の（正の）平方根であるから，=SQRT(VAR.P(範囲)) としても同じになる．

●4で標準偏差を求めるには，関数 =sd(ベクトル) を使う．
　ただし，「R」で準備されている関数は推測統計用のものだから，ここでも，高校で習う分散（記述統計の分散）に直すためには
　　=sd(…)*sqrt((n−1)/n)
とする．=sqrt(var(範囲)*(n−1)/n) としても同じになる．

名前	国語	数学	英語
平清盛	40	67	67
源頼朝	59	92	93
武田信玄	97	98	87
上杉謙信	98	75	92
織田信長	57	95	85
豊臣秀吉	79	90	90
徳川家康	89	86	69
足利尊氏	73	62	90
毛利元就	53	52	63
伊達政宗	85	70	59

【簡単チェック問題4.1】
　右の表のデータ（架空データです!当然!）について，表計算ソフト●1～●3または「R」を用いて，分散，標準偏差を求めてください．
（結果は四捨五入して，小数点以下第2位まで求めてください）

※各自で問題を解くときに，この問題を手書きで書き写す必要はない．画面上で，左上端の「名前」から右下端の「59」までを，「ドラッグ」「右クリック」「コピー」．次に，Excel（onlineでもGoogleスプレッドシートでも同じ）のワークシート上で「貼り付け」すればよい．以下の問題も同様

解答を見る

●1～●3では，=VAR.P(セルの範囲), =STDEV.P(セルの範囲) を使う．
●4では，=var(ベクトル)*(9/10)，=sqrt(var(ベクトル)*(9/10))

	国語	数学	英語
分散	357.80	221.41	160.45
標準偏差	18.92	14.88	12.67

･･･（答）
→隠す←

-表3（再掲）-
	A	B	C
1	整理番号	項目1	偏差＾2
2	No.1	7	4
3	No.2	6	1
4	No.3	3	4
5	No.4	4	1
6	No.5	5	0
7	合計	25	10
8	人数	5
9	平均値	5
10	分散	2	2

(2)
右の表3について，No.1～No.5の5個のデータの分散を求めるには，

　●1～●3で，V(x)=x2−x2から分散を求めるには，
=SUMSQ(B2:B6)/B8
　　−(AVERAGE(B2:B6))^2
標準偏差は，
=SQRT(そのセル)

●4で分散を求めるには，

data1<-c(7,6,3,4,5)
V1=mean(data1^2)-(mean(data1))^2

標準偏差は，上記に続けて

sqrt(V1)
　[1] 1.414214

名前	理科	社会
平清盛	54	71
源頼朝	85	37
武田信玄	76	35
上杉謙信	96	58
織田信長	99	86
豊臣秀吉	81	31
徳川家康	60	43
足利尊氏	99	55
毛利元就	57	31
伊達政宗	87	35

【簡単チェック問題4.2】
　右の表のデータ（架空データです!当然!）について，表計算ソフト●1～●3または「R」により， 2乗平均−（平均）²の公式を使って，分散を求めてください．
（結果は四捨五入して，小数点以下第2位まで求めてください）

解答を見る

●1～●3では，=SUMSQ(セルの範囲)/COUNT()を2乗平均とし，=AVERAGE(セルの範囲) を平均として，2乗平均−（平均）²を求める．
●4では，

ri<-c(54,85,76,96,99,81,60,99,57,87)
sha<-c(71,37,35,58,86,31,43,55,31,35)
mean(ri^2)-(mean(ri))^2
　[1] 267.04
mean(sha^2)-(mean(sha))^2
　[1] 318.36

などとする．
理科 267.04，社会 318.36･･･（答）
→隠す←

5. 度数分布表から分散，標準偏差を求める

-この項目の要約-
全部数値のとき	●1～●3 表計算ソフト	●4 「R」
分散	=SUMPRODUCT(x,x,f)/N 　　−平均^2	sum(d1^2f1)/N1 　　−(sum(d1f1)/N1)^2
標準偏差	=SQRT(上記の値)	=sqrt(上記の値)

-表5-
	A	B	C	D	E	F
1	以上	未満	階級値 xk	度数 fk
2	5	10	7.5	3
3	10	15	12.5	4
4	15	20	17.5	7
5	20	25	22.5	5
6	25	30	27.5	1
7			合計	20
8			平均	16.75

(A)　データが度数分布表で与えられているときは，各々の階級の真ん中の値，すなわち階級値xkに各々の度数fkで示される個数のデータがあると見なす．
　右の表のような度数分布表が与えられたとき，分散V(x)は

$V(x)=\frac{(x_1-\bar{x})^2f_1+ (x_2-\bar{x})^2f_2+ (x_3-\bar{x})^2f_3+\cdots +(x_n-\bar{x})^2f_n}{f_1+ f_2+ f_3+\cdots+ f_n}$

で求められる．ただし， $N=f_1+ f_2+ f_3+\cdots+ f_n$ はデータ総数である．
　この公式に沿って，偏差2乗和×度数を作り，表を組み立てていくのが1つの方法である．（これは前のページに書いたので，ここでは省略する）

(B)　V(x)=x2−x2から求める場合
　●1～●3で，V(x)=x2−x2から分散を求めるには，
　はじめに，D7に合計（個数）=SUM(D2:D6)，D8に平均値 =SUMPRODUCT(C2:C6,D2:D6)/D7を求めておく．

　ここで，表計算ソフトの関数 SUMPRODUCT() は，数学の内積よりも自由で，3個以上のベクトルの積も可能であることに注意する．
　例えば，=SUMPRODUCT(C2:C6, C2:C6, D2:D6) とすれば，Σx2k fk が求まる．

(1)　E7をポイントし，
=SUMPRODUCT(C2:C6, C2:C6, D2:D6) と書き込む
(2)　E8をポイントし，=E7/D7 と書き込む
(3)　F7をポイントし，
=SUMPRODUCT(C2:C6, D2:D6) と書き込む
(4)　F8をポイントし，=F7/D7 と書き込む
(5)　F9をポイントし，=F8-D8^2 と書き込む

E8の計算で =AVERAGE(C2:C6)を使うことはできない．また，E2～E6に =C2^2～=C6^2などを準備しておいても，=AVERAGE(E2:E6)を使うことはできない．これらの関数では，データの個数を5個としてしまうので，合わなくなる．

標準偏差は，
　=SQRT(F9)

●4で分散を求めるには，

d1<-c(7.5,12.5,17.5,22.5,27.5)
f1<-c(3,4,7,5,1)
N1=sum(f1)
V1=sum(d1^2*f1)/N1-(sum(d1*f1)/N1)^2
　[1] 30.6875

なお，分散の計算で mean(d1^2*f1)-(mean(d1*f1))^2 を使うことはできない．この計算方法では，データの個数を5個としてしまうので，合わなくなる．

標準偏差は，上記に続けて

sqrt(V1)
　[1] 5.53963

	A	B	C	D
1			階級値	度数
2	以上	未満	xk	fk
3	30	35	32.5	1
4	35	40	37.5	3
5	40	45	42.5	5
6	45	50	47.5	7
7	50	55	52.5	6
8	55	60	57.5	5
9	60	65	62.5	2
10	65	70	67.5	1

【簡単チェック問題5.1】
　右の表のデータについて，表計算ソフト●1～●3または「R」を用いて，分散，標準偏差を求めてください．
（結果は四捨五入して，小数点以下第2位まで求めてください）

解答を見る

分散 67.67，標準偏差 8.23･･･（答）
→隠す←

	A	B	C	D
1			階級値	度数
2	以上	未満	xk	fk
3	150	155	152.5	1
4	155	160	157.5	1
5	160	165	162.5	2
6	165	170	167.5	3
7	170	175	172.5	5
8	175	180	177.5	3

【簡単チェック問題5.2】
　右の表のデータについて，表計算ソフト●1～●3または「R」を用いて，分散，標準偏差を求めてください．
（結果は四捨五入して，小数点以下第2位まで求めてください）

解答を見る

分散 51.56，標準偏差 7.18･･･（答）
→隠す←

...メニューに戻る