相関係数の求め方を解説:
公式・計算例・Excelの手順と注意点


相関係数の求め方を解説:公式・計算例・Excelの手順と注意点

「商品を使いやすいと評価した人ほど、全体の満足度も高いのだろうか」。こうした2つの数値の関係を調べるときに役立つのが、相関係数です。平均点を比べるだけではわからない、回答者ごとの評価のつながりを数値で確かめられます。

ExcelならCORREL関数で計算できますが、値だけを見て「この機能を改善すれば満足度が上がる」と結論づけることはできません。何を計算しているのか、結果をどう読むのかも押さえておきましょう。

本記事では、5人分の回答を使って手計算の流れを説明し、同じデータでExcelの操作と散布図の見方を紹介します。初めて計算する方も、表と図を見ながら手順をたどれる内容です。

AIがレポートまで!
セルフ型ネットリサーチFastaskはこちら

圧倒的なスピード・低コストでご利用いただける
セルフ型ネットリサーチ「Fastask」をご紹介します。

相関係数の求め方は?公式とExcelの計算方法

相関係数は、2つの数値が「一緒に増えるか」「一方が増えると他方は減るか」を−1~1で表します。たとえば、使いやすさの点数が高い人ほど、総合満足度も高い傾向があるかを調べられます。
本記事では、点の並びがどれほど一直線に近いかを見る「ピアソンの相関係数」を扱います。主な求め方は、公式による手計算とExcelです。

手計算では共分散を標準偏差の積で割る

手計算では、次の公式を使います。XとYは、使いやすさと総合満足度など、比べたい2項目です。「積」は掛け算をした値のことで、rは相関係数を表す記号です。

r   =   X と Y の共分散 X の標準偏差  ×   Y の標準偏差 r\ =\ \frac{XとYの共分散}{Xの標準偏差\ ×\ Yの標準偏差}

共分散は、2項目の点数が平均より高い・低いときに、同じ方向に動くかを見る数値です。標準偏差は、それぞれの点数の散らばりを表します。

共分散を2つの標準偏差で割るのは、点数の単位やばらつきの違いを調整するためです。たとえば、100点満点を10点満点に換算しても、同じ基準で関係を比べられます。各数値の求め方は、次の章で具体的に見ていきます。

ExcelではCORREL関数を使う

Excelでは、2項目のセル範囲をCORREL関数に指定します。使いやすさがB2~B6、総合満足度がC2~C6に入っているなら、式は次のとおりです。

=CORREL(B2:B6,C2:C6)

B2:B6は「B2からB6まで」を意味します。2項目は、同じ行に同じ人の回答を並べることが大切です。ここで紹介する5人分の例では、0.80と計算されます。

相関係数を手計算で求める5つのステップ

同じ5人が、商品の「使いやすさ」と「総合満足度」を100点満点で評価したとします。以下は計算を説明するための架空の回答です。Xを使いやすさ、Yを総合満足度として、順に求めましょう。

回答者 使いやすさX(点) 総合満足度Y(点)
A 40 30
B 50 50
C 60 40
D 70 70
E 80 60

1.2種類のデータの平均値を求める

まず、XとYそれぞれの合計を人数で割り、平均値を出します。使いやすさの平均は60点、総合満足度の平均は50点です。

Xの平均=(40+50+60+70+80)÷5=60点
Yの平均=(30+50+40+70+60)÷5=50点

2.それぞれのデータの偏差を求める

各人の点数から、その項目の平均を引きます。この差が「偏差」です。
Aさんなら、Xは40−60=−20点、Yは30−50=−20点です。どちらも平均より20点低いことがわかります。

Xの偏差は−20・−10・0・10・20、Yの偏差は−20・0・−10・20・10です。平均より高い点数はプラス、低い点数はマイナスになります。

3.偏差の積から共分散を求める

次に、同じ人のXとYの偏差を掛け合わせます。両方とも平均より低いAさんは「−20×−20=400」でプラスです。両方高い場合もプラスになり、一方だけが平均より高い場合はマイナスになります。

回答者 Xの偏差 Yの偏差 掛け合わせた値
A −20 −20 400
B −10 0 0
C 0 −10 0
D 10 20 200
E 20 10 200
合計 0 0 800

掛け合わせた値を合計し、人数の5で割ったものが共分散です。

共分散=800÷5=160

今回はプラスなので、Xが平均より高い人はYも高く、Xが低い人はYも低いという傾向があります。ただし、160という数値だけで関係の強さを比べることはできないため、標準偏差で調整します。

4.それぞれのデータの標準偏差を求める

標準偏差は、各項目の点数が平均からどの程度散らばっているかを表します。偏差を2乗して平均した「分散」を出し、点数と同じ単位に戻すため、平方根を取ります。2乗は同じ数を2回掛けること、平方根は2乗するとその数になる値です。

