直線上に配置

     数  学

Excelで学ぶ統計処理

  都数研コンピュータ分科会 

植 松 嘉 夫(淵江高)

T.はじめに

 本校では3年生の自由選択科目の中の1つとして数学科の中に

「コンピュータ」という科目を置いている。コンピュータを活

用して授業を行う科目である。3年前から数学Cの中の「統計

処理」の学習をExcelという表計算ソフトを用いて、週1回2

時間行っている。平成13年度は、過去の経験も踏まえ授業は

教科書に沿ってすべてExcelで行うことにした。授業の形態は、

生徒が主体的に学習を進められるような課題学習である。1,2

学期で教科書を終了し、3学期は生徒各自が課題を見つけ、そ

れを処理する課題研究授業を行う予定である。

U.表計算ソフト「Excel」を使用する理由

数学Cの「統計資料の整理」では、11社中4社の教科書に表計算ソフトを利用した説明がある。表計算ソフトの1つであるExcelは社会的にも広く利用されており、生徒が高校卒業後も進学にしろ就職にしろExcelを利用する機会があると考える。Excelの操作にも習熟するので高校卒業後もExcelを用いての様々な利用が可能である。
 Excelは、豊富な関数がそろっているので大量のデータ処理が行え、表やグラフの作成が簡単にできる。また、コンピュータの乱数発生機能やExcelマクロを用いて簡単なシミュレーションが行える。

V.単元の目標

統計についての基本的な概念を理解し,身近な資料をExcelを利用して整理・分析し,資料の傾向を的確にとらえることができるようにする。また、統計的な推測について理解し,統計的な見方・考え方を豊かにするとともにそれらを統計的推測に活用できるようにする。
 2003年度実施の新学習指導要領では、次の科目で扱うことになる。

 数学基礎 (3) 身近な統計

 統計の考えを理解し、それを活用できるようにする。 

ア 資料の整理・・・目的に応じて資料を収集し、それを表やグラフなどを用いて整理する。

  イ 資料の傾向の把握・・・資料の傾向を代表値を用いてとらえる。       

数学B  (3) 統計とコンピュータ

身近な資料を表計算ソフトなどを利用して整理分析し資料の傾向を的確にとらえる。

ア 資料の整理・・・度数分布表、相関図

 イ 資料の分析・・・代表値、分散、標準偏差、相関係数

数学C    (4) 統計処理

統計的な見方や考え方を豊かにし、それらを統計的な推測に活用できるようにする。

ア 正規分布・・・連続型確率変数、正規分布

 イ 統計的な推測・・・母集団と標本、推測

W.授業展開の基本的考え方

教科書に沿って生徒主体の課題学習を行う。授業は2時間連続なので、課題は1時間ないし2時間以内で完成できるのを用意する。次回に持ち越さないのである。課題が未完成の場合は引き続き放課後に行わせる。また、前回欠席した生徒がいても今回の課題に対しては全員同じようにできるようにするためである。
 週1回の授業では,前の授業でやったことを忘れてしまう生徒がほとんどなので先を急がずにExcelの操作や統計量の理解を定着させるため異なる資料で復習をさせながら学習を進める。キーボード上からローマ字が打てる生徒が対象である。Excelの予備知識は仮定していない。Excelの使い方については、必要に応じてその都度説明するようにした。資料の傾向を的確にとらえるため、Excelを用いて表やグラフの作成が容易にできるようにする。Excelでは相関図に回帰直線が簡単に描けるので、学習指導要領にはない回帰分析についても少し触れ商品の売り上げ予測などを考えさせる。

X.学習活動の実際

指導内容 数学C 「統計処理」

1学期 統計資料の整理     18時間実施

 2学期 確率分布,統計的な推測 16時間実施

 3学期 課題研究         4時間予定

 3学期は生徒各自が収集した「身近なデータ」を用いて整理・分析させ、統計処理のなお一層の習熟を図る。

2学期までに実施した授業内容


時間

学習段階

学習活動

留意点

2

 

1

 

1

 

2

 

 

2

 

 

Excelの使い方


・度数分布表とヒストグラム

 

・代表値と散布度

 

