计算机三级考试宝典(Excel).pdfVIP

(一)学号(数组公式if).xls

1.使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均

分”列当中。

总分：{=C2:C39+D2:D39+E2:E39}平均分：{=F2:F39/3}

（注意，题目明确要求用数组公式，就必须用数组公式，否则不得分。数组公式中的一对{}不是人

为录入的，必须用Ctrl+Shift+Enter组合健输入。）

2.使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”

列当中。

排名：=RANK(G2,G$2:G$39)或者=RANK(F2,F$2:F$39)然后利用填充柄复制公式

3.使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，

否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。

=IF(AND(C2AVERAGE($G$2:$G$39),D2AVERAGE($G$2:$G$39),E2AVERAGE($G$2:$G$39)),TRUE,FAL

SE)

或者：

=IF(C2AVERAGE($C$2:$C$39),IF(D2AVERAGE($D$2:$D$39),IF(E2AVERAGE($E$2:$E$39),TRUE,FA

LSE),FALSE),FALSE)

4.根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计

结果保存到Sheet2中的相应位置。

数学分数位于0到20

分的人数：

数学分数位于20到

40分的人数：

数学分数位于40到

60分的人数：

数学分数位于60到

80分的人数：

数学分数位于80到

100分的人数：

或者：利用数据库函数Dcount，如下所示，在sheet2表中自己构建条件区间，如；B10:C11。

=DCOUNT(Sheet1!A1:I39,Sheet1!D1,B10:C11)

数学数学数学数学数学数学数学数学数学数学

=020=2040=4060=6080=80=100

5.将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求：

a.筛选条件：“语文”＝75，“数学”＝75，“英语”＝75，“总分”＝250；

筛选的条件区间如下：

语文数学英语总分

=75=75=75=250

b.将结果保存在Sheet3中。

注：（a）无需考虑是否删除或移动筛选条件;

（b）复制过程中，将标题项“学生成绩表”连同数据一同复制;

（c）复制数据表后，粘贴时，数据表必须顶格放置。

(二)折扣表(采购表)(VLOOKUP函数).xls

1.使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。

要求：根据“价格表”中的商品单价，利用VLOOKUP函数，

将其单价自动填充到采购表中的“单价”列中。

=VLOOKUP(A11,F$2:G$5,2,0)

或者用数组公式做：

=VLOOKUP($A$11:$A$43,$F$2:$G$5,2,0)

2.使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。

要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的

“折扣“列中。

=IF(B11=A$6,B$6,IF(B11=A$5,B$5,IF(B11=A$4,B$4,B$3)))

3.利用公式，计算Sheet1中的“合计金额”。

要求：根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。

计算公式：单价*采购数量*（1-折扣）

=D11*B11*(1-E11)

4.使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”

当中。

采购总量：=SUMIF(A$11:A$43,I12,B$11:B$43)

采购总金额：=SUMIF(A$11:A$43,I12,F$11:F$43)

(三)客户（教材）(数组公式if).