【Excel】Excel関数で簡単！度数分布表とヒストグラムを作ろう～FREQUENCY関数～

皆さん、こんにちは。初投稿のとんすけです。
夏野菜が美味しい季節になりましたね。
とんすけは「つるむらさき」という野菜が好物で、毎年夏にスーパーに現れるのを心待ちにしています。

さて、今回はExcelで度数分布表を作る時に役立つ、「FREQUENCY関数」をご紹介します。
ばらばらのデータを入手したものの、どこから手をつけたらよいか分からない…。
そんなときにはひとまず区間ごとに集計してみると、データの分布を大まかに把握することができます。

FREQUENCY関数とは

FREQUENCY関数は区間ごとのデータ個数を求める関数です。
これを使えば簡単に度数分布表やヒストグラムを作成することができます。

関数の入力方法は以下の通りです。
”=FREQUENCY(データ配列, 区間配列)”
データ配列、区間配列ともに縦方向である必要があるので注意してください。

それでは、具体的な使い方を見ていきましょう。

FREQUENCY関数を使った度数分布表の作り方

今回は薬剤Aの6月の処方量を、10mgごとに区切って集計してみます。
区間の幅の決め方については後ほどご紹介します。

※処方量(mg)は１処方当たりの薬剤の処方量を表しています。
1.区間配列（階級）を入力します。入力した値が区間の上限値になります。
今回の場合、階級「20」には「10mgより大きく、20mg以下」のデータがカウントされることになります。
もし「10mg以上20mg未満」で集計したい場合は区間配列を「9.9」、「19.9」等で入力していきます。
最後の階級は空白にしておくことで自動的に最大の階級より大きいデータをカウントします。

2.度数分布を入れるセル範囲を選択します。
3.数式バーに「=FREQUENCY(データ配列, 区間配列)」を入力します。
今回、度数分布を知りたいデータ配列は「処方量(mg)」、区間配列は先程1.で設定した階級です。
4.関数をコピーします。
図ではCtrl+Shift+Enterキーで参照範囲を固定してコピーをしていますが、
F4キーで参照範囲を固定してコピーしても構いません。
階級ごとの度数を求めることができました。

ヒストグラムにしてみよう

完成した度数分布表の階級を横軸、度数を縦軸として縦棒グラフにすれば、ヒストグラムを簡単に作成できます。もう少しヒストグラムらしい見た目にしていきましょう。

• データ系列の書式設定＞系列のオプション＞要素の間隔を０％に
• 軸の書式設定＞軸のオプション＞目盛間隔を調整
• その他、お好みで色や表示を変更して…

完成です！

今回の処方量(mg)は１処方当たりの処方量なので、度数を「処方数(枚)」として表示しました。

階級幅の決め方について

度数分布表やヒストグラムを作る時に問題となるのが、階級の数と幅です。
どのくらいに設定したらいいのか分からない…。そんなときに目安として使えるのが「スタージェスの公式」です。
データ数ｎ、階級数ｋとすると次のような式になります。
k=１+log₂n
これもエクセルで計算してしまえば簡単です。logの計算には以下の式を使います。
”=LOG(n, [底])”　（底を省略すると底10になります）
今回の場合なら、

13階級くらいが目安のようです。
今回は階級をあらかじめ決めていましたが、特に決まっていない場合は参考に計算してみると良さそうです。

累積相対度数グラフの追加

せっかくなので、累積相対度数表とそのグラフも作ってみましょう。
累積相対度数を使うことで、その階級までに含まれるデータの割合を知ることができます。

1.相対度数を計算していきます。
度数の合計を出し、

相対度数＝度数/度数の合計を階級ごとに出していきます。

2.累積相対度数を求めます。
一番上の階級から順に、相対度数を足し上げていきます。

あとはオートフィル機能を使って…

累積相対度数表が完成しました。

3.先程作成したグラフを選択し、デザイン＞データの選択から、完成した累積相対度数表を追加します。
系列”累積相対度数”のデータ系列の書式設定＞系列のオプションから「第２軸」を選択します。
現れた棒グラフを右クリックして系列グラフの種類の変更＞「マーカー付き折れ線」を選択します。

• 第2軸を選択し、軸の書式設定＞軸のオプションから最大値を1.0に、目盛間隔を0.2に変更
• その他、お好みで色等を変えていけば…

累積相対度数付きグラフの完成です！

今回の場合では、0～60mgの範囲に全体の約70%が存在することが分かります。

まとめ

度数分布表やヒストグラムを使うとデータの構造が一目で分かります。
多数のデータを理解したい時、まずはここから始めてみてはいかがでしょうか。
是非ご活用いただけたら幸いです。