エクセル分析ツール

エクセル分析ツール、関数を使った相関分析のやり方と結果の見方

2024.05.31
記事内に広告が含まれています。

「この2つのデータは関係があるのか」を調べるのが相関分析です。温度と収率、硬度と引張強度、原料の純度と製品品質など、製造現場では2つの変数の関係を確認したい場面が頻繁にあります。

この記事では、Excelで相関係数を計算して散布図を描くまでの手順と、相関係数の読み方・使う際の注意点を解説します。

【この記事でわかること】
・エクセル「分析ツール」と関数を使った相関係数の計算方法
・相関係数の見方と意味

相関分析でわかること

相関分析は、2つの変数の間に「直線的な関係があるかどうか」を数値で表します。その数値がピアソンの相関係数（r）です。\[ r = \frac{\sum_{i=1}^{n}(x_i – \bar{x})(y_i – \bar{y})}{\sqrt{\sum_{i=1}^{n}(x_i – \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}(y_i – \bar{y})^2}} \]

rは必ず -1 以上 +1 以下の値になります。

r の値意味散布図のイメージ
r = +1完全な正の相関点が右上がりの直線上に並ぶ
+0.7 〜 +1強い正の相関右上がりにまとまっている
+0.3 〜 +0.7中程度の正の相関右上がりだがばらつきあり
-0.3 〜 +0.3ほぼ無相関特定の方向性がない
-0.7 〜 -0.3中程度の負の相関右下がりだがばらつきあり
-1 〜 -0.7強い負の相関右下がりにまとまっている
r = -1完全な負の相関点が右下がりの直線上に並ぶ

ただし、相関分析でわかるのは「直線的な関係の強さ」だけです。「どちらが原因でどちらが結果か（因果関係）」はわかりません。この点は後述します。

エクセルの分析ツールを使った相関分析の手順

相関分析は”勤続年数と年収”、”広告費と売上”など、2以上の変数間の関係性を分析したいときに使用します。

今回の相関分析は”気温”と”アイスクリームの売り上げ”に関するデータを使用して説明します。データはchatGPTを使って生成した仮想データです。

まず、エクセルの分析ツールを使って相関分析を行う手順を解説します。

分析ツールの有効化

エクセルで回帰分析を行うためには、「分析ツール」を有効にする必要があります。このツールはデフォルトでは有効化されていないことが多いので、まずは「データ分析」ツールを有効化する必要があります。

データ分析ツールの有効化手順

  1. Excelを開き、上部にある「ファイル」タブをクリックします。
  2. メニューの一番下にある「オプション」を選択します。
  3. 左側のメニューから「アドイン」カテゴリを選択し、「分析ツール」を見つけます。
  4. 下部の「管理」ボックスで「Excelアドイン」を選択し、「設定」をクリックします。
  5. 表示されるリストから「分析ツール」にチェックを入れ、「OK」をクリックします。

これで、分析ツールが使用可能になります。

具体例で相関分析の手順を解説

次に、具体的な相関分析の手順を説明します。

単回帰分析の手順

  1. データ分析ツールを選択：Excelの「データ」タブに移動し、右端にある「データ分析」を選択
  2. 相関を選択：表示されるリストから「相関」を選択
  3. 入力範囲の指定：
    • 入力範囲：相関を計算したいデータの範囲を選択します。（例：$A$1:$C$13）
    • ラベル：データの最初の行にラベルが含まれている場合は、「ラベル」にチェックを入れます。
    • 出力範囲：分析結果を表示するセル範囲を指定します。（例：$E$1）
  4. OKをクリック：設定が完了したら、「OK」ボタンをクリック。エクセルが相関分析を実行し、指定したセル範囲に結果を出力します。

例題の相関分析結果

  • 月と平均気温、アイスクリームの売り上げの相関係数
    月と平均気温の相関係数は”0.249”、月とアイスクリームの売り上げの相関係数は”0.233”でありどちらもほとんど相関関係がないことがわかります。
  • 平均気温とアイスクリームの売り上げの相関係数
    平均気温とアイスクリームの売り上げの相関係数は”0.972”と非常に強い正の相関関係であることがわかりました。つまり、平均気温が高くなるにつれアイスクリームの売り上げが上昇すると言えます。

季節と気温が相関していそうだから月と気温も相関関係にあるのでは？
結果を見て「夏は気温が高く冬は気温が低いので月と気温は相関性がありそう」と疑問に思った人もいるかもしれません。先ほどのデータを見ると7月の平均気温がピークに1月、12月にかけて減少する非線形（直線ではない形状）であることがわかります。相関分析は線形関係（直線的な関係）の分析に適した方法です。したがって、月が増すと気温が上がるとは言えず、相関関係はないという結果になります。

エクセルの関数を使って相関係数を計算する場合

分析ツールを使う以外に、関数を使っても相関分析を行うことができます。ここでは、代表的な関数である CORREL 関数と PEARSON 関数の使用方法について説明します。

CORREL関数の使用方法
CORREL 関数は、2つの変数間の相関係数を計算するための関数です。相関係数は、-1から1の範囲の値を取り、2つの変数の間の関係の強さと方向を示します

  1. データを準備：エクセルに”気温”と”アイスクリームの売り上げ”のデータをB1~C13に準備
  2. 関数を入力：
    • 気温のデータ範囲が B2:B13、アイスクリームの売り上げが C2:C13 に入力されていると仮定します。
    • CORREL 関数を入力します。=CORREL(B2:B13, C2:C13)
  3. 結果の確認：Enterキーを押すと、指定した2つのデータ範囲の相関係数が表示されます

