0イントロダクションある事象において観測された,1000個のデータがあります。好適な階級数の設定を探索していくためのテンプレート(※)を作成します。グラフオブジェクトはそのままコピペで他文書でも利用可能です。※ここでいう「テンプレート」はExcelのファイル形式のそれでなく“ひな型”を指すものとします。テンプレートについて,2種の形式を想定していきます。横軸に階級の中心値(または任意の階級値でも)を表示する形式のテンプレート,後段は境界値を表示する形式のテンプレート(正規分布に近似の曲線がない形式/ある形式)作成のプロセスです。前段のプロセスを継承するものです。後段の形式については一部でVBAを扱います(必須)。特記事項bin(階級)の数を入力変数とします。入力するbinの数は,あくまで目安です。成果物としての度数分布表やグラフのそれとは必ずしも一致しません(↓の理由を主にして)。binの幅は範囲÷階級の数を計算し,この値を最小単位の整数倍(初期値:5倍)で丸めます。元データによってはデフォルトの倍数が合いません。この場合,設定を変更する必要があります。あらたなデータに入れ替える(=A列にデータをコピペする)際,従前のデータは「削除」でなく「クリア」にて消去する必要があります(列ごと削除すると参照構造が崩壊します)。1元データ・ペースト域の作成(1)適当な元データをA列に用意します。テンプレートとしての利便性に鑑みて,見出しは置かないものとします。2元データ・ペースト域の作成(2)セルB1に次の式を入力します。関係が常時成立する必要があります)。この式の戻り値は「測定単位の1/2」を測るために利用します(サンプルのように元データの測定単位が揃っていない場合,小数点以下桁数の最も多いものを基準にして設定します)。この操作について,レアケースとは思いますが上の例より測定単位がはるかに小さなデータ(≒小数点以下桁数が多い)を扱う場合...先の式を入力すると下の図のように指数で表示されることになるかと思います。このテンプレートは,こうした指数の表記の場合の丸めを組み込んでいないので...指数表記されるのを防ぐため,この列幅を余裕をもって拡げておきます。この結果,この列は結構な画面領域を占めることになるので,テンプレートの完成後は当該列を非表示にしてやるといいかもしれません。計算(1)次の見出しを作成します。計算(2)「n」~「RANGE」までの項目を求めます。範囲の順に次のようになります。計算(3)「測定単位の1/2」および「調整MIN.」の項目を求めます。順に次のようになります。計算(4)「BINの数」および「BINの幅」の項目を求めます。前者はとりあえずの数字(この時点では意味を持たない数字)として1を,後者は式を入力します。BINの数1BINの幅=CEILING(ROUNDDOWN(E5/E10,LEN(RIGHT(E7,LEN(E7)-FIND(".",E7)))),E7*10)なお後者は測定単位の整数倍(デフォルトでは5倍。21に)で丸めるための内容です。7度数分布表の作成(1)ここより度数分布表を作成していきます。次のような見出しを用意します。8度数分布表の作成(2)階級番号を振ります。ここでのヒストグラムは左端に度数0の階級を入れていくので,1行のブランクをとったのちに振っていきます。いくら振るかは想定における任意となりますが,ここでは(画像例示の上での制約から)Max20階級としておきます。し直下に「調整MIN.」を参照する式を入力し,この次行から「BINの幅」を加算していきます。no.」を1つ超えるところまでコピーします(したがってヒストグラムの右端にも度数0の階級が作成されます)。10度数分布表の作成(4)「階級中心値」列を埋めます。11度数分布表の作成(5)「度数」列に関して,「階級上限」および「階級中心値」列と同数の行を選択します。そのまま,次の式を入力して度数をとります。配列数式としますので,[Shift]+[Ctrl]+[Enter]で決定する必要があります。関数ウイザードを利用する方法も。詳細はリンク先を参照。12度数分布表の作成(6)度数の合計をとってnと合致するか確認します(念のため)。13ヒストグラムの作成(1)「度数」列の下図範囲(見出し含む,Sum除く)を選択し挿入タブ「グラフ」グループの縦棒をクリックします。プルダウンから,集合縦棒を選択します。14ヒストグラムの作成(2)そのまま,デザインタブ「データ」グループのデータの選択ボタンをクリックします。「データソースの選択」ダイアログが表示されます。右方「横(項目)軸ラベル」の編集ボタンをクリックします。15ヒストグラムの作成(3)「軸ラベル」ダイアログが表示されます。「軸ラベルの範囲」を「階級の中心値」列のデータ範囲(ラベル除く)に設定します。「データソースの選択」ダイアログに戻ったらOKボタンを返します。16ヒストグラムの作成(4)グラフの書式を任意に整えます。17ヒストグラムの作成(5)必要に応じて,グラフに加工を施します。ここでは目線を変えることなく階級の数と幅を確認できるよう,グラフ内にシェイプを配置し...これにセルの参照値を表示させるよう設定します(またはグラフ外のセルに作ってもいいかと思います)。18ユーザビリティ(1)「BINの数」の値を随時タイプして変更していくのは,グラフの変化を視認していくにあたってのスムーズさ欠けることから,ここにスピンボタンを配置することにします。開発タブ「コントロール」グループの挿入ボタンをクリックし,プルダウンからスピンボタンを選択します。シェイプを描くようにして,スピンボタンを任意の大きさで任意の場所に据えます。19ユーザビリティ(2)そのまま「開発」タブ・「コントロール」グループのプロパティボタンをクリックします。「コントロールの書式設定」ダイアログが表示されます。8での設定と同様の内容に)。20テンプレートの完成スピンボタンの選択を解除してテンプレートの完成です。スピンボタンに連動してグラフがさくさくと更新されます。A列をいったんクリアしてここに別のデータをコピペするのみで,適宜シートが再計算され同様のシミュレーションが可能となります(...ただしB列の数式が足りているかには注意が必要ですが)。グラフをそのまま他の文書で利用したいときは,グラフのデータ範囲を一時的に必要な範囲に調整(ドラッグによって)して用います。以上でForm1形式のテンプレートの作成工程は終了ですが,横軸に境界値を表示する形式のヒストグラムを目的とする場合,さらに2.or3.以降のステップをつづけます(この場合マクロが導入可能な環境であることも要します)。21拡張(1)元データによっては,デフォルトの「BINの幅」の丸めルール“最小単位(この例では0.01)の5倍”のハマリが悪い場合があります。必要により,この点をあらかじめ考慮に入れて,丸めルールの柔軟な変更をおこなえるようにしてもいいかもしれません。この場合,「BIN幅丸め」項目を作成し,「BINの幅」計算式を下図のように変更します(図の青字の部分を加える)。「BINの幅」修正=CEILING(ROUNDDOWN(E5/E10,LEN(RIGHT(E7,LEN(E7)-FIND(".",E7)))),E7*10*E14/5)以後「BIN幅丸め」のみ適当な値(整数)に変更すれば,「BINの幅」は(最小単位の)この倍数で丸められます(切り上げ)。拡張(2)また参考として,「BINの数」の指針として用いられている代表的な計算式の結果を表示しておくと,判断をたすけるのに有用かもしれません。平方根選択スタージェスの公式スコットの選択フリードマン=ダイアコニス(FD)の選択による各値を表示させるには,次の式を入力します。しの直下に,次の式を入力します。しの直下に,次の式を入力します。[階級中心値]列=H2-$E$11/2「度数」見出しの直下に,0を入力します。3度数分布表の修正(3)度数分布表の右方(ここではK列)にあたらしい列を1列作成します。この列に見出し「Dummy」を作成します。Dummy「Dummy」見出しの直下で,当該分布表に関する最大度数を求めます(グラフ描画にかかるダミーデータにつき変数として特段に意味をもつものではありません)。4グラフの修正(1)グラフをアクティブにし,デザインタブ「データ」グループのデータの選択ボタンをクリックします。「データソースの選択」ダイアログが表示されます。左方「凡例項目(系列)」の追加ボタンをクリックします。「系列の編集」ダイアログが表示されます。「系列名」および「系列値」に関して,下図の対応する彩色部分を指定します。「データソースの選択」ダイアログに戻ったら,左方で「Dummy」系列を選択したうえで右方にて編集ボタンをクリックします。「軸ラベル」ダイアログが表示されます。「軸ラベルの範囲」に関して,下図の対応する彩色部分を指定します。「データソースの選択」ダイアログに戻ったら,OKボタンを返します。5グラフの修正(2)ここからの工程でグラフを調整していきます。はじめに「度数」系列を選択しておきます。レイアウトタブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。「データ系列の書式設定」ダイアログが表示されます。「使用する軸」を第2軸に設定します。6グラフの修正(3)レイアウトタブ「軸」グループの軸ボタンをクリックし,プルダウンを第2縦軸なしとたどります。7グラフの修正(4)つづいて「Dummy」系列を選択します。デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。「グラフの種類の変更」ダイアログが表示されます。この「散布図」グループから散布図(マーカーのみ)を選択します。8グラフの修正(5)グラフの横軸を選択します(下図はサンプルデータを使った場合のイメージですが,これと異なった状態で表示されることがあります)。レイアウトタブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。「軸の書式設定」ダイアログが表示されます。関して,度数分布表などから適宜現状の値を読み取り設定します(暫定的なもの。下図の対応関係を参照)。9グラフの修正(6)「Dummy」系列を選択します。書式タブ「図形のスタイル」グループの図形の塗りつぶしおよび図形の枠線設定において下図のように設定し,「Dummy」系列を不可視とします。10マクロの導入(1)現ステップまでの構成においては,横軸の値と度数分布表とを連動させることができなくなります。これはかかる方法をとる上でのExcelの仕様上の制限となります。この制限のもとで連動させるためには,やはりコードを組み込む以外に方法がなさそうです。...ということで以下コードを組み込みます。Basicボタンをクリックします。起動したら,標準モジュールを作成します。Sub標準モジュールウインドウに貼り付け,VBEを閉じます。11マクロの導入(2)・テンプレートの完成シートのスピンボタンの上で右クリックし,ショートカットメニューからマクロの登録を選択します。「マクロの登録」ダイアログが表示されます。「マクロ名」からUpdateParams(このマクロの名前)を選択してOKを返します。スピンボタンの選択を解除してテンプレートの完成です。このマクロによりForm2の場合でもスピンボタンとグラフのすべての要素が連動します(「BIN幅丸め」を変更した場合はスピンボタンを押した時点で反映されます)。1アウトライン「2.境界値テンプレート」では,境界値を表示させるために散布図を利用しました。これは,技術的な辻褄合わせのためのものであって,終始不可視での利用です。ここで“どうせなら散布図も有効に活用できれば”...を考えるとき,ひとつに以下概略のような正規分布曲線付きのひな形へ転化してもいいかと思います(もっとも,元データの性格次第とはなります)。以下,この場合のざっくりとした手続きのみ示します。以下,「1.中心値テンプレート」を仕上げた状態からの流れとなります(ただしbinの数のみ例示の状態と変更しています)。シートに平均および標準偏差(の計算式)を挿し込みます(ここでは例として下図の場所に挿し込むものとします)。上図のシート構成のとき,「期待度数」列の最初のセル(K2)の計算式は次のようになります。...という流れでグラフをととのえます(「2.境界値テンプレート」とは軸の支配関係を逆にする点で異なります)。「2.境界値テンプレート」と同様,第2縦・横軸ともスケールを合わせてあります。次stepではマクロが勝手に合致させるしくみを用意するので,厳密にいえばこれは必須の作業ではありません。第2横軸ラベル位置を「下端/左端」にし,第1横軸および第2縦軸のラベルを不可視にしておきます(ここでの趣旨の上からは必要性を感じないので)。次のコードを組み込みます。軸のスケールを合わせるためのマクロです。この例示とシート構成が異なるとき,コードの中のセルアドレスは適切なものに変更する必要があります。5マクロの割り当てこのマクロを「2.境界値テンプレート」と同様にスピンボタンに割り当ててすべての作業の完了です(スピンボタンに連動してシートとグラフが正しく再計算・再描画されるかを確認します)。―"Wikipedia"(2015.2閲覧)<1234567その他の参照このサイトの関連How-toです。負数・外れ値対応)メインサイト「ひとりマーケティングのためのデータ分析」の基本統計ツールに関するHow-toです。異なるため左記の操作とは若干の変更点があります。操作に置き換えて追うことは可能です。