BDAstyle

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

ヒストグラムの作図Tips 2/7[平均・規格値などの垂線を重ねる]

「図形」を使わず垂線を描きこむことで,何らの位置(値)をピンポイントに示したい

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

家電メーカーY社の商品Aに関する,M県での店頭販売価格調査をもとに作成した度数分布表が以下にあります。

このとき,のちの作業に都合がいいので,度数分布表・F列の階級値については「階級下限」で表示するような形式にしておきます。

DL

この度数分布表をもとにヒストグラムを作成し加工を施すことによって,ここでは以下下図のような平均および±σ(±1標準偏差)をあらわす垂線を描画していきます。

垂線(縦線)付きヒストグラム

工程

棒グラフの作成

Step 1見出しの入力

度数分布表・右方のセルに下図のような見出しを入れます。

Y, Xについては垂線の座標を意味します。

先の設定にしたがえば,これら座標について,「平均」「平均マイナスσ」「平均プラスσ」の3セット分用意する必要があります。J列の見出しは,Y, Xについての入力箇所(行)を指示するものです。

[H1]Y, [I1]X, [J2]平均, [J5]平均-σ, [J8]平均+σ

最初にYについて,始点・終点の順に,必要な線の数(「平均」「平均マイナスσ」「平均プラスσ」)だけ書き入れていきます。

具体的には,始点については,ヒストグラムの高さの基点―――つまり0を指定してやればOKです。

また終点については,ヒストグラムの山の高さが最も高い場所―――これは度数分布表から目視で読みとれば26ですが―――より若干の余裕を見て切りのいい数字を指定してやればよいかと思います(ここでは30とします)。

つまり,Y軸の始点と終点はいずれも空白セルを1つ挟んで同じ値が(必要な線の数だけ)繰り返し並びます。

次にX軸の始点と終点については,線を垂直に引く意味において同じ値を用意します。具体的には,直右の見出しが指し示すところの具体的な値を,下図のように空白セルを挟みつつ並べていけばOKです。

Step 2棒グラフの作成

度数分布表の見出し「階級下限」をシート上の適当な場所に一時的に退避(移動)させます。

度数分布表(F列・G列)および垂線のY座標(H列)の,見出しを含むデータ範囲を選択します。

挿入タブ「グラフ」グループの縦棒ボタン集合縦棒をクリックします。

これまでの作業により,以下のグラフが生成されます。

ヒストグラム エクセル 垂線

棒グラフから垂線付きヒストグラムへ

Step 3複合グラフに転換

グラフの任意の系列を選択し………

デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

グラフの種類の変更」ダイアログが表示されます。

「度数」系列を第2軸に置き換えたうえで,「Y」系列を散布図(直線)に変更します。

Step 4「垂線」系列の補完

データの選択ボタンをクリックします。

データソースの選択」ダイアログが表示されます。

凡例項目の系列「Y」について選択し,編集ボタンをクリックします。

系列の編集」ダイアログが表示されます。

「系列名」をより判別の容易な任意の名前(ここでは「垂線」)に変更し,「系列Xの範囲」を見出しを除く「X」列のデータ範囲に設定します。

scrollable

これまでの作業により,グラフは下図の状態となります。

ヒストグラム エクセル 垂線

Step 5「度数」系列の柱状化

「度数」系列を選択し………

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

作業ウインドウ「データ系列の書式設定」から「系列のオプション」グループの要素の間隔を「0%」に変更します。

Step 6x軸の設定

横軸を選択し……

作業ウインドウ「軸の書式設定」――「軸のオプション」グループの境界値について,最小値に第1(いちばん最初の)階級の階級下限を,最大値に最後の階級の階級下限に階級幅(ここでは「interval」)を加えた値を指定します。

scrollable

[最小値]26000, [最大値]38000

Step 7y軸の設定

1縦軸と第2縦軸の最大値をグラフにて確認します。

すでにstep 1にて,垂線の高い方の座標を“30”とする旨決定しています。これに照らせば第1縦軸(散布図側の軸)の最大値については整合が取れていないことがわかります。したがって,

1縦軸を選択し……

最大値を適切な値に修正します。

[最大値]30

Step 8軸についてのその他設定

縦および横軸について目盛を作ります。

具体的には,「目盛」グループの目盛の種類について,外向きないしは内向きなど任意の選択をおこないます。

これまでの作業により,グラフは下図の状態となります。

もし,Excelの返した横軸の目盛間隔の設定(下の例では2000刻み)に不都合があれば,

ヒストグラム エクセル 垂線

「軸のオプション」グループの単位・主の値にて調整します。

Step 9第2y軸の不可視化

2縦軸が不要であれば,この軸を選択し……

「ラベル」グループのラベルの位置をなしにして不可視とします。

Step 10完成

その他任意の設定を加え,垂線を重ねたヒストグラムの完成です。

エクセルで作った縦線を重ねたヒストグラム

また可能であれば,最初に退避させた見出し「階級下限」をシート上のもとの場所へと戻しておいたほうがbetterかと思います。

Step 11拡張)線の色の変更とラベルの追加

以上の方法で作成した「垂線」系列は,単個あるいは複数の線によって構成されています。

もっともそれは表示上そのように見えるだけで,たとえ無数の線が描画されていたにしろ,系列としては単独のそれにすぎません。ゆえに「垂線」系列の線色を変えると,すべての線にその変更が適用されることとなります。

したがって線の色や形状・太さを各個変更したいときには,この系列の個別の要素を選択しつつおこなう必要があります。個別の要素の選択は,この系列が全く選択されていない状態であれば,間を置いた2クリック目で可能です。

ヒストグラム エクセル 垂線

線に関する説明を加えたい場合,データラベルを挿入して,その中身を任意の文字列で書き換えてやると有効かと思います。たとえば「μ」「-σ」「+σ」の凡例を加えるとして,データラベルを使うと下図のように表現できます。

ヒストグラム エクセル 垂線

Next

次頁は 正規分布曲線を重ねるための手続きです。

その他の参照