ひげ図・垂直線を「ヒストグラム」に合成してみたいと思います。単回の効率を至上とするタスク下ならば,これは「他のツールでやれ」案件なのかもしれません。その点を鑑みれば,(ここでは特に意識はしていきませんが)本来は繰り返しのデータ処理にも耐えうるシートの構成を考えていくべきテーマかな,とも思います。1シートの作成シートを5枚用意します。グラフの要素の上では[Hist]ヒストグラム[NC]正規分布曲線[BP]箱ひげ図[VL]垂直線を描くために利用します。1/1000位までを有効とする)。元データは,シート「Data」のA列に用意します。階級幅hを入力or求めます。この例示でのhはScottによるアルゴリズムを利用しています。階級の下上限,および度数を求めます。この例示では上限を含むFrequency関数で求めています。3シート「NC」:正規分布曲線シート「NC」で,ヒストグラムの第1階級下限から最後の階級の上限までをカバーするレンジで確率密度(「Density」)を求め,それをもとに期待度数(「[NC]Ex」)を計算します。4シート「BP」:箱ひげ図シート「BP」に次の見出しおよびデータを用意します。ここにいう「上辺の度数換算値」は,箱部の上辺を描く位置を,ヒストグラムにおける度数(グラフでいえば第1縦軸の目盛り)を基準に指定するものです。「箱の高さ」も同様に度数換算値です。scrollableA列に連なる各項目は,下の箱ひげ図にいういくつかの強調個所を描画するために利用します。Q3を求め(この例示ではQuartile.Exeを使用しています),これらをもとに右方でQ3+1.5IQR点(内壁。以下略)およびQ1-1.5IQR点を求めます。Q1-1.5IQRをもとにして,元データに外れ値がいくつ存在するか,上下方の別にCountif関数で調べています(「度数」)。scrollable先の処理を受け,大きな順に度数+1番目の観測値,つまりQ3+1.5IQR点直下の観測値がわかります([BP]U_lim;Large関数によって)。Q1+1.5IQR点直上の観測値も同様の考え方で確認することができます([BP]L_lim;small関数によって)。Y座標を作成することができます。ここでY座標に一部空欄があるのは,同値の他のセルの値を使いまわせばいいかな...といった趣旨によるものです。方向の別に,それぞれの外れ値を「度数」個分元データより抽出します。具体的には度数の分だけ連番を振り,これを引数にして上方向はLarge,下方向はSamll関数で値をそのまま各個抜き出し,これをX座標としています。Y座標についてはすべて同値とします(「上辺の度数換算値」-「箱の高さ」/2)。5シート「VL」:垂直線シート「VL」に垂直線用の座標を設定します。ここでは平均と平均±σの3つの位置を示すために利用するものとします(X座標)。重ねて,これらの線を「箱ひげに重ねて上下目いっぱいに描きたい」という設定を設けます。このため,シート「Hist」の最大度数と箱ひげ図の箱の高さ,加えてヒストグラムとの若干のマージンを考慮したグラフの第1縦軸の上限を下図「第1縦軸Max」のように見積もり,これをY座標の始点として据えています。6正規分布曲線の描画→ヒストグラムの重ねシート「NC」のデータを起点に,作図のプロセスを開始します。先のリンク先の手続きを終えると,下図のような状態のグラフが出来上がります。7垂直線の重ねシート「VL」から垂直線を組み入れます。Y座標の対応に注意しながら次(下図下段)のように順に追加していきます。先の手続きを終えると,下図のような状態に遷移します。8箱ひげ図の重ねシート「BP」から箱ひげ図を組み入れます。Y座標の対応に注意しながら次(下図下段)のように順に追加していきます。残りの外れ値2系列も同様にして組み入れます。両手続きを終え,外れ値2系列のグラフの種類を「散布図」(注:マーカーのみ)に変更すると,グラフは下図のような状態となります。必要に応じて軸位置等を調整して,最初に目的としたグラフの完成です。10その他の加工(1)下図では箱ひげ図や垂線にデータラベルを付加しています。水平線に関するシートを加え必要なデータを用意してやることで,下図のように箱部の彩色(線幅の太い線)や任意の度数目盛り線の不可視化(白色線を重ねる)が可能です。して描かざるを得ないといった(エクセルにおける)ヒストグラム作図のための根底にある要素は,筆者の個人的な感覚としては,いくつかの「痒いところに手が届かない」部分を生み出す主要因となっているように感じられるところです。した場合にトレードオフとしてあきらめざるを得ない要素を内包してしまうのが惜しまれるところで,この例に即して言えば,とにもかくにも半端な目盛りの読みづらさや,軸の始点まわりの要素の判別の難さにて顕著かと思います。した要素の並立を矛盾なく実現するツールが個人的には好きなのですが,エクセルにおいて所与の設定を崩せないとする状況下なら,前者は整合を保った状態でラベルの開始値を任意に設定することが仕様として制限されるため,いわゆるバッドノウハウと呼ばれる変則的なアイデアに依らなければ解決は困難なように思います。後者については,横軸をグラフ要素から多少の手順で距離をとってやることによっていくばくかは軽減できるかもしれません。下図は,直前の過程からかかる処置をさらに加えてみたものです。参考までにこの場合の手続きの要点を示しておくと,第2横軸を追加した上で第2縦軸が(第2横軸と)最小値で交わるように交点の設定を変更し,その上で第2横軸に関するいくつかの書式の設定を重ねることで可能となります(cf.「Excelのヒストグラムの外形をRのそれに似せるためのあれこれ」Case1)。scrollableその他の参照このサイトの関連How-toです。分布曲線のグラフ―確率とパーセント点のビジュアライゼーションメインサイト「ひとりマーケティングのためのデータ分析」のヒストグラムに関するHow-toです。