2019/7/2

イントロダクション

このページは ヒストグラムの作成 with Excel の補足ページです。

READ MORE

度数分布図で層別をおこなう際は,やはり層(群)の別に1つ1つ独立してグラフを描画していくのがbetterかと思いますが,ここではそれ以外の方法すなわち,1つの図でそれらをまとめて表現したい場合に必要とされる一連の手続きについて,例示をしたいと思います。

以下,Excel による層別ヒストグラム|度数多角形の描画法です。ここでは一連の手続きを,「Office365版」 Excel (ver.1811, なお本頁更新時点のバージョンは1905)で追っています。この手続きは,「永続ライセンス版」にいうところの Excel 2019, Excel 2016, Excel 2013 あるいは Excel 2010 でも基本的には同じ流れとなりますが,一部ボタン等の名称やUIが異なる箇所が存在する場合があります(この場合,可能であれば当該箇所に併記していきます)。

元データ

綾子

またまた匿名希望のRS部員仮にAさんとしてAさんからの相談です。

その後,いかがですか?

Mr.A

あ,えーと。これを見てください

元データ
綾子

これは何ですか?

Mr.A

あれから部長がまたヘンなコト言い出して

「出来る営業マンは足で稼ぐもんだ! だいたいお前らにはその基礎がねぇ!」とキレだしたんです。いやそこまではまぁ教訓として言うならば百歩譲っていくらかは納得できる部分もあるにしろまさかその後,僕を含む部の3人に,リアルに歩数計を持たせるとは! 発想がナナメ上すぎてもう言葉もありませんでした。この歩数計ですよ! この!

綾子

Mr.A

この表は,ここ1ヵ月にわたり3人が苦悩し格闘をつづけた(1日あたり歩数の)記録です。

これをヒストグラムにすると,ですねカカタカタ

こんなふうになるんですよ

元データ
綾子

ふむむむ。15000それから20000歩あたりにも山がありますね。健脚というかみなさん,すごいじゃないですか。運動不足の私じゃ,とても無理そうです。

Mr.A

う~んですか? まぁ,ですよね

とんだ理不尽を見返してやろうの一心でみんながんばったんです

綾子

ところで今日のお話というのは,いったい

Mr.A

それですよ!

まずはこれ。これを,層別してみてください。3人の別に。

話は,それからです。

綾子

ん?

じゃあ

層別に度数分布を可視化する場合,やはり第一選択はスケールを統一して当該個分の分布図をそれぞれに作成していくことになるんじゃないかなと思います。

この場合,具体的にはヒストグラムの作成 with Excelで示したような手続きを繰り返しおこなっていくわけですが,ここでは何らかの事情や意図において第一選択を用いることができないor用いないときの手段すなわち1つの図で複数の層を表現したい場合の手続きを見ていきます。

このような場合,ヒストグラムより度数多角形(度数折れ線)をチョイスしたほうが判別に易い点で有効だと思いますが(A・B),周知性を鑑みればヒストグラムを用いたい場合もあるかもしれません(C)。

以下,それらを作成するための手続きです。それぞれにプロセスが異なりますので,目的の図を選択(クリック)して当該部分までジャンプしてください。

PLEASE CHOOSE A TYPE

TYPE A: 度数多角形|面グラフ
TYPE B: 度数折れ線|線グラフ
TYPE C: ヒストグラム|棒グラフ

度数多角形|面グラフ

度数分布表を用意します。目的が度数多角形ですので,必ず最初と最後に度数0(ゼロ)の階級を用意しておきます。ここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法 がbetterかと思います。

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

(この時点で見出しはありませんが)「(階級の)中心値」とすべての層(群)列を選択し

リボンの挿入タブグラフグールプにある折れ線/面グラフの挿入ボタンをクリックします

つづいて,プルダウンからグラフを選択します。

DIFFERENT VERSIONS

2013: 挿入タブグラフグールプにある面グラフの挿入ボタン

2010: 挿入タブグラフグールプにあるボタン

グラフができました。

ここではtype A・B・Cすべての方法でラベルに階級の中心値をつかうとした手前,他の方法のアウトプットともスケールをあわせておく必要があるかなと思います。したがって下図の状態では軸位置が適当ではないので(注:面グラフの軸位置のデフォルトは「目盛」, 棒・線グラフは「目盛の間」),これを修正していきます。横軸上で右クリックから軸の書式設定を選択し(下図上段)

軸位置を目盛の間に変更します(下図下段)。

そのままいずれかの系列を選択し

塗りつぶしを単色にし,任意のを指定します。

また透明度も適宜調整して他の系列との積極的な干渉を和らげます

こうした書式設定を残りすべての系列に対し繰り返しおこなって完成です。

DIFFERENT VERSIONS

2010: [表記の違い] 透明度透過性

度数折れ線|線グラフ

度数分布表を用意します。目的が度数折れ線ですので,必ず最初と最後に度数 0(ゼロ)の階級を用意しておきます。またここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法 がbetterかと思います。

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

層(群)の列記号をすべて選択し,右クリックからあたらしい列を挿入します(挿入)。

あたらしく挿入された列の見出しに,層(群)名をあらためて入力します。

