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

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

ツール[1] ヒストグラム

How-to

ヒストグラム

ヒストグラムは、特定の集団・集合などにおいて観測される値を、階級と呼ばれるブロックごとに振り分けてカウントし(度数)、値の分布状況(ばらつき)を視覚的にとらえるために利用されるグラフです。

 more...

ものつくりの現場ではQC7つ道具の1つとして有名ですが、事務しごとの現場でも、売上額・販売数量などの分析ツールとして活用することができます。

Excel でヒストグラムを作成するには、「度数分布表」を作成しグラフ化する手続きが一般的なように思います。度数分布表の作成にはさまざまな方法がありますが、こちらでは「分析ツール」と呼ばれる Excel のアドインを利用した方法でおこなっています。

また、ここで扱うヒストグラムは QC ないしは R&D といった工学的な領域で用いられるそれではなく、一般的な事務しごとでの利用を想定したものです。

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

晴花

1

元のデータです。売上金額(万円)を個人別に集計したものです。

こちらの金額は、1 万円未満の端数は生じないものとします。

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

元データ・ヒストグラム

晴花

2

まず、このデータがとる範囲(range)を見てみます。

このケースはデータ数が少ないので、パッと見で分かるところかもしれませんが、念のため…シートの空いているところを使ってmin,max関数で最低値と最高値をそれぞれ求めてみます。

式は…下のとおりです。

最高値と最低値をMAX,MIN関数で計算 [最低]=MIN(D2:D25), [最高]=MAX(D2:D25)

晴花

3

結果は順に 18, 83 となります。したがって、83-18 = 65 の幅があることが分かります。

晴花

4

次に、この範囲をいくらかに分けていくことを考えます。

65 の幅を、上下に若干の余裕をとって何個に分けていくべきか…ということです。

晴花

5

あ、ええと…
分布をできるかぎり明瞭かつただしくとらえたい or 伝えたい ――という趣旨のもと、誤読をふせぐ意味合いからも、これは原則としては 等間隔であるべきとされるところでShow。

こちらからヒストグラムの形式に関するやや細かな話題になりますが、工程外の内容ですので JavaScript が ON の環境ではデフォルトで表示されないように設定しています。このエリアをクリックすると表示されますので、必要に応じてご覧ください

晴花

6

では具体的な工程に入る前に、少し遠回りになっちゃいますが、いささかストリクトな意味での階級の分け方から対応を考えていきたいと思います。たとえば、下の図のようなヒストグラムがあるとしたとき…

晴花

7

棒(「階級」または「ビン(bin)」といいます)A をとりあげると、図の青い点を「下境界値」、赤い点を「上境界値」と呼びます。

晴花

8

隣の棒 B をとりあげた場合には、下境界値と上境界値は次のようになります。

晴花

9

したがって、棒 A の上境界値と棒 B の下境界値は一致します。換言すれば、これは棒と棒の間に隔たりがない(=連続)…ということを意味します。ヒストグラムの特徴とも言える、グラフに間断なく並べられる棒の形状は、この点に理由があります。

晴花

10

では次にですね… 今、この棒 A の上境界値、つまり棒 B の下境界値を仮に「10」に決定したとします。

晴花

11

このとき、振り分けるべきデータとして「10」が登場してしまいました。これでは棒 A か棒 B かどちらでカウントすべきか…という問題が生じてしまいます。

晴花

12

そうした厄介なコトを避けるため、しごとの現場によっては 境界値を設定するとき, 境界値の上にデータが存在しないような値を設定すること が求められることもあります。こうした場合、ひとつの方法として… 境界値を 値の最小単位(たとえば図であげた「10」を「10万円」としたとき、最小単位は 1 万円)の半分(0.5 万円)になるように設定してやれば、境界値とデータとが被ってしまうことはなくなります。このケースで例えるならば、下のように A と B の境界値を 10.5 万円とする…といった具合です。

…厳密な手続きにしたがう QC の現場などでは、こうした点まで考慮されることが多いように思います。

晴花

13

ということで、そろそろ「65 の幅を、上下に若干の余裕をとって何個に分けていくべきか」…ということを考えなくてはならないですね。

晴花

14

