エクセル：次のような計算式（関数）を教えてください。

質問

★（表１）のような男女ソートされていない名簿があります。そこで、

★（表２）のような「人数をカウントする計算式」を作りたいのです。私の知識でもなんとかできそうなのですが、ものすごく複雑かつ、長くなってしまいます。 きっとシンプルな計算式で解決すると思い、ご指導お願い申しあげます。とりいそぎ、セルAB２,AC２,AB３,AC３の４つの式を教えていただけないでしょうか？よろしくお願いいたします。

★（表１）
　　　A　　　　　　　B　　　　　C
１　（氏名）　　　（性別）　（年齢）
２　山田太郎　　　男　　　92
３　川海花子　　　女　　　78
４　山田次郎　　　男　　　65
５　空田三郎　　　男　　　81
６　川村五郎　　　男　　　71
７　川村花恵　　　女　　　68
８　鈴木鈴子　　　女　　　102
　・・・以下約150人

★（表２）
　　AA　　　　　　AB　　　　　　AC
１　年齢　　　男（人数）　　　女（人数）
２　60～64
３　65～69
４　70～74
５　75～79
６　79～84
７　85～89
８　90～94
９　94～100

回答No.2

mshr1962 · ベストアンサー

AB2=SUMPRODUCT(($B$2:$B$1000="男")*($C$2:$C$1000>=60)*($C$2:$C$1000<65))
AC2=SUMPRODUCT(($B$2:$B$1000="女")*($C$2:$C$1000>=60)*($C$2:$C$1000<65))
年齢の上限・下限と男・女のセルを使って参照すればもっと楽になります。

まあ「データ」「ピボットテーブル」で年齢（列）と性別（行）と名前（データ：データの個数）で集計の方が楽かもしれません。
年齢は5歳おきにグループ化すればOKです。

お礼

mshr1962さん、いつもお世話になります。ありがとうございました。

・結果が出ました。

・また、ご指導の通り、上限、下限、男女のセル参照もやってみると簡単にできました。

・SUMPRODUCUT関数について調べて意味も理解できました。ありがとうございました。

回答No.4

noname#204879

結論から言えば、式は１個で事足ります。ただし、表２を少し細工します。

　AA　AB　　　AC　　　 AD
1　年齢　　男(人数) 女(人数)
2 60 ～64　　　　 0　　　　0
3 65 ～69　　　　 1　　　　1
4 70 ～74　　　　 1　　　　0
5 75 ～79　　　　 0　　　　1
6 79 ～84　　　　 1　　　　0
7 85 ～89　　　　 0　　　　0
8 90 ～94　　　　 1　　　　0
9 94 ～100　　　　0　　　　0

１．セル AA2 から下に 60、65、70、… を入力
２．セル AB2 から下に 64、69、74、… を入力して、この範囲を“ユーザー定
　　義”で "～"0 に書式設定
３．セル AC1、AD1 にそれぞれ「男」、「女」を入力して、この範囲を“ユーザ
　　ー定義”で @"(人数)" に書式設定
４．セル AC2 に次式を入力して、此れを範囲 AC2:AD9 に複写

=SUMPRODUCT(($B$2:$B$200=AC$1)*($C$2:$C$200>=$AA2)*($C$2:$C$200<=$AB2))

お礼

ご回答、ありがとうございます。
＃１さんの応用編ですね。ユーザー定義のすごわざに感心しきりです。ありがとうございました。

回答No.3

prairie-gentian

「frequency関数」が簡単でしょう。

結果を算出したい(数式を設定する)範囲を選び、

=frequency(集計するデータの範囲,区分上限範囲)として、「enter」ではなく、「ctrl」＋「shift」＋「enter」(配列関数の入力設定)とすれば出来ます。

ぜひ、お試し下さい。

※「区分上限範囲」とは、お尋ねの例では「64,69,74...」の範囲のことで、「60～64」を一つのセルに書くのではなく、「60～」「64」と別セルにします。

お礼

ご回答ありがとうございました。＃１さんのご回答により解決したこともあり、締め切らせていただきます。補足のお返事を待ちきれずごめんなさい。

　偶然ですが、{を使うのが配列数式であることを本日知りました。その関係のご指導をいただいていたのですね。勉強がんばってみます。ありがとうございました。

補足

ご回答ありがとうございます。
はじめての関数なので、frequency関数のヘルプで勉強していますが、少しわかりかけました。

◆しかし、男女別についての分け方がわかりません　

◆「=frequency(集計するデータの範囲,区分上限範囲)として、「enter」ではなく、「ctrl」＋「shift」＋「enter」(配列関数の入力設定)とすれば出来ます。」
について、ヘルプにも説明がありましたが、理解できませんでした。

もしよろしければ、補足をよろしくお願いいたします。

回答No.1

mshr1962

AB2=SUMPRODUCT(($B$2:$B$1000="男")*($C$2:$C$1000>=60)*($C$2:$C$1000<65))
AC2=SUMPRODUCT(($B$2:$B$1000="女")*($C$2:$C$1000>=60)*($C$2:$C$1000<65))

まあ「データ」「ピボットテーブル」で年齢（列）と性別（行）と名前（データ：データの個数）で集計の方が楽かもしれません。
年齢は5歳おきにグループ化すればOKです。
年齢の上限・下限と男・女のセルを使って参照すればもっと楽になります。