・復習

 

 

・素データより度数

分布表をつくる

・表とグラフの作成

 

・度数分布表からヒストグラム

と度数多角形を作成

・平均・分散・標準偏差を求める

・度数分布表からヒストグラム

を描き平均・分散・標準偏差を

求める

・素データより度数分布表

をつくり,ヒストグラムを

描き平均・分散・標準偏差

を求める

Excelの操作法を少しずつ指導

 

・グラフは修正よりやり直す方が早い

・定義どおりに求めさせる

 

・別の資料を用意する

 

 

・度数分布表のつくりかたを理解させる。

Excel関数や分析ツールの使い方を指

導する。分析ツールがツールにないと

きはツール→アドインで分析ツールを

選択する。

2

 

 

3

 

 

 

 

3

・相関図と相関係数

 

・相関表の作り方

 

・回帰分析について

 

 

 

 

復習

・相関図を描き,相関係数を求める

・相関表をつくる

 

・相関図に回帰直線を描く

 

・3店の売り上げ予測をする

 

 

・教科書の節末問題

 

Excel関数を使わずに相関係数の定義

に従って求める。

・相関図と比較させる。

 

・データの散らばり具合と回帰直線と

の関係に注意させる。

・相関図・回帰直線・相関係数をもとに

して3店の売り上げ予測の信頼性につ

いて検討させる。

Excelの操作や統計量の求め方を再確

認させる

2

・二項分布

・2項分布 B(10,p)

P=0.2,0.5,0.8のグ

ラフの作成

・独立試行の確率(数1)について復習

する。

2

 

 

 

2

 

 

 

 

2

 

 

2

・正規分布

 

 

 

・正規分布の利用

 

 

 

 

・二項分布の正規近似

 

 

・復習

 

・身長の度数多角形

と正規分布曲線との比較

・標準正規分布

N(0,1)の正規分布曲線の作成

・教科書にある例題や練習

 

 

 

 

・二項分布B(n,0.5)

n=10,20,30,40,50

作成

・教科書の節末問題

・階級幅を小さくしていくときの度数

多角形と正規分布曲線との比較をさ

せる。

 

・正規分布表は使わずにExcel関数を使

って解答させる。

・コンピュタの計算誤差があるので例

題の答えに一致しなくても近い値であ

ればよいことに注意させる。

nが大きくなるとどんなグラフに似て

くるかを調べさせる。

・標準正規分布N(0,1)を用いて各種の

問題が解けるようにする。


実施した課題のうち6例を次に示す。

1.                             度数分布表からヒストグラムを描き平均・分散・標準偏差を求める。(1学期第3回目の授業)

 本時の目標

(1)          データの整理法の理解

(2)          Excelによるグラフ作成法の習得

時間

()

学習活動

留意点

10

・コンピュータの起動・出欠点呼

 課題の説明

 

40

・課題の作成

 与えられた表の階級と度数を入力

 階級値・総度数・相対度数の計算

 罫線を引く

 ヒストグラムと度数多角形の描画

 表やグラフに色をつける

・プログラムの強制終了にあっても困ら

ないように上書き保存は頻繁に行わせる

 

・色づけは生徒の自主性に任せる。

時間がないときは省略してよい

10

   休み時間

 

15

・平均・分散・標準偏差の定義の確認

 Excelでの求め方の復習

・先週の課題を見て思い出させる

30

・課題の作成

 度数分布表で階級値x,度数fとして

 xf, x-E(x), (x-E(x))^2fの表を追加する。

平均・分散・標準偏差の計算

・表の形は教科書のようにする

 

・時間があれば表に色をつける

5

・コンピュータの終了処理

 フロッピーの提出

 

 

 

グラフウィザードを用いてヒストグラムと度数多角形を作成する。まず、ヒストグラムをつくる。グラフの種類は「棒グラフ」を選択する。棒と棒の隙間をなくしてヒストグラムにするには,棒グラフ作成後に棒の部分で右クリックし,データ系列の書式設定−オプションを選択し,棒の間隔を0にする。次に、作成したヒストグラムの棒グラフの上で右クリックし、「元のデータ」を選択する。「系列」画面に切り替え系列の追加をクリックする。グラフが棒グラフで表示されるのでそのグラフ上で右クリックし「グラフの種類−折れ線」を選択する。グラフの色づけは自動で行われるが、グラフエリアで右クリックしグラフエリアの書式設定−パターンを使用してカラフルなグラフにすることができる。時間に余裕のある生徒はオリジナルな色づけをして楽しんでいる。タイトルの文字もフォントを変えて見栄えよくしている。

