Excel の Analysis ToolPak の分散分析ツールで強化された数値の扱いについて

文書翻訳 文書翻訳
文書番号: 829215 - 対象製品
すべて展開する | すべて折りたたむ

目次

概要

この資料では、Analysis ToolPak の 3 つの分散分析ツールで強化された数値の扱いについて説明します。また、極端な条件では Microsoft Excel 2002 およびそれ以前の Excel で不正確な結果が返されることを示します。

詳細

多くの関数では、平均に対する偏差平方和の計算が必要です。Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel では、正確に算出するために、2 段階の計算方式を採用し、第 1 段階で平均値を求め、第 2 段階で平均に対する偏差平方和を計算します。

計算精度に制限がない場合、"電卓の数式" (この名前は、統計学者がコンピュータではなく電卓を利用していたころ広く使用されていたことからそう呼ばれています) が使用されている、以前のバージョンの Excel でも同じ結果が得られます。以前のバージョンでは、電卓の数式を使用して対象の値 (標本) の平方和が計算され、その合計値から次の値が引かれます。
((sum of observations)^2) / number of observations
第 1 段階でデータを処理する際にこの計算が行われます。

計算精度に制限がある場合、極端な条件では、電卓の数式では丸め誤差の問題が発生します。Excel 2002 およびそれ以前のバージョンの Excel では、平均に対する偏差平方和を求める必要があるほとんどの関数 (VAR、STDEV、SLOPE、PEARSON など) で電卓の数式が使用されています。ただし、これらのバージョンの Excel でも、CORREL、COVAR、および DEVSQ の各関数では、より数値的に安定した 2 段階の計算方式が使用されています。

統計計算の専門家は、電卓の数式を使用しないことを勧めています。電卓の数式は、統計計算に関するテキストでは "これを使用しない方法は" という形で取り上げられています。残念ながら、Excel 2002 およびそれ以前のバージョンの Excel の Analysis ToolPak (ATP) の 3 つの分散分析ツールではすべて、電卓の数式、つまり、1 段階の計算方式と同様の方法が広く使用されています。

Excel 2003 およびそれ以降のバージョンの Excel では、ATP の 3 つのすべての分散分析モデルで 2 段階の計算方式が使用されています。この資料では、ATP の 3 つの分散分析モデルで強化されたこれらの数値計算について説明します。
  • 一元配置
  • 繰り返しのある二元配置
  • 繰り返しのない二元配置
これらのモデルについては、この資料の後半で説明します。

Excel では、DEVSQ については常に 2 段階の計算方式が使用されていたため、この資料では、計算処理の向上について説明するために DEVSQ に言及することが多くなっています。変更が加えられた計算処理では、実際には DEVSQ を呼び出しているか、DEVSQ と同等の機能を持つコードが使用されています。

それぞれの分散分析ツールでは、ATP の出力として標本数、合計、平均、および分散の各値が格納された概要表と、各種の平方和や、変動、自由度、分散、観測された分散比、および P-値の各値が格納された分散分析表が含まれます。概要表の計算結果は、Excel 関数の COUNT、SUM、AVERAGE、および VAR を呼び出すことで計算されます。これらの 4 つの関数のうち、VAR だけで丸め誤差の問題が発生します。

Excel 2002 およびそれ以前のバージョンの Excel では、電卓の数式を使用して VAR 関数が実装されています。VAR に関する以下の資料では、Excel 2003 およびそれ以降のバージョンの Excel での機能強化について記述されており、以前のバージョンの Excel では丸め誤差の問題が発生する可能性の高かったケースについて、数値データで実際にテストして確認できます。

VAR の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
826112 Excel の統計関数 VAR


3 つの分散分析モデルを説明するために、この資料では出力される分散分析表を中心に説明します。いずれの例でも、Excel 2003 およびそれ以降のバージョンの Excel では、概要表の出力結果は正常です。Excel 2002 およびそれ以前のバージョンの Excel では、極端な値のデータが存在すると "分散" 列で問題が発生します。

ただし、「付録」に記載されている変更の事例を参照する際に比較に役立つため、この資料では、各モデルの説明に概要表を掲載しています。

モデル 1 : 一元配置

