浙江大学城市学院实验报告7
实验项目名称 实验7
中文Excel基本操作
实验成绩 指导老师(签名) 日期
一． 实验目的和要求
1. 单元格内容的输入和修改； 2. 移动和复制单元格或区域数据； 3. 掌握工作表单元格格式的设置； 4. 掌握工作表行高与列宽的设置； 5. 掌握行列的隐藏和取消隐藏； 6. 自定义下拉列表、序列与填充柄 7. 掌握条件格式的使用； 8. 数据的舍入方法 9. 单元格的引用
10. SUM函数、AVERAGE函数、IF函数的应用 11. 时间函数，RANK函数的应用
二．实验内容、原理及实验结果与分析
【练习7－1】
(一) 三科成绩(数组公式，if函数).xls
1. 使用公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
【练习7－2】
(一) 三科成绩(数组公式，if函数).xls
2. 使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。
排名：=RANK(G2,G$2:G$39) 或者 =RANK(F2,F$2:F$39) 然后利用填充柄复制公式
【练习7－3】
(一) 三科成绩(数组公式，if函数).xls
3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。
=IF(AND(C2>AVERAGE($G$2:$G$39),D2>AVERAGE($G$2:$G$39),E2>AVERAGE($G$2:$G$39)),TRUE,FALSE) 或者：
16
=IF(C2>AVERAGE($C$2:$C$39),IF(D2>AVERAGE($D$2:$D$39),IF(E2>AVERAGE($E$2:$E$39),TRUE,FALSE),FALSE),FALSE) 【练习7－4】
(二) 服装采购折扣表(采购表)(VLOOKUP函数).xls
2. 使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。
要求：根据“服装采购折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣“列中。
=IF(B11>=A$6,B$6,IF(B11>=A$5,B$5,IF(B11>=A$4,B$4,B$3)))
【练习7－5】
(三) 书籍出版（闰年，数组公式if).xls
4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
说明：闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。 =IF(MOD(A2,400)=0,\闰年\平年\闰年\平年\或者：
=IF(OR(AND(A2/4=TRUNC(A2/4),A2/100<>TRUNC(A2/100)),A2/400=TRUNC(A2/400)),\闰年\平年\【练习7－6】
(四) 电话升级(时间函数).xls
1. 使用时间函数，对Sheet1中用户的年龄进行计算。
要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。 =YEAR(NOW())-YEAR(C2) 或者：
=YEAR(TODAY())-YEAR(C2) 【练习7－7】
(四) 电话升级(时间函数).xls
3. 使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在Sheet1中的“是否>=40男性”。
=IF(D2>=40,IF(B2=\男\【练习7－8】
(六) 房产销售表(数组公式).xls
4. 使用RANK函数，根据Sheet2的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。 人员甲：
=RANK(B2,B$2:B$6)
然后利用填充柄复制公式 【练习7－9】
(七) 公务员考试成绩表(if函数).xls
1. 使用IF函数，对Sheet1中的“学位”列进行自动填充。
要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生－博士 - 硕士研究生－硕士
17
- 本科－学士 - 其他－无
=IF(G3=\博士研究生\博士\硕士研究生\硕士\本科\学士\无\
【练习7－10】
(八) 员工姓名(REPLACE函数).xls
2. 使用时间函数，对Sheet1员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”列和“工龄”列中。 年龄：=YEAR(TODAY())-YEAR(E2) 工龄：=YEAR(TODAY())-YEAR(G2) 【练习7－11】
(八) 员工姓名(REPLACE函数).xls
4. 使用逻辑函数，判断员工是否有资格评“高级工程师”。 评选条件为：工龄大于20，且为工程师的员工。 =IF(I2=\工程师\=IF(AND(H2>20,I2=\工程师\是\否\ 【练习7－12】
(九) 停车收费(HLOOKUP函数).xls
2. 在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求： a. 公式计算方法为“出库时间-入库时间” b. 格式为：“小时：分钟：秒”
（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”） =E9-D9
【练习7－13】
(九) 停车收费(HLOOKUP函数).xls
3. 使用函数公式，计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。 注意：
a. 停车按小时收费，对于不满一个小时的按照一个小时计费； b. 对于超过整点小时数十五分钟的多累积一个小时。 （例如1小时23分，将以2小时计费）
=IF(AND(HOUR(F9)=0,MINUTE(F9)>0),1*C9,IF(MINUTE(F9)>15,(HOUR(F9)+1)*C9,HOUR(F9)*C9))
注：把实验结果上传服务器
三、讨论、心得
记录实验感受、上机过程中遇到的困难及解决办法、遗留的问题、意见和建议等。
18
浙江大学城市学院实验报告8
实验项目名称 实验8 编辑工作表、数组和函数的使用 实验成绩 指导老师(签名) 日期
一． 实验目的和要求
1. 掌握工作表的复制、删除、移动及工作表的重命名； 2. 掌握工作表和工作簿的隐藏与取消隐藏； 3. 掌握工作表的保护设置；
4. 数组常数，编辑数组和数组公式的应用； 5. 财务函数、文本函数、日期与时间函数的使用； 6. 查找与引用函数、其他类型函数的使用
二．实验内容、原理及实验结果与分析
【练习8－1】
(一) 三科成绩(数组公式，if函数).xls
1. 使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
总分：{=C2:C39+D2:D39+E2:E39} 平均分：{=F2:F39/3}
（注意，题目明确要求用数组公式，就必须用数组公式，否则不得分。数组公式中的一对{}不是人为录入的，必须用Ctrl+Shift+Enter组合健输入。）
【练习8－2】
(一) 三科成绩(数组公式，if函数).xls
4. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。
数学分数位于0到
=COUNTIF(Sheet1!$D$2:$D$39,\
20分的人数： 数学分数位于20到
=COUNTIF(Sheet1!$D$2:$D$39,\
40分的人数： 数学分数位于40到
=COUNTIF(Sheet1!$D$2:$D$39,\
60分的人数： 数学分数位于60到
=COUNTIF(Sheet1!$D$2:$D$39,\
80分的人数： 数学分数位于80到
=COUNTIF(Sheet1!$D$2:$D$39,\
100分的人数：
【练习8－3】
19
(二) 服装采购折扣表(采购表)(VLOOKUP函数).xls
1. 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。
要求：根据“价格表”中的商品单价，利用VLOOKUP函数，将其单价自动填充到采购表中的“单价”列中。
=VLOOKUP(A11,F$2:G$5,2,0) 或者用数组公式做：
=VLOOKUP($A$11:$A$43,$F$2:$G$5,2,0) 【练习8－4】
(二) 服装采购折扣表(采购表)(VLOOKUP函数).xls
4. 使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表” 当中。
采购总量：=SUMIF(A$11:A$43,I12,B$11:B$43) 采购总金额：=SUMIF(A$11:A$43,I12,F$11:F$43) 【练习8－5】
(三) 书籍出版（闰年，数组公式if).xls
1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的“金额”列当中。 {=G2:G51*H2:H51} 【练习8－6】
(三) 书籍出版（闰年，数组公式if).xls
2. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：
a. 统计出版社名称为“高等教育出版社”的书的种类数； =COUNTIF(D2:D51,\高等教育出版社\【练习8－7】
(三) 书籍出版（闰年，数组公式if).xls
3. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中的相应位置。
=SUMIF(A$2:A$51,K8,I$2:I$51) 【练习8－8】
(四) 电话升级(时间函数).xls
2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。 升级方法是在区号（0571）后面加上“8”，
并将其计算结果保存在“升级电话号码”列的相应单元格中。 =REPLACE(F2,1,4,\或者：
=REPLACE(F2,5,8,\
（注意；先设单元格格式设为常规，再用公式。） 【练习8－9】
(四) 电话升级(时间函数).xls
4. 对Sheet1中的数据，根据以下条件,利用函数进行统计：
a. 统计性别为“男”的用户人数，将结果填入Sheet2的B1单元格中； =COUNTIF(Sheet1!B2:B37,\男\
20