第 3章 列表中的简单数据分析
电脑中的表格有多种类型,考虑后续计算分析的要求,必须要有数据源表,也就是列
表格式的数据源。本章将介绍一些在数据列表中执行数据分析的技巧。
3.1 在列表中执行排序
没用过函数的用户大有人在,没用过排序的估计就很少了。排序可以说是 Excel 功能
中普及 广的,但也并非人人都会用。用过不等于会用,这就是 Excel 软件 大的特点。
3.1.1 选择适合的排序方法
设置排序操作不止一种方法,选择何种方法首先要考虑操作的对象是什么样的数据源。
1.数据列表中的简单排序
对于一个规范的数据源,如之前介绍的符合“数据源表”要求的数据列表,在其中使
用排序功能是很简单的。如图 3.1 所示的数据列表,排序只需鼠标点几下即可。
图 3.1 需执行排序的数据列表
如需对“发生额”字段按金额大小降序排列,操作步骤如下:
(1)将鼠标定位在数据列表 E 列中。
(2)单击“开始”|“编辑”|“排序和筛选”,在弹出的下拉菜单中选择“降序”命令,
也可以单击“数据”|“排序和筛选”组中的“降序”按钮,如图 3.2 所示。
按“发生额”字段降序排列已完成,就这样简单。
2.不规范数据源中的排序
如果要操作的数据源不规范,不符合“数据源表”的要求,也许就不能按之前介绍的
第 3 章 列表中的简单数据分析
·55·
方法排序了。
图 3.2 设置“降序”排列
1)数据区域中有合并单元格
如图 3.3 所示的数据源,设置了多层表头,在第 1 行中有合并单元格。
图 3.3 有合并单元格的数据源
如需对 G 列“期末余额”字段执行升序排序,将鼠标定位在数据源 G 列中,如 G15
单元格,单击“升序”按钮后,Excel 报错,如图 3.4 所示。
图 3.4 简单排序时的出错提示
原因在于执行排序命令后,Excel 会自动选取当前的数据区域,这里是 A1:G19 单元
格区域。因该区域的第 1 行中有合并单元格,直接执行排序就会出错。解决方案为绕过合
并单元格,操作步骤如下:
(1)选取要排序的数据区域 A4:G19。
(2)连续按 Tab 键,确保活动单元格在数据区域中的 G 列(可观察“名称框”中的显
示变化)。
基础知识篇 不要输在起跑线上
·56·
(3)单击功能区中的“升序”按钮。
其中步骤(2)很关键,Excel 会按活动单元格所在列的项目执行排序。
2)数据区域中有空行
如图 3.5 中的示例,数据区域中有空行,如直接执行简单的排序,因 Excel 会根据活
动单元格所在的位置自动选择扩展数据源。空行的存在会让 Excel 犯错误,漏选部分需要
排序的区域。
图 3.5 数据区域中有空行
解决方案和在有合并单元格的区域中排序类似,操作步骤如下:
(1)选取要排序的数据区域 A2:G10。
(2)连续按 Tab 键,确保活动单元格在数据区域中的 G 列。
(3)单击功能区中的“降序”按钮完成排序,操作结果如图 3.6 所示。
图 3.6 有空行数据区域排序的结果
排序结果 后是两个空行,即使改为升序排列,空行也是在 后。由此可见在排序中,
空格和 0 值是不等价的。
3.数据列表中按多个字段排序
如果要排序的条件有多个,可以通过“排序”对话框来设置。如图 3.1 所示的数据源,
要求按照主要关键字“日期”升序、次要关键字“发生额”降序排列,操作步骤如下:
(1)鼠标定位在数据源区域中。
(2)单击“开始”|“编辑”|“排序和筛选”,在弹出的下拉菜单中选择“自定义排序”
第 3 章 列表中的简单数据分析
·57·
命令,也可以单击“数据”|“排序和筛选”组中的“排序”按钮,弹出“排序”对话框。
(3)在“排序”对话框中设置主要关键字为“日期”,次序为“升序”。
(4)单击“添加条件”按钮,设置次要关键字为“发生额”,次序为“降序”。
(5)单击“确定”按钮完成退出,排序条件设置如图 3.7 所示。
图 3.7 设置“排序”对话框
在 Excel 2007 及以上版本中,可以设置 64 个排序条件,轻松搞定多个关键字排序。
4.认识排序对话框
排序并不如多数用户想象的那样简单,隐藏着多个用户不熟悉的选项,举例如下。
点开“排序”对话框中“排序依据”列表,除了默认的按“数值”排序外,还有
按“单元格颜色”、“字体颜色”和“单元格图标”排序;
点开“排序”对话框中“次序”列表,有“升序”、“降序”和“自定义序列”
选项,如图 3.8 所示;
单击“选项”按钮,弹出的“排序选项”对话框中对于排序的“方向”和“方法”
都可以选择,如图 3.9 所示。
图 3.8 排序依据和次序 图 3.9 排序选项
在“排序”对话框中做相应的设置,即可完成一些非常规的排序要求,如“按行排序”、
“按笔划排序”、“按自定义序列排序”、“按单元格颜色排序”、“按字体颜色排序”以
及“按单元格图标排序”等等。
这些排序都是一些动动鼠标就能完成的基础操作,限于篇幅无法一一介绍,读者可以
基础知识篇 不要输在起跑线上
·58·
参阅拙作《绝了!Excel 可以这样用——数据处理、计算与分析》中第 9 章第 1 节的内容。
示例文件:3.1.1 选择适合的排序方法.xlsx。
3.1.2 万物皆有序
常规的排序就是对数字排序,非升序即降序。如参与排序的数据不仅是数值,比如有
文本、逻辑值等,结果会如何呢?
1.已知的排序规则
在图 3.6 的示例中,不管是升序排列还是降序排列,空格都排列在数值的后面,这到
底是什么规律呢?
在 Excel 帮助文件,如 LOOKUP 函数的帮助文件中,有关于对值升序排列的描述,即
“...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE”。根据该规律并结合一些测试,可以得到如下结论:
数值小于文本,文本小于逻辑值;
逻辑值中 FALSE 小于 TRUE;
英文字母按字母顺序排列。
掌握这些之后,还是有些迷惑,如对于文本中的汉字,又是如何排序的呢?比如企业
中需要按照职务大小排序,常见的职务有总经理、副总经理、经理、副经理、主管等。对
这些职务执行升序排列,得到的结果如图 3.10 所示,与期望的顺序并不相同。
不需要困惑,其实 Excel 已经给出了提示,在图
3.9 所示的“排序选项”对话框中,默认的排序方法
为按“字母排序”,就是按排序项中各个字符的字母
顺序排列。如这个排序项是汉字,则按照第一个汉字
的汉语拼音首字母比较,首字母相同则比较后续字母
的顺序。
职务“副经理”和“副总经理”第一字符中第一
个拼音字母是 F,排列在“经理”的 J 以及“主管”
和“总经理”的 Z 前面;“副经理”第二个字符的第一个拼音字母是 J,排列在“副总经
理”第二个字符的第一个字母 Z 前面,排序结果就是这样得到的。
2.增加排序规则
如果不做特别的设置,Excel 仅会按已有的规则排序。如需实现按职务高低排序之类
的规则,首先要在 Excel 中设置这样一个规则,然后告诉 Excel,在此次排序中需要使用这
个规则。操作步骤如下:
(1)单击“文件”|“选项”命令,在弹出的“Excel 选项”对话框中选取“高级”选
项卡,“常规”组中单击“编辑自定义列表”按钮。
(2)在弹出的“自定义序列”对话框的“输入序列”框中输入需要自定义排序的序列,
每个排序项输入完成之后按回车换行。
(3)单击“添加”按钮添加到“自定义序列”列表框中,如图 3.11 所示。
(4)单击“确定”按钮回到“Excel 选项”对话框,再单击“确定”按钮完成退出。
图 3.10 升序排列的职务
第 3 章 列表中的简单数据分析
·59·
图 3.11 设置“自定义序列”
(5)鼠标定位在需要排序的区域中,单击“数据”|“排序和筛选”|“排序”命令。
(6)在弹出的“排序”对话框中的“次序”下拉列表中选取“自定义序列”。
(7)在弹出的“自定义序列”对话框中选取之前添加的自定义序列“总经理,副总经
理,经理,副经理,主管”,单击“确定”按钮后回到“排序”对话框,如图 3.12 所示。
图 3.12 设置“自定义序列”排序
(8)单击“确定”按钮完成排序设置。
示例文件:3.1.2万物皆有序.xlsx。
3.1.3 一些特殊的排序
了解了排序规则之后,对于一些奇怪的排序结果也就见怪不怪了,知道了原因,自然
有办法解决。
1.数字排序出错
对数字排序属于 基础的排序操作,如果出现了意外的结果,可能和文本数值有关。
如图 3.13 所示,是一个销售统计表按“销售量”字段降序排列的结果。
基础知识篇 不要输在起跑线上
·60·
点开排列在前面的 3 项左上角的绿色小三角,显示“以文本形式存储的数字”。在排
序规则中,数值永远小于文本,面对文本数值亦是如此。
解决方案为将文本数值转化为真正的数字,如选中 C 列后做一次原地分列即可。
示例文件:3.1.3.1数字排序出错.xlsx。
2.文本数字混合排序
如果一个字符串中既有数字又有文本,则只能认为是文本性质的,排序的时候需要遵
循文本排序的规则,即一个个字符顺序比较。
如图 3.14 所示,订单号以英文字母 NB 开头,数字顺序号结束。如直接对“订单号”
字段执行升序排序,结果并非预期。
图 3.13 文本数值排序 图 3.14 文本数值混合排序
直接对 B 列排序就是这样的结果,要让订单号按其中的数值顺序排列,需要使用辅
助列。
1)通过分列构建辅助列
通过分列的方法把“订单号”字段中的英文字符和数值分开,然后按数值列排序,操
作步骤如下:
(1)选取 B 列“订单号”字段,单击“数据”|“数据工具”|“分列”命令,在弹出
的“文本分列向导-第 1 步,共 3 步”对话框中选择“固定宽度”,如图 3.15 所示。
图 3.15 文本分列向导 1
第 3 章 列表中的简单数据分析
·61·
(2)单击“下一步”按钮进入“文本分列向导-第 2 步,共 3 步”对话框,在“数据预
览”列表框中建立分列线,在字符 NB 之后,如图 3.16 所示。
图 3.16 文本分列向导 2
(3)单击“下一步”按钮进入“文本分列向导-第 3 步,共 3 步”对话框,在“目标区
域”框中选取分列结果存放区域 D1 单元格,如图 3.17 所示。
图 3.17 文本分列向导 3
(4)单击“完成”按钮退出,完成分列操作,效果如图 3.18 所示。
图 3.18 分列完成的效果
基础知识篇 不要输在起跑线上
·62·
(5) 后对分列形成的“单号”字段执行一次升序排列即可。
2)通过函数构建辅助列
除了分列的方法之外,还可以使用函数来构建辅助列,D2 单元格公式如下,向下复制
完成,如图 3.19 所示。 =--RIGHT(B2,LEN(B2)-2)
图 3.19 使用函数构建辅助列
之后对辅助列执行一次升序排列就可以达到按照“订单号”字段中数值部分升序排列
的效果。
D2 单元格中的公式用 LEN 函数判断“订单号”字段中的字符长度,减去 2 后即为数
值部分的长度,用 RIGHT 函数取出其中的数字部分。
因文本函数 RIGHT 得到的结果是文本,必须再执行一次数值运算,将文本数值转换
为真正的数字。
3)改变习惯
类似的问题还有很多,如图 3.20 中,对“月份”字段按升序排列时,“10 月”和“11
月”排列在“1 月”的前面。
图 3.20 对“月份”字段升序排列
这也是按文本排序规则造成的,排序项“10 月”第 2 个字符是“0”,而“1 月”第 2
个字符是“月”,在升序排列的时候,数字永远是排在文本前面的。
如在做表时能预见到以后会按照“月份”字段排序,那就有必要改变一下之前操作的
习惯。在“月份”字段中仅录入月份的数字,那样排序时就能得到期望值。
还可以使用单元格自定义格式,对录入的数字做一些修饰,使其显示的是“#月”,
如图 3.21 中,对 C 列录入的月份数值使用单元格自定义格式,在“自定义”中使用如下
代码:
第 3 章 列表中的简单数据分析
·63·
G/通用格式"月"
图 3.21 对录入的数字使用自定义格式
默认的格式代码就是“G/通用格式”,只需要在后面加上一个“月”字,外面的一对
半角英文状态下的双引号系统会自动生成。
示例文件:3.1.3.2 文本数字混合排序.xlsx。
3.2 对数据列表执行筛选
和排序一样,筛选也是运用 广泛的操作之一,在 Excel 2010 中,筛选增加了一些新
的内容,本节将做详细讲解。
3.2.1 设置筛选
要做筛选操作,首先要设置筛选,具体操作方法也和数据源的规范程度有关。
1.对规范的数据列表设置筛选
如是一个规范的数据列表,设置筛选时只需要将鼠标定位在数据列表中,单击“开始”|
“编辑”|“排序和筛选”,在弹出的下拉菜单中选择“筛选”命令;也可以单击“数据”|
“排序和筛选”|“筛选”命令,如图 3.22 所示。
图 3.22 设置筛选
顺序按“Alt”、“A”、“T”3 键也能设置筛选,设置筛选之后,字段名上自动生成
一个筛选标记,即一个向下的三角箭头 ,如图 3.23 所示。
基础知识篇 不要输在起跑线上
·64·
图 3.23 设置筛选之后的效果
筛选设置完成之后,再重复一次设置筛选的操作,就能取消筛选设置,回到初始状态。
2.对不规范区域设置筛选
如要设置筛选的数据区域不是一个规范的数据列表,直接设置筛选的方法可能会出
错,需要根据不同的情况选择适用的方法。
1)表头有合并单元格
如图 3.24 所示的数据源,表头有合并单元格,数据区域在第 4 行之后,可以选中第 2
行或者第 3 行后执行筛选(因 A 列和 B 列都有合并单元格,是无法直接选中真正的表头
A3:G3 的)。
图 3.24 对有合并单元格的数据区域执行筛选
2)数据区域中有空行
如图 3.25 所示的数据区域中有空行,要规避空行对设置筛选时的影响,应选取整个数
据区域(包含表头),这里是选中 A1:G10 单元格区域,然后再设置筛选,此时筛选结果
中已包含 2~10 行的内容。
图 3.25 对有空行的单元格区域执行筛选
第 3 章 列表中的简单数据分析
·65·
3.对工作表中多个区域设置筛选
一个工作表中可能会存在多个表格区域,如图 3.26 所示,能否同时对这些表格区域设
置筛选?
已对 A1:B8 单元格区域设置了筛选,此时将鼠标定位在 D1:F10 单元格区域中,单
击“筛选”命令,会取消原先对 A1:B8 单元格区域设置的筛选,再次单击“筛选”命令,
仅完成对 D1:F10 单元格区域的筛选设置。
图 3.26 对工作表中多个区域设置筛选
使用常规的筛选操作,是无法对一个工作表中的多个数
据区域同时设置筛选的。解决方案是使用 Excel 中的“表格”
功能,操作步骤如下:
(1)鼠标定位在 D1:F10 单元格区域中,单击“插入”|
“表格”|“表格”命令,弹出“创建表”对话框,如图 3.27
所示。
(2)Excel 已智能地选择了表格的数据来源,不改变默认
设置,单击“确定”按钮完成,效果如图 3.28 所示。
图 3.28 插入表格对多个区域设置筛选
Excel 中的“表格”本身就有筛选功能,通过设置表格,绕过了常规操作只能对工作
表中一个区域设置筛选的限制。
4.智能的筛选类型
Excel 软件是相当智能的,在筛选设置完成之后,会根据已设置筛选字段的数据类型
图 3.27 创建表格
基础知识篇 不要输在起跑线上
·66·
做出判断,自动选择可用的筛选类型。
如点开图 3.23 中数值类型字段“发生额”的筛选标记后,界面中“搜索”框上方显示
的是“数字筛选”,鼠标放在“数字筛选”上,右侧会弹出二级菜单,显示更多的数字筛
选方式,如图 3.29 所示。
如对日期值类型的“日期”字段或者文本值类型的“部门”字段执行筛选,显示的则
是“日期筛选”或者“文本筛选”。
图 3.29 数值字段适用“数字筛选”类型
如对筛选字段中单元格设置过字体颜色或背景颜色(包含条件格式中的设置),或者
设置过条件格式中的“色阶”、“图标集”之类,还会显示“按颜色筛选”(图 3.29 中该
选项是灰色的)。
示例文件:3.2.1设置筛选.xlsx。
3.2.2 执行筛选
设置完筛选之后就可以执行筛选操作了。可执行的筛选方法有多种,常规的方式是在
“不重复值列表”中筛选,还可以使用系统自定义的筛选方式、使用鼠标右键筛选,以及边
搜索边筛选等。
1.在“不重复值列表”中筛选
这是 常规的方法,以在“发生额”字段中筛选为例,点开筛选标记后,在“不重复
值列表”中勾选要筛选的项目,不需要的项目则取消勾选。
在筛选完成之后,字段标题中的筛选标记变成沙漏的样式 ,如需取消已执行的筛选,
在点开筛选标记后,单击“从"发生额"中清除筛选”即可。这里的“发生额”代表已执行
筛选的字段名。
第 3 章 列表中的简单数据分析
·67·
这种操作非常简单,但自 Excel 2007 版开始,“不重复值列表”中允许出现 1 万条记
录,在记录较多的情况下,直接在“不重复值列表”中勾选需要的项目,会有大海捞针的
感觉。
2.按鼠标右键筛选
如果要筛选的项目就在眼皮底下,不需要再到“不重复值列表”中去找,选择该项目
所在单元格,鼠标右键执行筛选即可。
如需在“科目”字段中筛选“促销费”,可先选中有“促销费”的单元格,如 D817
单元格,按右键,在弹出的快捷菜单中选取“筛选”|“按所选单元格的值筛选”命令,如
图 3.30 所示。
图 3.30 按右键执行筛选
使用右键筛选,除了按选定单元格的值筛选外,还可以按选定单元格的字体颜色、背
景颜色和条件格式设置的图标。
3.使用系统内置的筛选方式
Excel 已智能地根据筛选字段的数据类型自定义了一些筛选方式,如需要筛选的数据
有规律,灵活运用系统内置的筛选方式无疑能大幅度提升工作效率。
1)筛选一定金额范围内的发生额
如需筛选单笔 15~20 万的发生额,可使用“数字筛选”类型下的“介于”筛选方式,
弹出如图 3.31 所示的“自定义自动筛选方式”对话框,分别设置两个端点的金额为 15 万
和 20 万,保持“与”的关系。
图 3.31 筛选 15~20 万之间的发生额
2)筛选 5 笔 大的发生额
在“数字筛选”类型下选中“10 个 大的值”筛选方式,在弹出的“自动筛选前 10
基础知识篇 不要输在起跑线上
·68·
个”对话框中设置数值为 5,保持其他的默认设置不变,如图 3.32 所示。
这里能显示的项目不局限于 10 个,显示方式为“ 大”和“ 小”,单位为“项”
和“百分比”。
3)筛选金额 大的 1%发生额
在“数字筛选”类型下选中“10 个 大的值”筛选方式,在弹出的“自动筛选前 10
个”对话框中设置数值为 1,将显示单位改为“百分比”,如图 3.33 所示。
图 3.32 设置金额 大的 5 项 图 3.33 筛选金额 大的 1%发生额
数据列表中有 825 条记录,按 1%筛选出发生额 大的 8 条记录。
4)筛选“管理”部门的记录
如需筛选“管理”部门的记录,可在“文本筛选”类型下选择“开头是”筛选方式,
在弹出的“自定义自动筛选方式”对话框中设置如图 3.34 所示。
图 3.34 筛选“管理”部门的记录
5)筛选某期间的发生额
如需筛选 2013 年 1 月 15 日前的发生额,在“日期筛选”类型下选中“之前”筛选方
式,在弹出的“自定义自动筛选方式”对话框中设置日期为“2013-1-15”,保持其他选项
不变,如图 3.35 所示。
图 3.35 筛选 2013 年 1 月 15 日前的发生额
第 3 章 列表中的简单数据分析
·69·
6)自定义筛选
不论是“数字筛选”、“文本筛选”还是“日期筛选”,在系统内置筛选方式的 下
方,都有一种方式“自定义筛选”。而且在执行这 3 种筛选类型下的多数内置筛选方式时,
也会弹出“自定义自动筛选方式”对话框,如图 3.31、图 3.34 和图 3.35 所示。
比较这 3 个对话框,都只有两个筛选条件,不同之处是默认的条件不一样,这个和筛
选字段的数据类型有关。对于有两个筛选条件的操作,可以直接选中“自定义筛选”方式,
但由此也产生了一些疑问,如在筛选时是否能对某个字段设置两个以上的筛选条件,至少
使用系统自定义的筛选方式做不到。
4.边搜索边筛选
在 Excel 2010 版中新增了在筛选时搜索的功能,在搜索框中输入要搜索的关键字,“不
重复值列表”中的显示项会自动变化。
1)不带通配符的搜索
例如在“部门”字段的筛选下拉框中搜索“管理”,如图 3.36 所示,单击“确定”按
钮能返回所有名称中有“管理”字符的部门。
如不需要筛选“加盟连锁-管理”部门,可在“不重复值列表”中取消对该项的勾选,
再单击“确定”按钮完成。
2)带通配符的搜索
如要返回的仅是一级部门“管理”,也就是以字符“管理”开头的部门,不包括图 3.36
中自动选中的“加盟连锁-管理”,可以在搜索框中使用通配符,搜索“管理*”,如图 3.37
所示。
图 3.36 “部门”字段中搜索“管理” 图 3.37 “部门”字段中搜索“管理*”
此时仅会搜索到以“管理”开头的部门,单击“确定”按钮完成即可。
3)实现对多个并列条件的筛选
“自定义自动筛选方式”对话框中只能设置两个筛选条件,如有两个以上的筛选条件,
可使用搜索框操作实现。
如需在“科目”字段下筛选一级科目“办公费”、“电话费”和“人员成本”,操作
基础知识篇 不要输在起跑线上
·70·
步骤如下:
(1)搜索“办公费*”,单击“确定”按钮完成。
(2)再搜索“电话费*”,勾选“将当前所选内容添加到筛选器”,然后单击“确定”
按钮完成,如图 3.38 所示。
(3)继续搜索“人员成本*”,同样勾选“将当前所选内容添加到筛选器”,单击“确
定”按钮完成对一级科目“办公费”、“电话费”和“人员成本”的筛选。
4)和其他筛选方法结合使用
边搜索边筛选的方法可以和其他筛选方法结合使用。沿用上个案例,可以先使用系统
内置的“自定义筛选”方式筛选一级部门“办公费”和“电话费”,然后在此基础上,搜
索“人员成本*”,并勾选“将当前所选内容添加到筛选器”。
5)对日期值字段执行搜索
Excel 中的日期是一个特殊的数值,设置筛选后点开“日期”字段的筛选标记,在“不
重复值列表”中已对日期做了分组,如图 3.39 所示。
图 3.38 保留之前的筛选结果 图 3.39 分组日期
如需搜索具体的日期,例如搜索“2013-1-31”这一天的记录,会显示“没有与搜索匹
配的项目”,如图 3.40 所示,但实际上数据区域中是
有这一天的记录的。
解决方案为取消对日期字段的分组。可单击“开
始”|“选项”命令,在弹出的“Excel 选项”对话框
中取消“高级”选项卡下“此工作簿的显示选项”组
中“使用"自动筛选"菜单分组日期”选项,如图 3.41
所示。
图 3.41 取消对日期字段筛选时的自动分组
图 3.40 未筛选到记录
第 3 章 列表中的简单数据分析
·71·
示例文件:3.2.2执行筛选.xlsx。
3.2.3 处理复杂的筛选条件
之前介绍的都是对一个字段执行筛选,实际工作中的筛选要求可能会更加复杂。如涉
及多个字段多个条件的筛选,常规的自动筛选未必能够解决。
1.各个字段之间是“与”关系
如各个字段之间是“与”关系,即筛选各个条件同时满足的记录,可分步执行筛选。
如筛选“三联”公司,“管理-法务”部门的“电话费-个人电话费”科目的记录,执行 3
次筛选(顺序并无先后的规定),完成效果如图 3.42 所示。
图 3.42 3 个“与”关系的条件筛选
2.各个字段之间是“或”关系
如各个字段之间是“或”关系,即满足多个条件之一就要筛选出来,此时直接使用自
动筛选功能就无解了。
例如对如图 3.43 中的数据源,需筛选 EAST 区域或者“新概念”客户分类的记录。
图 3.43 两个“或”条件的筛选
如果还是按上个案例中的做法,分别对“区域”和“客户分类”字段执行筛选,只会
得到一条记录,而期望的结果如图 3.44 所示。
图 3.44 期望的筛选结果
基础知识篇 不要输在起跑线上
·72·
直接对各个字段执行筛选,仅能满足各个字段并列的筛选条件,即“与”关系,而处
理“或”关系,就需要使用其他方法。
3.认识“高级筛选”功能
在 Excel 中提供了一个更加高级的筛选功能,在低版本中叫做“高级筛选”,而在 Excel
2010 等高版本中,这项功能出现在“数据”选项卡下“排序和筛选”组中,名字变成了“高
级”。习惯上还是把这项功能叫做“高级筛选”。
1)使用“高级筛选”
要使用“高级筛选”功能,首先要设置一个筛选的“条件区域”。这里在 F1:G3 单
元格区域中设置条件区域,如图 3.45 所示。
图 3.45 设置“高级筛选”的条件区域
在设置时,条件区域的首行要和数据区域中的字段名一致,两个条件分行写代表“或”
关系,并列写则代表“与”关系。设置完成后进入“高级筛选”操作,步骤如下:
(1)鼠标定位在高级筛选返回结果所在工作表中,
单击“数据”|“排序和筛选”|“高级”命令。
(2)在弹出的“高级筛选”对话框中,在“方式”
下点选“将筛选结果复制到其他位置”,“列表区域”
框中已有默认的选择,“条件区域”框中选取之前设置
的条件区域“F1:G3”,“复制到”框中选取要复制到
的位置“I1”单元格,如图 3.46 所示。
(3)单击“确定”按钮完成退出,在 I1:L7 单元格
区域中返回了高级筛选的结果,和图 3.44 中的期望结果
一致。
2)自动出现的定义名称
在设置过高级筛选之后,Excel 中会自动对一些区域
定义名称,可使用“Ctrl+F3”组合键进入“名称管理器”中查看。其中名称 Criteria 为高
级筛选时设置的条件区域。
3)“高级筛选”的局限性
因筛选时需要构建条件区域,如筛选条件中既有“或”关系又有“与”关系,构建的
条件区域会比较复杂,此时不推荐使用高级筛选。
对于复杂的筛选条件,使用函数公式构建辅助列的方式更加通用,只要有能力把判断
的函数公式写出了,筛选的问题就解决了。而相对于“与”关系、“或”关系,函数仅是
AND 和 OR 而已。示例文件中在 E 列设置辅助列,E2 单元格中公式如下,双击向下填充
图 3.46 设置“高级筛选”
第 3 章 列表中的简单数据分析
·73·
完成,如图 3.47 所示。 =IF(OR(A2="EAST",B2="新概念"),"是","")
图 3.47 使用辅助列筛选
完成之后对辅助列执行筛选,筛选公式返回结果为“是”的项目,把复杂的条件筛选
转换成了函数公式的问题。
示例文件:3.2.3处理复杂的筛选条件.xlsx。
3.3 使用分类汇总
对于数据列表,除了排序和筛选之外,还经常需要查看一些汇总的数据,此时可以使
用 Excel 自带的分类汇总功能。
3.3.1 建立分类汇总
相对于排序和筛选,使用分类汇总的操作有些麻烦,首先需要对数据区域按分类字段
执行排序,否则得不到正确的结果。
1.简单的分类汇总
如需对图 3.48 中的数据源按区域统计销售数量和金额,可以执行分类汇总操作。
图 3.48 需要分类统计的数据源
操作步骤如下:
(1)按“区域”字段对数据源排序(升序降序都可以)。
(2)鼠标定位在数据区域中,单击“数据”|“分级显示”|“分类汇总”命令。
(3)在弹出的“分类汇总”对话框中,设置“分类字段”为“区域”,在“选定汇总
项”列表框中勾选“数量”和“金额”,保持其他默认设置不变,如图 3.49 所示。
基础知识篇 不要输在起跑线上
·74·
(4)单击“确定”按钮,完成分类汇总设置,分类汇总结果如图 3.50 所示。
图 3.49 设置“分类汇总” 图 3.50 按“区域”字段分类汇总
2.分级显示数据
在窗口区域的行标题左侧出现了分级显示符号 。这里第 1 级代表总计,第 2
级是分类汇总的结果,第 3 级代表明细数据。单击分级符号 2 即可得到按区域汇总的数据,
如图 3.51 所示。
图 3.51 分级显示汇总数据
3.复杂的分类汇总要求
在“分类汇总”对话框中,汇总方式并非仅“求和”一种,点开“汇总方式”下拉列
表,除了默认的“求和”外,还有“计数”、“平均值”、
“ 大值”和“ 小值”等 11 种选项,如图 3.52 所示。
在分类汇总操作中,每次只能设置一个“分类字段”,
如有对多个字段分类汇总的要求,可以分批设置,但在
第 2 次及以后的操作中,需要取消“替换当前分类汇总”
的勾选。
不推荐对多个字段执行分类汇总的做法,使用数据
透视表完全可以达成分类汇总的效果,而且比使用分类汇总的方法操作简便、功能更加
强大。
示例文件:3.3.1建立分类汇总.xlsx。
图 3.52 11 种汇总方式
第 3 章 列表中的简单数据分析
·75·
3.3.2 复制分类汇总结果
如需复制分类汇总的结果到其他区域,需要先选定可见单元格后再复制。直接用
“Ctrl+C”组合键复制会连带其中的隐藏行一起。以复制图 3.51 中的分级显示结果为例,
正确的操作步骤如下:
(1)选中需要复制的分类汇总结果区域,即图 3.51 中的 A1:E191 单元格区域。
(2)按“Alt+;”组合键选中可见单元格(也可以按 F5 键在“定位条件”对话框中操
作),然后按“Ctrl+C”组合键复制。
(3)选择目标单元格,按“Ctrl+V”组合键粘贴。
3.3.3 删除分类汇总结果
如需删除已设置的分类汇总结果,可再执行一次“分类汇总”操作,在弹出的“分类
汇总”对话框中单击“全部删除”按钮即可删除已建立的分类汇总。
删除分类汇总的操作是不可逆的,无法通过“撤销”命令来恢复。