中职高职计算机课件中文版电子表格-实例与操作：第6章-管理工作表中数据

第6章管理工作表中

的数据

除了利用公式和函数对数据进行计算和分析外，Excel还提供了数据清单、数据排序、数据筛选、分类汇总以及合并计算等功能，利用这些功能可以方便地管理、分析各类数据，从而为企事业单位的决策管理提供可靠的依据。本章内容提要建立数据清单

数据排序数据筛选分类汇总合并计算数据分析6.1建立数据清单

在Excel中，数据清单是包含一系列数据的工作表行，清单的第一行具有列标志，紧随其后的行是数据行，在标题与数据、数据与数据之间不能有空行，而且同一列数据必须具有同样的类型和含义。

在执行数据操作的过程中，Excel会自动将数据清单视作数据库，即数据清单中的列被视作数据库的字段，数据清单的列标记被视作数据库的字段名称，数据清单的每一行被认为是数据库的一条记录。6.1.1建立数据清单的准则数据清单的大小和位置（1）避免在一个工作表中建立多个数据清单，因为数据清单的某些处理功能（如筛选等），一次只能在同一工作表的一个数据清单中使用。（2）在工作表的数据清单和其他数据间至少留出一个空白列和一个空白行，从而在执行排序、筛选或插入自动汇总等操作时，有利于Excel检测和选定数据清单。（3）避免在数据清单中放置空白行和列，从而有利于Excel检测和选定数据清单。（4）避免将关键数据放到数据清单的左右两侧。因为这些数据在筛选数据清单时可能会被隐藏。列标志（1）需在数据清单的第一行里创建列标志。Excel将使用这些标志创建报告，并查找和组织数据。（2）列标志使用的字体、对齐方式、格式、图案、边框或大小写样式，最好与数据清单中其他数据的格式相差别。行和列内容（1）在设计数据清单时，应使同一列中的各行数据具有同样的类型和含义。（2）在单元格的开始处不要插入多余的空格，因为多余的空格影响排序和查找。（3）不要使用空白行将列标志和第一行数据分开。6.1.2直接输入数据清单

建立数据清单分以下两个步骤：（1）建立数据清单的标题：在数据清单的标题行中键入各字段名。（2）输入各项记录：可以直接在数据清单中键人数据（该方式下当数据清单栏目较多时容易出错）；或使用“记录单”输入数据。例如要制作一个通讯录，首先要规划好数据清单的结构，即字段，然后在工作表中输入相应的数据。6.1.3使用记录单输入与编辑数据1．使用记录单输入数据

2．使用记录单编辑数据

若要添加新的记录，可在记录单对话框中单击“新建”按钮，然后输入各字段内容即可。重复该操作可添加多条记录。添加完毕，单击“关闭”按钮，退出记录单对话框。若要修改记录，可单击“上一条”或“下一条”按钮查找并显示出要修改数据的记录，如图6-8所示，然后修改该记录的内容。修改完毕，单击“关闭”按钮，退出记录单对话框。若要删除记录，可单击“上一条”或“下一条”按钮查找要删除的记录，然后单击“删除”按钮，弹出提示对话框，单击“确定”按钮，在记录单中显示的记录被删除。6.2数据排序

排序是对工作表中的数据进行重新组织安排的一种方式。Excel可以对整个工作表或选定的某个单元格区域进行排序。在Excel中，可以对一列或多列中的数据按文本、数字以及日期和时间进行排序，还可以按自定义序列（如大、中和小）进行排序。大多数排序操作都是针对列进行的，但是，也可以针对行进行。6.2.1简单排序

简单排序是指对工作表中的单列数据按照Excel默认的升序或降序方式进行排列。升序排序数字：按从最小的负数到最大的正数进行排序。日期：按从最早的日期到最晚的日期进行排序。文本：按照特殊字符、数字（0…9）、小写英文字母（a…z）、大写英文字母（A…Z）、汉字（以拼音排序）进行排序逻辑值：FALSE排在TRUE之前。错误值：所有错误值（如#NUM!和#REF!）的优先级相同。空白单元格：总是放在最后。降序排序与升序排序的顺序相反。对“水果名称”列进行升序排序6.2.2多个关键字排序

