浙江省计算机二级Excel公式一览（6）

第二十四题 员工工资表

【闰年公式】2.在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。

=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0)),TRUE,FALSE)

*编者注：里面的TODAY()也可以与NOW()进行互换，在这里没有影响。

【查询函数】3.在员工工资表，使用Vlookup函数和Hlookup函数，填写“基本工资”列：基本工资=岗位工资+学历津贴。

=VLOOKUP(C3,$L$15:$M$17,2,0)+HLOOKUP(E3,$O$14:$Q$15,2,0)

*编者注：竖向查找用Vlookup，横向查找用Hlookup。

【数组公式】4.使用数组公式计算“应发工资”列，应发工资=基本工资+绩效工资。

*编者注：同时摁住Ctrl+Shift，不松手的情况下再去按Enter。

【数据库函数】5.使用数据库函数统计：（1）业务代表中绩效工资最高的（填入单元格P21）和应发工资最低的（填入单元格P22）；（2）所有女业务代表的应发工资总额（填入单元格P23）。

在P21单元格中填入：=DMAX(A2:I39,7,G2:G39)

在P22单元格中填入：=DMIN(A2:I39,8,H2:H39)

在P23单元格中填入：=DSUM(A2:I39,8,R19:S20)

*编者注：使用dsum函数时，和高级筛选一样，需要自己创建条件区域，在这里条件区域的范围就是dsum函数的第3个参数。

【逻辑函数】6.使用逻辑函数and和or判断是否是高级职位员工、且应发工资大于等于10000，填入“是”或“否”高级职位指项目经理和销售经理。

=IF(AND(OR(C3="项目经理",C3="销售经理"),H3>10000),"是","否")

第二十五题 员工信息表

【四舍五入】2.在Sheet4中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单元格中。

=ROUND(B1*96,0)/96

【文本函数】3.使用REPLACE函数，对Sheet1中“员工信息表”的员工代码进行升级。

=REPLACE(B3,3,0,0)

*编者注：replace函数第二个参数指的是从第几位文本或数值开始进行替换，第三个参数指的是需要替换掉原来单元格多少位文本或数值，第三个参数写0即表示不进行替换，直接进行增加。

【时间函数】4.使用时间函数，计算Sheet1中“员工信息表”的“年龄”和“工龄”列。

年龄：=YEAR("2013-5-1")-YEAR(E3)

工龄：=YEAR("2013-5-1")-YEAR(G3)

*编者注：需要将这一列单元格格式设置为“常规”。

【统计函数】5.使用统计函数，根据Sheet1中“员工信息表”的数据，对以下条件进行统计。

在N3单元格中填入：=COUNTIF(D2:D66,"男")

在N4单元格中填入：=COUNTIF(I2:I66,"高级工程师")

在N5单元格中填入：=COUNTIF(H2:H66,">=10")

【逻辑函数】6.使用逻辑函数，判断员工是否有资格评“高级工程师”。

=IF(AND(H3>20,I3="工程师"),TRUE,FALSE)