2012年3月25日日曜日

ExcelでBox Plot(箱ひげ図)を描く

このエントリーをはてなブックマークに追加 Evernoteにクリップ
Excel 2007での箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)の作成方法を紹介します。元ネタは、Box Plot for Excel 2007からとなります。

下記が完成した箱ひげ図(box plot)です。株価チャートを使って似たようなグラフを作成できますが、100万倍こちらの方がステキです。
箱ひげ図
Box Plot(箱ひげ図)

箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)とは

箱ひげ図(box plot)は、データの分布を可視化するのに優れたグラフです。特に幾つかのデータの分布を比較するのに長けており、分布がどのように異なっているかを視覚的に捉えるのに便利です。Wikipediaで下記のように説明されています。
箱ひげ図(はこひげず、箱髭図、box plot)とは、ばらつきのあるデータをわかりやすく表現するための統計学的グラフである。細長い箱と、その両側に出たひげで表現されることからこの名がある。一般的には(ジョン・テューキーの方式)、重要な5種の要約統計量である、最小値、第1四分位点、中央値、第3四分位点と最大値を表現する。母集団は実際には様々なタイプの確率分布に従うわけだが、箱ひげ図はそのような仮定に関係なく、データの分布を表現することができる。箱の各部分の間隔から分散や歪度の程度、また外れ値(これは後述のように箱ひげ図の方式により異なる)を知ることもできる。
箱ひげ図(box plot)は、データを1/4(25%)ずつに分割して、最小値、第一四分位(25%点)、中央値(50%点)、第三四分位(75%)、最大値の5つの要素でデータの分布を可視化します。
データの分布を5つの要素で可視化

Excelで四分位を求める

Excelで四分位を求めるには、=percentile()、若しくは=quartile()で求めることができます。最大値、中央値、最小値は、それぞれ=max、=median()、=min()で求めることもできます。

四分位の求め方
項目名備考PERCENTILEQUARTILE
最大値データの最大値PERCENTILE(データ範囲,1) QUARTILE(データ範囲,4)
第3四分位データの下から3/4に位置する値(75%点)PERCENTILE(データ範囲,0.75)QUARTILE(データ範囲,3)
中央値データの中央値(50%点)PERCENTILE(データ範囲,0.5)QUARTILE(データ範囲,2)
第1四分位データの下から4/1に位置する値(25%点)PERCENTILE(データ範囲,0.25)QUARTILE(データ範囲,1)
最小値データの最小値PERCENTILE(データ範囲,0)QUARTILE(データ範囲,0)

Excel 2007で箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)を描く

Box Plot for Excel 2007にあるサンプルデータを使用して説明しますが、今回作成したサンプルを下記に置いてありますのでご利用ください。



サンプルデータ
 
ABCDEFG
1 sample1 sample2 sample3 sample4 sample5 sample6
2 50.5 46.7 43.2 62.5 52.0 53.2
3 51.3 45.5 45.3 64.2 52.3 58.6
4 55.3 45.6 43.2 66.1 55.0 55.4
5 50.3 46.3 43.5 66.7 54.3 53.5
6 55.0 49.7 45.6 63.4 52.6 56.0
7 59.6 49.8 43.1 67.7 53.9 57.6
8 51.3 48.5 45.4 62.2 51.2 54.5
9 56.1 48.7 46.0 68.4 52.1 58.7
10 59.7 48.8 44.1 62.7 54.5 55.7

12-16行にそれぞれ最大値、第三四分位、中央値、第一四分位、最小値を求めます。

B12:B16に下の式を代入してC12:C16からG12:G16にコピーします。

=QUARTILE(B2:B10,4)
=QUARTILE(B2:B10,3)
=QUARTILE(B2:B10,2)
=QUARTILE(B2:B10,1)
=QUARTILE(B2:B10,0)

四分位数の計算結果)
ABCDEFG
12最大値59.749.846.068.455.058.7
13第3四分位56.148.845.466.754.357.6
14中央値55.048.544.164.252.655.7
15第1四分位51.346.343.262.752.154.5
16最小値50.345.543.162.251.253.2

次にグラフを描写するために必要な値を計算して18-22行に代入します。

B18:B22に下の式を代入してC12:C16からG12:G16にコピーします。

=B15-B16
=B15
=B14-B15
=B13-B14
=B12-B13

グラフ用の計算値)
ABCDEFG
18第1四分位-最小値10.80.10.50.91.3
19第1四分位51.346.343.262.752.154.5
20中央値-第1四分位3.72.20.91.50.51.2
21第3四分位-中央値1.10.31.32.51.71.9
22最大値-第3四分位3.610.61.70.71.1

積み上げ縦棒グラフで基本グラフの作成

