BDAstyle

Business Data Analysis & Visualization with Excel

ヒストグラムの作成 with Excel 4/4[ver.2016以降の新グラフ ―旧来のアドインによる方法との比較をふまえて]

アウトライン

Excelのversion 2016からあたらしく導入された組み込みグラフ「統計グラフ」の“ヒストグラム”機能を使ってヒストグラムを作成する方法です(ヒストグラムって何?的な話が必要な場合はまずメインサイトの解説へどうぞ)。

またかかる方法と従来の方法(ただしアドインを使用する方法のみ)とでどのような違いがみられるか,特徴的な要素に関してざっくりと眺めてみたいと思います。

組み込み新グラフで描くヒストグラム

Step 0シチュエーションの設定

この例示ではリサーチサービス社の個人別売上実績データを利用します。

DL

手順

Step 1データ範囲の選択

表の中の,ヒストグラムにしたいデータ範囲を選択します。

ここでは“売上”の分布を確かめたいので,具体的には下図の彩色部分の選択が必要になります。なお,ラベル(見出し)部分の選択の有無に関しては,あとから凡例を挿入する予定があれば選択に含めます。

ヒストグラム エクセル 2016 2019

Step 2統計グラフ「ヒストグラム」

挿入タブ「グラフ」グループの統計グラフの挿入ボタンをクリックします。

そのまま「ヒストグラム」グループからヒストグラムボタンを選択します。

ヒストグラム エクセル 2016 2019

Step 3ヒストグラムの完成

グラフが出力されます。

階級幅を決定するアルゴリズムは,「スコットの選択」(オフィシャルページの記述: スコットの正規化基準ルール“Scott’s normal reference rule”)が利用されています。

cf. ビンの個数と幅 「ヒストグラム」 ―"Wikipedia"

2016のあたらしい機能でつくられた「ヒストグラム」

さて新グラフの場合,横軸のラベルは数学記号を交えて描かれます。これは変更不可,つまり任意の描き方[直観的な「72-90」などの表現]に変更することはできません。多様な背景のユーザーがいるExcelでこの形式のラベルしか選択できないことは,“他者に見せるための”Figureとしての活用より,“自分のみが”データの外観を面倒な操作なしにつかむためのツールとしての在り方にウエイトが置かれただろうことを推察できます。

またその他特徴として,最初の(=第1の)階級のみ閉区間で,その他すべての階級は右閉半開区間での表示となっていることが挙げられます。

横軸のラベルは数学式表記―閉区間と半開区間

具体的には,これらは柱の順に18以上36以下, 3654以下, 5472以下, 7290以下 の階級幅を意味します。したがってストリクトに見れば最初の階級幅のみ不等間隔となることがわかります。

つまり重要なことに,この新グラフは「階級のとりはじめ=データの中の最小値」といったルールを採用しています。このルールもまた,ユーザー側での任意の変更が不可能です(hackは可能: 後述)。こうした仕様があるところに,“階級上限を含んでカウントする”旧来の度数まわりの仕様のようなものが重なって,“最初の階級幅のみ他とは異なる”ある意味変則的な階級幅の取り方に落ち着いたのかもしれません。

とはいえ,たとえば100点満点のテストの分布を10本の階級ではかる時などを好例として(0~9点, 10~19点といった階級の取り方では100点の扱いに困り,1~10点, 11~20点といった階級の取り方では0点の扱いに困る),こうした類の処理のしかたが実務まわりで散見されることに照らせば,ここにいう「不等間隔」という点について,一般的な利用シーンに限ればセンシティブである必要もないでしょう。

18≦x≦36,36<x≦54,54<x≦72,72<x≦90

設定

Step 1作業ウインドウの表示

新グラフは,各種の設定を加えることが可能です。ここでは固有機能(ヒストグラムを描画するにあたっての重要な要素)についてのみ見ていきます。

設定は,すべて作業ウインドウでおこないます。具体的には,作成したグラフの横軸を選択し,リボンの書式タブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。

ヒストグラム エクセル 2016 2019

「軸の書式設定」ウインドウが表示されたら,「軸のオプション」カテゴリが表示されていることを確認します。表示されていない場合,軸のオプションボタンをクリックして下図のような状態にしておきます。

(作業ウインドウ)ヒストグラムの設定項目いろいろ

Step 2bin幅の変更

※ 以下「ビン」は,ビルド16.0.6366.2056より前に,「ごみ箱」という名称が利用されていた時期があります。これより前のビルドを利用する環境では,名称を適宜読み換えてください。

「ビン」グループの設定項目は<自動>, <ビンの幅>, <ビンの数>から任意に排他的な指定ができます。なお,ここでの設定項目のひとつ,「分類項目別」について,筆者には良好な使い勝手をどうにも推察することが叶いません。他のツールと比べて優位とされるポイントが不明なため,ここでは扱わないものとします。

