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

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

How-to

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

度数分布の層別

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

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

以下、Excel 2013 を使った度数分布の層別に関する表現です。Excel 2016, Excel 2010, Excel 2007 についても基本同じ流れとなりますが、一部ボタン等の名称が異なる箇所があります(この場合、当該箇所に明記しています)。

元データ

綾子

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・D)。

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

PLEASE CHOOSE

Method A

度数多角形|面グラフ

綾子

A1

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

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

綾子

A2

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

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

綾子

A3

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

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

2016

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

2010

2007

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

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

綾子

A4

グラフができました。

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

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

綾子

A5

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

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

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

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

2010

2007

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

Method 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)

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

綾子

B5

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

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

2016

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

2010

2007

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

つづいて プルダウンメニューの折れ線 または マーカー付き折れ線ボタンの適当な方をクリックします(ここでは「折れ線」で作成します)。

綾子

B6

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

なお Step B3, B4 で N/A 処理をしたのは、下のグラフの赤い囲みの部分のような、評価する人を惑わすおそれのある(何らの意味を含むといった推測を生む)余計な線を不可視化するためです。まぁ、私は…見た目的にスマートな方がなんかいいだけなんですけど…。

Method C

ヒストグラム|棒グラフ

綾子

C1

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

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

綾子

C2

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

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

綾子

C3

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

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

2016

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

2010

2007

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

つづいて プルダウンメニューの集合縦棒ボタンをクリックします。

綾子

C4

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

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

綾子

C5

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

綾子

C6

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

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

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

2010

2007

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

Method D

ヒストグラム|線グラフ

綾子

D1

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

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

綾子

D2

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

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

綾子

D3

度数分布表の右に 2 列あけて層(群)名を 2 セット繰り返し入力します。なお後からグラフ化したとき系列が容易に判別できるよう、この時点では最初のセットの名前に記号などの修飾を加えておくと便利です(ここでは “線” の意味で先頭に “L” を加えました)。

綾子

D4

下の図の青緑色のセルに

  • =INT((ROW()-1)/2)

と入力し、これを下方に 階級の数の 2 倍量コピーします。ここでは階級の数は 21 ですから、これにしたがえば あと 42 行これをコピーすることとなります(先頭行を含めると 43 行できあがる)。もっとも、厳密に数えなくても階級の数より 1 つ大きな数字がでてくるまでてきとうにコピーしていった方が簡単かもしれません。

ただし 1 つ注意点ですが…Row関数 で連番を作っているので、開始行番号が “2” でない場合(この例のシート構成とは異なる場合)、開始行で ROW()-1 が 0 を返すように -1 部分を調整する必要があります。

綾子

D5

下の図の紫色のセルに

  • =OFFSET(H$2, INT((ROW()-1)/2-0.5), 0)

と入力し、線グラフを階段状にあらわすために元表の度数を(L列を x, P・Q・R列を y とする直交座標のように見立てて)調整して転記します。これをP・Q・R 3 列の最下行までコピーします。

ここでも Row関数 を使用しているので上と同様の要注意点があります。

綾子

D6

下の図のオレンジ色のセルに

  • =IF(OR(AND(P1=0, P2=0, P3=0), AND(ISNUMBER(P1)=FALSE, P2=0, P3=0), AND(P1=0, P2=0, ISNUMBER(P3)=FALSE)), NA(), P2)

と入力し、これをコピーして表の残りの領域を埋めていきます(一部を除いて、原則 [ここではP・Q・R列の]度数 0 N/A へ変換するための式。理由は後述)。

綾子

D7

左側の度数分布表を使って、棒グラフをつくります(挿入タブグラフグールプにある縦棒グラフの挿入集合縦棒)。

2016

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

2010

2007

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

綾子

D8

右側の度数分布表の左半分を選択しコピーします。

綾子

D9

グラフエリアを選択し、上でコピーした系列を貼り付けます。

(※下の図は貼り付け前のもの。なお、ここでは棒をわかりやすく示す目的で太くしています。実際の作業では必要ありません)

綾子

D10

グラフエリアで右クリックし、グラフの種類の変更を選択します(図・上)。

グラフの種類の変更ダイアログから組み合わせカテゴリを選び、あらたに加えた 3 つの系列について、グラフの種類を折れ線に変更したうえで第 2 軸に振り替えます(図・下)。


2010

2007

次の処理を順番におこないます。

  1. あたらしく追加した系列を 1 つ選択デザインタブグラフの種類の変更 折れ線に変更します。これを 残りの系列に関して繰り返します。
  2. ふたたびあたらしく追加した方の系列を 1 つ選択書式設定ダイアログをひらく 使用する軸第 2 軸へ振り替える …作業を残りの系列に関して繰り返します。

綾子

D11

グラフを選択したまま…

リボンのデザインタブグラフのレイアウトグールプにあるグラフ要素を追加ボタンをクリックします。

2010

2007

レイアウトタブグールプにあるボタン

つづいて プルダウンメニューから第 2 横軸とたどります。

2010

2007

第 2 横軸左から右方向へ軸を表示ボタン

綾子

D12

作ったばかりの第 2 横軸を選択し、書式設定で

  • 軸の種類 … “日付軸”
  • 軸位置 … “目盛”

に変更して第 1 横軸とスケールを(機械的に)合わせ、くわえて

  • 目盛の種類 … “なし”
  • ラベルの位置 … “なし”

としてこの軸を不可視にします。

綾子

D13

右側の度数分布表にて、Step D3 で便宜的につけた各層(群)の名前を本来のものに変更します(下の図は変更前のもの)。

綾子

D14

第 2 縦軸も Step D12 後段と同様の操作(目盛・ラベル)で不可視にします。

つづいて、棒グラフに関する凡例についてのみ削除していきます(個別に選択→DELETE)。

綾子

D15

最後に棒グラフの塗り色を、それぞれ塗りつぶしなしに変更し不可視にします。つづけてその他必要な書式を任意に設定して完了です。

綾子

D16

なお Step D6 で N/A 処理をしたのは、下のグラフの赤い囲みの部分のような、評価する人を惑わすおそれのある(何らの意味を含むといった推測を生む)余計な線を不可視化するためです。まぁ、私は…見た目的にスマートな方がなんかいいだけなんですけど…。

綾子

14

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

MisterA

15

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

綾子

16

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

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

MisterA

17

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

ひとこと「いつもお前を見ているぞ」なんてすわった眼をしながら耳元でささやいてくるんですよ。これが部長なりのねぎらいの言葉だったのかなぁ…。これまたキモチワルイと言うか…。

綾子

18

……。

MisterA

19

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

そのあかしというか…実はこの歩数計だけじゃなく…“商売繁盛のお守り” だって…ほら…もらったんです。…なぜか週に 1 度回収されるんですけど。

綾子

20

……え!?

MisterA

21

なんでもこのカミサマは週に一度特殊なスピリチュアルエネルギーをチャージする必要があるとか…。そう考えると、部長もなんか悪い人じゃないな…と最近は思いはじめたり。

綾子

22

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

MisterA

23

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

綾子

24

……ナンゾコレ!? この小さなハコは…?

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

綾子

25

げ………。これ…GPSロガーですよ。

MisterA

26

!!

MisterA

27

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

綾子

28

………コク。

MisterA

29

ちくしょう やっぱり転


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

LastUpdate

2016.7.12

.
このページの先頭へ