Excel2003中怎么利用公式或命令统计工龄?

全部回复

• 

  

  0

  登录进行投票

  可以使用函数、分类汇总或数据透视表，实现起来要看具体情况，方便的话最好能上个样本。

  ---

  Microsoft Technet Observer

  2009年4月20日 6:06

  回复

  |

  引用

  版主
• 

  

  0

  登录进行投票

  1.如下面的文件: 

  序号	姓  名	性  别	出生  　年月	身 份	参加工　作时间	工龄	学历
  1	甲	男	1958.11	干部	1977.03		大学
  2	张三	男	1958.9	干部	1977.07		专科
  3	李四	男	1948.06	干部	1968.09		中专
  4	王五	女	1962.05	干部	1981.03		大学
  5	六一	男	1955.04	干部	1975.07		大学
  6	丁一	女	1963.10	工人	1969.07		专科
  7	李白	男	1953.09	干部	1975.07		专科
  8	左右	男	1967.01	工人	1988.07		中专

  根据上表,想统计年龄在40~45间的有几人?共有大学学历的多少人?干部身份中有大学学历的多少人?怎么根据出生年用公式月计算工龄?
  2.下一个实例:

  序号	姓  名	性  别	身份证号	身 份	参加工　作时间	年龄	学历
  1	甲	男	370223195810121543'	干部	1977.03		大学
  2	张三	男	370223195708024125'	干部	1977.07		专科
  3	李四	男	370223196802061791'	干部	1968.09		中专
  4	王五	女	370223196405161547'	干部	1981.03		大学
  5	六一	男	370223195711021439'	干部	1975.07		大学
  6	丁一	女	370223195910245231'	工人	1979.07		专科
  7	李白	男	370223196010235321'	干部	1975.07		专科
  8	左右	男	370223196910231549'	工人	1988.07		中专

  根据上表数据,怎样统计年龄在40~45间的人数?怎么用公式计算年龄?
  

  2009年4月20日 11:01

  回复

  |

  引用
• 

  

  0

  登录进行投票

  第一个问题：假设你的表格左上角从A1单元格开始，先整理下你的表，删除无用的行2，再添加两列（出生年月1、参加工作时间1），把你表中的出生年月和参加工作时间两列中数据格式转为EXCEL可接受的日期格式，以便下面的运算，再添加一辅助列“年龄”，在表下添加三行，分别放置“年龄在40-45岁之间的人数”、“大学学历人数”、“干部身份中大学学历人数”，输入公式，得到结果如下：
  

  序号	姓名	性 别	出生年月	身份	参加工作时间	工龄	学历	出生年月1	参加工作时间1	年龄
  1	甲	男	1958.11	干部	1977.03		大学	1958年11月	1977年3月	50
  2	张三	男	1958.9	干部	1977.07		专科	1958年9月	1977年7月	50
  3	李四	男	1948.06	干部	1968.09		中专	1948年6月	1968年9月	60
  4	王五	女	1962.05	干部	1981.03		大学	1962年5月	1981年3月	46
  5	六一	男	1955.04	干部	1975.07		大学	1955年4月	1975年7月	54
  6	丁一	女	1963.1	工人	1969.07		专科	1963年1月	1969年7月	46
  7	李白	男	1953.09	干部	1975.07		专科	1953年9月	1975年7月	55
  8	左右	男	1967.01	工人	1988.07		中专	1967年1月	1988年7月	42
  年龄在40-45岁之间的人数有：	1
  大学学历人数：	3
  干部身份中大学学历人数：	3

  
  公式说明：
  
  I2单元格的公式为：=DATE(VALUE(LEFT(D2,4)),VALUE(IF(LEN(D2)=7,RIGHT(D2,2),RIGHT(D2,1))),1)
  将D列的出生年月转换为I列的日期格式。
  
  J2单元格的公式为：=DATE(VALUE(LEFT(F2,4)),VALUE(IF(LEN(F2)=7,RIGHT(F2,2),RIGHT(F2,1))),1)
  将F列的参加工作时间转换为J列的日期格式。
  
  K2单元格的公式为：=YEAR(TODAY()-I2+1)-1900
  计算出年龄，注意此公式计算的是实龄。
  
  B11单元格的公式为：=SUMPRODUCT((K2:K9>=40)*(K2:K9<=45))
  计算年龄大于等于40且小于等于45的人数。
  
  B12单元格的公式为：=COUNTIF(H2:H9,"大学")
  计算学历为“大学”的人数。
  
  B13单元格的公式为：=SUMPRODUCT((H2:H9="大学")*(E2:E9="干部"))
  计算学历为“大学”且身份为“干部”的人数。
  
  最后一问没看懂，什么叫“根据出生年，用公式月计算工龄？如果是计算到当前的工龄的话，可参照前面计算年龄的公式，在G2输入公式：=YEAR(TODAY()-J2+1)-1900
  
  注意：G列和K列要设置单元格格式数字分类为常规。 

  ---

  Microsoft Technet Observer

  • 已标记为答案 Leo ChenModerator 2009年4月23日 15:19

  2009年4月20日 13:56

  回复

  |

  引用

  版主
