事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

ヒストグラム(度数分布表)を作成する(その2)"COUNTIF関数" を使う

ヒストグラム(度数分布表)の作成方法は複数あります

 ヒストグラム( histogram )は、縦軸に度数、横軸に階級をとった統計グラフの一種で、データの分布状況を視覚的に認識することができるので、「パレート図」などと同様に品質管理などに使われます。

 

度数分布表の「階級」を設定する

 (その1)の、FREQUENCY関数を使って度数分布表を作成する方法は少ない工程数で分布表が作成できる方法です。

 しかし、FREQUENCY関数を使う方法は "階級 の指定方法" 「〇〇以上)、△△以下」に限られます。(※ 最初(最後)の階級は「以下(以上)」)

 COUNTIF関数(COUNTIFS関数)を使えば、 "階級を詳細に指定" することができます。

 

「階級」の種類

 "階級" の種類は複数あります。

 「以上」「以下」「未満」「超」の使われ方で、COUNTIF関数の「引数」の設定方法が変わります。

  1. 50以上、59以下(階級 50~59)
  2. 50超、60以下(階級 51~60)
  3. 50以上、60未満 (階級 50~59)

 

"COUNTIF関数" と "COUNTIFS関数"の違い

 COUNTIFS関数Excel2007で追加された比較的新しい関数です。

 従来のCOUNTIF関数では、2つのCOUNTIF関数を使って引き算をして求める知りたい階級の "度数" をカウントする必要がありました。

  • COUNTIF(元データ , 条件1)ー COUNTIF(元データ , 条件2

 

 COUNTIFS関数"2つの条件" を引数にすることができるのでより簡単に数式を作成できるようになりました。

  • COUNTIFS(元データ , 条件1 , 元データ , 条件2

  

1.50以上、59以下階級 50~59)

 COUNTIF関数"59以下のデータの数" をカウントして、そこから "50未満のデータの数"を除する(除く)ことで、"50以上、59以下のデータの数" を求めます。

   f:id:k-ohmori9616:20200722113652p:plain

   

2.50超、60以下(階級 51~60)

 COUNTIF関数"60以下のデータの数" をカウントして、そこから "50以下のデータの数"を除する(除く)ことで、"50以上、60以下のデータの数" を求めます。

   f:id:k-ohmori9616:20200720162855p:plain

 

3.50以上、60未満(階級 50~59)

 COUNTIF関数"60未満のデータの数" をカウントして、そこから "50未満のデータの数"を除する(除く)ことで、"50以上、60未満のデータの数" を求めます。

   f:id:k-ohmori9616:20200720162925p:plain

 

"COUNTIF関数" を使って「度数」をカウントする

 COUNTIF関数「階級」ごとの「度数」を求める方法は、"大きい数値の上限" のデータ数をカウントして、そこから "小さい数値の上限" のデータの数を除く(叙する)ことで、調べたい「階級」の「度数(数)」を算出します。

  COUNTIF関数の「引数」は、

  COUNTIF(元データ , 条件)

 

1.50以上、59以下階級 50~59)

 "度数" を表示する範囲の一番上のセルに "数式" を入力して、その内容を下方向にコピーします。

   数式 =COUNTIF($C$3:$C$15,$F$2&F3)-COUNTIF($C$3:$C$15,$E$2&E3)

  ※ "<=" や "<" をセルに入力しておき、数式で「そのセル」を指定します

  ※ "&" を使って「上限」の数値と "<=" を入力したセルを連結

  1. "元データ" を選択
  2. "<=" 以下を入力したセルを選択
  3. "大きい方の上限" のセルを選択
  4. "<" 未満を入力したセルを選択
  5. "小さいほうの上限" のセルを選択

 ※ "<=" と "<" をセルに入力しておき、数式でセルを指定します

 

   f:id:k-ohmori9616:20200721083931p:plain

f:id:k-ohmori9616:20200722084325p:plain

2.50超、60以下(階級 51~60)

  数式 =COUNTIF($C$3:$C$15,$F$2&F3)-COUNTIF($C$3:$C$15,$E$2&E3)

  1. "元データ" を選択
  2. "<=" 以下を入力したセルを選択
  3. "大きい方の上限" のセルを選択
  4. "<=" 以下を入力したセルを選択
  5. "小さいほうの上限" のセルを選択

   f:id:k-ohmori9616:20200721094828p:plain

f:id:k-ohmori9616:20200722084511p:plain

 

3.50以上、60未満(階級 50~59)

 数式 =COUNTIF($C$3:$C$15,$F$2&F3)-COUNTIF($C$3:$C$15,$E$2&E3)

  1. "元データ" を選択
  2. "<=" 以下を入力したセルを選択
  3. "大きい方の上限" のセルを選択
  4. "<=" 以下を入力したセルを選択
  5. "小さいほうの上限" のセルを選択

   f:id:k-ohmori9616:20200721100244p:plain

f:id:k-ohmori9616:20200722084903p:plain

 

"COUNTIFS関数" を使って「度数」をカウントする

 "COUNTIFS関数" の引数には、複数の条件を設定することができます。

 これにより、COUNTIF関数を使う場合より数式が単純になります。

  COUNTIFS(元データ , 条件1 , 元データ , 条件2

 条件1には "「大きい方の上限」<=(以下)"を設定し、条件2には"「小さいほうの上限」 >=(以上)" を設定します。

  数式 =COUNTIFS($C$3:$C$15,$F$2&F3,$C$3:$C$15,$E$2&E3)

  1. "元データ" を選択
  2. "<=" 以下を入力したセルを選択
  3. "大きい方の上限" のセルを選択
  4. ">=" 以上を入力したセルを選択
  5. "小さいほうの上限" のセルを選択

   f:id:k-ohmori9616:20200721101912p:plain

f:id:k-ohmori9616:20200722085846p:plain

 

 COUNTIF関数(COUNTIFS関数)を使うことにより、「階級」を自在に設定することができます。

 FREQUENCY関数関数とは異なり階級の "上限"と"下限"を自由に設定できるので、階級ごとに"階級の幅を変更”することもできます。

 ① 40以上~50未満(幅は10)

 ② 50以上~80未満(幅は30)

 ③ 80以上

 

 その3では、「ピボットテーブル」を使って "ヒストグラム(度数分布表)” を作成する方法を説明します。

 

 

k-ohmori9616.hatenablog.com