商品の好感度について関連性を調べる

相関関係を調べたいとき、例えば、

タバコをすう本数が多い人は、酒を飲む量も多いか
英語の点数が高い人は数学の点数も高いか
売上が高い会社ほどよく儲かっているか
商品Aを買った人と商品Bを買った人の関連性

などを調べるときに使える関数を紹介します。

関数名：CORREL関数
書式：=CORREL(データ1の列;データ2の列)
何ができる：データ1とデータ2の相関関係が分かる
平たく書くと：=CORREL(A2:A9;B2:B9)

数式にしてしまうとごく簡単な数式ですが、この関数が処理していることを理解するには、数学的に結構高等な知識(私にとっては)を必要とします。
下の例は、商品Aと商品Bの好感度の関連性を調べたものです。
それぞれ5段階評価でA列とB列に評価の点数が入っています。

送信者 OpenOffice

この関数が処理している内容を簡単に説明すると、

商品Aの好感度のそれぞれの値から商品Aの好感度の平均値を引きます。___1。。。。。C列
商品Bの好感度のそれぞれの値から商品Bの好感度の平均値を引きます。___2。。。。。D列
同じ行の___1と___2を掛けてその合計を算出します。___3。。。。。G列
___1の値を２乗してその合計を算出します。___4。。。。。E列
___2の値を２乗してその合計を算出します。___5。。。。。F列
___3を、___4と___5の積をルートで括った値で割ります。

上の１から6の経過を経て算出した値が、G12セルの値です。これらの経過を関数に任せて算出した結果がG13セルの値です。
見事に一致しました。

上の経過の意味を数学的な用語に頼らずに説明すると、
二つの商品の好感度が反比例（Aの好感度が高いほどBの好感度が低い）に近いとき、C列とD列の同じ行のセルの値の差が大きくなります。どちらかがプラスのときにどちらかがマイナスになる傾向が強くなり、結果として、G列の合計においてマイナスになります。
このとき、E列の合計とF列の合計を掛けてルートで括った値は、G列の合計にマイナス1を掛けた値に近づきます。
このために、G列の合計を、E列の合計のF列の合計の積をルートで括った値で割ったとき、返される値は-1に近づきます。

二つの商品の好感度が比例（Aの好感度が高いほどBの好感度が高い）に近いとき、C列とD列の同じ行のセルの値の差が小さくなります。どちらかがプラスのときにもう一方もプラス、あるいはどちらかがマイナスのときにもう一方もマイナスになる傾向が強くなり、結果として、G列の合計においてプラスになります。
このとき、E列の合計とF列の合計を掛けてルートで括った値は、G列の合計に近づきます。
このために、G列の合計を、E列の合計のF列の合計の積をルートで括った値で割ったとき、返される値は1に近づきます。

二つの商品の好感度がまったく相関関係を示さないとき、E列の合計とF列の合計を掛けた値は、G列の合計にから大きく乖離します。
このために、G列の合計を、E列の合計のF列の合計の積をルートで括った値で割ったとき、分母が大きくなり、返される値はゼロに近づきます。

いずれかが常に一定のとき、G列の合計はゼロになります。このために、返される結果はエラーになります(#DIV/0!）。

こんな感じです。間違ってたらすみません。
数学的な用語を使って説明すると、単相関係数は積和をｘの偏差平方和とｙの偏差平方和の積の平方根で割ることによって求められる　と言うそうです。

相関関係を調べるサンプルファイルは、calc104.odsをSKYDRIVEからダウンロードしてください。
サンプルファイルで、数字を入れ替えながら色々試していただくと、相関係数の変化の傾向をよりよく感じていただけると思います