BDAstyle

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

箱ひげ図の作成 with Excel2016新グラフ― Quartile.Inc と Quartile.Exc 関数の違いをふまえて

1.イントロダクション

ver.2016で追加された新機能「統計グラフ」のひとつ「箱ひげ図」の作り方です。とはいえ(筆者が)実際に動かしてみたところで,この機能は拍子抜けするくらいに簡単に エクセルという制約の上でもよくできたグラフをつくり上げてしまうシロモノだと感じました(念のため,肯定的な意味でです)。

おそらく「箱ひげ図って何?」を知らなくても作れてしまうほどの簡単さと言っていいと思います。箱ひげ図は本来ヒストグラムと同じく,ものすごくディープなしくみをもつものだと個人的には思いますので,少し大げさに言えば,アウトラインさえ知らずにアウトプットまで到達できてしまうゆえのリスクさえ懸念させる性格のものとも言えるかもしれません。そこでここでは手順そのものは従とすることにして,エクセル版にてらした“箱ひげ図のしくみ・構造”的な駄文をつづっていきたいと思います。

箱ひげ図2016 完成図

サンプルデータは以下のものを使います。3群からなる2つのサンプルです(この新グラフでは,複数の群・サンプルでも特段の加工なしに処理できるようです)。

架空メーカーのX社の製品A,B,Cに関する問い合わせ窓口業務を,同社は2拠点体制(自社拠点,委託先)で運営しています。両拠点での問い合わせ案件ごとに要した対応時間(秒)の記録を各製品ごとに自社拠点では100件づつ,委託先では97件づつ無作為に抽出したものが下表です(n=300, n=291)。

初期データ


作成にあたり参考にしたWebサイト

2. 箱ひげ図の作成 with Excel2016 新グラフ

1.手順

Step 1新グラフ「箱ひげ図」

データ範囲を見出しも含めて選択し,挿入タブ「グラフ」グループの統計グラフの挿入ボタンをクリックします。

そのまま「箱ひげ図」グループから箱ひげ図ボタンを選択します。

Step 2グラフ|箱ひげ図を構成する諸要素(1)

次のようなグラフが出力されます。

ここではおこないませんが,サンプルが複数の場合通常のグラフと同様凡例もきちんと挿入できます(最初に掲示したイメージ図のような)。

3群2サンプルの箱ひげ図

以下しばらくグラフの構成要素に関して触れます。

例示のため,グラフのなかから1つの系列をクローズアップしたとき…次の要素は「箱」

部位「箱」

次の要素は「ひげ」と呼びます。

部位「ひげ」

また,元にしたデータによっては「箱・ひげ」の部分からいくらか離れたところに1つ,または複数の「外れ値」と呼ばれるマーカーがあらわれることがあります。

部位「外れ値」

Step 3グラフ|箱ひげ図を構成する諸要素(2)

「箱」の構成要素にも役割があって,これらは下の線から順に「第1四分位数」「第2四分位数(中央値)」「第3四分位数」を意味します。

部位「第<span>1</span>四分位数Q1」「第<span>2</span>四分位数(中央値)Q2」「第<span>3</span>四分位数Q3」

同様に,「ひげ」の突端にも役割があり,通常,これらは下の線から順に「最小値」「最大値」を意味しますが…

部位「最大値」「最小値」

上下いずれか,あるいはその両方向に「外れ値」が表示されている場合は,(外れ値が存在するサイドでのみ)とりあえずそれらをないものとして考えたとして,下から順に,元のデータに実際に登場している最も小さな値と,最も大きな値 とを意味します。

部位「ひげ上限」「ひげ下限」

いずれの場合にしろ,最小値~箱の下側の線(第1四分位数),箱の下側の線~箱の真ん中の線(中央値),箱の真ん中の線~箱の上側の線(第3四分位数),箱の上側の線~最大値 の各区間においては,全体のうち,およそ1/4の観測値を含むことになります。

では「外れ値」になるデータと「箱」または「ひげ」を構成するデータとでは何がちがうのかといえば…

箱の下側の線(第1四分位数)と上側の線(第3四分位数)をそれぞれスタート地点として,前者は線から下,後者は線から上方向に,箱の高さの1.5倍の長さのものさしを置いて判断します。

