エクセル年齢早見(誕生月）

質問

エクセルで年齢早見表を作成しています。
年齢の高い順で、かつ誕生月毎（1月から12月）に順位をつけたいのですが、
どのような関数をつかえば作成することは可能でしょうか。

回答No.9

kagakusuki · ベストアンサー

>12月のデータは拾ってくれないようです。

　重ねての不具合、誠に申し訳御座いません。
　抽出した生年月日を表示させるための関数中に、バグの取りこぼしが御座いました。
　不具合を解決するために、Sheet2のB3セルに入力する関数を次のものに差し替えてから、Sheet2のB3セルをコピーして、Sheet2の各月の各生年月日を表示させるセル（B列、E列、H列、K列、N列、Q列、T列、W列、Z列、AC列、AF列、AI列の各列の3行目以下のセル）に貼り付けて下さい。

=IF(ISNUMBER(MONTH(("1904年"&A$1&"1日")+0)),IF(ROWS($3:3)>COUNTIF(Sheet3!$B:$B,"<"&(MONTH(("1904年"&A$1)+0)+1)*10000000)-COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000),"",INDEX(Sheet4!$B:$B,MATCH(SMALL(Sheet3!$B:$B,ROWS($3:3)+COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000)),Sheet3!$B:$B,0))),"")

お礼

ありがとうございます。
見事に自分の理想としていたデータを抽出することができました。
最後に確認ですが、関数の途中でINDEX(Sheet4!$B:$B,・・・省略）があるのですが、これはSheet4ではなく、Sheet1で処理してよかったのでしょうか？

回答No.10

kagakusuki

>最後に確認ですが、関数の途中でINDEX(Sheet4!$B:$B,・・・省略）があるのですが、これはSheet4ではなく、Sheet1で処理してよかったのでしょうか？

　はい、仰る通りです。
　何度も間違いを犯してしまい、誠に申し訳御座いません。

回答No.8

kagakusuki

>「並べ替えを行わないままの、元データの表の左隣に、同じ月生まれ（生まれ年が異なる場合も含む）の中での生年月日の日付が古さの順位」を表示させる

ための方法です。
　今仮に、御質問欄に添付されている画像中で、「生年月日」と入力されている水色のセルがSheet1のD4セルであり、Sheet2のA列を作業列として使用し、Sheet1のA5以下に「同じ月生まれ（生まれ年が異なる場合も含む）の中での日付が古さの順位」を表示するものとします。

　まず、Sheet2のA5セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/DAY(INDEX(Sheet1!$D:$D,ROW()))),INDEX(Sheet1!$D:$D,ROW())+MONTH(INDEX(Sheet1!D:D,ROW()))*10000000,"")

　次に、Sheet2のA5セルをコピーして、Sheet2のA6以下に貼り付けて下さい。

　次に、Sheet1のA5セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet2!$A:$A,ROW())),COUNTIF(Sheet2!$A:$A,"<"&INDEX(Sheet2!$A:$A,ROW()))-COUNTIF(Sheet2!$A:$A,"<"&FLOOR(INDEX(Sheet2!$A:$A,ROW()),10000000))+1,"")

　次に、Sheet1のA5セルをコピーして、Sheet2のA5以下に貼り付けて下さい。

　以上です。

　後、もう一点確認しておきたい事がありました。
　御質問欄の添付画像の上の方に桃色で塗り潰されているセルが写っていて、その下に何か記入されている様なのですが、その「桃色で塗り潰されているセル」の直ぐ下のセルに入っているデータは、どの様な意味を持っているのでしょうか？
　そして、そのデータは、この御質問の早見表を作成する際に必要となる条件に関わるデータなのでしょうか？

回答No.7

kagakusuki

回答No.5です。

>全て1位の人のみしか表示されません。
>何か設定が間違っているのでしょうか。

　申し訳御座いません、私のミスです。
　Sheet2のA3セルとB3セルのそれぞれに入力する関数の中に、

ROW($3:$3)

という部分が御座いますが、この部分は本当の処は

ROW($3:3)

とせねばなりませんでした。
　ですから、Sheet2のA3セルに入力する正しい関数は、