簡単なデータを使用した例を示します。
元に戻す全体を表示する
分散分析の基本モデル 1
1 2 3
2 4 4
3 6 5
4 8 6
5 7
6 8
分散分析 : 一元配置
概要
グループ 標本数 合計 平均 分散
列 1 6 21 3.5 3.5
列 2 4 20 5 6.666667
列 3 6 33 5.5 3.5
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
グループ間 12.75 2 6.375 1.506818 0.257897 3.805567
グループ内 55 13 4.230769
合計 67.75 15

Excel 2002 およびそれ以前のバージョンの Excel では、平方和の計算に次のようなコードが使用されています。
GrandSum = 0;
GrandSumOfSqs = 0; 
GrandSampleMeanSqrd = 0; 
GrandMeanSqrd = 0; 
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
   GrandSum = GrandSum + sum of observations in s-th sample;
   GrandSumOfSqs = GrandSumOfSqs + sum of squared observations in s-th sample;
   GrandSampleMeanSqrd = GrandSampleMeanSqrd  +
      (sum of observations in s-th sample^2)/size of s-th sample;
   GrandSampleSize = GrandSampleSize + size of s-th sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs ? GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd ? GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs ? GrandSampleMeanSqrd;
この方法は、本質的には電卓の数式です。標本の平方和を計算し、その値から量を引きます。これは、VAR で、標本の平方和を計算し、sum of observations^2/sample size を引くのと同じです。モデル 2 およびモデル 3 のコードは同様であるため省略しています。

ただし、モデル 2 およびモデル 3 でも、電卓の数式と同様に、平方和が計算され、その平方和から量が引かれます。残念ながら、統計学の入門テキストでは多くの場合、この資料の前半で説明したような分散分析の方式を推奨しています。

Excel 2003 およびそれ以降のバージョンの Excel では、分散分析表の "変動" 列の各項目を計算するのに異なる方式を使用しています。例として、この資料の前の例では、数値データがセル A2:C7 に格納され、セル B6 と B7 にはデータが存在しません。
  • 変動の合計は、単に、すべてのデータを対象とした DEVSQ であり、この例では DEVSQ(A2:C7) です。データが不足している場合でも DEVSQ の計算結果は正常です。
  • グループ間の変動は、変動の合計から、各列に対する DEVSQ の合計 (この例では DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7)) を引いた値です。
  • グループ内の変動は、変動の合計からグループ間変動を引いた値です。
分散分析表の "変動" 列の各項目が正しく算出される場合、この表内の他の項目の精度もそれと同じになります。

モデル 2 : 繰り返しのある二元配置

簡単なデータを使用した例を示します。
元に戻す全体を表示する
分散分析の基本モデル 2 グループ 1 グループ 2 グループ 3
試行 1 1 2 3
2 4 4
3 6 5
試行 2 4 8 6
5 10 7
6 12 8
分散分析 : 繰り返しのある二元配置
概要 グループ 1 グループ 2 グループ 3 合計
試行 1
標本数 3 3 3 9
合計 6 12 12 30
平均 2 4 4 3.333333
分散 1 4 1 2.5
試行 2
標本数 3 3 3 9
合計 15 30 21 66
平均 5 10 7 7.333333
分散 1 4 1 6.25
合計
標本数 6 6 6
合計 21 42 33
平均 3.5 7 5.5
分散 3.5 14 3.5
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
標本 72 1 72 36 6.22E-05 4.747221
37 2 18.5 9.25 0.003709 3.88529
交互作用 9 2 4.5 2.25 0.147973 3.88529
繰り返し誤差 24 12 2
合計 142 17

繰り返しになりますが、"変動" 列の各項目が正しく算出される場合、出力される分散分析表の他の項目の精度もすべてそれと同じになります。

以下に、Excel 2003 およびそれ以降のバージョンの Excel での計算手順を示します。この手順では DEVSQ を使用して、分散分析表の "変動" 列の各項目を計算します。説明のために、この例では数値データがセル B2:D7 に格納されていると仮定します。
  • 変動の合計は、すべてのデータに対する DEVSQ であり、この例では DEVSQ(B2:D7) です。
  • 標本の変動は、変動の合計から、各標本に対する DEVSQ の合計 (この例では DEVSQ(B2:D4) + DEVSQ(B5:D7)) を引いた値です。
  • 列の変動は、変動の合計から、各列に対する DEVSQ の合計 (この例では DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7)) を引いた値です。
  • 繰り返し誤差の変動は、各試行のペアまたは各グループのペアに対する DEVSQ の合計 (この例では DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7)) です。
  • 交互作用の変動は、変動の合計から標本の変動、列の変動、繰り返し誤差の変動の各値を引いた値と等しくなります。

