概要
この記事では、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 関数を説明するには、次の手順に従います。
空白の Excel ワークシートを作成し、次の表をコピーします。
ある 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 空白の Excel ワークシートでセル A1 を選択し、ワークシートのセル A1:D13 がテーブルに入力されるようにエントリを貼り付けます。
新しい 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 の丸めエラーが発生します。 PEARSON の動作、つまり 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 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 乗差が見つかり、これらの二乗差が合計されます。 各 Y と Y 平均の 2 乗差が見つかり、これらの 2 乗差が合計されます。 さらに、データ ポイントのペアごとに積 (X – X 平均) * (Y – Y 平均) が見つかり、合計されます。 これらの 3 つの合計は、PEARSON の数式で結合されます。 Y 配列 (または X 配列) の各値に定数を追加しても、3 つの合計のいずれも影響を受けないことに注意してください。 この動作は、同じ値が Y 平均 (または X 平均) に加算されるために発生します。 数値の例では、セル D12 で 10 の累乗が大きい場合でも、これら 3 つの合計は影響を受けず、2 番目のパスの結果はセル D2 のエントリに依存しません。 そのため、Excel 2003 以降のバージョンの Excel の結果は数値的により安定しています。
まとめ
1 パス アプローチを 2 パス アプローチに置き換えると、Excel 2003 以降のバージョンの Excel では、PEARSON (したがって RSQ) の数値パフォーマンスが向上します。 Excel 2003 以降のバージョンの Excel で取得した結果は、以前のバージョンの Excel で取得した結果よりも正確ではありません。
ほとんどの実用的な例では、新しいバージョンの Excel の結果と以前のバージョンの Excel の結果の間に違いは見られない可能性があります。 この動作は、一般的なデータがこの実験で示す異常な動作を示す可能性が低いために発生します。 数値の不安定性は、データに含まれる有効桁数が多く、データ値間の変動が比較的少ない場合に、以前のバージョンの Excel に表示される可能性が最も高くなります。
標本平均を求め、各二乗偏差を計算し、二乗偏差を合計することによって、サンプル平均に関する二乗偏差の合計を求める手順は、代替手順よりも正確です。 この代替手順は、いくつかのデータ ポイントでの電卓の使用に適していたため、"電卓の数式" という名前が付けられる場合がよくありました。 別の手順では、次の手順を使用しました。
- すべての観測値の平方和、サンプル サイズ、およびすべての観測値の合計が見つかりました
- すべての観測値の平方和からマイナス ([すべての観測値の合計]^2)/サンプル サイズを計算しました。
Excel 2003 以降のバージョンの Excel では、他にも多くの機能が改善されています。 これらの関数は、新しいバージョンの Excel がワンパス プロシージャを 2 パス プロシージャに置き換え、最初のパスでサンプル平均を検索し、2 番目のパスのサンプル平均に関する 2 乗偏差の合計を計算するため、改善されました。
このような関数の一覧を次に示します。
- VAR
- VARP
- STDEV
- STDEVP
- DVAR
- DVARP (ディーヴァープ)
- DSTDEV
- DSTDEVP
- 予報
- 坂
- 傍受する
- ピアソン
- RSQ
- STEYX
分析ツールの 3 つの分散分析ツールのそれぞれでも同様の改善が行われました。