2012年浙江省计算机二级高级办公软件应用AOA教程（全） 图文并茂就算菜鸟也看得懂

field（字段）：指定数据库函数所作用的数据列名。

criteria（条件区域）：一组包含给定条件的单元格区域。 (a) DCOUNTA(A2:K24,B2,M2:N3) (b) 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中。 注意：

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

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

性别 男 英语 >90 信息技术 >95 6. 根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。 要求：

（a）显示不同性别、不同考评结果的学生人数情况； （b）行区域设置为“性别”； （c）列区域设置为“考评”； （d）数据区域设置为“考评”； （e）计数项为“考评”。

(十二)、卡特扫描(VLOOKUP函数).xls

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

要求：根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。 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中。 注意：

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

13

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

销售数量 所属部门 销售金额 >3 市场1部 >1000 6. 根据Sheet1的“3月份销售统计表”中的数据，新建一个数据透视图Chart1。 要求：

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

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

（e）将对应的数据透视表保存在Sheet3中。

（十三）、等级考试(数组公式2c财务函数).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（text, start_num，num_chars） 功能：从文本串中指定位置开始返回特定数目的字符。 MID(A3,8,1) 注意：这里有一“陷阱”，要把单元格的格式从“文本”改为“常规”。 3. 使用统计函数，根据以下要求对Sheet1中“学生成绩表”的数据进行统计。 要求：

（a）统计“考1级的考生人数”，并将计算结果填入到N2单元格中；

（b）统计“考试通过人数（>=60）”,并将计算结果填入到N3单元格中； （c）统计“全体1级考生的考试平均分”，并将计算结果填入到N4单元格中。（其中，计算时候的分母直接使用“N2”单元格的数据） (a) COUNTIF(C3:C57,\

(b) COUNTIF(J3:J57,\

(c) SUMIF(C3:C57,\

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

要求：

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

（b）根据“投资情况表2”中的数据，计算预计投资金额，并将结果填入到E7单元格中。 财务函数：FV 先投一笔钱，每年再投一笔钱，有年利率回报，多少年以后的总金额。 格式：FV (rate，nper，pmt，pv，type)

rate：各期利率（年利率）

nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数（再投资年限）

14

pmt：各期所应支付的金额（每年再投资金额）

pv：现值，即从该项投资开始计算时已经入账的款项，也称为本金 （先投资金额） type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末。 (a) FV(B3,B5,B4,B2)

财务函数：PV 每年投一笔钱，有年利率回报，多少年以后预计投资总金额。 格式：PV(rate，nper，pmt，fv，type) rate：贷款利率（年利率）

nper：该项贷款的总贷款期限或者总投资期 （年限） pmt：各期所应支付的金额 （每年投资金额）

fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末 (b) PV(E3,E4,E2)

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

（a）筛选条件为：“级别”－2、“总分”－>=70 ； （b）将筛选结果保存在Sheet3中。 注意：

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

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

级别 总分 2 >=70 6. 根据Sheet1中的“学生成绩表”，在Sheet4中新建一张数据透视表。 要求：

（a）显示每个级别不同总分的人数汇总情况； （b）行区域设置为“级别”； （c）列区域设置为“总分”； （d）数据区域设置为“总分”； （e）计数项为总分。

(十四)、通讯费年度计划表(INT函数).xls

1.使用VLOOKUP函数,根据Sheet1中的“岗位最高限额明细表”，填充 “通讯费年度计划表”中的“岗位标准”列。

VLOOKUP(C4,$K$5:$L$12,2,FALSE)

2. 使用INT函数，计算Sheet1中“通讯费年度计划表”的“预计报销总时间”列。 要求：

（a）每月以30天计算；

（b）将结果填充在“预计报销总时间”列中。 INT(number) 取整函数（不四舍五入） INT((F4-E4)/30)

Excle非常聪明，日期相减得出来是天数；394天（单元格的格式要设成“常规”） 3. 使用数组公式，计算Sheet1中“通讯费年度计划表”的“年度费用”列。 计算方法为：年度费用 = 岗位标准 * 预计报销总时间。

{D4:D26*G4:G26}

4. 使用函数，根据Sheet1中“通讯费年度计划表”的“年度费用”列，计算预算总金额。

15

要求：

（a）将结果保存在Sheet1中的C2单元格中。

（b）并根据C2单元格中的结果，转换为金额大写形式，保存在Sheet1中的 F2单元格中。 SUM(range) 求和函数 (a) SUM(H4:H26)

(b) =C2 F2单元格格式-特殊-中文大写数字

5. 把Sheet1中的“通讯费年度计划表”复制到Sheet2中，并对Sheet2进行自动筛选。 要求：

（a）筛选条件为：“岗位类别”-技术研发、“报销地点”-武汉； （b）将筛选条件保存在Sheet2中。

注意：

（a）复制过程中，将标题项“通讯费年度计划表”连同数据一同复制; （b）复制数据表后，粘贴时，数据表必须顶格放置。 自动筛选：

6. 根据Sheet1中的“通讯费年度计划表”，在Sheet3中新建一张数据透视表。 要求：

（a）显示不同报销地点不同岗位的年度费用情况； （b）行区域设置为“报销地点”； （c）列区域设置为“岗位类别”； （d）数据区域设置为“年度费用”； （e）求和项为年度费用。

（十五）、医院病人护理统计表(数据库函数.xls

1. 使用VLOOKUP函数,根据Sheet1中的“护理价格表”，对“医院病人护理统计表”中的“护理价格”列进行自动填充。

VLOOKUP(E3,$K$3:$L$5,2,FALSE)

2. 使用INT函数，根据heet1中“医院病人护理统计表”中的“入住时间”列和“出院时间”列中的数据计算护理天数，并把结果保存在“护理天数”列中。

计算方法为： 护理天数 = 出院时间 - 入住时间。

INT(G3-D3) 日期相减得出来是天数；35天（单元格的格式要设成“常规”）,此处照理可不用INT 3. 使用数组公式,根据Sheet1中“医院病人护理统计表”的“护理价格”和“护理天数”列，对病人的护理费用进行计算，并把结果保存在该表的“护理费用”列中。 计算方法为：护理费用 = 护理价格 * 护理天数 {=F3:F30*H3:H30}

4. 使用数据库函数，按以下要求计算: 要求：

（a）计算Sheet1“医院病人护理统计表”中，性别为女性，护理级别为中级护理，护理天数大于30天的人数，并保存N13单元格中；

（b）计算护理级别为高级护理的护理费用总和，并保存N22单元格中。 (a) DCOUNTA(A2:I30,A2,K8:M9)

(b) DSUM(A2:I30,I2,K17:K18)

5. 把Sheet1中的“医院病人护理统计表”复制到Sheet2，进行自动筛选。 要求：

（a）筛选条件为：“性别”－女、“护理级别”－高级护理； （b）将筛选结果保存在Sheet2中。

16