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

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

How-to

層別散布図

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

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

以下,エクセルによる層別散布図の作り方です。ここでは一連の手続きをExcel 2016で追っています。Excel 2013およびExcel 2010 についても基本的には同じ流れとなりますが,一部ボタンの配置や名称などが異なる箇所があります(この場合,可能であれば当該箇所に明記します)。

元データ

綾子

1

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

では

MisterA

2

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

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

MisterA

3

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

綾子

4

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



元データ

綾子

5

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

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

綾子

6

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

2010

挿入タブグラフグールプにある散布図ボタン

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

2010

散布図(マーカーのみ)ボタン

綾子

7

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

綾子

8

いかがですか?

群を分けずに作った散布図

MisterA

9

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


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

綾子

10

カタカタカタタターン

う~ん。概ね無相関と言っていいんじゃないでしょうか。これでは

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

MisterA

11

ぐぎぎ

綾子

12

あの!Aさん。

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

層別散布図

工程

綾子

13

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

昇順で並べ替え

綾子

14

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

そしてさらにその右隣へと,残りのグループ(ここでは2グループ)の名称を順に書き加えていきます。

第2変数の見出しを群の名前に書き換える

綾子

15

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

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

2群目のデータを移動

綾子

16

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

3群目のデータを移動

綾子

17

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

綾子

18

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

2010

挿入タブグラフグールプにある散布図ボタン

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

2010

散布図(マーカーのみ)ボタン

綾子

19

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

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

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

綾子

20

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

層別散布図をつくる場合,一般的には上のグラフのようにマーカーに異なる色をあてて群を分かつことが容易かと思います。もっともこれ以外にも,成果物のプリントアウトをモノクロでおこなうことを想定した場合など,下図のようにマーカーの形状そのもので群を区別することも少なくありません。

説明のみ

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

綾子

21

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

説明のみ

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

綾子

22

裏を返せばカスタムマーカーを自在に使うことができたなら,表現の幅もひろがるかと思います。

このように,元画像を用意してカスタムマーカーを選択する場合,

  • (クリップ[くり抜き]されないので)画像の輪郭線を維持したい要素(商品ロゴなど)を配置できる
  • 元画像の高さと幅が同じ長さ(正方形)でなくても適用できる

といった利点がある一方,

  • マーカーの枠線を設定から必ず外す必要がある(そうしない場合,元画像がぼやける)
  • グラフ上にどの程度の大きさで反映されるかあらかじめ予測をつけがたい(たとえば下図。縦80px横100pxの元画像を適用した結果,この環境の場合はとても過剰な表現に
  • 転じて,試行による調整作業がほぼ必須で,リサイズなどに手間が掛かる

といったデメリットには注意する必要があります。


説明のみ

層別散布図:マーカーを直接画像に置き換えた表現の例

綾子

23

また,こちらは先に比べていささか“カスタム”といった表現がなじみ難いかもしれませんが,元画像をそのまま適用するのではなく,既定の種のマーカーの“塗りつぶし色”として元画像を填めることも可能です。この場合,

  • 下図上段の赤い系列のように,マーカーサイズを任意に調整したうえで,
  • “塗りつぶし色”として元画像を指定すると,下図下段のようにマーカーのサイズにフィットするかたちで,かつマーカーの形状に沿うようクリップさせる ことができます。つまり,大きさを狙って指定しやすいといった利点を持っています。
  • また,これは円形以外に正方形でも可能です。

ただしこのとき,

  • 正方の画像しかそぐわない(そうでない場合,アスペクト比が崩れます)

といった制限を含みます。


説明のみ

説明のみ

層別散布図:デフォルトのマーカーに画像をはめた表現の例

綾子

24

もっとも,ユーザーによっては,

「いや,上で見た方法は,結局どっちも画像を用意するのが面倒じゃん!」

なんて思いを抱くこともあるやもしれません。
実のところ,いずれの方法とも図形機能で描いた図形で代用が可能です。


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

デフォルトのマーカーと替えたい図形を描く→グラフに見合う大きさに整える

綾子

25

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


! もちろん,図形によっては「使える場」が限られちゃいますです!(限られる例

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

MisterA

26

これはっ!!

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

綾子

27

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

綾子

28

相関係数を見ればなるほど納得の強さです。

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

MisterA

29

ぐぎぎ

綾子

30

こんなこと聞くのもアレなんですけどAさんって私の記憶が確かならRS 1部の所属でしたよね?

MisterA

31

そうですマイナス0.77 !! RS 1部 です

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


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

綾子

32

MisterA

33

ちくしょう 転 


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

LastUpdate

.
このページの先頭へ