Xの分散=(400+100+0+100+400)÷5=200
Yの分散=(400+0+100+400+100)÷5=200

XもYも、標準偏差は√200≒14.14点です。約14.14を2乗すると200になるため、この値になります。ここでは5人分の平均として、共分散も分散も、合計を5で割っています。

5.公式に当てはめて相関係数を求める

共分散160を、2項目の標準偏差を掛けた値で割ります。この時、途中で14.14など小数点を丸めず、√200のまま計算すると、細かなずれを防げます。

相関係数=160÷(√200×√200)=160÷200=0.80

使いやすさの点数が高い人ほど、総合満足度も高い傾向がある結果です。0.80は「80%の人が満足している」という意味ではありません。数値の読み方は、後ほど散布図とあわせて説明します。

Excelで相関係数を求める方法

手計算の流れがつかめたら、同じ5人分の回答をExcelで計算してみましょう。点数を並べる、関数を入力する、散布図で確かめる、の順に進めます。

同じ回答者の2項目を同じ行に並べる

A列に回答者ID、B列に使いやすさ、C列に総合満足度を入力します。1行目を見出しにし、2~6行目にA~Eさんの回答を並べてください。Aさんの40点と30点は、どちらも2行目に入れます。

XとYを別々に小さい順へ並べ替えると、別の人同士の点数を組み合わせてしまいます。並べ替えるときは、回答者IDを含めて行全体を動かしましょう。

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

空いているセルに「=CORREL(B2:B6,C2:C6)」と入力すると、手計算と同じ0.80が表示されます。小数点以下の表示桁数を2桁にすれば、「0.8」を「0.80」と表示できます。数値の意味は同じです。

同じ人の2項目を並べ、CORREL関数で計算

同じ回答者の使いやすさXと総合満足度YをB2からB6、C2からC6に入力し、CORRELで0.80を求める例。
空いているセルに入力
=CORREL(B2:B6,C2:C6)
計算結果
0.80

B2:B6とC2:C6は、同じ回答者の5組を指定
※説明用に再構成した入力例。IDの並びをそろえ、2列を別々に並べ替えない。

図1 回答者ごとに2項目を並べたExcelの入力例

範囲には合計行や別の回答者を混ぜず、同じ5人分を指定します。未回答や文字が含まれる場合は、計算対象となる組が変わるため、後述の注意点も確認してください。

散布図を作り、データの形を確認する

散布図は、2項目の点数を横軸と縦軸に取り、1人分の回答を1つの点で表すグラフです。横軸をX、縦軸をYとすれば、Aさんは「横40・縦30」の位置に置かれます。

  • B1~C6を選び、「挿入」から「散布図」を選ぶ
  • 点だけを表示する種類を選び、横軸を使いやすさ、縦軸を総合満足度とする
  • 各軸に項目名と「点」を表示し、点の並びや大きく離れた回答がないか確認する

点が右肩上がりに並べば、使いやすさが高い人ほど総合満足度も高い傾向が見えます。数値が0.80だった理由を、目でも確かめられます。散布図の代表的な形は次の章の図3で比較します。

5人の回答を、1人につき1つの点で表す

使いやすさXと総合満足度Yの散布図。Aは40点と30点、Bは50点と50点、Cは60点と40点、Dは70点と70点、Eは80点と60点。相関係数0.80。

Aさんの点の位置

横40点・縦30点

全体では右肩上がり

Xが高い人ほど
Yも高い傾向

相関係数

r = 0.80

※本文と同じ架空の回答例。点同士は線で結ばず、並び方を確認します。

図2 本文の5人分の回答を散布図にした例(相関係数0.80)

相関係数の見方と読み取り方

相関係数は、「プラスかマイナスか」と「0からどれだけ離れているか」を分けて読みます。主な例は次のとおりです。

係数の例 方向 直線的な関係 点の並び方
0.80 正 比較的強い 右肩上がり
−0.80 負 比較的強い 右肩下がり
0 直線的な方向なし 直線的な相関なし 曲線の関係が残る場合もある

符号で正・負の方向を読み取る

プラスは、一方の数値が大きい人ほど、もう一方も大きい傾向です。今回の使いやすさと総合満足度が当たります。
マイナスは、一方が大きいほど、もう一方が小さい傾向です。たとえば、待ち時間が長い人ほど満足度が低い場合に、負の相関が表れます。

マイナスは「関係が弱い」という意味ではありません。増減の向きが逆であることを表しています。

絶対値で直線的な関係の強さを確かめる

絶対値とは、プラス・マイナスを外した数の大きさです。0.80と−0.80の絶対値は、どちらも0.80です。値が1に近いほど点が一直線に並びやすく、0に近いほど直線的な傾向が弱くなります。

相関係数は、点の並び方とセットで読む

