第6回 - 2013/10/31

基本的なグラフ作成


グラフ作成のポイント

おそらく、グラフについてはそれほど難しくはないと思うので、ポイントを示しておきます。

グラフに適したワークシート作成を心がける

ワークシートにただデータを入力すればいいというわけではありません。縦方向、横方向にそれぞれ何を並べるかということや、連続して入力するなどのポイントを忘れてはいけません。

範囲を選択して「挿入」のグラフで、あとは画面とおり

対話的に行えばOKですが、「系列」についての理解は必要です。あとは見えるとおりです。

細かい修正は、パーツの選択と、右ボタンで

軸だけを変更したい場合は、軸をクリックして選択する必要があります。うまくクリックしますが、場合によってはツールバーの機能を使って選択をすればいいでしょう。また、たいがいの変更は右ボタンでパーツをクリックして、「○○の書式設定」を選んでダイアログボックスを使えばできます。

ヒストグラムの作成

ある一定の範囲内にあるデータの個数を求めた表やグラフをヒストグラムといいます。

たとえば、20人の身長のデータが以下のようにあるとします。コピー&ペーストして、ワークシートに持ち込みます。

144
161
143
169
158
145
140
165
149
128
160
148
142
148
148
159
154
148
162
151

さて、ヒストグラムを作りたいわけですが、1つの方法はCOUNTIF関数を使うことです。

一発で求めることはできなくはありませんが、ここで工夫が必要です。COUNTIF関数を単純に使うわけではありません。ここで、相対参照や絶対参照のことをしっていなければなりません。実際にやってみます。

また、実際にグラフまで作成します。

FREQUENCY関数を使った方法

別の方法としては、FREQUENCY関数を使うことです。実際にやってみますが、範囲の指定方法や「配列」として式を入力するなど、ちょっと難しいです。また、値を求める規則についても、注意が必要です。

配列については以下にまとめておきますが、文字で説明するより実際にやってみないと分かりにくい機能です。

もっと簡単にヒストグラムを作る方法

データ分析ツールを使います。「データ」タブの「分析」にボタンがあります。

実際の作業は次のように行います。

  1. 「分析ツール」ボタンをクリックする。
  2. ダイアログボックスで「ヒストグラム」を選択する。
  3. ダイアログボックスが出てくるので、各種設定を行う。ポイントは講義で説明します。
  4. 簡単に表やグラフができました。

分析ツールを使うためには、以下の準備が必要です。

  1. 「ファイル」タブをクリックし、左側にある「Excelのオプション」をクリックします。
  2. 「Excelのオプション」ウインドウの左側で「アドイン」をクリックして選択します。
  3. 右側のパネルの下にある「管理」で「Excelアドイン」が選択されていることを確認して、「設定」ボタンをクリックします。
  4. 「アドイン」ウインドウが表示されるので、「分析ツール」にチェックを入れて、OKをクリックします。


本日の演習

第3回の課題でダウンロードしたデータをもとにして、以下の練習問題をやってみてください。その前に必要なデータ処理があれば、やっておくこと。

ヒント:データ処理した結果を、別のワークシートにコピーして、グラフを作成しやすいような表形式のデータに整えるとやりやすいかもしれません。

できれば、グラフの色やマーキングなどのみやすさについても、徹底的に検討して、いいグラフにしてみてください。

練習問題6-1

日ごとの売上合計を折れ線グラフにすること。

練習問題6-2

担当者ごとの総売上金額を円グラフで示すこと。

練習問題6-3

担当者ごとの売上金額の棒グラフを作成すること。ただし、その中での顧客ごとの売上もグラフ化したいので、積み重ね棒グラフにすること。棒全体の高さが、担当者ごとの売上になるグラフをまず1つ作る。そして、棒全体の高さが特定顧客に対する売上高になるグラフも作成すること。

練習問題6-4

一件一件の売上金額のデータをもとに、ヒストグラムを作成せよ。つまり、いくらからいくらまでの売上が何件という一覧表を作り、それをもとにグラフ化する。

もちろん、自分で式を書く方法と、分析ツールを使った方法の両方を行うこと。


解答

(以前に作った解答なので、データが違います。やり方をこちらでチェックしてください。そして、Excel 2013のやり方に置き換えてみてください。…しかも相当以前ですが、ご容赦を)

練習問題6-1

日ごとの売上合計を折れ線グラフにするとは言っても、ちょっと簡単ではありません。4回目の講義で最初に言ったように、うまくグラフ化しやすいようなデータを得たいわけです。ちょっとテクがいります。次のようにやってみましょう。

元データを日付で並べ替えます。

データメニューの集計を呼び出し、グループの基準を「日付」にして、金額の合計を求めます。

アウトライン表示の部分の「2」のボタンをクリックすると、画面には集計結果だけが表示されるようになります。これは、間の詳細なデータを表示している行が、単に非表示になっただけです。ここで、まずデータ全体を選択します。このままでは、見えていない、2〜7行目なども選択していることになります。

ここで、Alt+; というキー操作をします。すると、見えているセルだけが選択されます。表示が微妙に変化していることに注意しましょう。

コピーをします。

別のシートに移動し(ない場合にはあらかじめ作っておくか、この場で用意する)、ペーストします。ここでは、日付と金額の間にある列を全部削除しました。

日付と金額のセルが並んでいるので、後はグラフを書くだけです。データの範囲を選択して、グラフウィザードでグラフを作成します。

とりあえず、こんな感じでしょうか。

練習問題6-2

前の問題と基本的には同じなのですが、集計のグループが違うなど、そこは自分でやってみてください。

練習問題6-3

ピボットテーブルをつくり、そのグラフを作ればいいでしょう。正確に作ってくれるのもそれはそれでいいのですが、出来上がったらいろいろとパラメータを動かしてみてください。

練習問題6-4

まず、J列に図のように、ヒストグラムの見出し項目を入れる

K2:K11を選択する

式として「=FREQUENCY(H2:H460, J2:J11)」を入力する

Ctrl+Shift+Enterで配列として入力する。これで、K列の数値が出てくる

グラフを書く場合、J列が数値なので、そのままだと系列だと思ってしまう。そこで、系列はK列だけになるようにし、J列は項目軸ラベルを使用するように、グラフウィザードで設定をする。設定のポイントとなるダイアログボックスは図に示した。

Excel 2007で系列を修正する方法はたとえば次のように行います。A1:B3にあるようなヒストグラムのデータからグラフを描きます。すると、A列も系列と思ってしまっています。

「グラフツール」タブの「データ」にある「データの選択」ボタンをクリックして、「データソースの選択」ダイアログボックスを表示します。まず、A列はグラフを描くデータではないので、「系列1」を選択して、「削除」ボタンをクリックします。

続いてA列をデータ系列にしたいので、右側の「横軸ラベル」のところの「編集」ボタンをクリックします。

「軸ラベル」というダイアログボックスに変わるので、ラベルが入力されている、A1:A3のセル範囲を選択してOKボタンをクリックします。

これでヒストグラムになりました。こうして系列の編集を行い、データとラベルを分離します。