• 

  

  0

  登录进行投票

  第二个问题：还是假设你的表格左上角从A1单元格开始，还是要先整理下你的表，删除无用的行2，再添加I列用于存放整理过的身份证号（如果要强制身份证号以文本方式存放，应该在其前面加单引号'而不是在后面加'），再添加一辅助列K列用于存放从身份证中提取出来的出生年月，最后在相应单元格输入公式并填充，得到结果如下：
  
  

  序号	姓名	性别	身份证号	身份	参加工作时间	年龄	学历	身份证号1	出生年月
  1	甲	男	370223195810121543'	干部	1977.03	50	大学	370223195810121543	1958-10-12
  2	张三	男	370223195708024125'	干部	1977.07	51	专科	370223195708024125	1957-8-2
  3	李四	男	370223196802061791'	干部	1968.09	41	中专	370223196802061791	1968-2-6
  4	王五	女	370223196405161547'	干部	1981.03	44	大学	370223196405161547	1964-5-16
  5	六一	男	370223195711021439'	干部	1975.07	51	大学	370223195711021439	1957-11-2
  6	丁一	女	370223195910245231'	工人	1979.07	49	专科	370223195910245231	1959-10-24
  7	李白	男	370223196010235321'	干部	1975.07	48	专科	370223196010235321	1960-10-23
  8	左右	男	370223196910231549'	工人	1988.07	39	中专	370223196910231549	1969-10-23
  年龄在40-45之间的人数：	2

  
  公式说明：
  
  在I2单元格输入公式：=LEFT(D2,LEN(D2)-1)
  规范身份证格式。
  
  在K2单元格输入公式：=IF(LEN(I2)=18,DATE(VALUE(MID(I2,7,4)),VALUE(MID(I2,11,2)),VALUE(MID(I2,13,2))),IF(LEN(I2)=15,DATE(1900+VALUE(MID(I2,7,2)),VALUE(MID(I2,9,2)),VALUE(MID(I2,11,2))),"身份证位数错"))
  该公式从身份证中提取出出生日期，公式之所以长，是因为它要判断身份证是15位还是18位并进行相应的处理。
  
  在G2单元格输入公式：=YEAR(TODAY()-J2+1)-1900
  计算实际年龄。
  
  在B11单元格输入公式：=SUMPRODUCT((G2:G9>=40)*(G2:G9<=45))
  计算年龄大于等于40岁且小于等于45岁的人数。

  ---

  Microsoft Technet Observer

  • 已标记为答案 Leo ChenModerator 2009年4月23日 15:19

  2009年4月20日 14:33

  回复

  |

  引用

  版主
• 

  

  0

  登录进行投票

  谢谢!问题已经解决.

  2009年4月25日 10:15

  回复

  |

  引用