Excel の分析ツールの分散分析ツールでの数値の改善の説明

概要

この資料では、3 つの分析ツールの分散分析ツールでの数値の改善について説明します。この資料では、Microsoft Excel 2002 では極端な場合に、Excel の以前のバージョンで、不正確な結果も示します。

詳細

多くの関数では、平均についての偏差の平方和の計算が必要です。正確には、Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel の最初のパスでは、平均値を検索し、第 2 段階で平均についての偏差の平方和を計算します。 2 つのパス プロシージャを使用します。

「電卓の数式」を使用して、以前のバージョンの Excel で正確な計算は、同じ結果が発生します。統計学者がコンピューターではなく電卓を使用する場合に広く使用されていた、この数式が引かれます。電卓の数式を Excel の以前のバージョンの観測値の平方和の合計を計算して次の数量の合計を減算します。
((sum of observations)^2) / number of observations
この計算は、データを単一のパスで発生します。

有限精度演算の場合は、電卓の数式は、極端な事例で丸め誤差がでますが。Excel 2002 および Excel の以前のバージョン (VAR、STDEV、傾き、ピアソンなど) の平均に関する偏差の平方和を必要とするほとんどの関数電卓の数式を使用します。ただし、これらのバージョンの Excel が含まれる、covar 関数では、DEVSQ 関数の数値より堅牢な 2 段階の手順を使用するもできます。

統計計算の専門家は、電卓の数式を使用しないことをお勧めします。電卓の数式は、統計計算に関するテキストで、「しない」として表示されます。残念ながら、分析ツール (ATP) の分散分析ツールの 3 つのすべては、Excel 2002 および Excel の以前のバージョンで、電卓の数式または同等の単一パスのアプローチの普及を確認します。

Excel 2003 およびそれ以降のバージョンの Excel は、すべての 3 つの ATP 分散分析モデルの 2 段階の手順を使用します。この資料では、ATP の 3 つの分散分析モデルで次の計算の改善について説明します。
  • 1 つの要因
  • レプリケーションでは、2 つの要素
  • レプリケートしない場合に、2 つの要素
この資料では、これらのモデルを後で説明します。

Excel は、DEVSQ を 2 段階の手順を使用するには常に、ためこの資料では、改良した手順を説明することを頻繁に使用します。これらの変更後の手順は DEVSQ を呼び出して効果的にか、機能を持つには正確には DEVSQ の機能と同じコードを使用します。

それぞれの分散分析ツールの ATP の出力には、概要テーブルの値をカウント、合計、平均、分散、および正方形のさまざまな合計と SS、df の値を持つ分散分析テーブルが含まれています、および P 値です。概要表の結果が Excel の関数をカウント、合計、平均、および var 関数を呼び出すことによって計算されます。これら 4 つの関数では、VAR だけは、丸め誤差がでます。

Excel 2002 および Excel の以前のバージョンは、電卓の数式を使用して、VAR を実装します。VAR に関する次の資料では、Excel 2003 およびそれ以降のバージョンの Excel で発生した機能強化について説明します。この資料では、丸めエラーは以前のバージョンの Excel で発生する可能性が高いときに表示する数値データを実験することができます。

Var 関数に関する詳細については、マイクロソフト サポート技術情報の記事を表示するのには次の資料番号をクリックします。

826112 excel の統計関数: VAR



この資料では、次の 3 つの分散分析モデルを説明とは、分散分析の出力テーブルについて説明します。どちらの場合も、概要表は、Excel 2003 およびそれ以降のバージョンの Excel で適切に動作します。Excel 2002 および Excel の以前のバージョンでは、極端な値のデータが存在すると"分散"列で問題が発生します。

ただし、この資料が含まれています概要表にはモデルのセクションでこれらのテーブルは、「付録」に変更した例を確認する比較に役立つため。

モデル 1: 1 つの要因

データの簡単な例は次のとおりです。
1 分散分析の基本モデル
123
244
365
486
57
68
分散分析: 一元
概要
グループカウント合計平均差異
列 16213.53.5
列 242056.666667
列 36335.53.5
分散分析
バリエーションのソースSSdfMSFP 値F crit
グループ間で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 sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs – GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd – GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs – GrandSampleMeanSqrd;

このアプローチは、電卓の数式では本質的にです。この方法は、観測値の平方和の合計を計算し、VAR の観測値の平方和の合計を計算し、減算と同じように、それらからの数量を減算
標本の合計^2/サンプル サイズです。モデル 2 およびモデル 3 のような擬似コードが省略されています。

もう一度、モデル 2 およびモデル 3 でも、平方和が計算され、数量は、電卓の数式のように四角形の合計から差し引かれます。残念ながら、基本的な統計情報のテキスト頻繁に方式を推奨して分散分析のこの資料の前半で説明したようです。

Excel 2003 およびそれ以降のバージョンの Excel は、分散分析表の"変動"列の各項目を計算するのに別の方法を使用します。図では、この資料では、セル B6 と B7 セルにデータがないと、その以前の例では、数値データがセル A2:C7 に表示されていると見なされます。
  • 変動の合計は、DEVSQ(A2:C7) など、すべてのデータに適用される devsq 関数だけです。Devsq 関数は、データが不足している場合でも正しく動作します。
  • グループ SS の間、DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7) など、各列に適用される DEVSQ の合計を差し引いた変動の合計です。
  • グループ内の変動は、変動の合計からグループ間変動します。
分散分析表の"変動"列内のエントリが正しく算出される場合、テーブル内の他のエントリの正確性に従います。

