Excel(エクセル)実用編：勤続年数や年齢の計算

DATEDIF関数を利用して年齢や勤続年数を計算する 　　　　　topへ

• (問題1)下表の勤続年数の計算表に数式を入れて完成しなさい。
  　　　　　（青色のセルはデータを直接入力します)

  	B	C	D	D	F	G
  2		基準年月日	H15.4.1
  3	社員番号	氏名	生年月日	入社年月日	年齢(歳)	勤続年数(年)
  4	1001	相沢栄一	S40.01.11	H2.04.01	38	13
  5	1002	井上太郎	S42.02.22	H2.04.01	36	13
  6	1003	上田肇	S43.03.03	H3.04.01	35	12
  7	1004	榎本浩次	S44.04.04	H3.04.04	33	11
  8	1005	大川栄治	S45.05.15	H4.04.01	32	11
  9	1006	川田広志	S46.06.26	H4.04.01	31	11
  10	1007	木下昇	S47.07.17	H5.04.01	30	10

• (解答例)

  1. 社員番号は1000番からの連番としました。
     • B4セルに「=CELL("ROW",A1)+1000」と入力し、フィルハンドルをドラッグして下方向へコピーします。
     • B4セルは「=ROW()-3+1000」としてもOKです。
     • 社員番号が行の削除によって変更されて困るときは、直接値を入力します。
       • B4セルに「1001」,B5セルに「1002」と入力して、B4:B5セルを選択し、フィルハンドルをドラッグして下方向へコピーすると、連続した数値が入力できます。
  2. 年齢、勤続年数ともDATEDIF関数で求められます。
     基準年月日（D2セル)の参照は絶対参照となります。
     • F4,G4セルにそれぞれの数式を入力し、F4:G4セルを選択して、フィルハンドルをドラッグして下方向へコピーします。

  	B	C	D	E	F	G
  2		基準年月日	H15.04.01
  3	社員番号	氏名	生年月日	入社年月日	年齢(歳)	勤続年数(年)
  4	=CELL("ROW",A1)+1000	相沢栄一	S40.01.11	H2.04.01	=DATEDIF(D4,$D$2,"Y")	=DATEDIF(E4,$D$2,"Y")
  5	=CELL("ROW",A2)+1000	井上太郎	S42.02.22	H2.04.01	=DATEDIF(D5,$D$2,"Y")	=DATEDIF(E5,$D$2,"Y")
  6	=CELL("ROW",A3)+1000	上田肇	S43.03.03	H3.04.01	=DATEDIF(D6,$D$2,"Y")	=DATEDIF(E6,$D$2,"Y")
  7	=CELL("ROW",A4)+1000	榎本浩次	S44.04.04	H3.04.04	=DATEDIF(D7,$D$2,"Y")	=DATEDIF(E7,$D$2,"Y")
  8	=CELL("ROW",A5)+1000	大川栄治	S45.05.15	H4.04.01	=DATEDIF(D8,$D$2,"Y")	=DATEDIF(E8,$D$2,"Y")
  9	=CELL("ROW",A6)+1000	川田広志	S46.06.26	H4.04.01	=DATEDIF(D9,$D$2,"Y")	=DATEDIF(E9,$D$2,"Y")
  10	=CELL("ROW",A7)+1000	木下昇	S47.07.17	H5.04.01	=DATEDIF(D10,$D$2,"Y")	=DATEDIF(E10,$D$2,"Y")

年齢の計算

• (問題2)　年齢を　○年○ヶ月　で表示しなさい。

  	B	C	D	E	F
  2		基準年月日	H15.4.1
  3	社員番号	氏名	生年月日		年齢(年 月)
  4	1001	相沢栄一	S40.1.11		38年2ヶ月
  5	1002	井上太郎	S42.2.22		36年1ヶ月
  6	1003	上田肇	S43.3.3		35年0ヶ月
  7	1004	榎本浩次	S44.4.4		33年11ヶ月
  8	1005	大川栄治	S45.5.15		32年10ヶ月
  9	1006	川田広志	S46.6.26		31年9ヶ月
  10	1007	木下昇	S47.7.17		30年8ヶ月