A19:G21を選択して、2-D 縦棒から積み上げ縦棒を選択します(下記のイメージを参考)。
積み上げ縦棒グラフの作成
作成したグラフは下から第1四分位、中央値-第1四分位、第3四分位で構成されています。このグラフをよくよく眺めると赤色と緑色の部分が箱ひげ図(Box Plot)の箱の部分になっていることが分かりますでしょうか。

積み上げ縦棒グラフ

箱ひげ図の箱を作成する

作成した積み上げ縦棒グラフの青色(第1四分位)の部分を「塗りつぶしなし」にして、箱ひげ図の箱の部分だけにします。

1)グラフの青色(第1四分位)部分をマウスで選択して、右クリックで「データ系列の書式設定」を選択

第1四分位を選択
2)「データ系列の書式設定」→「塗りつぶし」で「塗りつぶしなし(N)」を選択

第1四分位を塗りつぶしなしに設定
これで、箱ひげ図の箱の部分が作成できました。

箱ひげ図(box plot)の箱部分

箱ひげ図(box plot)の髭(ひげ)を作成する

箱ひげ図(box plot)の髭(ひげ)の部分は、誤差範囲で作成します。

1)グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択

誤差範囲の指定
2)誤差範囲の追加から「第1四分位」を選択


3)箱ひげ図の下側の髭(ひげ)を作成するため縦軸誤差範囲の表示は「負の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「負の誤差の値(N)」に第1四分位-最小値(B18:G18)を範囲選択して値を入力


箱ひげ図(box plot)の下髭を作成
これで下側の髭(ひげ)が完成です。

箱ひげ図(box plot)の下髭
続いて上側の髭(ひげ)を作成します。

4)グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択

誤差範囲の指定
5)誤差範囲の追加から「第3四分位-中央値」を選択


6)箱ひげ図の上側の髭(ひげ)を作成するため、縦軸誤差範囲の表示は「正の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「正の誤差の値(N)」に最大値-第3四分位(B22:G22)を範囲選択して値を入力


箱ひげ図(box plot)の上髭を作成 
これで髭(ひげ)の部分も作成完了です。

箱ひげ図(box plot)の髭(ひげ)

箱ひげ図(box plot)の見栄えを調整する

箱ひげ図(box plot)はこれで一応完成となります。後は箱ひげ図(box plot)の見栄え(ボックスの縦幅と横幅、色など)を調整します。

ボックス縦幅の調整

1)グラフの縦軸を選んで右クリックから「軸の書式設定(F)」を選択

軸の書式設定
2)軸のオプションで最小値と最大値を自動から固定に変更して値を入力します。今回のサンプルでは、最小値を40、最大値を70としています

軸のオプション
下記が縦幅を調整した箱ひげ図になります。

縦に広がった箱ひげ図(box plot)

ボックス横幅の調整

次に横幅を調整します。

1)グラフの赤色(中央値-第1四分位)を選択して右クリックで「データ系列の書式設定(F)」を選択

データ系列の書式設定
2)系列のオプションで「要素の間隔(W)」で箱の横幅を調整します。値が小さくなるほど、箱の横幅は広がります。サンプルでは50%としています


横幅を調整したグラフが下記になります。

箱の横幅を調整した箱ひげ図(box plot)

グラフの色の削除

グラフの色を削除する場合に枠線をなしのままにしていると箱が表示されなくなってしまいますので注意してください。

1)グラフの赤色(中央値-第1四分位)部分を選択して右クリックで「データ系列の書式設定(F)」を選択


2)枠線の色で「線(単色)(S)」で枠線の色を指定


3)塗りつぶしで「塗りつぶしなし(N)」を選択


これで箱ひげ図の箱の下側が色がなくなり、枠線だけになりました。


同様にグラフの緑色(第3四分位-中央値)部分についても枠線だけにします。

箱ひげ図の完成

凡例を削除すれば、箱ひげ図の完成です。好みで箱に色を付けたり、目盛り線を調整してください。

完成した箱ひげ図

Excel Hacks 第2版― プロが教える究極のテクニック140選
Excel Hacks 第2版― プロが教える究極のテクニック140選David Hawley Raina Hawley 羽山 博

オライリー・ジャパン 2007-11-22
売り上げランキング : 155743


Amazonで詳しく見る by G-Tools

今日の一曲


Yellow Magic Orchestraのアルバムで唯一保有していなかったのが、『浮気なぼくら(インストゥルメンタル)』でした。iTunesでYMOのアルバムが公開され、実にリリースから28年経って、2枚組の『浮気なぼくら&インストゥルメンタル』を購入しました。アルバムを聴いてみると、後期の『BGM』、『テクノデリック』で完成されたYMOがしっかりと組み込まれていました。そして細野さんのリズムと歌詞は、いつの時代もステキです。今日の一曲は、ユキヒロと細野さんのハーモニーが至極刺激的な『Focus』です。




