实验进阶实验.docxVIP

编号：07实验7_电子表格软件excel进阶应用实验目的本实验为办公软件中电子表格软件的应用实验，其主要目的在于：学会在Excel中使用表格、图片、图表等操作；学会在Excel中使用公式、进行数据管理等操作；实验内容与步骤以下为本次Excel 上机实验的内容：（实验形成的Excel文件请以“***_实验7附件.xlsm”为文件名保存）注意：本次实验将在实验6的基础上进行。请先将实验6所生成的XLSx文件打开，然后进行下面的实验步骤。首先完成实验6的7、8步骤，然后在实验6的第一张工作表前插入一张新工作表，将该表单重新命名为“总店统计表”，对三个分店中的数据进行汇总，新工作表的结构如下表所示。总店销售状态统计表商品编号商品名称一季度销售额（万元）…四季度销售额（万元）年销售额（万元）总店年度商品销售额排名：：：：…：：：：…总店季销售总额总店季平均商品销售额总店季最高销量商品销售额总店季最低销量商品销售额全年总销售额（万元）总店季度及年度奖金总额总店年销售额低于100万的商品个数总店年销售额低于100万的部门的商品年平均销售额使用不同工作表地址引用的方法，用SUM( )函数，将表中绿色部分单元格分别填加上各分店季度销售总额（用三维求和法，不能手工输入数值）。其他彩色颜色部分用适当的公式计算。创建一个形如下表所示的统计表, 将该表单重新命名为“分店统计表”。各分店销售状态统计表用自动序列填充的方法在分店编号列中输入分店编号（86001，86002，86003）；使用不同工作表地址引用的方法，用SUM( )函数，将表中绿色部分单元格分别填加上各分店季度销售总额，用求和、求平均数等函数添加表中黄色部分；并以总销售额列进行年度排名输入（亮绿色单元格部分），然后再进行升序排序；用公式计算的方法添加表中紫色部分，并设置成百分比显示格式；在绿色标记单元格中使用COUNT( )函数，统计分店个数；注意上述公式输入操作也是先在一个单元格中输入公式，然后用复制公式的方法填充各个单元格，要特别注意地址引用格式（绝对引用，相对引用）。在兰色标记的单元格中使用VLOOKUP( )函数，建立对各分店总销售额的查询。用该表建立一个单独式图表工作表，反映出各分店销售情况；新建一个“销售提成”工作表，并做如下处理：将“素材-邮件合并数据源.doc”导入。删除其中的“城市”和“国家”数据列。在“出生日期”数据列前面增加6列，分别是：“一季度”、“二季度”、“三季度” “四季度”、“合计”五个销售提成数据列。将“出生日期”、“雇用日期”、“地址”、“邮政编码”、“家庭电话”和“分机”这些数据列隐藏起来。在“合计”列后面加上“地区”列，将九人所在地区按照“北京”、“上海”和“广州”填成三组。利用公式和引用填充销售人员的按季度销售提成，原则是职务为“销售代表”，提成金额为分公司季度奖金销售提成总额的70%且所有人员均分，其他管理人员为销售总额的30%且所有人员均分，精确到小数后4位。（提示：此处将用到if与countif的结合）将“合计”列的数据按“地区”排序。注：本次实验只要求做到这里。5~12步骤作为下次课的实验内容。从网上获取即时美元与人民币兑换价，在“销售提成”表内新建一列，放美元合计统计数据列“合计（美元）”，将销售人员的销售提成合计转换成美元。根据“尊称”数据列的数据在工作表中添加一个“性别”数据列，按“合计”列将“销售提成”工作表排序（性别为主关键字、合计为次关键字）。利用自动筛选功能，选出所有性别为“女”的记录，复制成新的数据表“女职工收入”。按性别分别求出男生、女的各平均销售提成（保留1位小数），结果放在另一个工作表中。（提示：必须先对要分类的字段进行排序，然后才能进行分类汇总。本例中分类的字段为性别，汇总的字段为销售提成数，汇总的方式为求平均值。）在原有分类汇总的基础上，再汇总出男、女职工的人数（汇总结果放在性别平均的上面）。即： 男职工人数：男职工平均销售提成；女职工人数：女职工平均销售提成。建立按性别及季度销售提成（一季度、二季度、三季度、四季度）分类的平均销售提成数据透视表。样张如，根据透视表建立数据透视图。为“姓氏”字段内建立到雇员照片的超级链接，照片文件可以使用tu1.jpg，......，tu7.jpg。在所有表的前面插入一新工作表做主页，同时插入一幅中国地图，在北京、上海、广州的位置上设置控制按钮，录制一些相应的宏，并编写按钮操作代码使用户可以通过单击相应的城市按钮定位到该工作表进行查询，且各工作表要有返回主页的按钮。（提示：需要使用控件工具箱、录制宏功能和VBA编码窗口，注意切换编辑和运行状态，打开Excel的运行宏功能）； 参考效果图如下：三个运行宏按钮注意:制作完成的文档命名为：“***_实验7附件.xlsm”（参考前面实验的