グラフ(Excel)

Excelを使ってヒストグラムを作る(Excel2016以降対応)

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。

エクセル歴20年、アクセス歴10年ですが、ブログは35記事投稿した程度です。ブログの書き方も少しずつ慣れてきましたので、これからは品質も意識していきます。


前回はExcelで散布図を作る方法について触れました。今回はヒストグラムです。

ヒストグラムは棒グラフのうちの一つですが、通常の棒グラフとは違う、特殊な目的で使われます。

この記事を読むことで、

  • ヒストグラムがどのような場合に使えば便利なのか分かる
  • ヒストグラムの作り方とデータ集計の方法が分かる

といった効果を期待できます。

ヒストグラムを使う目的

ヒストグラムは、横軸に階層、縦軸にデータ数をとった棒グラフで、グループ内の分布やバラツキを確認するために使われます。

ヒストグラムはこのような場面でよく使われます。

  • 身長の分布を確認する
  • 工業製品のバラツキを把握する
  • テストの点数分布を確認する

他にも、この世界で起こる自然現象のほとんどはヒストグラムで表すことができます

また、ヒストグラムは、問題解決でよく使われる「QC7つ道具」の一つであることも知られています。

このように、様々な場面でヒストグラムを活用できますので、Excelでヒストグラムを作れるようになると、様々な現象の実態をつかむのにとても役にたちます。

ヒストグラムの作り方

ヒストグラムの作り方は、生データから直接作る方法と集計後にグラフを作る方法の2種類があります。

生データから直接作る

Excel2016から、「統計グラフ」を作る機能が使えるようになりました。この統計グラフメニューからヒストグラムを作ることが出来ます。

集計されていない生データから直接グラフを作ることが出来ますので、自分だけでデータの分析を行うことに向いています。階層の調整も容易に行うことができます。

但し、生データからヒストグラムを作った場合、系列名が分かりづらいという欠点があります。

集計後にグラフを作る

データを集計して作る方法は、セルに入力した文字を系列名に設定できます。

他人に伝えるためのグラフを作るためには、集計後のデータからヒストグラムを作ってあげるとよいでしょう。

Excel2013以前をご使用の方は、統計グラフを使う機能がありませんので、棒グラフからヒストグラムを作ることになります。

  1. 生データから「試作型」のヒストグラムを作り、仕様を決める
  2. 仕様が決まったら、集計後のデータから「他人に伝える」ヒストグラムを作る

以上の順番に沿えば、効果的にヒストグラムを作ることができます。

生データから直接ヒストグラムを作る

実際に日別の平均気温からヒストグラムを作成してみます。

気象庁のホームページで、過去の気象データをダウンロードすることが出来ます。

期間も細かく指定できるようです。

気象庁|過去の気象データ・ダウンロード

気象庁のホームページ

ここでは、名古屋市の一年間の平均気温データをサンプリングしています。

名古屋の平均気温データ

範囲を選択し、グラフメニューの統計グラフを選択します。

統計グラフの挿入

ヒストグラムをクリックすると、自動でグラフが作成されました。

統計グラフからヒストグラムを作成

系列の書式設定で、ピンの範囲設定で系列(バー)の数を変えることが出来ます。初期設定では4に設定されています。

範囲を設定

ピンを2に設定して系列を増やしてみました。

範囲を小さくした

7に設定して系列を減らしてみました。

範囲を大きくした

作成されたヒストグラムについて分析してみます。

「正規分布になっているのではないか」と予想してヒストグラムを作ってみたのですが、実際はふた山型のヒストグラムになったようです。

5.8~9.8℃付近と21.8~25.8℃付近の2つの温度帯については、日数が多くなっていることが分かります。

2つの山が表示された
  • 折れ線グラフなどで温度推移と対象日付の検索を行う
  • データ範囲を増やすか変えるなどして、サンプリング期間が原因かを調査
  • 地点を変更して名古屋近辺のみの現象であるのか確認

「なぜふた山型になったのか」という理由を調査するためにできることが、以上の3項目です。

