![]() |
Excel(エクセル)実用編:勤続年数や年齢の計算 |
スポンサードリンク | |
DATEDIF関数を利用して年齢や勤続年数を計算する |
DATEDIF関数を使わずに年齢を計算する(その1) |
(問題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 |
(解答例)
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ヶ月 |
(解答例)
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,"YM")&"ヶ月" | |
5 | =CELL("ROW",A2)+1000 | 井上太郎 | S42.02.22 | =DATEDIF(D5,$D$2,"Y")&"年"&DATEDIF(D5,$D$2,"YM")&"ヶ月" | |
6 | =CELL("ROW",A3)+1000 | 上田肇 | S43.03.03 | =DATEDIF(D6,$D$2,"Y")&"年"&DATEDIF(D6,$D$2,"YM")&"ヶ月" | |
7 | =CELL("ROW",A4)+1000 | 榎本浩次 | S44.04.04 | =DATEDIF(D7,$D$2,"Y")&"年"&DATEDIF(D7,$D$2,"YM")&"ヶ月" | |
8 | =CELL("ROW",A5)+1000 | 大川栄治 | S45.05.15 | =DATEDIF(D8,$D$2,"Y")&"年"&DATEDIF(D8,$D$2,"YM")&"ヶ月" | |
9 | =CELL("ROW",A6)+1000 | 川田広志 | S46.06.26 | =DATEDIF(D9,$D$2,"Y")&"年"&DATEDIF(D9,$D$2,"YM")&"ヶ月" | |
10 | =CELL("ROW",A7)+1000 | 木下昇 | S47.07.17 | =DATEDIF(D10,$D$2,"Y")&"年"&DATEDIF(D10,$D$2,"YM")&"ヶ月" |
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 |
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) |
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")) |
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") |
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) |
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