わえなび ワード&エクセル問題集

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel】ピボットテーブルで度数分布表(ヒストグラム)を作成する方法

たくさんの物を整理するとき「グループに分ける」という方法があります。

数値データの場合、一定の間隔で区切ってグループにしてまとめます。このようなグループを日本語では階級といい、まとめた表のことを度数分布表といいます。度数分布表を作れば、グラフ(ヒストグラム)にするのはボタンを押すだけです。

f:id:waenavi:20181023142907j:plain

そこで、今回は、ピボットテーブル(Pivot Table)を用いて度数分布表を作る練習をしましょう。

目次

1.準備

ピボットテーブルの基本的な操作方法について、詳しくは、ファンダメンタルExcel Program7-21~7-27の動画をご覧ください。

また、わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお取りください。7-28,7-28-2,7-28-3の「サンプルファイル(ZIP)」です。

 

2.グループ(階級)の設定と解除

問題

次のリストで、100人の受験生が午前・午後合わせて200点満点の試験を受けた。

f:id:waenavi:20181023134720j:plain


(1)次の図のように10点刻みの度数分布表を作成しなさい。また、120点以上を合格とするとき、合格者が何人いるか確認しなさい。
(2)設問(1)で設定したグループを解除して、繰り返し練習しなさい。

f:id:waenavi:20181023134752j:plain

 

解説

(1)グループを設定する

ピボットテーブルを挿入します。

f:id:waenavi:20181023134758j:plain

f:id:waenavi:20181023134801j:plain

f:id:waenavi:20181023134803j:plain


合計を行のフィールドにドラッグします。受験番号を値のフィールドにドラッグします。

f:id:waenavi:20181023134806j:plain


得点と人数の表になります。

f:id:waenavi:20181023134810j:plain

 

度数分布はデータの個数をカウントすることを言いますが、とくに、数量の範囲を等間隔に区切ってカウントする方法があります。

f:id:waenavi:20181023142904j:plain

 

点数の部分を一度クリックします。

f:id:waenavi:20181023142919j:plain

 

グループフィールド(フィールドのグループ化)を選びます。

f:id:waenavi:20181023142923j:plain

 

正しい場所をクリックしていないとグループ化のボタンが使えないことに注意します。

f:id:waenavi:20181023142955j:plain 


先頭の値に0、末尾の値に200、単位を10とします。

f:id:waenavi:20181023142925j:plain


このときチェックが外れます。

f:id:waenavi:20181023142928j:plain


これで得点が10点刻みになります。例えば、120点台は7人であることがわかります。

f:id:waenavi:20181023142932j:plain

 

度数分布表を作るときには、フィールドのグループ化で、最小値と最大値、区切るときの間隔をそれぞれ入力します。

f:id:waenavi:20181023142912j:plain


なお、「グループフィールド」のボタンは、バージョンによっては「フィールドのグループ化」と表示されることがあります。

f:id:waenavi:20181023142915j:plain

 

下向きにドラッグして範囲選択します。

f:id:waenavi:20181023142935j:plain


オートカルク機能によって、画面の右下に「合計:32」と表示されます。合格者は32人です。

f:id:waenavi:20181023142939j:plain

  

(2)グループを解除する

点数の部分をクリックします。

f:id:waenavi:20181023142944j:plain


グループ解除のボタンを押します。10点刻みの解除になります。

f:id:waenavi:20181023142948j:plain


度数分布表を作成するには、行のフィールドへドラッグしてピボットテーブルを作る作業と、そのフィールドをグループ化する作業が必要です。

f:id:waenavi:20181023142950j:plain


グループ化を解除するとグループ化する前の状態に戻ります。

f:id:waenavi:20181023142953j:plain

 

今度は20点刻みにしてみましょう。グループ化のボタンをクリックします。先頭の値に0、末尾の値に200、単位を20とします。
f:id:waenavi:20181023142957j:plain


これで完成です。

f:id:waenavi:20181023143926j:plain

 

3.クロス集計の場合

問題

次のリストで、100人の受験生が午前・午後合わせて200点満点の試験を受けた。

f:id:waenavi:20181023134720j:plain


縦に午前試験、横に午後試験の点数を並べて、10点刻みで次の表を作りなさい。

f:id:waenavi:20181023134756j:plain

 

解説

午前試験と午後試験の相関関係を見るときに、縦横に並べて表を作ることがあります。これをクロス集計といいます。

f:id:waenavi:20181023144325j:plain

f:id:waenavi:20181023144725j:plain

 

これでは見にくいので10点刻みにします。まずは縦の数値をグループ化します。グループ化のボタンを押します。

f:id:waenavi:20181023144728j:plain

 

先頭の値に0、末尾の値に100、単位を10とします。

f:id:waenavi:20181023144731j:plain

 

同じように横の数値もグループ化します。

f:id:waenavi:20181023144733j:plain

 

先頭の値に0、末尾の値に100、単位を10とします。

f:id:waenavi:20181023144738j:plain

 

これで完成です。

f:id:waenavi:20181023144742j:plain

 

4.ピボットグラフのボタンを押すだけ

問題

次のようなリストがある。

f:id:waenavi:20181023145321j:plain


次のグラフを作りなさい。

f:id:waenavi:20181023145322j:plain


解説

ピボットテーブルをもとにして作るグラフを「ピボットグラフ Pivot Graph」といいます。ピボットテーブルで度数分布表を作った場合、それをもとにして作るグラフ(ヒストグラム)もピボットグラフの一種です。

f:id:waenavi:20181023145458j:plain


