BDAstyle

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

ヒストグラムの作成 with Excel Tips2 [平均値・規格値などを垂線として加える]

5.ヒストグラムに垂線を重ねる手続き

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

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

初期データ

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

ただし,前段は階級値など数値を項目軸に適用したい場合のヒストグラムの作成手順となっています。表題に係る箇所から確認する場合は,こちらから後段へジャンプします。

1.ヒストグラムの作成(数値を項目軸に適用する場合)

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

ここから,Step 0の度数分布表の「階級値」を項目軸に据えてヒストグラムを作成したいと思います。ただ,文字列を対象としたときと同様に「階級値」および「度数」列を選択して集合縦棒グラフを作成すると,ともに系列の扱いとなり下のようなグラフが描画されます。

そこでこうした場合,とりあえずの対処として「度数」列のみの集合縦棒グラフを取っ掛かりとしていきます(「度数」列のデータ範囲を選択(見出しを含む)挿入タブ「グラフ」グループの縦棒ボタン集合縦棒)。

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

デザインタブ「データ」グループのデータの選択ボタンをクリックします。

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

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

「横(項目)軸ラベル」の編集ボタンをクリックします。

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

軸ラベル」ダイアログが表示されます。

「軸ラベルの範囲」を 見出しを除く「階級値」列のデータ範囲に設定します。

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

ふたたび「データソースの選択」ダイアログに戻ります。

OKボタンをクリックします。

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

書式を任意に調整するなど,ヒストグラムを加工しておきます。

ここでは下のように整えたものとします。以下の工程で,これに垂直線を加えます。

2.垂線の描画

Step 7必要なデータの準備

ここでの作例においては,垂線の描画に散布図を利用します。

まずはその準備として,ヒストグラムの上に描画したい垂線に関して,シート上に必要なデータを加えていきます(データの内容は下表。具体的な配置については下図を参照)。

x ave.
=AVERAGE(A:A) 0
=C10 30

この各セルの意味するところは,以下のとおりです(見出しと内容が一致する性格のものではありません)。

見出し
 記号的な意味合いの便宜的なもの
見出し
 これは系列名として利用するので, 操作上のみ重要
平均
 描画したい垂線のx軸上の位置
1縦軸の書式上の最小値
 ※度数の最小値ではありません
平均
 上に同じ
1縦軸の書式上の最大値
 ※度数の最大値ではありません

さらに見方をかえれば,これらのデータは,散布図の

x値 y値
x値 y値

を構成するデータ対として使用するものです。

Step 8散布図の組み込み(1)

グラフをアクティブにした状態から,デザインタブ「データ」グループのデータの選択ボタンをクリックします。

Step 9散布図の組み込み(2)

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

「凡例項目(系列)」の追加ボタンをクリックします。

Step 10散布図の組み込み(3)

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

「系列名」および「系列値」について,対応する色の領域を指定します(下図)。

この操作を終えたら,OKボタンをクリックします。

Step 11散布図の組み込み(4)

再度「データソースの選択」ダイアログに戻ります。

ここではOKボタンをクリックします。

Step 12散布図の組み込み(5)

グラフにあたらしく作成された系列(“ave.”)を選択し,レイアウトタブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。

「データ系列の書式設定」ダイアログの「使用する軸」に関して,2に変更し,閉じるボタンをクリックします。

Step 13散布図の組み込み(6)

系列の選択を維持したまま,デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

「グラフの種類の変更」ダイアログが表示されたら「散布図」グループの散布図(直線)を選択します。

この操作を終えたのち,OKボタンをクリックします。

Step 14散布図の組み込み(7)

この時点において,グラフは下図のようになっているかと思います。

Step 15垂線の描画(1)

散布図を組み込んだところで,ここからしばらく整合性に関する設定作業をおこなっていきます。具体的には,デザインタブ「データ」グループのデータの選択ボタンをクリックし…

Step 16垂線の描画(2)

再度「データソースの選択」ダイアログを表示させます。

「凡例項目(系列)」に表示される系列の中から,「ave.」(=あたらしく作成した系列)を選択した上で,編集ボタンをクリックします。

Step 17垂線の描画(3)

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

空欄になっている「系列Xの値」について,対応する色の領域を指定します(下図)。

この操作を終えたら,OKボタンをクリックします(この作業においてグラフが乱れますが無視します)。

Step 18垂線の描画(4)

「データソースの選択」ダイアログに戻ります。

OKボタンをクリックします。

Step 19垂線の描画(5)

グラフをアクティブにしたまま,レイアウトタブ「軸」グループのボタン2横軸既定の軸を表示と辿ると…

Step 20垂線の描画(6)

下のようなグラフになります(第2横軸の作成)。

2横軸の最小値・最大値を第1横軸のそれらと同一の値に合わせます(軸の書式設定。下図は変更前のもの)。

Step 21垂線の描画(7)

同じようにして,第2縦軸の最小値・最大値も第1縦軸のそれらと同一の値に合わせます(軸の書式設定。下図は変更前のもの)。

Step 22垂線の描画(8)

2横・縦両軸について,(必要に応じて)軸の値および目盛線を不可視にします(「軸ラベル」および「目盛の種類」設定項目を“なし”に設定。あるいは文字をバックグラウンドと同色に)。

Step 23垂線を追加する(1)

以上の手続きで平均値を示す垂線がヒストグラム上に挿入されます。

σ線,あるいはQCの場面で利用する下限・上限規格線などを加える場合,いましばらく工程を続けます。

ここでは,±σの位置に垂線を加えるものと想定して話をすすめます。

必要な垂線に係るデータを入力したのち(Step 7の構成を参照),グラフをアクティブにして,デザインタブ「データ」グループのデータの選択ボタンをクリックします。

Step 24垂線を追加する(2)

「データソースの選択」ダイアログが表示されるので,「凡例項目(系列)」の追加ボタンをクリックし…

Step 25垂線を追加する(3)

「系列の編集」ダイアログの「系列名」「系列Xの値」「系列Yの値」を埋めていきます(下図の彩色による対応を参照)。

この操作を終え,OKボタンをクリックすると…

Step 26垂線を追加する(4)

「データソースの選択」ダイアログに戻るので,再びOKボタンをクリックします。

以上のように2本目からは簡単な手続きで垂線を追加できます(下図は線を追加したところ)。

これをStep 23から,必要な垂線の数だけ繰り返します(データが用意してあれば,「データソースの選択」ダイアログから続けて追加していきます)。

Step 27ヒストグラムの完成

書式等調整して,ヒストグラムの完成です。

Next

次頁は 正規分布曲線を重ねて描画する手続きです。

その他の参照