ヒストグラムの作成 with Excel 3/4 [CountIf関数,またはCountIfs関数による方法]
CountIf関数,またはCountIfs関数による方法
特に向いている利用シーン
- 「~以上~未満」の条件で度数をとりたいとき
- 境界の扱いを柔軟に変更できるようにしたいとき
Step 0シチュエーションの設定
この頁でもスポーツクラブXの会員の年齢データを元データとして,ヒストグラムを作成します。ただし,CountIf関数,あるいはCountIfs関数を利用しての方法です。
DL
Step 1MIN・MAXの確認
データをざっと眺めておくか,あるいはシートの任意の場所でMin, Max関数によって最小値・最大値を確認しておきます。後者の場合,下のように入力すると,最小は18,最大は81であることがわかります。
したがってここでは最初の階級を「10歳-19歳」,最後の階級を「80歳-89歳」とし,計8個の階級でヒストグラムを作成したいと思います。
Step 2CountIf系関数で度数を求める場合の考え方
下の図のように,シートに「階級幅」「A群上限」「B群上限」「階級」「度数」等々の見出しをもった表組みを用意しておきます。構成としては目的とするヒストグラムを描画することを“最終段階”としたとき,左2つの列はそれに向けた第1段階と,右の2つの列は同第2段階と位置づけています。
先に決めたとおり階級幅は10で処理します。この値を,「階級幅」ラベル直下にタイプします(以下では言及しませんが,必要によって「A群上限」列の値を自動で作成するために使います)。
さて,CountIf関数は所与の1条件にしたがって要素の数をカウントします。この仕様にもとづいて,次の手順で度数を導くものとします。
それぞれの階級につき,
- D列(シート上では,これをA群と呼びます)で指定する条件の範囲で要素の数を数える
- E列(シート上では,これをB群と呼びます)で指定する条件の範囲で要素の数を数える
- 1.の値と2.の値の差を求める
ことにより度数を求めます。つまり,下図の緑で彩色した条件を設定し処理していけば度数を導くことができます。
複数の条件をAND条件で指定ができるCountIfs関数の場合はさらに単純で,上図の赤で彩色した条件の設定のみで足ります。
いずれにしろ,ここでの設定のように階級を「10歳から19歳まで(10-19)」といった基準で作成したいとして,この範囲で正しく度数をとるために適切な条件を選択した関数の別に与えてやらねばなりません。
具体的にCountIf関数の場合には,上図から判断して
- A群 - 以下
- B群 - 未満
で,
CountIfs 関数の場合には,同じく上図から判断して
- A群 - 以下
- B群 - 以上
で処理してやればよいわけです。これを不等号あるいは等号付きの不等号で,各群の真上のセルにタイプします。たとえばCountIf関数の場合には,下図のように配置します。
そして,A群・B群の値を(計算式か,連続データでつくるかして※)埋めていきます。
※詳細が必要であれば,前頁を参照してください(以下に同じ)。
さらに,グラフ用のラベルも(計算式か,直接タイプするかして※)用意しておきます。
Step 3[CountIf関数の場合]関数を組み立てて度数を求める
全ての準備を終えたので,CountIf関数あるいはCountIfs関数の組み立てをおこないます。
ここでは両者を併記します。まず,CountIf関数の場合
この関数の要求する引数は次のとおりです。
この引数を指定しつつ実際に式を組み立てると,下式のようになります。
H7 | =COUNTIF( $B$2:$B$41, $D$5&D7 )-COUNTIF( $B$2:$B$41, $E$5&E7 ) |
---|
これを見出し「度数」直下のセルに入力して,「階級」列の最下行と同じ行までコピーします。
Step 4[CountIfs関数の場合]関数を組み立てて度数を求める
こちらはCountIfs関数の場合
この関数の要求する引数は次のとおりです。
この引数を指定しつつ実際に式を組み立てると,下式のようになります。
H7 | =COUNTIFS( $B$2:$B$41, $E$5&E7, $B$2:$B$41, $D$5&D7 ) |
---|
これを見出し「度数」直下のセルに入力して,「階級」列の最下行と同じ行までコピーします。
Step 5ヒストグラムを描画する(1)
そのままヒストグラムを作成します。度数分布表を選択し,
Step 6ヒストグラムを描画する(2)
挿入タブ「グラフ」グループの縦棒/横棒グラフの挿入ボタンをクリックします。つづいて「2-D縦棒」グループの集合縦棒をクリックします。
Step 7ヒストグラムを描画する(3)|完成
棒グラフが作成されました。“度数”系列の要素の間隔を「0」にしてヒストグラムの完成です。
なお,この手続きについて参照が必要な場合,先頭頁で解説しています(当該箇所から表示されます)。
その他任意の書式の設定を適宜重ねて,ヒストグラムの完成です。
Next
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」の基本統計ツールに関するHow-toです。