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

第二十一题 零件检测结果表

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

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

【数组公式】3. 使用数组公式，根据Sheet1中“零件检测结果表”的“外轮直径”和“内轮直径”列，计算内外轮差，并将结果表保存在“轮差”列中。

选中“轮差（mm）”列，在公式编辑栏输入=B3:B50-C3:C50，然后同时摁Ctrl+Shift+Enter。

【逻辑函数】4. 使用IF函数，对Sheet1中“零件检测结果表”的“检测结果”列进行填充。

在E3单元格中输入=IF(D3<4,"合格","不合格")，然后摁回车。

【统计函数】5. 使用统计函数，根据以下要求进行计算，并将结果保存在相应位置。

在K4单元格中输入=COUNTIF(D3:D50,"0")，然后摁回车。

在K5单元格中输入=COUNTIF(E3:E50,"合格")/COUNTA(E3:E50)，然后摁回车。

COUNTA(value1, [value2], ...)

【文本函数】6. 使用文本函数，判断Sheet1中“字符串2”在“字符串1”中的起始位置并把返回结果保存在Sheet1中的K9单元格中。

在K9单元格中输入=FIND(J9,I9,1)，然后摁回车。

FIND(find_text,within_text,start_num)

第二十二题 图书销售清单

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

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

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

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

【查询函数】3. 使用文本函数和VLOOKUP函数，填写“货品代码”列，规则是将“登记号”的前4位替换为出版社简码。

在B3单元格中输入=REPLACE(A3,1,4,VLOOKUP(E3,$K$7:$N$24,3,0))，注意绝对引用，然后进行自动填充。

【数组公式】4. 使用数组公式填写“销售额”列，销售额=单价×销售数，并将销售额四舍五入到整数。

选中“销售额”列后，在公式编辑栏输入=ROUND(D3:D102*F3:F102,0)，然后同时摁Ctrl+Shift+Enter。

【统计函数】5. 使用SUMIF函数计算每个出版社的销售总额，填入表1。

在L7单元格中输入=SUMIF($E$3:$E$102,K7,$G$3:$G$102)，注意绝对引用，然后进行自动填充。

【数据库函数】6. 使用DAVERAGE函数计算每个销售代表平均销售额，填入表2，并用RANK函数计算其排名。

自行创建出下图所示的条件区域：

示例公式将条件区域位置放至Sheet1!$L$36:$P$38，公式依此而写。

在L30单元格中输入=DAVERAGE($A$2:$H$102,6,L36:L37)，注意绝对引用，然后横向向右进行自动填充。

在L31单元格中输入=RANK(L30,$L$30:$P$30,0)，注意绝对引用，然后横向向右进行自动填充。

第二十三题 房屋销售清单

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

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

【逻辑函数】3. 使用IF函数自动填写“折扣率”列，标准：面积小于140的，九九折（即折扣率为99%）；小于200但大于等于140的，九七折；大于等于200的，九五折。并使用数组公式和ROUND函数填写“房价”列，四舍五入到百位。

在I3单元格中输入=IF(G3<140,"0.99",IF(AND(G3>=140,G3<200),"0.97","0.95"))，然后进行自动填充。

选中“房价”列，在公式编辑栏输入=ROUND(G3:G39*H3:H39*I3:I39,-2)，然后同时摁Ctrl+Shift+Enter。

【统计函数】4. 使用CONUTIF和SUMIF函数统计面积大于等于140的房屋户数和房价总额，结果填入单元格M9和N9。

在M9单元格中输入=COUNTIF(G3:G39,">140")，然后摁回车。

在N9单元格中输入=SUMIF(G3:G39,">140",J3:J39)，然后摁回车。

【文本函数】5. 将“小灵通号码”列号码升位并填入“新电话号码”栏，要求使用文本函数完成。

在C3单元格中输入="133"&REPLACE(B3,5,0,8)，然后进行自动填充。

【闰年公式】6. 判断客户的出生年份是否为闰年，将结果“是”或“否”填入“闰年”栏。

在E3单元格中输入=IF(OR(MOD(YEAR(D3),400)=0,AND(MOD(YEAR(D3),4)=0,MOD(YEAR(D3),100)<>0)),"是","否")，然后进行自动填充。

【分类汇总】7. 先将“房屋销售清单”拷贝到Sheet2中，然后汇总不同销售人员所售房屋总价。

分类汇总前必须以想要汇总的字段为主要关键字对数据区域进行排序，按以下步骤进行操作：