=IF(ISNUMBER(B3),INDEX(Sheet1!$A:$A,MATCH(SMALL(Sheet3!$B:$B,ROWS($3:3)+COUNTIF(Sheet3!$B:$B,"<"&MONTH(B3)*10000000)),Sheet3!$B:$B,0)),"")

です。
　同様に、Sheet2のB3セルに入力する正しい関数は、

=IF(ISNUMBER(MONTH(("1904年"&A$1&"1日")+0)),IF(ROWS($3:3)>COUNTIF(Sheet3!$B:$B,"<"&(MOD(MONTH(("1904年"&A$1)+0),12)+1)*10000000)-COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000),"",INDEX(Sheet1!$B:$B,MATCH(SMALL(Sheet3!$B:$B,ROWS($3:3)+COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000)),Sheet3!$B:$B,0))),"")

です。
　修正の際には、以下の様な操作を行われると宜しいかと思います。
　まず、上記のそれぞれの関数をSheet2のA3セルとB3セルにそれぞれ入力し直すか、或いはSheet2のA3セルとB3セルの2個のセルを選択してから、Excelの置換機能を使用して、

ROW($3:$

を

ROW($3:

に置換して下さい。
　次に、Sheet2のA2～C3の範囲をコピーして、Sheet2のD2～AJ3の範囲に貼り付けて下さい。
　次に、Sheet2の3行目全体（A3～～AJ3の範囲のみでも可）をコピーして、4行目以下に貼り付けて下さい。

　後それから確認したいのですが、後から御質問欄に添付された画像では、文字が潰れてしまっているために、どの様な内容が表示されているのかが良く判りません。
　只、枠で囲まれている表の部分の項目名が、中央が「名前」、右端が「生年月日」であるらしい事は判りました。（水色で塗り潰されたセルの中の左端に記されている項目名は読み取れませんでした）
　ですが、画像には行番号も列番号も写ってはおりませんので、例えば「生年月日」と表示されている水色のセルが、何列の何行目のセルであるのかも判りませんから、「何列の何行目以下に、何のデータが並んでいるのか」という事を御教え願います。
　それと、「枠で囲まれている表の部分」の左隣には「順位」と記されている様に見えなくもないのですが、若しかしますと、回答No.5の添付画像の例の様に、「別の表に1月生まれから12月生まれまで人のデータを、生年月日の日付が古い順に並べた一覧にする」のではなく、単に、「並べ替えを行わないままの、元データの表の左隣に、同じ月生まれ（生まれ年が異なる場合も含む）の中での生年月日の日付が古さの順位」を表示させる様にした方が宜しいのでしょうか？

補足

ありがとうございます。
早速、数式修正したところ表示されました。
しかし1月から11月は、表示されますが12月のデータは拾ってくれないようです。
なにか設定に問題ありますか？

ご質問の桃色のセルは、年齢算出基準日を入力しているだけです。

回答No.6

chonami

最終的に１２月からの並びになってしまうんですが…

生年月日がD2から始まるとして、例えばF列に

=TEXT(D2,"mm")&DATEDIF(D2,TODAY(),"Y")

と入力し、この列を降順で並べ替えるというのでは如何でしょうか？
年齢が１ケタの人がいるともう一工夫いります。

また、ランキングの数を出したいのでしたら、

=COUNTIF($F$2:F2,LEFT(F2,2)&"*")

と入力して下にコピーでは？
（参照の＄の位置に気をつけてください。）

すみません、かなりやっつけな式なので他にもっとスマートな回答がつくと思いますが。

回答No.5

kagakusuki

今仮に、Sheet1のA列の2行目以下に氏名が入力されていて、Sheet1のB列に生年月日が入力されているものとして、
Sheet2のA列に1月生まれの人の氏名を年齢順に表示し、Sheet2のB列にその生年月日を、Sheet2のC列にその順位を表示し、同様に、
Sheet2のD列～F列に2月生まれの人の、
Sheet2のG列～I列に3月生まれの人の、
Sheet2のJ列～L列に4月生まれの人の、
　　　　　　　　　・
　　　　　　　　　・
　　　　　　　　　・
Sheet2のAE列～AG列に11月生まれの人の、
Sheet2のAH列～AJ列に12月生まれの人の
データを、それぞれ表示するものとします。
　又、Sheet3のA列とB列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/DAY(INDEX(Sheet1!$B:$B,ROW()))),INDEX(Sheet1!$B:$B,ROW())+MONTH(INDEX(Sheet1!$B:$B,ROW()))*10000000,"")

