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

第十六题 图书订购信息表

【逻辑函数】1. 在Sheet4中，使用函数，根据E1单元格中的身份证号码判断性别，结果为“男”或“女”，存放在F1单元格中。

在F1单元格中输入=IF(MOD(MID(E1,17,1),2)=1,"男","女")，然后摁回车。

【逻辑函数】3. 使用IF和MID函数，根据Sheet1中的“图书订购信息表”中的“学号”列对“所属学院”列进行填充。要求：根据每位学生学号的第七位填充对应的“所属学院”。

在C3单元格中输入=IF(MID(A3,7,1)="1","计算机学院",IF(MID(A3,7,1)="0","电子信息学院"))，然后进行自动填充。

【统计函数】4. 使用COUNTBLANK函数，对Sheet1中的“图书订购信息表”中的“订书种类数”列进行填充。

在H3单元格中输入=4-COUNTBLANK(D3:G3)，然后进行自动填充。

【简单公式】5. 使用公式，对Sheet1中的“图书订购信息表”中的“订书金额（元）”列进行填充。

在I3单元格中输入公式=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$6，注意绝对引用，然后进行自动填充。

【统计函数】6. 使用统计函数，根据Sheet1中“图书订购信息表”的数据，统计订书金额大于100元的学生人数，将结果保存在Sheet1的M9单元格中。

在M9单元格中输入公式=COUNTIF(I3:I50,">100")，然后摁回车。

第十七题 学生成绩表

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

在C1单元格中输入=ROUND(B1*96,0)/96，然后摁回车。

*一天24小时由96个15分钟组成。

【数组公式】3. 在Sheet1“学生成绩表”中，使用REPLACE函数和数组公式，将原学号转变为新学号并填入“新学号”列中。

选中“新学号”列，在公式编辑栏输入=REPLACE(A3:A30,5,0,5)，然后同时摁Ctrl+Shift+Enter。

【逻辑函数】4. 使用IF函数和逻辑函数，对Sheet1“学生成绩表”中的“结果1”和“结果2”列进行填充。填充的内容根据以下条件确定。

在F3单元格中输入=IF(OR(AND(D3="男",E3<14),AND(D3="女",E3<16)),"合格","不合格")，然后进行自动填充。

在H3单元格中输入=IF(OR(AND(D3="男",G3>7.5),AND(D3="女",G3>5.5)),"合格","不合格")，然后进行自动填充。

【统计函数】5. 对Sheet1“学生成绩表”中的数据，根据以下条件，使用统计函数进行统计。

在K4单元格中输入=MIN(E3:E30)，然后摁回车。

在K5单元格中输入=COUNTIF(F3:F30,"合格")，然后摁回车。

【财务函数】6. 根据Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。

在E2单元格中输入=PMT(B4,B3,B2)，然后摁回车。

PMT(rate, nper, pv, [fv], [type])

在E3单元格中输入=IPMT(B4/12,9,B3*12,B2)，然后摁回车。

IPMT(rate, per, nper, pv, [fv], [type])

第十八题 员工资料表

【计数函数】1. 在Sheet5中使用函数计算A1:A10中奇数的个数，结果存放在A12单元格中。

在A12单元格中输入=SUMPRODUCT(MOD(A1:A10,2))，然后摁回车。

【四舍五入】2. 在Sheet5中，使用函数，将B1单元格中的数四舍五入到整百，结果存放在C1单元格中。

在C1单元格中输入=ROUND(B1,-2)，然后摁回车。

【文本函数】3. 仅使用MID函数和CONCATENATE函数，对Sheet1中“员工资料表”的“出生日期”列进行填充。

在G3单元格中输入=CONCATENATE(MID(E3,7,4),"年",MID(E3,11,2),"月",MID(E3,13,2),"日")，然后摁回车。

CONCATENATE(text1, [text2], ...)

【查询函数】4. 根据Sheet1中“职务补贴率表”的数据，使用VLOOKUP函数，对“员工资料表”中的“职务补贴率”列进行自动填充。