実際に札幌と那覇についても同じように調査してみました。

札幌は温度差とヒストグラムの高低差が大きくなりました。

札幌のデータ

一方、那覇は逆に温度差と高低差が小さくなりました。

那覇のデータ

ここから、北の地方ほどふた山の傾向が大きくなるのではないかという仮説が立てられることになります。

統計グラフから直接ヒストグラムの作ると、生データからすぐに結果が分かりますが、項目名がわかりづらくなります。

ヒストグラムを作るためにデータを集計する

サンプリングしたデータを階層別に集計する方法は4つあります。

  • FREQUENCY関数
  • SUMPRODUCT関数
  • COUNTIFS関数
  • ピボットテーブル

FREQUENCY関数

Excel2010以前は、主にFREQUENCY関数が使われていました。

生データの範囲と全階層の上限値を指定します。

FREQUENCY関数

Microsoft365はスピルの対象関数のようですので、1個目のセルに数式を入れたら全ての階層に反映されるようです。

スピルとして処理

SUMPRODUCT関数

また、SUMPRODUCT関数でも同じような計算を行えます。

SUMPRODUCT関数

SUMPRODUCT関数については過去に記事を投稿していますので、よかったらご覧ください。

COUNTIFS関数

Excel 2013以降はCOUNTIFS関数でも対応出来ます。

SOUNTIFS関数

不等号を使う場合は、前後を「”>=”」などのようにダブルクォーテーションで囲む必要がありますので、注意してください。

ピボットテーブル

また、ピボットテーブルを使っても楽に集計することが出来ます。

Excel2013以前を使用していれば、ピボットテーブルで試作する方法でもよいでしょう。

注意すべきところは、範囲を設定した結果が、FREQUENCY関数を使った場合と異なることです。

範囲の不等号の設定が違うものと思われますので、精度の高いデータが求められる場合は特に注意が必要です。

ピボットテーブル

ピボットテーブルについては、後日記事を掲載する予定にしています。

集計後データからヒストグラムを作る

棒グラフからヒストグラムを作る方法は、感覚的に分かり易くできます。

一方、統計グラフを使って集計後データからヒストグラムを作るためには、系列設定の変更が必要になります。

棒グラフからヒストグラムを作る

まずは、定番としてつかわれている棒グラフからヒストグラムを作る方法について紹介します。

元データを選択し、集合棒グラフを選択します。

集合縦棒グラフを作成

棒グラフが作成されましたが、ヒストグラムはかけ離れた状態です。

装飾前

系列の間隔を0%にします。

要素の間隔を調整

ヒストグラムっぽくなりましたが、系列の境界線が無いので、見た目がよくありません。

要素の間隔を調整した後

枠線を白に設定します。

線を白にする

境界線がはっきり表示されて、見た目も少し良くなりました。

線を白にした

更に塗りつぶし・軸・データラベルを設定し、見やすいグラフに仕上げました。

更に装飾した

また、項目名が長くなる場合は、縦棒グラフを横棒にすると見やすくなります。

統計グラフ機能から項目別にヒストグラムを作る

集計後のデータから統計グラフを使った場合、集計済みデータを生データと見なしてグラフを作るため、系列の並びがくずれてしまいます。

並びがくずれたヒストグラム

書式設定のビンを「分類項目別」に変更します。

ピンの設定

棒グラフから作るのと同じようなヒストグラムを作ることが出来ます。

ピンの設定後

まとめ

ヒストグラムを使うことで、様々なものや現象のバラツキを分かり易く表現することができます。

Excelを使えばヒストグラムを簡単に作ることができます。

特に、最近のバージョンでは統計グラフを活用することでデータを集計する手間もかかりません。

他人に伝える資料を作る前に、一度生データから直接作り確認することで、より的を得たヒストグラムを効率よく作ることが出来ます。


次回は、パレート図について記事を掲載する予定です。

最後まで読んで頂き、ありがとうございました。

スポンサーリンク

-グラフ(Excel)