实验指导8.doc

实验八 Excel考前复习【实验目的和要求】：1、掌握Excel单元格条件格式设置；2、掌握Excel单元格有效性设置；3、掌握Excel中公式、数组公式应用；4、掌握Hlookup（）、Vlookup（）、If（）、Hour（）、Minute（）、Countif（）、Max（）、Mid（）、Countblank（）等函数应用；5、掌握Excel中高级筛选应用；6、掌握Excel中数据透视图和数据透视表的应用。【实验内容】：1、停车价目表；2、图书订购信息表。【实验要求】：一：停车价目表1、 在Sheet4中的A1单元格中设置为只能5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。（4分）2、 使用HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。要求：根据Sheet1中的“停车价目表”价格，使用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。注意：函数中如果需要用到绝对地址的将使用绝对地址进行计算，其他方式无效（4分）3、 在Sheet1中，使用数组公式计算汽车在停车库中的停放时间。要求：（3分）计算方法为：“停放时间出库时间入库时间”格式为：“小时：分钟：秒”将结果保存在“停车情况记录表”中的“停放时间”列中。例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”4、 使用函数公式，对“停车情况记录表”的停车费用进行计算。（3分）要求：根据Sheet1停放时间的长短计算停车费用，将计算结果填入到“停车情况记录表”的“应付金额”列中。注意：停车按小时收费，对于不满一个小时的按照一个小时计费；对于超过整点小时数十五分钟（包含十五分钟）的多累计一个小时。（例如1小时23分，将以2小时计费）5、 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计。（4分）要求：（a）统计停车费用大于等于40元的停车记录条数，并将结果保存在J8单元格中；（b）统计最高的停车费用，并将结果保存在J9单元格中。6、 将Sheet1复制到Sheet2中，并对Sheet2进行高级筛选，要求：（6分）a.筛选条件：“车型”小汽车，“应付金额”=30；b.将结果保存在Sheet2中。 7、 根据Sheet1中的“停车情况记录表”，创建一个数据透视图Chart1，要求：（6分）a.显示各种车型所收费用的汇总；b.X坐标设置为“车型”；c.求和项为“应付金额”；d.将对应的数据透视表保存在Sheet3中。二：图书订购信息表1、 在Sheet4中，使用条件格式将“性别”列中数据为“女”的单元格中字体颜色为红色、加粗显示。（4分）2、 使用IF和MID函数，根据Sheet1中的“图书订购信息表”中的“学号”列对“所属学院”列进行填充。（4分）要求：根据每位学生学号的第七位填充相应的“所属学院”。注意：学号第七为为1计算机学院；学号第七位为0电子信息学院3、 使用COUNTBLANK函数，对Sheet1中的“图书订购信息表”中的“订书种类数”列进行填充。注意：（3分）其中“1”表示该同学订购该图书，空格表示没有订购；将结果保存在Sheet1中的“图书订购信息表”中的“订书种类数”列。4、 使用公式，对Sheet1中的“图书订购信息表”中“订数金额（元）”列进行填充（3分）计算方法：应缴总额C语言*单价高等数学*单价大学语文*单价大学英语*单价。5、 使用统计函数，根据Sheet1中的“图书订购信息表”的数据，统计订数金额大于100元的学生人数，将结果保存在Sheet1中的M9单元格中（4分）6、 将Sheet1复制到Sheet2中，并对Sheet2进行高级筛选，要求：（6分）a.筛选条件：“订数种类数”=3，“所属学院”计算机学院；b.将结果保存在Sheet2中。 注意：复制过程中，将标题项“教材订购情况表”连同数据一同复制； 复制数据后，粘贴时，数据表必须顶格放置。7、 根据Sheet1中的“图书订购信息表”，创建一个数据透视图Chart1，要求：（6分）a.显示每个学院图书订购的订数金额汇总情况；b.X坐标设置为“所属学院”；c.数据区域设置为“订数金额（元）”；d.求和项为订数金额（元）；e.将对应的数据透视表保存在Sheet3中。【操作过程】：一：停车价目表；1、选中Sheet4的A1单元格，【数据】【有效性】，设置允许文本长度为5，在“出错警告”选项卡中，设置样式为“警告”，错误信息为“只能录入5位数字或文本”。2、填充单价使用Hlookup（）函数，填充不同车型的计费单价，将光标放入C9单元格，设置函数如下：注意：查找区域必须绝对引用，否则在用填充柄填充后续单元格时会出错。3、使用数组公式计算停放时间a选中F9:F39区域b输入数组公式：c使用CtrlShiftEnter完成4、使用函数，计算停车时间提示：使用If（）、Hour（）、Minute（）函数a首先判断停车时间有否超过一个小时，如果停车的小时数为0，则在1小时内，按照一个小时收费，收的就是停车单价，如果超过1小时，还要考虑分钟，又要使用一个If（）函数b在超过一个小时后，如果分钟数超过15，则多计算一个小时，如果没超，则按照小时数计算停车时间，时间单价就是停车费用c最后的函数表达式参考如下（可以有多种方法）：5、统计a统计停车费用超过40的条数b统计最高的停车费用6、高级筛选：a在原有数据源隔开一列部分创建条件区域，参考如下：注意：上面的“车型”、“应付金额”之类的标签，如果原文中有，最好去复制，以防发生错误。b将光标放置入原有数据源区域，【数据】【筛选】【高级筛选】，设置参考如下：点击确定完成。7、创建数据透视图a将光标放入Sheet1中数据源部分，【数据】【数据透视表和数据透视图】b选择数据透视图c选择数据源区域d因为Sh