多关键字排序，也就是对工作表中的数据按两个或两个以上的关键字进行排序，。

下面将水果价格表中的“水果名称”、“产地/规格”和“最高价”按升序进行排序。将水果价格表中“水果名称”、“产地/规格”和“最高价”按升序进行排序后的效果6.2.3自定义排序

如果已有的排序规则无法满足用户要求，此时可以用自定义排序规则来解决。在该方式下，需首先自定义序列，然后再按自定义的序列排序。

自定义序列

自定义排序结果6.3数据筛选Excel的数据筛选功能为我们提供了一种方便、快捷的数据查询方法，它只显示符合条件的数据，而将不符合条件的数据隐藏起来，且隐藏起来的数据不会被打印出来。要进行筛选数据操作，工作表中必须有列标签。6.3.1自动筛选

自动筛选一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。

将水果价格表中“产地/规格”为“国产”的记录筛选出来了6.3.2自定义筛选

在筛选按钮下拉列表中选择“自定义”项，可以限定一个或两个筛选条件，以显示出所需的数据。下面将水果价格表中“均价”大于或等于10且小于或等于25的记录筛选出来。筛选出水果价格表中“均价”大于或等于10且小于或等于25的记录6.3.3高级筛选

高级筛选用于根据多个条件来查询数据。要使用高级筛选，应首先在选定工作表中的指定区域创建筛选条件，然后打开“高级筛选”对话框，接下来分别选择要筛选的单元格区域、筛选条件区域和保存筛选结果的目标区域即可。1．多条件筛选

多条件筛选，即利用高级筛选功能查找出同时满足多个条件的记录。

下面筛选“水果价格表中最高价、最低价和均价均大于或等于10的记录。水果价格表中最高价、最低价和均价均大于或等于10的记录被筛选出来一定要将列标签一起选取2．多选一条件筛选

多选一条件筛选，也就是只要满足几个条件当中的一个，记录就会显示出来。下面查找出水果价格表中“产地/规格”为“国产”或“均价”大于10的记录。

筛选出水果价格表中“产地/规格”为“国产”或“均价”大于10的记录6.3.4取消筛选若要在工作表中取消对某一列进行的筛选，只需单击该列列标右端的筛选箭头，再在展开的列表中单击“全部”项即可。若要在工作表中取消对所有列进行的筛选，则选择“数据”>“筛选”>“全部显示”菜单。若要删除工作表中的筛选箭头，则选择“数据”>“筛选”>“自动筛选”菜单6.4分类汇总

分类汇总是把工作表中的数据按指定的关键字进行各种汇总。它不需要建立公式，Excel会自动对各类别的数据进行求和、求平均值等多种计算，并且分级显示汇总的结果。

对工作表的数据进行分类汇总时，应注意如下事项：因为分类汇总是按字段名进行的，所以要进行分类汇总的数据表的第一行必须有列标签，并且数据区域中没有空行或空列。在分类汇总之前必须先对数据进行排序，将要进行分类汇总的数据信息组合在一起，然后再进行分类汇总操作。进行分类汇总前，应选择数据区域中的任意一个单元格。在“分类字段”下拉列表框中选择字段时，该字段必须是已排序的字段，若选择其他列，则分类汇总结果是不正确的。6.4.1简单分类汇总

简单分类汇总主要用于对数据表中的某一列进行排序，然后进行分类汇总。下面对病人登记表中的“手术方式”进行统计。对“手术方式”列进行升序排序

简单分类汇总结果6.4.2多重分类汇总

对工作表中的某列数据选择两种或两种以上的分类汇总方式或汇总项进行汇总，就叫多重分类汇总，也就是说，多重分类汇总每次用的“分类字段”总是相同的，但汇总方式或汇总项不同。

下面将病人登记表按“性别”对“手术中失血总量”和“手术中输血量”进行求和。对“性别”列进行降序排序

第一重分类汇总结果

两重分类汇总结果6.4.3嵌套分类汇总

嵌套分类汇总是指在一个已经建立了分类汇总的工作表中再进行另外一种分类汇总，两次分类汇总的字段不相同。

下面查看病人登记表中“手术中输血次数”总计，以及“性别”列男、女个数。

对“手术中输血次数”和“性别”列进行升序排序

第一次分类汇总结果分类汇总项目设置与第一次不同

