Excel の強化された統計関数が分析ツールに与える効果について

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

目次

概要

この資料では、Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel の統計関数の数値に関する機能強化が、分析ツール (ATP) の各ツールに与える影響について説明します。ほとんどの ATP ツールは、結果を計算する処理で Excel の統計関数を呼び出します。この資料は多くの場合、個々の Excel の統計関数について記述した資料への参照を示しています。さらに、一部の ATP ツールに関して、今後の機能強化に関する説明も記載しています。

Microsoft Excel 2004 for Mac の情報

Excel 2004 for Mac の統計関数は、Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel の統計関数を更新するために使用されたアルゴリズムと同じアルゴリズムを使用して更新されました。この資料に記載されている、Excel 2003 およびそれ以降のバージョンの Excel の関数の機能、および関数の変更内容に関する情報は、Excel 2004 for Mac にも適用されます。

詳細

3 つの ATP 分散分析ツールで行われた機能強化を除き、ATP のコードは直接変更されていません。

Excel 2003 およびそれ以降のバージョンの Excel の一部の ATP ツールは、Excel 2003 以降のバージョンの Excel で強化された Excel 関数を呼び出すため、数値に関する性能が向上しました。以前のバージョンの Excel と新しいバージョンの Excel で結果が異なる場合は、Excel 2003 以降のバージョンの Excel の値の方が正確です。

ユーザーが、各バージョンの Excel の結果の違いに気付くことはほとんどありません。これは一般に、極端な事例でのみ顕著になる丸め誤差によって結果に違いが生じるためです。ただし、この資料では、Microsoft Excel 2002 およびそれ以前のバージョンの Excel で、不適切な数式が原因で違いが生じる一例を最初に示します。Excel 2002 およびそれ以前のバージョンの回帰分析ツールは使用しないでください。

2 つ目の例には、Excel 2002 およびそれ以前のバージョンの Excel の不適切な数式が関係しています。この数式は、Excel 2003 以降のバージョンの Excel にも含まれています。下記の状況に該当する場合は、すべての Excel のバージョンの ATP ツールを使用しないでください。

まず、[定数に 0 を使用] チェック ボックスをオンにする必要がある場合は、回帰分析ツールを使用しないようにします。この問題は、Excel 2003 およびそれ以降のバージョンの Excel で修正されています。[定数に 0 を使用] チェック ボックスをオフにしている場合は、回帰分析ツールを使用しても構いません (実際にはこの設定がほとんどです)。

次に、欠測値がないことを保証できない限り、すべてのバージョンの Excel で、ATP の t 検定: 一対の標本による平均の検定を使用しないようにする必要があります。1 つまたは複数の欠測値が存在する場合、このツールは不適切な答を返します (または答がまったく返されません)。

ATP の一対の標本による t 検定ツールの関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
829252 Excel で分析ツールの t 検定を使用すると不正確な結果および誤解を招くラベルが表示されることがある
このツールを使用する必要があり、欠測値が存在する場合 (または欠測値が存在する可能性がある場合) は、Excel の TTEST 関数を使用すると欠測値が適切に処理されます。

この資料の後半で、ATP ツールについて個別に説明しています。記載されていないツールについては、Excel 2003 およびそれ以降のバージョンの Excel の機能強化の影響はありません。

分散分析 : 一元配置、繰り返しのある二元配置、繰り返しのない二元配置

これらの 3 つの分散分析ツールには変更が加えられ、数値面の堅牢性がより高い 2 段階のアルゴリズムを使用するように、計算方法が強化されました。これらの強化点は、平均に対する偏差の平方和を計算する統計関数 (VAR、STDEV、SLOPE、PEARSON など) における機能強化と同様です。

ATP の分散分析ツールの関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
829215 Excel の Analysis ToolPak の分散分析ツールで強化された数値の扱いについて

相関