モデル 3 : 繰り返しのない二元配置

簡単なデータを使用した例を示します。
元に戻す全体を表示する
分散分析の基本モデル 3
低所得 1 2 3
2 4 4
3 6 5
中間層 4 8 6
5 10 7
6 12 8
高所得 7 14 10
8 12 6
9 10 2
分散分析 : 繰り返しのない二元配置
概要 標本数 合計 平均 分散
低所得 3 6 2 1
3 10 3.333333 1.333333
3 14 4.666667 2.333333
中間層 3 18 6 4
3 22 7.333333 6.333333
3 26 8.666667 9.333333
高所得 3 31 10.33333 12.33333
3 26 8.666667 9.333333
3 21 7 19
9 45 5 7.5
9 78 8.666667 16
9 51 5.666667 6.25
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
176.6667 8 22.08333 5.76087 0.001476 2.591094
68.66667 2 34.33333 8.956522 0.002455 3.633716
誤差 61.33333 16 3.833333
合計 306.6667 26

"変動" 列の値が正しく算出される場合、分散分析表の他の値の精度もすべてそれと同じになります。

Excel 2003 およびそれ以降のバージョンの Excel では、以下の計算手順を使用しています。この手順では、DEVSQ を使用して分散分析表の "変動" 列の値を計算します。説明のために、この例では、前の例で示したセル範囲が A1:D10 であると仮定します。したがって、数値データはセル B2:D10 に格納されています。
  • 変動の合計は、すべてのデータに対する DEVSQ であり、この例では DEVSQ(B2:D10) です。
  • 行の変動は、変動の合計から、各行に対する DEVSQ の合計 (この例では DEVSQ(B2:D2) + DEVSQ(B3:D3) + DEVSQ(B4:D4) + DEVSQ(B5:D5) + DEVSQ(B6:D6) + DEVSQ(B7:D7) + DEVSQ(B8:D8) + DEVSQ(B9:D9) + DEVSQ(B10:D10)) を引いた値です。
  • 列の変動は、変動の合計から、各列に対する DEVSQ の合計 (この例では DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10)) を引いた値です。
  • 誤差の変動は、変動の合計から行の変動と列の変動の各値を引いた値です。

Excel 2002 およびそれ以前のバージョンの Excel の計算結果

データの有効桁数が大きく、しかも分散が小さいという極端な条件では、電卓の数式では不正確な結果になります。この資料の後半に記載されている「付録」には、このような極端な条件で発生する丸め誤差の問題の例を示しています。

Excel 2003 およびそれ以降のバージョンの Excel の計算結果

Excel 2003 およびそれ以降のバージョンの Excel では、データを 2 段階の計算方式で処理する手法を採用しています。第 1 段階で、各データ値の合計と標本数を計算します。これらの値から標本平均を算出できます。

第 2 段階では、各データ ポイントと標本平均の間の平方差を計算し、それらの平方差の合計を算出します。その結果、Excel 2003 およびそれ以降のバージョンの Excel での計算結果はより数値的に安定したものになります。

まとめ

2 段階の計算方式を採用したことにより、Excel 2003 およびそれ以降のバージョンの Excel では、ATP の 3 つの分散分析ツールすべての数値パフォーマンスが以前のバージョンの Excel よりも向上しています。Excel 2003 およびそれ以降のバージョンの Excel の計算結果が以前のバージョンの Excel の計算結果よりも低い精度になることはありません。

ただし実際には、ほとんどの場合、「付録」に記載されているような例外的な値を取るデータはないため、Excel 2003 およびそれ以降のバージョンの Excel の計算結果と以前のバージョンの Excel の計算結果との間には相違はありません。以前のバージョンの Excel で数値的な不安定さが発生しやすくなるのは、有効桁数の大きな値がデータに含まれていて、データ値の間の偏差が相対的に小さい場合です。

以前のバージョンの Excel を使用している場合に、Excel 2003 またはそれ以降のバージョンの Excel で分散分析の出力結果が異なるかどうかを確認するには、以前のバージョンの分散分析ツールを使用した計算結果と、DEVSQ を使用する手順の計算結果とを比較します。