4つの散布図の1つ目。正の相関 r=0.80。横軸X、縦軸Y。全て架空データ。 4つの散布図の2つ目。負の相関 r=−0.80。横軸X、縦軸Y。全て架空データ。 4つの散布図の3つ目。格子状で相関 r=0。横軸X、縦軸Y。全て架空データ。 4つの散布図の4つ目。U字型で相関 r=0。横軸X、縦軸Y。全て架空データ。

X・Y:2つの数値データ。全て説明用の架空例。点1つがデータ1組。

図3 正・負・0の相関と、0でも曲線の関係がある例

たとえば、−0.80は0.30よりも直線的な関係が強いと読めます。ただし、「0.7以上なら必ず重要」といった共通の基準はありません。調べる内容や回答数、点の並びも確認して判断します。
図3の右下のように、U字の関係があっても係数が0になる場合があります。「0だから何も関係がない」とは限りません。

相関係数を使う際の注意点

計算できた相関係数を、そのまま原因や改善の答えとして扱わないことが大切です。回答の組み合わせや、グラフの形も確認しましょう。

相関関係と因果関係を区別する

相関関係は「2つの数値に一緒に変わる傾向があること」、因果関係は「一方の変化が、もう一方を変えること」です。使いやすさと満足度の相関が強くても、使いやすさを直せば必ず満足度が上がるとは言い切れません。

たとえば、商品に慣れた人が両方を高く評価しているのかもしれません。使いやすさ以外の理由も確かめ、改善前後を比べるなど、追加の調査で検証します。

外れ値や曲線的な関係を散布図で確認する

ほかの回答から大きく離れた値を「外れ値」といいます。多くの点がまとまっていても、1組だけ離れた点があると、相関係数が大きく変わる場合があります。入力ミスや点数の付け方を確認し、正しい回答なら理由を調べましょう。都合が悪いという理由で削除してはいけません。

また、図3のU字型のような関係は、直線の傾向を見る係数では捉えきれません。相関係数と散布図をセットで見ると、こうした見落としを防げます。

データの対応と欠損を確認する

同じ人のXとYが、同じ行にあるかを確認します。たとえば、使いやすさはAさん、総合満足度はBさんという組み合わせでは、求めたい関係を計算できません。

片方が未回答なら、0を入れず、「両方の数値がそろった回答」を1組として扱います。0は実際の評価点として計算されるためです。何組を計算に使ったか、未回答をどう扱ったかも記録しましょう。欠けたセルを詰めるために片方の列だけ上へ動かすと、対応がずれるので注意してください。

データの尺度に合う方法を選ぶ

本記事の方法は、点数、金額、時間など、数の大小や差を比べるデータで使います。「1=店舗、2=EC、3=電話」といった分類用の番号は、大小を表していないため、そのまま計算に使いません。

商品の人気順位や、「不満・普通・満足」のように順番はあっても差の大きさがはっきりしない回答には、順位に置き換えて関係を見る「スピアマンの相関係数」が候補です。CORRELで選択肢番号をそのまま計算する方法とは異なります。

アンケート調査で相関係数を活用する方法

アンケートでは、総合満足度と項目別評価の関係から、詳しく調べる箇所を見つけられます。相関係数を「次に何を確かめるか」を考える手がかりにしましょう。

総合満足度と各評価項目の関係を確かめる

今回の例では、使いやすさを高く評価した人ほど総合満足度も高い傾向がありました。同じ回答者について、価格、機能、サポートなどの点数も集めれば、それぞれと総合満足度の関係を比べられます。

比較するときは、項目ごとの平均点や計算に使った人数も並べます。強い相関があっても、すでに高評価で改善の余地が小さいこともあります。関係の強さと、実際に困っている人の多さを分けて確認すると、検討を進めやすくなります。

関連の強い項目を追加調査の仮説にする

使いやすさとの相関が強かったなら、「操作に迷うことが不満につながっているのではないか」と仮説を立てられます。低評価の人の自由記述を読む、初めて使う人と長く使う人で分ける、操作時の困りごとを聞く、といった調査で確かめます。

数値の関係を見つけ、理由を調べる

  1. 相関を確かめる
    使いやすさと満足度
    今回の例では r = 0.80
  2. 理由を絞る
    低評価の人は
    どこで困ったのか?
  3. 追加調査で確かめる
    自由記述や
    インタビューで確認
相関が強いだけでは、
原因や改善効果までは決まらない
図4 相関係数から追加調査につなげる流れ

商品への好みなど、複数の評価に共通する理由がある可能性も残ります。相関係数の高さだけで改善の順番を決めず、回答の背景や改善にかかる費用も踏まえて検討しましょう。

質問の準備から集計・分析までの基本を整理したい方は、統計知識や必要な回答数の考え方も紹介する「マーケティングリサーチ実践ガイド」をご覧ください。

