常用函数汇总【我】

常用函数汇总

一、条件统计=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))

十二、根据成绩排名后，在指定单元格显示第一名的组别名称

做

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(D5<16,0,IF(D5=16,T5,IF(D5>16,T5)))

十五、事假不发交通费

=IF(M5<1,AB5,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,2 755,5505,13505},0),2)

=IF(G3<3500,0,IF(G3<3500,(G3-3500)*0.03,IF(G3<8000,(G3-3500)*0.1-105,IF(G 3<12500,(G3-3500)*0.2-555,IF(G3<38500,(G3-3500)*0.25-1005,IF(G3<58500,(G3-35 00)*0.3-2755,IF(G3<83500,(G3-3500)*0.35-5505,IF(G3<83501,(G3-3500)*0.45-1350 5))))))))

=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<=80000,F13*0.35-5505,F13*0.45-13505)))))))),2)

十七、风险金10%非四舍五入取整

=INT(T18*0.1)

十八、工资条

=IF(MOD(ROW(),3)=1,工资表!A$4,IF(MOD(ROW(),3)=2,OFFSET(工资

表!A$4,ROW()/3+1,0),""))

十九、工资表中查找对应员工名字的银行上帐金额

=IFERROR(VLOOKUP($B$3:$B$50,工资表!$C$5:$AF$50,30,0),0)

对应银行上帐工作表查找银行帐号【个税税单用】

=IFERROR(VLOOKUP($C$10:$C$50,银行!$B$3:$C$50,2,0),0)

二十、指定名称统计SUMIF

=SUMIF(E2:E663,"办公用品",F2:F663)

=SUMIF(B2:B22,">=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)【标题栏统计

二十一、汇总月薪为2000～4000的工资总额

=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分的男同学人数