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

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

How-to ヒストグラム

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

 more...

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

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

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

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

晴花

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

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

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

元データ・ヒストグラム

晴花

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

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

式は…下のとおりです。

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

晴花

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

晴花

次に、この範囲を等間隔に分けていくことを考えます。

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

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

晴花

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

晴花

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

晴花

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

晴花

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

晴花

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

晴花

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

晴花

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

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

晴花

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

晴花

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

晴花

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

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

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

晴花

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

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

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

晴花

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

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

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

晴花

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

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

晴花

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

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

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

晴花

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

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

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

晴花

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

社長

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

晴花

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

晴花

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


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

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

晴花

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

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

晴花

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

晴花

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

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

説明のみ

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

晴花

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

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

説明のみ

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

晴花

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

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

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

説明のみ

晴花

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

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

説明のみ

晴花

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

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

晴花

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

階級上限値の入力

晴花

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

※ボタンが表示されていない場合、「分析ツール」アドインのインストールが必要です(「分析ツールを読み込む」[2007][2010][2013]-officeヘルプ外部サイト)。

zoom可能

データ→データ分析

晴花

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

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

晴花

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


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

晴花

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

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

zoom可能

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

晴花

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

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

度数分布表

晴花

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

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

階級の表し方を修正

晴花

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

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


※「次の級」?

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

晴花

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

挿入→縦棒

晴花

《2-D縦棒》グループの [集合縦棒]ボタンをクリックします。

集合縦棒

晴花

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

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

晴花

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

zoom可能

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

晴花

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

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

晴花

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

FINISHED

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

晴花

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

晴花

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

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

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

晴花

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

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

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

晴花

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

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

晴花

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

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

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

ふた山型のヒストグラム

晴花

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

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

晴花

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

Tips) 度数折れ線

晴花

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

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


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

LastUpdate

2014.10.4

このページの先頭へ