いささか大げさに掲げたきらいもありますが… 実のところ、これは “正解” が存在する類のものではありません。タテマエ的にはその手続きがどうであれOK …というべきでしょうか。

晴花

15

でもやっぱり…。階級の数が少なすぎても、多すぎても、要点を得なくなるのも事実です。したがって、ある意味で自分の判断が最も問われてくるところかもしれません。であるならば、何らの目安は欲しいもの…ということで、下の図のような計算を順に処理してそれらのアテをつけることがあります(「平方根選択」とも)。

  • 階級の数:√n
  • 階級の幅:範囲 ÷ √n

[階級の数]√n, [階級の幅]範囲÷√n

晴花

16

今回の例にあてはめると、データ数 n は 24, 範囲は 65 万円でしたから…

階級の数はおよそ 5 個、階級の幅はおよそ 14 万円(ともに小数点以下切り上げ)…という値がみちびかれます。

[階級の数]√24≒5, [階級の幅]65÷√24≒13

晴花

17

ちなみに、階級の数については スタージェスの公式 も利用されています。

スタージェスの公式を以下に示します。赤字部分がデータの総数(n)です。このケースでは 24 件ですが、この数字を変更すれば他の事例にも適用できます。

[スタージェスの公式] 階級の数=1+log(n)/log(2)

晴花

18

えーと…結果はおよそ 6 です。√n の場合とかい離する値ではないですね。

※ この場合, 階級の幅は 範囲 ÷ (1+(log(n)÷log(2)))


※その他 こんな目安も

  • スコットの選択
  • フリードマン=ダイアコニスの選択

これらは先の 2 指標と異なり、階級の数を直接はじかないアプローチとなります(先に階級幅を計算して 範囲とそれを除すことで階級の数を求める)。エクセルでの計算例は こちら(―外部サイト「BDAstyle」)

晴花

19

…と、いうことで今までみちびいた条件を一度整理してみます。

ここでは、次のような方針を定めました。

(1)境界値は測定単位の1/2(0.5)で設定, (2)階級の数は5, (3)階級の幅は14

晴花

20

下のグラフが、この方針のもとに作成したヒストグラムです(サンプルのデータから作成したものではありません。イメージです)。

淡い緑色で囲った数字は「階級値」または「階級の中心値」といいます。“中心値” の文字どおり、これはその階級の下境界値と上境界値の真ん中([下境界値+上境界値]÷ 2)の値です。ヒストグラムを作成するにあたって、グラフの横軸にこの階級値(中心値)を示しておけば、境界値を明示する必要は基本的にはありません(計算できるので)。

階級の中心値のみを表示したヒストグラムのイメージ

晴花

21

で、私がこのヒストグラムを意気揚々とウチの社長に提出しに行くわけですが…。

社長

22

「キミはこの図を見て RS(販売担当)部員が最初に何を思うか…ここに来るまでに少しでも考えたことがあるのか(イヤミ的な意味で)?」

晴花

23

…なーんてシーンが目に浮かびます。…クッ。

晴花

24

ここまで長々とエクセル操作以外のコトをお話してしまいましたが、総務・経理など一般的(と言っていいかわかりませんが)な事務しごとでの活用を想定した今回のようなケースでは、使う人から見た “わかりやすさ” にこそ意味をおくべきかも…なんて思うところもあります。先に見たような指針やルールを軽視するわけではありませんが、TPOに応じたアレンジについて考えることもやっぱり大切…なのかもしれませんNe


※階級値でヒストグラムを描く

(書式の設定上)「数値」とみなされる要素を項目軸に振る場合、こちら(―外部サイト「BDAstyle」)の前段の手続きの方がスムーズかと思います。

晴花

25

では、ここから具体的な工程のお話となります。

事務しごとという背景においては、やはり「 “見る人” “使う人” にデータの特徴を示すにあたって、better(≠best)な選択ができたか」の一点が問われてくる場合も多いように思います。ですからこのケースでは、単純に “キリのいいところ” で分けるという選択をしたいと思います

晴花

26

具体的には…10 万円刻みで区切っていく方法で問題なさそうに思います。その場合、範囲が 65 万円ですから上下の若干の余裕を考えても、8 個の階級で全体をあらわすことができますね。

晴花

27

ここで、あらたに決めなおした条件を下のようにまとめておきたいと思います。