2012年3月5日月曜日

インターネット広告が新聞広告を抜いた?

このエントリーをはてなブックマークに追加 Evernoteにクリップ

2011年 マスコミ四媒体とインターネットの広告費

2012年2月15日に経済産業省の特定サービス産業動態統計調査の2011年12月確報が掲載され、その後、2月23日に電通が2011年の日本の広告費のニュースリリースをアップしていましたので、これらのデータを使ってマスコミ4媒体とインターネットの広告費の動向について考察してみました。

電通の2011年日本の広告費については、下記のように評されています。
2011 年(平成 23 年)の総広告費の特徴

媒体別にみると、「テレビ広告費」(前年比 99.5%)、「新聞広告費」(同 93.7%)、「雑誌広告費」(同 93.0%)、「ラジオ広告費」(同 96.0%)のいずれも減少し「マスコミ四媒体広告費」は同 97.4%と前年を下回った。・・・(中略)・・・「インターネット広告費」(同 104.1%)はソーシャルメディア活用などの新手法が増えた結果、引き続き増加となった。

一方、経済産業書の特定サービス産業動態統計調査によると、「テレビ広告費」(前年比 101.1%)、「新聞広告費」(同 98.3%)、「雑誌広告費」(同 98.6%)、「ラジオ広告費」(同 100%)で、「マスコミ四媒体広告」(同100.4%)と前年維持となっています。また「インターネット広告費」(同 171.6%)については大きく増加したという結果になります。

ふたつの資料には下記に示すように乖離があります。インターネット広告費については、電通の資料では媒体費と広告制作費が合算されていますが、広告制作費を除いても618,900(百万円)となり経済産業省の値とは大きく異なります。

2011年 広告費 特定サービス産業動態統計調査vs電通資料(単位:百万円)

インターネット広告が新聞広告を抜いた?

昨今、インターネット広告費が新聞広告費を上回ったというニュースが散見されましたが、マスコミ四媒体とインターネットの広告費について、経年グラフを描いてみました。

経済産業書の特定サービス産業動態統計調査

経済産業省のデータでみるとインターネット広告費は、2009年に雑誌を超えて、2011年には「新聞広告費」3,816億、「インターネット広告費」3,814億と僅差まで迫ってきています。

経済産業省 特定サービス産業動態統計調査より(単位:百万円)
2011年を月別に表したのが、次のグラフになりますが、第1四半期は各媒体共に震災の影響で、自粛ムードもあり広告告出稿も低迷していたことが分かります。また2011年は新聞広告費とインターネット広告費が拮抗していたことが分かります。但し、12月にはインターネット広告費が差を広げているので、2012年は新聞広告費を大きく引き離す可能性はあります。
経済産業省 特定サービス産業動態統計調査より(単位:百万円)

電通ニュースリリース - 2011年広告費

前述したように電通のインターネット広告費は媒体広告費と広告制作費の合算です。この合算額でのインターネット広告費では、既に2009年に新聞広告費を追い抜いています。下記のグラフは、広告制作費を除いた媒体広告費のみを使用して作成した経年グラフとなります。
電通ニュースリリース 平成24年2月23日より(単位:億円)

インターネット広告費は、経済産業省のデータよりも1年早く、2008年に雑誌広告費を超えています。また新聞広告費との比較では、2010年に追いつき、2011年には「新聞広告費」5,990億、「インターネット広告費」6,189億とインターネット広告費が新聞広告を超える額となっています。

2012年の広告費の動向

データとしては、この他に博報堂の月次売上高月次売上高推移(博報堂・大広・読売広告社3社合算)のExcelファイルが掲載されています。こちらは博報堂の業績データなので、このデータから広告費全体を推し量ることは難しいですが、参考値とはなります。

経年データから単純に時系列分析をすれば、インターネット広告費が2012年には、新聞広告費を大きく引き離す結果になると思いますが、2011年の月別のデータ、また、ここ数年の新聞広告費の下げ止まりをみると、広告出稿主側が媒体を使い分けてきているのではないかという感はあります。但し、昨年からスマートファンの利用が増加しているので、スマートフォンに特化した広告が急成長する可能性はあります。

何れにせよ、ニュース記事だけではなく、公開されているデータを自分で加工してトレンドをみることが大切だと思います。

今日の一曲


Everything But The Girl のデビューアルバム『Eden』のギター・デュオで奏でられる音楽は「ネオ・アコ」と言われていましたが、90年代中頃からエレクトリックポップへとシフトしていきます。1996年にリーリスされたアルバム『Walking Wounded』 は、そんな変遷期の1枚です。3曲目に収録されている Single は
、アコースティックとエレクトリックポップの中庸的な位置付けにあり、とても気持ちよく聴ける一曲です。