BDAstyle

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

ヒストグラムの作成 with Excel Tips4 [テンプレートをつかって 階級幅の設定を目視で探る]

7.テンプレートの作成

Step 0イントロダクション

ある事象において観測された,1000個のデータがあります。

初期データ

ここでは,こうしたデータをシートにペーストしたうえで,ヒストグラムの形状を見くらべながら 好適な階級数の設定を探索していくためのテンプレート(※)を作成します。また,グラフオブジェクトはそのままコピペで他文書でも利用可能です。

※ここでいう「テンプレート」はExcelのファイル形式のそれでなく“ひな型”を指すものとします。

テンプレートについて,2種の形式を想定していきます。以下前段は 横軸に階級の中心値(または任意の階級値でも)を表示する形式のテンプレート,後段は境界値を表示する形式のテンプレート(正規分布に近似の曲線がない形式/ある形式)作成のプロセスです。

ただし後段は独立したものではなく 前段のプロセスを継承するものです。また,後段の形式については一部でVBAを扱います(必須)。

特記事項

1.階級の中心値を表示する形式のテンプレート

Form1-Step 1元データ・ペースト域の作成(1)

適当な元データをA列に用意します。

テンプレートとしての利便性に鑑みて,見出しは置かないものとします。

Form1-Step 2元データ・ペースト域の作成(2)

セルB1に次の式を入力します。

=IFERROR(5/(10^LEN(MID(A1,FIND(".",A1),LEN(A1)))),0.5)

先と同様テンプレートとしての性格を与えるため,この式をいくらかのボリュームをもって下方にコピーしておきます(A列のアクティブな行数≦B列のアクティブな行数 の関係が常時成立する必要があります)。

なおこの式の戻り値は「測定単位の1/2」を測るために利用します(サンプルのように元データの測定単位が揃っていない場合,小数点以下桁数の最も多いものを基準にして設定します)。

この操作について,レアケースとは思いますが上の例より測定単位がはるかに小さなデータ(≒小数点以下桁数が多い)を扱う場合…

先の式を入力すると下の図のように指数で表示されることになるかと思います。このテンプレートは,こうした指数の表記の場合の丸めを組み込んでいないので…

指数表記されるのを防ぐため,この列幅を余裕をもって拡げておきます。この結果,この列は結構な画面領域を占めることになるので,テンプレートの完成後は当該列を非表示にしてやるといいかもしれません。

Form1-Step 3パラメータの入力 or 計算(1)

次の見出しを作成します。

Form1-Step 4パラメータの入力 or 計算(2)

「n」~「RANGE」までの項目を求めます。

式はサイズ, 最小値, 最大値, 範囲の順に次のようになります。

n =COUNT(A:A)
MIN. =MIN(A:A)
MAX. =MAX(A:A)
RANGE =E4-E3

Form1-Step 5パラメータの入力 or 計算(3)

「測定単位の1/2」および「調整MIN.」の項目を求めます。

式は 順に次のようになります。

測定単位の1/2 =MIN(B:B)
調整MIN. =E3-E7

Form1-Step 6パラメータの入力 or 計算(4)

「BINの数」および「BINの幅」の項目を求めます。

前者はとりあえずの数字(この時点では意味を持たない数字)として1を,後者は式を入力します。

BINの数 1
BINの幅 =CEILING(ROUNDDOWN(E5/E10,LEN(RIGHT(E7,LEN(E7)-FIND(".",E7)))),E7*10)

なお後者は測定単位の整数倍(デフォルトでは5倍。変更の方法はForm1-Step 21に)で丸めるための内容です。

Form1-Step 7度数分布表の作成(1)

ここより度数分布表を作成していきます。次のような見出しを用意します。

Form1-Step 8度数分布表の作成(2)

階級番号を振ります。ここでのヒストグラムは左端に度数0の階級を入れていくので,1行のブランクをとったのちに振っていきます。

総じていくら振るかは想定における任意となりますが,ここでは(画像例示の上での制約から)Max20階級としておきます。

Form1-Step 9度数分布表の作成(3)

「階級上限」列・見出し直下に「調整MIN.」を参照する式を入力し,この次行から「BINの幅」を加算していきます。

後者のセルのみ,「BIN no.」を1つ超えるところまでコピーします(したがってヒストグラムの右端にも度数0の階級が作成されます)。

[階級上限]列 =E8
[階級上限]列 =H2+$E$11

Form1-Step 10度数分布表の作成(4)

「階級中心値」列を埋めます。

