【Excel】データ分析ツールで回帰分析・相関係数を計算する方法

Excelでデータ分析を行いたいけれど、回帰分析や相関係数をどうやって計算すれば良いか分からない。

関数を一つずつ入力するのは手間がかかり、間違いも起きやすい。

この記事では、Excelに標準搭載されている「データ分析ツール」を使って、これらの分析を簡単かつ正確に行う方法を解説します。

複雑な数式を覚える必要はありません。すぐに業務で活用できる実践的な手順を学びましょう。

データ分析ツールの概要と準備

Excelの「データ分析ツール」は、統計的な分析を簡単に行うためのアドイン機能です。回帰分析、相関、ヒストグラム、記述統計など、様々な分析手法が用意されています。これらの機能はExcelに標準で搭載されていますが、初期設定では有効になっていません。

回帰分析や相関係数を計算するには、まずこのデータ分析ツールをExcelで利用できるように設定する必要があります。設定は一度行えば、以降のExcelファイルでも利用可能です。

データ分析ツールのアドイン設定手順

データ分析ツールを利用するには、Excelのリボンに表示されるように設定します。この設定は数ステップで完了します。

1. Excelオプションを開く
   Excelの画面左上にある「ファイル」タブをクリックし、表示されたメニューから一番下にある「オプション」を選択します。
2. アドインの設定画面へ移動
   Excelのオプション画面が表示されたら、左側のメニューから「アドイン」を選択します。
3. 「分析ツール」を選択して設定
   画面下部にある「管理」のドロップダウンリストで「Excelアドイン」が選択されていることを確認します。次に「設定」ボタンをクリックします。
4. 「分析ツール」にチェックを入れる
   表示された「アドイン」ダイアログボックスで、「分析ツール」のチェックボックスにチェックを入れ、「OK」をクリックします。
5. 「データ」タブを確認
   Excelのメイン画面に戻り、「データ」タブをクリックします。「分析」グループに「データ分析」というボタンが表示されていれば設定完了です。

回帰分析の実行手順

データ分析ツールが有効になったら、回帰分析を実行してみましょう。回帰分析は、ある変数(目的変数)が他の変数(説明変数)によってどの程度説明できるかを分析する手法です。例えば、広告費(説明変数)と売上(目的変数)の関係を分析する際に使われます。

回帰分析に必要なデータの準備

回帰分析を行うためには、分析したいデータが整理されている必要があります。通常、目的変数と説明変数はそれぞれ1列にまとめ、データ範囲内にラベル(項目名)を含めることが推奨されます。

例えば、以下のようなデータがあるとします。

広告費(説明変数)	売上(目的変数)
10	100
15	120
20	150
25	170
30	200

この例では、「広告費」が説明変数、「売上」が目的変数となります。データには必ず項目名(ラベル)を付け、分析対象のデータ範囲に含めてください。このラベルは、分析結果でどの変数が何に対応するかを理解するのに役立ちます。

回帰分析の実行ステップ

準備したデータを使って、回帰分析を実行します。

1. 「データ分析」を開く
   「データ」タブの「分析」グループにある「データ分析」ボタンをクリックします。
2. 「回帰分析」を選択
   表示された「データ分析」ダイアログボックスから、「回帰分析」を選択し、「OK」をクリックします。
3. 入力範囲を指定
   「回帰分析」ダイアログボックスで、以下の設定を行います。
   入力Y範囲: 目的変数(この例では「売上」の列)のデータ範囲を指定します。
   入力X範囲: 説明変数(この例では「広告費」の列)のデータ範囲を指定します。複数の説明変数がある場合は、それら全てをまとめて指定します。
4. 「ラベル」にチェックを入れる
   データ範囲に項目名(ラベル)を含めた場合は、「ラベル」のチェックボックスにチェックを入れます。
5. 出力先を指定
   分析結果を表示する場所を指定します。「新規ワークシート」や「新しい作業ウィンドウ」を選択するのが一般的ですが、「出力先」を指定して現在のシートの任意のセルから表示させることも可能です。
6. 「OK」をクリック
   すべての設定が終わったら、「OK」ボタンをクリックして回帰分析を実行します。

指定した出力先に、回帰分析の結果が表示されます。結果には、回帰係数、切片、決定係数(R-squared)、各種統計量などが含まれます。これらの値を見ることで、説明変数が目的変数にどの程度影響を与えているかを評価できます。

相関の計算手順

相関分析は、複数の変数間の線形な関係の強さと方向を測定する手法です。相関係数は-1から+1の間の値を取り、+1に近いほど強い正の相関、-1に近いほど強い負の相関、0に近いほど相関がないことを示します。

相関分析に必要なデータの準備

相関分析も回帰分析と同様に、分析したいデータが列ごとに整理されている必要があります。通常、各列が1つの変数を表すようにデータを配置します。回帰分析とは異なり、目的変数と説明変数を区別する必要はありません。すべての変数間の相関を一度に計算できます。

以下のようなデータで、各列の変数間の相関を計算してみましょう。

変数A	変数B	変数C
10	20	5
12	25	7
15	30	8
18	35	10
20	40	12

このデータでは、「変数A」「変数B」「変数C」の3つの変数間の相関を計算します。ここでも、データ範囲に項目名(ラベル)を含めることが推奨されます。

相関分析の実行ステップ

準備したデータで相関分析を実行します。

1. 「データ分析」を開く
   「データ」タブの「分析」グループにある「データ分析」ボタンをクリックします。
2. 「相関」を選択
   表示された「データ分析」ダイアログボックスから、「相関」を選択し、「OK」をクリックします。