PEARSON関数の使用方法
PEARSON 関数も、2つの変数間のピアソンの相関係数を計算するための関数です。実際には CORREL 関数と同じ結果を返しますが、名前が異なるだけです。使用方法もCORREL関数と同じです。

相関係数を使うときの注意点

1. 相関は因果関係ではない

相関係数が高くても、一方が他方の原因とは限りません。どちらも第三の変数（交絡因子）の影響を受けている可能性があります。例として、「アイスクリームの売上と溺死者数の相関が高い」という話がありますが、これは気温という第三変数が両方に影響しているためです。

「なぜ相関があるのか」を工程の知識から説明できるかどうかを必ず確認してください。

相関関係
2つの変数が一緒に変動する傾向があることを意味します。しかし、相関関係は必ずしも一方が他方を引き起こす（因果関係がある）ことを意味しません。

  • 例1：アイスクリームの売上と気温の間に強い正の相関があるとします。気温が上がるとアイスクリームの売上も増えますが、これは気温がアイスクリームの売上を引き起こしているとは限りません。実際には、暑い天候が人々にアイスクリームを買いたくさせるという共通の要因が存在します。
  • 例2：広告費と売上に相関関係がある場合、広告費を増やすと売上も増える傾向が見られるかもしれませんが、これも因果関係を示すものではありません。他の要因（例えば、季節要因や市場のトレンド）が関係している可能性もあります。

因果関係
一方の変数が他方の変数を直接引き起こす関係（原因）です。因果関係を証明するには、相関関係だけでは不十分であり、実験や詳細な分析が必要です。

  • 例1：科学実験において、特定の薬を投与したグループと投与しなかったグループの比較を行うことで、その薬が健康改善に因果的な効果を持つかどうかを検証する。
  • 例2：ビジネスにおいて、特定のマーケティングキャンペーンを実施した地域としなかった地域の売上データを比較することで、そのキャンペーンが売上増加に因果的な影響を与えるかどうかを確認する。

2. 外れ値の影響を受けやすい

ピアソンの相関係数は外れ値に敏感です。1点の外れ値で相関係数が大きく変わることがあります。散布図で確認して、外れ値がないかを必ずチェックしてください。

3. 非線形の関係は検出できない

温度と反応速度のように、U字型や指数関数型の関係がある場合、ピアソンの相関係数は低く出ても実際には強い関係があります。散布図で全体の形を確認することが重要です。

4. 相関係数の有意性を確認する

サンプル数が少ないと、本当は無相関でも偶然に高い相関係数が出ることがあります。計算した相関係数が統計的に有意かどうかを確認するには、t検定を使います。\[ t = \frac{r\sqrt{n-2}}{\sqrt{1-r^2}} \]

このt値を自由度 n-2 のt分布と比較します。Excelでは =T.DIST.2T(ABS(t値), n-2) でp値を計算できます。p < 0.05 なら有意な相関があると判断します。

検定の考え方はt検定とは？具体例でわかりやすくやり方を解説を参照してください。

相関分析から回帰分析へ

相関分析で2変数の関係が確認できたら、次のステップとして回帰分析があります。相関分析は「関係の強さ」を調べますが、回帰分析は「xがいくつ変わるとyはいくつ変わるか」という定量的な予測式を作ります。

説明変数が1つの場合は単回帰分析、複数ある場合は重回帰分析を使います。

まとめ

  • 相関係数（r）は −1〜+1 の値をとり、直線的な関係の強さと方向を示す
  • ExcelではCORREL関数、または分析ツールの「相関」で計算できる
  • 相関係数だけでなく、必ず散布図で視覚的に確認する
  • 相関は因果関係ではない。工程の知識に基づいた解釈が必要
  • 外れ値・非線形関係・サンプル数の小ささに注意する
  • 関係の定量化には回帰分析へ進む

相関分析は回帰分析の入口として使うことが多いです。「まず散布図と相関係数で関係を確認して、有意なら回帰分析で予測式を作る」という流れを覚えておくと、データ分析の進め方が整理されます。

なお、算出した相関係数が「偶然の産物ではない」かどうかを確かめるには、有意性検定が必要です。t検定を使った検定手順とExcelでの計算方法は、相関係数の有意性検定｜t検定でρ=0を確認する手順とExcel計算で詳しく解説しています。

なお、複数の変数が互いに相関している場合、それらをひとつの「総合スコア」に集約する主成分分析（PCA）も有効です。相関分析で変数間の関係を確認したあと、PCAで次元削減するという流れがよく使われます。

相関の強い変数を複数もつデータを自動的にグループ化したい場合は、クラスター分析（ウォード法・k-means）も活用できます。相関分析で変数間の関係を把握したあとにクラスタリングを組み合わせると、データの構造をより立体的に捉えられます。

外れ値がある場合やデータが順序尺度の場合は、スピアマン順位相関係数を使うとより正確な関係の強さを測ることができます。ピアソンとの使い分けも含めて解説しています。

タイトルとURLをコピーしました