ExcelのCORREL関数で相関係数を計算する

以前のデータ分析では、分析の詳細によってデータの内部法則を見つけますが、実際の運用では、データの法則を分析することに加えて、最も重要なことは将来を予測することです。 なぜデータ分析が必要なのか、最終的な目標は、既存のデータに基づいて、将来の発展を予測することです。 しかし、予測は考えだけに基づくことはできません。現時点では、データツールで相関分析と回帰分析を使用する必要があります。

相関分析

相関分析（correlationanalysis）は、現象間に特定の依存関係があるかどうかを調べ、特定の依存現象間の相関の方向と程度を検討します。確率変数間の相関を調べるための統計手法です。

相関分析で、1セットのデータと別のデータの関係を分析します。つまり、これらの2セットのデータ間に関連しているかどうかを判断します。

データの相関性を判断する

さまざまな分類方法に従って、相関分析は下記に示す種類に分類できます。（この部分は統計学に関連する知識に属します。わからない場合は、最初に統計学について学習してください。）

上記では、最も常用な相関分析です。2セットのデータ間の相関程度を調べるために、相関係数rを使用して、相関性を分析します。Excelでは、相関係数を計算する方法は一般に2つがあります。1つは相関係数関数を使用し、もう1つは相関分析係数ツールを使用します。最初の方法では、Excelに2つの変数間の相関係数を計算する2つの関数、つまりCORREL関数とPEARSON関数を提供します。（ピアソン積モーメント法、ピアソンの積率相関係数とも呼ばれ、統計で一般的に使用されます） これら2つの関数は同じであるため、この記事ではCORREL関数の使い方についてのみ説明します。

実例：CORREL関数と相関分析ツールを使用して相関性を判定します。

実例の説明

あるソフトウェア会社には全国に多くの代理店があります。ある製品の年間売上高とより関連性の高い費用との相関関係を研究するために、会社のデータアナリストXは、会社の多くの代理店のうちに15店舗を観察対象として選択し、これら15店舗の年間売上高、宣伝広告費、コスト、および管理費用を同じテーブルに並べ替えました。次の図に示すようになります。

実例の分析

上記のデータを観察したところ、Xは、年間の宣伝広告費と費用の増加に伴い、年間の売上高が比較的増加することを発見しました。管理費用は他の費用とほとんど関係がないように見えますが、ただの直観的に見るとこれらのデータ間の相関の程度と方向を分析できないため、XはExcelの関数ツールと相関係数ツールを使用して分析することにします。

操作の分析

印象を深めるために、この記事では相関係数分析ツールとCORREL関数を使用して相関係数を計算します。

ステップ1

CORREL関数を挿入します。セルH1を選択し、[数式]タブにある[関数の挿入]をクリックします。[関数の挿入]ダイアログボックスをポップアップし、[関数の種類]で[統計]を選択し、[関数名]リストからCORRELを選択します。次の図に示すようになります。

ステップ2

相関係数r1を計算します。[関数の引数]ダイアログボックスが表示され、配列1にB2:B16範囲を選択し、配列2をC2:C16範囲を選択し、最後に[OK]をクリックします。下記の図が示すようになります。

ステップ3

相関係数r2を計算します。同じ方法でセルH2にCORREL関数を挿入し、配列1をB2:B16範囲を選択し、配列2をD2:D16範囲を選択し、最後に[OK]をクリックします。下記の図が示すようになります。次に、同じ方法でセルH3のr3を計算します。

ステップ4

「相関」分析ツールを選択します。 ワークシートに戻り、テーブル内の任意のセルを選択します。[データ分析]ダイアログボックスを開き、[分析ツール]リストボックスで[相関]分析ツールを選択し、[OK]をクリックします。

ステップ5

相関係数のパラメーターを設定します。ポップアップされた「相関」ダイアログボックスで、入力範囲を「$B$1:$E$16」に設定し、「データ方向」は列を選択します。「先頭行をラベルとして使用」にチェックマークをつけます。「出力オプション」の「出力先」を$G$6に設定し、[OK]をクリックします。次の図が示すようになります。

ステップ6

相関係数の計算結果を表示します。ワークシートに戻ると、H1:H3にあるCORREL関数を使用して計算された相関係数r値、「相関」分析ツールによって生成されたマトリックスが表示されます。.マトリックスのデータの交点、つまりセル範囲H8:H10は、この例で研究する相関係数rの値です。次の図に示します。

決定の分析

上記の操作では、ステップ1、2、3はCORREL関数を使用し、ステップ4、5、6はデータ分析ツールを使用します。2つの方法で取得された3つの相関係数のr値は同じであることがわかります。r値は一般的に-1から1の間にあり、r>0は正の相関、r<0は負の相関、r=0は無相関、rの絶対値が1に近いほど、相関性は強くなります。r値の計算結果によると、同社の代理店の年間宣伝広告費とコストは、同社の年間売上高と高い相関があり、高い正の相関があると結論付けることができます。したがって、企業の売上高が低いまたは高いと判断した場合は、宣伝広告費とコストに注意すべきです。管理費用と年間売上高の相関係数の絶対値は0に近く、両方の相関はほとんどありません。売上高が削減または増加する場合、管理費用の影響を考慮する必要はありません。

まとめ

この記事はこれで終わりです。上記に従って、Excelで相関分析の方法を勉強しました。これはデータ分析に非常に役立ちます。業務に影響を与える相関要因が見えます。