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

  • 问题

  • Excel2003中,知道多个人的出生年月,想统计他们的某个年龄段有几个人,大学学历的有几个人,不知使用什么命令?再一个问题,知道多个人的身份证,怎么统计他们的某个年龄段有几个人?怎么统计工龄?谢谢!
    2009年4月19日 10:49

答案

  • 第一个问题:假设你的表格左上角从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
    2009年4月20日 13:56
    版主
  • 第二个问题:还是假设你的表格左上角从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
    2009年4月20日 14:33
    版主

全部回复

  • 可以使用函数、分类汇总或数据透视表,实现起来要看具体情况,方便的话最好能上个样本。
    Microsoft Technet Observer
    2009年4月20日 6:06
    版主
  • 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
  • 第一个问题:假设你的表格左上角从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
    2009年4月20日 13:56
    版主
  • 第二个问题:还是假设你的表格左上角从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
    2009年4月20日 14:33
    版主
  • 谢谢!问题已经解决.
    2009年4月25日 10:15