BDAstyle

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

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

ヒストグラムへのさまざまなアプローチ

ここでは,Excelアドインの「分析ツール」以外のヒストグラムのいろいろな作成方法について触れていきます。最初にピボットテーブルによる方法を,次にFrequency関数による方法 およびCountif関数による方法を,そしてさらには,横軸ラベルに境界値を適用する,工学・数学分野で利用されることの多いスタイルへの加工の方法について,順に解説していきます。

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

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

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

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

この表からピボットテーブルを使ってヒストグラムを作成します。このとき,階級の幅は10歳刻みとします。

初期データ

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

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

したがってここでは最初の階級を「10-19」,最後の階級を「80-89」とし,計8個の階級でヒストグラムを作成するものとします。

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

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

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

ピボットテーブルを作成する場所を指定します。ここでは同じシート上に作成することとします。したがって,下のように表右方の空いているセルを指定してから,[OK]ボタンをクリックします。

Step 4ピボットテーブルの操作(1)

画面右方,「ピボットテーブルのフィールドリスト」から「ID」(=集計の対象とするもの)をドラッグし,「Σ値」でドロップします。シート内では,「ID」の個数が合計されています。

Step 5ピボットテーブルの操作(2)

同じように,「ピボットテーブルのフィールドリスト」から「年齢」(=階級とするもの)をドラッグし,「行ラベル」でドロップします。シート内では,元データに該当する「年齢」ごとに「ID」の個数が合計されています。

Step 6度数分布表の作成(1)

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

Step 7度数分布表の作成(2)

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

先に見た通り,元データの最小値・最大値はそれぞれ18, 81でした。くわえて階級の幅は10という条件をつけました(すなわち10-19, 20-29, …といったように10歳ごとに階級を作成します)。したがってダイアログのテキストボックスには下図のように値を入力します。

すべて入力後,[OK]ボタンをクリックします。

Step 8度数分布表の作成(3)―度数0の階級がある(ありうる)場合の対応

ここで,元データの特性 あるいは階級幅のとり方によっては,“該当するデータがなかった”ことや“外れ値が発生した”ことにより,途中で度数が0の階級が生じることを考慮しなければならないこともあろうかと思います。というのも,たとえば下表A・Bのケースのように ピボットテーブルによる度数分布表には度数0の階級が表示されません。

これでは,下のヒストグラムのように分布の実態を見誤ってしまいかねないので,表示されていない度数0の階級があるようならば,これを表示されるよう設定をしていきます。

具体的には ピボット表の任意のラベルをアクティブにした状態でオプションタブ「アクティブなフィールド」グループのフィールドの設定ボタンをクリックし…

「フィールドの設定」ダイアログの レイアウトと印刷タブデータのないアイテムを表示する項にチェックを入れ[OK]ボタンをクリックします。この時点でピボット表に度数0の階級(項目名のみ)が表示されていることを確認します。

あらたに表示された階級には度数(つまり0)が表示されていません(下表の紫色の彩色部分)。したがって これを表示させる手続きを加えます。具体的には…

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

「ピボットテーブルオプション」ダイアログの レイアウトと書式タブ「書式」グループの空白セルに表示する値欄に0を入力し,該当する階級に0が表示されたことを確認します。

Step 9度数分布表の完成

度数分布表の完成です。

※目的が度数分布表の作成そのものにある場合,表を他シート等にコピーして列見出し等を修正します。たとえば「行ラベル」「階級」に,「データの個数/ID」を「度数」に書き換えたほうがbetterかもしれません。

Step 10ヒストグラムの作成(1)

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

作成したピボット表の,任意のセルをアクティブにします。その後,オプションタブ「ツール」グループのピボットグラフボタンをクリックします。

Step 11ヒストグラムの作成(2)

「グラフの挿入」ダイアログが表示されます。「縦棒」グループの集合縦棒を選択し,[OK]ボタンをクリックします。

Step 12ヒストグラムの作成(3)

ピボットグラフが作成されます。ここで任意の縦棒をクリックし,すべての系列を選択した状態にしておきます(下のようにすべての棒の周囲に選択マーカーが表示されます)。

Step 13ヒストグラムの作成(4)

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

Step 14ヒストグラムの作成(5)

「データ系列の書式設定」ダイアログが表示されます。

要素の間隔スライダーを「なし」まで動かした後,[閉じる]ボタンをクリックします。

Step 15ヒストグラムの完成

ヒストグラムの完成です。

Next

次頁は「Frequency関数」による作成手順です。

その他の参照