このツールは変更されていません。ただし、相関ツールと、すべてのバージョンの Excel に含まれている共分散ツールには若干の違いがあります。相関ツールを使用すると、対角線上に 1 があり、対角線を含まない下方の三角形の部分に相関値を示す表が得られます。このツールでは、CORREL を使用して対角線上以外のエントリを計算し、それらのエントリに CORREL から返される値を設定します。そのため、いずれかのデータ エントリが変更されても、表内のエントリは変更されません。この動作を共分散ツールの動作と比較してください。

共分散

このツールを使用すると、対角線上に分散値があり、対角線を含まない下方の三角形の部分に共分散値を示す表が得られます。対角線上のセルには、数式 "=VARP(...)" が含まれており、データ エントリが変更されると表内の結果も変更されます。Office Excel 2003 およびそれ以降のバージョンの Excel の VARP は機能が強化されています。

VARP の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
826393 Excel の統計関数 VARP
共分散ツールでは COVAR を使用して対角線上以外のエントリを計算し、それらのエントリに COVAR から返される値を設定します。そのため、データ エントリが変更されても、対角線上以外のエントリは変更されません。

基本統計量

このツールは、計算するすべての対象に対して Excel の統計関数を呼び出します。Excel 2003 およびそれ以降のバージョンの Excel の VAR および STDEV は強化されているため、極端な事例では、丸め誤差が原因で異なる値が示される可能性があります。

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

F 検定: 2 標本を使った分散の検定

基本統計量ツールと同様に、このツールも VAR を呼び出します。このツールでも、極端な事例では、丸め誤差が原因で異なる値が示される可能性があります。

乱数発生

このツールを使用すると、任意の範囲に乱数が入力されます。それらの乱数値はセルに直接入力されるので、シートが再計算されても、セルの値は再計算されたり、新しい乱数値で置き換えられたりすることはありません。一方、Excel に組み込まれている RAND 関数の場合は、シートが再計算されるたびに既存の乱数が新しい乱数に置き換えられます。RAND 関数を使用して値を保持できます。この操作を行うには、範囲内の結果をコピーし、[形式を選択して貼り付け] コマンドを使用して同じ範囲に値を貼り付けます。

乱数発生 (RNG) ツールもさまざまな確率分布から乱数を生成しますが、RAND は、0 〜 1 の範囲を持つ "均一" という乱数発生ツールの 1 つのオプションに対応しています。この資料では、RAND を Excel の統計関数と組み合わせてこうした乱数を生成する方法について説明します。

したがって、ある程度の工夫を行って RAND を使用することで、ATP の乱数発生ツールの機能をエミュレートできます。特に、大量の乱数が必要な場合にはこの方法が便利なことがあります。

Excel 2002 およびそれ以前のバージョンの Excel では、ATP の乱数発生ツールと RAND は、乱数の標準検定においてどちらも性能が低いことが確認されていました。性能が低いのは、擬似乱数列で繰り返しが始まるまでの周期が短すぎることが原因でした。これは、多数の乱数が必要な場合のみの問題です。

Excel 2003 およびそれ以降のバージョンの Excel の RAND は機能が強化されており、現在、RAND はすべての標準検定に合格します。RAND の乱数列では、1 兆個を超える数が生成された後で繰り返しが始まります。

RAND の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
828795 Excel 2007 と Excel 2003 の RAND 関数について
ただし、ATP の乱数発生ツールは、機能強化が行われませんでした。Excel 2002 およびそれ以前のバージョンの Excel に含まれている RAND と同様に、ATP の乱数発生ツールは標準検定における無作為さに関する性能は低く、短い周期で繰り返しが行われます。このことは、非常に長い乱数列 (100 万個の乱数など) が必要な場合にのみ悪影響を及ぼします。