このケースでは、以後、次の 2 つの条件のもとヒストグラムを作成していきます。

説明のみ

(1)階級の数は8つ, (2)階級の幅は10万円

晴花

28

エクセルの分析ツールでは、原則として境界値を指定する方式でなく、それぞれの階級でとりうる最大値(下の図の)を指定する…という方式で階級の幅を決定していきます。言い換えれば「前の階級の最大値より大きく、今の階級の最大値以下(たとえば Amax<x≦Bmax)」という条件で階級ごとに度数をカウントする方式です。

分析ツールのこうした特性にならい、今回のケースではグラフに中心値や境界値を表示する方式をとりません。その階級に該当する値の範囲を表示していく、という方法をとりたいと思います。

説明のみ

エクセル「分析ツール」における度数カウントの特性

晴花

29

さて、階級ごとに値の範囲を表示するにも、次の点を決めなければすすめません。

具体的な 8 つの階級に属する値を考えると、10~19, 20~29, 30~39, ……… , 80~89 とする案が 1 つ。また、11~20, 21~30, 31~40, ……… , 81~90 とする案が 2 つ目にうかびます。

ここで、仮に前者を プランA、後者をプランB と呼んでおきます。今回のケースでは小数点以下端数が発生しないという設定でしたので、区切りがいいという意味で プランA のほうが扱いやすく思います。

説明のみ

晴花

30

反対に「19.5 万円」のように端数が発生する場合には、プランB のが収まりがよさそうです。ただ、この場合には、たとえば 10.5 のような値が発生する可能性も考えなくてはならなくなります。

つまり、ラベル上「11-20」の表記における “11” は 不適切なものとなってしまいます。「10<x≦20」など、代わりに下のような表記へと置き換えてやることも必要となってくるかと思います。…いずれにしろ、これらは「ラベルとしていかに適切かつ分かりやすく階級の幅を示すか」という “見た目” の問題を指しているにすぎません。分析ツールが階級の幅を判断するのは、あくまで(階級ごとにとりうる)最大値(上限)となります。

説明のみ

晴花

31

では、階級の数は 8 つ…に決まったことですので、シートの空いているところにでも それぞれの階級がとりうる最大値を書き入れていきます。

繰り返しですが、最大値だけを入力する実際のデータにその値が登場するしないは関係なしに)…というところに注意です。

晴花

32

そうすると、こんな感じ(ハイライト部分)になります。

階級上限値の入力

晴花

33

…では次に、リボンのデータタブ →分析グループにあるデータ分析ボタンをクリックします

※ ボタンが表示されていない場合、「分析ツール」アドインのインストールが必要です(「分析ツールを読み込む」 - Officeサポート)

データ→データ分析

晴花

34

データ分析ダイアログがひらいたらヒストグラムを選択してOKボタンをクリックします。

「データ分析」ダイアログ…分析ツールからヒストグラムを選択

晴花

35

まず、ヒストグラムダイアログの入力範囲データ区間を指定します。具体的には次の図のように、シート上の対応する色の領域をそれぞれ指定していく作業となります。


あ…! ここでは見出しも選択していますのでラベルにもチェックを入れておいてくださいね。

晴花

36

さらに出力先をアクティブにしておきます。ここでは同じシート上に出力するものとして、空いているセルを任意に指定していただいて構いません。便宜上、ここではセルH1(シート上のピンク色の部分)としています。

全部入力できたらOKボタンをクリックします。

「ヒストグラム」ダイアログ…[入力範囲]$D$1:$D$25, [データ区間]$F$1:$F$9, [出力先]$H$1, 「ラベル」をチェック

晴花

37

階級ごとの人数がカウントされました(下図)。

表では、カウントされた人数の見出しが「頻度」となっていますが、「度数」とする表現の方がより一般的なように思われます。なおこの表は「度数分布表」と呼ばれます。

度数分布表

晴花

38

ところで、この度数分布表の階級のあらわし方…19…29…39…は あくまで「分析ツール」での処理を念頭にしたものですから、他の人から理解の得られそうな表現とは言えません。先に「プランA」方式を採用することに決めましたので、下の図のようにより分かりやすい表現に改めます。

