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

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

How-to

ビジュアライゼーション・メソッド

度数分布の層別

このページは ヒストグラムの作成 with Excel の補足ページです。

度数分布図で層別をおこなう際は,やはり層(群)の別に 1 つ 1 つ独立してグラフを描画していくのが better かと思いますが,ここではそれ以外の方法――すなわち,1 つの図でそれらをまとめて表現したい場合に必要とされる一連の手続きについて,例示をしたいと思います。

以下,Excel による層別ヒストグラム|度数多角形の描画法です。ここでは一連の手続きを,本頁更新時点で最新の「Office365版」 Excel (ver.1811)で追っています。この手続きは,「永続ライセンス版」にいうところの Excel 2019, Excel 2016, Excel 2013 あるいは Excel 2010 でも基本的には同じ流れとなりますが,一部ボタン等の名称や UI が異なる箇所が存在する場合があります(この場合,可能であれば当該箇所に併記していきます)。

元データ

綾子

1

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

その後,いかがですか?

MisterA

2

あ,えーと。これを見てください

元データ

綾子

3

これは何ですか?

MisterA

4

あれから部長がまたヘンなコト言い出して

「出来る営業マンは足で稼ぐもんだ! だいたいお前らにはその基礎がねぇ!」とキレだしたんです。いやそこまではまぁ教訓として言うならば百歩譲っていくらかは納得できる部分もあるにしろまさかその後,僕を含む部の3人に,リアルに歩数計を持たせるとは! 発想がナナメ上すぎてもう言葉もありませんでした。この歩数計ですよ! この!

綾子

5

…。

MisterA

6

この表は,ここ1ヵ月にわたり3人が苦悩し格闘をつづけた(1日あたり歩数の)記録です。

これをヒストグラムにすると,ですねカカタカタ

MisterA

7

こんなふうになるんですよ

元データ

綾子

8

ふむむむ。15000それから20000歩あたりにも山がありますね。健脚というかみなさん,すごいじゃないですか。運動不足の私じゃ,とても無理そうです。

MisterA

9

う~んですか? まぁ,ですよね

とんだ理不尽を見返してやろうの一心でみんながんばったんです

綾子

10

ところで今日のお話というのは,いったい

MisterA

11

それですよ!

まずはこれ。これを,層別してみてください。3人の別に。

話は,それからです。

綾子

12

ん?

じゃあ

綾子

13

層別に度数分布を可視化する場合,やはり第一選択はスケールを統一して当該個分の分布図をそれぞれに作成していくことになるんじゃないかなと思います。

この場合,具体的には ヒストグラムの作成 with Excel で示したような手続きを繰り返しおこなっていくわけですが,ここでは 何らかの事情や意図において第一選択を用いることができないor用いないときの手段すなわち1つの図で複数の層を表現したい場合の手続きを見ていきます。

このような場合,ヒストグラムより度数多角形(度数折れ線)をチョイスしたほうが判別に易い点で有効だと思いますが(A・B),周知性をかんがみればヒストグラムを用いたい場合もあるかもしれません(C)。

以下,それらを作成するための手続きです。それぞれにプロセスが異なりますので,目的の図を選択(クリック)して当該部分までジャンプしてください。

PLEASE CHOOSE

type A

度数多角形|面グラフ

綾子

A1

度数分布表を用意します。目的が度数多角形ですので,必ず最初と最後に度数 0(ゼロ)の階級を用意しておきます。ここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法betterかと思います。

綾子

A2

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

綾子

A3

(この時点で見出しはありませんが)「(階級の)中心値」とすべての層(群)列を選択し

リボンの挿入タブグラフグールプにある折れ線/面グラフの挿入ボタンをクリックします。

2013

挿入タブグラフグールプにある面グラフの挿入ボタン

2010

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

つづいて,プルダウンからグラフを選択します。

綾子

A4

グラフができました。

ここではtype A/B/Cすべての方法でラベルに階級の中心値をつかうとした手前,他の方法のアウトプットともスケールをあわせておく必要があるかなと思います。したがって下図の状態では軸位置が適当ではないので(注:面グラフの軸位置のデフォルトは「目盛」, 棒・線グラフは「目盛の間」),これを修正していきます。横軸上で右クリックから軸の書式設定を選択し(下図上段)

軸位置を目盛の間に変更します(下図下段)。

綾子

A5

そのままいずれかの系列を選択し

塗りつぶしを単色にし,任意のを指定します。

また透明度も適宜調整して他の系列との積極的な干渉を和らげます。

こうした書式設定を残りすべての系列に対し繰り返しおこなって完成です。

2010

このバージョンでの表記:透明度透過性

type B

度数折れ線|線グラフ

綾子

B1

度数分布表を用意します。目的が度数折れ線ですので,必ず最初と最後に度数 0(ゼロ)の階級を用意しておきます。またここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法 が better かと思います。

綾子

B2

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

綾子

B3

層(群)の列記号をすべて選択し,右クリックからあたらしい列を挿入します(挿入)。

綾子

B4

あたらしく挿入された列の見出しに,層(群)名をあらためて入力します。

