9/25/2017

Excel。事務職のデータ分析その10。散布図に平均値を表示する方法【Scatter plot】

Excel。事務職のデータ分析その10。散布図に平均値を表示する方法

<散布図のアレンジ>

さて今回は、量的関係を把握するのに使うことが多い
散布図】の作り方を紹介していきますが、
以前作り方は書いたことがあるので、
今回は少しアレンジして、
平均値も同時に描くにはどうしたらいいのか?という
散布図+平均線】というグラフを作ってみましょう。

次のようなグラフを作成していきます。

そして、次の表を使っていきます。

最初に、【散布図】を作成していきましょう。

B1:C11を範囲選択して、
挿入タブのグラフにある【散布図】を選択しましょう。

このようなグラフが表示されます。

近似曲線を追加しましょう。

グラフ要素にある、「近似曲線」の線形をクリックします。

これで、近似曲線を描くことが出来ます。

データラベルは後ほどとして、
この【散布図】に平均値の線を描くにはどうしたらいいのでしょうか?

まずは、表をアレンジします。

D列は、売上本数の平均値を算出します。

D2の数式は、
=AVERAGE($C$2:$C$11)

これを、オートフィルで数式をコピーします。

E列の横軸ですが、
0から40までで、設定します。
【散布図】なので、
X軸である横軸の数値が必要なのでこのE列を用意する必要があります。

では、グラフをアクティブ(クリック)します。

カラーリファレンスを使って、
売上本数の右下にあるハンドルを平均値まで
ドラッグして平均値を含めます。

グラフはこのように変化しました。

追加されました、平均値をどれでもいいので、クリックします。

再びカラーリファレンスを使って修正していきます。

気温の紫色のカラーリファレンスをE列の横軸に移動します。

グラフはこのように修正されました。

横軸の最低値と最高値を変更します。

横軸をダブルクリックするか、横軸をクリックして、
書式タブのグラフ要素が、「横 (値) 軸」になっていることを確認して、
選択対象の書式設定をクリックすると、
右側に作業ウインドウが表示されますので、
軸の書式設定の「軸のオプション」にある、
最小値を10に、最大値を40に変更します。

では、平均値を”点”から”線”に変更していきます。

グラフをアクティブにして、
デザインタブの「グラフの種類の変更」をクリックして、
グラフの種類の変更ダイアログボックスを表示して、
「すべてのグラフ」の平均値を、
【散布図(直線)】に変更して、OKボタンをクリックしましょう。

グラフに平均線が引けました。

あとは、線を太くしたり、データラベルを表示させたり、
軸ラベルを表示させたりして完成させましょう。

このように、通常の【散布図】もアイディアで
色々プラスアルファすることも出来そうですので、
試してみてはいかがでしょうか?