浙江省计算机二级办公软件高级应用技术Excel解答

文件if01.xls
1．按职称求取其工资的平均值，并放在Sheet2表中。

（1）把Sheet1表中的数据复制到Sheet2表中。

（2）按职称进行升序排序。

（3）然后分类汇总。

2．将职称为工程师，并且工资大于380的职工复制到F-H列。

3．在工资字段后增加等级字段，400元以上为等级为A，其余为B。

=IF(C2>=400,"A","B")
文件if02.xls
1．分别在G列和H列增加“平均分”与“等级”标题，并计算出平均分，其中平均分在85分及以上为优秀，84-75为良，60-74为及格，60分以下为不及格。

=IF(G2>85,"优秀",IF(G2>=75, "良", IF(G2>=60, "及格", "不及格") )) 2．新表格格式与旧表格格式相同。

方法一：选中原来的数据区域(C1:F31)，然后“复制”，再选中所有的数据区域(C1:H31)或新增的数据区域(G1:F31)，利用“选择性粘贴”→“格式”。

方法二：利用格式刷。

文件Excel03.xls
1．利用数组公式求金额：
数组是项的集合。

在Excel中，这些项可以位于一行（称为一维水平数组）中，也可位于一列（称为一维垂直数组）中或多行或多列（二维数组）中。

无法在Excel中创建三维数组或三维数组公式。

数组公式是指可以在数组的一项或多项上执行多个计算的公式。

数组公式可以返回多个结果，也可返回一个结果。

例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。

也可以将数组公式放入单个单元格中，然后计算单个量。

位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。

方法一：
（1）输入=SUM(G2*H2)
（2）然后敲Ctrl+Shift+Enter，显示的结果为：{=SUM(G2*H2)} （3）然后按填充柄拖动。

方法二：
（1）选择单元格I2到I51。

（2）在编辑栏中输入公式：=G2:G51*H2:H51，
然后敲Ctrl+Shift+Enter。

（3）公式自动变为：{=G2:G51*H2:H51}
2．使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：
（1）统计出版社名称为“高等教育出版社”的书的种类数：
=COUNTIF(D2:D51, "高等教育出版社")
（2）订购数量大于110，且小于850的书的种类数：
=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52, "<=110")
3．用户支付总额
（1）c1用户：=SUMIF($A$2:$A$51,"c1", I2:I51)
（2）c2用户：=SUMIF($A$2:$A$51,"c2", I2:I51)
（3）c3用户：=SUMIF($A$2:$A$51,"c3", I2:I51)
（4）c4用户：=SUMIF($A$2:$A$51,"c4", I2:I51)
4．闰年的判断
=IF(OR(AND( MOD(A2,4)=0,MOD(A2,100)<>0 ),MOD(A2,400)=0 ),"闰年","平年")
5．将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求：（1）筛选条件为“订数>=500，且金额总数<=30000”；
（2）将结果保存到Sheet2中。

6．根据Sheet1中的结果，在Sheet4中新建一张收费处数据透视表，要求：
（1）显示每个客户在每个出版社所订的教材数目；
（2）行区域设置为：“出版社”；
（3）列区域设置为：“客户”；
（4）计数项为订数。

文件if&countif01.xls
1．在Sheet1表中C19：C22填写各个工资级差人数。

600及以上
=COUNTIF(C2:C17,">=600")
500-600
=COUNTIF(C2:C17,"<600")-COUNTIF(C2:C17, "<500") 400-500
=COUNTIF(C2:C17,"<500")-COUNTIF(C2:C17, "<400") 400以下
=COUNTIF(C2:C17,"<400")
2、在Sheet1表中，在工资字段后增加等级字段，其中职称为“高工”为等级为A，“工程师”为B，“助工”为C，工人为“D”。

=IF(B2="高工","A", IF(B2="工程师","B",IF(B2="助工","C","D")))
文件if&countif02.xls
1．求语文功课的最高分和最低分。

=MAX(C2:C32)
=MIN(C2:C32)
2．求各分段的人数及比例，90以上（包括90）为优，80以上（包括80）到90为良，70以上（包括70）到80为中，60以上（包括60）到70为及格，60以下为不及格。

优：=COUNTIF($C$2:$C$32,">=90")
良：=COUNTIF($C$2:$C$32,"<90")-COUNTIF($C$2:$C$32,"<80") 中：=COUNTIF($C$2:$C$32,"<80")-COUNTIF($C$2:$C$32,"<70")
及格：=COUNTIF($C$2:$C$32,"<70")-COUNTIF($C$2:$C$32,"<60") 不及格：=COUNTIF($C$2:$C$32,"<60")
比例：=C38/COUNT($C$2:$C$32)
3．利用if函数判断语文和数学是否都及格，填“是”与“否”。

=IF(AND(C2>=60,D2>=60),"是","否")
文件RANK&vLOOKUP.xls
1．在sheet1里有一列考试成绩,利用rank函数根据考试成绩求名次。

=RANK(D4,$D$4:$D$127,0)
2．在sheet2有一个小表，用一条公式，把这些人所在学校找到并填到sheet2的考生单位所在的列，提示用vlookup函数。

=IF(A2="","",VLOOKUP(A2,题目!A3:G127,7,TRUE))。