Excelの散布図にラベルをつける

前回紹介した偏差値でポートフォリオを描くで示した例のように、件数が少ない場合には、グラフにラベルを付けた方が具体的に何が②の領域にあるのかが分かります。しかし、Excelでは散布図に簡単にラベルを付けることができません。

そんなときに便利なのがExcelアドインのXY Chart Labelerです。フリーソフトですが、機能的には十分です。

グラフ全体にラベルを付与することも、左図のように一部の値にだけにすることも可能です。

左図の例では、右側にラベルを付けていますが、上下左右の指定ができ、ラベリング後にラベル位置の微調整もできます。

XY Chart Labelerのインストール

ダウンロードしたXYChartLabeler.exeを実行すると下図のように自動的にExcelのアドインに組み込まれます。

XY Chart Labelerの実行方法

英語表記ですが、実行は簡単です。対象のグラフを選択した状態でアドインのXY Chart Labelerをクリックします。

Add Chart Labels...をクリックするとAdd Labelsの窓が開きます。最初に図形を選択していれば、Select a Data Series to Label:にはグラフ名が表示されています。

Select a Label Range:にラベル名が記載されているセルの範囲を選択します。（下記例ではa1からa3を選択）

データ例）

	A	B	C
1	ラベル名1	X1値	Y1値
2	ラベル名2	X2値	Y2値
3	ラベル名3	X3値	Y3値

Select Label Position:はラベル位置の設定なので、Above（上）、Below（下）、Left（左）、Right（右）の何れかを選択します。

『OK』をクリックすると散布図にラベルが付与されます。

今日の一曲

「Kraftwerkはボーカル・グループだよね」と、このアルバム（Computer Love）を聴くと改めて実感します。 単調なリズムにメロディアスなメロディーを絡ませ、その上にポップなボーカルをトッピングしてきています。このアルバムに収録されており、日本ではDentakuとして一世風靡したPocket Calculatorもまったく同じ手法で作られています。

アルバム全編を通して気持ちよく聴けますが、特に『Computer Love』は、切なく、シュールな出来上がりでとても好きです。

偏差値でポートフォリオを描く

２変数の散布図を描画して平均値を交点とすることで、データを①～④に4分類するポートフォリオを描くことができます。（左図）

直帰率の改善

例えばWEB系の分析で、直帰率の改善を行うとした場合にも、縦軸に入り口数、横軸に直帰率とすることで、入り口となっているページで直帰率が高いページを視覚的に表すことができます。左図では②に位置するページが、入り口数が平均以上であり、且つ、直帰率も平均以上です。

※入り口数：　サイトに最初に訪問したページの流入数（入り口ページの流入数）
※直帰率　：　サイトに最初に訪問したページだけを見てサイトから離脱してしまう割合

単純に直帰率が高いページだけを見ると、入り口ページの流入数が少ないページも含まれてしまうために、効果的な改善ができません。上図のように交点を平均値とするだけで、改善すべきページを視覚的に表すことができます。

しかし、グラフ的には①～④が均等になっていないので、ポートフォリオとしての見た目がいまひとつです。また入り口数と直帰率という異なる単位をグラフにしているため軸の最小値と最大値の設定によってもグラフの見え方が変わってしまいます。

何よりも平均値よりも大きいという条件（上図②）では、該当件数が多くなってしまう場合、単位が異なる２つの変数を平均から幾つあげればいいかを検討するには手間がかかります。

偏差値を使ったポートフォリオ

２変数を偏差値に変換して、偏差値50を交点として描いたポートフォリオが下図です。
Excelで軸の書式設定で、縦軸、横軸共に最小値20、最大値80を基準に設定してください（下図では25 75）。また縦軸との交点（横軸との交点）を50にします。

軸の書式設定

XとYのポートフォリオ

上図の交点を55に変更すると、下図のように、入り口数も直帰率も偏差値が55以上のものに絞り込むことができます。

交点を55に変更したポートフォリオ

データの標準化

誰もが知っている「偏差値」ですが、割と正しく理解されていなかったりします。偏差値は、データの標準化と同様の考え方です。データの標準化とは、平均が0、標準偏差が1になるように単位を揃えることです。標準化された値は標準化得点と呼ばれます。一方、偏差値は、平均が50、標準偏差が10になるように単位を揃えたものです。このようにデータを標準化（偏差値化）することによって、標準正規分布を用いた推定や検定ができるようになります。

