BDAstyle

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

ヒストグラムの作成 with Excel 3/3 [Countif関数による方法]

3.Countif関数による方法

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

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

スポーツクラブXの会員の年齢データをもとに,ここではCountif関数を使ってヒストグラムを作成します。ここでも同様,階級の幅は10とします(10歳きざみ)。

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

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

したがってここでは最初の階級を「10歳-19歳」,最後の階級を「80歳-89歳」とし,計8個の階級でヒストグラムを作成するものとします(「10歳-20歳以下」あるいは「10歳以上-20歳未満」といった条件で作成する場合,Step 2後段の「その他の条件の作成」を参照ください)。

Step 2度数分布表の作成(1)

下の図のように,シートに「境界の扱い」「B群上限」「A群上限」「階級」「度数」の見出しを含む4つの列を作成しておきます。左2つの列は計算用に,右の2つの列はグラフを作る際に使用します。

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

それぞれの階級につき,

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

ことにより度数を求めます。

この手順にしたがって,ここでの設定のように階級を「10歳から19歳まで(10-19)」といった基準で作成する場合,この範囲で正しく度数をとるために適切な条件は,E列が“その階級でとりうる上限の値以下”,D列が“その階級でとりうる下限の値より小さい”となります(下図参照)。

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


すなわち,E・D列の条件式は次のように入力します。

初期データ

    その他の条件の作成

  • ○○超~××以下でカウントしたい
  • 階級を「10歳超20歳以下」といった基準で作成する場合,この範囲で正しく度数をとるために適切な条件は,E・D列ともに“その階級でとりうる上限の値以下”となります(下図参照)。

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

    この場合,E・D列の条件式は次のように入力します。グラフ用の階級表記も適切なものへ変更してください。Step 3以降の手順は変わりません。

  • ○○以上~××未満でカウントしたい
  • また階級を「10歳以上20歳未満」といったルールで作成する場合,この範囲で正しく度数をとるために適切な条件は,E・D列ともに“その階級でとりうる上限の値未満”となります(下図参照)。

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

    この場合には,E・D列の条件式は次のように入力します。こちらもグラフ用の階級表記も適切なものへ変更してください。Step 3以降の手順も,上と同様に変わりません。

Step 3度数分布表の作成(2)

見出し「度数」直下のセルを選択し,数式バーの関数の挿入ボタンをクリックします。

Step 4度数分布表の作成(3)

「関数の挿入」ダイアログが表示されます。

「関数の検索」テキストボックスに「countif」と入力し,検索開始ボタンをクリックします。

Step 5度数分布表の作成(4)

「関数名」に表示された候補から「COUNTIF」を選択し,[OK]ボタンをクリックします。

Step 6度数分布表の作成(5)

「関数の引数」ダイアログが表示されます。

下の図のように,「範囲」には見出しを除くデータ領域を絶対参照で指定し,「検索条件」にはA群の「境界の扱い」に関する記号(絶対参照)と,「A群上限」見出し直下のセルとを半角の「&」記号で結んで指定します。

すべて入力したのち,[OK]ボタンをクリックします。

Step 7度数分布表の作成(6)

数式バーに表示されている数式の最後尾にカーソルを置き,「-」(マイナス)記号を入力します。

そのまま,数式バーの関数の挿入ボタンをクリックします。

Step 8度数分布表の作成(7)

ふたたびCountif関数の「関数の引数」ダイアログを呼び出し,「範囲(絶対参照)」「検索条件」について下の図のように指定します。

ここで指定する「検索条件」は,B群の「境界の扱い」に関する記号(絶対参照)と,「B群上限」見出し直下のセルとを半角の「&」記号で結んだものです。

すべて入力できたら,[OK]ボタンをクリックします。

Step 9度数分布表の作成(8)

入力した計算式を階級の数だけコピーします。度数分布表は以上で完成です。

Step 10ヒストグラムの作成(1)

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

Step 11ヒストグラムの作成(2)

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

Step 12ヒストグラムの作成(3)

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

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

Next

次頁は,境界値を階級の間に表示する形式へヒストグラムを加工する方法についてです。

その他の参照