e. 计数项为学历 数据透视表如下： 计数项:学历 学历 报考单位 本科 区法院 三中院 市高院 一中院 总计 3 3 1 7 博士研究生 1 1 2 大专 硕士研究生 总计 1 4 3 1 2 5 3 4 2 5 16 【练习10－8】
(八) 员工姓名(REPLACE函数).xls
6. 根据Sheet1中的数据，创建一张数据透视图Chart1，要求：
a. 显示工厂中各个职称的人数； b. x坐标设置为“职称”； c. 计数项为职称；
d. 将对应的数据透视表保存在Sheet3中。
数据透视图chart1如下：
数据透视表如下：
计数项:职称 职称 汇总 高级工 2 高级工程师 14 工程师 11 技工 5 技师 1 技术员 11 中级工 1 助工 19
31
总计 64 【练习10－9】
(九) 停车收费(HLOOKUP函数).xls
6. 根据Sheet1，创建一个数据透视图Chart1，要求： a. 显示各种车型所收费用的汇总； b. 行区域设置为“车型”； c. 计数项为“应付金额”；
d. 将对应的数据透视表保存在Sheet3中。 数据透视图chart1如下：
数据透视表如下：
求和项:应付金额 车型 汇总 大客车 200 小汽车 155 中客车 264 总计 619
注：把实验结果上传服务器
三、讨论、心得
记录实验感受、上机过程中遇到的困难及解决办法、遗留的问题、意见和建议等。
32
浙江大学城市学院实验报告11
实验项目名称 实验11 Excel综合练习
实验成绩 指导老师(签名) 日期
一． 实验目的和要求
1. 二级考试题综合练习
二．实验内容、原理及实验结果与分析
【练习11－1】
(十)气温比较(if函数).xls
1. 使用IF函数，对Sheet1中的“温度较高的城市”列进行自动填充。 =IF(B2>C2,\杭州\上海\
2. 使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充。 {=B2:B16-C2:C16}
3. 利用函数，根据Sheet1中的结果，符合以下条件的进行统计。
a. 杭州这半个月以来的最高气温和最低气温；=MAX(B2:B16) =MIN(B3:B17) b. 上海这半个月以来的最高气温和最低气温。=MAX(C2:C16) = MIN(C3:C17)
4. 将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）。 {=ABS(B2:B16-C2:C16)}
5. 将Sheet2复制到Sheet3中，并对Sheet3进行高级筛选，要求： 筛选条件：“杭州平均气温”>=20，“上海平均气温”<20
杭州平均气温 >=20 上海平均气温 <20 6. 根据Sheet1中的结果，在sheet4中创建一张数据透视表，要求：
a. 显示杭州气温高于上海气温的天数和上海气温高于杭州气温的天数； b. 行区域设置为“温度较高的城市”； c. 计数项设置为温度较高的城市。 数据透视表如下； 计数项:温度较高的城市 温度较高的城市 汇总 杭州 11 上海 4 总计 15 【练习11－2】
(十一) 学生成绩表.xls
1. 使用REPLACE函数，将Sheet1中“学生成绩表”的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号更改的方法为：在原学号的前面加上“2009”。
33
例如：\=REPLACE(A3,1,0,\
2. 使用数组公式，对Sheet1中“学生成绩表”的“总分”列进行计算。 计算方法：总分 =语文 + 数学 + 英语 + 信息技术 + 体育 {=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24}
3. 使用IF函数，根据以下条件，对Sheet1中“学生成绩表”的“考评”列进行计算。 条件：如果总分>=350，填充为“合格”；否则，填充为“不合格”。 =IF(J3>=350,\合格\不合格\
4. 在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。 条件：
（a）计算：“语文”和“数学”成绩都大于或等于85的学生人数； =DCOUNTA(A2:K24,B2,M2:N3)
（b）计算：“体育”成绩大于或等于90的“女生”姓名； =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中。 性别 男 英语 >90 信息技术 >95
注意：
（a）无需考虑是否删除或移动筛选条件;
（b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。
6. 根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。 要求：
（a）显示不同性别、不同考评结果的学生人数情况； （b）行区域设置为“性别”； （c）列区域设置为“考评”； （d）数据区域设置为“考评”； （e）计数项为“考评”。 数据透视表； 计数项:考评 考评 性别 不合格 合格 总计 男 3 12 15
34
女 总计
【练习11－3】
1 4 6 18 7 22 (十二) 卡特扫描销售统计表.xls
1. 使用VLOOKUP函数， 对Sheet1中的“3月份销售统计表”的“产品名称”列和“产品单价”列进行填充。
要求：根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。 “产品名称”列；=VLOOKUP(F3,$A$2:$C$10,2,FALSE) “产品单价”列：=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中。
销售数量 所属部门 销售金额 >3 市场1部 >1000 注意：
（a）无需考虑是否删除或移动筛选条件;
（b）复制过程中，将标题项“三月份销售统计表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。
6. 根据Sheet1的“3月份销售统计表”中的数据，新建一个数据透视图Chart1。 要求：
（a）该图形显示每位经办人的总销售额情况 （b）x坐标设置为“经办人”;
（c）数据区域设置为“销售金额”; （d）求和项为销售金额;
（e）将对应的数据透视表保存在Sheet3中。 数据透视图Chart1：
35