ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

層別散布図

このページは 散布図の作成と相関係数の計算(相関分析) with Excel の補足ページです。

ここでは、何らの属性情報をよりどころとして、データをいくつかのグループに分けてみる「層別」のための散布図作成の手順について扱います。内容にしたがい、マーカーの色(または形状)の異なるいくつかの系列を 1 つのグラフに併存図示することが目的です。

以下、エクセルによる層別散布図の作り方です。ここでは一連の手続きを Excel 2010 で追っています。これは Excel 2007 でも変わりません。また Excel 2013, Excel 2016 についても基本同じ流れとなりますが、一部ボタン等の名称が異なる箇所があります(この場合、当該箇所に明記しています)。

元データ

綾子

1

匿名希望の RS 部員…仮に A さんとして… A さんからの相談です。

では…

MisterA

2

先日、ウチの部長に「気力体力フル充電したいので、有給休暇消化しまーす」って申請したんです。そしたらムシの居所が悪かったらしく…

「そんなので充電できりゃ世話ねーYO! じゃ、なんだ? 有休とるヤツぁ意欲点が高いのか? あ!?」と人事考課の意欲点を引き合いにイヤミを言われてしまい…。

MisterA

3

ぼくは…休暇の多寡は意欲に直結すると思うんです。実にくやしいっ…! …そんなとき、です。神の仕業か…データが手元に降ってきました。 …というわけで、部長の言は誤りだって、ここ経理課ならいっしょになって考えてくれるかも… なんて思いまして…。

綾子

4

ふむふむ。そのデータは…これ…ですね。


※ データはサイドバーのボタンからご利用いただけます。

元データ

綾子

5

じゃあ、まずはデータそのままに散布図を作ってみませんか?

ということで、「有休取得日数」と「意欲点」の両変数を見出しを除いて選択して…と…

綾子

6

リボンの挿入タブグラフグールプにある散布図ボタンをクリックして…

2013

2016

挿入タブグラフグールプにある散布図(X, Y)またはバブルチャートの挿入ボタン

つづいて プルダウンメニューの散布図(マーカーのみ)ボタンをクリックしますね。

2013

2016

散布図ボタン

綾子

7

そしてグラフを適度に整形してくと…カチカチッ…。

綾子

8

いかがですか?

MisterA

9

あ…あんまり関連が見えてきませんが…。やはり…ぶ…部長の言うことはある意味で正解なのでしょうか…。


ええと…。なら、そ…相関係数も確認しときたいのですが…。

綾子

10

カタカタ…カタタ…ターン…。

う~ん。やっぱり…概ね無相関と見るべきでしょうか? これでは…。

相関係数: -0.0998 [=PEARSON(C2:C25,D2:D25)]

MisterA

11

ぐぎぎ…。

綾子

12

あの! A さん。

せっかくデータに「所属」があるんですから…これで層別してみましょう。

綾子

13

それでは層別にとりかかるとして、次の 2 通りのシチュエーションが考えられるじゃないですか? ええと…

  • まったくの新規で作図する
  • すでにある散布図をもとに作成する

…ってカンジでスタートラインが異なると思います。

ってなわけで、ここでは順に 2 通りの手続きについて触れていきます。後者についての説明は こちら からジャンプしてください。

手続きその1

1から作図

綾子

14

「所属」列(層別のキーとする項目)を昇順または降順で並べ替えます(ここでは昇順)。

綾子

15

(元表の右方があいていない場合)グループの数(ここでは RS1 ~ RS3 で 3 つ)- 1 列ぶん、あたらしい列を加えます。

具体的に、ここではあと 2 列欲しいわけですから… E ・ F の 2 列を選択して、列記号の上で右クリック挿入ですね。

綾子

16

もとの第 2 変数の見出しを、「所属」列の最初のグループの名称に書き換えます。

加えてあたらしく作成した列の見出しにも、残りのグループ(ここでは 2 グループ)の名称を順に入力しておきます。

綾子

17

旧第 2 変数の中から 2 グループ目にあたる要素を選び出します(領域選択)。

選択範囲の外周線上にカーソルを合わせると、カーソルは下の図のようなかたちになります。このとき、ドラッグ&ドロップでデータを 1 列右の同行へ移します。

綾子

18

同じようにして、旧第 2 変数の中から 3 グループ目にあたる要素を選び出し、ドラッグ&ドロップでデータを 2 列右の同行へ移します。

綾子

19

「有休取得日数」列から「RS3」列の 4 つの列のデータ範囲を選択します。

綾子

20

