Excel与数据处理（第6版）实验任务汇 实验1-8 Excel基础知识 -外部数据交换与数据分析工具应用

实验一Excel基础知识实验目的了解Excel的工作窗口，掌握常用工具栏的使用掌握对单元格的引用方法，区分相对引用与绝对引用了解Excel的数据类型，掌握不同类型数据的输入方法掌握通过自定义格式简少数据输入的方法掌握Excel工作表的日常操作实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题上传至服务器。实验内容理解绝对引用和相对引用的区别，注意它们各自的用途。新建文件oa1-1.xlsx，按要求完成表中的计算。要求：（1）写出B4:B7区域中各单元格内的值，计算方法为：总价=单价×数量。单价在B2单元格中，数量在A4:A7区域中。（2）写出G4:G7区域中各单元格内的值，计算方法为：总价=单价×数量。单价在E4:E7单元格区域中，数量在F4:F7区域中。（3）在K4单元格中输入了一个公式“=SUM(I$3:$J3)”，然后把该公式向下填充复制到K7单元格。试写出K4:K7单元格区域内的结果值。本题有助于理解几种引用形式的用法及区别，请上机验证计算的结果，特别是K4:K7单元格区域内的值。在Excel中建立如上图所示的表格来验证计算结果。【小技巧】：在Excel输入公式时，只要正确使用F4键，就能简单地对单元格的相对引用和绝对引用进行切换。现举例说明：对于某单元格所输入的公式为“=SUM(B4:B8)”，选中整个公式，按下F4键，该公式内容变为“=SUM($B$4:$B$8)”，表示对横、纵行单元格均进行绝对引用。第二次按下F4键，公式内容又变为“=SUM(B$4:B$8)”，表示对横行进行绝对引用，纵行相对引用。第三次按下F4键，公式则变为“=SUM($B4:$B8)”，表示对横行进行相对引用，对纵行进行绝对引用。第四次按下F4键时，公式变回到初始状态=SUM(B4:B8)”，即对横行纵行的单元格均进行相对引用。需要说明的一点是，F4键的切换功能只对所选中的公式段有作用。打开文件oa1-2.xlsx,按要求完成如下题目：①将其中工作表的名称依次改为“一班成绩表”、“二班成绩表”，……“五班成绩表”。②修改新建工作簿的默认工作表个数为1③新建一个工作簿，以自己的学号命名，将oa1-2.xlsx中的四班、五班成绩表移动到新建工作簿中，并插入新的工作表，工作表名称改为“成绩分析表”。④在oa1-2.xlsx中练习如下内容：选择单元格：单个、连续与不连续的多个单元格的选择清除单元格内容删除单元格（注意单元格清除与删除的区别）插入单元格移动单元格复制单元格合并单元格打开文件oa1-3.xlsx按如下要求完成相应的操作：①拆分窗口，在拆分后的多个窗口中查看数据。Excel的窗口是可以进行拆分的，也就是说用户可以把一个工作表分成两部分，这样在进行如数据比较、参考等操作时是非常方便的。②冻结订单表中的标题行③设置打印的每一页都有标题行④隐藏“雇员”、“订购日期”、“到货日期”、“发货日期”四列，再取消隐藏操作⑤全屏幕查看工作表数据，再取消全屏幕模式⑥在多个窗口中查看数据，分别按水平并排、垂直并排两种方式查看数据，并将多窗口数据设置为同步滚动。4.建立如下所示的职工档案表，并按要求设置各单元格或数据列的格式，保存为文件oa1-4.xlsx。EMBEDPBrush格式设置要求：（1）大标题18号字，加粗，“制表时间”与大标题在同一行上；第2、3行为标题行，按图所示进行相关单元格的合并，字号为11号，加粗；（2）设置“职工编号”的自定义格式，在实际输入时，只需要输入数字，由Excel自动进行编号的转换，如输入3，Excel将把它显示为“ZG003”；（3）职称采取下拉菜单的方式输入，分为四种：技术员，助理工程师，工程师，高级工程师。（4）设置“参加工作时间”的自定义格式，如输入“1998/2/1”，Excel将它显示为“一九九八年二月一日”。（5）基本工资与职称挂钩，技术员800，助理工程师1200，工程师1600，高级工程师2000。（6）计算“实发工资”，并把该列数据设置为人民币数据显示形式。5.某学校为了提高教学质量，对教学进行质量考评，根据考评的结果分发教师的劳酬和奖金。教学质量考评的工作表如下所示。在Excel中建立上图所示的“教学质量考评表”，保存为文件oa1-5.xlsx，要求如下：（1）对标题所在的单元格区域进行合并，设置它的字体、字号；（2）“职工编号”用填充序列的方式产生；（3）限制学生分数的范围在0-100之间，输入超出范围时则禁止输入并提示；（4）等级通过总评分计算，80以上为A，60-80为B，60以下为C。实验二、数据的输入和格式化实验目的了解Excel的数据类型掌握不同类型数据的输入方法掌握大批量相同数据的输入方法掌握数据自定义格式化方法，工作表格式化方法掌握用Rand和int函数产生大量模拟数据的方法。实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题上传至服务器。实验内容1.打开文件oa2-1.xlsx，根据服装原价及折扣计算今日价格。2.打开文件oa2-2.xlsx，按如下要求完成：（1）在工作表“成绩表”中完成以下操作：①学生成绩用rand函数或randbetween函数产生随机数，随机数的范围在20-100之间②学号为连续编号，用“自动填充序列”的方式产生。（2）在工作表“用户档案”中完成如下的操作：①“月租费”一栏中的金额根据电话类型确定，”公话”月租35,”办公”月租30,"私人"月租24。②“保险号”一栏中的数据格式为p00-000-0000，要求当输入数字1时，单元格中显示为p00-000-0001,当输入数字17时单元格中显示为p00-000-0017；3.某中药材公司2009年的药材销售利润表如下图所示，按如下要求设置格式，并将结果保存于文件oa2-3.xlsx：突出显示利润小于20的药材；突出显示利润占前40%的药材；用数据条表示出销售利润的多少；实验三公式及函数的使用（一）实验目的理解引用的形式和用法掌握数组公式的用法掌握名字及其应用了解逻辑函数及其调用方法掌握查找函数Vlookup的用法掌握条件函数的用法实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题上传至服务器。实验内容打开文件oa3-1.xlsx,按工作表中的要求完成。练习数组公式的使用，函数sum()的用法,打开文件oa3-2.xlsx按各工作表中的要求完成。教师想找出下表中三门成绩都在平均分以上的学生，在满足条件的学生中的“是否都过平均分”一列中标注“*”，文件保存为oa3-3.xlsx。实验四公式及函数的使用（二）实验目的掌握常用工作表函数的用法掌握常见统计函数的用法掌握条件函数的用法，特别是：If\sumif\countif\等实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题其上传至服务器。实验内容练习函数sumif()、sumifs()、count()、countif()的用法,打开文件oa4-1.xlsx按各工作表中的要求完成。某学校成绩表如下图所示，在Excel中建立该表，保存为oa4-2.xlsx，根据成绩用公式生成等级、名次以及分数段。要求如下：等级、名次和分数段需用公式生成；统计各等级的人数；保持学号顺序不变的前提下进行学生成绩名次排定；不及格的学生成绩及等级用红色字体显示；打开oa4-3.xlsx文件，练习INDEX函数和match函数的使用，用INDEX函数和match函数结合完成产品价格的查询。实验五公式及函数的使用（三）实验目的掌握常用工作表函数的用法掌握常见统计函数的用法掌握条件函数的用法，特别是：If\sumif\countif\等实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题其上传至服务器。实验内容打开文件oa5-1.xlsx，按要求完成：①统计每月出生的员工人数。②计算下表中各员工的工龄，以年为单位，取整数，半年以上算一年，不足半年忽略不计。请在蓝色区域内写一个函数，统计每人10月份的出勤情况。（文件：oa5-2.xlsx）打开文件oa5-3.xlsx，按要求计算起付线标准：①所有职工，住院起付线1300②在职职工，急诊或特殊门诊起付线2000；退休职工，急诊或特殊门诊起付线2200。实验六数据管理与数据透视表实验目的掌握数据排序的方法掌握数据筛选的方法掌握数据多重分类汇总、嵌套分类汇总的方法掌握数据透视表的使用方法掌握合并计算的方法实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题上传至服务器。实验内容打开文件oa6-1.xlsx，要求完成①将“歌单”表中按照歌名的字符个数从少到多排序；②在“订单”表中每行下加一行空白行。某超市销售的产品多达几百种，该超市在Excel数据表中管理各种销售商品的订购数据存于文件oa6-2.xlsx中，按如下条件筛选数据：①查询库存量已小于3，需要及时订购的产品；②查询单价最高的前10种产品；③查询已经中止订购关系的供应商；④查询所销售饮料有哪些品种；⑤筛选出库存量不超过20，再订购量高于20的饮料，以及库存量不少于于60，再订购量不高于30的调味品。打开文件oa6-3.xlsx，某商场有12月1号到8号的销售记录如图的A7:E15单元格区域所示，现在要求使用计算条件找出销售额高于平均值的销售记录，将筛选出的记录置于原始记录表的下方。打开文件oa6-4.xlsx，按表中要求完成分析。下图是某公司的职工工资表。打开文件oa6-5.xlsx,完成以下统计：①每部门有多少人；②每部门平均工资；③应分发给各部门的总金额各是多少。实验七图表处理实验目的了解Excel提供的图表类型，认识图表的组成元素掌握图表的制作及修改方法掌握数据系列的处理方法掌握复杂图表的建立方法掌握数据排序及简单筛选的方法实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。将本次实验各题上传至服务器。实验内容某电视机厂1999年到2001年的各类电视机销售数量如下所示，按要求做出各种图表。（1）1999~2001年的销售柱形图；（2）1999年各类电视机的销售折线图；（3）2000年各类电视机的销售饼图；（4）2001年各类电视机的销售折线图，并添加垂直线；（5）1999至2001年的29英寸和34英寸电视的销售折线图，并添加34英寸电视的销售趋势线。根据下表制作如下所示的图。IncomeProfitMarginJan154,0989.80%Feb187,98311.20%Mar145,08511.1%Apr155,9099.8%May189,34512.3%Jun159,34311.3%某证券交易数据如下所示，按要求做出图表。（1）做出高—低—收盘—成交量图形，如下所示。（2）做出高—低—收盘价图形，如下所示。（3）做出收盘价的折线图，并添加趋势线，如下所示。实验八、外部数据交换与数据分析工具应用实验目的了解Excel与外部数据交换的方法和条件了解MsQuery的功能及用法掌握单变量求解的方法与步骤掌握规划求解工具的使用方法与步骤实验要求从网上下载本次实验所需的文件，按实验任务要求完成各题。实验报告要求写出每题的主要操作步骤，下次实验时上交。实验内容查询“教务在线”网站上的“课表查询”，选取“学生课表”或“教室课表”，将课表的具体信息查询到Excel中，保存为oa8-1.xlsx。提示：导入Web数据的方法为：选择Excel的“数据”—“获取外部数据”—|“自网站”|，弹出“新建Web查询”对话框。在“地址”文本框中复制网页的地址，单击图中的“转到”按钮，单击网页数据区域中要导入数据前面的按钮，完成导入。现有一数据库的文件名为“学生教学管理.mdb”，将此数据表中的数据导入Excel中，保存为oa8-2.xlsx。将文本文件“产品.txt”中的数据导入Excel中，保存为oa8-3.xlsx。某开发商想贷款100万元建立一个山林果园，贷款利息8%，期限为25年，它每月的支付额是多少？假设有多种不同的利息，不同贷款年限可供选择，试计算出各种情况的每月支付额。利息有：5%，7%，9%，11%，这三种利息对应的年限分别为：10年，15年，20年，30年。用双模拟