BDAstyle

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

ヒストグラムの作図Tips 4/7[テンプレートでグラフづくりを容易にする]

階級幅の設定を目視で直感的に探索できる環境があれば,グラフの試行錯誤に疲弊することなく「読解」のほうにリソースを割くことができる

Step 0イントロダクション

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

DL

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

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

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

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

特記事項

  • bin(階級)の数を入力変数とします。入力するbinの数は,あくまで目安です。成果物としての度数分布表やグラフのそれとは必ずしも一致しません(の理由を主にして)。
  • binの幅は範囲÷階級の数を計算し,この値を最小単位の整数倍(初期値:5倍)で丸めます。元データによってはデフォルトの倍数が合いません。この場合,設定を変更する必要があります。
  • あらたなデータに入れ替える(=A列にデータをコピペする)際,従前のデータは「削除」でなく「クリア」にて消去する必要があります(列ごと削除すると参照構造が崩壊します)。
  • 小数点以下桁数が多い元データを扱う場合には不向きです。

工程

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

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

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

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

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

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

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

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

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

なお注意が必要な点として,この操作について仮に上の例より小数点以下桁数が多い数字を扱う場合……

たとえば下図のB列ように指数に関する対応を必要とするケースが出てきます。これはStep 5の処理等にも影響を生むので,その意味では,このテンプレートは測定単位がはるかに小さなデータを扱うことが想定されるような場合には向きません

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

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

  • n
  • min
  • max
  • range
  • 測定単位の1/2
  • 調整min
  • binの数
  • binの幅

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

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

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

E2 =COUNT(A:A)
E3 =MIN(A:A)
E4 =MAX(A:A)
E5 =E4-E3

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

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

式は 順に次のとおりです。

E7 =MIN(B:B)
E8 =E3-E7

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

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

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

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

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

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

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

  • bin番号
  • 階級上限
  • 中心値
  • 度数

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

階級番号を振ります。このシートで描画するヒストグラムは,読みやすさの意味から左端に度数0の階級を入れていくので,1行のブランクをとったのちに振っていきます。

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

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

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

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

H2 =E8
H3 =H2+$E$11

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

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

I2 =H2-$E$11/2

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

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

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

J2・配列数式※ =FREQUENCY(A:A, H2:H22)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

scrollable

これにセルの参照値を表示させるよう設定したいと思います。

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の幅」計算式を下図のように変更します。

E11(書き換え) =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)の選択

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

E18(上記1) =SQRT(E2)
E19(上記2) =1+LOG(E2)/LOG(2)
E20(上記3) =E5/(STDEV(A:A)*3.5/POWER(E2, 1/3))
E21(上記4) =E5/(2*(QUARTILE(A:A, 3)-QUARTILE(A:A, 1))/POWER(E2, 1/3))

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

でわかりやすい解説が加えられています(外部サイト, 2015.2閲覧)。

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

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

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

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

H2 =H3-$E$11

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

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

I2 =H2-$E$11/2

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

J2 0

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

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

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

  • DUMMY

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

K2 =MAX(J2:J24)

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

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

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

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

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

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

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

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

これ以降の工程は,グラフ加工の処理となります。

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

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

「グラフの種類の変更」ダイアログが表示されます。「度数」系列を2に変更してから,「DUMMY」系列を「散布図」に変更します。

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

「グラフのレイアウト」グループのグラフ要素の追加ボタンをクリックし,プルダウンを2縦軸とたどります(これにより第2縦軸を不可視とします)。

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

グラフの横軸を選択します。

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

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

scrollable

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

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

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

Form2-Step 9マクロの導入(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 10マクロの導入(2)・テンプレートの完成

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

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

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

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

Form3-Step 1アウトライン

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

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

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

Form3-Step 2母数の計算

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

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

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

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

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

cf. 別頁「Excelによるヒストグラム・作図のためのTips|正規分布曲線を重ねる

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

「期待度数」列のデータをコピー(見出し含む)グラフに貼り付け系列「期待度数」を散布図(平滑線)に転換 および 第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.境界値テンプレート」と同様にスピンボタンに割り当ててすべての作業の完了です(スピンボタンに連動してシートとグラフが正しく再計算・再描画されるかを確認します)。

scrollable

参考にした書籍およびWebページ

Next

次頁は 時間の流れを横軸としてヒストグラムをつくるための手続きです。

その他の参照