BellCurve 統計WEB

  • Excelノート
  • Excel関数の使い方

1-1. Excelで基本統計量を計算しよう

2021年7月から、統計検定の新しい試験である「データサイエンス基礎」が始まりました。「データサイエンス基礎」は、Excelを使いながら実際のデータを解析する能力を評価するための試験です。このExcelノートではデータ解析を行う際によく使うエクセル関数やエクセルの機能について説明していきます。

■基本統計量に関するExcel関数一覧

関数使い方(A1:A3のデータを用いる場合)説明
SUMSUM(A1:A3)合計値
AVERAGEAVERAGE(A1:A3)平均値
VAR.P
VARP
VAR.P(A1:A3)
VARP(A1:A3)
母集団における分散
標本分散
VAR.S
VAR
VAR.S(A1:A3)
VAR(A1:A3)
母集団から抽出した標本における分散
不偏分散
STDEV.P
STDEVP
STDEV.P(A1:A3)
STDEVP(A1:A3)
標本分散の平方根
STDEV.S
STDEV
STDEV.S(A1:A3)
STDEV(A1:A3)
不偏分散の平方根
STANDARDIZESTANDARDIZE(値,平均値,標準偏差)標準化
平均が0、分散が1となるようにデータを変換
MAXMAX(A1:A3)最大
MINMIN(A1:A3)最小
MEDIANMEDIAN(A1:A3)中央値
QUARTILE.INC
QUARTILE
QUARTILE.INC(A1:A3,戻り値)四分位数

・戻り値の設定
 0:最小値
 1:第1四分位数
 2:中央値
 3:第3四分位数
 4:最大値

■Excel関数の使い方

次のデータはあるクラスの生徒30人分の数学の試験結果(100点満点)です。このデータを使って、成績が一番良かった生徒の偏差値を求めてみます。


  1. データの平均値と標準偏差をを求める
  2. 30人分のデータの下に「平均」と「標準偏差」という項目を作っておきます。

    平均値を出力させたいセルを選択し、「=AVERAGE(B2:B31)」と入力します。もしくは「=AVERAGE(」と打ったあとに、データの範囲をドラッグで選択してもOKです。

    Enterを押すと、結果が表示されます。

    同様にして、VAR.P関数もしくはVARP関数を使って標準偏差を算出します。このデータは標本データではないので、不偏分散ではなく標本分散(母集団の分散)を算出します。

  3. すべてのデータを標準化する
  4. STANDARDIZE関数を使って、B2の値を標準化します。標準化するときには1で算出した平均値(B33)と標準偏差の値(B34)を使います。平均値と標準偏差の値は固定しておきたいので、「$」マークを付けてそれぞれ「$B$33」「$B$34」と記入します。

    C2のセルの右下部分にカーソルを持っていくと「+」マークが現れるので、ダブルクリックします。すると、30個すべてのデータに対して標準化した値が自動で入力されます(オートフィル機能といいます)。このときに、常に同じ平均値(B33)と標準偏差(B34)を使いたかったため(固定しておきたかったため)、上で「$」マークを付けるという作業を行いました。

  5. 標準化した値に10をかけて50を足して偏差値を算出する
  6. 掛け算は「*」、足し算は「+」で表します。D2のセルだけ偏差値を算出し、オートフィル機能を使って残りの値も自動で入力します。

    
    
    

  7. 偏差値の最大値を求める
  8. MAX関数を使って最大値を求めます。最大値を出力させたいセルを選択し、「=MAX(D2:D31)」と入力します。

    
    
    

    ここまでの作業で、成績が一番良かった生徒の偏差値が「50.8」であることが分かりました。

    「思ったより偏差値が低いな」と思った方は鋭いです。この例題では30人分のデータを使って偏差値を算出しています。普段、偏差値を算出するときは、学校全体や日本全体のデータを使うためサンプルサイズがかなり大きいです。中心極限定理によりサンプルサイズが大きいほど分散は小さくなるので、結果として得られる偏差値がこの例題で算出した値よりももっとばらつくことが予想されます(もちろん試験の難易度や点数のばらつきによっても異なりますが)。

Excel関数の使い方


統計学やデータ分析を学ぶなら、大人のための統計教室 和(なごみ) [業務提携]


統計WEBを運営するBellCurveは、統計解析ソフト「エクセル統計」を開発・販売しています! 統計解析ソフト「エクセル統計」をインストール後のExcel上のタブとメニュー エクセル統計ジャケット画像


【BellCurve監修】統計検定®2級対策に最適な模擬問題集1~3を各500円(税込)にて販売中!

Kindleストアで配信中

統計検定®2級 模擬問題集1

500円(税込)

統計検定®2級 模擬問題集2

500円(税込)

統計検定®2級 模擬問題集3

500円(税込)


【PR】