出産前後の痔にはご注意!

こんにちは皆さんのいつも参考にさせて頂いております。
実際はデータリストは別シートですが下記の用にして男女別の平均年齢を計算したいと考えています。
 DAVERAGEを使った方法、SUMを使った配列、COUNTIF・SUMPRODUCTを使った抽出などいくつ試しましたが、どうも上手くできませんでした。なにか良い方法があればご指導いただければ思います。


  A   B   C  D E  F
1 名前  性別  年齢   平均年齢
2 山田  男   52   男  ○○歳
3 林   女   63   女  ○○歳
4 木村  男   66
5 佐藤  女   24
6 伊藤  女   33

このQ&Aに関連する最新のQ&A

A 回答 (4件)

ANo.3です


sumif関数及びcountif関数ですが、計算では空白部分を無視しますので
空白のところは何も入れなくていいです

参照するフィールドが離れているということと、結果を書くシートが別
ということでしたので以下の式になります。

watawatasaさんの表ですと、
男性の場合
=TEXT(SUMIF(sheet2!B2:B6,"男",sheet2!C2:C6)/COUNTIF(sheet2!B2:B6,"男"),"0歳")
女性の場合
=TEXT(SUMIF(sheet2!B2:B6,"女",sheet2!C2:C6)/COUNTIF(sheet2!B2:B6,"女"),"0歳")

となります。
なお見やすくするため$は省いています。

性別フィールドと年齢フィールドのデータ数は一致させておいてください
でないと、平均が狂います。

結構大変でしょうけど、がんばってください。
    • good
    • 0
この回答へのお礼

K_moriさん おかげさまで無事に成功しました。

数値が異常に小さい原因は、ご指摘の通り性別フィールドが入力済みで年齢フィールドが未入力のレコードが沢山あったためでした。
 対策として
=if(年齢フィールド="","",性別フィールド)
という集計用の列を一つ作ってそちらを性別の対象フィールドとしました。

 丁寧かつ的確なアドバイスを頂き本当にありがとうございました。

お礼日時:2008/05/04 23:57

こんにちは


男女別の平均年齢を出すには、sumif関数とcountif関数の複合で
出ます。
watawatasaさんの表で作成すると以下の式になります

男姓の平均年齢
=SUMIF($B$2:$C$6,"男",$C$2:$C$6)/COUNTIF($B$2:$B$6,"男")

女性の平均年齢
=SUMIF($B$2:$C$6,"女",$C$2:$C$6)/COUNTIF($B$2:$B$6,"女")

同じようにOO歳とつけたい場合は以下の式です

男性の場合
=TEXT(SUMIF($B$2:$C$6,"男",$C$2:$C$6)/COUNTIF($B$2:$B$6,"男"),"0歳")

女性の場合
=TEXT(SUMIF($B$2:$C$6,"女",$C$2:$C$6)/COUNTIF($B$2:$B$6,"女"),"0歳")

シートが別な場合は以下の式です
データが入っているシート名が"sheet2"の場合

男性の場合
=TEXT(SUMIF(sheet2!$B$2:$C$6,"男",sheet2!$C$2:$C$6)/COUNTIF(sheet2!$B$2:$B$6,"男"),"0歳")

女性の場合
=TEXT(SUMIF(sheet2!$B$2:$C$6,"女",sheet2!$C$2:$C$6)/COUNTIF(sheet2!$B$2:$B$6,"女"),"0歳")

これで出ると思います。
関数は難しいですが、使い慣れると便利です。
がんばってください。

この回答への補足

大変丁寧でわかりやすいご回答を頂きありがとうございました。

早速、実際の表で試して試して見たところ、予想以上に小さな数字が出てきてしまいました。私なりに原因を考えてみたのですが、
・まず、実際の表は例示よりも多くの項目があり、性別フィールド(?)と年齢フィールドが離れていること
・次に、元になる250件ほどあるリストが入力途中で1/4程度空白があることです。

せっかく、アドバイス頂いたのに良い報告ができず申し訳ありません。
もしかすると、条件書式や関数で空白に"未入力"とか入れたら良いのでしょうか?

度々の質問で恐れ入りますが、宜しくお願いします。

補足日時:2008/05/04 21:06
    • good
    • 0

 