このものさしでも届かないところにあるデータを,すべて “外れ値” とします(この新グラフではできませんが,もちろんこれ以外の判断の方法もあります。ただし,この新グラフが採用している方法はおそらくもっともスタンダードと言える方法です)。

1.5×IQR(四分位範囲)

箱の中のマーカー(×印)は「平均」を意味します。エクセルではデフォルトで表示される設定のようです。

部位「平均」

2.設定|外れ値・平均値

Step 4グラフ|書式設定

ここからは設定可能な項目を見ていきます。設定はすべて作業ウインドウでおこないます。と,いうことで

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

Step 5グラフ|「特異ポイント」「平均マーカー」

はじめに,デフォルトで「特異ポイントを表示する」「平均マーカーを表示する」にチェックが入っていることと思います。

これらは順に,先に見た 外れ値を表示するかしないか,平均マーカーを表示するかしないか,を制御します。これらのチェックを外す必要に迫られる機会は,ごく少ないかもしれません。

チェックボックス「特異ポイントを表示する」「平均マーカーを表示する」

3.設定|四分位数

Step 6グラフ|「包括的な中央値」「排他的な中央値」

つづいて「四分位数計算」カテゴリを見ていきます。

「包括的な中央値」「排他的な中央値」の排他的な選択肢があり,デフォルトでは「排他的な―――」の方になっています。先の外れ値の判定のしかたと同じく,この四分位の計算にもいろいろな方法(アルゴリズム)があって,エクセルではこの2つを選択できるということです。

これらはもともと組み込みの関数によって求めることができ,「包括的な中央値」は “quartile.inc”(ver.2007では quartile )によるアウトプットと,「排他的な―――」の方は “quartile.exc” によるアウトプットと同様のものです。

チェックボックス「包括的な中央値」「排他的な中央値」

ならば,これらはどう異なるのか? …という点が次に気になるところです。少なくとも筆者には「包括的な―――」「排他的な―――」という字面だけで差異を理解しろと言われても不可能です。ちなみに相当関数の公式ヘルプに頼ってみると

とあり「ぐぬぬ…」な感じでした。筆者ならこれで察しろと言われるのはあまりに突き放された孤独を感じるので,ここでは筆者なりの単純な理解を別のアプローチから以下にまとめておきたいと思います。


例示のため,小さな順から並んだ 次の5つの値をつかって「包括的な―――」「排他的な―――」のちがいを考えます(n=5)。

まず,中央値は左から何番目の値…第1四分位数は左から何番目の値…という点を考えたいと思います。

具体的には,次の2つの考え方を用意します。最初に,いちばん左の値から1番…2番…と数えていく方法,次に,同じくいちばん左の値から0番…1番… と数えていく方法を想起します。便宜的に,ここでは前者をn+1法, 後者をn-1法と呼ぶものとします。上下が逆になりますが,下図では,n+1法の採番は下,n-1法の採番は上でおこないました。

ヘルプにいう 百分率の値に基づいて1四分位, 第2四分位, 第3四分位を求めます。第1四分位は下から1/4つまり0.25(25%)の点,第2四分位は2/4つまり0.5(50%)の点,第3四分位は3/4つまり0.75(75%)の点なので,これらをn-1,n+1に乗じます。具体的には,下表のような式ができ…

第1四分位 第2四分位 第3四分位
n-1法 (5-1)*0.25 (5-1)*0.5 (5-1)*0.75
n+1法 (5+1)*0.25 (5+1)*0.5 (5+1)*0.75

これらを逐次計算して,次のような値を導くことができます。

第1四分位 第2四分位 第3四分位
n-1法 1 2 3
n+1法 1.5 3 4.5

上の表は,それぞれの方法の採番をものさしの目盛の数字に見立てたとき,第1四分位,第2四分位,第3四分位がそれぞれものさしの目盛でいうどの位置にあるのかを教えてくれます。

それぞれの方法に対応する目盛を間違えないように注意しながら,上の表で求めた値を目盛の上にプロットしていった結果,下の図のようになりました。

