BDAstyle

ビジネスデータ分析ツールの作成 with Excel

ヒストグラムの作成 with Excel 2/4 [Frequency関数による方法]

Frequency関数による方法

特に向いている利用シーン

  • アドインの「分析ツール」が利用できない環境で,分析ツールと同じルールで作成したいとき
  • 関数を直接入力して数式を作る作業に慣れているユーザーがヒストグラムをつくるとき

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

前頁同様,スポーツクラブXの新会員の年齢データをもとにします。ただし,プロセスとしてはピボットテーブルにかわり,Frequency関数で度数分布表をつくる作業が主となります。

DL

FREQUENCY関数|元データ

Step 1最小値・最大値を確認する

最小値・最大値を確認します。具体的には下表の式で確認できます。

D2 =MIN( B2:B41 )
E2 =MAX( B2:B41 )
エクセル frequency

この場合,最小値は18,最大値は81であることがわかります。

エクセル frequency

Step 2階級幅を決める

階級幅を決定します。

先の最小値・最大値から,レンジ(範囲)は63であることがわかります。これをキリのよい数字―――仮に10ずつに均等に区切って階級をつくっていくとしたら,柱の数は10を超えることもなく済みそうです。

このサイズのデータをもとにヒストグラムをつくることを考えると,10を超えない柱の数は相応ではあるとは思うので,階級幅にはそのまま10を採用して手続きを進めます。

エクセル frequency

Step 3第1・第2段階 各表を準備

下図のように「階級を範囲で定義」表と「度数分布表」の見出しを準備します。

この頁で目的とするところのヒストグラムの描画段階を“最終段階”と呼んだとき,前者の表が第1段階,後者のそれが第2段階に相当するものです。

エクセル frequency

Step 4階級を定義する

ExcelFrequency関数を使ってヒストグラムをつくる場合の仕様として,度数のカウントは,最初の階級は「x1以下」で,それ以外の階級は「xi超える~xi+h以下h:階級幅)」とする条件下でおこなわれる点は留意が必要です。つまり,ここでの最小値の18を起点に10間隔で階級をつくると,値「18」のカウントは最初の階級で処理されることになります。これは他の階級と条件を同じとしない(18以上x以下)ので,場合によってはモヤモヤが残るところかもしれません。

したがって,気持ち的にもこのへんはスッキリしたいので,下方向にも若干の余裕を見て階級をつくっておこうと思います。ここで,この頁であつかう「年齢」といったデータの性格を考えると,表現のうえでも幸い10歳代,20歳代,30歳代,………とする区分を採ったほうが,なにぶん馴染むようにも感じられます。

これらの点をふまえて,最初の階級の「下」,すなわち階級の下限は10と設定することにしたいと思います。すなわち,最初の階級の「下」については,最小値を下回るキリのよい数字を充てるのがシンプルです(そのほか,たとえば=Floor.Math(最小値, 10)などの式で決めるなどしても)。

エクセル frequency

つづいて「上」については,原則としては先の「下」に「階級幅」を足し合わせ値をつくってやればよいわけですが,その場合,このケースに限っては都合の悪いことに「上」の値が20となってしまいます。つまり,Frequency関数の仕様からして10歳代,20歳代,30歳代,………とする区分には上手にハマらなくなってしまいます。

可視化を図る趣旨と照らして,ここで年齢は離散量ととらえられれば十分なので,上は19であれば狙った範囲で漏らさずキッカリとカウントも可能です。したがって,この列は次のように

  • 「下」+「階級幅」-1

で計算したいと思います。

E7 =D7+G2-1
エクセル frequency

2つ目の階級を定義します。

下表のとおり「下」「上」に式を填め,両者を下方向にコピーします。どの程度コピーするか,具体的には,「上」列に「最高」値以上の値がはじめて登場する行までとします(レンジをカバーできる)。

D8 =D7+$G$2
E8 =D8+$G$2-1
エクセル frequency

Step 5度数分布表を作成する(1)

後段のグラフで,横軸のラベルに表示する用途のデータを準備します。

これは先に決めたとおり,形式としては10歳代,20歳代,………という表記でもよいわけですが,例として示す上ではいささかアドホックで汎用性に欠けるかもしれません。したがって,ここでは年齢以外の離散データを扱うときでも対応可能な,範囲の表示に代えたいと思います。

具体的には,最初の階級には,

  • 10-19

と表示することとします。

こうしたテキストをつくるため,下図の彩色したセルに

G7 =D7 & "-" & E7

と入力し,これを「階級を範囲で定義」表の最下行と同じ行までコピーします。

エクセル frequency

Step 6度数分布表を作成する(2)

度数をカウントし,結果を「度数分布表」に表示します。

具体的には,「度数分布表」のラベルの数と同じだけ,「度数」列の領域を選択し―――

H7:H14

―――選択を維持したまま,Frequency関数の入力をはじめます。Frequency関数は引数として,次のデータ(ストリクトには配列)を要求します。

=frequency(元データ,各階級の上限※)

※「各階級の上限」に関しては,最後の階級を除外して指定します。これはFrequency関数の仕様です(cf. Frequency関数 ―"Office")。

引数の指すところの具体的なセル範囲は,この頁の例で示せば下図のようになります。

元データ:B2:B41, 各階級の上限:E7:E13

この関数の結果は配列(先に選択した範囲すべてに出力)として戻ります。ゆえに,入力時に配列数式である旨指示する必要があります。具体的には,関数のすべての引数ともタイプを終えたら,Ctrl + Shift + Enter で数式を確定させる作業が必要です

―――とまれ,Frequency関数によって度数は下図のとおり求めることができました。

ここで作業ミスがないかどうかの確認のため,総度数(度数の合計)とデータのサイズ(ここでは,人の数)が一致するかを,ステータスバーの情報などで確認しておくとよいかもしれません。

エクセル frequency

Step 7棒グラフでヒストグラムを描画する

つづいて目的としたヒストグラムを描画します。

これはExcel標準の組み込みグラフ「棒グラフ」を素地としてつくります。

「度数分布表」,下図の領域を選択し―――

エクセル frequency

―――挿入タブから集合縦棒グラフをシート上に挿入します。

エクセル frequency

これにより,下図のようなグラフが描かれます。

エクセル frequency

もっとも,この時点ではヒストグラムとひと目でわかるような外形的な特徴を揃えていません。

たとえば,柱を無間隔で並べることはその最たる要素のひとつかと思います。この処理の具体的な解説が必要な場合は,前頁を参照ください(当該箇所から表示されます)。

Step 8その他設定・ヒストグラムの完成

その他任意の書式の設定を適宜重ねて,ヒストグラムの完成です。

frequency関数でつくった度数分布表をもとにしたヒストグラムの完成

Step 9Tips) 連続量をあつかう場合

この場合,ラベルの表記(もちろん実際の階級上限の設定についても適切におこなっていることを前提として)を,たとえば下図のようにあらわすか,

ヒストグラム・ラベルの表記の例1

あるいはグラフを見る人に多少の説明が必要になるケースも出るかもしれませんが,Excel 2016の新グラフのように数学記号であらわす等々の手段をとれば,連続量でも対応は可能です。

ヒストグラム・ラベルの表記の例2

しかし判読の手間,言い換えればどこかストレートに目に入ってこない感は,やはり否定しがたいものがあります。それゆえグラフの読みやすさに注力できるリソースがあるならば,連続量をあつかう場合はことさら,ラベルを階級の直下でなくて,境界に表示する方式で処理していきたいところかと筆者自身は思います。

cf.

Next

次頁は「CountIf関数」または「CountIfs関数」でのつくりかたです。

その他の参照