標準化得点の算出方法

平均を0、標準偏差を1にするので、各値から平均値を減算して、標準偏差で除算します。

=（x - 平均）/標準偏差

ExcelではSTANDARDIZE関数が用意されていますが、結果は上式と同じになります。

=STANDARDIZE(x,平均,標準偏差)

※平均　　　：　=AVERAGE(データ範囲)

※標準偏差：　=STDEVP(データ範囲)

偏差値の算出方法

偏差値は平均を50、標準偏差を10にしたものです。式とExcelでの偏差値の求め方を下に記します。

=（x - 平均）/標準偏差×10＋50
=(x-average(データ範囲))/stdevp(データ範囲)*10+50

偏差値とは何か

データを標準化（偏差値化）することによって標準正規分布を用いた推定や検定ができると記載しましたが、もう少し詳しく説明します。下図は、標準得点と偏差値の標準正規分表です。

標準正規分布表

標準得点	偏差値	標準正規 分布表	確率
-3.0	20	0.4987	下位   0.1%
-2.5	25	0.4938	下位   0.6%
-2	30	0.4772	下位   2.3%
-1.5	35	0.4332	下位   6.7%
-1.0	40	0.3413	下位 15.9%
-0.5	45	0.1915	下位 30.9%
0	50	0	中央
0.5	55	0.1915	上位 30.9%
1.0	60	0.3413	上位 15.9%
1.5	65	0.4332	上位   6.7%
2	70	0.4772	上位   2.3%
2.5	75	0.4938	上位   0.6%
3.0	80	0.4987	上位   0.1%

標準正規分布表の値については、こちらを参考にしてください（統計学の教科書にも参考資料として添付されています）。正規分布では、指定された区間にある確率を求めることができます。偏差値60（標準得点1）は、標準正規分布表をみると0.3413です。正規分布の右片側だけみると偏差値60までにある確率は34.1％となります。これに左片側の50％を合算すると84.1％となり、偏差値60は100％-84.1％で上位15.9％に位置することがわかります。偏差値70になると上位2.3％で、全体の中でかなりの高位置であることがわかります。

直帰率の例題で、直帰率、入り口数の偏差値が共に50％以上の確率は、(1-50％)×(1-50％)=25％です。例題では、20変数なので②の枠内にあるのは、理論上5個となります。偏差値55以上であれば、(1-55％)×(1-55％)=20％で4個です。実際の個数と比較してみてください。正規分布を前提としているのと母数によっての誤差はありますが、偏差値を使用することで、大凡の個数がわかります。

なぜ標準得点ではなく偏差値を用いるかといえば、統計に詳しくないクライアントへの説明では、偏差値の方が理解されやすいからです。

Five Years

アメリカ最大手の自動車メーカーGM（ゼネラル・モーターズ）が、１日、日本の民事再生法に当たる連邦破産法１１条の適用を申請したというニュースがありました。ビックスリーといわれた米国の自動車会社のうちクライスラーに続き2社目の破産です。

米国の自動車業界の崩壊は、大量生産、大量消費、マスマーケティングの終焉を象徴しているのでしょうか。

マーケティングの始まりは、1908年の「T型フォード」であるといわれています。また黒一色のフォードに対して、GM(ゼネラル・モーターズ)は、自動車をアパレルと同様に捉え、デザイン、広告、モードの商品であることを確立させました。

アメ車といわれて、すぐ浮かぶのは、キャデラックやコルベットスティングレーです。

彼の David Bowie（デビッド・ボウイ）の「Five Years」（5年間）の歌詞にもキャデラックは登場します。「Five Years」はアルバム「Ziggy Stardust」（ジギー・スターダスト）の1曲目ですが、昨年、30年以上の年月を経て発売された「Live In Santa Monica '72」に収録されている「Five Years (Live)」は、一聴の価値があります。

A soldier with a broken arm, fixed his stare to the wheels of a cadillac
A cop knelt and kissed the feet of a priest, and a queer threw up at the sight of that

腕のない兵士がキャデラックのタイヤをじっと見つめている。
警官がひざまずいて牧師の足にキスをし、それを見たゲイが吐いた。

とてもシュールな歌詞で、「僕たちには5年間しかない」と切実に歌い上げます。しかし、GMの再生には5年間も残されていません。現実は非日常的では許されないようです。