发布时间 : 星期一 文章浙江省计算机二级高级办公软件PPT+EXCEL大全更新完毕开始阅读
（e）将对应的数据透视表保存在Sheet3中。
（十七）、学生体育成绩表(REPLACE函数2c财务函数).xls
1. 在sheet1“学生成绩表”中，使用REPLACE函数和数组公式，将原学号转变成新学号，同时将所得的新学号填入“新学号”列中。转变方法：将原学号的第四位后面加上“5”，例如：\-> \
{=REPLACE(A3:A30,5,0,5)}
2. 使用IF函数和逻辑函数，对Sheet1“学生成绩表”中的“结果1”和“结果2”列进行填充。 要求：
填充的内容根据以下条件确定：(将男生、女生分开写进IF函数当中) -结果1：如果是男生，成绩<14.00， 填充为“合格”； 成绩>=14.00，填充为“不合格”； 如果是女生，成绩<16.00， 填充为“合格”； 成绩>=16.00，填充为“不合格”； -结果2：如果是男生，成绩>7.50， 填充为“合格”； 成绩<=7.50，填充为“不合格”； 如果是女生，成绩>5.50， 填充为“合格”； 成绩<=5.50,填充为“不合格”。
-结果1：IF(OR(AND(D3=\男\女\合格\不合格\-结果2：IF(OR(AND(D3=\男\女\合格\不合格\ 3. 对于Sheet1“学生成绩表”中的数据，根据以下条件，使用统计函数进行统计。 要求：
（a）获取“100米跑的最快的学生成绩”， 并将结果填入到Sheet1的K4单元格中； （b）统计“所有学生结果1为合格的总人数” 并将结果填入Sheet1的K5单元格中。 (a) MIN(E3:E30)
(b) COUNTIF(F3:F30,\合格\
4. 根据sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。 要求：
（a）计算“按年偿还贷款金额（年末）”，
并将结果填入到Sheet2中的E2单元格中； （b）计算“第9个月贷款利息金额”，
并将结果填入到Sheet2中的E3单元格中。
财务函数：PMT 贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款金额（年末） 格式：PMT(rate，nper，pv，fv，type) rate：（年利息） nper: （贷款年限） pv： （贷款金额）
fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末 (a) PMT(B4,B3,B2)
财务函数：IPMT 贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款后，利息逐月减少，求某月所交的利息。
格式：IPMT(rate，per，nper，pv，fv)
25
rate：各期利率 （月利息，年利息/12）
per：用于计算利息数额的期数，介于1～nper之间 （如9月） nper：（年数*12月）
pv： （贷款金额）
fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 (b) IPMT(B4/12,9,B3*12,B2)
5. 将sheet1中的“学生成绩表”复制到sheet3，对Sheet3进行高级筛选。
要求：
（a）筛选条件为：“性别”－男、“100米成绩（秒）”－<=12.00、“铅球成绩（米）”－>9.00； （b）将筛选结果保存在Sheet3中。 注意：
（a）无需考虑是否删除或移动筛选条件;
（b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。
性别 男 100米成绩（秒） 铅球成绩（米） <=12.00 >9.00 6. 根据Sheet1中的“学生成绩表”，在Sheet4中创建一张数据透视表。
要求：
（a）显示每种性别学生的合格与不合格总人数； （b）行区域设置为“性别”； （c）列区域设置为“结果1”； （d）数据区域设置为“结果1”； （e）计数项为“结果1”。
26
二、AOA Excel 考试题用到的函数参考
[一类] 一般的函数
1．AVERAGE平均值函数: AVERAGE（被求平均值的区域） 2．SUM(range) 求和函数
3．函数 MAX（range） 功能：求range内的最大值。 4．函数 ABS（value） 功能：取value的绝对值。 5. 取除法后得余数的函数：MOD（被除数，除数）
[二类] IF（条件）函数
5．IF函数：
IF（判别条件，条件成立(真)时取值，条件不成立(假)时取值） 例：=IF（C5（成绩）<60，“不及格”，“及格”） IF函数的嵌套。
Excel中有关逻辑函数
⑴ AND（X条件 ， Y条件， Z条件，??）都成立（TRUE），则最终成立。 ⑵ OR （X条件， Y条件， Z条件，??）有一成立，则最终成立。 ⑶ NOT（条件） 反之！
[三类] 条件统计函数
6．条件统计函数
① 条件统计个数的函数COUNTIF(被统计个数的区域之绝对引用，条件) 例：数学分数位于0到20分的人数：=COUNTIF(Sheet1!$D$2:$D$39,\② 条件求和函数：SUMIF(放各种品种的区域，指定求和的品种，被求和的区域) 例：SUMIF(A11:A43,”=衣服“,B11:B43)
[四类] 引用函数
7． 函数RANK的用法：RANK（排名次的数据依据单元，排名的范围——绝对引用，0(或缺省)/非0） 说明：0（或缺省）以所在单元的名次是降序的位数排名，否则升序。 例：如平均分80.00在G2，全部平均分为$G$2:$G$39，则： RANK(G2,$G$2:$G$39,0)
8. 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)
27
Look表 11 12 A 项目 衣服 裤子 鞋子 衣服 裤子 鞋子 衣服 裤子 ?? ?? B C ?? ?? D 单价 ?? ?? table表 1 2 项目 单价 F 衣服 120 G 裤子 80 1 2 3 4 5 table表 F 项目 衣服 裤子 鞋子 G 单价 120 80 150 价格表 第2列
9. HLOOKUP 横向对照表：
H 鞋子 150 第2行
[五类] 数据库函数
10. 库函数格式为：函数名称（database，field，criteria）
database（数据库）：包含有每列项目标题的长方形单元格区域，一般即整个工作表。 field（字段）：指定数据库函数所作用的数据列名。 如：Sheet1!D1或Sheet1!4
criteria（条件区域）：一组包含给定条件的单元格区域。
如在sheet2表中自己先构建条件区间，如；$J$10:$K$11
Database: Criteria: field1 field2 语文 数学 ?? 数据 数据 数据 数据 ?? ?? ?? ?? 10 11
J 数学 >=80 K 数学 <=100 ?? ① 数据库计数函数：DCOUNT（数据库范围，被计数列，放条件的区域） 功能：计数数据库中满足指定条件的记录字段（列）中包含数值的单元格的个数 例：DCOUNT(Sheet1!A1:I39,Sheet1!C1,$J$10:$K$11) ② 数据库计数函数：DCOUNTA（数据库范围，被计数列，放条件的区域）
10
功能：数据库中满足指定条件的记录字段（列）中非空单元格的个数
11
例：DCOUNTA(A2:K24,B2, $J$10:$K$11)
J 语文 >=85 K 数学 >=85 重要：语文和数学，怎么办？用学号，计数满足条件的学号非空数
③ 数据库中求平均值函数：DAVERAGE(数据库范围，被求平均值的列，放条件的区域) 例：DAVERAGE(A1:G17,E1,J2:L3)
④ 获取数据库的单元格的值函数：DGET（数据库范围，单元格所在列，放条件的区域）
28