• (解答例)

  1. 社員番号は1000番からの連番としました。
     • B4セルに「=CELL("ROW",A1)+1000」と入力し、フィルハンドルをドラッグして下方向へコピーします。
  2. 年齢は歳と月数をそれぞれDATEDIF関数で求めら、文字列とを「&」でつなげてました。
     基準年月日（D2セル)の参照は絶対参照となります。
     • F4セルに数式を入力し、F4:G4セルを選択して、フィルハンドルをドラッグして下方向へコピーします。

     	B	C	D	E	F
     2		基準年月日	H15.04.01
     3	社員番号	氏名	生年月日		年齢(年 月)
     4	=CELL("ROW",A1)+1000	相沢栄一	S40.01.11		=DATEDIF(D4,$D$2,"Y")&"年"&DATEDIF(D4,$D$2,"YＭ")&"ヶ月"
     5	=CELL("ROW",A2)+1000	井上太郎	S42.02.22		=DATEDIF(D5,$D$2,"Y")&"年"&DATEDIF(D5,$D$2,"YＭ")&"ヶ月"
     6	=CELL("ROW",A3)+1000	上田肇	S43.03.03		=DATEDIF(D6,$D$2,"Y")&"年"&DATEDIF(D6,$D$2,"YＭ")&"ヶ月"
     7	=CELL("ROW",A4)+1000	榎本浩次	S44.04.04		=DATEDIF(D7,$D$2,"Y")&"年"&DATEDIF(D7,$D$2,"YＭ")&"ヶ月"
     8	=CELL("ROW",A5)+1000	大川栄治	S45.05.15		=DATEDIF(D8,$D$2,"Y")&"年"&DATEDIF(D8,$D$2,"YＭ")&"ヶ月"
     9	=CELL("ROW",A6)+1000	川田広志	S46.06.26		=DATEDIF(D9,$D$2,"Y")&"年"&DATEDIF(D9,$D$2,"YＭ")&"ヶ月"
     10	=CELL("ROW",A7)+1000	木下昇	S47.07.17		=DATEDIF(D10,$D$2,"Y")&"年"&DATEDIF(D10,$D$2,"YＭ")&"ヶ月"

DATEDIF関数を使わずに年齢を計算する（その1）

• (問題)DATEDIF関数を使わないでF列を計算しなさい。
  

  	B	C	D	E	F
  2		基準年月日	H15.4.1
  3	社員番号	氏名	生年月日		年齢(歳)
  4	1001	相沢栄一	S40.01.11		38
  5	1002	井上太郎	S42.02.22		36
  6	1003	上田肇	S43.03.03		35
  7	1004	榎本浩次	S44.04.04		33
  8	1005	大川栄治	S45.05.15		32
  9	1006	川田広志	S46.06.26		31
  10	1007	木下昇	S47.07.17		30