乱数発生ツールは、さまざまな確率分布と RAND から乱数を出力する際に使用される閉区間 [0,1] の一様 (均一) 分布を使用して乱数を出力します。ATP ツールは、最初に閉区間 [0,1] の一様乱数を 1 つまたは複数個取り出し、その数を、以下に示す特定の分布の乱数に変換します。多数の乱数を生成するユーザーのために、RAND を使用する数式を次の表に示します。この表の後には、分析ツールの正規分布に関する注記が記載されています。
元に戻す全体を表示する
分布 RAND() を使用する Excel の数式
ベルヌーイ (p) =IF(RAND() <= p, 1, 0)
二項 (n,p) =CRITBINOM(n, p, RAND())
離散 以下を参照
正規 (mu, sigma) =NORMINV(RAND(), mu, sigma)
パターン 実際には乱数ではありません
ポワソン (mean) 以下を参照
均一 (low, high) = low + (high ? low) * RAND()

正規 (mu, sigma) の場合には ATP の乱数発生ツールではなく RAND と上記の表の数式を使用した方が好ましい理由が 2 つあります。1 つ目の理由は、ATP の乱数発生ツールよりも RAND の方が優れた閉区間 [0,1] の一様乱数ジェネレータであることです。2 つ目の理由は、ATP の乱数発生ツールは Excel の NORMINV 関数を呼び出さず、独自の組み込みの正規分布の逆関数を使用します。これは、Excel 2003 およびそれ以降のバージョンの Excel の NORMINV ほど正確ではありません。使用する正規分布の近似 (Excel でははるかに強化された NORMSDIST 関数を使用します) の正確さにおいても、二分探索の精度 (Excel では精度がはるかに向上し、NORMINV の確率引数に近い値が保証されます) においても劣ります。簡単に言うと、この場合に ATP を使用しても、Excel 2003 およびそれ以降のバージョンの Excel の NORMINV、NORMSDIST、および RAND 関数の機能強化の効果を得られません。

離散分布の乱数については、値は列 B にあり、それらの確率は C にあるとします。列 A の各行に、その行の列 B の値よりも厳密に小さい値が観測される確率が入るようにします。値が 10 個の場合には、このデータをセル A1:C10 に入力します。その場合、A1 には最初の値よりも厳密に小さい値が観測される確率が格納されるので、A1 を 0 に設定する必要があります。VLOOKUP(RAND(), A1:C10, 2) を使用できます。VLOOKUP の 4 番目の引数は任意であり、省略するか TRUE に設定する必要があります。"2" は、2 番目の列 (この例では列 B) に値を返すことを表します。

ATP では、『Numerical Recipes in C, The Art of Scientific Computing』 (Press、W.H.、S.A. Teukolsky、W. T. Vetterling、および B.P. Flannery 共著、第 2 版、Cambridge University Press 発行、1992 年、pp. 293-295) に記載されているポワソンの観測値発生方式を使用しています。既存の Excel 関数を簡単に使用する方法は 2 つあります。

1 つ目の方法では、平均値 m を持つポワソン乱数は、大きな n の場合の BINOMIAL(n, m/n) によって適切に近似されている分布を持ちます。この場合、CRITBINOM(n, m/n, RAND()) を呼び出せます。n の選択は m に依存し、n は m の 1,000 倍となるように、十分に大きい値であることが必要です。

2 つ目の方法は、このポワソン分布を指数近似に関連付けます。事象が単位時間あたり m の割合でポワソン過程に従って発生する場合、事象間の時間は、平均値 1/m を持つ指数分布を持ちます。ポワソンの観測値の場合、この指数分布から観測値の列を取り出し、それらの総和が 1 を超えるときの観測値の数を数えることができます。指数分布から観測値を取得するには、GAMMAINV(RAND(), 1, 1/m) を使用します。この方法は、m が比較的 0 に近い場合に適しています。

回帰分析

回帰分析ツールは、Excel の LINEST を呼び出します。次の LINEST に関する資料では、Excel 2003 およびそれ以降のバージョンの Excel に対する広範な機能強化について説明しています。

