MENU
保険鑑定人試験合格率アップ支援サイト | ミラーマスター合同会社の公式AIサイト

高等学校数学I/データの分析

共通参考資料

本項は高等学校数学Iのデータの分析の解説です。

目次

はじめに(データの分析とは)

中学校課程の資料の散らばりと代表値に続き、データの散布などの概念、また実際の処理がどのように行われるかを身近な事例やコンピュータの表計算ソフトを利用して学習します。大まかな内容は以下の通りです。

  • 第2章「資料の散らばり」では資料の分布具合を数値にする方法を学習します。
  • 第3章「相関関係」では2種類のデータにどんな関係があるかを学習します。
  • 第4章「仮設検定」では、数学Bで扱う仮設検定の基礎的な考え方を学習します。
  • 第5章「表計算(基礎編)」では表計算に関する基礎事項や用語を学習し、実際に表計算ソフトを用いて演習を行います。
  • 第6章「表計算(実践編)」では実際の表計算で知っていれば便利な項目を紹介しています。

資料の散らばりと代表値の内容は既習であるものとして解説を進めていきます。

この分野が基礎になる科目は数学Bの確率分布と統計的な推測があります。統計に加えて確率・数列・微積分の知識も必要となります。

表計算のセクション(第5章・第6章)は2011年度以前の課程「統計とコンピュータ」の範囲で現在の指導要領では学習しません。ここで取り上げた資料の数値確認や演習に活用するといいでしょう。予め各自使用している表計算ソフトの操作を知っておくとスムーズに学習が進められます。このページではMicrosoft Excelの書式に基づいています。実践編は余力があればとりかかってみて下さい。ただしここでは数学Bで学習する確率分布などの関数は扱っていません。演習を始める前に必ず(基礎編)の冒頭にある注意書きをお読み下さい。

この分野の演習問題は大学受験数学 統計とコンピューターをご覧下さい(旧課程のものですが内容に殆ど変更がないのでこのまま使用しています)。表計算演習は該当セクション内の実習と前述のページ演習問題2・3にて代えます。

以降、「資料の散らばりと代表値」でも用いた以下の資料を頻繁に使いますのでメモしておいたほうがよいでしょう。

出席番号12345678910
体重（kg）60.357.965.456.153.662.770.055.867.163.1
階級52.0以上～55.0未満55.0～58.058.0～61.061.0～64.064.0～67.067.0～70.070.0～73.0
階級値53.556.559.562.565.568.571.5
度数1312111

資料の散らばり

代表値が同じであってもその分布が代表値近くに密集していたりばらばらであったりと色々なことが考えられる。ここでは資料の散らばり具合の表す量について見てみよう。

範囲

資料が取る最大値から最小値を引いた値をその資料の分布の範囲（はんい）と言う。

資料1の範囲は70.0−53.6=16.4{\displaystyle 70.0-53.6=16.4}(kg)となる。

四分位数

データを大きさの順に並べた時、25%、50%、75%に当たる数値をその資料の四分位数と言う。特に下位から25%に当たる数値を第1四分位数、 下位から75%に当たる数値を第3四分位数と言われる。下位から50%に当たる数値は第2四分位数と言うこともできるが、中央値と同義である。四分位数の英訳「Quartile」の頭文字を取ってそれぞれQ1,Q2,Q3{\displaystyle Q_{1},Q_{2},Q_{3}}と表すこととする。

資料1の四分位数を求めてみよう。まずは資料を昇順に並びかえる。

順位10987654321
体重（kg）53.655.856.157.960.362.763.165.467.170.0

まずは中央値を求めてみる。中央値のセクションでも述べた通り、この資料の中央値は5番目と6番目の平均である61.5kgである。

第1四分位数はこの資料では順位が6番目～10番目の中央値とも読み取ることができる。言い換えると8番目の値となるので56.1kgとなる。

第3四分位数も同様に順位が1番目～5番目の中央値とできるので求める数値は3番目の値の65.4kgである。

四分位範囲・四分位偏差

第3四分値と第1四分値の差をその資料の四分位範囲、四分位範囲の半分のことをその資料の四分位偏差と言う。

資料1の四分位範囲はQ3−Q1=65.4−56.1=9.3(kg){\displaystyle Q_{3}-Q_{1}=65.4-56.1=9.3(kg)}、四分位偏差はQ3−Q12=9.32=4.65(kg){\displaystyle {\frac {Q_{3}-Q_{1}}{2}}={\frac {9.3}{2}}=4.65(kg)}となる。

箱ひげ図

資料のばらつき具合をグラフにまとめて見やすくしたものを箱ひげ図と言う。

箱ひげ図の見方を以下で示す。なお、以下の図は資料3を参照して作成しているが、0.5kg未満の数値を切り捨てしてあるので正しく作成した図と等しくならないことには注意。

                      C
            +----------+------+       
       |----|         +|      |---------|
            +----------+------+    
       A    B          D      E         F          
+---+---+---+---+---+---+---+---+---+---+   (横軸1メモリは0.5kg)
50  52  54  56  58  60  62  64  66  68  70
  • Aは最小値(53.6→53.5)
  • Bは第1四分位数(56.1→56.0)
  • Cは平均値(61.2→61.0)
  • Dは中央値(61.5)
  • Eは第3四分位数(65.4→65.0)
  • Fは最大値(70.0)

外れ値

