Excelで相関関係を見つける方法

スピアマンの相関係数rSは、ランクに基づく最も初期のノンパラメトリック検定でした。Excelの2つの変数でそれぞれ測定された個人のサンプルの場合、アイデアは、各スコアを独自の変数内でランク付けすることです。次に、各個人について、一方のランクをもう一方のランクから引きます。相関が完全である場合（正の方向）、すべての差はゼロです。

下の画像は例を示しています。産業心理学者は、FarDrate TimepieceCorporationの20人の従業員の社会性を評価しました。スケールは1（最も社交的でない）から100（最も社交的）の範囲でした。また、FarDrateの各従業員は、自分の仕事の満足度を1（最低の満足度）から80（最高の満足度）のスケールで評価しました。帰無仮説は、社会性は仕事の満足度と相関していないというものです。対立仮説は、これら2つの変数が相関しているというものです。

データは列BとCにあり、ランクは列EとFにあります。ランクの各ペア間の違いは列Gにあります。

スピアマンのrS。

式は

ここで、dはペア間の差です。通常の相関係数の場合と同様に、帰無仮説が真の場合、rSの値はほぼゼロになります。

列Eのランクを計算するには、次のように入力します。

= RANK.AVG（B2、$ B $ 2：$ B $ 21,1）

E2に自動入力されます。列Eのランクには、次のように入力します。

= RANK.AVG（C2、$ C $ 2：$ C $ 21,1）

F2に入れて自動入力します。

相関係数を計算するために、セルJ4に複雑なExcel数式を入力する必要はありません。どうして？Excelと数理統計学が協力してうねりの驚きをもたらすため：あなたがしなければならないのはタイプすることだけです

= CORREL（E2：E21、F2：F21）

J4に。これですべてです。ランクでCORRELを使用すると、上記の式と同じ答えが得られます。（したがって、列Gでペア間のランク差を計算する必要はありません。）

計算する

Nはペアの数であり、テストにはN-2の自由度があります。

Number_of_pairsをセルI2の値の名前として定義できます。だからタイプ

= J4 * SQRT（Number_of_Pairs-2）/ SQRT（1-J4 ^ 2）

J6と

= T.DIST.2T（J6、Number_of_Pairs-2）

J7に。相関の方向が事前にわからない場合は、両側t分布関数を使用できます。また、p値が低いと、帰無仮説を棄却するように指示されます。