常用函数汇总【我】.doc

常用函数汇总一、条件统计=COUNTIF(考勤表!C2:AG2,A)二、VLOOKUP查找=IFERROR(VLOOKUP($B$2:$B$1499,填写!$B$2:$CW$1499,2,0),0)三、排名次=RANK(G4,$G$4:$G$54)四、按排名，确定金、银、铜牌=IF(H4$L$11,IF(H4$L$10,铜牌,IF(H4$L$9,银牌,金牌)五、统计80分以上人数=ROUND(SUMPRODUCT(N(G4:G54=80),0)【前20%】=ROUND(SUMPRODUCT(N(G4:G54=80)*0.2,0)六、统计60分以上人数=ROUND(SUMPRODUCT(N(G4:G54=60),0)【90%】=ROUND(SUMPRODUCT(N(G4:G54=60)*0.9,0)+1七、分母为0的简单函数理解=IF(ISERROR(A1/B1),0,A1/B1)八、单元格数量统计和为0的单元格数量统计和=COUNTIF(L5:L21)=COUNTIF(L5:L21,0)九、平均成绩的统计【解决0/0=0的问题；并考虑了弃权票的问题】=IF(ISERROR(SUM(C5:E5)/(COUNT(C5:E5)-COUNTIF(C5:E5,0),0, SUM(C5:E5)/(COUNT(C5:E5)-COUNTIF(C5:E5,0)十、小数的四舍五入问题【绝对四舍五入】=ROUND(T11,4)十一、平均成绩的简单函数【考虑到弃权票的问题】=SUM(T8:T10)/(COUNT(T8:T10)-COUNTIF(T8:T10,0)十二、根据成绩排名后，在指定单元格显示第一名的组别名称=INDEX(U:U,MATCH(1,W:W,)组别成绩排名U:UW:W,第一组109.37073第二组113.08921第三组110.96922经评比，本季度第第二组获得团队第一名 延伸学习函数：自动从多数据的排名中提取第一名的关键数据并显示怎么做1、=INDEX(B:B,MATCH(1,D:D,)2、=OFFSET(B1,MATCH(1,D:D,)-1,)十三、根据考核等次，按110%、100%、90%系数发放绩效工资=IF(E5=金牌,P5*1.1,IF(E5=银牌,P5,IF(E5=铜牌,P5*0.9)十四、根据出勤天数发放基础工资、工龄工资等=IF(D516,T5) 十五、事假不发交通费=IF(M5=1,0)十六、个税=ROUND(MAX(AA18-3500)*0.03,0.1,0.2,0.25,0.3,0.35,0.45-0,105,555,1005,2755,5505,13505,0),2)=IF(G33500,0,IF(G33500,(G3-3500)*0.03,IF(G38000,(G3-3500)*0.1-105,IF(G312500,(G3-3500)*0.2-555,IF(G338500,(G3-3500)*0.25-1005,IF(G358500,(G3-3500)*0.3-2755,IF(G383500,(G3-3500)*0.35-5505,IF(G383501,(G3-3500)*0.45-13505)=ROUND(IF(F13=0,0,(IF(F13=1500,F13*0.03,IF(F13=4500,F13*0.1-105,IF(F13=9000,F13*0.2-555,IF(F13=35000,F13*0.25-1005,IF(F13=55000,F13*0.3-2755,IF(F13=80,H2:H22)=SUMIF(B3:B26,F3,D3:D26)=SUMIF(B3:B26,F11,D3)=SUMIF($A$3:$I$10,D15,$C$3)=SUMIF($A$3:$I$10,A15,$C$3:$K$10)=SUMIF($E$2:$AT$2,C$2,$E3)【标题栏统计二十一、汇总月薪为20004000的工资总额=SUMIFS(D3:D18,D3:D18,=2000,D3:D18,=4000)二十二、汇总后勤部月薪低于2000的工资总额=SUMIFS(D3:D18,C3:C18,后勤部,D3:D18,2000)二十三、汇总姓名为2个字，且部门不等于人事部，员工编号A开头的工资总额=SUMIFS(D3:D18,C3:C18,人事部,A3:A18,A*,B3:B18,?)二十四、及格人数统计表=COUNTIF(D:D,=60)二十五、统计大于等于80分，且小于等于90分的人数=COUNTIF(D3:D33,=80)-COUNTIF(D3:D33,90)二十六、统计大于80分的男同学人数=COUNTIFS(D3:D33,80,$C3:$C33,男)二十七、统计五门功课均几个的人员名单=COUNTIFS(D3:D33,=60,E3:E33,=60,F3:F33,=60,G3:G33,=60,H3:H33,=60)二十八、甲车间生产规格带*号的产品种类个数：=COUNTIFS(C3:C17,*,D3:D17,甲)二十九、所有车间生产规格带*号的产品种类个数：=COUNTIF(C3:C17,*)三十、35岁以上有房有车的人数=COUNTIFS(D3:D20,35,E3:E20,是,F3:F20,是)三十、不重复数据个数=SUM(1/COUNTIF(A2:A16,A2:A16)=SUMPRODUCT(-(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1)三十三、重复值查找=IF(ISERROR(VLOOKUP(C1,A$1:A$15000,1,0),不重复,重复)三十四、身份证信息1、性 别=IF(B4,IF(MOD(RIGHT(LEFT(B4,17),2),男,女),)2、出生年月=IF(B40,IF(LEN(B4)=15,CONCATENATE(19,MID(B4,7,2),年,MID(B4,9,2),月,MID(B4,11,2),日),CONCATENATE(MID(B4,7,4),年,MID(B4,11,2),月,MID(B4,13,2),日),)3、出生年月=MID(B7,7,4)&年&MID(B7,11,2)&月&MID(B7,13,2)&日4、年 龄=IF(D4=,DATEDIF(D4,TODAY(),y)C4公式 =IF(B4,IF(MOD(RIGHT(LEFT(B4,17),2),男,女),)C5公式 =IF(MOD(RIGHT(LEFT(B5,17),2),男,女)C6公式 =IF(IF(LEN(B6)=15,MOD(MID(B6,15,1),2),MOD(MID(B6,17,1),2)=1,男,女)E14公式 =INT(TODAY()-D13)/365)F13公式 =G17=MID(B13,7,4)&年&MID(B13,11,2)&月&MID(B13,13,2)&日D4公式 =IF(B40,IF(LEN(B4)=15,CONCATENATE(19,MID(B4,7,2),年,MID(B4,9,2),月,MID(B4,11,2),日), CONCATENATE(MID(B4,7,4),年,MID(B4,11,2),月,MID(B4,13,2),日),)D4可改为 =IF(B40,IF(LEN(B4)=15,19&MID(B4,7,2)&年&MID(B4,9,2)&月&MID(B4,11,2)&日,MID(B4,7,4)&年&MID(B4,11,2)&月&MID(B4,13,2)&日),)D5公式=IF(B5,TEXT(LEN(B5)=15)*19&MID(B5,7,6+(LEN(B5)=18)*2),#-00-00)*1,)D6公式 =-TEXT(MID(B6,7,IF(LEN(B6)=15,6,8),#-00-00)D7公式 =-TEXT(LEN(B7)=15)*19&MID(B7,7,(LEN(B7)=18)*2+6), #-00-00)E4公式 =IF(D4=,DATEDIF(D4,TODAY(),y)三十五、班级平均分AVERGERIF=AVERAGEIF($A:$A,$K3,D:D)三十六、班级男同学平均分 AVERGERIFS=AVERAGEIFS(D$3:D$72,$A$3:$A$72,$K16,$C$3:$C$72,男)三十七、提成奖金方法1=MIN(1000,MAX(100,C3*1%)=MAX(100,MIN(1000,C3*1%)=MEDIAN(100,1000,C3*1