このとき「10-19」と入力すると自動的に日付として認識されてしまいますので、頭にアポストロフィ( ' )をつけ「'10-19」と入力することで、通常の文字列の扱いとしておきます。

階級の表し方を修正

晴花

39

さて、ここからは上の度数分布表をもとに棒グラフを作成する工程です。

表の「次の級」を除く階級・頻度の両範囲(下のハイライトの部分)を選択します。


※「次の級」?

最後の階級を超えるデータが存在した場合にはここでカウントされます(たとえば、このケースで最後の階級「80-89」に含まれない「100」というデータが存在したような場合)。つまり、事前に最小値・最大値・範囲を確認し、適切に階級を設定しておけば意味のない項目です。

晴花

40

リボンの挿入タブ →グラフグループにある縦棒ver.2013 :[縦棒グラフの挿入])ボタンをクリックします。

挿入→縦棒

晴花

41

「2-D 縦棒」グループの集合縦棒ボタンをクリックします。

集合縦棒

晴花

42

表示されたグラフの任意の棒(柱)をクリックし、「頻度」系列を選択した状態にしておきます。

任意の系列をクリックすると、棒の4隅にマーカーが表示されます

晴花

43

リボンの書式タブ →現在の選択範囲グループにある選択対象の書式設定をクリックします。

書式→選択対象の書式設定

晴花

44

データ系列の書式設定ダイアログ・「系列のオプション」カテゴリの要素の間隔スライダーを「なし」(ver.2013 :「0%」)まで動かして、閉じるボタンをクリックします。

要素の間隔を[なし]にする

晴花

45

ヒストグラムの完成です。

FINISHED

売上実績ヒストグラムの完成

晴花

46

Tips 的なコトになりますが、作成した度数分布表に「累積度数」や「相対度数」、あるいは「累積相対度数」を必要に応じて加えることができます。


累積度数は…たとえば売上 49 万円までの RS 部員は何人いるか…といったことの理解に強みを発揮します。また、相対度数は…たとえば、現在の RS 部員は 24 名ですが…昔はもっと大勢いました。相対度数であれば、この 2 つの時点間で人員の数が異なっても、特定の階級の変化に関して比較・分析が可能となります。

  • [累積度数] =SUM($C$3:C3)
  • [相対度数] =$C$3/SUM($C$3:$C$10)

Tips) [累積度数]=SUM($C$3:C3) [相対度数]=C3/SUM($C$3:$C$10)

Tips) [累積度数]下から階級を束ねて考えてみるとして、ある階級までのボリュームを絶対数で確認する [相対度数]・構成比を確認する ・時系列上で比較する

晴花

47

ということで…ヒストグラムの縦軸には、度数のかわりに 上にみた相対度数をとる場合もあります(下図)。

Tips) [累積度数]縦軸は「度数」だけでなく「相対度数」も利用可能

晴花

48

あと、あたりまえでナンなんですが…相対度数はいわば構成比ですから…すべてを足し合わせれば 1(100%) になるはずです。

この点を念頭に置いておけば、たとえば 管理上、あまりウエイトの高くない位置にあるとされる…度数の少ない階級などを併合してしまうことも可能です(不等間隔な階級幅。ただし前例やガイドラインなどに拠ることができない場合、使いどころの判断が難しいと思います。ここではあくまで例外的処理としての例示です)。


※階級幅の異なるヒストグラムの例

Examples, "Histogram" - Wikipedia[en] (ページ中段にイメージ)

もっとも、このパターンのヒストグラムを Excel ベースで描画するのはちょっとばかりややこしいです(Excel 外でレタッチが必要)。作業効率の点からは「R」など他の処理系に依存した方が捗るかもしれません。

Tips) [累積度数]一部階級の併合(不等幅な階級のヒストグラム)

晴花

49

併合すると、上のようなヒストグラムにかたちをかえます。

相対度数が赤矢印の値となることに注目です。…つまり、併合の対象とした階級にかんして、相対度数の合計を併合前の階級の数で除しています。

まぁ、こんなややこしいことをしなくても「(たとえば左側の併合した階級について)3つあわせて 0.167 (16.7%) なんだし…そのまま 0.167 の高さをとればいいじゃん! 」…と感覚的には思いたくなるようなところもないわけではないのですが、いかんせん…それではちょっとばかり不都合が生じます。