Excelでの計算式は必ず「=」から始まることに注意する。総度数は、度数の合計であるからアイコンの狽クリックして簡単に求められる。はじめに計算結果を表示するセルをクリックしておくことに注意する。

階級値      =(階級の下限 + 上限) / 2

相対度数     =度数 / 総度数

総度数は絶対番地なので総度数のセルをクリックした後f4キーを押す。

平均・分散・標準偏差の計算は定義どおりに行う。

平均E(X) =x*f / f

分散    =(x-E(X))^2*f / f

標準偏差  =SQRT(分散)

小数点第何位までにするかは計算途中では気にさせずにやり、最後の結果だけ丸めを行わせる。

 

2.素データより度数分布表をつくってヒストグラムを描き,平均と標準偏差を求める。

 


















 

上図の(1)は,Excel関数を使って素データの平均と標準偏差を求めた。

Excel関数

        =AVERAGE(データ範囲)

標準偏差  =STDEV(データ範囲)

  最大値   =MAX(データ範囲)

  最小値   =MIN(データ範囲)

上図の(2)は,Excelに組み込まれている分析ツールの中の「ヒストグラム」を利用して度数分布表をつくる。この「ヒストグラム」は階級の各区間の最大値以下の度数を数えるものであるので、データ区間をつくるときは注意が必要である。分析ツールが組み込まれていないときは、メニューバーよりツール−アドイン−分析ツールを選択し、OKをクリックする。

まず,階級の幅を決めるため素データの最大値と最小値を求める。階級数は7から10がよいとされている。