計算式を間違ってました
=SUMPRODUCT((B1:B6="男")*C1:C6)/COUNTIF(B1:B6,"男")
これが正解、C8とタイプミスしてました。

 

この回答への補足

早速のアドバイスを頂きありがとうございました。

実際の表で早速試してみた所、「$value!」のエラーが出てしまいました。私なりに次の原因を考えてみました。
・実際のリストは例示より項目が多く、年齢(c)と性別(au)が離れていること
・分析シートとリストシートを同時進行で作っているため、空白セルが1/4程度あること

なお、実際に入力は以下のように行いました(Excel 2000使用、読みやすくするために$は削除)

=SUMPRODUCT((一覧!C2:C9999="男")*一覧!aU2:AU9999)/COUNTIF(一覧!C2:C9999,"男")

なんかイージーミスかもしれませんが、お気づきの点がありましたら、アドバイス頂ければ幸いです。

補足日時:2008/05/04 21:27
    • good
    • 0
この回答へのお礼

n4330さん アドバイス頂きありがとうございました。
今回は、No3 k_moriさんの方法で行うことにさせて頂きました。

sumproduct関数は、今後に向けて何とか使いこなせるようになりたいと思っています。また、機会がありましたら宜しくお願いします。

お礼日時:2008/05/05 20:59

 


=SUMPRODUCT((B1:B6="男")*C1:C8)/COUNTIF(B1:B6,"男")

女なら"男"を"女"に変えて下さい。

 
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで平均年齢や平均勤続年数を求めるには?

エクセルで複数の構成員の平均年齢や平均勤続年数を求めるにはどうしたらいいのでしょうか?できれば、何年何ヶ月何日まで出せるといいのですが。

Aベストアンサー

それぞれの年齢と勤続年数の列を、日付関数を使ってつくります。関数ボックスにはない「DATEDIF関数」です。
=DATEDIF(A1,TODAY(),"D") ※A1は、生年月日、入社年月日が入力されているセルに書き換えてください。
平均値は「AVERAGE関数」で計算しましょう。=AVERAGE(C1:C4)
セルの書式をユーザー定義で「yy"歳"mm"ヵ月"dd"日"」や「yy"年"mm"ヵ月"dd"日"」などお好みに設定してください。

余談ですが、DATEDIF関数の第3引数「D」は日数を指定しています。年数なら「Y」、月数なら「M」です。解説しているサイトがありますので、検索してみてください。あと、年齢計算は =DATEDIF(A1,TODAY()+1,"D") の方が自然なのかな?

Qエクセルの関数で平均年齢の出し方を教えてください。

エクセルでTODAYを元に年齢を
=DATEDIF(H5,$J$3,"Y")&"歳"&DATEDIF(H5,$J$3,"YM")&"ヶ月"
のようにして○歳○ヶ月というようにセルに表示されるよう関数
で求めています。

この○歳○ヶ月のセル列の最後に平均年齢を求めたいのですが、
=AVERAGE(範囲)では求められないので
どのようにすえば求められるのか教えていただけないでしょうか?

宜しくお願いたします。

Aベストアンサー

次のような式でどうでしょう。
=DATEDIF(AVERAGE(H:H),$J$3,"Y")&"年"&DATEDIF(AVERAGE(H:H),$J3,"YM")&"ヶ月"

Q勤続年数の平均を求めたい時の関数

エクセルで会社の従業員の勤続年数とその平均を求めたいです。
勤続年数のセルに
=DATEDIF(A1,TODAY(),"y")&"年"&DATEDIF(A1,TODAY(),"ym")&"ケ月"
といれて勤続年数を出してます。
それで次に従業員の勤続年数の平均を出したいのですが、AVERAGEを使うとエラー#DIV/0!が出ます。
どうすれば勤続年数とその平均が求められますか?
どんな方法でも良いので教えて下さい!!非常に困ってます(ー_ー)!!

Aベストアンサー

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で、出てくるのは勤続延べ何ヶ月ですから、こいつを12で割って「年」
余りを「月」にする必要があります。平均月が出たセルをB1として、

="平均"&INT(B1/12)&"年"&(B1-INT(B1/12)*12)&"ケ月"

こんな感じで表示できるはずです。
キモは「計算に使うセルと表示するセルを分ける」ということです。

