Excel 統計関数: RSQ

  • [アーティクル]
  • 適用対象:
    Microsoft Office Excel 2007, Microsoft Office Excel 2003

概要

この記事では、Microsoft Office Excel 2003 以降のバージョンの Excel の RSQ 関数について説明します。 この記事では、関数の使用方法について説明し、これらの新しいバージョンの Excel の RSQ の結果と、以前のバージョンの Excel の RSQ の結果を比較します。

詳細情報

RSQ(array1, array2) 関数は、2 つのデータ配列間のピアソン Product-Moment 相関係数の 2 乗を返します。

構文

RSQ(array1, array2)

引数 array1 と array2 は、数値または名前、配列定数、または数値を含む参照である必要があります。

RSQ の最も一般的な使用法には、RSQ(A1:A100、B1:B100) などのデータを含む 2 つのセル範囲が含まれます。

使用例

RSQ 関数を説明するには、次の手順に従います。

  1. 空白の Excel ワークシートを作成し、次の表をコピーします。

    A B C D
    1 = 3 + 10^$D$2 データに追加する 10 のパワー
    2 =4 + 10^$D$2 0
    3 =2 + 10^$D$2
    4 =5 + 10^$D$2
    5 =4+10^$D$2
    6 =7+10^$D$2 Excel 2003 より前
    =RSQ(A1:A6,B1:B6) D2 = 7.5 の場合
    =PEARSON(A1:A6,B1:B6)^2 RSQ = ピアソン^2 0.492857142857143
    =CORREL(A1:A6,B1:B6)^2 CORREL^2 0.509470304975923
    D2 = 8 の場合
    RSQ = ピアソン^2 #DIV/0!
    CORREL^2 0.509470304975923

  2. 空白の Excel ワークシートでセル A1 を選択し、ワークシート内のセル A1:D13 が表に入力されるようにエントリを貼り付けます。

  3. 新しい Excel ワークシートにテーブルを貼り付けた後、[ 貼り付けオプション ] ボタンをクリックし、[ 一致する変換先の書式設定] をクリックします。 貼り付けた範囲がまだ選択されている状態で、実行している Excel のバージョンに応じて、次のいずれかの手順を使用します。

    • Microsoft Office Excel 2007 で、[ホーム] タブをクリックし、[セル] グループの [書式] をクリックし、[列の幅の自動調整] をクリックします。
    • Excel 2003 の [書式] メニューの [列] をポイントし、[オートフィット選択] をクリックします。

注:

セル B1:B6 を小数点以下 0 桁の数値として書式設定できます。

セル A1:A6 と B1:B6 には、セル A8:A10 で RSQ、PEARSON、CORREL を呼び出すためにこの例で使用される 2 つのデータ配列が含まれています。 RSQ は、基本的に PEARSON を計算し、結果を 2 乗することによって計算されます。 PEARSON と CORREL はどちらもピアソン Product-Moment 相関係数を計算するため、結果は一致する必要があります。 RSQ は、本質的に CORREL を計算し、結果を 2 乗するように実装されている可能性があります (ただし、実装されませんでした)。

Excel 2003 より前のバージョンの Excel では、ピアソンで丸めエラーが発生する可能性があります。 この動作により、RSQ で丸めエラーが発生します。 ピアソンの動作、つまり RSQ は、Excel 2003 以降のバージョンの Excel で改善されました。 CORREL は、Excel 2003 以降のバージョンの Excel で見つかった改善された手順を使用して、常に実装されています。 したがって、以前のバージョンの Excel の RSQ の代わりに、代わりに CORREL を使用し、結果を 2 乗することです。

Excel 2003 より前のバージョンの Excel では、この記事のワークシートを使用して実験を実行し、丸めエラーが発生したタイミングを検出できます。 B1:B6 の各観測値に定数を追加した場合、セル A7:A9 の RSQ、PEARSON^2、および CORREL^2 の値は影響を受けません。 D2 で値を増やすと、B1:B6 に大きな定数が追加されます。 D2 <= 7 の場合、A7:A9 に表示される丸めエラーはありません。 次に、7.25、7.5、7.75、および 8 の値を変更します。 A9 の CORREL^2 は影響を受けませんが、RSQ と PEARSON^2 (これらの式は常に一致します) は A7:A8 の丸めエラーを示します。 D6:D13 は、D2 = 7.5 および 8 の場合、RSQ = PEARSON^2 と CORREL^2 の値をそれぞれ表示します。