n-1法(青)の方のマーカーの位置は “4等分” という意味ではまぁ…腑に落ちるわけですが,n+1法(赤)の結果は何だか等分とは呼べないような…そんなもやもやが残ります。そこで,つづいては実際の値のブロックの方(図のいちばん下)で確かめてみたいと思います。

…ということで,ここでもあらたにものさしを用意します。いちばん左の値のブロックから,それぞれの方法が示した最初のマーカーまでの長さをもったものさしを作って,これをブロックの上に置いてみます(ものさし×1)。

つづいて,2つ目のものさしを右隣にならべます(ものさし×2)。

今度はいちばん右の値のブロックから,上と同様にならべてみます(ものさし×3)。

こちらもつづいて,4つ目のものさしを左隣にならべます(ものさし×4)。

ここであらためて最初にあげた設定画面を再掲して上の図の結果と照らし合わせてみると…

中央値を「包括」「排他」の意味がおぼろげに察せられてきます。要するに「包括的な―――」の方は計算において中央値を含み,「排他的な―――」の方は含めません。第2四分位(中央)はどちらの方法でも同じになりますが,第1・3四分位は両方法で異なってきます。

さて,ここでn-1法,n+1法ともに目盛の先にある値を読んでみます。まずn-1法(青)からですが…

マーカーを打った場所が目盛とドンピシャ(=先に求めた値が整数)なので,下のブロックの数字を読むのに苦労はありません。左のマーカーから順に,16, 25, 32を指しているのがわかります。すなわち,それらは順に 第1四分位, 第2四分位, 第3四分位の内容(四分位数)となります。

転じて,ここでn+1法(赤)も同じようにしてみましたが…中央値以外は目盛ドンピシャじゃないので値を読むのが少しめんどうになります。ええと…自然な感覚的には…これは 第1四分位数は値15,16の真ん中なので15.5,第3四分位数は値32,63の真ん中なので47.5と読めばいいような気もします。

ここまでのプロセスでおこなったように,四分位数を測るのに毎度毎度目盛を描いてるわけにもいきませんが,実のところ,今おこなった方法は分位数を求めるにあたってのたくさんの方法のなかのひとつとなります。「包括的な―――」「排他的な―――」に相当するQuartile.IncおよびQuartile.Exc関数はこれとは少し異なる「線形補間」と呼ばれる方法で分位数を解決します。

ただ,線形補間を用いるにあたっては,ここまでにおこなってきた採番のしかたを少し変えてやった方がおそらくはかどると思います。具体的には,n-1法の採番のしかたを変えて,1+(n-1)法としてやります。

何がしたいのかと言えば,n-11を加えて,n+1法の採番のしかたとまったく同じにしてしまいたいのです(目盛が揃う)。

したがって,分位数を旧n-1法についてのみ再度計算しなおします。

第1四分位 第2四分位 第3四分位
1+(n-1)法 1+(5-1)*0.25 1+(5-1)*0.5 1+(5-1)*0.75

結果↓

第1四分位 第2四分位 第3四分位
1+(n-1)法 2 3 4

もちろん目盛の位置関係は以前と変わるものではありません。

ではこの分位と値の一覧から,下式を使って線形補間により分位数を求めてみます。式の中に,ユーザーによってはあまり見かけることのないかもしれない床関数や天井関数といったややこしそうな記号が登場します。これはエクセルでも専用の関数がある(順にFloor系,Celling系)ので,実のところそう悩むこともなく式はつくれるといえるでしょうか。

Q=X\lfloor r \rfloor+(Xc\lceil r \rceil-Xf\lfloor r \rfloor)(r-\lfloor r \rfloor)

シート上に先ほどの目盛と値とを対応させて表をつくり…

その右側へすでに求めてある各分位を入力します。

さらに右方の列に表組みを作成し,彩色したセルに先の式をもとに作成したエクセルでの式※を入力してこれをコピーすると…

