(完整版)计算机二级EXCEL必考函数汇总.doc
套题11.VLOOKUP函数叫做垂直查询函数，也叫首列查询函数。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP ( 查找值 , 查找的区域 , 返回位置(列序号) , 查找方式)(1)在"订单明细表"工作表的E3单元格中输入"=VLOOKUP(D3，编号对照!$A$3:$C$19,2,FALSE)"， “2”查询第二列。(2)在"订单明细表"工作表的F3单元格中输入"=VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE)"，“3”查询第三列。2.条件求和函数(3)"=SUMPRODUCT(1*(订单明细表!E3:E262="MS Office高级应用"),订单明细表!H3:H262)" （4）在"统计报告"工作表的B5单元格中输入"=SUMPRODUCT(1*(订单明细表!C350:C461="隆华书店"),订单明细表!H350:H461)"。（5）在"统计报告"工作表的B6单元格中输入"=SUMPRODUCT(1*(订单明细表!C263:C636="隆华书店"),订单明细表!H263:H636)/12"3.人民币 CNY套题2=MID(A3,5,1)&”班”套题3排名函数 "=RANK(D2,$D$2:$D$21,0)"套题4套题5判断是否周末函数 =IF(WEEKDAY(A3,2)>5,"是","否") A3为单元格表示取当前文字左侧的前三个字符 =LEFT(C3,3) 2013年钱顺卓报销的火车票总计金额为："=SUMPRODUCT(1*(费用报销管理!B3:B401="钱顺卓"),1*(费用报销管理!F3:F401="火车票"),费用报销管理!G3:G401")，两个条件！套题7税收 =IF(K3<=1500,K3*3%,IF(K3<=4500,K3*10%-105,IF(K3<=9000,K3*20%-555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505)套题8班级对应： =IF(MID(A3,4,2)="01","1班",IF(MID(A3,4,2)="02","2班","3班")，套题91.身份证号码年月日=MID(F3,7,4)&"年" &MID(F3,11,2)&"月" &MID(F3,13,2)&"日"（必须用&）2.INT函数（向下取整函数）入职时间"=INT(TODAY()-I3)/365)"，表示当前日期减去入职时间的余额除以365天后再向下取整，求年龄=INT(DAYS360(出生日期,DATE(2015,9,30)/360)3.身份证号码判断男女=IF(MOD(MID(身份证号码,17,1),2)=1,"男","女")4.本科生平均工资 "=AVERAGEIF(员工档案!H3:H37，"本科"，员工档案!K3:K37) （注意：不能用”=”）相同条件求和=SUMIF(一季度销售情况表!$B$2:$B$44, 产品销售汇总表!B2, 一季度销售情况表!$D$2:$D$44)滨海市套题计数=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!A2,成绩单!$B$2:$B$950,按班级汇总!B2)求最大最小值必须同时按 CTRL+SHIFT+ENTER平均分=AVERAGEIFS(成绩单!$D$2:$D$950, 成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)客观题平均分得分率=SUM(小分统计!$A$2:$A$33=按学校汇总!$A2)*(小分统计!C$2:C$33)*(按班级汇总!$C$2:$C$33)/$B2/分值表!B$3季度=INT(1+(MONTH(A3)-1)/3)&"季度"停放时间=DATEDIF(F2,H2,”YD”)*24+(I2-G2)停车费(收费金额)=ROUNDUP(HOUR(J2)*60+MINUTE(J2)/15, 0 )*E2拟收费 =INT(HOUR(J2)*60+MINUTE(J2)/15)*E2达标率折扣工资条=CHOOSE(MOD( ROW(), 3)+1, OFFSET('12月工资表'!A$3, ROW()/3, ), "", '12月工资表'!A$3)完成情况=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")=IF(I3="完成",IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1)+30,IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1)截取拼音名字=LEFT(C3,LENB(C3)-LEN(C3)公务员考试地区=LOOKUP(MID(B4,5,2),LEFT(行政区划代码!B$4:B$38,2),MID(行政区划代码!B$4:B$38,4,LEN(行政区划代码!B$4:B$38)-3)笔试最低分=MIN(IF(名单!F$4:F$1777=统计分析!B5,名单!J$4:J$1777)