リボンの挿入タブグラフグールプにある散布図ボタンをクリックします。

2013

2016

挿入タブグラフグールプにある散布図(X, Y)またはバブルチャートの挿入ボタン

つづいて プルダウンメニューの散布図(マーカーのみ)ボタンをクリックします。

2013

2016

散布図ボタン

綾子

21

書式等を整えて散布図の完成です。

あ!…層別散布図の場合、凡例の表示が必要になりますね。

完成した層別散布図:色分けによる層の表現

綾子

22

ええと…この関連と言ってはナンなんですが…散布図の主役(…ビジュアル的な意味で)とも言えるマーカーの選択についてここですこし加えたいと思います。

層別散布図の場合、上のグラフのようにマーカーの色を変えて群を表現すること以外にも、別手続き「その2」の散布図のようにマーカーの形状そのものに差異を与えて群を表現することもあるかと思います。

完成した散布図:マーカーの種類による層の表現

綾子

23

しかし…既定のマーカーの種類も限られているので(下図・上から 9 個)、ある意味平坦な印象から離れたいときなどには、読みやすいかどうかは別にして カスタムマーカーを選択するしかありません(下図・いちばん下の項目。さらに下の図に参考例)。

説明のみ

Excelデフォルトのマーカーの種類:9個+1

説明のみ

綾子

24

この場合、マーカーの指示ポイントと画像の中心とが合致するような元画像を用意しないといけなくなります。場合によっては、この調整がけっこう面倒となるかもしれません。

説明のみ

綾子

25

…ということで、カスタムマーカーとしてはいわゆる “画像” より図形(シェイプ機能)の方が使い勝手がいいよNe…なんて思います。シェイプであればトリミングなどの処置の必要がないので、こういった言い方もヘンですが重心も安定しがちです。


図形を使う場合、具体的には…
まず、下の図のように群の数だけ任意のシェイプを用意し、これらのサイズをグラフで使う(使いたい)大きさにととのえておきます。

綾子

26

そしてこれを 1 つコピーして、グラフの当該系列に貼り付け(ホームタブ貼り付けボタン)ます。残りのシェイプに関して、これを残りの群の数だけ(ここでは 2 つ)繰り返して完了です。


あ…! もちろん、シェイプによっては「使える場」が限られちゃいますです!(限られる例

ハート型マーカーの散布図

手続きその2

既存の図を加工

綾子

27

「所属」列(層別のキーとする項目)を昇順または降順で並べ替えます(ここでは昇順)。

綾子

28

グラフをアクティブにしてから、リボンのデザインタブデータグールプにあるデータの選択ボタンをクリックします。

綾子

29

データソースの選択ダイアログが開きます。凡例項目(系列)グループの編集ボタンをクリックします。

綾子

30

系列の編集ダイアログが開きます。系列名に 1 つ目のグループの名称を

  • ="(1つ目のグループの名称)"

という形式で入力し、つづけてこのグループの X(第 1 変数)の値の範囲、Y(第 2 変数)の値の範囲を指定していきます(系列 X の値系列 Y の値:下図参照)。


これをすべて終えたら、OKボタンをクリックします。

綾子

31

ふたたびデータソースの選択ダイアログに戻ります。今度は「凡例項目(系列)」グループの追加ボタンをクリックします。

綾子

32

2 つ目のグループに関して、さきほどと同様の設定をおこないます(2 手前を参照)。

綾子

33

このようにして、残りのグループの数だけ系列を追加していきます(2 手前からくりかえし)。

綾子

34

すべての系列を追加し終えたら、データソースの選択ダイアログのOKボタンをクリックします。

綾子

35

書式等を整えて散布図の完成です。

こちらも層別散布図ですから…凡例の表示が必要になりますね。

完成した散布図:マーカーの種類による層の表現

MisterA

36

これはっ…!

綾子

37

直線的関係… 1 部と 2 部については思いのほか、はっきり…ですね。

綾子

38

相関係数に見て…納得の強さです。

[相関係数] RS1:0.9186, RS2:-0.7709, RS3:-0.1645

MisterA

39

ぐぎぎ…。

綾子

40

こんなこと聞くのもアレなんですけど… A さんは、RS 1 部に所属されてますよね?

MisterA

41

そうです…。マイナス0.7709 !! の RS 1 部 です…。

意欲点は…各部長のさじかげんで決まるのに鑑みるに…


どうやら、ぼくの理想郷は…ウチではなく RS 2 部にあったことがわかりました…。

綾子

42

…。

MisterA

43

ちくしょう 


  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名、および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2016.7.12

.
このページの先頭へ