嵌套分类汇总结果6.4.4分级显示或隐藏汇总结果1．分级显示明细数据

在分级显示符号中，单击所需级别的数字，较低级别的明细数据会隐藏起来。其中数值越大的显示符号表示显示的汇总结果就越详细，单击符号可隐藏分类汇总结果的所有明细数据。2．隐藏与显示明细数据

要隐藏工作表中某组明细数据行，可以单击该组行号左侧的按钮。此时按钮变为按钮，单击该按钮可重新显示本组中的明细数据。

要隐藏明细数据，也可以单击某组中的单元格，然后选择“数据”>“组及分级显示”>“隐藏明细数据”菜单。隐藏明细数据后，选择“组及分级显示”子菜单中的“显示明细数据”项，可显示明细数据。3．清除分级显示

进行分类汇总后，如果只需要保留分类汇总的各项明细数据，而不需要分级显示符号，此时可选择“数据”>“组及分级显示”>“清除分级显示”菜单。分级显示符号消失

6.4.5取消分类汇总工作表中的分类汇总结果被清除6.5合并计算6.5.1建立合并计算

所谓合并计算，是指用来汇总一个或多个源区域中数据的方法。

要想合并计算数据，首先必须为合并数据定义一个目标区，用来显示合并后的信息。此目标区域可位于与源数据相同的工作表中，也可位于另一个工作表中。其次，需要选择要合并计算的数据源。此数据源可以来自单个工作表、多个工作表或多个工作簿。1．按位置合并计算

按位置合并计算，要求源区域中的数据使用相同的行标签和列标签，并按相同的顺序排列在工作表中。

下面将几个销售部门办事费用开支的工作表“按位置合并计算”到公司的总开支工作表中。

销售各部费用表

在工作表“三部”的右侧新建一个与“一部”格式一致的工作表“总部”

用同样的方法将“二部”和“三部”中的单元格区域B3：F6添加到“所用引用位置”列表中得到相同季度各部门各费用的小计及合计2．按分类合并计算

当源区域中的数据没有相同的组织结构，但有相同的行标签或列标签时，需采用按分类合并计算方式进行汇总。

下面将两个部门的电脑配件费用表合并到“总费用”工作表中。

两个部门的费用表

按分类合并计算结果6.5.2更改合并计算1．增加源区域

如果增加了新的工作表，要将这个源区域数据添加到一个已存在的合并计算中，只需单击要放置合并结果工作表单元格区域左上角的单元格，接下来的操作与前面介绍的一样：将其添加到“所有引用位置”列表中并确定即可。

对来自多个工作表中的数据进行合并计算后，如增加或删除了源区域中的某些数据，或添加（删除）了作为数据源区域的某个工作表，此时就需要更改合并计算。需要注意的是：只有对没有建立与源区域链接的合并工作表，才能够执行更改操作。2．从合并计算中删除部分源区域重新选择数据区域

例如，要将“电脑配件费用表”工作簿“甲部”中的“键盘”和“鼠标”的有关数据不计算在内，此时就需要更改“总费用”工作表中引用的区域。删除先前添加的引用区域更改前的合并计算结果更改后的合并计算结果6.6数据分析Excel为数据查询、制作报表、数据运算等数据处理提供了许多分析工具和实用函数，这些工具和函数在解决工程计算、金融分析和财政决算等方面发挥着巨大作用。

安装加载宏并添加相应的分析工具6.5.1假设分析Excel2003提供了与假设前提相关的三种简单的数据分析方法：单变量求解、模拟数据表和方案管理器。它们的共同特点是问题的求解都由两个部分组成：一个待求解目标模型，一组与模型相关的变量。通过假设求解过程可以获得两种类型的结果：一是在指定假设目标结果的制约下与目标相关某因素的可行解，二是能够在指定变量值域范围的前提下观察各变量对于模型所产生的影响。

假设公式（或函数）中的一个或两个变量有一组替换值，将这些值分别代入公式（或函数）中取得一组结果，这组变量替换值和结果值构成的一个表称为模拟运算表。若只有一个变量，称为单变量模拟运算表；若有两个变量，称为双变量模拟运算表。模拟运算表可以将变量取不同值时对应的结果一次计算出来，并按规