計算に使うセルが表示上邪魔な場合は「書式」「列」「表示しない」
などで表示上隠す方法と、AA1~などの極端に離れたセルに記入して
おく方法などがあります。

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で...続きを読む

QExcel 性別毎の年代別件数の求め方

Excelで、性別毎の年代別件数を求めたいのですが、良い方法が見つかりません。
皆様のお力をお貸し下さい。

業務で、お客様の統計を採るよう命じられました。

A列に性別(女性or男性)、B列に年齢(18~99)が入力されています。
性別はCOUNTIF関数を使い、女性と男性の件数を出しました。
年齢はFREQUENCY関数を使い、年代(10代~90代)別に件数を出しました。
今度は、性別毎に年代別件数を求めるよう指示されたのですが
一日考えても良い方法が見つかりません。
どのようにすれば、性別毎の年代別件数を求めることが出来るのでしょうか?

利用月毎に複数のファイルに分かれており、集計用のファイルを作成し
全てのファイルの合計を集計するよう指示されています。
(利用月毎のファイルは12ヶ月分あります・・・)
Sheet1に性別と年齢を入力し、Sheet2でCOUNTIF関数、FREQUENCY関数を組み集計しています。

良いお知恵がありましたら、どうぞ御教授下さい。

Aベストアンサー

単純なのは「データ」「ピボットテーブル」で年齢をグループ化する方法ですね。

関数で行う場合は
=SUMPRODUCT(($A$2:$A$100="男")*($B$2:$B$100>=18)*($B$2:$B$100<20))
上記で18~19歳の男の数が集計できます。
性別と年齢の上下をセル参照すれば楽だと思いますよ。

Q年齢の平均を求めたいのですが

EXCELを使って表計算をしています。
次のような、年と月が合わさった年齢の平均の求め方を教えてください。
(例)Aくん 7歳6か月
   Bくん 8歳10か月
   Cさん 5歳1か月
      ・
      ・
      ・
   平均:( ○歳○か月)

ただ今、名前と年齢(8-10の形で)が入力されているのですが、この形から平均を出せるでしょうか?教えてくださいませ。  

Aベストアンサー

たとえば、セルA2に8歳10か月というデータが、'8-10 という文字列の形で入っているとします。

B2に =FIND("-",A2)
C2に =LEFT(A2,B2-1)
D2に =MID(A2,B2+1,999) '999は適当です
E2に =C2*12+D2

3行目から下には、これらの式を必要なだけコピーします。

こうすると、E列は「月齢」になりますから、それを平均します。
仮に、でてきた平均値が E50に入っているとすると、

E51に =INT(E50/12) 'これが、平均値の「歳」の部分
E52に =E50-12*E51 'これが、平均値の「か月」の部分になります。

Qエクセルで男女別の数を数えたい

エクセル超初心者です。すみませんが教えてください。
以下のようなデータで、男○人、女○人を出したいのですが、どのようにすればよいのですか?

A列     B列
アベ タロウ 男
アベ タロウ   男
イケダ ジロウ  男
イケダ ハナコ   女
イケダ ハナコ   女
ウノ サブロウ   男
ウノ サブロウ   男

6,815行あり、男女とも、重複して入っているのですが、重複していても1人として数えたいです。

よろしくお願いします。

Aベストアンサー

こんにちは

C1に =IF(COUNTIF(A$1:A1,A1)=1,A1,"")
D1に =IF(C1="","",B1)
それぞれの式を下方向にコピー
(B列に空白行がないのであれば、C1とD1を範囲選択して
 フィルハンドルのダブルクリックでもいいです)

任意のセルに
=COUNTIF(D:D,"男")
=COUNTIF(D:D,"女")

で出来ると思います。

#1さんのC列の式 
=IF(A2=A1,"",1)
これですと、同じ名前の人が離れた行に入力されてしまっている場合に
別の人として判断されてしまいます。

Q勤続年数の平均値の求め方

こんにちは、エクセルについて教えてください。
ある会社での各社員の勤続年数の計算でA列に入社年月日、B列に本日の年月日があり、C列にAとBの期間(勤続年数)計算を
=DATEDIF(A30,B30,"Y")&"年" & DATEDIF(A30,B30,"YM") & "月"で
●年●月で表示させています。
さて、この各社員の勤続年数の平均を出すために通常のAvarage関数を使うと「ゼロで割りました」のメッセージが出て計算してくれません。勤務年数の平均を求める方法を教えてください。

