BDAstyle

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

ヒストグラムの作成 with Excel 1/4 [ピボットテーブルによる方法]

ここでは,Excelアドインの「分析ツール」以外のヒストグラムのいろいろな作成方法について触れていきます。最初にピボットテーブルによる方法を,次にFrequency関数による方法 およびCountif関数による方法を,そしてさらにはExcel 2016新機能による描画の方法について,順に説明を加えたいと思います。

ヒストグラムのさまざまなつくりかた

ピボットテーブルによる方法

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

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

先週,スポーツクラブXは特定の年齢層向けに加入料を差別化したキャンペーンをおこないました。キャンペーンを終えてみれば,たしかに40人の新規加入会員数を獲得することはできたのですが,Xは一度この企画が特定の年齢層にきっちりとリーチできたのかを振り返ってみたいと考えました。そこで,加入時に登録してもらった生年月日をもとに新会員の年齢を計算したところ,下の表のようになりました。

以下,この表からピボットテーブルを使ってヒストグラムを作成します。

DL

FREQUENCY関数|元データ

Step 1最低値・最高値の確認

データをざっと眺めておくか,あるいはシートの任意の場所でMin, Max関数によって最小値・最大値を確認しておきます。後者の場合,下のように入力すると最小値は18,最大値は81であることがわかります。

したがってここでは最初の階級を「10-19」,最後の階級を「80-89」とし,計8個の階級でヒストグラムをつくろうと思います。

最低・最高の確認

Step 2ピボットテーブルの準備(1)

データ領域の任意のセルをアクティブにし,挿入タブ「テーブル」グループのピボットテーブルボタンをクリックします。

Step 3ピボットテーブルの準備(2)

ピボットテーブルをどこにつくるか,場所について指示します。ここでは同じシートの上に差し込む方向で進めたいと思います。

―――ということで,下のように表の右方の未使用のセルを指定してから,[OK]ボタンをクリックします。

同じシートの中にピボットテーブルをつくる

Step 4ピボットテーブルの設計(1)

この例で集計(この場合,度数を求めること)の対象となるのは,いわずもがな「年齢」です。画面の右方にあらわれた「ピボットテーブルのフィールド」ウインドウで,この役目を担うのは「値」枠です。ゆえに「年齢」を「値」に填める必要があります。

具体的には,リストから年齢をドラッグし,でドロップします。

scrollable

「年齢」を[値]にドラッグ&ドロップ

Step 5ピボットテーブルの設計(2)

次に「年齢」の集計をどのようなルールを充てておこなうか(この場合,階級をつくること)を指示してやります。

具体的には,ふたたびリストから年齢をドラッグし,でドロップします。

scrollable

「年齢」を[行]にドラッグ&ドロップ

Step 6修正作業|合計をカウントに(1)

さて,この時点でピボット表がどのような状態となっているかを確認してみます。

頭の中で描いてきたのは度数分布表のはずでしたが―――たとえばこの表の23歳のところを見てみると,このすぐ右のセルは46人といった,元データのサイズと照らして奇異な数字が出てきました。

行ラベル:23, 合計/年齢:46

それもそのはず,「値」に填めた「年齢」フィールドについてについて確認してみると,“合計”となっています。

値:「合計/年齢」

欲しいのは度数ですので,「23歳と23歳を足して何歳か」―――じゃなくて,「23歳が何人なのか」です。

Step 7修正作業|合計をカウントに(2)

―――ということで,足し算ではなくこれをカウントするように修正します。

具体的には,「合計/年齢」列の任意のセルをアクティブにし,分析タブ「アクティブなフィールド」グループのフィールドの設定ボタンをクリックして,

「値フィールドの設定」ダイアログを呼び出します。これを使って,集計方法を個数に変更しておきます。

再度ピボット表を確認すると,カウントが機能していることがわかります。

23歳が→2人

Step 8修正作業|グルーピング(1)

しかし,結果としてはこうした階級幅の非常に狭いヒストグラムが欲しいわけではありません。先に決めたとおり,幅には幾らかの広がり(範囲)が必要です。

