AOA考试宝典(三合一完整版) - 图文 下载本文

b. 行区域设置为“温度较高的城市”; c. 计数项设置为温度较高的城市。 数据透视表如下;

计数项:温度较高的城市 温度较高的城市 汇总 杭州 11 上海 4 总计 15

(十一) 学生成绩表.xls

1. 使用REPLACE函数,将Sheet1中“学生成绩表”的学生学号进行更改,并将更改的学号填入到“新学号”列中,学号更改的方法为:在原学号的前面加上“2009”。 例如:\=REPLACE(A3,1,0,\

2. 使用数组公式,对Sheet1中“学生成绩表”的“总分”列进行计算。 计算方法:总分 =语文 + 数学 + 英语 + 信息技术 + 体育 {=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24}

3. 使用IF函数,根据以下条件,对Sheet1中“学生成绩表”的“考评”列进行计算。 条件:如果总分>=350,填充为“合格”;否则,填充为“不合格”。 =IF(J3>=350,\合格\不合格\

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

(a)计算:“语文”和“数学”成绩都大于或等于85的学生人数; =DCOUNTA(A2:K24,B2,M2:N3)

(b)计算:“体育”成绩大于或等于90的“女生”姓名; =DGET(A2:K24,C2,M7:N8)

(c)计算:“体育”成绩中男生的平均分; =DAVERAGE(A2:K24,I2,M12:M13)

(d)计算:“体育”成绩中男生的最高分。 =DMAX(A2:K24,I2,M12:M13)

5. 将Sheet1中的“学生成绩表”复制到Sheet2当中,并对Sheet2进行高级筛选。 要求:

(a)筛选条件为:“性别” - 男;“英语” - >90;“信息技术” - >95; (b)将筛选结果保存在Sheet2中。 性别 男 英语 >90 信息技术 >95

注意:

(a)无需考虑是否删除或移动筛选条件;

(b)复制过程中,将标题项“学生成绩表”连同数据一同复制; (c)复制数据表后,粘贴时,数据表必须顶格放置。

13

6. 根据Sheet1中“学生成绩表”,在Sheet3中新建一张数据透视表。 要求:

(a)显示不同性别、不同考评结果的学生人数情况; (b)行区域设置为“性别”; (c)列区域设置为“考评”; (d)数据区域设置为“考评”; (e)计数项为“考评”。 数据透视表; 计数项:考评 考评 性别 不合格 合格 总计 男 3 12 15 女 1 6 7 总计 4 18 22

(十二) 销售统计表.xls

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

要求:根据“企业销售产品清单”,使用VLOOKUP函数,将产品名称和产品单价填充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。 “产品名称”列;=VLOOKUP(F3,$A$2:$C$10,2,FALSE) “产品单价”列:=VLOOKUP(F3,$A$2:$C$10,3,FALSE)

2. 使用数组公式,计算Sheet1中的“3月份销售统计表”中的销售金额,并将结果填入到该表的“销售金额”列中。计算方法:销售金额 = 产品单价 * 销售数量 {=H3:H44*I3:I44}

3. 使用统计函数,根据“3月份销售统计表”中的数据,计算“分部销售业绩统计表”中的总销售额,并将结果填入该表的“总销售额”列。 =SUMIF($K$3:$K$44,N3,$L$3:$L$44)

4. 在Sheet1中,使用RANK函数,在“分部销售业绩统计”表中,根据“总销售额”对各部门进行排名,并将结果填入到“销售排名”列中。 =RANK(O3,$O$3:$O$5)

5. 将sheet1中的“三月份销售统计表”复制到Sheet2中,对Sheet2进行高级筛选。 要求:

(a)筛选条件为:“销售数量”->3、“所属部门”-市场1部、“销售金额”->1000 (b)将筛选结果保存在Sheet2中。

销售数量 所属部门 销售金额 >3 市场1部 >1000

注意:

(a)无需考虑是否删除或移动筛选条件;

(b)复制过程中,将标题项“三月份销售统计表”连同数据一同复制; (c)复制数据表后,粘贴时,数据表必须顶格放置。

6. 根据Sheet1的“3月份销售统计表”中的数据,新建一个数据透视图Chart1。

14

要求:

(a)该图形显示每位经办人的总销售额情况 (b)x坐标设置为“经办人”;

(c)数据区域设置为“销售金额”; (d)求和项为销售金额;

(e)将对应的数据透视表保存在Sheet3中。 数据透视图Chart1:

数据透视表:

求和项:销售金额 经办人 甘倩琦 李成蹊 刘 惠 孙国成 王 勇 吴 仕 吴小平 许 丹 赵 荣 总计

汇总 5752 4416 18232 6920 3976 12116 3424 11352 6436 72624 15

(十三) 学生成绩表.xls

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

计算方法:总分 = 单选题 + 判断题 + windows操作题 + Excel操作题 + PowerPoint操作题 + IE操作题

{=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57}

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

(a)学号中的第八位指示的考生所考级别,例如:“085200821023080”中的“2”标识了该考生所考级别为二级

(b)在“级别”列中,填入的数据是函数的返回值。 =MID(A3,8,1)

3. 使用统计函数,根据以下要求对Sheet1中“学生成绩表”的数据进行统计。 要求:

(a)统计“考1级的考生人数”,并将计算结果填入到N2单元格中; =COUNTIF(C3:C57,\

(b)统计“考试通过人数(>=60)”,并将计算结果填入到N3单元格中; =COUNTIF(J3:J57,\

(c)统计“全体1级考生的考试平均分”,并将计算结果填入到N4单元格中。(其中,计算时候的分母直接使用“N2”单元格的数据) =SUMIF(C3:C57,\

4. 使用财务函数,根据以下要求对Sheet2中的数据进行计算。 要求:

(a)根据“投资情况表1”中的数据,计算10年以后得到的金额,并将结果填入到B7单元格中;

=FV(B3,B5,B4,B2)

(b)根据“投资情况表2”中的数据,计算预计投资金额,并将结果填入到E7单元格中。 =PV(E3,E4,E2)

5. 将Sheet1中的“学生成绩表”复制到Sheet3,并对Sheet3进行高级筛选。 要求:

(a)筛选条件为:“级别”-2、“总分”->=70 ; (b)将筛选结果保存在Sheet3中。

级别 总分 2 >=70 注意:

(a)无需考虑是否删除或移动筛选条件;

(b)复制过程中,将标题项“学生成绩表”连同数据一同复制; (c)复制数据表后,粘贴时,数据表必须顶格放置。

16