CORREL はまだ適切に動作しますが、ピアソンのラウンドオフ エラーは非常に深刻になり、D2 = 8 の場合、RSQ と PEARSON^2 で 0 による除算が発生します。

以前のバージョンの Excel では、これらのバージョンの Excel で使用される計算式の方が丸めエラーの影響がより深いため、このような場合に誤った回答が表示されます。 それでも、この実験で使用されるケースは極端と見なされる可能性があります。

Excel 2003 以降のバージョンの Excel を使用している場合、実験を試しても RSQ と PEARSON^2 の値は変更されません。 ただし、セル D6:D13 には、以前のバージョンの Excel で取得したであろう丸めエラーが表示されます。

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

2 つのデータ配列に X と Y の名前を付ける場合、以前のバージョンの Excel では、データを 1 回通過して、X の平方和、Y の平方和、X の合計、Y の合計、XY の合計、各配列内の観測値の数を計算しました。 これらの数量は、以前のバージョンの Excel のヘルプ ファイルで指定された計算式で結合されました。 RSQ のヘルプ ファイルには、ピアソン Product-Moment 相関係数の数式が表示されます。 この結果は、RSQ を取得するために 2 乗されます。

Excel 2003 以降のバージョンの Excel の結果

Excel 2003 以降のバージョンの Excel で使用される手順では、データを 2 パスで処理します。 最初に、X と Y の合計と各配列の観測値の数が計算され、これらから X と Y の観測値の平均 (平均) を計算できます。 次に、2 番目のパスでは、各 X と X 平均の 2 乗差が見つかり、これらの 2 乗差が合計されます。 各 Y と Y 平均の 2 乗差が見つかり、これらの二乗差が合計されます。 さらに、データ ポイントのペアごとに積 (X – X 平均) * (Y – Y 平均) が見つかり、合計されます。 これらの 3 つの合計は、PEARSON の数式で結合されます。 Y 配列 (または X 配列) 内の各値に定数を追加した場合、3 つの合計のいずれも影響を受けません。 この動作は、同じ値が Y 平均 (または X 平均) に加算されるために発生します。 数値の例では、セル D12 の 10 の高い累乗であっても、これらの 3 つの合計は影響を受けず、2 番目のパスの結果はセル D2 のエントリとは無関係です。 そのため、Excel 2003 以降のバージョンの Excel の結果は数値的により安定しています。

結論

ワンパス アプローチを 2 パス アプローチに置き換えると、Excel 2003 以降のバージョンの Excel では、PEARSON の数値パフォーマンスが向上するため、RSQ が保証されます。 Excel 2003 以降のバージョンの Excel で取得した結果は、以前のバージョンの Excel で取得した結果よりも正確になることはありません。

ほとんどの実用的な例では、新しいバージョンの Excel の結果と、以前のバージョンの Excel の結果との違いは見られない可能性があります。 この動作は、一般的なデータがこの実験で示す異常な動作を示す可能性が低いために発生します。 数値の不安定性は、以前のバージョンの Excel では、データに含まれる有効桁数が多く、データ値間の変動が比較的少ない場合に発生する可能性が最も高くなります。

標本平均を求め、各二乗偏差を計算し、二乗偏差を合計することによって、サンプル平均に関する二乗偏差の合計を求める手順は、代替手順よりも正確です。 この代替手順は、少数のデータ ポイントでの電卓の使用に適していたため、頻繁に "電卓の数式" という名前が付けられました。 別の手順では、次の手順を使用しました。

  • すべての観測値の平方和、サンプル サイズ、およびすべての観測値の合計が見つかりました
  • すべての観測値の平方和からマイナス ([すべての観測値の合計]^2)/サンプル サイズを計算します。

Excel 2003 以降のバージョンの Excel では、他にも多くの機能が改善されています。 以降のバージョンの Excel では、1 回目のパスでサンプル平均を検索し、2 番目のパスでサンプル平均に関する 2 乗偏差の合計を計算する 2 パス プロシージャに置き換えられるため、これらの関数が改善されました。

次の一覧は、このような関数の一覧です。

  • Var
  • Varp
  • 標準偏差
  • STDEVP
  • Dvar
  • DVARP
  • DSTDEV
  • DSTDEVP
  • 予報
  • 斜面
  • インターセプト
  • ピアソン
  • RSQ
  • STEYX

同様の改善は、分析ツールPakの3つの分散分析ツールのそれぞれで行われました。