「行ラベル」列の任意のセルをアクティブにします。その後,分析タブ「グループ」グループのフィールドのグループ化ボタンをクリックします。

scrollable

Step 9修正作業|グルーピング(2)

「グループ化」ダイアログが表示されます。

Step 1では,10-19, 20-29, …といったように10歳ごとに階級を作成することを決めています。これは,ダイアログのボックスに下図のように入力することで実現します。

10歳から90歳まで,10区切りで階級をつくる

Step 10作図ミスの回避策|階級抜けのチェックとそれへの対応

表が出来上がったところで,階級の抜けがないかを確認します。ここにいう抜けというのは,ラベルの連続性が切れているところ―――たとえばSAMPLE 1では階級40-49が,SAMPLE 2では階級60-69および70-79を指して言います。

ピボット表でのカウントは,横着に言えば“ないものは拾わない”主義なので,外れ値を考慮しなきゃならない事情と直面するユーザーの現実からしたら,この確認作業は怠ることができないものとなります。幸いにも(?),もし抜けがないことを確認できたなら,ここをすっとばして次のStep 11へジャンプしてもかまいません。

階級の抜けがあるピボット表の2つのサンプル

下図上段は「抜けがあるまま作成したヒストグラム」,下段は「抜けを解決してから作成したヒストグラム」です。

ヒストグラムはここで扱う年齢のような変数の,真の分布を映す鏡としての役割を担い―――というか,そうでなければヒストグラムをつくる意味がありません。したがって,下図下段のように度数0の階級が省略されずにきちんと表示されるよう,重ねて設定を加えます。

抜けがあると分布の実態を見誤る!

ということで具体的に,ピボット表の任意の階級(「行ラベル」)をアクティブにした状態で分析タブ「アクティブなフィールド」グループのフィールドの設定ボタンをクリックし,

scrollable

「フィールドの設定」ダイアログの レイアウトと印刷タブデータのないアイテムを表示する項にチェックを入れ[OK]を返します。

この時点でピボット表にこれまで拾われなかった階級が表示されていることを確認します。もっとも,あらたに表示された階級には度数(つまり0)が表示されてはいません(下表の彩色部分)。したがって,これを表示させるための手続きも必要です。具体的には,

抜けの部分は出てきたが,度数が表示されていない

「ピボットテーブル」グループの オプションボタンをクリックし,

「ピボットテーブル オプション」ダイアログの レイアウトと書式タブ「書式」グループの空白セルに表示する値0を入力し[OK]を返します。

―――以上の作業で,当該の階級に0が表示されたかと思います。

Step 11度数分布表の完成

度数分布表の完成です。

※目的が度数分布表をつくることそのものにある場合,表を他シート等にコピーして列見出し等に修正を加えます。たとえば「行ラベル」「階級」に,「個数/年齢」を「度数」に書き換えるなどの作業は最低限必要なところかと思います。

ピボットテーブルで作った度数分布表

Step 12ピボットグラフ|ヒストグラムを描画する(1)

ここからの工程はグラフ化,すなわちヒストグラム作成の工程です。

これまでに作成したピボット表の,任意のセルをアクティブにします。そして,分析タブ「ツール」グループのピボットグラフボタンをクリックします。

scrollable

Step 13ピボットグラフ|ヒストグラムを描画する(2)

「グラフの挿入」ダイアログが表示されます。「縦棒」群から集合縦棒を選択します。

Step 14ピボットグラフ|ヒストグラムを描画する(3)

ピボットグラフをつくることができました。ここで任意の縦棒をクリックし,度数(グラフの表記にしたがえば「集計」)系列を選択した状態にしておきます。

ヒストグラムに柱の間隔は不要

Step 15ピボットグラフ|ヒストグラムを描画する(4)

書式タブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。

scrollable

Step 16ピボットグラフ|ヒストグラムを描画する(5)

「データ系列の書式設定」ウインドウ・要素の間隔のスライダーを「0%」まで動かすか値を直接タイプするかして,柱の間隔を無くします。

Step 17ヒストグラムの完成

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

Excelによるピボットテーブルを使ったヒストグラムの作成・完了

Next

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

その他の参照