マーケティングリサーチ実践ガイド

マーケティングリサーチを成功させるために必要な統計知識をはじめ、サンプルサイズの決め方や調査実施・集計分析の方法などをわかりやすく解説します。

アンケートの実施・集計を効率化するFastask

相関係数を活用するには、比べたい2項目を同じ人に聞き、対応する回答を集める準備が必要です。調査票の確認や回答の収集、集計に負担を感じる場合は、セルフ型ネットリサーチのFastaskを検討できます。

リサーチャーの調査票チェックを受けられる

Fastaskでは、作成した調査票を専任リサーチャーが無料で確認します。質問の表現に加え、回答に応じた分岐などもチェックを受けられるため、調査に不慣れな担当者も進めやすくなります。

たとえば、「使いやすさ」と「満足度」を1つの質問にまとめると、2項目の関係は調べられません。それぞれ別の質問で点数を聞き、使ったことがない人の回答方法も決めておくことが大切です。

回答データの集計やレポート作成の負担を減らせる

Fastaskは、モニターへのアンケート配信から集計、分析レポートの提供まで対応しています。生成AIを活用した要約や考察、グラフ作成により、調査結果を資料に整理する負担を軽くできます。

相関係数の計算には、本記事で紹介したExcelを使います。Fastaskで調査を実施して結果を整理し、Excelで項目間の関係も確認すれば、追加で調べたい内容を具体化しやすくなるでしょう。

相関係数の求め方に関するよくある質問

最後に、計算の手間を減らす方法や、Excelで迷いやすい点を確認します。

相関係数を簡単に求める裏ワザはありますか?

手計算には、各人の偏差を求めず、点数の合計・2乗の合計・XとYを掛けた値の合計から計算する式もあります。別の指標になるわけではなく、同じ相関係数の式を整理したものです。

r   =   n Σ X Y   −   ( Σ X ) ( Σ Y ) { n Σ X 2   −   ( Σ X ) 2 } { n Σ Y 2   −   ( Σ Y ) 2 } r\ =\ \frac{nΣXY\ -\ (ΣX)(ΣY)}{\sqrt{\{nΣX²\ -\ (ΣX)²\}\{nΣY²\ -\ (ΣY)²\}}}

nはデータの組数、Σは合計です。ΣX²は「Xをそれぞれ2乗してから足す」、(ΣX)²は「Xを全部足してから2乗する」ので、計算の順番が違います。

今回の5組では、ΣX=300、ΣY=250、ΣXY=15,800、ΣX²=19,000、ΣY²=13,500です。上の式に入れると4,000÷5,000=0.80になります。実務で手早く求めるなら、CORREL関数を使う方が簡単です。

散布図の近似線に表示されるR²は相関係数ですか?

同じものではありません。R²は「決定係数」といい、点のばらつきに近似線がどの程度合っているかを見る数値です。
1本の直線の近似線を使い、「切片=0」(Xが0ならYも0とする指定)を使わない設定では、R²は相関係数rを2乗した値です。たとえば、rが0.80でも−0.80でも、R²は0.64になります。増減の方向はわからないので、相関係数を知りたい場合はCORRELで求めましょう。

相関係数が計算できないときは何を確認しますか?

まず、指定した範囲と回答データを確認します。主な確認点は次の3つです。

  • 2つの範囲の行数が同じか。B2:B6とC2:C5などのずれは、\#N/Aの原因になる
  • 両方に数値が入った組があるか。未回答や文字だけの列を指定していないか確認する
  • 片方の点数が全員同じではないか。全員70点など、ばらつきが0なら相関係数は求められない

両方が数値の組は少なくとも2組必要です。空の範囲や標準偏差が0の範囲では、\#DIV/0\!になることがあります。エラーを消すために0を追加せず、元の回答と範囲を見直してください。

まとめ

相関係数は、2つの数値が一緒に増えるか、逆に動くか、どの程度一直線に並ぶかを確かめる指標です。手計算では共分散を2つの標準偏差の掛け算で割り、ExcelではCORREL関数を使います。

計算には同じ人の回答を1組として使い、結果は散布図とあわせて確認しましょう。0でも曲線の関係がある場合があり、強い相関があっても原因や改善効果までは決まりません。アンケートでは、低評価の理由などを追加で調べる手がかりとして役立ちます。

調査の準備や回答収集、結果の整理を効率よく進めたい方は、Fastaskをご活用ください。調査票のチェックや集計・レポート作成を支援し、分析に使うデータを集める段階から取り組みやすくします。

AIがレポートまで!
セルフ型ネットリサーチFastaskはこちら

圧倒的なスピード・低コストでご利用いただける
セルフ型ネットリサーチ「Fastask」をご紹介します。

関連記事