3. 入力範囲を指定
   「相関」ダイアログボックスで、分析したいすべての変数を含むデータ範囲を指定します。
4. 「ラベル」にチェックを入れる
   データ範囲に項目名(ラベル)を含めた場合は、「ラベル」のチェックボックスにチェックを入れます。
5. 出力先を指定
   分析結果を表示する場所を指定します。「新規ワークシート」や「新しい作業ウィンドウ」を選択するのが一般的です。
6. 「OK」をクリック
   すべての設定が終わったら、「OK」ボタンをクリックして相関分析を実行します。

出力結果は、変数間の相関係数を示す行列形式になります。対角線上の値は常に1(自分自身との相関)となり、それ以外の値が各変数ペアの相関係数を示します。

結果の解釈と注意点

データ分析ツールで得られた回帰分析や相関分析の結果は、正しく解釈することが重要です。

回帰分析結果の解釈

回帰分析の結果では、特に以下の点に注目します。

• 決定係数(R-squared): 0から1の間の値を取り、目的変数の変動のうち、説明変数によって説明できる割合を示します。1に近いほど、モデルの当てはまりが良いとされます。
• 回帰係数: 各説明変数が1単位増加したときに、目的変数がどれだけ変化するかを示します。正の値であれば増加、負の値であれば減少を示します。
• P値(Significance F / P-value): 各説明変数が統計的に有意であるかを示す指標です。一般的に0.05未満であれば、その変数は統計的に有意であると判断されます。

ただし、決定係数が高くても、必ずしも因果関係があるとは限りません。相関関係は因果関係を意味しないため、結果の解釈には注意が必要です。

相関分析結果の解釈

相関係数の値の絶対値が大きいほど、変数間の線形関係は強いと判断できます。

• +1に近い: 強い正の相関。一方の変数が増加すると、もう一方の変数も増加する傾向があります。
• -1に近い: 強い負の相関。一方の変数が増加すると、もう一方の変数は減少する傾向があります。
• 0に近い: 線形な相関はほとんどないと考えられます。

相関分析でも、相関関係が必ずしも因果関係を示すわけではありません。例えば、アイスクリームの売上と水難事故件数は夏場にどちらも増加するため、高い正の相関を示しますが、アイスクリームが水難事故を引き起こすわけではありません。両者とも「気温の上昇」という共通の原因によって影響を受けていると考えられます。

よくある誤解と注意点

• データ形式: 分析対象のデータが数値データであることを確認してください。文字列や空白が多いと、正しく計算されない場合があります。
• ラベルの有無: 「ラベル」のチェックボックスは、データ範囲に項目名を含めた場合にのみチェックを入れてください。含めていないのにチェックを入れると、項目名がデータとして誤って解釈されます。
• 変数の選択: 回帰分析で目的変数と説明変数を間違えると、意味のない結果になります。分析したい関係性を明確にしてから、変数を正しく指定してください。
• 過学習: 説明変数が多すぎると、特定のデータセットにはよく当てはまるものの、新しいデータには当てはまらない「過学習」の状態になることがあります。必要最低限の説明変数に絞ることが重要です。
• 外れ値: データの中に極端に大きい値や小さい値(外れ値)があると、回帰分析や相関分析の結果に大きな影響を与えることがあります。外れ値の有無を確認し、必要に応じて処理を検討してください。

Excel関数との比較

Excelには、回帰分析や相関係数を計算するための関数も用意されています。データ分析ツールと比較してみましょう。

関数による計算

例えば、回帰分析の係数や切片は以下の関数で計算できます。

• SLOPE関数: 回帰直線の傾き(係数)を計算します。
• INTERCEPT関数: 回帰直線の切片を計算します。
• LINEST関数: 回帰分析の統計量を配列として返します。より詳細な分析が可能です。

相関係数は、以下の関数で計算できます。

• CORREL関数: 2つのデータセット間の相関係数を計算します。
• PEARSON関数: CORREL関数と同様に、2つのデータセット間の相関係数を計算します。

データ分析ツールとの違い

データ分析ツールの主な利点は、一度の設定で複数の統計分析をまとめて実行できることです。特に、回帰分析では決定係数やP値といった統計量も同時に出力されるため、迅速な分析が可能です。

一方、関数は特定の計算のみを行いたい場合に便利です。例えば、単一の変数間の相関だけを知りたい場合は、CORREL関数を使う方が手軽です。LINEST関数は配列関数であり、入力と出力の理解に少し慣れが必要ですが、より柔軟な分析ができます。

どちらの方法を選択するかは、分析の目的、必要な統計量、そして使い慣れているかどうかによって異なります。まずはデータ分析ツールで全体像を掴み、必要に応じて関数を使い分けるのが効率的でしょう。

項目	データ分析ツール	Excel関数
主な用途	多変量解析、統計的サマリー	特定の値の計算
出力内容	回帰分析、相関、ヒストグラム等、包括的な統計量	単一の係数、相関係数、配列結果等
設定の手間	初回のアドイン設定が必要	不要(関数入力のみ)
分析の容易さ	GUI操作で簡単	数式入力が必要
柔軟性	標準的な分析に特化	高い(組み合わせ自由)

データ分析ツールは、複雑な統計分析をGUI操作で簡単に行いたい場合に非常に強力なツールです。特に、初めて回帰分析や相関分析を行う方にとって、理解を深めるための第一歩となるでしょう。

この記事で解説した手順を参考に、ぜひExcelでのデータ分析に挑戦してみてください。

さらに高度な分析を行いたい場合は、Excelの他の分析機能(分散分析、移動平均など)や、統計解析ソフトの利用も検討すると良いでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】