浙江省计算机二级高级Office应用AOA教程 
(一)、三科成绩(数组公式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）
说明：0（或缺省）以所在单元的名次是降序的位数排名，否则升序。
=RANK(F2,$F$2:$F$39,0)
3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE
    将结果保存在表中的“三科成绩是否均超过平均”列当中。
（1）Excel中有关逻辑函数
1.  AND（X条件 ， Y条件， Z条件，……）都成立（TRUE），则最终成立。
2.  OR （X条件，  Y条件， Z条件，……）有一成立，则最终成立。
3.  NOT（条件）    反之！           
（2）IF函数：
IF（判别条件，条件成立(真)时取值，条件不成立(假)时取值）
例：=IF（C5（成绩）<60，“不及格”，“及格”）
（3）AVERAGE平均值函数
AVERAGE（被求平均值的区域）
=IF(AND(C2>AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>AVERAGE($E$2:$E$39)),TRUE,FALSE)
4. 根据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")
等等
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
裤子
鞋子
衣服
裤子
鞋子
衣服
裤子
第2列
……
……
table表
F
G
1
价格表
2
项目
单价
3
衣服
120
4
裤子
80
5
鞋子
150
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中的“统计表”当中。
（1）判断条件求和函数SUMIF
SUMIF(被判断的区域，判断条件，要求和的列)
=SUMIF(A$11:A$43,I12,B$11:B$43)  =SUMIF（各种商品，衣服，采购数量）等等，等等！
=SUMIF(A$11:A$43,I12,F$11:F$43)  =SUMIF（各种商品，衣服，合）  等等，等等！
统计表
统计类别
总采购量
总采购金额
衣服
2800
305424
5. 对Sheet2中的“采购表”进行高级筛选。
a. 筛选条件为：“采购数量”>150，“折扣”>0；
采购数量
折扣
>150
>0
    b. 将筛选结果保存在Sheet2中。
6. 根据Sheet1中的采购表，新建一个数据透视图Chart1，要求：
    a. 该图形显示每个采购时间点所采购的所有项目数量汇总情况；
    b. x坐标设置为“采购时间”；
    c. 求和项为采购数量；
    d. 将对应的数据透视表保存在Sheet3中。
①选择所创建的数据透视表的数据源类型（会自动出默认）。
选：数据透视图（及数据透视表）（R）  下一步
  重要： 对该题来说：出图后在Sheet3中把“项目”拖到“汇总”

(三)、书籍出版(闰年new)(数组公式if).xls
1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的  “金额”列当中。
={=H2:H51*G2:G51}
2. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：
a. 统计出版社名称为“高等教育出版社”的书的种类数；
=COUNTIF(D2:D51,"高等教育出版社")
b. 统计订购数量大于110且小于850的书的种类数。
=COUNTIF(G2:G51,">110")-COUNTIF(G2:G51,">=850")
3. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中的相应位置。
SUMIF(A2:A51,K8,I2:I51)
等等，等等
4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
  说明：闰年定义：能被400整除的年份，或者年数能被4整除而不能被100整除。
取除法后得余数的函数：MOD（被除数，除数）
方法:
IF(                        , "闰年" , "平年")
OR( MOD(x，400)=0,              )
AND( MOD(x，4)=0 ,MOD(x，100)<>0 )
成为：=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"闰年","平年")
5. 将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求：
a. 筛选条件为“订数>=500，且金额总数<=30000”；
订数
金额
sumif函数的使用方法绝对引用
>=500
<=30000
b. 将结果保存在Sheet3中。
6. 根据Sheet1中的结果，在Sheet4中新建一张数据透视表，要求：
    a. 显示每个客户在每个出版社所订的教材数目；
    b. 行区域设置为：“出版社”；
    c. 列区域设置为：“客户”；
d. 计数项为订数。
(四)、电话升级(时间函数).xls
1. 使用时间函数，对Sheet1中用户的年龄进行计算。
    要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。
（1）取年份函数：YEAR（date）,取出date的4位年份整数。
（2）取当前日期函数：NOW（）
=YEAR(NOW())-YEAR(C2)
2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。