　次に、Sheet3のB1セルに次の関数を入力して下さい。

=IF(ISNUMBER($A1),$A1+COUNTIF($A$1:$A1,$A1)/(COUNT($A:$A)+1),"")

　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2の
A1セルに　　　1月
D1セルに　　　2月
G1セルに　　　3月
J1セルに　　　4月
M1セルに　　　5月
P1セルに　　　6月
S1セルに　　　7月
V1セルに　　　8月
Y1セルに　　　9月
AB1セルに　　10月
AE1セルに　　11月
AH1セルに　　12月
と入力して下さい。
　次に、Sheet2のA2セルに「氏名」、Sheet2のB2セルに「生年月日」、Sheet2のC2セルに「順位」と入力して下さい。
　次に、Sheet2のA3セルに次の関数を入力して下さい。

=IF(ISNUMBER(B3),INDEX(Sheet1!$A:$A,MATCH(SMALL(Sheet3!$B:$B,ROWS($3:$3)+COUNTIF(Sheet3!$B:$B,"<"&MONTH(B3)*10000000)),Sheet3!$B:$B,0)),"")

　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF(ISNUMBER(MONTH(("1904年"&A$1&"1日")+0)),IF(ROWS($3:$3)>COUNTIF(Sheet3!$B:$B,"<"&(MOD(MONTH(("1904年"&A$1)+0),12)+1)*10000000)-COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000),"",INDEX(Sheet1!$B:$B,MATCH(SMALL(Sheet3!$B:$B,ROWS($3:$3)+COUNTIF(Sheet3!$B:$B,"<"&MONTH(("1904年"&A$1)+0)*10000000)),Sheet3!$B:$B,0))),"")

　次に、Sheet2のC3セルに次の関数を入力して下さい。

=IF(ISNUMBER(B3),RANK(B3,B:B,1),"")

　次に、Sheet2のA2～C3の範囲をコピーして、Sheet2のD2～AJ3の範囲に貼り付けて下さい。

　これで、Sheet2に「年齢の高い順で、かつ誕生月毎（1月から12月）に順位をつけた」表が、自動的に表示されます。

お礼

ありがとうございます。
早速試してみます。

補足

数式を入力して頑張ってみましたが、Sheet2に「年齢の高い順で、かつ誕生月毎に順位」をだそうとすると、全て1位の人のみしか表示されません。
何か設定が間違っているのでしょうか。

回答No.4

chayamati

今日現在の計算です
年齢の関数は =DATEDIF(生年月日,TODAY(),"Y")
誕生月は　 　 =Month(生年月日)

補足
　 生年月日からの経過月数　 =DATEDIF(生年月日,TODAY(),"M")
　 生年月日からの経過日数　 =DATEDIF(生年月日,TODAY(),"D")

回答No.3

CC_T

> 年齢の高い順で、かつ誕生月毎（1月から12月）に順位をつけたいのですが

作りたいものがイマイチイメージできません・・・。

例えば老人ホームで毎月の誕生会で入居者の誕生日を祝うのに、複数の人名と生年月日のリストから、その月に生まれた人をピックアップして年齢順に並べたいのでしょうかね？

⇒例えばＡ列に2013/1/16　など日付が入っているなら、＝YEAR(A1)や=MONTH(A1)で年や月の値がそれぞれ別個に取り出せますから、両者の結果で並べ替えをさせれば良いでしょう。

回答No.2

chonami

誕生日を昇順で並べればご希望通りになるんじゃないかと思いますが、

>誕生月毎（1月から12月）に順位

の意味がちょっとわかりません。

どのようにデータを入力しているのかもわかりませんし…（シリアル値？）
具体的にどういったデータが入っていてどのようになって欲しいのか提示していただけますでしょうか？

補足

言葉足らずで申し訳ございません。
サンプルイメージを添付しましたので宜しくお願いします。

誕生月毎　1月生まれなら1月生まれの中で年齢が高い順に順位をつける方法が
あればご教示いただきたく宜しくお願いします。

回答No.1

maiko0318

誕生日欄があればそれでソートすれば？