BDAstyle

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

ヒストグラムの作成 with Excel 3/4 [CountIf関数,またはCountIfs関数による方法]

CountIf関数,またはCountIfs関数による方法

特に向いている利用シーン

  • 「~以上~未満」の条件で度数をとりたいとき
  • 境界の扱いを柔軟に変更できるようにしたいとき

Step 0シチュエーションの設定

この頁でもスポーツクラブXの会員の年齢データを元データとして,ヒストグラムを作成します。ただし,CountIf関数,あるいはCountIfs関数を利用しての方法です。

DL

COUNTIF関数, COUNTIFS関数|元データ

Step 1最低値・最高値の確認

データをざっと眺めておくか,あるいはシートの任意の場所でMin, Max関数によって最小値・最大値を確認しておきます。後者の場合,下のように入力すると,最小値は18,最大値は81であることがわかります。

したがってここでは最初の階級を「10歳-19歳」,最後の階級を「80歳-89歳」とし,計8個の階級でヒストグラムを作成したいと思います。

最低・最高の確認

Step 2CountIf系関数で度数を求める場合の考え方

下の図のように,シートに「階級幅」「A群上限」「B群上限」「階級」「度数」等々の見出しをもった表組みを用意しておきます。構成としては目的とするヒストグラムを描画することを“最終段階”としたとき,左2つの列はそれに向けた第1段階と,右の2つの列は同第2段階と位置づけています。

第1段階|弾2段階表の見出しを準備

先に決めたとおり階級幅は10で処理します。この値を,「階級幅」ラベル直下にタイプします(―――以下では言及しませんが,必要によって「A群上限」列の値を自動で作成するために使います)。

階級幅:10

さて,CountIf関数は所与の1条件にしたがって要素の数をカウントします。この仕様にもとづいて,次の手順で度数を導くものとします。

それぞれの階級につき,

  1. D列(シート上では,これをA群と呼びます)で指定する条件の範囲で要素の数を数える
  2. E列(シート上では,これをB群と呼びます)で指定する条件の範囲で要素の数を数える
  3. 1.の値と2.の値の差を求める

ことにより度数を求めます。つまり,下図の緑で彩色した条件を設定し処理していけば度数を導くことができます。

49以下の人を数えておいて→40未満の人を差し引くと→40~49の人だけ残る

複数の条件をAND条件で指定ができるCountIfs関数の場合はさらに単純で,上図の赤で彩色した条件の設定のみで足ります。

いずれにしろ,ここでの設定のように階級を「10歳から19歳まで(10-19)」といった基準で作成したいとして,この範囲で正しく度数をとるために適切な条件を選択した関数の別に与えてやらねばなりません。

具体的にCountIf関数の場合には,上図から判断して

  • A群 - 以下
  • B群 - 未満

で,

CountIfs 関数の場合には,同じく上図から判断して

  • A群 - 以下
  • B群 - 以上

で処理してやればいいわけです。これを不等号あるいは等号付きの不等号で,各群の真上のセルにタイプします。たとえばCountIf関数の場合には,下図のように配置します。

A群:<=, B群:<

そして,A群・B群の値を(計算式か,連続データでつくるかして※)埋めていきます。

※詳細が必要であれば,前頁を参照してください(以下に同じ)。

ヒストグラム countif,countifs

さらに,グラフ用のラベルも(計算式か,直接タイプするかして※)用意しておきます。

10-19, 20-29, 30-39, ...

そのほかの条件で作成したいとき
○○超~××以下でカウントしたい

階級を「10歳超20歳以下」といった基準で作成する場合,正しく度数をとるためには下図のように処理します。

50以下の人を数えておいて→40以下の人を差し引くと→40超~50以下の人だけ残る

上図の条件をシート上で構成すると,下図のようになります(CountIf関数の場合)。

超以下の場合のシート構成(countif版)
○○以上~××未満でカウントしたい

また階級を「10歳以上20歳未満」といった基準で作成したい場合には,正しく度数をとるために下図のように処理します。

50未満の人を数えておいて→未満の人を差し引くと→40以上~50未満の人だけ残る

上図の条件をシート上で構成すると,下図のようになります(CountIf関数の場合)。

以上未満の場合のシート構成(countif版)

Step 3[CountIf関数の場合]関数を組み立てて度数を求める

全ての準備を終えたので,CountIf関数あるいはCountIfs関数の組み立てをおこないます。

ここでは両者を併記します。まず,CountIf関数の場合―――――

この関数の要求する引数は次のとおりです。

=CountIf(元データ,条件)

この引数を指定しつつ実際に式を組み立てると,下式のようになります。

H7 =COUNTIF( $B$2:$B$41, $D$5&D7 )-COUNTIF( $B$2:$B$41, $E$5&E7 )

これを見出し「度数」直下のセルに入力して,「階級」列の最下行と同じ行までコピーします。

[セル]H7に入力→最下行までコピー

Step 4[CountIfs関数の場合]関数を組み立てて度数を求める

こちらはCountIfs関数の場合―――――

この関数の要求する引数は次のとおりです。

=CountIfs(元データ,条件1, 元データ,条件2)

この引数を指定しつつ実際に式を組み立てると,下式のようになります。

H7 =COUNTIFS( $B$2:$B$41, $E$5&E7, $B$2:$B$41, $D$5&D7 )

これを見出し「度数」直下のセルに入力して,「階級」列の最下行と同じ行までコピーします。

[セル]H7に入力→最下行までコピー

Step 5ヒストグラムを描画する(1)

そのままヒストグラムを作成します。度数分布表を選択し,

countifまたはcountifs関数で作った度数分布表

Step 6ヒストグラムを描画する(2)

挿入タブ「グラフ」グループの縦棒/横棒グラフの挿入ボタンをクリックします。つづいて「2-D縦棒」グループの集合縦棒をクリックします。

ヒストグラム countif,countifs

Step 7ヒストグラムを描画する(3)|完成

棒グラフが作成されました。“度数”系列の要素の間隔を「0」にしてヒストグラムの完成です。

なお,この手続きについて参照が必要な場合,先頭頁で解説しています(当該箇所から表示されます)。

挿入された棒グラフ。柱の間隔が残っている状態→無間隔に

その他任意の書式の設定を適宜重ねて,ヒストグラムの完成です。

countifまたはcountifs関数で作ったヒストグラム

Next

次頁は Excel2016 の新機能でのつくりかたです。

その他の参照