データ分析

ヒストグラムを作成するためのエクセル手順

ヒストグラムは、細かなデータ分析を始める前に全体の傾向を把握するのに便利です。

ヒストグラムで傾向をつかんでからデータ分析をヒストグラムは度数と階級を視覚化した棒グラフです。お客様毎の累計売上や購入頻度、あるいは生産現場における故障発生頻度など、ビジネスの様々なシーンで、傾向を大まかな把握するのに使える便利な方法です。具体例としてトライアスロンのレース結果を分析してみました。...

今回は、エクセルを使ってヒストグラムを作成する手順をお伝えします。

<スポンサードリンク>



ヒストグラムを作成するための大まかな手順

具体的な手順の前に、大まかな手順をお伝えします。

  1. 元データを準備する(セル結合などが無い状態にしておく)
  2. 階級を決める(各データの「幅」をどれくらい持たせておくかを決める)
  3. エクセル関数を使って、各階級の度数(データの数)を求める
  4. 折れ線グラフを使ってヒストグラムを作成する

データの前処理は大変

ビジネスの現場において、最も手間が掛かるのは、最初のデータ準備です。
最初からデータがキレイに整って準備されていれば良いのですが、そんなことは稀です。

  • 元データが紙書類で存在する
  • いくつかの書類やデータを組み合わせないと、必要な情報が揃わない
    (他部署からデータをもらうのに社内調整が必要になるなど・・泣)
  • セルが結合されている
    (そろそろマイクロソフトさんはセル結合機能をなくすべきでは?)
  • セルに直接単位やカンマ区切りが入力されている
    (見た目の書式と実データは分けて考えるべきなんですが・・)

このようにデータ分析以前の「前処理」に膨大な時間が掛かります。
そしてこの前処理は、データ分析が好きな人・得意な人であっても「全くモチベーションが上がらない作業」であることを知っておいた方が良いでしょう。

義務教育で教えるべきとは思いませんが、社員研修の1コマくらいは割いても良いネタだと思っています。
なので、会社のエライ人はお気楽に「こんな感じのレポートつくってくれる?」と依頼するのは止めましょう。本当に必要なら良いのですが。

業務効率の良い会社はデータの前処理が無い

業務効率の良い会社は、最初からレポートを作成する(=データを分析する)イメージを持って業務プロセスを組み立てています。

したがって社内で業務をするために紙書類はありませんし、データは全社員が必要な権限に応じてデータを参照・入力できるようになっています。
わざわざ部署を超えて調整して、データを集めるような愚はおかさないのです。

さて、この例(トライアスロンのレースデータ分析)では、ネット上にPDFのレース結果が載っていますので、それをダウンロードしました。
その後、こちらのサービスでPDFデータをExcelに変換しています。

セルの位置がズレたりすることがあるので、多少の前処理は必要でした。
上位10人までを表示すると、このようなデータになりました。
(元データはネットに公開されているのですが、念のため氏名はぼやかしました)

実際には完走者383人と、完走できなかった人のデータも含まれています。
このデータを元に、ヒストグラムを作成します。

階級を決める(多すぎず、少なすぎず)

次に階級を決めます。
データ幅は、一般的なヒストグラムでは同じにすることが多いです。
一方で、例えば総務省 家計調査のように、幅が変わっているヒストグラムもあります。

200万円から1,000万円までは、100万円間隔となっていますが、それ以上では250万円間隔になっています。さらに200万円未満と、1,500万円以上も別扱いです。

このような特殊な例を除いて、ビジネスでは等間隔にすることが多いのではないでしょうか。(特にヒストグラムで全体の傾向を探るくらいのときは)

階級の数は多すぎても少なすぎても、データが見難くなってしまいます。
階級が10個前後(5~15個くらい)に分割するのが良いと感じています。

このトライアスロンデータの例では、最も速い(タイムが小さい)人で2時間弱、最も遅い(タイムが大きい)人で4時間弱です。
1時間50分から4時間まで、10分間隔で階級を区切ってみました。
こんな感じです。

(度数:人数は、この後に求め方をお伝えします)

各階級の度数(データ数)を求める

ここまでは、ほぼエクセルの知識を使わずにできたと思います。
度数(データ数)を求めるのに、エクセル関数を使います。
1つ1つ手で数えていたら、日が暮れますよね。。

データ数を数える関数として、ここでは「COUNTIFS」を使いました。
指定された範囲に含まれるセルのうち、(複数の)検索条件に一致するセルの個数を数えることができます。

COUNTIFSの解説はさておき、具体例を見てみましょう。

ここでは、赤線で囲った「2時間10分以上・2時間20分未満」の人数(データ数)を求めます。COUNTIFSは複数の条件を同時に満たすセルの数を出してくれます。

=COUNTIFS(G$2:G$384,”>=”&U3, G$2:G$384, “<“&U4)

パッと見、ややこしそうに見えますが、分解すると大したことありません。
2つの条件「2時間10分以上」と「2時間20分未満」が記載されています。

G$2:G$384,”>=”&U3

は、G2からG384セルの間から、U3(2時間10分)以上を探すということです。
G2からG384は、完走した選手全員の総合記録が書かれています。
U3は、階級を定義したときに入力した、1つ上の階級を示すセルです。

G$2:G$384,”>=2:10:00″

という書き方もできるのですが、全部手入力していたら面倒ですので、他のセルを参照しています。もう一方の「2時間20分未満」も同じです。
一番最初のV2セル「2時間未満」など一部は、手入力しています。

なお、ヒストグラム作成時の注意点ですが、階級の境目のデータを2重カウントしないようにする必要があります。
この例ですと、2時間20分を「未満」ではなく「以下」にしてしまうと、ピッタリ2時間20分の人が次の階級でもカウントされてしまいます。

このようなミスを避けるため、全ての度数を算出した後で、合計数を確認すると良いでしょう。この例では、合計数が完走者数と同じ383になることを確認しています。

棒グラフでヒストグラムを作成する

ここまで出来たら、後は棒グラフを挿入するだけです。
階級と度数(データ数)の表を選択して、「挿入」から棒グラフを選択します。
(それぞれ、赤線で囲ったところです)

グラフが挿入されるので、タイトルなどを編集して完成です。

ビジネスではザックリと概要(傾向)を掴むことを求められることが多いように感じます。
このようにヒストグラムを活用することで、大まかな傾向を理解できますので、使ってみて下さい。

まとめ
  • データ分析をする前の「前処理」が本当に大変
  • 各階級の度数(データ数)を求めるには、COUNTIFS関数を使う
  • ヒストグラムで全体の傾向を理解できる



【編集後記】
昨日は鎌倉の山を23kmほど走ってきました。
さすがに筋肉痛とだるさが出ているので、スタンディングデスクで立ちっぱなしはツラいです。
ちょいちょい座ってます(笑)


メルマガ『経営は100種競技!』を毎日配信しています。
マーケティングやITを身につけたい。
ビジネスを楽しみたい。
変化・成長したいというビジネスパーソンにお読みいただいています。

渋屋 隆一
プロフィール
マーケティングとIT、そしてデータを使った「売れ続ける仕組みづくり」「業務改善」が得意。コンサルティングや研修・セミナーで中小企業の経営支援をしています。元IT企業でエンジニア→マーケティング。中小企業診断士。
\ Follow me /