Aベストアンサー

過去ログです。
エクセルで平均年齢や平均勤続年数を求めるには?
http://oshiete1.goo.ne.jp/qa3213296.html

Q【Excel】年齢・人数・性別を年代・性別毎に集計したい

業務上にて集計していたのですが、自分の知っている範囲で処理できず困っています。
まず以下のテーブルが前提にあります。
***********************************
年齢    人数    性別コード
18     2      0
22     3      0
22     2      1
35     2      0
***********************************
※性別コード 0が男性 1が女性です。

これを以下の表にまとめたいと思っています。
*****************************
   10代 20代 30代・・・
男性  2  3   2
女性  0  2   0
*****************************

SUMPRODUCT関数でやってみたのですが、年齢別に人数が数える方法が分かりませんでした。よろしくご指導願います。

Aベストアンサー

ちょっと違うやり方になりますが、項目を一つ増やしてよければ
D2に[=TRUNC(A2,-1)]と入力して、これを最終行までコピーします。
(これでD列には10代なら10、20代なら20、が入ります)
後は、ピボットテーブルで集計できます。

Qexcel男女混合名簿を別のシートへ男女別にしたい

sheet1に、男女混合名簿を作成しました。
   A    B    C    D    E
1  年   組   性  名前   住所 
2   1   1    男   山田   東京都
3   1    1   女   鈴木   神奈川県
4   1    1   男   高橋   埼玉県
5 1 1 男  佐藤  千葉県
6 1 1 女  田中 茨城県
7 1 1 男  松井  沖縄県

sheet2に、男女別の名前だけの名簿(男だけが先女だけが後)
  A   B  C  D  E
1 山田
2 高橋
3 佐藤
4 松井
5 鈴木
6 田中
のようにしたいのですが、どうしたらいいですか?
(1)sheet1が変更されたら、sheet2も自動的に変更したい。
(2)VBAなしで
という初心者向けのわがままですが、よろしくお願いいたします。
 

sheet1に、男女混合名簿を作成しました。
   A    B    C    D    E
1  年   組   性  名前   住所 
2   1   1    男   山田   東京都
3   1    1   女   鈴木   神奈川県
4   1    1   男   高橋   埼玉県
5 1 1 男  佐藤  千葉県
6 1 1 女  田中 茨城県
7 1 1 男  松井  沖縄県

sheet2に、男女別の名前だけの名...続きを読む

Aベストアンサー

論理的に説明が難しいので数式を説明しません。

Sheet2のA1セルに次の式を入力し、Ctrl+Shift+Enterで確定してください。

=INDEX(Sheet1!D$1:D$7,MOD(SMALL((Sheet1!C$2:C$7="男")*ROW(C$2:C$7)+((Sheet1!C$2:C$7="女")*1000+(Sheet1!C$2:C$7="女")*ROW(C$2:C$7)),ROW()),1000))

男女の判別データ(Sheet1のD列)に空欄があると正しい処理になりませんので注意してください。
提示のリストでは実効の元データがC2~C7でありその範囲のみで式を組み立てています。
空欄が含まれているときは判定方法を変更する必要があります。

QEXCELで年齢別の分布表をつくりたい

こんにちは。エクセルで年齢のデータがあるのですが
10代は何人20代は何人30代は何人と年齢別の分布表をつくりたいのですが、どのような関数を使ったらいいですか? 教えてください。

Aベストアンサー

FREQUENCY関数を使います。

たとえばC列に年齢が入力されていて、F2からF4に分布を表示するとします。

F2からF4を範囲選択して、
=FREQUENCY(C2:C10,{19,29})
と入力して、CtrlキーとShiftキーを押しながらEnterキーを押します。

関数貼り付けでは、
FREQUENCYを選んで、
データ配列に
C2:C10
区間配列に
{19,29}
と入力して
Ctrl+Shift+Enterキーを押します。

式はこのようになります。
={FREQUENCY(C2:C10,{19,29})}

40代以降まで必要なら、区間配列を
{19,29,39}
のようにします。


人気Q&Aランキング