excel 顧客人数の年代別集計

excel2010を使用しています。
現在、会員のお客様の年代別集計表を作成しています。
Ａ列に小学生以下、中学生、高校生、大学生以上と4項目に分けて該当する年代のお客様の人数の集計をしております。

<シート１>
Ａ列　　　　　　Ｂ列 C列 Ｄ列　Ｅ列
小学生以下　　　　 ～　 12
中学生　　　　 13　 ～　 15
高校生　　　　 16 　 ～　 18
大学生以上　 19 　 ～

<シート2>
Ａ列　　　　　　Ｂ列　　　　　　　　　　Ｃ列
会員No. 　　 生年月日　　　　　　　　年齢
　　Ａ　　　19XX/XX/XX　　=datedif(B2,today(),"Y")
　　Ｂ　　　19YY/YY/YY 　 =datedif(B3,today(),"Y")
　　Ｃ 　　 19ZZ/ZZ/ZZ 　 =datedif(B4,today(),"Y")

としてシート1のＥ1:E4にFREQUENCY関数を用いて{=frequency(Sheet2!C2:C4,Sheet1!D1:D3)}と、年齢毎の人数を求める表を作成したのですが、12歳でも中学生、15歳でも高校生、大学生でも18歳の方がいらっしゃるわけで、このままでは正しい結果が得られない為、現在頭を悩ませております。

どのようにすればシート1のＡ列に記載している通りの年代のお客様がシート1のＥ列に表示出来ますでしょうか？
上手く状況が説明出来ていないかもしれませんが、皆様のお知恵を拝借させていただけますと幸いです。

No.3 ベストアンサー 回答者： kagakusuki 回答日時： 2014/02/18 04:22

>何度入力し直しても

>中学生の方は1900/1/0
>高校生の方は1900/1/2
>と表示されてしまいます。

　それは、セルの書式設定の表示形式が[日付]となっているためです。
　Excelではシリアル値と言って、日付けや時刻のデータを、1899年12月31日の午前0時(Excel2007よりも前のMac版のExcelでは1904年1月1日の午前0時)から経過した日数として扱っています。
　そのため、セルの書式設定が日付となっている場合には、1や2といった単なる数値が、日付けとして表示されてしまうのです。

　ですから、次の様な操作を行って、人数を表示させるセルの書式設定の表示形式を[G/標準]か[数値]に設定して下さい。

年代のお客様の人数を表示させるセルをまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[標準]か又は[数値]をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

【参考URL】
　楽遊の部屋 > Excel超入門 > (1)セルの書式設定方法
　　http://homepage3.nifty.com/gakuyu/excel/syosiki. …



　それと、「1900/1/0」や「1900/1/2」と表示されてしまう問題とは全く別の話になるのですが、回答No.1及び回答No.2において提示させて頂いた関数には、見落としていた点があるため、今日の日付が1月～3月である場合には、年度が1年ずれてしまうという欠陥が御座いました。
　ですから、各関数を次のものと差し替えて下さい。


【小学生以下の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,">="&YEAR(TODAY())-12-(MONTH(TODAY())<4)&"/4/3")


【中学生の人数を求める関数】

=COUNTIFS(Sheet2!$B:$B,">="&YEAR(TODAY())-15-(MONTH(TODAY())<4)&"/4/3",Sheet2!$B:$B,"<"&YEAR(TODAY())-12-(MONTH(TODAY())<4)&"/4/3")

或いは

=COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-12-(MONTH(TODAY())<4)&"/4/3")-COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-15-(MONTH(TODAY())<4)&"/4/3")


【高校生の人数を求める関数】

=COUNTIFS(Sheet2!$B:$B,">="&YEAR(TODAY())-18-(MONTH(TODAY())<4)&"/4/3",Sheet2!$B:$B,"<"&YEAR(TODAY())-15-(MONTH(TODAY())<4)&"/4/3")

或いは

=COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-15-(MONTH(TODAY())<4)&"/4/3")-COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-18-(MONTH(TODAY())<4)&"/4/3")


【大学生以上の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-18-(MONTH(TODAY())<4)&"/4/3")

この回答へのお礼

お返事が遅くなり申し訳ありません！
教えていただいた通りに入力し無事に表が完成しました！
この度は詳しく丁寧に教えていただき誠にありがとうございました！

お礼日時：2014/02/18 21:34

No.2 回答者： kagakusuki 回答日時： 2014/02/17 22:15

　御質問文では

>excel2010を使用しています。

となっておりましたので、COUNTIFS関数を使用する例を提示させて頂いたのですが、


>早速やってみたのですが、職場のPCはexcel2002だった為、中学生の場合と高校生の場合のCOUNTIFSが使用出来ませんでした…

>他の関数で代用出来ませんでしょうか…？


という事でしたら、次の様にされると良いと思います。


【中学生の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-12&"/4/3")-COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-15&"/4/3")


【高校生の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-15&"/4/3")-COUNTIF(Sheet2!$B:$B,"<"&YEAR(TODAY())-18&"/4/3")

この回答への補足

迅速な回答ありがとうございます！
説明が不足しておりました申し訳ありません。
自宅のexcelは確かに2010だったのですが、職場のexcelは2002でした…
早速説明いただいたように入力してみたのですが、何度入力し直しても
中学生の方は1900/1/0
高校生の方は1900/1/2
と表示されてしまいます。
何か設定が間違っているのでしょうか？

補足日時：2014/02/17 23:12

No.1 回答者： kagakusuki 回答日時： 2014/02/17 18:25

　高校や大学の場合、入学出来ずに浪人する事もあるため、16歳以上で高校生でも大学生でもない人や、19歳以上の高校生もいますし、必ずしも高校に入学しなければならないという訳でもないため、中学や高校を卒業した直後に社会人となる人などもいますから、必ずしも年齢や生年月日だけでは、小学生以下、中学生、高校生、大学生以上等に分ける事は出来ないと思います。

　その様なパターンがある事を無視して、取り敢えずの話として、全員が必ず浪人する事無く大学まで進学するものとした場合には、次の様な関数となります。


【小学生以下の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,">="&YEAR(TODAY())-12&"/4/3")


【中学生の人数を求める関数】

=COUNTIFS(Sheet2!$B:$B,">="&YEAR(TODAY())-15&"/4/3",Sheet2!$B:$B,"<"&YEAR(TODAY())-12&"/4/3")


【高校生の人数を求める関数】

=COUNTIFS(Sheet2!$B:$B,">="&YEAR(TODAY())-18&"/4/3",Sheet2!$B:$B,"<"&YEAR(TODAY())-15&"/4/3")


【大学生以上の人数を求める関数】

=COUNTIF(Sheet2!$B:$B,">="&YEAR(TODAY())-19&"/4/3")



【参考URL】
　文部科学省 > 教育 > 小学校、中学校、高等学校 > 小・中学校への就学について > 就学事務Q＆A > 1．4月1日生まれの児童生徒の学年について
　　http://www.mext.go.jp/a_menu/shotou/shugaku/deta …

この回答への補足

回答ありがとうございます！
早速やってみたのですが、職場のPCはexcel2002だった為、中学生の場合と高校生の場合のCOUNTIFSが使用出来ませんでした…
情報が不足しており、大変失礼致しました！
SUMPRODUCTで色々試しましたがうまくいきません。他の関数で代用出来ませんでしょうか…？

補足日時：2014/02/17 20:14