浙江省计算机二级高级Office应用AOA教程.docx

浙江省计算机二级高级 Office 应用 AOA 教程 (一)、三科成绩(数组公式if).xls 到 39使用数组公式，对Sheet1 到 39 （1）鼠标套中[总分]全列(F2 到 F39) （2）在编辑栏变成：=C2:C39+D2:D39+E2:E39（会吗？记住） 按 Ctrl+Shift+Enter 变成 {=C2:C39+D2:D39+E2:E39} “总分”全部自动出来。 同理做“平均分” {=F2:F39/3} 使用RANK 函数，对Sheet1 中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。函数 RANK 的用法：RANK（排名次的数据依据单元，排名的范围——绝对引用，0(或缺省)/非 0） 说明：0（或缺省）以所在单元的名次是降序的位数排名，否则升序。 =RANK(F2,$F$2:$F$39,0) 使用逻辑函数判断Sheet1 中每个同学的每门功课是否均高于平均分，如果是，保存结果为 TRUE，否则，保存结果为 FALSE 将结果保存在表中的“三科成绩是否均超过平均”列当中。 Excel 中有关逻辑函数 AND（X 条件 ， Y 条件， Z 条件，??）都成立（TRUE），则最终成立。 OR （X 条件， Y 条件， Z 条件，??）有一成立，则最终成立。 NOT（条件） 反之！ IF 函数： IF（判别条件，条件成立(真)时取值，条件不成立(假)时取值） 例：=IF（C5（成绩）60，“不及格”，“及格”） AVERAGE 平均值函数 AVERAGE（被求平均值的区域） =IF(AND(C2AVERAGE($C$2:$C$39),D2AVERAGE($D$2:$D$39),E2AVERAGE($E$2:$E$39)),TRUE,FALSE) 根据Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个 分数段的同学人数，将统计结果保存到 Sheet2 中的相应位置。 条件统计个数的函数 COUNTIF COUNTIF(被统计个数的区域之绝对引用，条件) 数学分数位于 0 到 20 分的人数：=COUNTIF(Sheet1!$D$2:$D$39,20) 数学分数位于 20 到 40 分的人数： =COUNTIF(Sheet1!$D$2:$D$39,40)-COUNTIF(Sheet1!$D$2:$D$39,20) 等等 将 Sheet1 复制到Sheet3 中，并对Sheet3 进行高级筛选，要求： a. 筛选条件：“语文”＝75，“数学”＝75，“英语”＝75，“总分”＝250； b. 将结果保存在Sheet3 中。 将 Sheet1 复制到Sheet3 中 在 Sheet3 的空白处，自己先建如下条件 语文 =75 选中 点[数据] 如下图： 数学 =75 英语 =75 总分 =250  Sheet3 中的表格全部。 →[筛选] →[高级筛选] 选中条件区域，成为如下图：确定！ 注：（a）无需考虑是否删除或移动筛选条件; （b）复制数据表后，粘贴时，数据表必须顶格放置。 根据Sheet1 中的结果，在Sheet4 中创建一张数据透视表，要求： 显示是否三科均超过平均分的学生人数； 行区域设置为：“三科成绩是否均超过平均”； 计数项为三科成绩是否均超过平均。 数据透视表： 用“数据透视表和数据透视图向导” 选中 Sheet1 的整个表：[数据]→[数据透视表和数据透视图 P..] ②选择数据源的区域，包括那张表 sheet？（没有汇总过的）。下一步 ②选择数据源的区域，包括那张表 sheet？（没有汇总过的）。下一步 ③“布局”设计将要生成的数据透视表的版式和选项。 ④在以下的图中，按题目要求进行拖放（搞错可拖回去） (二)、服装采购(VLOOKUP 函数).xls 使用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_n