公司人事管理表格设计Excel公式.doc

目录 （按Ctrl+左键跳转链接） 求平均值 忽略0求平均值 计算各部分的平均值 求和 相乘求和 筛选条件后求和 排名 指定排名 当两者数量一样时排名 通过身份证号码提取员工性别 通过身份证号码提取员工出生日期 根据出生日期计算年龄（得出年份） LEN函数用于返回字符串长度 MOD函数用于求两个数值相除后的余数，其结果的正负号与除数相同，用于求星期几 MID用于从文本字符串中提取指定的位置开始的特定位数字符 CONCATENATE用于将多个文本字符串合并为一个文本字符串 平均值 =AVERAGE(B1:B10) =AVERAGE(IF(C2:E20,C2:E2)) 按Ctrl+Shift+Enter组合键忽略0进行平均值 =AVERAGE(IF($B$2:$B$13=E7,$C$2:$C$13)) 按Ctrl+Shift+Enter组合键可计算出各部门的平均销售量 求和 =SUM(B1:B10) =SUM(B2:B11*C2:C11) 在=SUM(B2:B11*C2:C11)的基础上按Ctrl+Shift+Enter组合键可统计出数量乘于单价 =SUM(($A$2:$A$11=E$11)*($B$2:$B$11=$D12)*$D$2:$D$12) 按Ctrl+Shift+Enter组合键筛选条件员工名字为甲日期在2008/4/1的和 名次 =RANK(B1,$B$1:$B$10) =RANK(C5,($C$5,$C$9,$C$13,$C$17)) =RANK(B1,$B$1:$B$10)+COUNTIF($B$1:B1,B1)-1 这样当两个员工的数量一样时，根据在前的排位为X，在后的排位为X+1 通过身份证号码提取员工性别 =IF(LEN(D1)=15,IF(MOD(MID(D1,15,1),2)=1,男,女),IF(MOD(MID(D1,17,1),2)=1,男,女)) 解析：由于身份证号码有十五位和十八位之分，当号码为十五位时，使用IF(LEN(D1)=15,IF(MOD(MID(D1,15,1),2)=1,男,女)进行判断，提取号码最后一位数，如果它是二的倍数则性别为“女”，否则为“男”；当号码为十八时，使用公式IF(MOD(MID(D1,17,1),2)=1,男,女)进行判断，提取号码的倒数第二位数，如果它是二的倍数则性别为“女”，否则为“男”。 通过身份证号码提取员工出生日期 =IF(LEN(D1)=15,CONCATENATE(19,MID(D1,7,2),年,MID(D1,9,2),月,MID(D1,11,2),日),CONCATENATE(MID(D1,7,4),年,MID(D1,11,2),月,MID(D1,13,2),日)) 解析：由于身份证号码有十五位和十八位之分，当号码为十五时，使用公式：CONCATENATE(19,MID(D1,7,2)，“年”，MID(D1,9,2)，“月”，MID(D1,11,2),日，从身份证号码中提取第7，8位数字，接着再提取第9，10位数字，最后再提取第11，12位的数字，组合成员工的出生日期，当号码为18位时，使用公式：CONCATENATE(MID(D1,7,4),年,MID(D1,11,2),月,MID(D1,13,2),日)，从身份证号码中先提取第7，8，9，10位数字，接着再提第11，12位数字，最后再提取第13，14位的数字，组合成员工的出生日期。 根据出生日期计算年龄 =YEAR(TODAY())-YEAR(F1) 解析：使用当前系统日期减去F1单元格中的员工出生日期，最终得出员工的年龄 =YEAR(A1)年 LEN函数用于返回字符串长度 =IF(OR(LEN(D1)=15,LEN(D1)=18),,错误) 判断字符串位数是否为15位或18位，否则为错误 MOD函数用于求两个数值相除后的余数，其结果的正负号与除数相同，用于求星期几 MOD（number，divisor） Number：表示指定的被除数值 Divisor：表示指定的除数值，并且不能为0值 =星期MOD(F1-1,7)+1 MID用于从文本字符串中提取指定的位置开始的特定位数字符 MID（text,start_num,num_chars） Text:表示要提取字符串的文本字符串 Start——num:表示从文本字符串中要提取的第一个字符的开始位置 Num_chart：表示提取字符的个数 如上面由身份证计算出生日期与判断男女 CONCATENATE用于将多个文本字符串合并为一个文本字符串 =CONCATENATE(A1,B1,C1,D1)