[階級中心値]列 =H2-$E$11/2

Form1-Step 11度数分布表の作成(5)

「度数」列に関して,「階級上限」および「階級中心値」列と同数の行を選択します。

そのまま,次の式を入力して度数をとります。ただし配列数式としますので,[Shift]+[Ctrl]+[Enter]で決定する必要があります。

[度数]列・配列数式※ =FREQUENCY(A:A,H2:H22)

Frequency関数によるヒストグラムの作り方に関しては 関数ウイザードを利用する方法も。詳細はリンク先を参照。

Form1-Step 12度数分布表の作成(6)

度数の合計をとってnと合致するか確認します(念のため)。

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

「度数」列の下図範囲(見出し含む,Sum除く)を選択し挿入タブ「グラフ」グループの縦棒をクリックします。

プルダウンから,集合縦棒を選択します。

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

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

「データソースの選択」ダイアログが表示されます。右方「横(項目)軸ラベル」の編集ボタンをクリックします。

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

「軸ラベル」ダイアログが表示されます。「軸ラベルの範囲」を「階級の中心値」列のデータ範囲(ラベル除く)に設定します。

ふたたび「データソースの選択」ダイアログに戻ったらOKボタンを返します。

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

グラフの書式を任意に整えます。

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

必要に応じて,グラフに加工を施します。ここでは目線を変えることなく階級の数と幅を確認できるよう,グラフ内にシェイプを配置し…

これにセルの参照値を表示させるよう設定します(またはグラフ外のセルに作ってもいいかと思います)。

Form1-Step 18ユーザビリティ(1)

「BINの数」の値を随時タイプして変更していくのは,グラフの変化を視認していくにあたってのスムーズさ欠けることから,ここにスピンボタンを配置することにします。

開発タブ「コントロール」グループの挿入ボタンをクリックし,プルダウンからスピンボタンを選択します。

シェイプを描くようにして,スピンボタンを任意の大きさで任意の場所に据えます。

Form1-Step 19ユーザビリティ(2)

そのまま「開発」タブ・「コントロール」グループのプロパティボタンをクリックします。

「コントロールの書式設定」ダイアログが表示されます。「コントロール」タブの現在値最小値最大値変化の増分リンクするセル の各項目を次のように設定します(最大値についてはForm1-Step 8での設定と同様の内容に)。

Form1-Step 20テンプレートの完成

スピンボタンの選択を解除してテンプレートの完成です。スピンボタンに連動してグラフがさくさくと更新されます。

またA列をいったんクリアしてここに別のデータをコピペするのみで,適宜シートが再計算され同様のシミュレーションが可能となります(…ただしB列の数式が足りているかには注意が必要ですが)。

グラフをそのまま他の文書で利用したいときは,グラフのデータ範囲を一時的に必要な範囲に調整(ドラッグによって)して用います。

以上でForm1形式のテンプレートの作成工程は終了ですが,横軸に境界値を表示する形式のヒストグラムを目的とする場合,さらに2.or3.以降のステップをつづけます(この場合マクロが導入可能な環境であることも要します)。

Form1-Step 21拡張(1)