データの中に含まれることがある、他の値とは極端にかけ離れた値のことを外れ値と呼ぶ。データのとる値を変数xとみなしたとき、外れ値の基準を以下のように定める。{x≤Q1−1.5(Q3−Q1)x≥Q3+1.5(Q3−Q1)

{\displaystyle {\begin{cases}x\leq Q_{1}-1.5(Q_{3}-Q_{1})\\x\geq Q_{3}+1.5(Q_{3}-Q_{1})\end{cases}}}

外れ値が存在する場合、四分位数は全てのデータを用いて考えるが、箱ひげ図の左右のひげは外れ値を除いて考える。外れ値を箱ひげ図にあえて示す場合は以下のように⚪︎を用いて表す。

  Q1−1.5(Q3−Q1){\displaystyle Q_{1}-1.5(Q_{3}-Q_{1})}                Q3+1.5∗(Q3−Q1){\displaystyle Q_{3}+1.5*(Q_{3}-Q_{1})}       
      |        +----------+------+     |
⚪︎⚪︎⚪︎ |   |----|        + |      |---| |          ⚪︎
      |        +----------+------+     |
               Q1{\displaystyle Q_{1}}        Q2{\displaystyle Q_{2}}     Q3{\displaystyle Q_{3}}

外れ値は必ずしも測定ミス等で発生した異常な値とは限らない。外れ値の背景を調査することで、新たな問題が発見されたり問題解決の糸口が掴めたりする場合がある。

偏差

変数xのとる値がx1,x2,⋯,xn

{\displaystyle x_{1},x_{2},\cdots ,x_{n}}

のn個あるとき、各値と平均値x¯{\displaystyle {\overline {x}}}との差x1−x¯,x2−x¯,⋯,xn−x¯

{\displaystyle x_{1}-{\overline {x}},x_{2}-{\overline {x}},\cdots ,x_{n}-{\overline {x}}}

を、それぞれ平均値からの偏差（へんさ）という。

資料1で、平均値からの偏差は次のようになる。

出席番号12345678910
体重60.357.965.456.153.662.770.055.867.163.1
偏差-0.9-3.34.2-5.1-7.61.58.8-5.45.91.9

さて、今知りたいのは資料全体の偏り具合の傾向であった。それを調べるために、試しに偏差の平均値を計算してみよう。(x1−x¯)+(x2−x¯)+⋯+(xn−x¯)n

{\displaystyle {\frac {(x_{1}-{\overline {x}})+(x_{2}-{\overline {x}})+\cdots +(x_{n}-{\overline {x}})}{n}}}

=1n(x1+x2+⋯+xn)−1n×nx¯

{\displaystyle ={\frac {1}{n}}(x_{1}+x_{2}+\cdots +x_{n})-{\frac {1}{n}}\times n{\overline {x}}}

=x¯−x¯=0

{\displaystyle ={\overline {x}}-{\overline {x}}=0}

このように、偏差の平均値は常に0になる。

分散と標準偏差

偏差の平均は常に0となるので、これを計算してもデータの散らばりの大きさを知ることはできないことがわかった。そこで、偏差の2乗の平均値を考える。この値を分散(ぶんさん、英：variance）という。分散をs2{\displaystyle s^{2}}で表すと、次のようになる。

分散
s2=(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2n{\displaystyle s^{2}={\frac {(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}}{n}}}

この分散の定義は自然なものであるが、たとえば、データが身長の場合、その単位はcmであるが、分散は偏差の2乗の平均なので、その単位はcm2{\displaystyle cm^{2}}になってしまう。そのため、単位を変量と合わせるために、分散s2{\displaystyle s^{2}}の正の平方根sを考えることも多い。このsを資料xの標準偏差(ひょうじゅんへんさ、英：standard deviation)という。

標準偏差
s=(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2n{\displaystyle s={\sqrt {\frac {(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}}{n}}}}

資料1の分散と標準偏差を求めよう。

体重60.357.965.456.153.662.770.055.867.163.1
偏差-0.9-3.34.2-5.1-7.61.58.8-5.45.91.9
偏差の2乗0.8110.8917.6427.0457.762.2577.4429.1634.813.61

分散s2{\displaystyle s^{2}}はs2=0.81+10.89+17.64+27.04+57.76+2.25+77.44+29.16+34.81+3.6110=26.038

{\displaystyle s^{2}={\frac {0.81+10.89+17.64+27.04+57.76+2.25+77.44+29.16+34.81+3.61}{10}}=26.038}

標準偏差sはs=26.038=5.102⋯

{\displaystyle s={\sqrt {26.038}}=5.102\cdots }


度数分布表から分散と標準偏差を求めるときは次のようになる。

度数分布表からの分散と標準偏差
階級値をx1,x2,⋯,xr{\displaystyle x_{1},x_{2},\cdots ,x_{r}}とし、それに対応する度数をf1,f2,⋯,fr{\displaystyle f_{1},f_{2},\cdots ,f_{r}}とする。分散s2{\displaystyle s^{2}}と標準偏差sはs2=(x1−x¯)2f1+(x2−x¯)2f2+⋯+(xr−x¯)2frns=(x1−x¯)2f1+(x2−x¯)2f2+⋯+(xr−x¯)2frn

偏差値(コラム)

諸君も興味を持っているかもしれない大学受験の世界では、「偏差値」という数値がしばしば取り上げられる。偏差値は、次の式で計算される。

偏差値
x1,x2,...{\displaystyle x_{1},x_{2},...}の中の数値xi{\displaystyle x_{i}}の偏差値は、10(xi−x¯)s+50

10とか50といった定数は、出てきた数値が直感的にわかりやすい大きさとなるようにしている定数（規格化定数という）であり、直接に意味はない。注目すべきは、この計算式の中に、平均と標準偏差が含まれているということである。つまり、同じ学力を持った人どうしであっても、違う試験を受ければ、試験を受けた他の人たちの動向によって偏差値は大きく変化するということである。そのような数値であるので、少しの変化にあまり一喜一憂しすぎないようにしたい。

分散と2乗の平均値

分散の式は、次のように変形できる。s2=1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}

{\displaystyle s^{2}={\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}

=1n[{(x1)2+(x2)2+⋯+(xn)2}−2x¯(x1+x2+⋯+xn)+n(x¯)2]

{\displaystyle ={\frac {1}{n}}\left[\left\{(x_{1})^{2}+(x_{2})^{2}+\cdots +(x_{n})^{2}\right\}-2{\overline {x}}(x_{1}+x_{2}+\cdots +x_{n})+n({\overline {x}})^{2}\right]}

=1n{(x1)2+(x2)2+⋯+(xn)2}−1n×2x¯(x1+x2+⋯+xn)+1n×n(x¯)2

{\displaystyle ={\frac {1}{n}}\left\{(x_{1})^{2}+(x_{2})^{2}+\cdots +(x_{n})^{2}\right\}-{\frac {1}{n}}\times 2{\overline {x}}(x_{1}+x_{2}+\cdots +x_{n})+{\frac {1}{n}}\times n({\overline {x}})^{2}}

=1n{(x1)2+(x2)2+⋯+(xn)2}−2x¯×1n(x1+x2+⋯+xn)+(x¯)2

{\displaystyle ={\frac {1}{n}}\left\{(x_{1})^{2}+(x_{2})^{2}+\cdots +(x_{n})^{2}\right\}-2{\overline {x}}\times {\frac {1}{n}}(x_{1}+x_{2}+\cdots +x_{n})+({\overline {x}})^{2}}

=x2¯−2x¯×x¯+(x¯)2

{\displaystyle ={\overline {x^{2}}}-2{\overline {x}}\times {\overline {x}}+({\overline {x}})^{2}}

=x2¯−(x¯)2

{\displaystyle ={\overline {x^{2}}}-({\overline {x}})^{2}}

すなわち、公式の形にするならば、次のように書ける。

分散と2乗の平均値
(xの分散) = (x2の平均) – (xの平均)2


この式を使って、資料1の分散を求めよう。

x2{\displaystyle x^{2}}の平均はx2¯=(60.3)2+(57.9)2+(65.4)2+(56.1)2+(53.6)2+(62.7)2+(70.0)2+(55.8)2+(67.1)2+(63.1)210=3771.478

{\displaystyle {\overline {x^{2}}}={\frac {(60.3)^{2}+(57.9)^{2}+(65.4)^{2}+(56.1)^{2}+(53.6)^{2}+(62.7)^{2}+(70.0)^{2}+(55.8)^{2}+(67.1)^{2}+(63.1)^{2}}{10}}=3771.478}

xの平均の2乗は(x¯)2=(61.2)2=3745.44

{\displaystyle ({\overline {x}})^{2}=(61.2)^{2}=3745.44}

よって、分散はs2=x2¯−(x¯)2=3771.478−3745.44=26.038

{\displaystyle s^{2}={\overline {x^{2}}}-({\overline {x}})^{2}=3771.478-3745.44=26.038}

と、前に出した方法と同じ値になる。

変量の変換（コラム）

変量xについてのデータx1,x2,⋯xn{\displaystyle x_{1},x_{2},\cdots x_{n}}があり、その平均値、分散、標準偏差をそれぞれx¯,sx2,sx{\displaystyle {\overline {x}},s_{x}^{2},s_{x}}とする。

定数a,b{\displaystyle a,b}を用いて新たな変量yをy=ax+b{\displaystyle y=ax+b}で定義する。

このときyのデータはn個の値を持ち、それはy1=ax1+b,y2=ax2+b,⋯,yn=axn+b{\displaystyle y_{1}=ax_{1}+b,y_{2}=ax_{2}+b,\cdots ,y_{n}=ax_{n}+b}となる。


yのデータの平均値は、y¯=y1+y2+⋯+ynn

{\displaystyle {\overline {y}}={\frac {y_{1}+y_{2}+\cdots +y_{n}}{n}}}

=(ax1+b)+(ax2+b)+⋯+(axn+b)n

{\displaystyle ={\frac {(ax_{1}+b)+(ax_{2}+b)+\cdots +(ax_{n}+b)}{n}}}

=a(x1+x2+⋯+xn)+nbn

{\displaystyle ={\frac {a(x_{1}+x_{2}+\cdots +x_{n})+nb}{n}}}

=a(x1+x2+⋯+xn)n+b

{\displaystyle =a{\frac {(x_{1}+x_{2}+\cdots +x_{n})}{n}}+b}

=ax¯+b

{\displaystyle =a{\overline {x}}+b}


yk−y=axk+b−(ax¯+b)=a(xk−x¯){\displaystyle y_{k}-y=ax_{k}+b-(a{\overline {x}}+b)=a(x_{k}-{\overline {x}})}を用いると、yのデータの分散はsy2=(y1−y)2+(yn−y)2+⋯+(yn−y)2n

{\displaystyle s_{y}^{2}={\frac {(y_{1}-y)^{2}+(y_{n}-y)^{2}+\cdots +(y_{n}-y)^{2}}{n}}}

=a2(x1−x)2+a2(x2−x)2+⋯+a2(xn−x)2n

{\displaystyle ={\frac {a^{2}(x_{1}-x)^{2}+a^{2}(x_{2}-x)^{2}+\cdots +a^{2}(x_{n}-x)^{2}}{n}}}

=a2(x1−x)2+(x2−x)2+⋯+(xn−x)2n

{\displaystyle =a^{2}{\frac {(x_{1}-x)^{2}+(x_{2}-x)^{2}+\cdots +(x_{n}-x)^{2}}{n}}}

=a2sx2

{\displaystyle =a^{2}s_{x}^{2}}


また、上式よりyの標準偏差はsy=|a|sx

{\displaystyle s_{y}=|a|s_{x}}


このように、変量xを定数a,bを用いた一次式によって別の変量yに変換した際、y¯,sy2,sy{\displaystyle {\overline {y}},s_{y}^{2},s_{y}}をa,b,x¯,sx2,sx{\displaystyle a,b,{\overline {x}},s_{x}^{2},s_{x}}のみで表すことができる。このような処理を変量の変換と呼ぶ。

なお、数学Bの「統計的な推測」において、確率変数における変量の変換と期待値の変量変換について取り扱う。

相関関係

今までは1種類のステータスについてのデータ分析を行ってきた。ここでは2種類のステータスがどのような傾向になっているか見て行くこととしよう。

散布図

以下の資料6は資料1に身長の値を加えたものである。

出席番号12345678910
体重（kg）60.357.965.456.153.662.770.055.867.163.1
身長（cm）161.2154.3162.8160.4155.7163.5172.5166.4173.2164.0

例えば、上の資料6の体重をx（kg）、身長をy（cm）として、点(x,y){\displaystyle \left(x,y\right)}を座標平面上にとったとする。

2つの変量からなる資料を平面上に図示したものを散布図（さんぷず）または相関図（そうかんず）という。以下は資料8の相関図である。点の付近にある数字はその数値に該当する人の出席番号を表す。

一般に、散布図において、

  • 2つのデータの一方が増えるとき、もう一方も増える傾向にある場合、正の相関関係があるという。
  • 2つのデータの一方が増えるとき、もう一方が減る傾向にある場合、負の相関関係があるという。
  • 2つのデータの間に、正の相関関係も負の相関関係もない場合、相関関係はないという。

相関係数

2つのデータx , yについて、次のn個の値の組を考える。(x1,y1),(x2,y2),⋯,(xn,yn)

{\displaystyle \left(x_{1},y_{1}\right),\left(x_{2},y_{2}\right),\cdots ,\left(x_{n},y_{n}\right)}

xの平均値をx¯{\displaystyle {\overline {x}}}、yの平均値をy¯{\displaystyle {\overline {y}}}とするとx¯=1n(x1+x2+⋯+xn)

{\displaystyle {\overline {x}}={\frac {1}{n}}(x_{1}+x_{2}+\cdots +x_{n})}

y¯=1n(y1+y2+⋯+yn)

{\displaystyle {\overline {y}}={\frac {1}{n}}(y_{1}+y_{2}+\cdots +y_{n})}

また、xの標準偏差をSx{\displaystyle S_{x}}、yの標準偏差をSy{\displaystyle S_{y}}とするとSx=1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}

{\displaystyle S_{x}={\sqrt {{\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}}}

Sy=1n{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}

{\displaystyle S_{y}={\sqrt {{\frac {1}{n}}\left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}

ここでSxy=1n{(x1−x¯)(y1−y¯)+(x2−x¯)(y2−y¯)+⋯+(xn−x¯)(yn−y¯)}

{\displaystyle S_{xy}={\frac {1}{n}}\left\{(x_{1}-{\overline {x}})(y_{1}-{\overline {y}})+(x_{2}-{\overline {x}})(y_{2}-{\overline {y}})+\cdots +(x_{n}-{\overline {x}})(y_{n}-{\overline {y}})\right\}}

 ……(1)

の値の符号について考える。(1)をxとyの共分散（きょうぶんさん、英：covariance）といい、Sxy{\displaystyle S_{xy}}と表す。

共分散が正のときは、(xk−x¯)(yk−y¯)>0{\displaystyle (x_{k}-{\overline {x}})(y_{k}-{\overline {y}})>0}となるものが、(xk−x¯)(yk−y¯)<0{\displaystyle (x_{k}-{\overline {x}})(y_{k}-{\overline {y}})<0}よりも多いと考えられる。

すなわち

(xk−x¯)>0{\displaystyle (x_{k}-{\overline {x}})>0} かつ　(yk−y¯)>0{\displaystyle (y_{k}-{\overline {y}})>0}

または

(xk−x¯)<0{\displaystyle (x_{k}-{\overline {x}})<0} かつ　(yk−y¯)<0{\displaystyle (y_{k}-{\overline {y}})<0}

が多いということになる。

よって、共分散が正のとき、xとyには正の相関関係があるといえる。


共分散が負のときは、(xk−x¯)(yk−y¯)<0{\displaystyle (x_{k}-{\overline {x}})(y_{k}-{\overline {y}})<0}となるものが、(xk−x¯)(yk−y¯)>0{\displaystyle (x_{k}-{\overline {x}})(y_{k}-{\overline {y}})>0}よりも多いと考えられる。

すなわち

(xk−x¯)>0{\displaystyle (x_{k}-{\overline {x}})>0} かつ　(yk−y¯)<0{\displaystyle (y_{k}-{\overline {y}})<0}

または

(xk−x¯)<0{\displaystyle (x_{k}-{\overline {x}})<0} かつ　(yk−y¯)>0{\displaystyle (y_{k}-{\overline {y}})>0}

が多いということになる。

よって、共分散が負のとき、xとyには負の相関関係があるといえる。


共分散の値は、資料x , yの内容によって大きく値が変わるので、x , yの偏差をそれぞれの標準偏差Sx,Sy{\displaystyle S_{x},S_{y}}で割った値の積の平均値1n(x1−x¯Sx×y1−y¯Sy+x2−x¯Sx×y2−y¯Sy+⋯+xn−x¯Sx×yn−y¯Sy)

{\displaystyle {\frac {1}{n}}\left({\frac {x_{1}-{\overline {x}}}{S_{x}}}\times {\frac {y_{1}-{\overline {y}}}{S_{y}}}+{\frac {x_{2}-{\overline {x}}}{S_{x}}}\times {\frac {y_{2}-{\overline {y}}}{S_{y}}}+\cdots +{\frac {x_{n}-{\overline {x}}}{S_{x}}}\times {\frac {y_{n}-{\overline {y}}}{S_{y}}}\right)}

を考え、この値を資料x , yの相関係数（そうかんけいすう、英: correlation coefficient）といい、rで表す。Sx=1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}

{\displaystyle S_{x}={\sqrt {{\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}}}

Sy=1n{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}

{\displaystyle S_{y}={\sqrt {{\frac {1}{n}}\left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}

であるから、1n(x1−x¯Sx×y1−y¯Sy+x2−x¯Sx×y2−y¯Sy+⋯+xn−x¯Sx×yn−y¯Sy)

{\displaystyle {\frac {1}{n}}\left({\frac {x_{1}-{\overline {x}}}{S_{x}}}\times {\frac {y_{1}-{\overline {y}}}{S_{y}}}+{\frac {x_{2}-{\overline {x}}}{S_{x}}}\times {\frac {y_{2}-{\overline {y}}}{S_{y}}}+\cdots +{\frac {x_{n}-{\overline {x}}}{S_{x}}}\times {\frac {y_{n}-{\overline {y}}}{S_{y}}}\right)}

=1n(x1−x¯1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}×y1−y¯1n{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}+x2−x¯1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}×y2−y¯1n{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}+⋯+xn−x¯1n{(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}×yn−y¯1n{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2})

{\displaystyle ={\frac {1}{n}}\left({\frac {x_{1}-{\overline {x}}}{\sqrt {{\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}}}\times {\frac {y_{1}-{\overline {y}}}{\sqrt {{\frac {1}{n}}\left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}+{\frac {x_{2}-{\overline {x}}}{\sqrt {{\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}}}\times {\frac {y_{2}-{\overline {y}}}{\sqrt {{\frac {1}{n}}\left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}+\cdots +{\frac {x_{n}-{\overline {x}}}{\sqrt {{\frac {1}{n}}\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}}}}\times {\frac {y_{n}-{\overline {y}}}{\sqrt {{\frac {1}{n}}\left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}\right)}

=(x1−x¯)(y1−y¯)+(x2−x¯)(y2−y¯)+⋯+(xn−x¯)(yn−y¯){(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}×{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}

{\displaystyle ={\frac {(x_{1}-{\overline {x}})(y_{1}-{\overline {y}})+(x_{2}-{\overline {x}})(y_{2}-{\overline {y}})+\cdots +(x_{n}-{\overline {x}})(y_{n}-{\overline {y}})}{\sqrt {\left\{(x_{1}-{\overline {x}})^{2}+(x_{2}-{\overline {x}})^{2}+\cdots +(x_{n}-{\overline {x}})^{2}\right\}\times \left\{(y_{1}-{\overline {y}})^{2}+(y_{2}-{\overline {y}})^{2}+\cdots +(y_{n}-{\overline {y}})^{2}\right\}}}}}
相関係数
xの平均値をx¯{\displaystyle {\overline {x}}}、yの平均値をy¯{\displaystyle {\overline {y}}}とすると、相関係数rはr=(x1−x¯)(y1−y¯)+(x2−x¯)(y2−y¯)+⋯+(xn−x¯)(yn−y¯){(x1−x¯)2+(x2−x¯)2+⋯+(xn−x¯)2}×{(y1−y¯)2+(y2−y¯)2+⋯+(yn−y¯)2}


相関係数rは、一般に−1≤r≤1{\displaystyle -1\leq r\leq 1}が成り立つ。

  • 相関係数rの値が1に近いほど、正の相関が強くなる。このとき、相関図の点は右上がりに分布する。
  • 相関係数rの値が-1に近いほど、負の相関が強くなる。このとき、相関図の点は右下がりに分布する。
  • 相関係数rの値が0に近いときは、相関は弱くなる。

ではこれを用いて資料6の相関関係を見てみよう。

出席番号12345678910
体重（kg）60.357.965.456.153.662.770.055.867.163.1
体重偏差-0.9-3.34.2-5.1-7.61.58.8-5.45.91.9
身長（cm）161.2154.3162.8160.4155.7163.5172.5166.4173.2164.0
身長偏差-2.2-9.1-0.6-3.07.70.19.13.09.80.6

よって相関係数rは r=(−0.9)×(−2.2)+(−3.3)×(−9.1)+4.2×(−0.6)+(−5.1)×(−3.0)+(−7.6)×(−7.7)+1.5×0.1+8.8×9.1+(−5.4)×3.0+5.9×9.8+1.9×0.6{(−0.9)2+(−3.3)2+(4.2)2+(−5.1)2+(−7.6)2+(1.5)2+(8.8)2+(−5.4)2+(5.9)2+(1.9)2}×{(−2.2)2+(−9.1)2+(−0.6)2+(−3.0)2+(7.7)2+(0.1)2+(9.1)2+(3.0)2+(9.8)2+(0.6)2}{\displaystyle r={\frac {(-0.9)\times (-2.2)+(-3.3)\times (-9.1)+4.2\times (-0.6)+(-5.1)\times (-3.0)+(-7.6)\times (-7.7)+1.5\times 0.1+8.8\times 9.1+(-5.4)\times 3.0+5.9\times 9.8+1.9\times 0.6}{\sqrt {\left\{(-0.9)^{2}+(-3.3)^{2}+(4.2)^{2}+(-5.1)^{2}+(-7.6)^{2}+(1.5)^{2}+(8.8)^{2}+(-5.4)^{2}+(5.9)^{2}+(1.9)^{2}\right\}\times \left\{(-2.2)^{2}+(-9.1)^{2}+(-0.6)^{2}+(-3.0)^{2}+(7.7)^{2}+(0.1)^{2}+(9.1)^{2}+(3.0)^{2}+(9.8)^{2}+(0.6)^{2}\right\}}}}}

=0.755568⋯{\displaystyle =0.755568\cdots }

となり、この10人の身長と体重には正の相関関係があることが分かる。


共分散Sxy{\displaystyle S_{xy}}を用いると、相関係数はr=SxySxSy{\displaystyle r={\frac {S_{xy}}{S_{x}S_{y}}}}と求まる。普通、こちらで求める方が多い。

仮説検定

集団に対して調査を行う場合、調べたい集団の全体のデータを集めることは難しい。このとき、データの一部を抜き出して全体を推測することがある。この一つの考え方として、仮説検定がある。

仮説検定の考え方

例題を使って考えてみよう。

例題

あるせんべいの製造会社が、今までの塩味のせんべいを改良し、天然塩を使ったせんべいを開発した。既存のせんべいをA、天然塩のせんべいをBとするとき、BがAよりも美味しいと評価されるかを調査するため、無作為な30人にそれぞれのせんべいを食べてもらい、どちらが美味しいと感じるかを回答してもらった。その結果、20人がBと回答した。この回答のデータから、BはAよりも美味しいと判断してよいか。仮説検定の考え方を用い、基準となる確率を0.05として考察しなさい。ただし、公正なコインを30枚投げて表の出た枚数を記録する実験を300回おこなったところ、次の表のようになったとし、この結果を用いなさい。

表の枚数891011121314151617181920212223計
度数512152329343841352817145211300

仮説検定では、ある主張〈1〉が正しいと判断できるかどうかを考えます。その時、それに反する主張〈2〉を立てます。今回の場合は、

　主張〈1〉 BはAよりも美味しいと評価されている

　主張〈2〉 AとBのどちらの回答も全くの偶然でおこる

となります。

このとき、主張〈2〉を帰無仮説(きむかせつ)、主張〈1〉を対立仮説(たいりつかせつ)といいます。

次に、主張〈2〉のもとで、実際の事象が起こりにくいかどうかを考えます。 主張〈2〉より、表が20枚以上でる場合の相対度数は、

5+2+1+1300=0.03{\displaystyle {\frac {5+2+1+1}{300}}=0.03}

となります。

問題文より、基準となる確率が0.05となっているので、この数値より小さいのがわかります。ちなみに、この基準となる確率のことを有意水準(ゆういすいじゅん)といいます。

この結果より、実際の事象は起こりにくいと考えられます。つまり、主張〈2〉は正しくないと評価できるのです。

よって、主張〈1〉は主張〈2〉と反するものであることから、主張〈1〉は正しいと判断してもよいという解答になります。

有意水準より実際の事象の相対度数が大きかった場合は、主張〈2〉の仮定が否定できません。よって、主張〈1〉も判断できないという解答になります。

詳しくは数学Bで取り扱います。そちらもご覧ください。

表計算(基礎編)

アンケートなど、資料の数が多い場合には手作業で計算をすると膨大な時間がかかる。そこでコンピュータの表計算ソフト（ここではMicrosoft Excelを例に取る）を用いて統計処理を行ってみよう。

コンピュータにMicrosoft Excelが入っていない場合はフリーソフトのOpenoffice Calcなどで代用できる。自身のOS（Windows,Mac,Linuxなど）に合ったバージョンをダウンロードしないと動かないので注意。

「はじめに」のセクションでも述べた通り、ここから先は学習指導要領外となりますので余力のある方が学習するといいでしょう。

  • 演習上の注意（wikibooks免責事項もお読み下さい）
    • コンピュータ操作中に何らかの不具合が生じても、一切の責任を負いかねます。
    • 必ずしも当ページの説明と実際の表計算ソフトの動作が一致するとは限りません。
    • 学校のコンピュータを使用する場合は必ずコンピュータ室担当の先生に許可を貰って下さい。

表計算ソフト

表計算ソフトを起動すると長方形の何も書かれていない枠が無数に並んでいる。この枠それぞれのことをセルと言う。また1,2,3,・・・から右に出るラインそれぞれを行と言い、A,B,C,・・・から下に出るラインそれぞれを列と言う。

セルの個々の呼び方は列番号→行番号のように表す。例えば列番号がC、行番号が3であるセルは「C3のセル」であると言う。

ABCDE
1302
2204
3406
4355
  • 問：A2のセル・B3のセルに当たる数値をそれぞれ答えよ。また、「35」・「2」はそれぞれどのセルに入力されているか。
  • 実習1：表計算ソフトを起動し、上記の表を作成してみよ。各セルをクリックすると文字入力待機状態になり、キーボードからの入力を受け付ける。

数値計算

ここでは数値が入力されたセルに対しての計算方法を学ぶ。表計算ソフトによって計算式の種類や入力方法など異なる場合があるので事前に確認しておくこと。ここではよく用いられる演算式を示すが、詳細は表計算ソフトのヘルプ・表計算ソフトについて書かれた書籍を参考にして欲しい。

表計算ソフトでは直接セルに計算式を入力することによって、指定されたセルに対して計算を行い、その実行結果が計算式を入力したセルに反映される。またそのセルを複写すると複写先のセルに応じた計算式となって入力され、その実行結果が表示される。（※詳細は実践編「セルの参照」で）

演算子

セルに計算式を入力することによって様々な計算ができる。また、その計算に必要な記号のことを（算術-）演算子と言う。一般にX1のセルとY1のセルに入力されている数値の計算は以下のようになる。

  • X1を代入・・・ =X1{\displaystyle =X1}
  • X1とY1の和・・・ =X1+Y1{\displaystyle =X1+Y1}
  • X1からY1を引いた値・・・ =X1−Y1{\displaystyle =X1-Y1}
  • X1とY1の積・・・ =X1∗Y1{\displaystyle =X1*Y1}
  • X1をY1で割った値の整数部分・・・ =X1/Y1{\displaystyle =X1/Y1}
  • X1をY1で割った余り・・・ =X1%Y1{\displaystyle =X1\%Y1}
  • X1をY1回掛けた値（べき乗）・・・ =X1{\displaystyle =X1}^Y1{\displaystyle Y1}
  • 実習2：表1のC1のセルに=A1+B1{\displaystyle =A1+B1}、C2のセルに=A2−B2{\displaystyle =A2-B2}、C3のセルに=A3∗B3{\displaystyle =A3*B3}、C4のセルに=A4/B4{\displaystyle =A4/B4}と入力してみよ。また、それらの数値が他の方法で計算した結果と合致しているか確かめよ。

関数

一般に関数とはxの値を決めるとyの値が1つに定まるものであるが、コンピュータ分野においての関数は一般のそれとは異なり用途別に予め用意された計算式のことを表す。この時計算対象のセルを括弧で指定するが、括弧内を引数（ひきすう）と言う。X1のセルに入力された数値の演算の代表的な例を以下に挙げる。関数の計算結果を出力することを値を返すと言い、その値のことを返り値と言う。表計算ソフトには膨大な種類の関数が用意されているが、このページでの紹介は極一部に留める。

  • 正の平方根・・・ =SQRT(X1){\displaystyle =SQRT(X1)}
  • 絶対値・・・ =ABS(X1){\displaystyle =ABS(X1)}
  • X1を超えない最大の整数・・・ =INT(X1){\displaystyle =INT(X1)}
  • 整数値で四捨五入・・・ =ROUND(X1){\displaystyle =ROUND(X1)}

今の段階ではあまり気にしなくてもよいが三角関数を用いる場合は弧度法（「弧の長さ÷半径の長さ」で記述する角の測り方で、単位はラジアン：詳細は数学IIで勉強する）での取扱いになる為、度数法での記述の場合は予め弧度法に直しておかなければならない。（※詳細は実践編「関数の仕様」で）

度数法から弧度法への変換は、n∘=n×π180{\displaystyle n^{\circ }=n\times {\frac {\pi }{180}}}とすればよい。

  • 正弦（サイン）・・・ =SIN(X1){\displaystyle =SIN(X1)}
  • 余弦（コサイン）・・・ =COS(X1){\displaystyle =COS(X1)}
  • 正接（タンジェント）・・・ =TAN(X1){\displaystyle =TAN(X1)}

またX1・X2・X3・・・Xnのセルに対して演算を行う場合は以下のようになる。A1・B1・C1・・・x1のセルに対して演算する場合は以下の(X1:Xn)を(A1:x1)と書き換えればよい。

  • セルの個数・・・ =COUNT(X1:Xn){\displaystyle =COUNT(X1:Xn)}
  • 全ての和・・・ =SUM(X1:Xn){\displaystyle =SUM(X1:Xn)}
  • 平均値・・・ =AVERAGE(X1:Xn){\displaystyle =AVERAGE(X1:Xn)}
  • 中央値・・・ =MEDIAN(X1:Xn){\displaystyle =MEDIAN(X1:Xn)}
  • 最頻値・・・ =MODE(X1:Xn){\displaystyle =MODE(X1:Xn)}

グラフの作成

以下の表は資料2を表計算ソフトに入力したものである。ただし階級は、52.0kg以上55.0kg未満の階級のことを52.0-55.0などと表すことにする。セルに入る文字が長くデフォルトの大きさで収まらない場合、セルの大きさを調節して表を見やすくしてみよう。グラフの作成の仕方を以下に示す。

  1. グラフの元になるデータの左上のセルから右下のセルまでドラッグし、選択させた状態にする。
  2. ヒストグラムが書いてあるアイコンをクリックし、グラフウィザードを起動させ、グラフの種類を選択する。
  3. 範囲が正しく設定されてることを確認し、系列を選択する。
  4. タイトルと項目軸の名前を設定し（無くても可）、グラフを表示させるSheetを選択する。
ABC
1階級階級値度数
252.0-55.053.51
355.0-58.056.53
458.0-61.059.51
561.0-64.062.52
664.0-67.065.51
767.0-70.068.51
870.0-73.071.51
  • 実習3：表計算ソフトに上記の表を作成してみよ。また、グラフ作成機能を用いてヒストグラムと度数折れ線を作成してみよ。この時、B列・C列さえあればグラフは作成できる。完成すると「リンク先」に挙げたようなグラフになるはずである。
  • 注意

度数折れ線は左右両端に度数が0である階級があるものとして作図をすると前に述べた。故にこのグラフを表計算ソフトで作成する場合は表2の2行の前の行に階級値が50.5であるもの、8行の後の行に階級値が74.5であるもの（それぞれ度数は0）を事前に挿入しておかなければならない。

平均値・分散・標準偏差

以下の表3は表2にいくつかの情報を追加したものである。尚、10行については表を見やすくするために空けてある。表の空欄を埋めながら実習をするとよい。

ABCDEFG
1階級階級値度数階級値×度数偏差偏差の2乗偏差の2乗×度数
252.0-55.053.5153.5
355.0-58.056.53
458.0-61.059.51
561.0-64.062.52
664.0-67.065.51
767.0-70.068.51
870.0-73.071.51
9合計10
10
11平均値
12分散
13標準偏差
  • 実習4：表計算ソフトに上記の表を作成し、D列にそれぞれの「階級値×度数」を求める式を入力せよ。例えばD2のセルの値はB2のセルの値とC2のセルの値を掛け合わせた数値なので=B2∗C2{\displaystyle =B2*C2}と入力される。
  • 実習5：実習3の結果からB11のセルに平均値を求める式をSUM{\displaystyle SUM}を使った式で入力せよ。（ヒント：D2～D8のセルの数値の合計を10で割る。）
  • 実習6：E列にそれぞれの偏差を求める式を入力せよ。例えばE2のセルの値はB2のセルの値からB11のセルの値を引いた数値なので=B2−B11{\displaystyle =B2-B11}と入力される。
  • 実習7：F列にそれぞれ偏差の2乗を入力した後、G9のセルに「偏差の2乗×度数」の合計を求める式を入力せよ。
  • 実習8：実習7よりB12のセルに分散、B13のセルに標準偏差をそれぞれ表示させてみよ。（ヒント：分散は偏差の2乗の平均なので実習5に同じくSUM{\displaystyle SUM}が使える。標準偏差は分散の正の平方根なのでSQRT{\displaystyle SQRT}を使うと簡単にできる。）


尚、全ての空欄を埋めた表は以下の通りになる。

ABCDEFG
1階級階級値度数階級値×度数偏差偏差の2乗偏差の2乗×度数
252.0-55.053.5153.5-7.860.8460.84
355.0-58.056.53169.5-4.823.0469.12
458.0-61.059.5159.5-1.83.243.24
561.0-64.062.52125.01.21.442.88
664.0-67.065.5165.54.217.6417.64
767.0-70.068.5168.57.251.8451.84
870.0-73.071.5171.510.2104.04104.04
9合計10309.6
10
11平均値61.3
12分散30.96
13標準偏差5.564

相関係数

以下の表4は資料7を表にしたものである。ここでは今まで学んだことを用いて全ての空欄を埋めて欲しい。13行は表の見やすさのために空けてある。いくつかのセルは結合されているがその手順を以下に示す。以下の例ではA1・A2のセルを結合させる場合を考える。

  1. A1のセルからA2のセルに向けてドラッグ（逆方向にドラッグしてもよい）し、2つのセルを選択させた状態にする。
  2. 選択された範囲内で右クリックし、「セルの書式設定＞配置＞文字の制御」の「セルの結合」の部分にチェックマークを入れる。
  3. A1・A2のセルの間の境界線が無くなり、2つのセルが結合された状態になる。
ABCDEFG
1出席番号体重身長
2数値偏差偏差の2乗数値偏差偏差の2乗
3160.3161.2
4257.9154.3
5365.4162.8
6456.1160.4
7553.6155.7
8662.7163.5
9770.0172.5
10855.8166.4
11967.1173.2
121063.1164.0
13
14相関係数

全ての空欄を埋めた表は以下の通りである。各々作成した表と見比べ確かめてみるとよい。

ABCDEFG
1出席番号体重身長
2数値偏差偏差の2乗数値偏差偏差の2乗
3160.3-0.90.81161.2-2.24.84
4257.9-3.310.89154.3-9.182.81
5365.44.217.64162.8-0.60.36
6456.1-5.126.01160.4-39
7553.6-7.657.76155.7-7.759.29
8662.71.52.25163.50.10.01
9770.08.877.44172.59.182.81
10855.8-5.429.16166.439
11967.15.934.81173.29.896.04
121063.11.93.61164.00.60.36
13
14相関係数0.755568

表計算(実践編)

ここではセルの参照など、実際の表計算で知っていると便利な項目を紹介しています。

関数の仕様

関数の中に別の関数を書くこともできますし、関数を項とみなして加減乗除などもできます。

例えば30度の正弦を求めたい場合には=SIN(RADIANS(30)){\displaystyle =SIN(RADIANS(30))}と入力します。=RADIANS(degree){\displaystyle =RADIANS(degree)}は度数法を弧度法に変換する関数のことです。degreeには求めたい角度を入れます。(この行は数学IIの範囲です)

表計算ソフトには統計に必要な関数が揃っており、以下は前セクションまでに扱った関数です。

  • 四分位数・・・ =QUARTILE(X1:Xn,number){\displaystyle =QUARTILE(X1:Xn,number)} ※numberには最小値=0・第一四分位数=1・中央値=2・第三四分位数=3・最大値=4と入れる
  • 分散・・・ =VARP(X1:Xn){\displaystyle =VARP(X1:Xn)}
  • 標準偏差・・・ =STDEVP(X1:Xn){\displaystyle =STDEVP(X1:Xn)}
  • 共分散・・・ =COVAR(X1:Xn,Y1:Yn){\displaystyle =COVAR(X1:Xn,Y1:Yn)}
  • 相関係数・・・ =CORREL(X1:Xn,Y1:Yn){\displaystyle =CORREL(X1:Xn,Y1:Yn)}

今までの関数を利用して資料1の代表値等をまとめてみましょう。=MAX(X1:Xn){\displaystyle =MAX(X1:Xn)} は最大値を返す関数、=MIN(X1:Xn){\displaystyle =MIN(X1:Xn)}は最小値を返す関数です。

ABCDEFGHIJK
1出席番号12345678910
2体重60.357.965.456.153.662.770.055.867.163.1
3
4平均値61.2=AVERAGE(B2:K2)
5中央値61.5=MEDIAN(B2:K2)
6範囲16.4=MAX(B2:K2)-MIN(B2:K2)
7分散26.038=VARP(B2:K2)
8標準偏差5.1027=STDEVP(B2:K2)

セルの参照

前の実習のようにいちいち式を書くのは面倒ですし間違いが起こりやすくなります。ここで活躍するのがセルの参照です。実際に見ていきましょう。

下の表は表3のB・C・D列を抜き出し、E列に備考を加えたものです。備考には左隣のセルに対応する式が入ります。

BCDE
1階級値度数階級値×度数備考
253.51
356.53
459.51
562.52
665.51
768.51
871.51

D2のセルは実習3の通り=B2∗C2{\displaystyle =B2*C2}でしたね。D3以降は実習では=B3∗C3{\displaystyle =B3*C3}や=B4∗C4{\displaystyle =B4*C4}・・・とやったはずです。

D2のセルの数式をコピーしD3のセルにペーストしてみましょう。するとD3のセルには169.5と出力されます。ここでD3に代入された式を見ると=B3∗C3{\displaystyle =B3*C3}と参照しているセルが自動的にそれぞれが1行下になっていることが分かります。目で見える情報では番地になって出てきますがプログラム内では3つ左のセルの数値と2つ左のセルの数値を掛け合わせなさいという命令に置き換わっているのです。この命令をコピーペーストしているのですから、反映先のセルの命令も全く変わりません。下の表は必要な部分だけ抜き出しています。

BCDE
1階級値度数階級値×度数備考
253.5153.5=B2*C2
356.53169.5=B3*C3

同じようにD列の他のセルにペーストしてみましょう。

BCDE
1階級値度数階級値×度数備考
253.5153.5=B2*C2
356.53169.5=B3*C3
459.5159.5=B4*C4
562.52125.0=B5*C5
665.5165.5=B6*C6
768.5168.5=B7*C7
871.5171.5=B8*C8

これで完成しました。コピーペーストをした時に自動的に参照が変わる方法を相対参照と言います。

下の表は表3の平均値の計算まで終わり偏差を求めようとする段階です。F列は備考としておきます。偏差は階級値-平均値でしたね。E2のセルに=B2−B11{\displaystyle =B2-B11}と入力しましょう。

ABCDEF
1階級階級値度数階級値×度数偏差備考
252.0-55.053.5153.5-7.8=B2-B11
355.0-58.056.53169.5
458.0-61.059.5159.5
561.0-64.062.52125.0
664.0-67.065.5165.5
767.0-70.068.5168.5
870.0-73.071.5171.5
9合計10
10
11平均値61.3
12分散
13標準偏差

E2のセルをコピーしてE3のセルにペーストしてみましょう。4行から9行は割愛しています。

ABCDEF
1階級階級値度数階級値×度数偏差備考
252.0-55.053.5153.5-7.8=B2-B11
355.0-58.056.53169.556.5=B3-B12
10
11平均値61.3
12分散
13標準偏差

明らかに間違いな数値が出てきてしまいました。E3のセルの式を見ると=B3−B12{\displaystyle =B3-B12} となっています。プログラム内では3つ左のセルの数値から3つ左、9つ下のセルの数値を引きなさいという命令に置き変わっています。コピーペーストしてもその命令は変わらないので、参照先が両方とも移動してしまいます。今の段階ではB12のセルに何も入っていないのですから、そのセルには0が入っているものとして計算されます。他のE列にコピーしてもやはり間違いな数値が出力されます（実験してみて下さい）。ここでは出てきませんが、文字列のセルと数値のセルを計算しようとするとエラーになります。

このような場合は参照するセルを固定することが必要になります。参照セルを固定する場合は固定したい番号の前に $ の文字を入れます。行番号も列番号も固定したい場合はそれぞれの番号の前に$をつけます。

では平均値が出力されているB11を固定してE2のセルをコピーしE3のセルにペーストしてみましょう。この場合は11のほうを固定したいのでB$11のように入力して固定します。

ABCDEF
1階級階級値度数階級値×度数偏差備考
252.0-55.053.5153.5-7.8=B2-B$11
355.0-58.056.53169.5-4.8=B3-B$11
10
11平均値61.3
12分散
13標準偏差

これで正しい結果を得ることができました。参照セルを固定する方法を絶対参照と言います。

「結局$ はどうつければいいの？」という疑問があるかと思いますが、ここでは簡単のために左右に移動させたくない場合はアルファベットの前に$、上下に移動させたくない場合は数字の前に$、どちらにも移動させたくない場合はアルファベット・数字両方の前に$と思っておけばいいでしょう。つまずきやすい場所なので実際に練習してみて動きを見るのも大切です。慣れると考えずとも正しく$をつけられるようになります。

詳しくは旧初級シスアド試験の表計算セクションに記述されています。

条件分岐

ある物事を一定の数値以上ならAを表示、それ未満ならBを表示する・・・などの操作をするためにどのようなことをするか学びましょう。

以下の表はレタス・トマト・ねぎの値段を記したものです。ここで以下のような条件をつけてみます。

値段を比較して昨年と同じか上がっている野菜は「↑」下がっていれば「↓」を比較列に入力する

ABCD
1野菜昨年同時期の値段現在の値段比較
2レタス138125
3トマト152160
4きゅうり9999

IF関数は=IF(formula,value1,value2){\displaystyle =IF(formula,value1,value2)}で指定します。formulaには論理式、value1には真の場合の値を、value2には偽の場合の値を入力します。値が半角数字や関数でない場合はvalue1やvalue2に” “をつけるのを忘れずに。” “は” “で囲まれた文字を出力しなさい、という命令です。

論理式には判定の条件となる式を入れます。真（true）であることは論理式を満たすもの、逆に偽（false）はそうでないもののことです。

論理式には比較演算子なるものを入れます。簡単に言えば等号や不等号のことです。気をつけるべき点としてはいわゆる≧や≦、≠の全角記号は使えないということです。

  • A1>100{\displaystyle A1>100}・・・A1の値が100より大きい
  • A1<100{\displaystyle A1<100}・・・A1の値が100より小さい
  • A1>=100{\displaystyle A1>=100}・・・A1の値が100以上
  • A1<=100{\displaystyle A1<=100}・・・A1の値が100以下
  • A1=100{\displaystyle A1=100}・・・A1の値が100である
  • A1<>100{\displaystyle A1<>100}・・・A1の値が100ではない

また、真偽を反転させたい場合は=NOT(formula){\displaystyle =NOT(formula)}で記述します。=NOT(true){\displaystyle =NOT(true)}はfalse、=NOT(false){\displaystyle =NOT(false)}はtrueになります。

レタスを例にすると、D2のセルを選択し、以下のように記述します。昨年を基準として今年はそれ以上なのかどうかを判定するわけですから、論理式にはB2<=C2{\displaystyle B2<=C2}と入力します。真偽の部分には矢印を入れます。

  • =IF(B2<=C2,”↑”,”↓”)

レタスは昨年より値段が下がっているので論理式を満たさず偽に書かれている内容が出力されます。

ABCD
1野菜昨年同時期の値段現在の値段比較
2レタス138125↓
3トマト152160
4きゅうり9999

他の野菜は相対参照を活用することができますので、似た式の入力を2回も3回もやる必要はありません。

ABCD
1野菜昨年同時期の値段現在の値段比較
2レタス138125↓
3トマト152160↑
4きゅうり9999↑

IF関数は真・偽の2つの分岐をする関数ですので、3分岐以上させるにはIF関数を複数使う必要があります。以下の表はある娯楽施設の入場料を示したものです。

一度に入場する人数1人当たりの入場料
30人未満1,200円
30人～39人1,100円
40人以上1,000円

こちらは上記の娯楽施設の団体予約表です。

ABC
1期日予約人数1人当たりの値段
27月18日25
37月19日46
47月20日38

まずは40人以上から設定しましょう。C2のセルにIF関数を用います。40人以上ならば入場料を1,000円にするので、以下のように設定します。

  • =IF(C2>=40,”1,000円”,)

ここで偽となった場合、更に2種類の選択肢があります。更に分岐させる場合は1度IF関数を呼び出します。

  • =IF(C2>=40,”1,000円”,IF())

2つ目のIF関数において今度は30人～39人の入場料は1,100円を設定していきましょう。既に40人以上の設定は1つ目のIF関数で終わっているので30<=B2<=39と書く必要はなく30<=B2だけでよいのです。ここで真の場合は30人～39人、偽の場合は29人以下ですので、これで設定は全て終了です。エラーが出る場合は括弧や” “が正しく閉じているか、カンマに漏れや余計なものがないかに気をつけましょう。

  • =IF(C2>=40,”1,000円”,IF(C2>=30,”1,100円”,”1,200円”))

セルに反映してみましょう。4つ以上の場合も偽の場合に更にIF関数を使用することによって分岐できます。ただし、IF関数を同時に使用できるのは64回（Excel2003バージョンは7回）までなことには注意しましょう。

ABC
1期日予約人数1人当たりの値段
27月18日251,200円
37月19日461,000円
47月20日381,100円

複数の条件がある分岐

条件が1つでない場合は論理式にAND関数ないしOR関数で複数の条件を記述します。

  • 複数の条件があり全てが満たされている場合真となるものはAND関数
  • 複数の条件がありどれか1つでも満たされている場合真となるものはOR関数

AND関数の例を見てみましょう。以下はある資格試験の点数状況の受験番号の若い人から数人を示したものです。配点は第1問400点・第2問300点・第3問300点とし、合格ラインは全体7割以上かつ各問5割以上です。

ABCDEF
1受験番号第1問
(配点400)		第2問
(配点300)		第3問
(配点300)		全体
(満点1000)		判定
21001A325269172766
31002B173260291724
41003C232163200595

論理式には合格ラインを入れます。点数の条件が全て合格ライン以上でないと合格にならないため、AND関数を使用します。AND関数は=AND(formula1,formula2,...){\displaystyle =AND(formula1,formula2,...)}で表記します。各formulaには条件式を入れます。

この試験の場合は第1問200点以上・第2問150点以上・第3問150点以上・全体700点以上の全てを満たせば合格です。

  • AND(B2>=200,C2>=150,D2>=150,E2>=700){\displaystyle AND(B2>=200,C2>=150,D2>=150,E2>=700)}

これを条件にしたIF文を記述します。受験番号1001Aの人の判定をしてみましょう。

  • =IF(AND(B2>=200,C2>=150,D2>=150,E2>=700),”合格”,”不合格”)

受験番号1001Aの人は合格ラインの全てを満たしていたので合格です。

ABCDEF
1受験番号第1問
(配点400)		第2問
(配点300)		第3問
(配点300)		全体
(満点1000)		判定
21001A325269172766合格
31002B173260291724
41003C232163200595

他の人も見ると受験番号1002Bの人は第1問が下回っていたので不合格、受験番号1003Cの人は全体が下回っていたので不合格となります。

ABCDEF
1受験番号第1問
(配点400)		第2問
(配点300)		第3問
(配点300)		全体
(満点1000)		判定
21001A325269172766合格
31002B173260291724不合格
41003C232163200595不合格


OR関数も同様にして=OR(formula1,formula2,...){\displaystyle =OR(formula1,formula2,...)}で記述します。

先程の試験は第1問200点以上・第2問150点以上・第3問150点以上・全体700点以上の全てを満たせば合格でした。この合格ラインを逆に見ると第1問200点未満・第2問150点未満・第3問150点未満・全体700点未満のどれか1つでも満たしてしまうと不合格になるということです。これを条件にしてみましょう。

  • OR(B2<200,C2<150,D2<150,E2<700){\displaystyle OR(B2<200,C2<150,D2<150,E2<700)}

OR関数が真の時不合格になるわけですから、真偽の振る舞いが先程とは逆になることに注意しましょう。

  • =IF(OR(B2<200,C2<150,D2<150,E2<700),”不合格”,”合格”)

受験番号1001Aの人の判定に上式を入れても2つ上の表と同じになります。

演習問題

共通参考資料
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！

この記事を書いた人

MirrorMasterのアバター MirrorMaster

関連記事

コメント

コメントする

CAPTCHA


目次