相対度数ではなく、(ヘンな言い方ですが)ナマの度数で考えてみるとその点は明らかです。具体的にナマの度数…左の併合区間の総度数は 4 人で 右のそれは 3 人ですね…だけに注目してヒストグラムを描くと………こんな ↓ カンジになるようです。

45番のステップで完成したヒストグラムとあらためて見くらべてみると、やはり「ん?」な感覚が拭えません。

晴花

50

等間隔に階級をとってヒストグラムをつくっていくときは、こうした感覚に悩まされることもないので注意をそそぐべきところではないのかもしれませんが、念のため補足として加えると…

ストリクトな意味においては、度数は本来 “高さ” でなく “面積” によって示されていることを頭の片隅にでも入れておいた方がシアワセかもしれません。

このページの最初の「誤読をふせぐ意味合いからも、これは原則としては等間隔であるべきとされるところ」という言及は、以上のような背景があるゆえです。

度数をあらわすのは「面積」

晴花

51

さて、今度は累積相対度数です。相対度数と同様に比較に強みを持ちますが、こちらはグラフ化することでなおのこと使い勝手のいいツールとなると言えるのではないでしょうか。

分析ツールのヒストグラムダイアログを操作する際、下のように累積度数分布の表示グラフ作成にチェックを入れておくことで…

Tips) 「ヒストグラム」ダイアログでの累積相対度数の設定項目

晴花

52

累積相対度数線の入ったヒストグラムの原型を簡単に作成することができます。作成された原型を加工すると、下のようなヒストグラムができあがります(加工については パレート図の作成 QC版 を参照ください)。

累積相対度数線の形から、全体の構成比の変化を直観的に確認できます(ただしグラフの階級のとり方 および縦横比を 異時点間で統一していることが前提)。

Tips) 累積相対度数線を加えたヒストグラム

晴花

53

これはまた、トップ x %, ボトム x %分析にも利用できちゃいますね…。

たとえば売上上位 20% の区切りとなるデータ(人)は、階級「60-69」にある(いる)ことが読み取れます。

晴花

54

次にヒストグラムの見方に関する補足です。

たとえば、私が毎月、以上みてきたデータ処理をしていたものとして、リサーチサービス社の個人別売上データを集計したヒストグラムは(乱暴な言い方ですが)正規分布ライクになることを感覚的に知っていたとします。しかしある月、なにげに集計した結果次のようなヒストグラムが出力されてきた…としたらどうでしょうか?

ドーム型(高原型)のヒストグラム

ふた山型のヒストグラム

晴花

55

こんなときは 層別 によって原因を掘り下げてみることも大事かもしれません。

上のヒストグラムのタネ明かしですが…紫の方は、この月「若手」「中堅」「熟練 」社員による違いが色濃くなったことが、また緑の方は、この月「営業活動に重点的にリソースを注げたグループ」と「他のことも同時進行しなければならなかったグループ」による違いが顕著に出た結果だとわかりました。

晴花

56

最後に、ヒストグラムにくらべて見かける機会はぐっと減りますが、ヒストグラム様(よう)のグラフとして「度数折れ線」または「度数多角形」などとよばれるグラフがあります。こちらは、ヒストグラムの各 “棒”の上辺の中点を左からつないだものになります。ただし、下のように折れ線下の面積がヒストグラムの外周線下の面積と同じになるよう、度数折れ線のグラフの両端には必ず度数 0(ゼロ)をとる必要があります。

Tips) 度数折れ線

晴花

57

私は、この度数折れ線の最大の長所は…やはり 1 枚のグラフの上での多系列の層別にだって比較的耐え抜いてしまう便利さにあると思っています(逆にそれが時系列の変化ではなく度数分布を表現するものと理解してもらうための努力は必要ですが…)。同じ条件であれば、下図のようにヒストグラムではそれがなかなかに難しいところです。

Tips) 度数折れ線

晴花

58

以上、今回は分析ツールでヒストグラムを作りましたが、今の方法以外でヒストグラムを作るとしたら…

countif関数を使うfrequency関数を使うピボットグラフを使う(以上、外部サイト ―「BDAstyle」)といった方法でも可能だと思います。

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

LastUpdate

2015.8.26

.
このページの先頭へ