現在オフラインです。再接続するためにインターネットの接続を待っています

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

Office 2003 のサポートは終了しました

マイクロソフトでは、2014 年 4 月 8 日に Office 2003 のサポートを終了しました。この変更は、ソフトウェアの更新プログラムおよびセキュリティ オプションに影響しています。 この変更の意味および保護された状態を維持する方法について説明します。

概要
この資料では、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
123
244
365
486
57
68
分散分析 : 一元配置
概要
グループ標本数合計平均分散
列 16213.53.5
列 242056.666667
列 36335.53.5
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
グループ間12.7526.3751.5068180.2578973.805567
グループ内55134.230769
合計67.7515

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 sampleEndfor;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
試行 1123
244
365
試行 2486
5107
6128
分散分析 : 繰り返しのある二元配置
概要グループ 1グループ 2グループ 3合計
試行 1
標本数3339
合計6121230
平均2443.333333
分散1412.5
試行 2
標本数3339
合計15302166
平均51077.333333
分散1416.25
合計
標本数666
合計214233
平均3.575.5
分散3.5143.5
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
標本72172366.22E-054.747221
37218.59.250.0037093.88529
交互作用924.52.250.1479733.88529
繰り返し誤差24122
合計14217

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

以下に、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
低所得123
244
365
中間層486
5107
6128
高所得71410
8126
9102
分散分析 : 繰り返しのない二元配置
概要標本数合計平均分散
低所得3621
3103.3333331.333333
3144.6666672.333333
中間層31864
3227.3333336.333333
3268.6666679.333333
高所得33110.3333312.33333
3268.6666679.333333
321719
94557.5
9788.66666716
9515.6666676.25
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
176.6667822.083335.760870.0014762.591094
68.66667234.333338.9565220.0024553.633716
誤差61.33333163.833333
合計306.666726

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

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

100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
分散分析 : 一元配置
概要
グループ標本数合計平均分散
列 166000000211E+084.8
列 244000000201E+088
列 366000000331E+081.6
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
グループ間020013.805567
グループ内64134.923077
合計6415

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

グループ 1グループ 2グループ 3
試行 1100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
試行 2100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
分散分析 : 繰り返しのある二元配置
概要グループ 1グループ 2グループ 3合計
試行 1
標本数3339
合計3000000063000000123000000129E+08
平均1000000021000000041000000041E+08
分散0404
試行 2
標本数3339
合計3000000153000000303000000219E+08
平均1000000051000000101000000071E+08
分散0406
合計
標本数666
合計600000021600000042600000033
平均100000004100000007100000005.5
分散4.814.41.6
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
標本64164240.0003674.747221
3221660.0156253.88529
交互作用3221660.0156253.88529
繰り返し誤差32122.666666667
合計12817

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

低所得100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
中間層100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
高所得100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
分散分析 : 繰り返しのない二元配置
概要標本数合計平均分散
行 133000000061000000020
行 233000000101000000032
行 333000000141000000052
行 433000000181000000064<---
行 533000000221000000076
行 6330000002610000000910
行 7330000003110000001012
行 8330000002610000000910
行 9330000002110000000718
列 199000000451000000058
列 2990000007810000000914
列 399000000511000000064
分散分析表
変動要因変動自由度分散観測された分散比P-値F 境界値
12881620.1132812.591094
3221620.1677723.633716
誤差128168
合計28826
プロパティ

文書番号:829215 - 最終更新日: 04/23/2007 03:34:22 - リビジョン: 2.0

Microsoft Office Excel 2007, Microsoft Office Excel 2003

  • kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215
フィードバック