在J3单元格中输入=VLOOKUP(H3,$A$3:$B$6,2,0)，注意绝对引用，然后进行自动填充。

【数组公式】5. 使用数组公式，在Sheet1中对“员工资料表”的“工资总额”列进行计算，并将计算结果保存在“工资总额”列。

选中“工资总额”列，在公式编辑栏输入=I3:I38*(1+J3:J38)，然后同时摁Ctrl+Shift+Enter。

【财务函数】6. 在Sheet2中，根据“固定资产情况表”，使用财务函数，对以下条件进行计算。

在E2单元格中输入=SLN(B2,B3,B4*365)，然后摁回车。

SLN (cost, salvage, life)

在E3单元格中输入=SLN(B2,B3,B4*12)，然后摁回车。

在E4单元格中输入=SLN(B2,B3,B4)，然后摁回车。

第十九题 公司员工人事信息表

【计数函数】1. 在Sheet4中使用函数计算A1:A10中奇数的个数，结果存放在A12单元格中。

在A12单元格中输入=SUMPRODUCT(MOD(A1:A10,2))，然后摁回车。

【数据查重】2. 在Sheet4中设定B列中不能输入重复的数值。

“允许”选择自定义，勾选忽略空值，公式输入=COUNTIF(B:B,B1)=1。

【文本函数】3. 使用大小写转换函数，根据Sheet1中“公司人事信息表”的“编号”列，对“新编号”列进行填充。

在B3单元格中输入=UPPER(A3)，然后进行自动填充。

UPPER(text)

【文本函数】4. 使用文本函数和时间函数，根据Sheet1中“公司人事信息表”的“身份证号码”列，计算用户的年龄，并保存在“年龄”列中。

在F3单元格中输入=YEAR(TODAY())-MID(G3,7,4)，然后进行自动填充。

【数据库函数】5. 在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。

在E31单元格中输入=DGET(A2:J27,3,L3:M4)，然后摁回车。

【文本函数】6. 使用函数，判断Sheet1中L12和M12单元格中的文本字符串是否完全相同。

在N12单元格中输入=EXACT(L12,M12)，然后摁回车。

第二十题 打印机备货清单

【逻辑函数】1. 在Sheet4中，使用函数，根据A1单元格中的身份证号码判断性别，结果为“男”或“女”，存放在A2单元格中。倒数第二位为奇数的为“男”，为偶数的为“女”。

在A2单元格中输入=IF(MOD(MID(A1,17,1),2)=1,"男","女")，然后摁回车。

【闰年公式】2. 在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

在Sheet4表B1单元格中输入=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0)),TRUE,FALSE)，然后摁回车。

【逻辑函数】3. 使用IF函数，对Sheet1中的“界面”列，根据“打印机类型”列的内容，进行自动填充。

在E3单元格输入=IF(D3="点阵","D",IF(D3="喷墨","P",IF(D3="黑白激光","H",IF(D3="彩色激光","C","T"))))，然后进行自动填充。

【数组公式】4. 使用REPLACE函数和数组公式对“新货号”列进行填充。

选中“新货号”列，在公式编辑栏输入=REPLACE(A3:A189,1,3,"0233PRT")，然后同时摁Ctrl+Shift+Enter。

【查询函数】5. 使用VLOOKUP函数对“供货商”列进行填充。

在I3单元格输入=VLOOKUP(B3,$M$12:$N$29,2,0)，注意绝对引用，然后进行自动填充。

【数据库函数】6. 使用数据库函数统计厂牌为EPSON，兼容性为支持的型号总数（不计空白型号）。

在N38单元格输入=DCOUNTA(A2:F189,F2,M35:N36)，然后摁回车。

【自定义序列】7. 将Sheet1中的“打印机备货清单”复制到Sheet2中，然后依照打印机类型重新排序。

复制后，依次进行如下操作：