レプリケーション モデル 2: 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
分散分析
バリエーションのソースSSdfMSFP 値F crit
サンプル72172366.22E-054.747221
37218.59.250.0037093.88529
相互作用924.52.250.1479733.88529
内で24122
合計14217
もう一度、"変動"列内のエントリが正しく算出される場合、出力の分散分析の部分で他のすべてのエントリの正確性に従います。

ここでは、Excel 2003 およびそれ以降のバージョンの Excel の計算の手順です。この手順では、DEVSQ を使用して、分散分析表の"変動"列の各項目を計算します。図では、次の使用例では、B2:D7 のセルに、数値データが表示されている想定しています。
  • 変動の合計は、DEVSQ(B2:D7) など、すべてのデータに適用される devsq 関数だけです。
  • 標本の変動は、変動の合計、各サンプルについては、DEVSQ(B2:D4) + DEVSQ(B5:D7) のように適用される DEVSQ の合計からです。
  • SS の列は、DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7) など、各列に適用される DEVSQ の合計を差し引いた変動の合計です。
  • SS 内では、DEVSQ の試用版または DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7) などのグループのペアごとに適用されるの合計です。
  • 交互作用の変動は、変動の合計から標本の変動、マイナスの変動の各列の変動と同じです。

レプリケーションなしモデル 3: 2 つの要素

データの簡単な例は次のとおりです。
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
分散分析
バリエーションのソースSSdfMSFP 値F crit
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(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 の合計からです。
  • SS の列は、DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10) など、各列に適用される DEVSQ の合計を差し引いた変動の合計です。
  • 誤差の変動は、変動の合計行の変動と列の変動を引いたからです。

Excel 2002 および Excel の以前のバージョンでの結果

極端な場合、データの有効桁数があるが、分散が小さい、電卓の数式が不正確な結果にもなります。この資料の後半にある付録では、このような極端な場合に発生する丸め誤差の問題の例を紹介します。

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

Excel 2003 およびそれ以降のバージョンの Excel は、データを 2 つのパスでは、プロシージャを使用します。最初のパスでは、Excel 2003 およびそれ以降のバージョンの Excel とデータ値の数の合計を計算します。Excel は、これらの値から標本平均 (平均) を計算できます。

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

まとめ

2 段階のアプローチでは、3 つすべての ATP 分散分析ツール Excel 2003 および Excel の以前のバージョンと比較した場合、以降のバージョンの Excel での数値パフォーマンスが向上します。Excel 2003 およびそれ以降のバージョンの Excel を使用して取得した結果を Excel の以前のバージョンを使用して取得した結果よりも精度が劣ることはできません。

実際のほとんどの場合、ただしはこれらの結果の違いです。データは以下の付録を示す異常な動作の種類が通常発生しないためにです。数値が不安定になるが、多数の比較的小さなバリエーション データ値の間での有効桁数がデータに含まれている場合、Excel の以前のバージョンで発生することが考えられます。

以前のバージョンの Excel を使用する場合、表示するかどうか Excel 2003 またはそれ以降のバージョンの Excel を使用する分散分析の結果が異なる場合は、DEVSQ を使用するプロシージャを使用するときに取得される結果を Excel の以前のバージョンの分散分析ツールを使用するときに取得される結果を比較します。

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

差異がそれぞれの範囲の要約テーブルに正しいことを確認するには、DEVSQ (範囲) を使用して/(COUNT (範囲): 1)。

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

モデル 1、2、および 3 の各基本的な例では、この資料は以前、ATP ツールの出力が表示されます。これには、概要や分散分析テーブルが含まれています。「ストレス」の例を作成するには、各例では、データが変更されました。10 を追加することによってこれは、^8 の各データ値にします。10 などの定数を追加する ^ データの各値に 8 概要表の分散は影響しません (ただし、明らかな方法で平均と合計に影響されます)。影響はありません、分散分析表のエントリ。

分散分析表の概要表と SS の差異を比較することがわかります、これらのすべての計算が誤ってストレス モデルで、次にポイントされているモデル 3 の 1 つのエントリの 3 つのすべての"<---」です。

高負荷の場合、すべて Excel 2003 およびそれ以降のバージョンの Excel を使用して取得する分散分析の結果は (だけは基本的なケースで以前の結果を同意します。

大きなデータ値を持つモデルの負荷を分散 1

100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
分散分析: 一元
概要
グループカウント合計平均差異
列 166000000211E + 084.8
列 244000000201E + 088
列 366000000331E + 081.6
分散分析
バリエーションのソースSSdfMSFP 値F crit
グループ間で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
分散分析
バリエーションのソースSSdfMSFP 値F crit
サンプル64164240.0003674.747221
3221660.0156253.88529
相互作用3221660.0156253.88529
内で32122.666666667
合計12817

大きなデータ値を持つモデルの負荷を分散 3

(中)
こんにちは
低下100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
中間クラス100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
リッチ100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
レプリケーションのない二元配置分散分析:
概要カウント合計平均差異
行 133000000061000000020
行 233000000101000000032
3 行目33000000141000000052
行 433000000181000000064<---
行 533000000221000000076
行 6330000002610000000910
行 7330000003110000001012
行 8330000002610000000910
9 行目330000002110000000718
列 199000000451000000058
列 2990000007810000000914
列 399000000511000000064
分散分析
バリエーションのソースSSdfMSFP 値F crit
12881620.1132812.591094
3221620.1677723.633716
エラー128168
合計28826
プロパティ

文書番号:829215 - 最終更新日: 2017/02/01 - リビジョン: 2

フィードバック