リスト形式の表は、いったんピボットテーブルで集計した後で、集計した表をもとにピボットグラフを作ります。

f:id:waenavi:20181023214749j:plain

 

ピボットテーブルを挿入します。行を打率にします。値を氏名にします。

f:id:waenavi:20181023150744j:plain

 

ピボットテーブルツールの中にピボットグラフのボタンがあります。縦棒グラフにします。

f:id:waenavi:20181023150756j:plain


縦棒グラフ(ピボットグラフ)が挿入されます。さきほどの問題と同じように、フィールドのグループ化で、0.2から0.4で0.02ごとのグループにします。

f:id:waenavi:20181023150747j:plain

f:id:waenavi:20181023150749j:plain


打率ごとの人数、ヒストグラムになります。これで完成です。

f:id:waenavi:20181023150752j:plain

 

ピボットテーブルが変わると、連動してピボットグラフも変わります。

f:id:waenavi:20181023150801j:plain

 

5.日付のグループ化

問題

2050年1月から2051年12月まで日付と売上金額が入力されている。月ごとの合計売上金額を求めなさい。

f:id:waenavi:20181023154346j:plain

 

解説

ピボットテーブルを挿入します。

f:id:waenavi:20181023154348j:plain


日付を行のフィールドにドラッグします。金額を値のフィールドにドラッグします。

f:id:waenavi:20181023154351j:plain


フィールドのグループ化をします。

f:id:waenavi:20181023154355j:plain

 

行のフィールドとして日付が入力され、しかも、日付の表示形式になっている場合、グループ化の画面も日付を指定する画面になります。

f:id:waenavi:20181023154358j:plain

 

元の表で日付の範囲を確認して、開始日と終了日を2050年1月1日から2051年12月31日までとします。単位は月を選びます。

f:id:waenavi:20181023154401j:plain 


これでそれぞれの月の合計を求めることができますが、たとえば、1月は2050年1月と2051年1月を合わせた合計となります。

f:id:waenavi:20181023154404j:plain


これを分けたい場合はグループ化の画面で年と月を同時に選べばよいです。

f:id:waenavi:20181023154406j:plain

 

これで完成です。

f:id:waenavi:20181023154411j:plain

 

6.データのないアイテムを表示する

問題

次のリストで10点刻みの度数分布表を作りなさい。

f:id:waenavi:20181023160522j:plain

 

解説

ピボットテーブルを挿入します。

f:id:waenavi:20181023160524j:plain


得点を行のフィールドにドラッグします。受験番号を値のフィールドにドラッグします。

f:id:waenavi:20181023160526j:plain

 

フィールドのグループ化を選びます。先頭の値に0、末尾の値に100、単位を10とします。

f:id:waenavi:20181023160528j:plain

 

50点台と70点台の人がいないことがわかります。

f:id:waenavi:20181023160531j:plain


そこで得点のフィールドの設定を押します。

f:id:waenavi:20181023160535j:plain


レイアウトと印刷の中から、データのないアイテムを表示する、にチェックを入れます。

f:id:waenavi:20181023160538j:plain

 

50点台と70点台が表示されます(データの個数は空白)。

f:id:waenavi:20181023160540j:plain


さらに、オプションで、空白セルに表示する値に0を入れます。

f:id:waenavi:20181023161336j:plain

f:id:waenavi:20181023161339j:plain


これで完成です。

f:id:waenavi:20181023161342j:plain

 

データの個数が0の場合は、フィールドの設定でデータのないアイテムを表示して、空白セルを0にします。

f:id:waenavi:20181023161344j:plain

f:id:waenavi:20181023161346j:plain

  

7.2列の度数分布

問題

次のリストで、100人の受験生が午前・午後合わせて200点満点の試験を受けた。

f:id:waenavi:20181023134720j:plain

 

次のような表を得るには、どうすればよいか述べなさい。

f:id:waenavi:20181023161833j:plain

 

解説

ピボットテーブルを挿入します。

f:id:waenavi:20181023163115j:plain 

 

まずは、午前試験の列について考えます。

f:id:waenavi:20181023163119j:plain 

 

10点刻みで度数分布表を作成します。

f:id:waenavi:20181023163122j:plain 

 

これをコピーして、別のシートに値だけ貼り付けます。

f:id:waenavi:20181023163124j:plain 

f:id:waenavi:20181023163127j:plain

 

午後試験の列と差し替えます。

f:id:waenavi:20181023163132j:plain 


10点刻みで度数分布表を作成します。

f:id:waenavi:20181023163134j:plain 


これをコピーして、別のシートに値だけ貼り付けます。

f:id:waenavi:20181023163138j:plain

f:id:waenavi:20181023163142j:plain

 

午前・午後を差し替えてコピーしたほうが早く作れます。

f:id:waenavi:20181023163145j:plain

 

別解

1つのピボットテーブルとして作成するには、もともとのリストの作り方を変える必要があります。クロス集計として、午前と午後を横に並べるためには、午前・午後を入力するためのフィールドがあるということです。

f:id:waenavi:20181023163149j:plain

 

つまり、このような表です。午前と午後の得点を縦に並べます。

f:id:waenavi:20181023163154j:plain


ピボットテーブルを挿入します。縦に得点、横に午前午後を並べます。

f:id:waenavi:20181023163156j:plain


10点刻みで度数分布表を作成します。

f:id:waenavi:20181023163201j:plain


これで完成です。

f:id:waenavi:20181023163206j:plain

  


解説は以上です。


8.動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-28、7-28-2、7-28-3、9-35 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。

ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko

www.youtube.com


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]