Excel(エクセル)で分散を求める

Excel(エクセル)で分散を求める

Excel(エクセル)で分散を求める

使用関数:VAR.P(VARP)、VAR.S(VAR)、VARPA、VARA、DVARP、DVAR

分散とは?

統計における「分散」とは「平均値」からのデータのバラツキ具合を示す数値です。英語ではvariance(バリアンス・ヴァリアンス)。
「平均値」からのデータのバラツキ「分散」のイメージ

データが平均値からさほど離れずに散らばっていれば「分散」の値は小さく、平均値から距離を持って散らばっていれば「分散」の値は大きくなります。「分散」は平均値だけでは把握できないデータ分析の指標を数値化したものです。

平均→「分散」→標準偏差の関係

統計で「平均・平均値」は欠かせない基準です。
ですが、単純に全数値の和をデータ個数で割った平均値ではデータ間の優劣を判断するのに必ずしも適正とは言えません。

下図は2つの商品を試食して100点満点で評価したデータ表です。「商品1」と「商品2」の平均値の比較ではどちらも同じ。評価は五分五分と考えていいでしょうか?
「商品1」と「商品2」の平均値の比較

データを仔細に見ると、「商品1」の評価は個人差が大きく、「商品2」の評価は平均しているようです。「平均値」の数字ではその辺の状況を示すことができません。そこで、「分散」の出番です。

「分散」を求める計算式

「分散」を求めるには、個々のデータから平均値を引いた値の二乗を全て足し算し、データ数で割り算します。(後述しますが、データ数-1で割り算する場合もあります)
「分散」を求める計算式

Excel関数を使った分散の出し方については後述するとして、ここでは取り敢えず「分散」の数値を表示してみます。
「商品1」と「商品2」の分散の比較

「平均値」は同じなのに「分散」の数値を比較すると「商品1」と「商品2」では大きく違っています。「商品1」は評価が分かれ、バラツキ度が極端です。コアなリピーターは付くかもしれませんが、万人受けする商品ではなさそうです。対して「商品2」はバラツキ度が低く、平均値の評価点は実態に則していると言えるでしょう。

「分散」と「標準偏差」

「分散」の数値が平均値では表せない指標を示す有用な分析データであることは分かりました。しかし、「386」「26」という数字は実感が無いというか、ストレートに響いてこない印象です。プレゼンで示しても説得力が今ひとつです。分析データとしてレクチャに使う場合、多くは「分散」ではなく「標準偏差」が採用されます。

「標準偏差」は「分散」に対する平方根の値で、標準偏差と分散は実質同じものです。「分散」をルートに入れ、単位もデータと揃えることで、人が扱いやすい数値にしています。
「商品1」と「商品2」の標準偏差の比較
「19.65」「5.1」はそれぞれ平均値からのバラツキの距離を示しています。「商品1」は平均値「72点」から「±19.65点」の範囲にデータが散らばっており、人によって評価が50点台、90点台に大きく振れるでしょう、と説明できるわけです。

標準偏差については以下の記事で詳しく解説しています

503 Error - Service Temporarily Unavailable

「平均」→「分散」→「標準偏差」の流れを把握しておくと、データ分析を文書にまとめる作業で迷わずにすみます。

分散を求めるExcel関数

Excelで「分散」を求める関数には、VAR.P(VARP)、VAR(VAR.S)、VARPA、VARA、DVARP、DVARの6種類があります。

VAR.P関数(標本分散)の使い方

母集団全体を範囲にして数値を対象に標準偏差を求める関数です。

数値で標本分散を求めるVAR.P(VARP)関数の計算方法、使用例を分かりやすく解説しています。

https://www.tschoolbank.com/excel/function-var-p/

VAR.S関数(不偏分散)の使い方

母集団から無作為抽出したサンプルを範囲にして数値を対象に分散を求める関数です。

数値で不偏分散を求めるVAR.S(VAR)関数の使い方、使用例を分かりやすく解説しています。

VAR/VAR.S関数の使い方|数値で不偏分散を求める
Excelで分散を求める関数VAR/VAR.Sの使い方を初心者にも分かりやすく解説。VAR関数とは? VAR.S関数とは? VAR.PとVAR.Sの違い、VAR.S関数で分散の計算方法、分散のエクセル関数の種類など

VAR.P(標本分散)とVAR.S(不偏分散)の計算の違い

VAR.P関数は母集団(分散を求める全データ)すべてを対象に分散を計算し、VAR.Sは母集団からサンプルを抜き出して分散を計算することは上述しました。

それだけでなく、VAR.P(標本分散)とVAR.S(不偏分散)には計算式自体に違いがあり、当然結果も異なります。
VAR.P(標本分散)とVAR.S(不偏分散)の計算結果の比較
このページの上で紹介した分散の計算式「個々のデータから平均値を引いた値の二乗を全て足し算し、データ数で割り算する」はVAR.P関数で計算した場合です。

VAR.Sでは計算対象のサンプル(標本)数が母集団より少ないことから、割る数に「-1」の補正が入ります。VAR.Sの計算は「個々のデータから平均値を引いた値の二乗を全て足し算し、データ数-1で割り算する」となります。

VARP・VAR関数について

VARP・VAR関数は、Excel2010以降、それぞれVAR.P、VAR.S関数に置き換わっています。この2つの関数は「統計」ではなく、「互換性」に格納されています。
「数式」タブ→「関数ライブラリ」→「その他の関数」→「互換性」→VARP、VAR
使い方はVAR.P、VAR.S関数と同じです。

VARPA関数の使い方

VARPA関数は数値だけでなく、文字列と論理値も含めて分散の計算対象にします。VARPAは母集団全体を範囲として分散を計算する関数です。

データで標本分散を求めるVARPA関数の計算方法、使用例を分かりやすく解説しています。

https://www.tschoolbank.com/excel/function-varpa/

VARA関数の使い方

VARA関数は数値だけでなく、文字列と論理値も含めて分散の計算対象にします。VARAはサンプリングした標本を範囲として不偏分散を計算する関数です。

データで不偏分散を求めるVARA関数の計算方法、使用例を分かりやすく解説しています。

VARA関数の使い方|データで不偏分散を求める
Excelで不偏分散を求める関数VARAの使い方を初心者にも分かりやすく解説。VARA関数とは? VAR.SとVARAの違い、VARA関数で不偏分散の計算方法、分散のエクセル関数の種類など

DVARP関数の使い方

DVARP関数はデータベースで条件を指定して分散を求めます。DVARPは母集団全体を範囲として分散を計算する関数です。

条件を指定して標本分散を求めるDVARP関数の計算方法、使用例を分かりやすく解説しています。

503 Error - Service Temporarily Unavailable

DVAR関数の使い方

DVAR関数はデータベースで条件を指定して不偏分散を求めます。DVARはサンプリングした標本を範囲として不偏分散を計算する関数です。

条件を指定して不偏分散を求めるDVAR関数の計算方法、使用例を分かりやすく解説しています。

DVAR関数の使い方|条件を指定して不偏分散を求める
DVAR関数の使い方を初心者にも分かりやすく解説。DVAR関数の引数を指定して不偏分散を求める方法、複数の条件をANDかOR、またはAND・ORを組み合わせて指定する方法など。
タイトルとURLをコピーしました