さて,この「ビン」では階級に関するいくつかの設定をおこなうことができます。

具体的に,第1として,「ビンの幅」つまり階級幅・区間の幅を任意の値に直接指定することが可能です。試しにこれを10と指定してみると

設定項目「ビン(階級, 区間)の幅」

グラフの階級幅が自動的にこの値に変更されることを確認できます。

区間の幅→10になった

Step 3binの数の変更

第2に,「ビンの数」つまり階級の数・区間の数を任意の値に直接指定することも可能です。試しにこれを5と指定してみると

設定項目「ビン(階級, 区間)の数」

グラフの階級の数が自動的にこの値でつくりなおされることを確認できます。

区間の数→5になった

なお,この設定値はあくまで目安ではなく,そのものズバリの値として扱われます。測定単位の整数倍で丸めるなどの処理を咬ませず,レンジ(範囲: 最大値-最小値)を「ビンの数」で除した値をそのまま階級幅に適用してきますので,除算の結果によっては下図のグラフのラベルのような,かなりの躊躇を覚えるアウトプットを得る可能性もあります。はて,これはちょっとということで,

区間の数設定:アンチパターン

「表示形式」から丸めちゃえばいいんじゃね?

区間の数設定:アンチパターン


「なるほどそうかも!」と勇んで手を加えてみると,

ヒストグラム エクセル 2016 2019

一見,上手くいったように見えるのですが,注意して見てみると,ここだけ階級幅が不等間隔になったらしいことに気づかされます。

ヒストグラム エクセル 2016 2019

「それでも中身のツジツマさえ合っててくれれば,ま,気にしない気にしない」と思いたいところですが,実際の表から51<x≦61に該当する人を抜き出してみると

ヒストグラム エクセル 2016 2019

となり,度数(7人)がグラフ上のそれ(8人)と一致しません。そこで,階級をすべて等間隔とする前提を据えた上で,先のグラフに示された階級の取り方でヒストグラムを再度作りなおしてみたいと思います。

結果実際のところは,元の設定による分布(左図)とは若干異なる,下・右図のような分布を得ることができます(統計処理系「R」で用意したもの)。と,まぁここまで引っ張っておいて実際のところはあらためて大げさに言及できるようなポイントでもないのですが,「表示形式」による丸めは,“見た目”に関しての調整機能にすぎず,データのツジツマにかかる部分には当然ながら無関心です。

ヒストグラム エクセル 2016 2019

すなわち,新グラフにて「表示形式」に手を加えることは,“グラフから読み取れる情報をもとに元データを処理すれば,だれがやっても同じ結果を導くことができる”再現可能性を失うおそれをはらみます。転じて,丸めにともなう階級値の“定義の違い”は,作成者以外の他者が元データを利用できない場合に,加重平均や標準偏差といったパラメータを計算する際の“結果の違い”となって現れます(※これについては一様な是非を言うものではありません)。

ヒストグラムの横軸の性質は,Excelの機能上でいう棒グラフのそれよりも,散布図のそれに近くなります。散布図の目盛の値を丸めるとなれば,多くの人が自然と違和感を覚えるかと思いますが,一見棒グラフのようにも見えるヒストグラムに対しては,不思議とそうした感覚も薄れがちです。

つまり,記述統計におけるデータの示すところの“忠実さ”を担保するなら,ヒストグラム機能に関する「表示形式」を使った丸めにも慎重であるべきでしょう。その意味では,この新機能でビンの数を指定する方法を常用するなら,面倒を避けるうえでも「ビンの幅」が指し示す値がいくらになっているかを都度注意していく必要があると考えます。

Step 4境界値以下・境界値超でのまとめ

さて,第3に,ビンに関する<オーバーフロー>, <アンダーフロー>の指定ができます。

これは文字通り一定の境界を設けてのフロー分,すなわちヒストグラムの左方ないし右方,またはその両方でスパンを切り分け,とにかく極端な値がどれだけあるか,カウントが分かればそれでいい手短にまとめればそんな場合に有効な機能だと言えそうです。

設定項目「ビンのオーバーフロー」「ビンのアンダーフロー」

たとえば57の度数をまとめてカウントしたいとして,<オーバーフロー>に直接57と入力すると

下図のようなグラフにかたちをかえます。このとき,残りの階級幅は再度自動で調整されます。

この設定は,ことばを変えれば不等間隔な階級幅を組み入れるのと同義ですから,手続的には本来はグラフの上でも整合をとならきゃならないハズです。実装上そうした処理が省かれていることをふまえれば,分布の確認という目的よりあくまで極端な値をカウントする目的の方に合致する機能と見るべきかもしれません。

オーバーフロー→57に設定した場合

