別稿「入学試験での成績と定期考査のパフォーマンス」で触れた、入試結果と定期考査成績との相関を調べる方法、学び方の転換などに問題を抱える生徒を抽出する「残差」を導出する方法をご紹介します。
❏ 計算式を設定しておけば、データを取り込むだけ
下図は、ある学校で実際に作成したものですが、12行目以降にクラス、出席番号、生徒氏名を、Ｄ列以降に得点が入力されています。
入試成績と考査成績のデータシートから、VLOOKUP関数を使って取り込むようにすれば、入力ミスのリスクも作業量の増大もありません。
散布図は、ＤＥ列のデータ（国語の場合）を選択して、「挿入」タブから「散布図」を選択すれば作成できます。
平均や標準偏差、四分位数などの記述統計量は、関数を設定しておけば自動で計算しくれます。実際のデータシート（エクセルファイル：ウイルス検査済み）はこちらからダウンロードしてご覧ください。
| セル | 出力項目 | 関数 |
| D2 | サンプルサイズ | =COUNT(D$16:D$175) |
| D3 | 平均 | =AVERAGE(D$16:D$175) |
| D4 | 標準偏差 | =STDEV.P(D$16:D$175) |
| D5 | 最大値 | =QUARTILE(D$16:D$175,4) |
| D6 | 第3四分位数 | =QUARTILE(D$16:D$175,3) |
| D7 | 中央値 | =QUARTILE(D$16:D$175,2) |
| D8 | 第1四分位数 | =QUARTILE(D$16:D$175,1) |
| D9 | 最小値 | =QUARTILE(D$16:D$175,0) |
| D10 | 相関係数 | =CORREL(D$16:D$175,E$16:E$175) |
❏ 回帰式の係数と切片を求めて、残差を出力
ここまではお馴染みかと思いますが、「残差」（近似線からの距離）を算出するには、回帰直線（＝近似線）の傾斜と切片を算出しておく必要があります。
エクセルに実装されている データ分析ツール の「回帰分析」を使う方法や、LINEST関数 を用いる方法もありますが、以下の数式をシートに予め設定しておく方が、繰り返して用いるときの操作を減らせます。
| セル | 出力項目 | 関数 |
| D11 | 回帰直線の傾斜 | =SLOPE(E$16:E$175,D$16:D$175) |
| D12 | 回帰直線の切片 | =INTERCEPT(E$16:E$175,D$16:D$175) |
| F16 | 残差 | =E16-(D16*D$11+D$12) |
F17～F175の各セルには、F16セルと同じ数式をコピーしてあります。
❏ 条件式書式やフィルターを利用して要注意者を抽出
上記の例では、残差のデータ列に「条件付き書式」でセルに網掛をしています。フィルターを設定すれば、セルの色でフィルターをかけるなどの作業も容易になり、「要注意」の生徒を抽出するのも簡単です。
❏ データを用いた個別指導と、行動評価の観点作り
ここまでくれば、後はデータを実際の指導に活用するだけです。
- 伸びていない生徒には学習方法や生活リズムに問題がないか面談指導を通じて確かめる
- 伸びている生徒群の行動を観察した結果を持ち寄り、好ましい学習者像（＝生徒に求めるべき行動）の描出に使う
前者は、個々の生徒の問題を解決するのに役立ちますし、後者は生徒自身が学習者としてのセルフチェックを行うときの規準に転用できます。
先生方にとっても、指導スキルの一つである「生徒観察」における観点の定立に利用でき、学年や教科で指導を進めるときの目線合わせにも役立つのではないでしょうか。
教育実践研究オフィスＦ 代表 鍋島史一