メニュ−からツール−分析ツール−ヒストグラムと選択し,データ区間・入力範囲(素データ全体・出力先(データ区間と書いたすぐ上のセルがよい)を入力してOKを押す。度数分布表から平均・標準偏差を求める場合簡便法もあるが、定義をしっかりと理解・定着させるため、ここではまだ定義によって求めさせた。

素データと度数分布表にまとめたもの双方の平均・標準偏差の食い違いについても検討させた。

3.                             相関の応用

 相関図や相関係数を求めることにより資料の傾向の把握ができるようになったのでここではその応用として次の課題を考えさせる。

A,B,C3商品の広告費と売上げ個数の相関を調べて広告費2150万円にしたときの3商品の売り上げ予測を予測させ、その信頼性について検討する。

相関係数の定義による求め方はすでに学習済みであるので、ここではExcel関数のCORRELを使用することにする。

Excel関数

相関係数 

=CORREL(広告費の範囲,売上げ個数の範囲)

売上の予測

=FORECAST(広告費, 売上げ個数の範囲, 広告費の範囲)

回帰直線の表示の仕方

グラフエリアをクリックし,メニューのグラフ−「近似曲線の追加」を選択する。オプションの「グラフに数式を表示する」もチェックする。

                        下にA商品とB商品についての結果を示す。予測した値は妥当かどうかを自由に記述させた。相関図に回帰直線を追加することで各点の直線への近づき具合が容易に見て取れ、相関の様子がよりはっきりする。

4.二項分布の正規分布による近似

二項分布B(n,0.5) n=10,20,30,40,50のグラフを作らせ、nの数が大きくなると二項分布のグラフは左右に広がって対称になり正規分布のグラフのように見えてくることを実感させる。

教科書にはグラフだけが出ているので教科書のグラフを見てそれと同じものをつくりなさいといって考えさせる。独立試行の確率の計算はここではExcel関数を使用して求める。

Excel関数

1回の試行でのXが起こる確率をpとする。n回の試行 Xr回起こる確率(1-p)n−r   

独立試行の確率 =BINOMDIST(rの値,nの値,pの値,0)

n=50のときxは0から50であるが、x=39からは小数点以下第4位までは0なのでグラフ描画範囲もxは40までとした。



 

 

 

 












5.教科書にある例題や練習

教科書では巻末の生起分布表を用いているがここではExcel関数のNORMSDISTを用いて確率を求める

Excel関数

Z〜標準正規分布N(0,1)のとき

P(ZZ) の値は

=NORMSDIST(Z)

分数の分子と分母を分ける線は下線

Z=」のところは上下2つのセルを結合する

 

 

 

 

6.標本平均の分布

1から999までの奇数が1つずつ書かれた札500枚からn枚を復元抽出し書かれた値の平均を求める。これを200回繰り返しn10,20,40,80と変えて標本平均の分布の変化を調べる。

これは、Excelマクロを用いて作成する。

メニュ−からツール−マクロ−マクロと選択し、マクロ名を入力して「作成」をクリックする。すると、マクロエディターに切り替わるので教科書にあるBASICプログラムをもとにして入力する。教科書のBASICとの主な違いは、行番号がないことのほかは画面表示命令の相違である。BASICPRINT文は、ExcelではCells文である。Excelはセルが構成単位なのでセルに表示内容を代入する。

例 E23セルを「平均」と表示

命令は、 Cells(23,5)=”平均

Cells(a,b) a行b列のセルを表す。

列も数字で表し,E5である。

プログラム入力後画面右上の×をクリックするとExcel画面に戻る。ツール−マクロ−マクロの実行を選択するとマクロが実行さ  れる。プログラムにミスがあるとエディター画面に切り替わる。

 このマクロでもう一つBASICと異なるところはBASICの配列変数である。BASICの配列変数は、Excelマクロでは、セルがその役目をします。例えば、BASICの配列変数A(1)A(2),・・・,A(20)はセルのA1A2,・・・,A20にすればよいのです。

                               マクロ実行ボタンの作成

メニューから挿入−図−クリップアートを選択し,その中から適当な図を選びExcel画面に貼り付ける。図で右クリックし,「マクロの登録」をクリックする。マクロ名を選択してOKをクリックする。作成したボタンをクリックすると実行される。途中で実行を止めるときは,「Esc」キーを押す。
 

 

 

 

 















Y.学習評価の方法とその結果

 各回の課題への取り組み方や課題の完成度に応じて評価する。欠席して課題ができなかったときは,次回の授業日の放課後にその課題を行う。欠席する生徒もほとんどなく全員熱心に課題に取り組み,2学期までの全課題をこなした。課題数は、1学期は12、2学期は15である。
 生徒は課題解決に当たってExcel操作の分からないところをオンラインヘルプで調べたり,生徒同士で聞きあったりして生徒の主体的な活動ができた。また,生徒はExcelの操作にも慣れ統計資料の整理・分析が容易にできるようになった。各回2時間続きの授業であったが,生徒は休み時間も取らずに熱心に課題に取り組んでいた。特に売上げ予測やマクロを使ったシミュレーションに大変興味を示した。

Z.今後の課題

 今年の生徒はみな理解力があり、Excelの操作への抵抗もあまりないように感じた。課題作成時間もほぼ同じで大幅に遅れる生徒はいなかった。1時間でどれだけのことができるのかをつかむのにあまり時間を要しなかった。1講座10人前後の生徒数なのでそれは当然のことかもしれない。

 しかし、パソコンに長けた生徒がいたりキー入力に時間のかかる生徒がいる場合はうまくいかない。時間が足りなくてもいけないし時間が余ってしまうのもいけない。決められた時間内で作成でき、早くできた生徒も余った時間で色づけなど付加的なことができる課題を蓄積していくことが今後の課題である。

さらに、生徒自らが興味・関心のある資料を収集してそれを整理・分析し,統計的推測を行う課題研究まで行えればよいのであるが、授業時間が行事等で削られるため資料の収集・整理あたりで終わってしまい課題研究を深めるところまで行かないのが現状である。また、Excelを用いた統計処理の授業が誰でもできるような教材の整備も今後の課題である。

 
トップ アイコントップページヘもどる

 

トップ アイコントップページヘもどる