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

第十一题 学生成绩表

【计数函数】2. 在Sheet1中使用函数计算全部数学成绩中奇数的个数，结果存放在A41单元格中。

在A41单元格中输入=SUMPRODUCT(MOD(F3:F24,2))，然后摁回车。

SUMPRODUCT (array1， [array2]， [array3]， ...)

【替换函数】3. 使用REPLACE函数，将Sheet1中“学生成绩表”的学生学号进行更改，并将更改后的学号填入到“新学号”列中，学号更改的方法为：在原学号的前面加上“2009”。

在B3单元格中输入=REPLACE(A3,1,0,"2009")，然后进行自动填充。

【数组公式】4. 使用数组公式，对Sheet1中“学生成绩表”中的“总分”列进行计算。

选中“总分”列，在公式编辑栏输入=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24，然后同时摁Ctrl+Shift+Enter。

【逻辑函数】5. 使用IF函数，根据以下条件，对Sheet1中“学生成绩表”的“考核”列进行计算。

在K3单元格输入=IF(J3>=350,"合格","不合格")，然后进行自动填充。

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

在D28单元格中输入=DCOUNTA(A2:K24,6,M2:N3)，然后摁回车。

DCOUNTA(database, field, criteria)

在D29单元格中输入=DGET(A2:K24,3,M7:N8)，然后摁回车。

DGET(database, field, criteria)

在D30单元格中输入=DAVERAGE(A2:K24,9,M12:M13)，然后摁回车。

DAVERAGE (database, field, criteria)

在D31单元格中输入=DMAX(A2:K24,9,M12:M13)，然后摁回车。

DMAX(database, field, criteria)

第十二题 三月份销售统计表

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

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

【查询函数】3. 使用VLOOKUP函数，对Sheet1中的“三月份销售统计表”的“产品名称”列和“产品单价”列进行填充。

在G3单元格中输入=VLOOKUP(F3,$A$2:$C$10,2,0)，注意绝对引用，然后进行自动填充。

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

【数组公式】4. 使用数组公式，计算Sheet1中的“三月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。

选中“销售金额”列，在公式编辑栏输入=H3:H44*I3:I44，然后同时摁Ctrl+Shift+Enter。

【统计函数】5. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。

在O3单元格中输入=SUMIF($K$3:$K$44,N3,$L$3:$L$44)，注意绝对引用，然后进行自动填充。

【排序函数】6. 在Sheet1中，使用RANK函数，在“分部销售业绩统计表”中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。

在P3单元格中输入=RANK(O3,$O$3:$O$5,0)，注意绝对引用，然后进行自动填充。

第十三题 学生成绩表

【数据查重】1. 在Sheet5中设定F列中不能输入重复的数值。

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

【数组公式】3. 使用数组公式，根据Sheet1中“学生成绩表”的数据，计算考试总分，并将结果填入到“总分”列中。

选中“总分”列，在公式编辑栏输入=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57，然后同时摁Ctrl+Shift+Enter。

【文本函数】4. 使用文本函数中的一个函数，在Sheet1中，利用“学号”列的数据，根据以下要求获得考生所考级别，并将结果填入“级别”列中。

在C3单元格中输入=MID(A3,8,1)，然后进行自动填充。

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

在N2单元格中输入=COUNTIF(C3:C57,"1")，然后摁回车。

在N3单元格中输入=COUNTIF(J3:J57,">=60")，然后摁回车。

在N4单元格中输入=SUMIF(C3:C57,"1",J3:J57)/N2，然后摁回车。

【财务函数】6. 使用财务函数，根据以下要求对Sheet2中的数据进行计算。

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

FV(rate,nper,pmt,[pv],[type])

在E7单元格中输入=PV(E3,E4,E2,0)，然后摁回车。

PV(rate, nper, pmt, [fv], [type])

第十四题 通讯费年度计划表

【闰年公式】1. 在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. 使用VLOOKUP函数，根据Sheet1中的“岗位最高限额明细表”，填充“通讯费年度计划表”中的“岗位标准”列。

在D4单元格输入=VLOOKUP(C4,$K$5:$L$12,2,0)，注意绝对引用，然后进行自动填充。

【取整函数】4. 使用INT函数，计算Sheet1中“通讯费年度计划表”的“预计报销总时间”列。

在G4单元格输入=INT((F4-E4)/30)，然后进行自动填充。

INT(Number)

【数组公式】5. 使用数组公式，计算Sheet1中“通讯费年度计划表”的“年度费用”列。

选中“年度费用”列，在公式编辑栏输入=D4:D26*G4:G26，然后同时摁Ctrl+Shift+Enter。

【求和公式】6. 根据Sheet1中“通讯费年度计划表”的“年度费用”列，计算预算总金额。

在C2单元格输入=SUM(H4:H26)，然后摁回车。

第十五题 医院病人护理统计表

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

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

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

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

【查询函数】3. 使用VLOOKUP函数，根据Sheet1中的“护理价格表”，对“医院病人护理统计表”中的“护理价格”列进行自动填充。

在F3单元格中输入公式=VLOOKUP(E3,$K$3:$L$5,2,0)，注意绝对引用，然后进行自动填充。

【数组公式】4. 使用数组公式，根据Sheet1中“医院病人护理统计表”中的“入住时间”列和“出院时间”列中的数据计算护理天数，并把结果保存在“护理天数”列中。

选中“护理天数”列，在公式编辑栏输入=G3:G30-D3:D30，然后同时摁Ctrl+Shift+Enter。

【数组公式】5. 使用数组公式，根据Sheet1中“医院病人护理统计表”中的“护理价格”列和“护理天数”列，对病人的护理费用进行计算，并把结果保存在“护理费用”列中。

选中“护理费用”列，在公式编辑栏输入=F3:F30*H3:H30，然后同时摁Ctrl+Shift+Enter。

【数据库函数】6. 使用数据库函数，按以下要求计算。

在N13中输入=DCOUNTA(A2:I30,3,K8:M9)，然后摁回车。

在N22中输入=DSUM(A2:I30,9,K17:K18)，然后摁回车。