EXCEL实验手册电子版.doc

实验三：办公自动化—EXCEL 3.1 实验目的 掌握公式和函数的运用 掌握工作表数据的编辑 掌握工作表的自定义格式化 掌握图表的创建、编辑和格式化 掌握数据的排序和筛选 掌握分类汇总和数据透视表的使用 掌握工作表的页面设置 3.2实验范例 打开FL素材.xls文件，按下列要求操作，结果如样张所示。 （1）在sheet1表的开始处插入一行，输入标题“学生成绩表”，标题设置为楷体、18磅，并在A1：I1区域合并居中，用蓝色作为底色。 单击A1单元格，选用“插入”菜单的“行”命令插入一行； 在A1单元格中输入标题，选中A1:I1区域，选用“格式/单元格”命令，在“单元格格式”对话框中按要求进行设置； （2） 按系别进行降序排列，然后求每个学生三门课程的平均成绩及总评成绩（总评成绩 = 平时成绩×0.3+平均成绩×成绩系数数值）。 选中A2：I20区域，选用“数据/排序”命令按要求进行设置； 选中H3单元格，单击“常用工具栏”中的《粘贴函数》按钮，在“粘贴函数”对话框中选用“AVERAGE”函数，打开“AVERAGE”函数选项板后，用鼠标拖曳的方法在数据表中正确选择函数的作用范围；再按《确定》按钮，拖曳H3单元格的自动填充柄，将公式拖曳至H20单元格； 选取I3单元格，输入公式:=G3*0.3+H3*$B$21，拖曳I3单元格的自动填充柄，将该公式拖曳至I20单元格； 　　（3）为I11单元格设置批注，内容为“最高分”；将数据表中大于等于90的数值用条件格式设置为蓝色字体、加粗显示；将表格中的各列设置为最合适的列宽；将D3：F20区域命名为“考试成绩”。 选中I11单元格，选用“插入/批注”命令，输入批注内容； 选中D3：I20区域，选用“格式/条件格式”命令，按要求进行设置； 选中各列，用“格式/列/最适合的列宽”命令； 选中D3：F20区域，在名称编辑栏左端的《名称框》中输入“考试成绩”，然后回车； （4）在J2单元格中输入“是否合格”，并统计各个学生考试是否合格，条件为：学生只要有一门课程不及格就不合格，否则就合格。 选中J2单元格，，输入相应内容； 选中J3单元格，输入IF函数“=IF(MIN(D3:F3)60,不合格,合格)”，拖曳J3单元格的自动填充柄，将公式拖曳至J20单元格； （5）以每个系别中第一个学生的姓名和总评成绩按样张作图表。 依次从上到下、从左到右选中C2、I2、C3、I3、C6、I6、C10、I10、C14、 I14单元格，单击工具栏上的《图表向导》按钮，选择“标准型”中的“三维堆积柱形图”； 鼠标右击图表，选择快显菜单中的“图表选项”命令，添加数据标志，其余以默认方式设置； 将图表移动并调节大小到A22:G36区域； 3.3 实验内容 实验一： 打开SY1素材.xls文件，按下列要求操作，结果如样张所示。 （1）添加标题，并将标题设置为隶书、20磅、蓝色、合并居中，销售总额和销售利润的字段格式按样张设置。 （2）计算销售总额和销售利润，销售利润=销售总额×利润率。 （3）计算“华东地区”区域的销售总额，存放在如样张所示的单元格中；将表格中所有金额数据保留小数2位，并按样张设置会计货币形式。 （4）在I2单元格中输入“销售状况”，并统计各个部门的销售状况，条件为：只要有一个商店的销售额超过40000元并且该部门的总销售额超过50000元，则该部门的销售状况为“好”，否则为“一般”。 （5）按部门进行排序，然后用数据透视表计算各个商店的销售总计。 实验二： 打开SY2素材.xls文件，按下列要求操作，结果如样张所示。 （1）在sheet1中的A1单元格中输入标题“职工工资表”，标题设置为楷体、18磅，并在A1：H1区域合并居中，并用蓝色作为底色，隐藏“业绩津贴”列（G列）；将“工资”区域内的数据设置为蓝色。 （2）计算所有职工的实发工资，计算公式如下：基本工资＋岗位津贴一个人调节税，基本工资大于等于1400（元）者，其个人调节税为基本工资的6%；基本工资1400（元）以下者，其个人调节税为基本工资的3%。 （3）计算基本工资、岗位津贴和实发工资的平均值；设置列表中所有的金额数值格式为“货币样式”；将“性别”列设置为最合适的列宽。 （4）在sheet1工作表中筛选出所有主管地区为上海的男职工记录，“平均值”行不参加筛选，将筛选结果复制到sheet3工作表中。 （5）在sheet1的A45开始的区域中创建如样张所示的数据透视表（sheet1中的数据恢复）。 平均值项:基本工资 　 　 主管地区 采购部 规划部 销售部 江苏 ＃＃＃＃.## ＃＃＃＃.## ＃＃＃＃.## 上海 ＃＃＃＃.## ＃＃＃＃.## ＃＃＃＃.## 浙江 ＃＃＃＃.## ＃＃＃＃.## ＃＃＃＃.## （6）在shee