: この資料の前半では、分散分析表とそれに関連する各ツールについて DEVSQ を使用する手順を説明しています。

概要表の分散の値が各範囲で正しいことを検証するには、DEVSQ(range)/(COUNT(range) ? 1) を使用します (range にはセル範囲を指定します)。

付録 : Excel 2002 およびそれ以前のバージョンの Excel のパフォーマンスを示す数値例

この資料では、モデル 1、2、および 3 のそれぞれの基本的な例について、前に記載した概要表や分散分析表などの ATP ツールの出力を示します。それぞれの例ではデータに変更を加えて、パフォーマンスに "ストレス" を与えるような極端なデータ例が作成されています。このため、各データ値に 10^8 を加算しています。各データの値に 10^8 などの定数を追加しても、概要表の分散の値には影響はありません (ただし、平均と合計には当然影響があります)。また、分散分析表の各項目にも本来は影響はありません。

概要表の分散の値と分散分析表の変動の値を比較すると、次の 3 つのストレス モデルではいずれも、それらのすべての計算が誤っていることがわかります。ただし、"<---" で示されている、モデル 3 の 1 つの項目だけは例外です。

いずれのストレス モデルでも、Excel 2003 の分散分析表の出力結果は、前に説明した基本的な例の出力結果と一致します (本来一致するデータです)。

大きなデータ値を使用した分散分析ストレス モデル 1

元に戻す全体を表示する
100000001 100000002 100000003
100000002 100000004 100000004
100000003 100000006 100000005
100000004 100000008 100000006
100000005 100000007
100000006 100000008
分散分析 : 一元配置
概要
グループ 標本数 合計 平均 分散
列 1 6 600000021 1E+08 4.8
列 2 4 400000020 1E+08 8
列 3 6 600000033 1E+08 1.6
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
グループ間 0 2 0 0 1 3.805567
グループ内 64 13 4.923077
合計 64 15

大きなデータ値を使用した分散分析ストレス モデル 2

元に戻す全体を表示する
グループ 1 グループ 2 グループ 3
試行 1 100000001 100000002 100000003
100000002 100000004 100000004
100000003 100000006 100000005
試行 2 100000004 100000008 100000006
100000005 100000010 100000007
100000006 100000012 100000008
分散分析 : 繰り返しのある二元配置
概要 グループ 1 グループ 2 グループ 3 合計
試行 1
標本数 3 3 3 9
合計 300000006 300000012 300000012 9E+08
平均 100000002 100000004 100000004 1E+08
分散 0 4 0 4
試行 2
標本数 3 3 3 9
合計 300000015 300000030 300000021 9E+08
平均 100000005 100000010 100000007 1E+08
分散 0 4 0 6
合計
標本数 6 6 6
合計 600000021 600000042 600000033
平均 100000004 100000007 100000005.5
分散 4.8 14.4 1.6
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
標本 64 1 64 24 0.000367 4.747221
32 2 16 6 0.015625 3.88529
交互作用 32 2 16 6 0.015625 3.88529
繰り返し誤差 32 12 2.666666667
合計 128 17

大きなデータ値を使用した分散分析ストレス モデル 3

元に戻す全体を表示する
低所得 100000001 100000002 100000003
100000002 100000004 100000004
100000003 100000006 100000005
中間層 100000004 100000008 100000006
100000005 100000010 100000007
100000006 100000012 100000008
高所得 100000007 100000014 100000010
100000008 100000012 100000006
100000009 100000010 100000002
分散分析 : 繰り返しのない二元配置
概要 標本数 合計 平均 分散
行 1 3 300000006 100000002 0
行 2 3 300000010 100000003 2
行 3 3 300000014 100000005 2
行 4 3 300000018 100000006 4 <---
行 5 3 300000022 100000007 6
行 6 3 300000026 100000009 10
行 7 3 300000031 100000010 12
行 8 3 300000026 100000009 10
行 9 3 300000021 100000007 18
列 1 9 900000045 100000005 8
列 2 9 900000078 100000009 14
列 3 9 900000051 100000006 4
分散分析表
変動要因 変動 自由度 分散 観測された分散比 P-値 F 境界値
128 8 16 2 0.113281 2.591094
32 2 16 2 0.167772 3.633716
誤差 128 16 8
合計 288 26

プロパティ

文書番号: 829215 - 最終更新日: 2007年4月23日 - リビジョン: 2.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
キーワード:?
kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com