【Excel統計】Excel関数の利用と分析ツールの利用で手軽に統計処理

収集したデータを分析するためにはいろいろな方法がありますが、中でもおすすめなのがエクセルの使用です。

しかし、エクセルをあまり使った経験がない人からすると、なかなか手が出しづらいかもしれません。

そこで今回は、エクセルを使った統計処理について基礎から詳しく解説していきます。

エクセルで統計を出す場合に一般的に使用される関数や分析ツールについて、具体的に紹介するので、ぜひ参考にしてください。

この記事は、

  • 統計処理をできるだけ簡単にしたい人
  • エクセル関数を使って統計を出したい人
  • エクセルの分析ツールの使い方を知りたい人

におすすめの内容です。

それではまず、エクセルで統計処理をする方法の種類について見ていきましょう。

Excelでの統計処理は関数の利用と分析ツールの利用の2通り

冒頭で触れた通り、エクセルを使ってデータの統計を出す方法は、関数か分析ツールのどちらかを利用する2通りです。

今回はまず、統計を出すときによく使用される関数を紹介した後、分析ツールの種類について説明していきます。

どちらもエクセルを使って統計を出すときによく使用されるので、ポイントをしっかり押さえておきましょう。

関数を利用する方法

「エクセルに関数がある」と知っていても、「統計を出すときにどの種類の関数を使えばいいか分らない」という人もいるでしょう。

今回は、統計を出すときによく使用される関数を5つ紹介します。

  • AVERAGE関数
  • GEOMEAN関数
  • VAR.P関数
  • COVAR関数
  • MEDIAN関数

それぞれの内容だけでなく、分かりづらい箇所に具体例を入れて解説しているのでしっかり見ていきましょう。

AVERAGE関数

AVERAGE関数とは、範囲指定したセルに入力されている数字データの平均値を返す関数です。

「数値1」に平均値を出したいセルの範囲を指定して「OK」をクリックするだけで、平均値を算出することができます。

GEOMEAN関数

GEOMEAN関数とは、指定したセルに入力されている数字データの相乗平均(幾何平均)を求める関数です。

GEOMEAN関数を使用して相乗平均を求める場合、指定した引数を掛け合わせて個数に応じたべき乗根を計算すれば算出することができます。

ちなみに、エクセルにおけるGEOMEAN関数の引数は255個まで指定可能です。

VAR.P関数

VAR.P関数とは、指定したセルに入力されている数字データを母集団の全体とみなして、標本分散(分散)を求める関数です。

標本を使って統計分析をしたい場合にVAR.P関数を使用することが多くあります。

ちなみに、VAR.P関数を使って求めた統計結果はSTDEVP関数(偏差)の2条と等しくなるので覚えておきましょう。

COVAR関数

COVAR関数とは、対応する2組のデータにおける標準偏差の積の平均値である共分散を求める関数です。

相関係数と共分散は似た性質を持つ数値ですが、共分散は数値範囲が定まっておらず、計算過程で使われることが多いという特徴があります。

データから統計を出すときに適切でない関数を使用すると、結果が大きく異なる可能性があるので気を付けましょう。

MEDIAN関数

MEDIAN関数とは、引用した数字データに含まれる数値の中央値(メジアン)を求める関数です。

データが昇順に並んでいる場合に使用します。

先に紹介したAVERAGE関数とMEDIAN関数を混同する人も多くいますが、2つの関数はまったくの別ものです。

AVERAGE関数で求められる平均値は、指定したセルの合計を出して等分したものなのでデータに極端な数値が入っていると統計の結果に影響が出てしまうという特徴があります。

具体例を挙げて説明していきましょう。

例えば、年収が1,000万円、300万円、200万円、100万円の4人のデータがあるとしましょう。

AVERAGE関数を使って平均値を求めると400万円になりますが、4人中3人が400万円以下の年収であるのに、1人の1,000万円につられて実態と大きくかけ離れた統計になってしまいます。

一方、同じデータでMEDIAN関数を使って中央値を求めた場合の計算式は、「(200万円+300万円)÷2」で、250万円になります。

つまり、この例の場合はAVERAGE関数を使用して平均値を出すよりも、MEDIAN関数を使った方が実態に近い数値が導き出せるというわけです。

統計を出す数値が比較的まとまっているならAVERAGE関数、極端な数値が混在しているならMEDIAN関数を使用して使い分けをするといいでしょう。

分析ツールを利用する方法

エクセルに分析ツールがあることを知らない人もいるかもしれませんが、エクセルには分析ツールがあります。

エクセルのウインドウのメニューバーにあるツールタブに「分析ツール」があるのでクリックして開いてみましょう。もし、分析ツールがタブ内にない場合は、ツールメニュー中の「アドイン」をクリックして分析ツールを選択してください。

今回は、統計処理でよく使われるエクセルの分析ツールとして

  • 相関係数
  • t検定
  • 回帰分析

の3つを紹介します。

相関係数

相関係数とは、2つの変数の関係がどれくらい強いのかを表す数値です。

絶対値が1に近くなるほど2変数に強い相関がある状態になります。

エクセルを使って相関係数を出す場合は、まず、分析ツールから「相関」を選択してOKをクリックします。

その後、開いたウインドウの入力範囲欄に対象データが入力されているセルを入れます。このとき、対象データの1行目が数字ではなく、変数名である場合は同ウインドウ内の「先頭行をラベルとして使用」にチェックを入れましょう。

データ方向と出力先を指定すれば、結果が表示されます。

t検定

t検定は、2変数の平均値に差が生じているかどうかを見るときに使用します。

具体的な使用方法は、分析ツールをクリックして「t検定:等分散を仮定した2標本による検定」を選択してOKを押します。

検定する2群のデータを「変数1の入力範囲」と「変数2の入力範囲」に分けてセルの選択をします。

このときに出る数値が0.5よりも小さい場合は、帰無仮説が棄却されるということです。

回帰分析

回帰分析は、分析対象の変数間の関係についての結果である変数の予測がどれくらいできるかを調べたいときに使用します。

要因になる変数には、独立変数もしくは説明変数の2つがありますが独立変数が1つである場合は「単回帰分析」と呼ばれるのに対して、2つ以上ある場合は「重回帰分析」と言うことは押さえておきましょう。

他のツールと同様に、分析ツールをクリックして「回帰分析」を選択しましょう。

開いたウインドウの「入力Y範囲」に目的変数を入力し、「入力X範囲」に説明変数を入れると回帰分析が完了します。

まとめ

データから統計を出すための方法はいろいろありますが、エクセルを使用するとより手軽に検定ができます。

関数を使う方法と分析ツールの2つをうまく組み合わせながら、適切な統計を出してみましょう。

 


<参考>

  1. 【Excelまとめ】Excel関数、分析ツールで統計解析を行おう(電池の情報サイト)
    http://kenkou888.com/category13/Excel_%E7%B5%B1%E8%A8%88%E8%A7%A3%E6%9E%90.html
  2. エクセルで使える“統計”を学ぼう(エクセルサプリ)
    http://excel.resocia.jp/analyse/1523/
  3. エクセルマニア(EXCELで統計解析)
    http://excel-mania.com/stat/
  4. エクセル統計.doc
    http://www.cc.u-ryukyu.ac.jp/~minoru/edu/class/hi/excel.pdf