元データによっては,デフォルトの「BINの幅」の丸めルール最小単位(この例では0.015のハマリが悪い場合があります。

必要により,この点をあらかじめ考慮に入れて,丸めルールの柔軟な変更をおこなえるようにしてもいいかもしれません。この場合,「BIN幅丸め」項目を作成し,「BINの幅」計算式を下図のように変更します(図の青字の部分を加える)。

「BINの幅」修正 =CEILING(ROUNDDOWN(E5/E10,LEN(RIGHT(E7,LEN(E7)-FIND(".",E7)))),E7*10*E14/5)

以後「BIN幅丸め」のみ適当な値(整数)に変更すれば,「BINの幅」は(最小単位の)この倍数で丸められます(切り上げ)。

=CEILING(ROUNDDOWN(E5/E10,LEN(RIGHT(E7,LEN(E7)-FIND(".",E7)))),E7*10*E14/5)

Form1-Step 22 拡張(2)

また参考として,「BINの数」の指針として用いられている代表的な計算式の結果を表示しておくと,判断をたすけるのに有用かもしれません。たとえば,

  1. 平方根選択
  2. スタージェスの公式
  3. スコットの選択
  4. フリードマン=ダイアコニス(FD)の選択

による各値を表示させるには,次の式を入力します。

1 =SQRT(E2)
2 =1+LOG(E2)/LOG(2)
3 =E5/(STDEV(A:A)*3.5/POWER(E2,1/3))
4 =E5/(2*(QUARTILE(A:A,3)-QUARTILE(A:A,1))/POWER(E2,1/3))

なおこれらの指標については,

で詳細にわかりやすく解説がされています(外部サイト, 2015.2閲覧)。


2.境界値を表示する形式のテンプレート

Form2-Step 1度数分布表の修正(1)

度数分布表の見出しの直下に,あたらしい行を1行(4つのセル)作成します。

「階級上限」見出しの直下に,次の式を入力します。

[階級上限]列 =H3-$E$11

Form2-Step 2度数分布表の修正(2)

「階級中心値」見出しの直下に,次の式を入力します。

[階級中心値]列 =H2-$E$11/2

「度数」見出しの直下に,0を入力します。

[度数]列 0

Form2-Step 3度数分布表の修正(3)

度数分布表の右方(ここではK列)にあたらしい列を1列作成します。

この列に見出し「Dummy」を作成します。

「Dummy」見出しの直下で,当該分布表に関する最大度数を求めます(グラフ描画にかかるダミーデータにつき変数として特段に意味をもつものではありません)。

[Dummy]列 =MAX(J2:J24)

Form2-Step 4グラフの修正(1)

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

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

「系列の編集」ダイアログが表示されます。「系列名」および「系列値」に関して,下図の対応する彩色部分を指定します。

「データソースの選択」ダイアログに戻ったら,左方で「Dummy」系列を選択したうえで右方にて編集ボタンをクリックします。

「軸ラベル」ダイアログが表示されます。「軸ラベルの範囲」に関して,下図の対応する彩色部分を指定します。

ふたたび「データソースの選択」ダイアログに戻ったら,OKボタンを返します。

Form2-Step 5グラフの修正(2)

ここからの工程でグラフを調整していきます。

はじめに「度数」系列を選択しておきます。

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

「データ系列の書式設定」ダイアログが表示されます。「使用する軸」を第2軸に設定します。

Form2-Step 6グラフの修正(3)

レイアウトタブ「軸」グループのボタンをクリックし,プルダウンを2縦軸なしとたどります。

Form2-Step 7グラフの修正(4)

つづいて「Dummy」系列を選択します。

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

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

Form2-Step 8グラフの修正(5)

グラフの横軸を選択します(下図はサンプルデータを使った場合のイメージですが,これと異なった状態で表示されることがあります)。

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

「軸の書式設定」ダイアログが表示されます。「軸のオプション」カテゴリの最小値最大値目盛間隔に関して,度数分布表などから適宜現状の値を読み取り設定します(暫定的なもの。下図の対応関係を参照)。

Form2-Step 9グラフの修正(6)

「Dummy」系列を選択します。

書式タブ「図形のスタイル」グループの図形の塗りつぶしおよび図形の枠線設定において下図のように設定し,「Dummy」系列を不可視とします。

Form2-Step 10マクロの導入(1)

現ステップまでの構成においては,横軸の値と度数分布表とを連動させることができなくなります。これはかかる方法をとる上でのExcelの仕様上の制限となります。この制限のもとで連動させるためには,やはりコードを組み込む以外に方法がなさそうです。

…ということで以下コードを組み込みます。

開発タブ「コード」グループのVisual Basicボタンをクリックします。

VBE が起動したら,標準モジュールを作成します。

以下のコードをすべてコピーし…

※コードの中のセルアドレスは合致するものに適宜変更を要します

Sub UpdateParams()

' X軸パラメータの更新
' ※コードの中のセルアドレスが手元のシートと異なる場合これらを変更する必要があります
    
Dim Tmp As String
Dim Digit As Long

Tmp = Range("E7").Value ' 「測定単位の1/2」の値のセルアドレスを指定
Digit = Len(Mid(Tmp, InStr(Tmp, ".") + 1))

ActiveSheet.ChartObjects(1).Activate
   
    With ActiveChart.Axes(xlCategory)
        .MinimumScale = Application.WorksheetFunction.RoundDown(Range("H2").Value, Digit) ' 度数分布表「階級上限」最初の値のセルアドレスを指定
        .MaximumScale = Application.WorksheetFunction.RoundDown(Range("H24").Value, Digit) ' 度数分布表「階級上限」最後の値のセルアドレスを指定
        .MajorUnit = Application.WorksheetFunction.RoundDown(Range("E11").Value, Digit - 1) ' 「BINの幅」の値のセルアドレスを指定
    End With

End Sub

標準モジュールウインドウに貼り付け,VBEを閉じます。

Form2-Step 11マクロの導入(2)・テンプレートの完成

シートのスピンボタンの上で右クリックし,ショートカットメニューからマクロの登録を選択します。

「マクロの登録」ダイアログが表示されます。「マクロ名」からUpdateParams(このマクロの名前)を選択してOKを返します。

スピンボタンの選択を解除してテンプレートの完成です。このマクロによりForm2の場合でもスピンボタンとグラフのすべての要素が連動します(「BIN幅丸め」を変更した場合はスピンボタンを押した時点で反映されます)。


3.境界値を表示する形式のテンプレート+正規分布曲線

Form3-Step 1アウトライン

「2.境界値テンプレート」では,境界値を表示させるために散布図を利用しました。これは,技術的な辻褄合わせのためのものであって,終始不可視での利用です。

ここで“どうせなら散布図も有効に活用できれば”…を考えるとき,ひとつに以下概略のような正規分布曲線付きのひな形へ転化してもいいかと思います(もっとも,元データの性格次第とはなります)。

以下,この場合のざっくりとした手続きのみ示します。

Form3-Step 2母数の計算

以下,「1.中心値テンプレート」を仕上げた状態からの流れとなります(ただしbinの数のみ例示の状態と変更しています)。

まずシートに平均および標準偏差(の計算式)を挿し込みます(ここでは例として下図の場所に挿し込むものとします)。

また,度数分布表に別途「期待度数」列を挿入します。

たとえば上図のシート構成のとき,「期待度数」列の最初のセル(K2)の計算式は次のようになります。

セルK2 =NORM.DIST(I2, $E$3, $E$4, FALSE)*$E$2*$E$14

cf. 別頁「ヒストグラムの作成 with Excel Tips3 [正規分布曲線を重ねて描画する]

Form3-Step 3系列の追加とグラフの調整

「期待度数」列のデータをコピー(見出し含む)グラフに貼り付け系列「期待度数」を散布図(線のみ)に転換 および 第2軸へ振り替え …という流れでグラフをととのえます(「2.境界値テンプレート」とは軸の支配関係を逆にする点で異なります)。

※下図例示では確認のため 「2.境界値テンプレート」と同様,第2縦・横軸ともスケールを合わせてあります。次stepではマクロが勝手に合致させるしくみを用意するので,厳密にいえばこれは必須の作業ではありません。

2横軸ラベル位置を「下端/左端」にし,第1軸および第2縦軸のラベルを不可視にしておきます(ここでの趣旨の上からは必要性を感じないので)。

Form3-Step 4マクロの導入

次のコードを組み込みます。

軸のスケールを合わせるためのマクロです。ただしこの例示とシート構成が異なるとき,コードの中のセルアドレスは適切なものに変更する必要があります。

Sub UpdateParams_for_Form3()

' X,Y軸パラメータの更新
' ※コードの中のセルアドレスが手元のシートと異なる場合これらを変更する必要があります
    
Dim Tmp As String
Dim Digit As Long

Tmp = Range("E10").Value ' 「測定単位の1/2」の値のセルアドレスを指定
Digit = Len(Mid(Tmp, InStr(Tmp, ".") + 1))

ActiveSheet.ChartObjects(1).Activate

    With ActiveChart.Axes(xlCategory, xlSecondary)
        .MinimumScale = Application.WorksheetFunction.RoundDown(Range("H2").Value - Range("E14").Value, Digit) ' 度数分布表「階級上限」最初の値のセルアドレスを指定
        .MaximumScale = Application.WorksheetFunction.RoundDown(Range("H23").Value, Digit) ' 度数分布表「階級上限」最後の値のセルアドレスを指定
        .MajorUnit = Application.WorksheetFunction.RoundDown(Range("E14").Value, Digit - 1) ' 「BINの幅」の値のセルアドレスを指定
    End With
    
    With ActiveChart.Axes(xlValue, xlSecondary) ' Y1,Y2スケール合わせ
        .MinimumScale = 0
        .MaximumScale = ActiveChart.Axes(xlValue).MaximumScale
        .MajorUnit = ActiveChart.Axes(xlValue).MajorUnit
    End With
    
End Sub

Form3-Step 5マクロの割り当て

このマクロを「2.境界値テンプレート」と同様にスピンボタンに割り当ててすべての作業の完了です(スピンボタンに連動してシートとグラフが正しく再計算・再描画されるかを確認します)。

参考文献・Webページ

その他の参照