つづいて最初の層(群)名の直下のセル(ここでは セルH2)に

  • CELL H2=IF(OR(AND(K1=0, K2=0, K3=0), AND(ISNUMBER(K1)=FALSE, K2=0, K3=0), AND(K1=0, K2=0, ISNUMBER(K3)=FALSE)), NA(), K2)

と入力し,これをコピーしてあたらしく作成した表の領域を埋めていきます(一部を除いて,原則 度数 0N/Aへ変換するための式。理由は後述)。

(この時点で見出しはありませんが)「(階級の)中心値」と(あたらしく作成した方の)すべての層(群)列を選択し

リボンの挿入タブグラフグールプにある折れ線/面グラフの挿入ボタンをクリックします

つづいて,プルダウンから折れ線 または マーカー付き折れ線グラフの適当な方を選びます(例では「折れ線」で作成します)。

DIFFERENT VERSIONS

2013: 挿入タブグラフグールプにある折れ線グラフの挿入ボタン

2010: 挿入タブグラフグールプにある折れ線ボタン

グラフができました(下図上段)。あとは任意に書式を整えて完成です。

なお22 でN/A処理をしておかないと,グラフは下図下段のようになります。この場合,縦軸0位置に評価する人を惑わすおそれのある(何らの意味を含むといった推測を生む)副産物の水平線が発生します。加えて見た目的にもどこかスマートさに欠けるので,N/A処理は必須と言ってもいいでしょう。

ヒストグラム|棒グラフ

度数分布表を用意します。ここではグラフの横軸に「(階級の)中心値」を表示する用途から,別途ラベル用の列も用意しました。

なおここでのように度数分布を1枚のグラフに層別に表現するような場合には,Frequency関数による方法CountIf関数による方法 がbetterかと思います。

すこし横着な方法ですが手っ取り早いので ラベル用に用意した「(階級の)中心値」列の見出しを一時的に外します(DELETE)。

理由はこれを外すことによってExcelに以下につづく数値をカテゴリデータとして扱ってもらえるからです。

(この時点で見出しはありませんが)「(階級の)中心値」とすべての層(群)列を選択し

リボンの挿入タブグラフグールプにある縦棒/横棒グラフの挿入ボタンをクリックします

つづいて,プルダウンから集合縦棒グラフを選択します。

DIFFERENT VERSIONS

2013: 挿入タブグラフグールプにある縦棒グラフの挿入ボタン

2010: 挿入タブグラフグールプにある縦棒ボタン

いちおうのグラフができました。

つづいて,任意の系列の上で右クリックし,データ系列の書式設定を選択します(他の方法でも)。

系列の重なりを100%にし,要素の間隔を0%に変更します(他の系列も連動します)。

そのまま 塗りつぶしを単色にし,任意のを指定します。

また透明度も適宜調整して他の系列との積極的な干渉を和らげます

こうした書式設定をすべての系列に対し繰り返しおこなって完成です。

DIFFERENT VERSIONS

2010: [表記の違い] 透明度透過性

綾子

おぁあぁ!Aさん! ダントツ上の方陣取ってますね! すごい!

Mr.A

自分で言うのもまぁアレなんですけど,すごいですよね。部長の言うとおり,この期間はさいわい成績もついてきたという感じで

綾子

ホントデスカ!? さすがです!

これじゃ部長もぎゃふんと言うしかないですよ。

Mr.A

それがおっかしいんですよね

ふいにひとこと「いつもお前を見ているぞ」なんてすわった眼をしながら耳元でささやいてきたりするわけで。これ実は部長なりのねぎらいの言葉だったりするんですか? なんともキモチワルサしかないと言うか

綾子

Mr.A

まぁとは言え,気にかけてもらえるようになったんだとしたら,以前に比べれば進歩ですよね!?


あ! 確かによく考えてみれば,そんなフシもありました!

ええとですねひと月ほど前,部長から,実は“商売の守り神” だってほらこのお守りをいただいたんです。

綾子

へぇあの部長さんが,ですか。

実はみんなが言うのと逆で,意外と部下思いなトコがあったんですねー。それもお守りだなんて,見かけによらずやることがかわいいし。

Mr.A

ですよねー。

いや,それだけじゃないっすよ。週に1度,わざわざこのお守り,回収してくれたりしちゃうんです。

綾子

はい!?

Mr.A

なんでもこのカミサマは定期的に特殊なスピリチュアルエネルギーをチャージすることで,持つ人の運気をさらに高めることができるといわれてる,とか

部下を相手にこの親心部長もなんか悪い人じゃないのかなと。

綾子

ちょっと拝見しますねっ!

Mr.A

あぁ! 寺畠さんっ! お守り袋をあけてはダメえええっ! 副作用としてま末代までおそろしくタタられるって部長があぁぁ言ってぇあえええぇっ!!

綾子

んんんっ!? この小さなハコはなんだろう

…ネットで…カタカタ…カタカタ………………ターン…

。これGPSロガー,ですよ。

Mr.A

!!

あのじゃ僕が仕事中ネカフェ行ったことも育毛サロン行ったことも公園で昼寝してたことも牛丼屋で紅ショウガしゃぶりながら3時間ねばったこともひとりカラオケに行ったことも街コンに参加したことも電器街で店員とパーツバトルしてたことも部長ぜんぶがってん承知!?

綾子

コク。

Mr.A