2016年6月10日金曜日

エクセルの散布図のオプションの「グラフに数式を表示する」の活用法

ご多分に漏れず,本学でもエクセルを使ったデータ処理の実習をしています.
「相関関係」と「回帰分析」では,グラフとなる散布図に近似曲線を入れて,そのオプションのなかの「数式」や「R-2乗値(もしくはr値)」を表示させることがあります.
例えば以下のように.
で,学生からこんな質問を受けることがあるのです.
「なんで『グラフに数式を表示する』にチェックを入れなきゃいけないんですか? てか,この数式は何を意味しているんですか? どうしてグラフに数式を入れなきゃいけないんですか?」

まったく同じ質問を私も学生の時にしたことがあります.
先生に怒られました.「お前はバカなのか?」って.

今になって思うと・・・,そうですね.

これは,
y=ax+b
という基本中の基本の中学数学ですが,この数式が何を意味し,どのように活用できるのか解説されることは意外と少ないものですから,数学に弱い文系学生なんかは,それを知らないまま放ったらかしの人って結構多いんですよ.
それに,あらためて統計学の授業を受けていない学生にとっては,なかばルーティーンの如く表示させるこの「数式」の意味が分からないのは当たり前かと思います.

本来なら相関関係と回帰分析を丁寧に教えるべきところですが,なかなか伝えきれないまま終わることが多いものです.
上記のような課題ですと,「相関関係」に関する説明が主だったものになり(学生も解釈しやすいから),回帰分析の詳細な説明までいかないことがあります.

そこで今回の記事はそれを補完するためにも,
エクセルの散布図の「近似曲線の書式設定」のオプションにある「グラフに数式を表示する」の活用法
について取り上げます.
実はこの「数式」,チェックボックスをクリックするだけで簡単に算出されるわけですが,非常に利用価値の高いものなんです.

例題とした上記画像の数式をここに示します.

y = -33.337x + 158.24

これは,
「y(ジャンプ力)は,x(疾走タイム)に-33.337をかけて,それに158.24を足した値である」
ということを意味します.
ということは,「y」「x」のいずれかの値が分かれば,もう片方の値を推定することができるということなのです.

例えば「私」が疾走タイムを測定したとします.
ここでは仮に,「2.9秒」だったとしましょう.
この疾走タイムの値(x)が分かれば,ジャンプ力が推定できます.
以下の図のように,

=-33.337*B15+158.24

を計算してみましょう.

その結果,ジャンプ力は約「61.6cm」と推定されるわけです.
以下に算出結果の図を示します.


このことは,
疾走タイムを「x」にしたい場合,ジャンプ力「y」はどれくらい必要なのか?
という推定ができることを意味します.
一般的に,ジャンプ力が高い人は走る速度も速いとされています.ですから,速く走るためのトレーニングとしてジャンプ動作を用いることは多いものです.

例えば,疾走タイムを「2.5秒」にしたいのであれば,

=-33.337*2.5+158.24

を計算します.
その結果,約「75cm」のジャンプ力が必要だという分析ができるわけです.


もちろん,これは推定ですし人間の運動能力が直線回帰することは極めて稀ですので,理論上の話ということになりますが.

もう一つ,別の観点からの活用法もご紹介します.
2000年頃から,文部科学省は学校等で行なっている体力テストの内容を改定しています.
現在,「垂直とび」は行われておらず,代わりに「立ち幅とび」を行なっています.
ですから,「子供の頃,垂直とびはどれくらい跳んでた?」っていう話題を今の若者にしても「え? 垂直とびってなんですか?」という反応が返ってくることが多いので注意しましょう.
「おまえ何言ってるんだよ.手にチョークの粉を付けて壁際で跳ぶやつだよ」って言っても理解されません.
同様のものに,「立位体前屈(旧テスト)」と「長座体前屈(新テスト)」があります.
それでだいたいの年齢がバレますので,女性は特に気をつけてください.

さてさて,そうはいっても今の若者から「垂直とび」がどれくらい跳んでいたのか何が何でも聞きたいという場合,例の数式で推定することができます.
例えば以下のようなデータ.
【注意】以下のデータは本ブログ記事の例題用として作成したものです.学術的なデータではありません.


仮に相手が「そうですねぇ,215cmくらいだったと思います」って答えたら,以下のように計算しましょう.
以下の図では,

=0.4725*A13-46.72

を計算しています.


その答えは約「55cm」となります.
つまり,立ち幅とびで215cm跳んでいた人は,垂直とびなら55cmくらい跳ぶだろうということです.

今回は「体力テスト」のデータを使いましたが,これを別のデータで応用すれば,いろいろな分析ができるようになります.