Excel数据管理与分析

2023-11-26 理论教育版权反馈

【摘要】：然而Excel在保持自身强势的同时，又汲取了数据库软件在数据管理方面的先进功能，使得Excel像处理数据库文件那样方便地对数据列表实施排序、筛选、分类汇总乃至数据透视。图4-33 “数据列表”示例14.7.2 列表排序“排序”是按规律组织数据的一种手段。

在数据处理方面，Excel以强大的计算功能独占鳌头，这是数据库软件无法与其媲美的。然而Excel在保持自身强势的同时，又汲取了数据库软件在数据管理方面的先进功能，使得Excel像处理数据库文件那样方便地对数据列表实施排序、筛选、分类汇总乃至数据透视。

4.7.1 数据列表

在介绍Excel的数据管理功能之前，先介绍“数据列表”的概念，它很重要，贯穿于排序、筛选、分类汇总、数据透视的全部操作过程中。

按照数据库的方式管理工作表，则工作表必须符合数据库文件的结构。例如，工作表中的一行对应于数据库文件中的一个记录，因此同一个工作表中不允许出现全等的行（即记录不能重复）；工作表中的一列对应于记录中的一个字段，因此工作表中的列必须有列名（类似于字段名），而且同列数据必须同类型；Excel中的一张工作表对应于一个数据库文件，因此一张工作表最好只描述一个数据表格，若描述多个数据表格，则表格与表格之间必须有全空的行或全空的列分隔。

我们将符合上述特点的工作表称为“数据列表”或“数据清单”。例如，图4-33和图4-34的工作表均为数据列表。

图4-33 “数据列表”示例1

4.7.2 列表排序

“排序”是按规律组织数据的一种手段。在Excel中，字段可以按数值大小、字符大小、时间顺序和逻辑值顺序进行排序。

1.单字段排序

这是最简单的排序，先单击欲排序的列名，打开“数据”选项卡，从“排序和筛选分组中直接单击“升排序”按钮或“降排序”按钮。

2.多字段排序

现在以图4-22工作表为例，介绍多字段排序的方法。

（1）将光标移至列表内。

图4-34 “数据列表”示例2

（2）从“数据”选项卡的“排序和筛选”组中单击“排序”按钮，打开“排序”对话框（图4-35）。

图4-35 “排序”对话框

（3）选择第一关键字（本例为“数学”）和排序方式（本例为“降序”）。

（4）单击“添加条件（）”按钮，再选择次关键字（本例为“物理”）和排序方式（本例为“降序”），单击“确定”按钮。

以上步骤便完成了两个字段的排序，即首先以数学成绩由高到低排序，若分数相同再以物理成绩由高到低排序，图4-36为排序结果。如果排序字段超过两个，重复步骤（4）。

图4-36 双字段排序示例

3.自定义排序

在Excel中，排序字段无论是数值型、字符型、日期型或逻辑性，它们都是按照系统内部约定的规则比较大小的，它们都有“值”的概念。数值型和日期型的大小概念不言自明，而逻辑值TRUE比FALSE大；文本型数据按ASCII码（西文字符）和拼音（汉字）排序。例如：字符‘A’（ASCII码为65）比字符‘a’（ASCII码为97）小，“工人”比“工程师”大，因为两个汉字串的首字符相同，而第二个不同，“人”的拼音为ren，而“程”的拼音为cheng，显然r比c大。

若某工作表希望按部门排序，顺序为：销售部、企划部、业务部、工程部、管理部⋯⋯由于无规则可循，因此按值的大小为依据实现排序已成为不可能，对于这种情况我们可以通过“自定义序列”的方法实现排序。下面以图4-37工作表为例，介绍首先以主关键字部门（排序顺序为：企划部、销售部、工程部、业务部、管理部），以员工代号为次关键字（升序）方式排序，排序结果如图4-38所示。

图4-37 自定义排序源工作表

图4-38 自定义排序示例

操作步骤：

（1）先将欲排序的数据系列（按企划部、销售部、工程部、业务部、管理部的顺序）定义成自定义序列。

（2）打开“排序”对话框，从“次序”下拉框中单击“自定义序列”选项，打开“自定义序列”对话框（图4-39）。

图4-39 “自定义序列”对话框

（3）从左侧“自定义序列”窗格中点击欲排序的序列内容，单击“确定”，返回“排序”对话框，“次序”下拉框中出现所选序列内容，单击“确定”，关闭“排序”对话框。

4.7.3 列表筛选

所谓“筛选”是指将数据列表中符合指定条件的记录显示出来，不符合条件的记录予以隐藏。该功能能够实现从众多数据中快速查找和遴选数据的目的。Excel 2010提供了“自动筛选”“自定义筛选”和“高级筛选”三种筛选方式。

1.自动筛选

自动筛选属于简单筛选，现在以图4-40工作表为例，介绍如何从该表中筛选出北京和浙江两地的男生记录。

（1）将光标移至列表内。

（2）从“数据”选项卡“排序和筛选”组中单击“筛选”按钮，列名变为筛选按钮。

（3）单击“来源”列的筛选按钮，弹出筛选表单，从中勾选“北京”和“浙江”两项（图4-41），再单击“性别”列的筛选按钮，从打开的筛选表单中勾选“男”选项（图4-42）。

（4）单击“确定”按钮，关闭筛选表单。

至此便完成了自动筛选，图4-43为筛选结果。

图4-40 新生情况表

图4-41 筛选“涞源”列

图4-42 筛选“性别”列

图4-43 筛选结果

2.自定义筛选

