电子表格软件Excel.doc

3 电子表格软件Excel实 习 1 电子表格的基本处理一、实习目的1掌握建立电子表格的基本方法。2掌握编辑电子表格的基本技巧。3学会应用函数和公式求出单元格的数值。二、实习内容1启动Excel2000，熟悉其主窗口的工作界面和各个部分的基本功能。2建立如图4-11所示的一个工资表。图4-11 工资表输入模拟数据，先不要输入应发工资、扣所得税和实发工资的数据。操作提示：在输入时要主动利用自动填充、序列填充等技巧；熟悉重新输入数据、在单元格中修改数据、在公式编辑栏中修改数据等修改数据的方法。3利用函数和公式得出应发工资、扣所得税和实发工资的数据。操作提示：由于应发工资=基本工资职务工资补贴，所以计算“张然”的“应发工资”单元格数据的公式是：=E3+F3+G3假设个人所得税的计算方法是：当应发工资小于等于800元时，不交所得税。当应发工资大于800元、小于等于1500元时，交纳超出800元部分的5所得税。当应发工资大于1500元时，8001500元交纳5所得税，超出1500元以上的部分按10交纳所得税。由于有判断关系，所以可以用IF函数来进行计算。IF函数的格式是： IF(逻辑表达式,值表达式1,值表达式2)其中第一个参数运算后得到逻辑值“真”或“假”，如果是真，就计算“值表达式1”作为函数的值，如果是假，就计算“值表达式2”作为函数的值。因此，计算第三行数据的“扣所得税”单元格数据是：=IF(H3800,IF(H31500,(1500-800)*0.05+(H3-1500)*0.1,(H3-800)*0.05),0)另外，实发工资应发工资扣所得税，所以计算公式为：=H3-I3然后使用复制公式的方法计算其他人员的“应发工资”、“扣所得税”和“实发工资”。4. 要进行合并单元格、单元格文字的居中、数字的格式化等处理，如图4-12所示。图4-12 利用单元格格式对话框进行格式处理操作提示：执行“格式”“单元格”命令，将显示“单元格格式”对话框。5将Sheet1的工资表复制到Sheet2后，在Sheet2上进行编辑操作，使工资表如图4-13所示。注意第一行已经删除，其他数据行的顺序已经打乱。图4-13 复制到Sheet2中经过处理的工资表6在Sheet1工资表的第A21单元格输入“合计”，对E21单元格求和，然后对其他数字列也求和。操作提示：选择E21单元格，单击工具栏上的“自动求和”按钮，此时Excel将自动给出求和的区域（即被虚线框围住的单元格），按下Enter键，在E21单元格就显示了求和的结果。其他数字列求和可以用上述方法，也可以采取单元格复制的方法。7将工作簿以“excel实习1”的文件名保存在自己学号的文件夹中。实 习 2 电子表格的数据管理一、实习目的1熟悉对电子表格的排序。2熟悉对电子表格的筛选。3熟悉对电子表格的汇总。4熟悉电子表格的图表技术。二、实习内容1启动PowerPoint2000，打开“excel实习1”电子表格，选择Sheet2进行操作。2将Sheet2工作表复制4份，依次为Sheet2（2）、Sheet2（3）、Sheet2（4）、Sheet2（5）。3将Sheet2（2）进行排序。(1) 简单排序：按照降序查看整个工资表员工的实发工资。操作提示：为了使数据清晰，首先选中E、F、G三列，执行“格式” “列” “隐藏”命令，将该三列隐藏起来。然后选择表中任意一单元格后，执行“数据”“排序”命令，弹出“排序”对话框。排序设置和排序后的结果如图4-14所示。图4-14 按照“实发工资”降序排列的结果 (2) 多重排序：先按照“部门”升序排序，再按照“实发工资”降序排列。排序设置和排序后的结果如图4-15所示。图4-15 按照“部门”和“实发工资”多重排序的结果4对Sheet2（3）做筛选处理。(1) 自动筛选工资表。操作提示：先将“工资号”列隐藏起来，选择表中任意一单元格后，执行“数据”“筛选” “自动筛选”命令，再单击“部门”列的下拉箭头，选择“财务处”，则得到的结果如图4-16所示。图4-16 自动筛选后选择的结果 (2) 自动筛选实发工资最多的5个记录。操作提示：首先执行“数据” “筛选” “全部显示”命令，恢复所有记录。再单击“实发工资”列的下拉箭头，选择“前10个”，打开“自动筛选前10个”对话框进行设置后，得到的结果如图4-17所示。图4-17 自动筛选实发工资最多的5个记录(3) 自定义自动筛选。这次是要查看实发工资介于某两个值之间的记录。操作提示：首先恢复所有记录。再单击“实发工资”列的下拉箭头，选择“自定义”，打开“自定义自动筛选”对话框进行设置后，得到的结果如图4-18所示。图4-18 自定义筛选后的结果(4) 高级筛选如果进行的筛选比较复杂，就必须使用高级筛选。现在要查看实发工资在1700元以上的科长和实发工资在2300元以上的处长的记录。操作提示：首先恢复所有记录。在电子表格空白区域输入筛选条件后，执行“数据” “筛选” “高级筛选”命令，打开“高级筛选”对话框进行设置，如图4-19所示。输入的筛选条件和得到的结果如图4-20所示。在“高级筛选”对话框中“数据区域”和“条件区域”已经自动设置了一个范围，一般要核对后修改。对于“条件区域”，可以单击某个区域右侧的按钮，打开一个编辑框，在工作区中选择好条件区域后，在编辑框中就会显示对条件区域单元格的引用。通常希望高级筛选的结果显示到工作表的其他空白位置。因此首先要在“方式”下激活“将筛选结果复制到其他位置”，再在“复制到”编辑框中输入对单元格的引用。图4-19 对“高级筛选”对话框的设置图4-20 输入的筛选条件和得到的结果4分类汇总(1) 按部门简单汇总工资表Sheet2（4）操作提示：先将“工资号”、“基本工资”、“职务工资”、“补贴”各列隐藏起来，选择表中任意一单元格后，按要分类汇总的字段“部门”进行排序。执行“数据”“分类汇总”命令，弹出“分类汇总”对话框，依次选择好分类字段、汇总方式、汇总项等，单击“确定”按钮，其分类汇总的设置和结果如图4-21所示。隐藏明细按钮图4-21 分类汇总的结果 这里隐藏了后面三个处的明细，正像文件夹的展开和折叠一样，只要单击隐藏明细按钮，即可展开其明细。还应当注意到在“分类汇总”对话框中，汇总方式是可以选择的，如图4-22所示。图4-22 可以选择多种分类汇总方式 (2) 嵌套汇总在Excel数据清单中，用户先对某项指标汇总，然后再对汇总后的数据作进一步的细化，称为分类汇总的嵌套。例如，在工资数据清单中，先按“部门”汇总，再按“职务”分类汇总，就是一种嵌套汇总。操作提示：在Sheet2（5）中按以下步骤进行： 单击工资清单内任意一个单元格，先按“部门”递减，后按“职务”递增进行排序； 执行“数据” “分类汇总”命令，在弹出的“分类汇总”对话框中，先按第一个字段“部门”汇总求和，单击“确定”按钮得到结果； 再次执行数据菜单的“分类汇总”命令，选择要分类汇总的第二个字段“职务”进行汇总求和，但必须在“分类汇总”对话框中取消对“替换当前分类汇总”复选框的选择，单击“确定”按钮，则显示先按“部门”，再按“职务”的嵌套汇总结果。在隐藏明细后如图4-23所示。图4-23 工资表嵌套汇总隐藏明细后的结果5图表报告把Sheet2（4）复制到Sheet3中，将简单汇总的结果隐藏明细后生成图表。表格和图表如图4-24所示。图4-24 工资表汇总隐藏明细后的结果及其图表6保存后退出Excel。实 习 3 电子表格的数据分析一、实习目的1掌握如何用向导生成数据透视表。2掌握如何对已经生成的数据透视表自动套用格式。3掌握如何修改已经生成的数据透视表的汇总方式。二、实习内容1启动PowerPoint2000，打开“excel实习1”电子表格，将Sheet2工作表再复制1份，依次为Sheet2（6），选择Sheet2（6）进行操作。2用Sheet2（6）生成数据透视表。操作提示：选择表中任意一单元格后，执行“数据”“数据透视表和图表报告”命令，启动数据透视表向导。读者对其步骤之1和步骤之2都不陌生，关键是步骤之3对话框。在步骤之3对话框中，先把放置透视表的空白区域或新建工作表设置好，单击“版式”按钮，在弹出的“版式”对话框将相应字段拖到透视表的行、列和数据区中，如图4-25所示。这里“计数项：工资号”就是统计人数。图4-25 “3步骤之3”对话框和“版式”对话框的设置 单击“版式”对话框的“确定”按钮回到“3步骤之3”对话框，再单击“完成”按钮后，得到的数据透视表如图4-26所示。显示的“数据透视表”工具栏如图4-27所示。图4-26 按照“版式”设置得到的数据透视表图4-27 “数据透视表”工具栏3自动套用格式将Sheet2（6）复制为Sheet2（7）和Sheet2（8），在Sheet2（7）上操作。先单击数据透视表中任意单元格（表示当前对象是数据透视表），再单击“数据透视表”工具栏中的“设置报告格式”按钮，将打开“自动套用格式”对话框，从中选择“传统数据透视表”格式，得到如图4-28所示透视表。图4-28 用“自动套用格式”得到的数据透视表4更改汇总方式以上所做的数据透视表对于数据的汇总方式都是“求和”。现在为了更好地分析数据，我们可以在Sheet2（8）已有的数据透视表上对于数据的汇总方式改为求平均。操作如下：(1) 右单击“求和项：扣所得税”单元格，在打开的快捷菜单中执行“字段设置”命令，打开“数据透视表字段”对话框，在汇总方式列表中选择“平均值”