办公中的表格数据处理VIP

第4章办公中的表格数据处理,办公自动化,主要内容,办公自动化,4.2“工资表”表格的建立,1.创建“工资统计表”框架（1）在Excel中有3张工作表，【重命名】第一张工作表为“工资管理统计表”。（2）设计表头（3）设计表结构其中用到的知识点：单元格格式中的各种设置、插入行、合并单元格、序列填充、格式刷。,办公自动化,4.2“工资表”表格的建立,2.对表进行特殊的设置插入批注右击该单元格，选择【插入批注】，向文本框中输入内容，在表格的任意处单击关闭批注，该单元格右上角出现一个红三角形，说明此单元格添加有批注，并处于隐藏状态。在该单元格的快捷菜单中还可选择【编辑批注】、【删除批注】、【隐藏/显示批注】等命令对批注进行设置。,单元格及批注内容,办公自动化,办公自动化,4.2“工资表”表格的建立,2.对表进行特殊的设置定义单元格数据的有效性：设置数据有效性规则，就可以避免错误。选定单元格或区域，【数据】【数据有效性】，进行相关设置。在Excel2007版中，设置好数据有效性规则后，单击数据功能区【数据有效性】按钮右侧的【】按钮，在下拉菜单中选择【圈释无效数据】命令，表格中所有无效数据即可被一个红色的椭圆形圈出来，错误数据一目了然。,办公自动化,4.2“工资表”表格的建立,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置公式中单元格的引用单元格引用指在公式中用单元格的名称调用该单元格中的数据来参与公式的计算。引用单元格生成公式后，该单元格中的数据发生改变时，计算结果会自动更新。所以在公式中引用单元格地址进行计算是非常方便实用的。单元格的引用分相对引用和绝对引用。,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置公式中单元格的引用（1）相对引用当公式移动或复制到其他位置时，引用的单元格地址也会做相应的改变。例：A3中的公式：A3=A1+A2复制到B3时：B3=B1+B2,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置公式中单元格的引用（2）绝对引用：公式中引用的单元格地址不随公式所在单元格的位置而变化。在单元格地址的列标和行号前加一个$号，就意味着该单元格被绝对引用例：C3中的公式：C3=$C$1+$C$2复制到D3时，D3=$C$1+$C$2,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置公式中单元格的引用公式中，根据实际情况混用相对引用和绝对引用。在行号前加$则行是绝对引用，列号前加$只是列绝对引用。例如D$5，意味着列随着公式的移动自动调整而行保持不变（即行绝对引用）。$D5则是列绝对引用。,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置非当前工作表中单元格的引用从其他工作表中引用单元格的格式是：工作表标签！单元格地址。例：Sheet1的A9单元格中的公式要相对引用Sheet2中的A3单元格，在Sheet1的A9单元格中输入的公式为=Sheet2！A3。,办公自动化,4.2“工资表”表格的建立,3.表内公式的设置用到的常用函数（1）求和函数Sum(range1,range2,)括号中可以是一个单元格区域，或多个单元格、单元格区域、具体数据、表达式等，之间用英文逗号间隔。（2）条件函数if(logical_test,value_if_true,value_if_false)根据条件表达的真假结果，返回不同的值，返回值可以是任意数据类型，如果是字符型的，要用字符定界符引号括起来。其中：logical_test，要判断的条件的任意值或表达式。可使用任何比较运算符。value_if_true，当条件为真时的返回值。value_if_false，当条件为假时的返回值。,办公自动化,4.2“工资表”表格的建立,4.个人税的计算,办公自动化,4.2“工资表”表格的建立,4.个人税的计算,个人税的计算,=-(IF(N8-R8-Q8-3500)=0,0,IF(N8-R8-Q8-3500)=1500,(N8-R8-Q8-3500)*0.03,IF(N8-R8-Q8-3500)=4500,(N8-R8-Q8-3500)*0.1-105,IF(N8-R8-Q8-3500)=9000,(N8-R8-Q8-3500)*0.2-555),办公自动化,办公自动化,主要内容,办公自动化,4.3数据分析,Excel提供了对表格中的数据进行分析的多种功能，如排序、筛选、分类汇总以及数据透视分析等。,办公自动化,4.3数据分析,1.筛选和排序Excel的数据筛选功能为查找记录提供了很大的方便，既可以按一定方式筛选，也可按特定条件筛选。,办公自动化,4.3数据分析,1.筛选和排序自定义筛选,办公自动化,4.3数据分析,1.筛选和排序自定义筛选,自定义筛选条件,自定义筛选结果,办公自动化,4.3数据分析,自定义筛选条件,办公自动化,4.3数据分析,1.筛选和排序排序：按指定字段改变顺序。,办公自动化,4.3数据分析,1.筛选和排序排序：-注意Excel中的排序最多可以按3个关键字进行，在按主要关键字的值排序的基础上，再按次要关键字的值进行。如果选择的是【无标题行】，则关键字列表框中只列出列号。,办公自动化,4.3数据分析,1.筛选和排序高级筛选：要通过复杂的条件来筛选单元格区域中的记录，就需要用到高级筛选。,筛选条件区域,办公自动化,4.3数据分析,1.筛选和排序高级筛选：,办公自动化,4.3数据分析,1.筛选和排序高级筛选：-注意要指定一个单元格区域作为条件区域，该区域与源数据区域间至少有两到三行空行间隔，条件区域必须具有列标签，在列标签下面的行中，输入所要匹配的条件。条件区域中列标签的内容必须与源表中列的字段名完全一致。用于筛选的列中数据必须连续，不能有空白单元格。,办公自动化,4.3数据分析,2.数据分类汇总分类汇总是通过Subtotal汇总函数计算得到的，可以为每列显示多个汇总函数类型。分类汇总还会分级显示列表，以便显示和隐藏每个分类汇总的明细行。分类汇总前应该先按照分类字段进行排序,办公自动化,4.3数据分析,2.数据分类汇总,办公自动化,4.3数据分析,2.数据分类汇总-注意用作分类的列中应该包含相同的事实数据，如职称、性别和部门等，而且该区域没有空的行或列。必须对分类的列进行排序。如果想按每个分类汇总自动分页，选中【每组数据分页】复选框即可。汇总方式有多种，如求和、求平均值、求最大值、求最小值等。再次使用【分类汇总】命令，以便使用不同汇总函数添加更多分类汇总。取消选中【替换当前分类汇总】复选框，可以避免覆盖现有分类汇总结果。单击行编号旁边的分级显示符号，或使用和显示或隐藏单个分类汇总的明细行，或只显示分类汇总和总计的汇总。【分类汇总】【全部删除】，可以删除分类汇总。,办公自动化,4.3数据分析,3.数据透视分析数据透视表能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，是Excel强大数据处理能力的具体体现。它可以对大量数据快速汇总和建立交叉列表，可以快速合并和比较大量数据，具有透视和筛选功能及数据分析功能，可以通过转换行或列以查看数据的不同汇总结果，也可以显示不同页面以筛选数据，或根据需要显示区域中的明细数据。,办公自动化,4.3数据分析,3.数据透视分析,办公自动化,4.3数据分析,办公自动化,4.3数据分析,办公自动化,4.3数据分析,选择不同的条件可以看到不同的汇总结果,数据透视表工具栏,办公自动化,4.3数据分析,3.数据透视分析从数据透视表中各项下拉列表中选择不同选项，可以获得各职称、各员工、各项数据的列表和汇总。生成的数据透视表中，还可以再加入其他列，如将“性别”列拖到“职称”列所在区，或选定“性别”后，在下面的组合框中选择【列区域】，单击【添加到】按钮。可以利用数据透视表工具栏中的工具对数据表进行其他设置，如选择【设置报告格式】，可以为透视表套用一种已有格式，还可以显示/隐藏明细数据、刷新数据等。,办公自动化,4.3数据分析,4.数据合并计算Excel中若要汇总和报告多个单独工作表的结果，可以将每个单独工作表中的数据合并计算到一个主工作表中。这些工作表可以与主工作表在同一个工作簿中，也可以位于其他工作簿中。对数据进行合并计算就是组合数据，以便能够更容易地对数据进行定期或不定期的更新和汇总。,办公自动化,4.3数据分析,4.数据合并计算,光标定位到要存放合并值的单元格,办公自动化,4.3数据分析,4.数据合并计算确保要引用的每个表格区域都具有相同的布局。如果要引用的工作表在另一个工作簿中，可以单击对话框中的【浏览】按钮查找文件。合并计算结果表中，行编号旁边出现分级显示符号，左侧栏中的和可以显示或隐藏单