LINEST の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
828533 Excel の LINEST 関数について
Excel 2002 またはそれ以前のバージョンの Excel を使用する場合は、LINEST と同じ 2 つの短所が ATP の回帰分析ツールにも存在することに注意してください。
  • 回帰平方和、重決定 R2、および f 統計値は、原点を通る回帰の場合には、必ず不適切な値になります。

    LINEST の場合、これは、"3 番目の引数が、TRUE に設定されるまたは省略される代わりに、FALSE に設定される" ことを意味します。ATP の回帰分析ツールでは、[定数に 0 を使用] チェック ボックスをオンにすることを意味します。
  • LINEST および ATP の回帰分析ツールは、共線の問題に対しては機能しません。Excel 2003 およびそれ以降のバージョンの Excel の LINEST は、共線、または、ほぼ共線が存在する場合にはそれを検出し、適切に対処するように設計されています。LINEST に関する上記の資料では、LINEST での計算方法について説明しています。
これらの LINEST の短所はどちらも、Excel 2003 およびそれ以降のバージョンの Excel で修正されました。ATP の回帰分析ツールの性能も同様に向上します。ツールのコードは変更されていませんが、強化された Excel 関数を呼び出すことによって性能が向上します。LINEST の機能強化は、統計関数の機能強化の中で最も重要だと考えられます。

以前のバージョンの Excel および新しいバージョンの Excel について、[定数に 0 を使用] チェック ボックスをオンにした状態での回帰分析ツールの出力を次の表に示します。この表は、上記で説明した 1 つ目の短所を表しています。以前のバージョンの Excel では、回帰平方和は、重決定 R2 と同様に負の数です。
元に戻す全体を表示する
X Y
1 11
2 12
3 13
Excel 2002 およびそれ以前のバージョンの Excel
概要
回帰統計
重相関 R 65535
重決定 R2 -20.4285714
補正 R2 -20.9285714
標準誤差 4.629100499
観測数 3
分散分析表
自由度 変動 分散 観測された分散比 有意 F
回帰 1 -40.85714286 -40.85714286 -1.90666667 #NUM!
残差 2 42.85714286 21.42857143
合計 3 2
Excel 2003 およびそれ以降のバージョンの Excel
概要
回帰統計
重相関 R 0.949342311
重決定 R2 0.901250823
補正 R2 0.401250823
標準誤差 4.629100499
観測数 3
分散分析表
自由度 変動 分散 観測された分散比 有意 F
回帰 1 391.1428571 391.1428571 18.25333333 0.14637279
残差 2 42.85714286 21.42857143
合計 3 434

t 検定: 一対の標本による平均の検定

前半で説明したように、1 つまたは複数の欠測値が存在する可能性がある場合は、このツールを使用しないようにします。この検定の典型的な応用例は、対象に対してある処置を行う前後のデータを測定する実験です (60 日間のダイエット計画の前後の体重など)。欠測値がなければ、このツールは正常に機能します。処置の前後のデータ数が異なる場合はエラー メッセージが表示され、計算がまったく行われません。欠測値があり、処置の前後のデータ数が等しい場合は、いくつかの誤差値を含む結果が返されます。

標準的な方法として、前後の計測値のいずれかが欠損している場合には、その対象をデータから削除し、処置前後の両方の計測値を持つ対象のみについてデータを分析します。Excel の TTEST 関数では、この標準的な方法に従って欠測値が処理されます。

残りの 2 つの t 検定ツール、等分散を仮定した 2 標本による検定、および分散が等しくないと仮定した 2 標本による検定にはこの短所はありません。

z 検定: 2 標本による平均の検定

この資料では、乱数発生ツールでは、正規分布の場合には NORMSINV 関数 (より正確には NORMSINV を呼び出す NORMINV) を呼び出さず、独自の性能の低い方法で正規分布の逆関数値の値が求められることを説明しました。

z 検定ツールでは NORMSINV 関数を呼び出すので、Excel 2003 およびそれ以降のバージョンの Excel の機能強化の効果を得られます。

以前のバージョンの Excel の計算結果