なお,値を指定せずに<オーバーフロー>または<アンダーフロー>のチェックのみを入れると,前者は+3σ(平均+3標準偏差),後者は-3σ(平均-3標準偏差)を境界としてヒストグラムの再描画がおこなわれます。

最後に,設定不可能だった第1(最初の)階級のとりはじめ(下限)は,この<アンダーフロー>を使って無理押しする選択もあります。たとえばより自然な階級のとり方をしたいとして,(この例の場合の)既定の18でなく10からとりはじめたい場合,<アンダーフロー>を20※と指定したうえで幅を整えれば,下図のようにできないでもありません。

※18を10の倍数でCeiling。

アンダーフロー→20に設定した場合

この場合,Figureとして使うなら,上図にいう強調部分をテキストボックスなどで(10, 20]と上書きしたほうが混乱を生まないでしょう。

旧来の純正アドインとの比較

Step 12016, 2019新グラフ|分析ツール|ストアアドイン の各特徴

新グラフ「ヒストグラム」の特徴がある程度見えてきたところで,旧来からのアドインによる方法,すなわち

  • アドイン「分析ツール」
  • ストアアドイン“Histogram”

とともに,ヒストグラムを描画のうえで特徴づけるいくつかの骨格的な要素に関して一覧を作成すると,下表のような感じになるかと思います(筆者によるもの。主観的判断も含みます)。

2016新グラフ (ver. 16. 0. 4229. 1024) アドイン 「分析ツール」 ストアアドイン “Histogram” (ver. 4. 0)
度数分布表 作成されない 作成される 作成されない
再計算(再描画) ×
bin設定 幅アルゴリズム Scott※1 平方根※2※7 独自形式※3
最初の階級 最小値を階級下限に適用 最小値を階級上限に適用※7 -
任意の幅の指定 ×※8 ×
任意の数の指定 ×※8
「以上未満」でのカウント × ×※9 ×
不等間隔な階級幅 △※4 ×※10 ×
横軸設定 境界値の表示 × ×※11
ラベルの表記 最初の階級のみ閉区間, その他は半開区間 階級上限※12 境界
縦軸設定 相対度数の適用 × ×※12
柱と軸との分離※5 × × ×
付加要素 累積相対度数線の挿入 × ○※6 ×
垂線の挿入 × ×※13 ×
正規分布曲線の挿入 × ×※14 ×
  • ※1 ただし切り上げでなく四捨五入
  • ※2 ただしレンジ÷(切り捨て[平方根(n)])
  • ※3 最初の階級の下限と最後の階級の上限を切りのいい値で固定[自動計算]して,ユーザーが指定したbin数をもとに階級幅を決定
  • ※4 ±3σまたは任意の境界を設けてのカウントのみ○,相対面積での描画は×
  • ※5 特に階級値を表示する場合など,縦軸の0が最初の階級幅の一部と誤読されるのを防ぐ処置。あるいは,読みやすさを高めるための処置
  • ※6 軸の交点を始点としない簡易型
  • ※7 目的の方法で作成した「データ区間」を指定することで他の方法も実質的には適用可
  • ※8 「データ区間」の指定により任意の方法で可能
  • ※9 微小値を減じて「データ区間」を作成することで可能
  • ※10 カウントのみ○,相対面積での描画は×
  • ※11 たとえば,ダミーデータを使う方法(リンク先はこのサイトの別ページ)で実現可
  • ※12 度数分布表の当該箇所を書き換えて変更可能
  • ※13 たとえば,散布図を使って描画する方法(リンク先はこのサイトの別ページ)で実現可
  • ※14 たとえば,折れ線グラフ または 散布図を使って重ねる方法(リンク先はこのサイトの別ページ)で実現可

性格に照らした使い分けが必要

Step 2雑感

以下,上の比較をふまえての筆者の雑感です。

ひとつに,新グラフは従来の主要な3つの手法(アドイン・Frequency・Countif系)に包括・発展的にとってかわることを狙ったものでないことは何となく伝わります。とりわけ今バージョンの新グラフには,これまでの手法で弱かった部分,すなわち応答性というかレスポンシブな部分を補うことを担うものとしての役割を強く感じるところです。

ユーザーにわずらわしい準備を要求することなくダイレクトにアウトプットを返してくれる点については,好適な階級数を探るシミュレーションなど作図前のプリアプローチや,層別にデータを次々と観察していきたいときなど,十分にハマる感があります。

反面,細部に至るカスタマイズの自由さが必要な場合たとえば評価者に照らしたプレゼン資料を作成する際や,補足的な判断要素を複合させたい場合などには,度数分布表ありきの旧来のアプローチの方が適しています。総じて使い分けのできる選択肢が増えた,という意味ではver.2016以降のユーザーなら眠らせておく理由がない機能のひとつではあると思います。

作成にあたり参考にしたWebページ

Next

その他の参照