つづいて最初の層(群)名の直下のセル(ここでは セルH2)に

  • =IF(OR(AND(K1=0, K2=0, K3=0), AND(ISNUMBER(K1)=FALSE, K2=0, K3=0), AND(K1=0, K2=0, ISNUMBER(K3)=FALSE)), NA(), K2)

と入力し,これをコピーしてあたらしく作成した表の領域を埋めていきます(一部を除いて,原則 度数 0N/Aへ変換するための式。理由は後述)。

綾子

B5

(この時点で見出しはありませんが)「(階級の)中心値」と(あたらしく作成した方の)すべての層(群)列を選択し

リボンの挿入タブグラフグールプにある折れ線/面グラフの挿入ボタンをクリックします。

2013

挿入タブグラフグールプにある折れ線グラフの挿入ボタン

2010

挿入タブグラフグールプにある折れ線ボタン

つづいて,プルダウンから折れ線 または マーカー付き折れ線グラフの適当な方を選びます(例では「折れ線」で作成します)。

綾子

B6

グラフができました(下図上段)。あとは任意に書式を整えて完成です。

なおStep B3, B4N/A処理をしておかないと,グラフは下図下段のようになります。この場合,縦軸0位置に評価する人を惑わすおそれのある(何らの意味を含むといった推測を生む)副産物の水平線が発生します。加えて見た目的にもどこかスマートさに欠けるので,N/A 処理は必須と言っていいんじゃないかと考えます。

type C

ヒストグラム|棒グラフ

綾子

C1

度数分布表を用意します。ここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法betterかと思います。

綾子

C2

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

綾子

C3

(この時点で見出しはありませんが)「(階級の)中心値」とすべての層(群)列を選択し

リボンの挿入タブグラフグールプにある縦棒/横棒グラフの挿入ボタンをクリックします。

2013

挿入タブグラフグールプにある縦棒グラフの挿入ボタン

2010

挿入タブグラフグールプにある縦棒ボタン

つづいて,プルダウンから集合縦棒グラフを選択します。

綾子

C4

いちおうのグラフができました。

つづいて,任意の系列の上で右クリックし,データ系列の書式設定を選択します(他の方法でも)。

綾子

C5

系列の重なりを100%にし,要素の間隔を0%に変更します(他の系列も連動します)。

綾子

C6

そのまま 塗りつぶしを単色にし,任意のを指定します。

また透明度も適宜調整して他の系列との積極的な干渉を和らげます。

こうした書式設定をすべての系列に対し繰り返しおこなって完成です。

2010

このバージョンでの表記:透明度透過性

綾子

14

おぁあぁ!Aさん! ダントツ上の方陣取ってますね! すごい!

MisterA

15

自分で言うのもまぁアレなんですけど,すごいですよね。部長の言うとおり,この期間はさいわい成績もついてきたという感じで

綾子

16

ホントデスカ!? さすがです!

これじゃ部長もぎゃふんと言うしかないですよ。

MisterA

17

それがおっかしいんですよね

ふいにひとこと「いつもお前を見ているぞ」なんてすわった眼をしながら耳元でささやいてきたりするわけで。これ実は部長なりのねぎらいの言葉だったりするんですか? なんともキモチワルサしかないと言うか

綾子

18

…。

MisterA

19

まぁとは言え,気にかけてもらえるようになったんだとしたら,以前に比べれば進歩ですよね!?


あ! 確かによく考えてみれば,そんなフシもありました!

ええとですねひと月ほど前,部長から,実は“商売の守り神” だってほらこのお守りをいただいたんです。

綾子

20

へぇあの部長さんが,ですか。

実はみんなが言うのと逆で,意外と部下思いなトコがあったんですねー。それもお守りだなんて,見かけによらずやることがかわいいし。

MisterA

21

ですよねー。

いや,それだけじゃないっすよ。週に1度,わざわざこのお守り,回収してくれたりしちゃうんです。

綾子

22

はい!?

MisterA

23

なんでもこのカミサマは定期的に特殊なスピリチュアルエネルギーをチャージすることで,持つ人の運気をさらに高めることができるといわれてる,とか

部下を相手にこの親心部長もなんか悪い人じゃないのかなと。

綾子

24

ちょっと拝見しますねっ!

MisterA

25

あぁ! 寺畠さんっ! お守り袋をあけてはダメえええっ! 副作用としてま末代までおそろしくタタられるって部長があぁぁ言ってぇあえええぇっ!!

綾子

26

んんんっ!? この小さなハコはなんだろう

…ネットで…カタカタ…カタカタターン…

綾子

27

。これGPSロガー,ですよ。

MisterA

28

!!

MisterA

29

あのじゃ僕が仕事中ネカフェ行ったことも育毛サロン行ったことも公園で昼寝してたことも牛丼屋で紅ショウガしゃぶりながら3時間ねばったこともひとりカラオケに行ったことも街コンに参加したことも電器街で店員とパーツバトルしてたことも部長ぜんぶがってん承知!?

綾子

30

コク。

MisterA

31

ちくしょう やっぱり転


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

LastUpdate

.
このページの先頭へ