エクセルの関数について質問

質問

次のような表を考えているのですが関数をどうすれば良いか教えてください
エクセルのバージョンは2003です。

・下記の４列目に2013年3月31日時点におけるに入社してからの年数を関数で計算させたい
・下記の5列目に4列目が10年以下の人は10、10年超20年以下の人には20、20年超30年以下の人には30と表示される関数を組みたい
・下記の6列目に2013年3月31日時点における年齢を関数で表示させたい

氏名　入社日付 生年月日　　入社年数　区分　年齢
Ａ　1999/4/1　1975/11/9　
Ｂ 1988/4/1 1962/8/1
Ｃ 2007/6/3
Ｄ
Ｅ
Ｆ
Ｇ
・
・
・
・

以上お手数をおかけしますが、宜しくお願いします。

回答No.1

WDY · ベストアンサー

氏名のセルをA1とした場合
2Dセルに　=DATEDIF(B2,"2013/3/31","Y")
2Eセルに　=IF(D2>20,30,IF(AND(D2>10,D2<=20),20,10))
2Fセルに　=DATEDIF(C2,"2013/3/31","Y")

後は引き伸ばしていただければ計算されます。

お礼

ありがとうございます。
質問に一つ訂正があります。
2Eセルに　=IF(D2>20,30,IF(AND(D2>10,D2<=20),20,10))の部分ですが
下記の5列目に4列目が10年以下の人は10、10年超20年以下の人には20、20年超30年以下の人には30、
30年超40年以下の人には40、40年超の人にはＮＧと表示したいのですが、再度教えていただけないでしょうか。
たびたびですみません。

回答No.6

MarcoRossiItaly

No.3・4です。まだご質問が締め切られていないようだから。E2セルに入力するのは次の数式でもいいです。こちらのほうが少し短くて済みますね。

E2　=if(d2=0,"",if(d2>40,"ＮＧ",10*(int(d2/10)+(mod(d2,10)>0))))
　　あるいは
　　=if(d2=0,"",if(d2>40,"ＮＧ",floor(d2,10)+10*(mod(d2,10)>0)))

お礼

ありがとうございました。

回答No.5

WDY

WDYです
確認が遅れて申し訳ございません

>2Eセルに　=IF(D2>20,30,IF(AND(D2>10,D2<=20),20,10))の部分ですが
>下記の5列目に4列目が10年以下の人は10、10年超20年以下の人には20、20年超30年以下の人には30、
>30年超40年以下の人には40、40年超の人にはＮＧと表示したいのですが、再度教えていただけないでしょうか。
の件ですが
=IF(D2>40,"NG",IF(AND(D2>30,D2<=40),40,IF(AND(D2>20,D2<=30),30,IF(AND(D2>10,D2<=20),20,10))))
とすれば出来ますよ。

がんば！

お礼

ありがとうございました。

回答No.4

MarcoRossiItaly

＞…5列目に4列目が10年以下の人は10、10年超20年以下の人には20、20年超30年以下の人には30、30年超40年以下の人には40、40年超の人にはＮＧと表示したい…

…年寄りはNG（笑）　　ならこんなふうに式を差し替えればいいのではありませんか？

E2　=choose(sum(d2=0,d2<=10,d2<=20,d2<=30,d2<=40,d2<=999),"ＮＧ",40,30,20,10,"")

ところで、D2セルの式で四捨五入する場合について補足を。0.1年≒36.5日、0.01年≒3.65日、0.01年≒0.365日＝8.76時間であることを考えると、小数点第2位で四捨五入するのでは、計算結果の変化しない時間が長くなる（36.5日÷2＝18日程度の前後の期間で、計算結果が変化しない）ので、第3位が丁度良いケースが多いかもしれませんね。第4位だと、365日＝0.999年みたいに、丁度1年であるにも関らず整数にならないので、それはそれで愉快でないかもしれません。第3位にするなら、式は次のとおり。

D2　=round((date(2013,3,31)+1-b2)/365.25,2)　　……概数を小数値で求めた後、小数点第3位を四捨五入

お礼

ありがとうございました。

回答No.3

MarcoRossiItaly

年数、年齢の計算式は、定義によるので、下に示したものは一例。なおDATEDIF関数は、年や月の計算に限れば大丈夫とも言われていますが、バグが気持ち悪いので、使わない方向で回答。

1.次の数式を入力

D2　=2013-year(b2)-((month(b2)=4)*(day(b2)>1)+(month(b2)>4))　　……整数値（満年数）で算出
　　=(date(2013,3,31)+1-b2)/365.25　　……概数を小数値で算出
　　=round((date(2013,3,31)+1-b2)/365.25,1)　　……概数を小数値で求めた後、小数点第2位を四捨五入

E2　=choose(sum(d2=0,d2<=10,d2<=20,d2<=30,d2<=999),"長老",30,20,10,"")

F2　D2の式中の「b2」を「c2」に書き換えた式

2.D～F列に対して、「セルの書式設定」の「表示形式」タブで次のとおり操作
　●D列　　 「数値」に設定し、「小数点以下の桁数」を「1」とする
　●E～F列　「数値」などに設定

　※1.での入力をしているうちにセルの書式が「日付」になってしまう場合があります。
　　2.の操作をしないと正しく結果を表示しません。

お礼

ありがとうございました。

回答No.2

aokii

入社年数：=DATEDIF(B2,TODAY(),"y")
区分　　　：=IF(D2<=10,10,IF(D2<=20,20,30))
年齢　　　：=DATEDIF(C2,TODAY(),"y")

お礼

ありがとうございました。