→ 携帯用は別頁
◇この教材は,高校生が表計算ソフトを使って,数学Tの「データ分析」レベルの内容を扱うときの「演習の手引き」として書いたものです.(筆者自身用の備忘録でもある)
≪平均値,分散の求め方≫◇●1〜●3のソフトについて操作方法を解説していますが,全部読む必要はありません.自分のパソコンで使えるソフトを選んで,読んでください. 1. 平均値
変量xのデータがn個の値x1, x2, x3, ···, xnであるとき,
ワークシート上で,10人の生徒の教科ごとの得点データが,表1のように書かれているものとする.それらの合計は, 平均値は ただし,空欄は欠席のため得点が未記入であるとする.
●1 「Microsoft Excel 2021」(インストール型)
合計 (1) B12のセルをポイントし,「ホーム」「ΣオートSUM」の順に選択する. ⇒ B2からB11の範囲が選択表示されて,B12のセルに自動的に=SUM(B2:B11)という数式が記入される. ⇒ Enterキーを押す |
(2) B12のセルの右下の■をポイントすると+というマークが出るので,これをF12までドラッグする.
● 表計算ソフトの特徴として,一度は,次の性質を確かめておく方がよい.
受験者数@ =SUM(B2:B11) のような「式」は「相対参照」(その式が書かれているセルと参照されているセルとの相対的な位置関係に沿って)で計算される.だから,上記のように式のコピー&貼り付けが行われると,C12は =SUM(C2:C11)が,D12は =SUM(D2:D11),...になる. A =SUM()のようなワークシート関数で求められた値は,その式が参照しているセルの値が書き換えられたとき,再計算される.例えば,国語のNo.1〜No.10のいずれかの得点が書き換えられたとき,国語の合計も変わる.空欄の欠席分が埋められらとき,その得点も反映される. (これに対して,ピボットテーブルや分析ツールなどから出力された結果は,元のデータが書き換えられても,自動的には再計算されない). B 上記の表示において,例えば12行目の行見出しをポイントして行を挿入した場合(もしくは,A12〜F12までのセルを選択してから,「挿入」「セルの挿入」「下方向にシフト」), =SUM(B2:B11) の「式」は,B13のセルに移る.(B13が =SUM(B3:B12) になるのではない) 同様?にして,例えば8行目の行見出しをポイントして,もしくは,A8〜F8までのセルを選択してから,「削除」「シートの削除」をした場合(もしくは,A8〜F8までのセルを選択してから,「削除」「セルの削除」「上方向にシフト」),B12に書かれていた =SUM(B2:B11) の「式」は,B11に移されて,=SUM(B2:B10)に書き換わる. (3) B13のセルをポイントし,「数式」「関数の挿入」・・・と進んで「=COUNT()」を選択する・・・範囲をB2:B11に選ぶ.もしくは,画面上にある数式バーの左隣にあるfxという記号をクリックする.「=COUNT()」を選択する・・・範囲をB2:B11に選ぶ.もしくはB13のセルに直接 =COUNT(B2:B11) と書き込む. (4) B13の右下の■をポイントすると+というマークが出るので,これをF13までドラッグする.
関数COUNT()とCOUNTA()があって,COUNT()の方は「数値が入力されているセルの数」を数える.COUNTA()は,「数値のセル」も「文字のセル」も数える点が異なるが,いずれも空白セルはカウントしない点に注意.例えば,F13の受験者数は7になる.
平均値(5) B14の平均値は,ここまでに求めた SUM(B2:B11)をCOUNT(B2:B11)で割ったものになるが,これは =AVERAGE(B2:B11)に等しい. (6) B14の右下の■をポイントすると+というマークが出るので,これをF14までドラッグする. |
●2 無料で使える「Excel for the web」
⇒導入方法は前のページ●1とほぼ同様であるが,細部は異なる部分がある 合計 (1) B12のセルをポイントし,「ホーム」,画面上端にある「リボン」の右の方にある「Σ」の順に選択する. ⇒ B2からB11の範囲が選択表示されて,B12のセルに自動的に=SUM(B2:B11)という数式が記入される. ⇒ Enterキーを押す (2) (●1と全く同じ) B12のセルの右下の■をポイントすると+というマークが出るので,これをF12までドラッグする. 受験者数 (3) B13のセルをポイントし,「数式」「関数の挿入」「=COUNT() 次へ」を選択する.値1のところで,「右にある指の絵ではなく」値1の枠内の適当な個所をポイントして,マウスドラッグにより範囲をB2:B11に選び,「挿入」.もしくは,画面上にある数式バーの左隣にあるfxという記号をクリックする.「=COUNT()」を選択する.以下は同様.もしくはB13のセルに直接 =COUNT(B2:B11) と書き込む. (4) (●1と全く同じ) B13の右下の■をポイントすると+というマークが出るので,これをF13までドラッグする. 平均値 (5) (●1と全く同じ) B14の平均値は,ここまでに求めた SUM(B2:B11)をCOUNT(B2:B11)で割ったものになるが,これは =AVERAGE(B2:B11)に等しい. B14のセルをポイントし,「数式」「関数の挿入」「AVERAGE() 次へ」を選択する.値1のところで,「右にある指の絵ではなく」値1の枠内の適当な個所をポイントして,マウスドラッグにより範囲をB2:B11に選び,「挿入」.もしくは,画面上にある数式バーの左隣にあるfxという記号をクリックする.「AVERAGE()」を選択する.以下は同様.もしくはB14のセルに直接 =AVERAGE(B2:B11) と書き込む. (6) (●1と全く同じ) B14の右下の■をポイントすると+というマークが出るので,これをF14までドラッグする. |
●3 無料で使える「Google スプレッドシート」
⇒導入方法は前のページ●1とほぼ同様であるが,細部は異なる部分がある 合計 (1) B12のセルをポイントし,「メニュー」,右の方にある「Σ」を選択する. B12のセルに ?=SUM( )という数式が記入される. マウスでB2〜B11までの範囲をドラッグして,Enterキーを押す. (2) (●1とほぼ同じ) B12のセルの右下の●をポイントすると+というマークが出るので,これをF12までドラッグする. 受験者数 (3) B13のセルをポイントし,「挿入」「関数」「=COUNT() 」を選択する. B13のセルに =COUNT( )という数式が記入される. マウスでB2〜B11までの範囲をドラッグして,Enterキーを押す. (4) (●1とほぼ同じ) B13の右下の●をポイントすると+というマークが出るので,これをF13までドラッグする. 平均値 (5) (●1とほぼ同じ) B14の平均値は,ここまでに求めた SUM(B2:B11)をCOUNT(B2:B11)で割ったものになるが,これは =AVERAGE(B2:B11)に等しい. B14のセルをポイントし,「数式」「関数の挿入」「AVERAGE() 次へ」を選択する.値1のところで,「右にある指の絵ではなく」値1の枠内の適当な個所をポイントして,マウスドラッグにより範囲をB2:B11に選び,「挿入」.もしくは,画面上にある数式バーの左隣にあるfxという記号をクリックする.「AVERAGE()」を選択する.以下は同様.もしくはB14のセルに直接 =AVERAGE(B2:B11) と書き込む. (6) (●1とほぼ同じ) B14の右下の●をポイントすると+というマークが出るので,これをF14までドラッグする. (「Google スプレッドシート」での小数点以下の表示形式の選択:B14〜F14を反転表示して,「表示形式」「123数字」「数値」,はじめ小数点以下第2位まで表示されるので,.0←のアイコンを1回クリックすると小数点以下第1位までになる) |
【簡単チェック問題1】
●1 Microsoft Excel(インストール型),●2 Excel for the web,●3 Google スプレッドシート:共通
★印:難しい
(1) 合計の欄 B10 のセルに入る「式」を答えてください.(「値」ではない) (2) 人数の欄 B11 のセルに入る「式」と「値」を答えてください (3) 平均値の欄 B12 のセルに入る「式」を答えてください (4) 表の合計,人数,平均値の欄が正しく入力されているとき,行見出し9をポイントして,行の挿入を行うと,合計,人数,平均値のB列のセルの内容は,どんな式になるか (●2で「行の挿入」を行うには,行10のいずれかのセルをポイントして,その他のオプション(…)→挿入→行の挿入) (●3で「行の挿入」を行うには,行10のいずれかのセルをポイントして,挿入→行→上に1行挿入) (5) 次に,(4)で挿入してできた欄に,転入生の名前,身長,体重として,伊達政宗,170,65 を記入すると,人数のB欄の「値」はどうなるか (6) ★さらに.行見出し11をポイントして,名簿の最後(合計の前)に行の挿入を行うと,合計,人数,平均値のB列のセルの内容は,どんな式になるか (7) ★次に,(6)で挿入してできた欄に,転入生の名前,身長,体重として,毛利元就,178,63 を記入すると,合計,人数,平均値のB欄,C欄の「式」はどうなるか (8) さらに,織田信長が転校(転出)したとして,行見出し6の行を削除したとき,合計,人数,平均値のB列のセルの内容は,どんな式になるか |
(1)の解答を見る
=SUM(B2:B9)
(2)の解答を見る
=COUNT(B2:B9)
(3)の解答を見る7
=AVERAGE(B2:B9)
(4)の解答を見る(5)の解答を見る
8
(6)の解答を見る(7)の解答を見る
この例のように,繋がった範囲の下端(上端)にデータを追加すると,表計算ソフトによって,動作が違うので確かめる習慣にしておく方がよい
(8)の解答を見る●1 ⇒ =SUM(B2:B11) , =COUNT(B2:B11) , =AVERAGE(B2:B11) のようの合計,人数,平均値とも自動的に変わる ●2 ⇒ =SUM(B2:B10) , =COUNT(B2:B10) , =AVERAGE(B2:B10) のようの合計,人数,平均値とも変わらない(必要ならば各自で書き換えるべし) ●3 ⇒ =SUM(B2:B11) , =COUNT(B2:B10) , =AVERAGE(B2:B10) のようの合計だけ自動的に変わる.人数,平均値とも変わらない(必要ならば各自で書き換えるべし) |
2. 度数分布表で与えられたデータの平均値
右の表のような度数分布表が与えられたとき,合計S,データ総数N,平均値xは この値を,表計算ソフト●1〜●3で行うには,データが上記のようにC3〜D7の範囲に書かれているものとして,次のように入力すればよい. データ総数N ●1 「Microsoft Excel 2021」(インストール型)
(1) セルD8をマウスでポイントする.「ΣオートSUM」→ Enterキーを押すセルD8には,=SUM(D3:D7)という式が入り,画面上は20という値が表示される ●2 無料で使える「Excel for the web」
(1) セルD8をマウスでポイントする.「Σ」→ Enterキーを押すセルD8には,=SUM(D3:D7)という式が入り,画面上は20という値が表示される ●3 無料で使える「Google スプレッドシート」
(1) セルD8をマウスでポイントする.「挿入」「関数」「SUM」「引数の欄がブリンクする」「D3〜D7の範囲をドラッグ」→ Enterキーを押すセルD8には,=SUM(D3:D7)という式が入り,画面上は20という値が表示される |
合計Sの値 ●1 「Microsoft Excel 2021」(インストール型)
※直接求める方法もある(下記参考)が,次の(1)(2)のようにワンステップずつ組み立てて行く方が,点検しやすい.
(1) セルE3をマウスでポイントする.「=C3*D3と書き込む(半角文字で)」→ Enterキーを押す(2) セルE3の右下の■をポイントすると+というマークが出るので,これをE7までドラッグする. (3) セルE8をマウスでポイントする.「ΣオートSUM」→ Enterキーを押す (*) 参考 2つのベクトルとの内積(各々の積の和)を求める関数=SUMPRODUCT()を用いれば,2つの列ベクトルC3:C7とD3:D7の内積が求められる. セルE8に=SUMPRODUCT(C3:C7,D3:D7) と記入すると,上記の(1)〜(3)の結果が直接得られる. なお,関数=SUMPRODUCT()は,列ベクトルと列ベクトル, 行ベクトルと行ベクトルの内積を求めるときに使える. 行と列の積として行列計算で行うには,左側のベクトルの縦横を転置してから,行列の積を求める.おそらく,行列計算が好きな人だけが使うと思う. =MMULT(TRANSPOSE(C3:C7),D3:D7) ●2 無料で使える「Excel for the web」
●1とほぼ同じ●3 無料で使える「Google スプレッドシート」
●1とほぼ同じ平均値xの値 ●1〜●3とも同じ:セルE9に =E8/D8と記入する. |
●4 仮平均を用いた簡便計算法
表2において,度数分布表から平均値を求める計算の代わりに,表3のような仮変数を用いて”小さな整数の計算”で求める方法を簡便計算法という. この計算においては, 仮平均を x0=162.5 とおく 平均値を x=x0+5×u=162.5-1.25=161.25 とする
【簡便計算法1】
(解説)変数xの仮平均をx0,階級幅をc,仮変数uの平均値をuとすると @ xの階級値のうちで,真ん中付近にあって,度数の大きいものを,仮平均x0に選ぶと,計算が楽になる. (上記のように選ぶと,仮変数u=0に対して度数の大きい値を掛けることになり,積が0になって消えるからである) A xk=x0+c×uk ⇔ とおくと,仮変数ukの値は,u0=0の前後が,−2, −1, 0, 1, 2のような小さな整数になる.このような,小さな整数の計算は簡単になる. |
[簡便計算法の他の例1]
x=62.5+5×(-0.45)=62.5-2.25=60.25が求まる. [簡便計算法の他の例2]
x=42.5+5×(-0.25)=42.5-1.25=41.25が求まる.
【簡単チェック問題2】
解答を見る
x=27.5+5×(-0.33)=27.5-1.65=25.85が求まる. |
3. 分散データxの散らばり具合を表す分散V(x)は,次の式で定義される.ただし,xは平均値である.
このデータの分散を計算するには, ●1〜●3に共通 (1) 分散を求める関数を使う.ただし,似たものがあるので注意 VAR.S()は推測統計で使う,いわゆる不変分散で,ここでは,使わない
=VAR.P(B2:B11)
|
(2) 表を順に組み立てて,分散の定義に沿って計算する. B13をポイントして,=AVERAGE(B2:B11)と書き込む C2をポイントして,=(B2-$B$13)^2と書き込む C2の右下の■をポイントすると+というマークが出るので,これをC11までドラッグする. C13をポイントして,=AVERAGE(C2:C11)と書き込む (B列,C列とも12行の合計を埋めてから,個数10で割って13行のセルの値としても同じことになる)
右の表のデータについて,それぞれ分散を求めてください.(結果は四捨五入して,小数点以下第2位まで求めてください) |
4. 度数分布表で与えられたデータの分散
右の表のような度数分布表が与えられたとき,分散V(x)は 表7のデータを表計算ソフトで求める方法 ●1 Microsoft Excel(インストール型),●2 Excel for the web,●3 Google スプレッドシート:共通
(1) E列に 度数×偏差^2 を埋めていくために,まず偏差xk−xを求める必要がある.そのためには,はじめに、平均値xを求めておく.度数計を求める:D8をポイントして,●1 :「ΣオートSUM」→Enterキー,●2:「Σ」→Enterキー,●3:「Σ」「SUM」「D3:D7の範囲をドラッグ」→Enterキー 合計を求める:D9に =SUMPRODUCT(D3:D7,C3:C7) と書き込む 平均値を求める:D10に =D9/D8 と書き込む |
(2) E3のセルに =D3*(C3-$D$10)^2
と書き込む.赤字で書いた部分は絶対参照で,コピー&貼り付けしても変化しない.D10と書き込んでから,ファンクションキーのF4を押すと,絶対参照を表すドルマークが追加される.
表計算ソフトで式のコピー&貼り付けを効果的に行うためには,相対参照,絶対参照を使い分けできるようにしなければならない.上記のように書いておくと,E4,E5,...のセルに貼り付けたとき,
(3) E3の右下の■をポイントすると+というマークが出るので,これをC7までドラッグする.=D4*(C4-$D$10)^2
=D5*(C5-$D$10)^2
・・・となって,絶対参照となっている平均値は動かないところがポイント. (4) E8のセルをポイントして,●1 :「ΣオートSUM」→Enterキー,●2:「Σ」→Enterキー,●3:「Σ」「SUM」「E3:E7の範囲をドラッグ」→Enterキー (5) E10のセルに,=E8/D8 を記入する |
●4 仮平均を用いた簡便計算法
表xの形で与えられた度数分布表を表uの形に直して,仮平均を用いた簡便計算法により,小さな整数の四則計算で分散を求める方法がある.
【簡便計算法2】
(解説)変数xの階級幅をc,分散をV(x),仮変数uの分散をV(u)とすると 仮変数の分散V(u)は,その定義に従って, ・・・(3) でも求められるが,通常uが小数になるため,筆算向きではない.これに対して,(2)を使えば,小数計算は1回もしくは2回でできる.
証明は,このページの下端にある.
V(x),V(u)を求める実際の計算(2)でuの分散を求めて,(1)でc2倍すれば,xの分散になる. u2=1.5 u=−0.1 だから,(2)により V(u)=1.5−(−0.1)2=1.49 (1)により V(x)=25×1.49=37.25・・・(答) |
[簡便計算法による分散の計算:他の例1]
表8のデータに対して,仮平均を22.5として,仮変数uを用いた簡便計算法を表9のように行うと u2=1.1 u=−0.1 だから,(2)により V(u)=1.1−(−0.1)2=1.09 (1)により V(x)=25×1.09=27.25・・・(答) [簡便計算法による分散の計算:他の例2]
表8のデータに対して,仮平均を22.5として,仮変数uを用いた簡便計算法を表9のように行うと u2=2.0 u=−0.4 だから,(2)により V(u)=2.0−(−0.4)2=1.84 (1)により V(x)=25×1.84=46.00・・・(答) |
右の表のデータについて,表計算ソフト●1〜●3いずれかを用いて,分散を求めてください. また,仮平均を用いた簡便計算法によって,分散を求めてください.(結果は四捨五入して,小数点以下第2位まで求めてください)
●1〜●3 共通
D9に =SUMPRODUCT(C3:C7,D3:D7)/D8 と書き込み,xの平均値を求める ⇒ 11.25 E3に =D3*(C3-$D$9)^2 と書き込み,この式をE7までドラッグする E8に =SUM(E3:E7) と書き込み,E9に =E8/D9 と書き込む 以上により,分散29.69 が求まる
u2=1.25 u=−0.25 だから V(u)=1.25−(−0.25)2=1.1875 xに戻すと V(x)=25×1.1875=29.6875 ≒29.69・・・(答) |
右の表のデータについて,表計算ソフト●1〜●3いずれかを用いて,分散を求めてください. また,仮平均を用いた簡便計算法によって,分散を求めてください.(結果は四捨五入して,小数点以下第2位まで求めてください)
●1〜●3 共通
D11に =SUMPRODUCT(C3:C9,D3:D9)/D10 と書き込み,xの平均値を求める ⇒ 167.25 E3に =D3*(C3-$D$11)^2 と書き込み,この式をE9までドラッグする E10に =SUM(E3:E9) と書き込み,E11に =E10/D10 と書き込む 以上により,分散51.1875≒51.19 が求まる
u2=2.05 u=−0.05 だから V(u)=2.05−(−0.05)2=2.0475 xに戻すと V(x)=25×2.0475=51.1875 ≒51.19・・・(答) |
5.(参考)変数変換に伴う平均値,分散の変換公式
a, bを定数とし,x, uを変数とする.
(証明)xk=auk+b のとき,x, uの平均値x, uは次の関係を満たす x=au+b…(1) x, uの分散V(x), V(u)は次の関係を満たす V(x)=a2V(u)…(2) V(x)=x2−x2…(3) V(u)=u2−u2…(4)
(3)において,xの平均値xとは
x2の平均値x2とは (4)も同様 (1)← ・・・(証明終) |
(2)← ・・・(証明終) (3)← ・・・(証明終) (4)← (3)と同様にして証明できる |