• 年齢の計算は誕生日と同じ月日以降で1歳と計算すると、年の引き算をして、基準の月日が誕生月日以上のときはそのまま、そうでないときは -1 すればよいと考えます。
  • 年の差を求める　：=YEAR($D$2)-YEAR(D4)
  • 誕生日の月日が基準とする月日はTEXT関数で求めることができます。
    ポイントは月と日の桁数を揃えて比較するところです。月の日も2桁にしています。
    (TEXT関数の戻り値は文字列なので、月を一桁にすると　9/1　と　10/1　の比較は　"901">"1001"　となってしまいます。)
    • 誕生日の月日　：=TEXT(D4,"mmdd")
    • 基準日の月日　：TEXT($D$2,"mmdd")
    • 基準日の月日が誕生日の月日以上であれば、年の計算結果でよいので「0」、そうでなければ　「-1」　とします。
      よって、+IF(TEXT($D$2,"mmdd")>=TEXT(D4,"mmdd"),0,-1)　とすればよいことになります。
  • F4セルの数式は　=YEAR($D$2)-YEAR(D4)+IF(TEXT($D$2,"mmdd")>=TEXT(D4,"mmdd"),0,-1)　で求めることができます。
    

    	B	C	D	E	F
    2		基準年月日	H15.4.1
    3	社員番号	氏名	生年月日		年齢(歳)
    4	1001	相沢栄一	S40.01.11		=YEAR($D$2)-YEAR(D4)+IF(TEXT($D$2,"mmdd")>=TEXT(D4,"mmdd"),0,-1)
    5	1002	井上太郎	S42.02.22		=YEAR($D$2)-YEAR(D5)+IF(TEXT($D$2,"mmdd")>=TEXT(D5,"mmdd"),0,-1)
    6	1003	上田肇	S43.03.03		=YEAR($D$2)-YEAR(D6)+IF(TEXT($D$2,"mmdd")>=TEXT(D6,"mmdd"),0,-1)
    7	1004	榎本浩次	S44.04.04		=YEAR($D$2)-YEAR(D7)+IF(TEXT($D$2,"mmdd")>=TEXT(D7,"mmdd"),0,-1)
    8	1005	大川栄治	S45.05.15		=YEAR($D$2)-YEAR(D8)+IF(TEXT($D$2,"mmdd")>=TEXT(D8,"mmdd"),0,-1)
    9	1006	川田広志	S46.06.26		=YEAR($D$2)-YEAR(D9)+IF(TEXT($D$2,"mmdd")>=TEXT(D9,"mmdd"),0,-1)
    10	1007	木下昇	S47.07.17		=YEAR($D$2)-YEAR(D10)+IF(TEXT($D$2,"mmdd")>=TEXT(D10,"mmdd"),0,-1)

  • IF関数の部分を論理式に置き換えることもできます。
    論理式で条件を真逆にすると、TRUEの時「1」となるので　-(TEXT($D$2,"mmdd")<TEXT(D4,"mmdd"))　とします。
    F4セルの数式は　=YEAR($D$2)-YEAR(D4)-(TEXT($D$2,"mmdd")<TEXT(D4,"mmdd"))　となります。
    

    	B	C	D	E	F
    2		基準年月日	H15.4.1
    3	社員番号	氏名	生年月日		年齢(歳)
    4	1001	相沢栄一	S40.01.11		=YEAR($D$2)-YEAR(D4)-(TEXT($D$2,"mmdd")<TEXT(D4,"mmdd"))
    5	1002	井上太郎	S42.02.22		=YEAR($D$2)-YEAR(D5)-(TEXT($D$2,"mmdd")<TEXT(D5,"mmdd"))
    6	1003	上田肇	S43.03.03		=YEAR($D$2)-YEAR(D6)-(TEXT($D$2,"mmdd")<TEXT(D6,"mmdd"))
    7	1004	榎本浩次	S44.04.04		=YEAR($D$2)-YEAR(D7)-(TEXT($D$2,"mmdd")<TEXT(D7,"mmdd"))
    8	1005	大川栄治	S45.05.15		=YEAR($D$2)-YEAR(D8)-(TEXT($D$2,"mmdd")<TEXT(D8,"mmdd"))
    9	1006	川田広志	S46.06.26		=YEAR($D$2)-YEAR(D9)-(TEXT($D$2,"mmdd")<TEXT(D9,"mmdd"))
    10	1007	木下昇	S47.07.17		=YEAR($D$2)-YEAR(D10)-(TEXT($D$2,"mmdd")<TEXT(D10,"mmdd"))

DATEDIF関数を使わずに年齢を計算する(その2)

• 以下の方法では、小数誤差によって、正しい答えが求められないケースがあるとの指摘がありました。
  よって、削除ではなく、取り消し線を入れることで記事の取り消しという対処をさせていただきたいと思います。(2014/12/7 記)
