ベーシクスと、Excelでこれらを描画・計算するための一連の手順。TOOLS2019/9/4イントロダクション相関分析のツール,散布図・相関係数を描画・計算します。変数どうし,互いの関係をビジュアルに表現するのが散布図で,定量的に表現するのが相関係数です。MORE相関係数(r)は-1から1の範囲の値をとります。このとき,-1≦r<0の範囲を負の相関,r=0のものを無相関,0<r≦1の範囲を正の相関と呼びます。相関は,rが負であれば-1,正であれば1に近づくほど強く,反対に0に近づくほど弱いと判断します。した相関の強弱については,相関分析に触れた書籍などの記述において,細かな階層的判断基準が提示されているのを見かけることもあると思います。それは目安としてユーザーの判断をたすけてくれますが,相関係数が順序尺度である以上,たとえば所属部門での指針,あるいは過去との比較を繰り返すなどした自らの経験にも照らしつつ,慎重に判断を補っていくこともまた一方で大切なことかと思います。以下,Excelによる散布図の描き方と相関係数の求め方です。とも基本的には同じです。元データ元のデータです。顧客マスタに存在する,ある業種の顧客すべてについて,購買指数(ここでは,売上金額と販売回数の各実績値をもとに算出した値を意味します)と直近の評点(ここでは,企業活動に係る総合スコアのようなものと考えてください)を抽出したものです。このデータから,相関分析に必要な散布図を作成し,相関係数を求めます。散布図からデータを眺める大きな流れの第一として,散布図を作成してデータの概観から捕捉していきたいと思います。「購買指数」列と「直近の評点」列のデータを見出しを除きすべて選択します。Y)またはバブルチャートの挿入ボタンをクリックします1。プルダウンメニューの散布図ボタンをクリックします2。散布図(マーカーのみ)ボタンプレーンな散布図が出来ました。このとき,グラフの横軸がシートのB列の値(第1変数:「購買指数」列のデータ),縦軸がシートのC列の値(第2変数:「直近の評点」列のデータ)です。どっちが横でどっちが縦?第1・第2変数をそれぞれ横縦どちらの軸に置くべきかについては,ここでのような「相関分析」の範囲に限れば,自由に決めればOKです。Excelの仕様としては,シートの上で最も左方に存在する変数(ここでは「購買指数」)が横軸に振られます。ここでは扱いませんが2つの変数の関係にはっきりとした方向性をおく「回帰分析」につなげることが視野にある場合には,説明変数とする方を横(x),被説明変数とする方を縦(y)に据え,関係を明確にしておくべきでしょう。グラフの書式を任意に整えます。ここでは,次のような点を調整しました。マーカーの塗り色と線色を異にして判別を容易にこの散布図を一瞥した感覚では,2つの変数には直線的な関係があることが窺えます。ま,なんとなくですけど。「直線的な関係」について,右上がりのものを正の相関,右下がりのものを負の相関といいます。翻って,そうした関係が見られないものを無相関と呼びます。正の相関は,すなわち“第1変数が増加すれば第2変数も増加する”という関係が,負の相関は“第1変数が増加すれば第2変数は減少する”という関係が互いの変数にあることを意味します。Extension元データの性格(2変数が明瞭に正規分布にしたがうような)次第で,ふつうに散布図を描くより,データを標準化してから描く方が有効な場面があります。このエクステンションでは,データの標準化を経て散布図を描く手続きに触れながら,後述のPearson関数によって求められる相関係数の中身(式の内容)について見ていきたいと思います。散布図を作成すると,“2変数の関係が直線的なものかどうか”と,そうであるなら“正の相関がありそうなのか・負の相関がありそうなのか”についてを知ることができます。後の工程で求める相関係数を散布図から「だいたいこのくらいかな」と推し量るのには少し注意が必要かもしれません。さしたる状況の変化も感じなかったし前回の相関係数が0.4だったからこの分布だと今回は少し上の0.5くらいかな?」と散布図だけでおおよその判断をつけられる場合もあるでしょう。現実の話としては,分析下の環境や対象がそうした単純かつ固定的な状況にあることが確約されるはずもなく,また人によっては目盛りの取り方に“クセ”のようなものが強く出たりもします。した点に思い当たると,相関係数と散布図の形状は,ここで「一意に結びつく」と明言してしまうのもある意味では危険かもなんて,わたし的には思うところもあります。ここまでの過程で描いた散布図について,片方の軸だけ目盛のとりかたを変えてしまえば,形状は下の図のよう変貌します(下方の2つ)。ここで善意の読み手に対し,目盛のとりかたを変えた事実を伏せてこの図を開示したとします。この善意の読み手の何人かは,見た目の密度の高さに気を取られ,真のものより高い相関を推察するなんてミスリードが成立する可能性を否定できません。翻って考えると,スケールを統一してしまえば,分布のかたちと相関係数とのつながりも,より密なものにできそうです。単位の大きく異なるデータを扱うときや,予測しがたいムラの大きなデータを扱うとき(いずれも目盛のとりかたを統一・維持していくことが難しいケース),あるいは2群ないし2時点間での分布のかたちを比べてみたいときには,2変数のスケール調整が有効な場面も多いように考えます。データを標準化してから散布図を描くプロセスを挟んでいきます。この形式の散布図は,ふつうの散布図との比較に言えば,平均および1標準偏差を示しうるという意味で情報量を増やしたり,あるいはスケールの揃った4象限マトリクスにスッキリ・スマートにデータを落とし込めるといった,いくつかの利点を持ちます(後述)。任意の場所に見出し平均標準偏差およびzXzYを用意します。zは標準得点(z-socre)を指しています。D列のzXは「購買指数」の,E列のzYは「直近の評点」のそれです。2つの変数(購買指数・直近の評点)について,平均と標準偏差を求めます。具体的に,まず1番目の変数について以下を順に入力し,=AVERAGE(B2:B117)=STDEV.P(B2:B117)これらを2番目の変数の列にもコピーします。Standardize関数を使って両変数の標準得点を求めます(標準化)。$C$120)と入力し,ともに最下行までコピーします。Standardize関数の「関数ウイザード」を使った入力方法は,必要であれば偏差値表の作成頁を参照のこと。標準化という処理を挟んだのでそれと密接に絡むお話あとから出てくる相関係数の中身(計算のしくみ)についても,少しだけ加えておきたいと思います。標準化を経た「購買指数」および「直近の評点」のいずれも,平均が0,標準偏差が1のパラメーターをもつ変数となっています。ここで「平均が0」であることに着目すると,標準得点は,おのおのの顧客IDが得た変数の値が“平均からプラス方向あるいはマイナス方向にどれだけ(標準偏差何個分)離れているか”を示していると解釈できます。―"Wikipedia"そして,これらの標準得点について下の図のように先頭の顧客の「zX」「zY」をzX×zY列で乗じてやると,偏差積と呼ばれる値が求まります(ハイライトの部分)。これをすべての顧客について計算(データのサイズn個分)していくと現実的にはセルF2の計算式を表の最下行までコピーします。こんな感じになりました。ことで,今度は,このハイライトの部分の総和(偏差積和)をデータのサイズnで割ってみちゃったりしたいと思うのです。言い換えれば,これは偏差積の平均を求めることですから下の図のようにAverage関数を使えば簡単ですね。いうわけで,このAverage関数の戻り値のことは共分散と呼ばれます(下の図ではセルF119)。nでなくn-1で除すタイプもあります。Excelには共分散を直接計算できる関数があります。CoVariance.s(n-1で除す場合)の各関数が利用できます。いずれも引数には2つの変数を指定します。r)と呼ばれるものは,下に示された式から求めることができます。上で求めた共分散というものが,実のところ相関係数を求める上でかなり重要な要素となっていることがわかります。この式によれば相関係数は,共分散を両変数の標準偏差の積で割って求めます。先に触れたように両列の標準偏差は標準化しているのでともに1であることは自明です。2つの変数を標準化した場合は,分母は必ず1となるので相関係数=共分散の関係が成り立ちます。この点を鑑みれば,このあと触れる相関係数と呼ばれるものは,“標準化された共分散”と考えても分かりやすいかと思います。前後してごめんなさいですけど,ここからふたたび標準化したデータを使って散布図を描く話に戻ります。,「zX」列と「zY」列のデータを見出しを除きすべて選択します。Y)またはバブルチャートの挿入ボタンをクリックします1。プルダウンメニューの散布図ボタンをクリックします2。散布図(マーカーのみ)ボタンシートに散布図が差し込まれました。ここでは5でしたように,グラフタイトルを削除したうえで,マーカーの書式だけちょろっと触っておきたいと思います。グラフタイトルが挿入されない代わりに,凡例が差し込まれます。散布図の場合,層別グラフでもない限り凡例の必要性も高くはないので,凡例を直接選択しDeleteキーで消去します。グラフの横軸・縦軸とも最大値・最小値を揃えます。この例では,横軸最大値が他と異なっていますので合わせていきます。グラフの横軸を選択し,書式タブ現在の選択範囲グールプにある選択対象の書式設定ボタンをクリックします。スケールを固定して異なる条件・時点で観察する正規分布を仮定した場合,±3標準偏差にほとんどのデータ(99.7%)が入ります。あたりの設定でカバーできると思います。選択対象の書式設定ボタンはレイアウトまたは書式タブの中にあります。軸の書式設定ダイアログが表示されます。3とします),単位の主には1を入力します(なお,グラフの上では適切な数値となっているはずの最小値と目盛間隔をここであえて指定するのは,意図しない図のくずれに対する予防策的な意味合いからです)。加えて目盛の種類を交差に変更します。一方の軸も同様に調整を済ませます。[変更前の表記]目盛間隔。グラフエリアがアクティブになっているか確認します。アクティブな状態を維持しつつ,サイズグールプの図形の高さ,図形の幅を任意の等しい値にするなどして,グラフができるだけ等比となるよう調整します(たとえば高さ10cm・幅10cmのように指定)。px単位でも指定できます。平均で仕切る4象限のマトリクスExtensionで意図した散布図が用意できました。縦軸・横軸の中心のラインは,それぞれの変数の平均(=0)です。目盛1つが1標準偏差(=1)です。両軸のラインを基準に区別可能な4つの区画は象限と呼ばれ,下の図のように右上がともに正の値となるような目盛をとるとき,右上から順に,反時計回りで第1象限,第2象限,第3象限,第4象限と呼んでいきます。xのみ平均より高いざっくりと言えば,この象限1と3,ないしは2と4のどちらにより多くの要素が集まるかで,相関係数の符号が決まります(より忠実には,先に見た偏差積の和を1・3象限[+側]および2・4象限[-側]の別にとったとき,いずれの側が符号を決める力をもっているか[絶対値で大きいか])。前者の場合プラス(正の相関),後者の場合マイナス(負の相関)となります。ここでは下のように2・4側により多くの要素が集まりましたからマイナスですね。この構成の散布図は4つの大きなセルを持つことから,ポジショニング,あるいはセグメンテーションのツールとして平易で,ある種のコンセンサスもある「4象限マトリクス」として準用することが可能です。この例では,2変数それぞれの意味において第1象限から第4象限まで下のような名前をつけ,セグメントごとにより相応しい施策を管理するといった活用のしかたが浮かびます。この場合,マクロを使って要素にラベル(ここでは「顧客コード」)を表示させてやれば,一覧性を高めることができるかもしれません。「外れ値」を考えるここで最初のExtensionを終えて,頁頭でしていたふつうの散布図の話に戻りたいと思います。えーと先の散布図からは負の相関の存在が察せられたので,今度は散布図のこの部分(ハイライトの部分)に注目したいと思います。1つだけ,マーカーの密集しているエリアから目立って離れたところにデータが存在しているのがわかります。これを外れ値といいます。この外れ値が,何らかの特殊な事情をもっている(異常値)のか,検討したほうがよさそうです。いうのも,外れ値は,後の工程で求める相関係数に強い影響を与えます1。他のデータと馴染みがたい異常値であることが色濃くなったとき,検討次第でデータの塊から除外する選択も出てきます。私が欲しいのは,単なる“計算したら出てきた値”じゃなくて,実情がよく斟酌された値のハズですから,異常値の可能性を探ることにとても重要な意味があります。外れ値の検出法としては,もちろん統計学的手法を利用するものもありますが2,“違和感”を判じるのが得意な人間の視覚こそ第一義だと考えます。マハラノビス距離の2乗そこで一旦作業を中断して,この顧客を調べます。私のことなので,そもそもデータの入力ミスかも知れませんし。えーと,顧客コードLMCPAのお客さんのは入力ミスじゃない。社長自らイレギュラーにフォローしているよう。結局,ウチの社長と深く関係のある会社さんで,他のお客さんと比べきわめて特殊な形態で取引している先であることが分かりました。今回の分析の前提とは相容れない(と考えてください)ということでこれを“除外すべき”異常値として認め,データから外すことに決めました。いうことで除外の対象となったこのデータはえーと40行目にありました。これを行ごと削除します。具体的には,40行目の任意の列をアクティブにした状態から,セルグループの削除メニューでおこないます。散布図が下のように自動的に修正を反映します。ここまでに軸の最大値・最小値の設定を経ていない場合,削除したデータによってはそれらが自動で再調整されることがあります(この例では縦の最大値が8070のように)。これについて不都合のある場合,必要に応じて適切な設定を加えます。以上で散布図は完成です。相関係数を求める大きな流れの第二として,相関係数を求めます。シートの空いているところ(ここではセルE1)に,あたらしい見出し相関係数を作成します。いえば,すこし脇道にそれちゃいますけど,一口に「相関係数」と言っても,あいまいな意味ではいろいろなものがありますね。えーと元データの2つの変数がともに数量(間隔尺度・比尺度)であり,直線的関係が見られる場合は,ピアソンの積率相関係数(単相関係数)を用います。この頁の例でも変数はともに数量(間隔尺度)ですから,ピアソンの積率相関係数を求めることができますね。Excelにはこれを計算するための組み込み関数があります。参考として,その他2つの変数が数量データ(間隔尺度・比尺度)とカテゴリデータ(名義尺度)の場合の“相関係数”として相関比が,ともにカテゴリデータ(名義尺度)の場合にはクラメールの連関係数が,また,とりわけ順序尺度の場合にはスピアマンの順位相関係数といったものが利用されたりしますです。...といった状況下で選択されることが多いです。先ほど作成した「相関係数」見出しの直下のセルをアクティブにし,数式バー横の関数の挿入ボタンをクリックします。関数の挿入ダイアログが開きます。入力し,検索開始ボタンをクリックします。―"Office")。関数名のいちばん上に,目的の「PEARSON」が表示されていると思います。こちらを選択し,OKボタンをクリックします。カ所について,下のようにシート上の対応する色のセル範囲(各列の見出しを除くすべてのデータ)を指定します。すべて入力した後,OKボタンをクリックします。散布図の作成と相関係数の計算,完了です。そのものは単位を持たない無名数として扱います。これは,順序尺度となります。―"Wikipedia")。同じ2つの変数の相関係数を過去2つの時点にわたり観測してきたとして,その内容が以下の通りであったとします。0.4このとき,直前のものを評価するとして,順序尺度は大小のみが意味を持ちますから前時点より「強く(大きく)なった」ことを言うことは可能なことがわかります。反面,加減乗除(+-×÷)に意味はないので,第一として前時点より200%(2倍)強いとする表現を用いることが適切とは言えなくなります。その後の観測値が0.6であったとしたとき,第二として直前との比較で0.2ずつ増加してきたすなわち,等間隔で均等に強くなってきたとする表現も不適切となることが分かります。Extensionこの頁の事例は標本を対象としたものではないことから,相関分析のための手続きは以上で完了としています。ここからは仮のお話として,元データが標本であった場合の検定(無相関の検定)と推定(母相関係数の推定)について加えたいと思います。その前に,ここからは仮のお話ですので前提を崩しちゃいます。今回分析対象としたデータがより大きな母集団から無作為に抽出した標本だったとして,先のような相関係数:-0.52を導いたと仮定します。この場合,実際には2つの母集団が無相関(母相関係数ρ=0)であったとしても,きわめて希な組み合わせで相関の見られる標本となってしまうケースもあります。そこで相関があるかないかのひとつの判断として,無相関検定をおこなってみます。検定ということで,何よりも仮説がなければ始まりません。帰無仮説と対立仮説は次のようにしておきます。帰無仮説(捨てたい仮説)2つの母集団の相関係数はゼロ。対立仮説(採用したい仮説)2つの母集団の相関係数はゼロでない。データ対の数,自由度,そして先に求めたピアソンの積率相関係数は,次のとおりです。-0.52検定統計量Tを求めます。シートの空いているところを使って,上で示した値を式にあてはめて計算してみます。いえ,上の式の入力はちょっと繁雑ですね。よってここでは分子と分母に分けて計算したいと思います。計算式は次のようになります。T=F5/F6検定統計量Tはおよそ6.43となりました。この検定統計量Tの値と,自由度を使って有意確率(P値)を計算します。したがうことから,両側の有意確率をT.Dist.2T関数で求めます。ここでは,先にPearson関数を使用するときに呼び出した関数の挿入ダイアログを再び使って入力していくことにします。したがって,空いている適当なセルをアクティブにし(ここではセルF9),関数の挿入ボタンからT.Dist.2T関数のダイアログを呼び出します。関数の引数ダイアログのXについて,下のようにシート上の対応する色のセル(Tの値)を指定します。つづいて,自由度はn-2ですから,この例の場合は113と指定します。すべて設定した後,OKボタンをクリックします。P値は「3.10192E-09」と表示されています。参考までに通常の表記になおすと「0.000000003...」といったところです。1%(0.01)いずれであったとしてもP<αとなり,帰無仮説が棄却されます。2つの母集団の「相関係数はゼロではない」と考えることができます。母相関係数の信頼区間の推定これによって,たしかに“(程度を問わず)相関がある”ことを補強できたとは思います。サンプルサイズが大きければrが小さくても有意になる)。この点については,発表等の場において当該結果を評価者の耳目にさらすことになると,えてしてツッコミが返ってくることも少なくない部分のような私自身は,そんな感触をもっています。した点をふまえれば,“母相関係数がゼロでない”ことを言うよりも,“母相関係数はこの範囲の中にあると推定できますっ!”なんてことを言えた方が何かと都合がいいのかもとも思えてきます。以下,しばらくそのための手続きです(母相関係数の信頼区間の推定)。正規分布に近似的にしたがうようなかたちに変換)。=FISHER(E2)(以下,計算のしくみを明らかにしておきたい場合の選択)=1/2*LN((1+E2)/(1-E2))上で求めた値(rz)にz(α/2)/Sqrt(n-3)を加減して,変換値による母相関係数(ρz)の信頼区間を求めます。下式にいうz(α/2)は標準正規分布の上側α/2パーセント点とします。ここでは信頼区間を信頼度95%,つまりα=0.05で求めるものとします。いうことで,先の式のz(α/2)は2.5%点の値が必要なのでこれをNorm.S.Inv関数によって求めます(ただし引数は下側[累積]確率を求められるので0.975)。式をシートに具体的に入力すると,次のようになります。下側=F13-NORM.S.INV(0.975)/SQRT(115-3)上側=F13+NORM.S.INV(0.975)/SQRT(115-3)ρzをρに戻します(逆変換)。下側=FISHERINV(E16)上側=FISHERINV(G16)(以下,計算のしくみを明らかにしておきたい場合の選択)下側=(EXP(2*E16)-1)/(EXP(2*E16)+1)上側=(EXP(2*G16)-1)/(EXP(2*G16)+1)母相関係数の信頼区間は-0.641~-0.369となりました。先の検定でみたとおり,この範囲にはゼロが含まれていないことを確認できます。信頼度95%としたので,同じ母集団から無作為に20回標本を抽出して信頼区間を求めてみるとして,真の母相関係数がうち1回だけは計算によって求められた範囲から外れる程度の確からしさとなります。以上,あくまで元データが標本であることを仮定した場合のお話でした。ストーリーパートの方ではこのような検定や推定をする必要はないですYo。DATA動作確認のための元データExcelにコピペする方法このページで使用するデータはすべて創作にもとづく架空のデータです。特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものではありません。「あのグラフを自分の持っているデータでつくってほしい」といった代行のご依頼その他筆者が対処しえないと判断した事項プライバシー・ポリシー|権利表記・著作権と。