※式の中にSmall関数がいっぱい並ぶと…前言を否定するようでなんなんですが…やっぱりややこしそうに見え(ry

G3 =SMALL($B$2:$B$6, FLOOR.MATH(E3))+(SMALL($B$2:$B$6, CEILING.MATH(E3))-SMALL($B$2:$B$6, FLOOR.MATH(E3)))*(E3-FLOOR.MATH(E3))

下のような値がでてきます。このとき…

左側の値がQuartile.Incと,右側の値がQuartile.Excの戻り値と等しくなります。

Quartile.Inc,Quartile.Excのいずれにしろ,第1四分位数から第3四分位数の幅(Q3-Q1: 四分位範囲,IQR)が箱の高さになり

先に見たとおり,中央値を計算に含めない分,Quartile.Excによる戻り値の方が箱の高さは高くなります。

ここで,外れ値が箱の高さの1.5倍のものさしを基準にして決められた点をふりかえると(再掲)…

Quartile.IncQuartile.Excとの比較において,リクツの上では 前者は(元データの性質により程度はありますが)外れ値が出やすく,後者は外れ値が出にくいといった特性をもつことがわかります。

quartile.inc:外れ値が出やすい, quartile.exc:外れ値が出にくい

その意味では,外れ値の扱いをよりシビアにするかどうかでQuartile.IncQuartile.Excの選択, 箱ひげ図の設定でいえば「包括的―――」と「排他的―――」の選択を判断すればいいかと思います。

またこちらの解説によると,Quartile.Exc方式は,近時Excel以外の主要な統計パッケージ間でも広く利用可能なアルゴリズムとなっているようです。エクセル2016の新グラフ「箱ひげ図」が「排他的―――」を四分位数計算のデフォルトのアルゴリズムとしてきたのも,こうした趨勢を斟酌してのことかもしれません。

Excel2010以降の主要な統計ソフトにおける四分位計算の利用可能なアルゴリズム(画像引用)

[図の出所]Why Excel has Multiple Quartile Functions and How to Replicate the Quartiles from R and Other Statistical Packages -"Bacon Bits"

もっとも,現実には観測条件の継続性といった点にはとりわけ慎重であるべきでしょうか。後方互換性のあるQuartile関数によるひな形を使って過去継続的に箱ひげ図を記録してきた場合などは,新グラフの「排他的―――」のラジオボタンを塗りつぶす作業が1クリックで済むほど軽いものとはならないでしょう。

4.設定|データポイント

Step 7グラフ|「内側のポイントを表示する」

以下,残りの設定箇所の話です。

「内側のポイントを表示する」のチェックを入れると,外れ値以外の元データを別途マーカーで表示します。

設定「内側のポイントを表示する」

具体的には,次のように外れ値と同じ書式で描画されます。これによって,ある意味で箱ひげ図の弱点たる “分布の細かなクセ” を知ることができない点に関して若干は補うことができます。

実データをプロットした2016版箱ひげ図

これを補うことの意義については,こちらの論文「データ分析における「箱ひげ図」の誤解-高校教科書における多数の誤り-」に多くの示唆があります。

ただ,このあたらしいグラフにかかわる注意点を1点だけあげておくと,データポイントは何らかのアルゴリズムで端折られるようです(上のグラフを見ても,マーカーが100個近くあるようには見えません。もちろん,座標が重なったものは判断がつきませんが)。すべてのデータに対応するマーカーが表示されるとは限らなさそうなので,先の言及には「若干は」という修飾語をつけています。このへんは欲を言えば,ビースウォームやドットヒストグラムなど機能で補完できたらいいな…と思わせるところです。

5.設定|平均線

Step 8グラフ|「平均線を表示」

「平均線を表示」です。

設定「平均線を表示」

これは下のように指定した系列の平均マーカーをx軸方向に折れ線グラフのようにして結びます。変数を時系列で観察していくようなときには,役に立つかもしれません。

平均線を描画した2016版箱ひげ図

6.その他要素|データラベル

Step 9グラフ|「データラベル」

最後にこれは固有機能ではないですが,きちんとデータラベルも挿入できます。任意のラベルを個別に選んで削除することも可能です。またラベル位置も他のグラフと同様4方向を排他的に選択して設置することができますが,筆者の試した限りでは位置に関する微調整が効かない点で制約を抱えるようです。

データラベルを加えた2016版箱ひげ図

その他の参照