Excel 2003 およびそれ以降のバージョンの Excel の ATP ツールには、Excel 2003 以降のバージョンの Excel で機能強化された Excel の統計関数を呼び出すことから、性能が向上したツールがあります。LINEST に対する強化点の 1 つとして 3 番目の引数が FALSE に設定されることから、Excel 2002 およびそれ以前のバージョンの Excel では、[定数に 0 を使用] チェック ボックスをオンにしている場合には、ATP の回帰分析ツールで不適切な結果が返されることがわかります。Excel 関数の強化の効果が得られるその他のツールの場合については、以前のバージョンの Excel との違いに気付くことはほとんどありません (それらの違いの多くは、極端な事例での丸め誤差に関連します)。

ATP のコードが、数値に関してより堅牢なアルゴリズムを使用するように変更されたことにより、3 つの ATP 分散分析ツールの性能が向上しました (Excel の VAR と同じ機能強化です)。以前のバージョンの Excel との違いは、極端な事例でのみ気付くことがあります。

すべてのバージョンのユーザーに対する警告 : 欠測値が存在する可能性が少しでもある場合は、t 検定: 一対の標本による平均の検定を使用しないでください。

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

Excel の統計関数が大幅に強化された結果、Excel 関数を呼び出す多数の ATP ツールにその効果が現れ、性能が向上しました。ATP ツールには、強化された RAND 関数を利用しない乱数発生ツールがあります (ツール自体に乱数ジェネレータが組み込まれているため RAND を呼び出しません)。このことは残念ですが、正規分布での乱数値という特殊な場合はさらに残念です。正規分布の逆関数も組み込まれているので、それよりもはるかに高性能の NORMSINV 関数が呼び出されません。

ATP ツールと、それらのツールが呼び出す Excel 2003 およびそれ以降のバージョンの Excel で強化された Excel 関数を次の表に示します。呼び出される各 Excel 関数については、個別の資料を参照してください。
元に戻す全体を表示する
ATP ツール呼び出される Excel 関数
分散分析 : 一元配置 VAR、FINV
分散分析 : 繰り返しのある二元配置 VAR、FINV
分散分析 : 繰り返しのない二元配置 VAR、FINV
相関
共分散
基本統計量 STDEV、TINV、VAR
指数平滑
F 検定: 2 標本を使った分散の検定 VAR、FINV
フーリエ解析
ヒストグラム
移動平均
乱数発生
順位と百分位数
回帰分析 LINEST
サンプリング RAND
t 検定: 一対の標本による平均の検定 VAR、PEARSON、TINV
t 検定: 等分散を仮定した 2 標本による検定 VAR、TINV
t 検定: 分散が等しくないと仮定した 2 標本による検定 VAR、TINV
z 検定: 2 標本による平均の検定 NORMSDIST、NORMSINV

上記の表に記載されている LINEST および RAND 以外のすべての関数については、以前のバージョンの Excel と新しいバージョンの Excel との間の違いに気付くのは、極端な事例で丸め誤差が発生した場合のみです。この資料で説明したように、LINEST が大幅に強化されました。また、RAND の性能も向上しました。非常に長い乱数列が必要な場合には、サンプリング ツールは RAND を呼び出しますが、乱数発生ツールは低い性能を示す独自のジェネレータに依存します。

まとめ

ATP のコードは、3 つの分散分析ツール以外は変更されていませんが、ATP ツールの表に示した、強化された Excel 関数を呼び出すため、その機能強化の効果を得られます。t 検定: 一対の標本による平均の検定の不具合は、Excel 2003 またはそれ以降のバージョンの Excel で修正されていません。最も顕著な強化点は回帰分析ツールにあり、LINEST が [定数に 0 を使用] チェック ボックスがオンの場合にも不適切な結果を返さず、共線を適切に処理するように設計されたことです。

プロパティ

文書番号: 829208 - 最終更新日: 2007年5月29日 - リビジョン: 4.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
キーワード:?
kbformula kbexpertisebeginner kbfunctions kbfuncstat kbinfo KB829208
"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