• 上記の計算方法「年齢の計算は誕生日と同じ月日以降で1歳と計算すると、年の引き算をして、基準の月日が誕生月日以上のときはそのまま、そうでないときは -1 すればよいと考えます。」の処理の仕方が異なる例です。
• 年を整数、月日を小数として引き算する例です。
  =INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D4,"yyyy.mmdd"))　とすることでも求めることができます。
  表示形式のyyyyの後ろにドットがあることに注意してください。
  
  • まず、=TEXT($D$2,"yyyymmdd")-TEXT(D4,"yyyymmdd")　と計算すると下表のようになります。
    

    	B	C	D	E	F
    2		基準年月日	H15.4.1
    3	社員番号	氏名	生年月日		年齢(歳)
    4	1001	相沢栄一	S40.1.11		380290	=TEXT($D$2,"yyyymmdd")-TEXT(D4,"yyyymmdd")
    5	1002	井上太郎	S42.2.22		360179	=TEXT($D$2,"yyyymmdd")-TEXT(D5,"yyyymmdd")
    6	1003	上田肇	S43.3.3		350098	=TEXT($D$2,"yyyymmdd")-TEXT(D6,"yyyymmdd")
    7	1004	榎本浩次	S44.4.4		339997	=TEXT($D$2,"yyyymmdd")-TEXT(D7,"yyyymmdd")
    8	1005	大川栄治	S45.5.15		329886	=TEXT($D$2,"yyyymmdd")-TEXT(D8,"yyyymmdd")
    9	1006	川田広志	S46.6.26		319775	=TEXT($D$2,"yyyymmdd")-TEXT(D9,"yyyymmdd")
    10	1007	木下昇	S47.7.17		309684	=TEXT($D$2,"yyyymmdd")-TEXT(D10,"yyyymmdd")

  • この計算結果を1/10000して整数部分を取り出せばよいことになります。
    計算式を　=INT((TEXT($D$2,"yyyymmdd")-TEXT(D4,"yyyymmdd"))/10^4)　とします。
    

    	B	C	D	E	F
    2		基準年月日	H15.4.1
    3	社員番号	氏名	生年月日		年齢(歳)
    4	1001	相沢栄一	S40.1.11		38	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D4,"yyyymmdd"))/10^4)
    5	1002	井上太郎	S42.2.22		36	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D5,"yyyymmdd"))/10^4)
    6	1003	上田肇	S43.3.3		35	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D6,"yyyymmdd"))/10^4)
    7	1004	榎本浩次	S44.4.4		33	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D7,"yyyymmdd"))/10^4)
    8	1005	大川栄治	S45.5.15		32	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D8,"yyyymmdd"))/10^4)
    9	1006	川田広志	S46.6.26		31	=INT((TEXT($D$2,"yyyymmdd")-TEXT(D9,"yyyymmdd"))/10^4)
    10	1007	木下昇	S47.7.17		30	=INT((TEXT($D$2,"yyyymmdd")-TEXT(104,"yyyymmdd"))/10^4)

  • さらに表示形式を利用すると、=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D4,"yyyy.mmdd"))　とすることができます。
    ポイントは　年を整数、月日を4桁の小数にして計算することです。

    	B	C	D	E	F
    2		基準年月日	H15.4.1
    3	社員番号	氏名	生年月日		年齢(歳)
    4	1001	相沢栄一	S40.1.11		38	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D4,"yyyy.mmdd"))
    5	1002	井上太郎	S42.2.22		36	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D5,"yyyy.mmdd"))
    6	1003	上田肇	S43.3.3		35	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D6,"yyyy.mmdd"))
    7	1004	榎本浩次	S44.4.4		33	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D7,"yyyy.mmdd"))
    8	1005	大川栄治	S45.5.15		32	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D8,"yyyy.mmdd"))
    9	1006	川田広志	S46.6.26		31	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D9,"yyyy.mmdd"))
    10	1007	木下昇	S47.7.17		30	=INT(TEXT($D$2,"yyyy.mmdd")-TEXT(D10,"yyyy.mmdd"))

スポンサードリンク

PageViewCounter
　
　Since2006/2/27