当被筛选对象不是可选择的单值（例如北京、浙江、男、女等）而是一个条件表达式时，自动筛选就无能为力了。例如，我们要从图4-40工作表中筛选出高考成绩在500分以上（含500分）和440分以下（不含440分）的学生记录，显然这里的筛选范围不是单值，而是两段区域，要用“高考成绩＜440或高考成绩＞＝500”这样的表达式来构成筛选条件，这种情况可以通过“自定义筛选”实现。

操作步骤：

（1）（2）与自动筛选相同。

（3）单击“高考成绩”列的筛选按钮，弹出筛选表单，将鼠标指针指向“数字筛选（）”选项上，弹出级联列表，从中单击“自定义筛选（）⋯”选项（图4-44），打开“自定义自动筛选方式”对话框，从中构造高考成绩＜440或高考成绩＞＝500的条件表达式（图4-45）。

（4）单击“确定”按钮。

3.高级筛选

自定义筛选最多只能针对同一个字段作“与”和“或”两种逻辑运算，因此对于某些更为复杂、而且条件交叉的筛选，自定义筛选也无能为力了。例如，对图4-40工作表筛选出北京、上海两地的男生记录，浙江、福建两地的女生记录。这是多字段，条件交叉的筛选，必须通过“高级筛选”来完成。

图4-44 “条件筛选”操作示例

图4-45 “自定义自动筛选方式”对话框(www.chuimin.cn)

操作步骤：

（1）构造条件区域

将用于筛选的条件构造成一个小的数据区域（见图4-46的G2：H6区域）。注意，条件区域同样必须符合列表的定义规则，有列名，与其他数据区域之间有全空的行或列分隔。

图4-46 “高级筛选”示例

（2）从“数据”选项卡的“排序和筛选”组中单击“高级”按钮，打开“高级筛选”对话框（图4-47）。

图4-47 “高级筛选”对话框

（3）Excel自动捕捉到列表区域为A2：E18区域，向“条件区域”文本框中输入地址引用$G$2：$H$6（可以用鼠标选择间接输入）。

（4）选择筛选结果的显示位置，默认位置为“在原有区域显示筛选结果”，本例选择“将筛选结果复制到其他位置”，单击“复制到”文本框，再单击A21单元格，此时“复制到”文本框中被间接输入了显示筛选结果的地址sheet2！$A$21（即当前工作表sheet2的A21单元格），单击“确定”按钮。

至此便完成了“高级筛选”的全部过程，北京和上海两地的男生记录，浙江和福建两地的女士记录被显示在A21开始的区域中。

4.7.4 列表的分类汇总

与排序、筛选相比较，分类汇总用于对数据列表通过自动创建公式并生成按关键字段分组的汇总结果，是数据分析中最常用的方法。例如，通过分类汇总，对某职工表按“职称”统计平均薪水，按“部门”统计人数；对某商品表按“商品名称”统计库存量，按商品类别统计积压资金等。

1.创建分类汇总表

下面以图4-40工作表为数据源，以图4-48为样张介绍创建分类汇总表的方法。

图4-48 “分类汇总”示例

①首先按分类字段（本例为“来源”）对列表排序。

②从“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，显示“分类汇总”对话框（图4-49）。

③从“分类字段（）”下拉框中选择分类字段（本例为来源），从“汇总方式（）”下拉框中选择汇总函数（本例为平均值），从“选定汇总项（）”列表框中选择汇总字段（本例为高考成绩）。

④对三个复选项目作适当选择。

替换当前分类汇总：若选中，则上一次分类汇总的结果将被本次分类汇总结果覆盖。

图4-49 “分类汇总”对话框

每组数据分页：若选中，每次分类汇总的结果独占显示一页。

汇总结果显示在数据下方：若不选中，汇总结果将出现在列的上方。

⑤单击“确定”，关闭对话框。

以上步骤便完成了对图4-40工作表按地区统计高考平均分。

2.汇总数据的分级显示

由图4-48可见，当列表完成了分类汇总以后，数据列表左侧的汇总区出现了“＋”（展开）和“－”（隐藏）按钮，上方出现了“1”“2”“3”（汇总层次）按钮。当呈现“－”按钮时，表示该组数据被展开为明细形式，单击它，则“－”变为“＋”，该组明细数据又被折叠隐藏，重复单击交替变化。

若单击“1”按钮，只显示总的汇总结果，所有细节全部隐藏；若单击“2”按钮，则显示每一组的汇总结果和总的汇总结果，数据细节不显示；若单击“3”按钮，则显示每一组的汇总结果、总的汇总结果以及全部数据细节（图4-48就是该方式）。

3.删除分类汇总

从“分类汇总”对话框中单击“全部删除”按钮。

4.7.5 列表的数据透视

说得直接一点，数据透视表具备筛选、排序、分类汇总三重功能，而且还能快速对数据列表按多个字段交叉分组生成汇总表，无论在功能上还是速度上都要强于分类汇总。

1.创建数据透视表

现在以图4-50为数据源，以图4-51为样张，介绍创建数据透视表的方法。

由图4-51可见，该汇总表的行分组字段为“用途”，列分组字段为“部门”，汇总字段为报销额，汇总方式为求和，是一个典型的交叉表汇总。

①将光标移至列表内。

②“插入”选项卡的“表格”组中单击“数据透视表”按钮，再从展开的列表中单击“数据透视表（）”选项，打开“数据透视表”对话框（图4-52）。

图4-50 制作数据透视表的源表

图4-51 数据透视表样张

③选择创建数据透视表的数据源：从当前工作簿中选择一个表或区域（自动默认）或使用外部数据源，本例为默认选项，此时系统自动选中当前工作表中的数据源区域为数据源，并将区域地址填入“表/区域（）”文本框中（本例为sheet3！$A$2：$E$41）。再选择放置数据透视表的位置（直接单击某单元格，该单元格地址便自动写入“位置（L）”框中，本例为A43），单击“确定”按钮。