aoa三一宝典(计算机二级考试)要点.doc

省二级AOA考试 宝典 毛根生 一、AOA Excel 考试题（17题对照）参考 (一)、三科成绩(数组公式if).xls 1. 使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。 （1）鼠标套中[总分]全列(F2到F39) （2）在编辑栏变成：=C2:C39+D2:D39+E2:E39（会吗？记住） （3）按Ctrl+Shift+Enter 变成 {=C2:C39+D2:D39+E2:E39} （4）“总分”全部自动出来。 同理做“平均分” {=F2:F39/3} 2. 使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。 函数RANK的用法：RANK（排名次的数据依据单元，排名的范围——绝对引用，0(或缺省)/非0） 1. AND（X条件 ， Y条件， Z条件，……）都成立（TRUE），则最终成立。 2. OR （X条件， Y条件， Z条件，……）有一成立，则最终成立。 3. NOT（条件） 反之！ （2）IF函数： IF（判别条件，条件成立(真)时取值，条件不成立(假)时取值） 例：=IF（C5（成绩）60，“不及格”，“及格”） （3）AVERAGE平均值函数 AVERAGE（被求平均值的区域）=COUNTIF(Sheet1!$D$2:$D$39,20) 数学分数位于20到40分的人数： =COUNTIF(Sheet1!$D$2:$D$39,40)-COUNTIF(Sheet1!$D$2:$D$39,20) 等等 5. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求： a. 筛选条件：“语文”＝75，“数学”＝75，“英语”＝75，“总分”＝250； b. 将结果保存在Sheet3中。 （1）将Sheet1复制到Sheet3中 （2）在Sheet3的空白处，自己先建如下条件 语文 数学 英语 总分 =75 =75 =75 =250 （3）选中Sheet3中的表格全部。 （4）点[数据]→[筛选] →[高级筛选] （5）如下图： （6）选中条件区域，成为如下图：确定！ 注：（a）无需考虑是否删除或移动筛选条件; （b）复制数据表后，粘贴时，数据表必须顶格放置。 6. 根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求： a. 显示是否三科均超过平均分的学生人数； b. 行区域设置为：“三科成绩是否均超过平均”； c. 计数项为三科成绩是否均超过平均。 数据透视表： 用“数据透视表和数据透视图向导” 选中Sheet1的整个表：[数据]→[数据透视表和数据透视图P..] ①选择所创建的数据透视表的数据源类型（会自动出默认）。下一步 ②选择数据源的区域，包括那张表sheet？（没有汇总过的）。下一步 ③“布局”设计将要生成的数据透视表的版式和选项。 ④在以下的图中，按题目要求进行拖放（搞错可拖回去） (二)、服装采购(VLOOKUP函数).xls 1. 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用VLOOKUP函数， 将其单价自动填充到采购表中的“单价”列中。 （1）VLOOKUP叫引用函数；一个数组或表格②table_array的最左列中含有特定值的字段，到另一数据表格、数组或数据库①look_value去查找，找到同值时，把②的第col_index_num列的对应值填到①的某一指定单元格中。 VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup） look_value：被查找的列的值， 如A11,A12,……会相对下去 table_array：引用的数据表格、数组或数据库，如$F$2:$G$4 绝对引用。 col_index_num：一个数字，代表要返回的值位于table_array中的第几列。 rang_lookup：一个逻辑值，如果其值为TRUE或被省略，则符合多少算多少；如果该值为FALSE时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。 =VLOOKUP(A11,$F$2:$G$5,2,FALSE) Look表 A B C D 项目 …… …… 单价 …… …… 11 衣服 12 裤子 鞋子 衣服 裤子 鞋子 衣服 裤子 …… …… table表 F G 1