第3章
数 据 处 理





第3章
案例导读



Excel提供了强大的数据处理工具，相比其他数据处理工具而言，具有更好的易用性，也更易于学习。Excel可以作为数据处理的入门基础工具，之后逐步向数据处理专业软件过渡。本章主要介绍的数据处理工具包括导入外部数据、数据的合并与拆分整理、数据排序、数据筛选、数据分类汇总等功能。通过这些数据处理功能可以帮助用户更方便地利用Excel分析并获取重要信息，以便做出合理科学的决策。

实例31数据处理
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■



西宇公司不仅有线下门店，还有线上商城。李蕾需要对公司本年度的购销数据进行统计，按照下列要求帮助李蕾完成相关数据的整理、计算和分析工作。

在“西宇公司年销售统计表”工作表右侧插入一个名为“品名”的工作表，如图31所示，并将文本文件“品名.txt”中的数据导入“品名”工作表，并删除工作表中“商品名称”重复的记录。对工作表“西宇公司年销售统计表”中的数据进行修饰、完善。



图31西宇公司年销售统计表(局部)


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

3.1导入外部数据

Excel提供了强大的数据处理工具，但是很多时候需要处理的数据并不在Excel表中，而是在文本文件中或者需要从网页中获取，此时，就需要先将这些在外部文件中的数据首先导入Excel中，再进行相应的处理。导入外部数据的方式有很多，这里主要介绍从文本文件导入数据和从网页中导入数据两种。

3.1.1从文本文件导入数据

假设有一个名为“品名.txt”的文本文件，如图32所示，里面存放着产品信息。Excel可以从该文本文件中获取数据。根据实例31的要求，完成数据的导入。



图32“品名”文本文件


操作步骤如下。

(1) 单击“数据”→“自文本”按钮，弹出“导入文本文件”对话框。

(2) 在对话框中找到“品名.txt”所在位置，单击选中“品名.txt”后单击“导入”按钮。

(3) 在弹出的“文本导入向导第1步，共3步”对话框中，在“请选择合适的文件类型”选项区域中单击“分隔符号”，勾选“数据包含标题”复选框选项，如图33所示。单击“下一步”按钮，弹出“文本导入向导第2步，共3步”对话框。


(4) 在“文本导入向导第2步，共3步”对话框中，可设置分列数据所包含的分隔符号。勾选“分隔符号”选项区的“Tab键”复选框选项，如图34所示。单击“下一步”按钮，弹出“文本导入向导第3步，共3步”对话框。

(5) 在“文本导入导向第3步，共3步”对话框中，可根据具体的文本文件，选择对应的各列的数据格式。在“列数据格式”选项区域中选择“常规”选项，如图35所示。单击“完成”按钮，弹出“导入数据”对话框。



图33“文本导入向导第1步，共3步”对话框




图34“文本导入向导第2步，共3步”对话框




图35“文本导入向导第3步，共3步”对话框


(6) 在“导入数据”对话框中，选择“现有工作表”文本框右侧的选择数据源按钮后，单击A1单元格后再次单击选择数据源按钮，返回“导入数据”对话框，单击“确定”按钮，如图36所示。




图36“导入数据”对话框


(7) 导入的数据如图37所示。



图37导入的数据


实例32从网页导入数据
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


如今是大数据时代，数据的主要来源是网络，很多时候需要分析的数据来自网络，因此有必要学会如何从网页中导入数据并保存更新。从“财富”网站将“2022年中国500强利润率最高的40家公司”相关的表导入Excel工作表中并更新。


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

3.1.2从网页中导入数据

操作步骤如下。

(1) 单击“数据”→“自网站”按钮。

(2) 弹出“新建Web查询”对话框，在“地址”文本框中粘贴网址(请扫前言中的二维码获取)，单击右侧的“转到”按钮，对话框中将是该网址的内容。

(3) 鼠标拖动对话框右侧的进度条直到出现要导入的表格，将光标移动到表格区域，表格的左上角会出现向右的箭头“→”，旁边有“单击可选定此表”的提示语，如图38所示。


(4) 单击图38中的箭头“→”，由箭头“→”转变为勾“√”，说明此时已经选中要导入的表格，单击“导入”按钮。如图39所示。

(5) 弹出“导入数据”对话框，单击工作表中的A1单元格，单击“确定”按钮，如图310所示。



图38选择要导入的数据表




图39导入选中的表格




图310“导入数据”对话框



(6) 在工作表中会出现“正在获取数据”的提示，如图311所示。



图311正在获取数据


(7) 加载完毕后，数据将导入Excel的工作表中，表中为“2022年中国500强净利润率最高的40家公司”，如图312所示。



图312导入数据的结果


(8) 数据更新。此例的数据不存在更新的问题，但是其他数据经常出现更新的情况，比如关于股票信息的表，就需要更新。可以直接在Excel中获取最新信息，实现数据的更新，不用重新导入数据。选中工作表的数据区域后按Ctrl+Alt+F5组合键更新数据，也可以单击“数据”→“全部刷新”按钮更新数据。

此外还可以通过其他方式更新数据。例如，单击“数据”→“属性”按钮，弹出“外部数据区域属性”对话框，如图313所示。可以通过设置“刷新频率”或者“打开文件时刷新数据”等来实现数据的更新。



图313“外部数据区域属性”对话框


3.1.3删除重复项

如果数据是直接从外部导入的，或者是由多个数据源合并生成的，那么数据中可能包含重复记录，会影响数据的唯一性，可能造成数据处理中得到错误的结果，从而影响决策者的决策，因此有必要删除重复项。

下面以图37所示的数据为例，删除工作表中“商品名称”重复的记录，对于重复信息只保留最前面的一个。删除重复项的操作步骤如下。

(1) 选中“品名”工作表中的任意单元格。

(2) 单击“数据”→“删除重复值”按钮，弹出“删除重复项”对话框。

(3) 在“删除重复项”对话框中单击“取消全选”按钮，然后选择要删除的重复项的列，即勾选“商品名称”复选框，单击“确定”按钮，如图314所示。



图314“删除重复项”对话框


(4) 弹出提示框，告知用户删除了多少条包含重复项的记录，保留了多少条记录，如图315所示。



图315删除重复项提示框



实例33数据的合并
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


张一是公司的人事专员，根据公司提供的员工的数据表，如图316所示，完善数据。根据省份、城市、地址列信息，通过数据合并功能得到详细地址。



图316员工数据表


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

3.2数据的合并与拆分整理
3.2.1数据的合并处理

数据的合并是指汇总多个单元格中的数据，并在单个单元格中合并计算结果。

一般可使用以下两种方法实现数据的合并。

1. 使用连接符&组合数据

选择要放置合并后数据的单元格，输入“=”，随后单击待合并的第一个单元格，输入“&”，随后单击待合并的下一个单元格，输入“&”，以此类推，直到单击选中最后一个单元格，按Enter按钮。例如，公式为“=A2&B2”，A2单元格的内容是“1”，B2单元格的内容是“hello”，则返回“1hello”。

2. 使用CONCAT函数合并数据

CONCAT函数的语法格式为： CONCAT(text1,［text2］,…)。其中，text1是所需要的联接的文本项，可以是字符串或字符串数组，如单元格区域。［text2,…］，可选参数，要连接的其他文本项，文本项最多可以有253个文本参数，每个参数可以是一个字符串或字符串数组，如单元格区域。

例如，“=CONCAT("我","爱","中国")”将返回“我爱中国”。提示： 参数中如果是字符串，则用英文状态下的双引号引起来即可，参数与参数之间用英文状态下的逗号隔开。

使用CONCAT函数合并数据的一般操作步骤如下。

(1) 选择要放置合并后数据的单元格，输入“=CONCAT()”。

(2) 选择要合并的单元格，使用逗号分隔要合并的单元格，使用引号添加空格、逗号或其他文本。

(3) 在公式末尾添加括号，然后按Enter键。例如，公式为“=CONCAT(A2,B2)”，A2单元格的内容是字符串“China is a great country！”，B2单元格的内容是字符串“中国是一个伟大的国家！”，则返回“China is a great country！中国是一个伟大的国家！”。

实现实例33中数据合并的具体步骤如下。

(1) 在D2单元格中输入“=A2&B2&C2“，按Enter键实现数据合并，得到如图317所示的结果。



图317合并D2单元格中的数据


(2) 选中合并好的D2单元格，光标移动到单元格区域右下角，当鼠标呈黑色十字时，按住鼠标左键并向下拖动至最后一行，单击“自动填充选项”下拉按钮，在下拉菜单中选择“快速填充”，如图318所示。



图318合并数据结果



实例34数据的拆分
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


销售员给李蕾一份如图319所示的表格，请你帮助李蕾完成相关数据的整理。根据销售记录，通过数据拆分功能，获取商品名称、销售数量和销售额。



图319销售记录


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

3.2.2数据的拆分处理

数据的拆分是指将一列包含多项信息的数据按某种规则拆分至各列。

实现实例34的具体操作步骤如下。

(1) 将A2单元格中的数据手动拆分输入对应的单元格中： B2单元格中输入“Haier LED42K326X3D 42英寸智能网络3D电视”，C2单元格中输入“11台”，D2单元格中输入“3599.00元”，如图320所示。



图320手动拆分A2单元格中的数据


（2） 选中B2单元格，按住右下角的填充柄不放拖动至最后一行，单击“自动填充选项”下拉按钮，在下拉菜单中选择“快速填充”，如图321所示。



图321快速填充


（3） C和D列重复步骤②，最后拆分结果如图322所示。



图322拆分数据结果

3.3数 据 排 序

对数据进行排序是数据分析中不可缺少的组成部分，后续很多对数据的处理都需要以排序好的数据为前提，如“分类汇总”。

按方向分，排序可分为按列排序和按行排序。Excel默认是按列排序。按列排序是指根据列(字段)的值对行(记录)进行重新排序。按行排序是指根据某一行(记录)的顺序，对数据表中列(字段)的顺序进行重新排序。

作为排序依据的字段称为“关键字段”，简称“关键字”。关键字只有一个的排序称为单列排序，关键字有多个(两个或者两个以上)的排序称为多列排序。其中，第一个关键字称为“主要关键字”，第二个及之后的关键字称为“次要关键字”，Excel最多可以有64个关键字。可以通过“排序”对话框中的“添加条件”按钮来添加一个“主要关键字”(一般情况默认已有主要关键字)和若干“次要关键字”(不超过63个)。也可以通过“删除条件”按钮来删除不需要的“次要关键字”，如图323所示。



图323“排序”对话框


Excel不仅可以按单元格值的大小进行排序，还可以按字符内码的大小、汉字笔画、单元格颜色、字体颜色、条件格式图标等进行排序，如图324所示。



图324“排序”对话框的“排序依据”下拉列表


3.3.1自定义排序

有时，需要按照固定的文本顺序进行排序，如“一部、二部、三部、四部……”这样的序号，但是Excel内置的几种排序依据都无法实现，这时就可以直接自定义排序序列。

将实例31中“西宇公司2021年销售统计表”按分部进行排序，排序的规则为“总部、一部门、二部门、三部门、四部门、五部门、六部门”。排序前如图325所示。



图325西宇公司2021年销售统计表


对于Excel中没有预先定义的序列，首先需要添加自定义序列，步骤如下。

（1） 单击“文件”→“选项”→“高级”按钮，拖动滚动条，找到“编辑自定义列表”按钮，如图326所示。


(2) 单击“编辑自定义列表”按钮，弹出“自定义序列”对话框，在“输入序列”文本框中输入自定义的排序序列
“总部”“一部门”“二部门”“三部门”“四部门”“五部门”“六部门”，并用Enter键隔开，如图327所示。


(3) 单击“添加”按钮后，可以在左侧“自定义序列”选项框中找到刚添加的自定义序列，如图328所示。单击“确定”按钮，关闭对话框。


现在，Excel中已经添加了自定义序列，可以利用自定义序列进行排序了。

(1) 将光标定位在要排序的列中任意单元格，单击“数据”→“排序”按钮。

(2) 弹出“排序”对话框，在“主要关键字”下拉列表中选择“分部”，“排序依据”下拉列表中选择“单元格值”，“次选”下拉列表中选择“总部，一部门，二部门，三部门……”，如图329所示。单击“确定”按钮。



图326“编辑自定义列表”按钮




图327输入自定义序列




图328自定义序列添加成功




图329“排序”对话框



（3） 按分部排序的结果如图330所示。



图330按“分部”自定义排序的结果


3.3.2单列和多列排序
1. 排序原则

(1) 文本从A到Z排序为升序； 从Z到A排序为降序。

(2) 数字从小到大排序为升序； 从大到小排序为降序。

(3) 日期和时间从最旧到最新排序为升序； 从最新到最旧排序为降序。

(4) 自定义排序指可以按自己创建的序列(如大、中和小)或格式(包括单元格颜色、字体颜色或图标集)进行的排序。

2. 单列排序

选中要排序的列中任意单元格，在单击“开始”→“排序和筛选”按钮，下拉菜单中选择为升序，为降序。另外，还可以单击“数据”选项卡，在“排序和筛选”组中选择为升序，为降序。

如果工作表中有空行，则不会对整个工作表的数据进行排序，因此建议先删除空白行后用上述的排序方法。或者选中整个数据区域，单击“数据”→“排序”按钮，弹出“排序”对话框，根据情况对于“列”“排序依据”“次序”通过下拉菜单进行选择，如图331所示。有时，进行排序的数据是包含标题的，如果排序时发现标题也参与排序了，只需要勾选“数据包含标题行”，这样再进行排序时标题就不会参与排序了。



图331有空白行的数据进行排序


西宇公司2021年销售统计表将实例31中按销量进行升序排序，具体操作方法为： 将光标定位在要排序的列中任意单元格，单击“数据”选项卡中的“升序”按钮即可。排序效果如图332所示，通过上述方法可自动删除空白行，实现排序。



图332有空白行的排序结果


3. 多列排序(按指定条件排序)

下面以图325“西宇公司2021年销售统计表”为例，说明多列排序的具体操作步骤。

(1) 选中工作表中数据区域中任意一个单元格或者整个数据区域。

(2) 单击“数据”→“排序”按钮，弹出“排序”对话框。

(3) 在“主要关键字”下拉列表中选择“商品类别”，“排序依据”下拉列表中选择“单元格值”，“次序”下拉列表中选择“降序”。单击左上角的“添加条件”按钮，在“次要关键字”下拉列表中选择“销量”，“排序依据”下拉列表中选择“单元格值”，“次序”下拉列表中选择“升序”，如图333所示。单击“确定”按钮，关闭对话框。



图333“排序”对话框


(4) 排序后如图334所示。可以看出“西宇公司2021年销售统计表”中的记录是先按商品类别进行降序排序，对于相同的商品类别的记录，再按销量进行升序排序的。



图334多列排序


3.3.3按行排序

按行排序的实质是改变表的结构，因为按行排序是改变工作表数据的列的顺序，会影响数据之间的关系。但是Excel允许按行排序，可以解决一些特殊问题，按行排序的主要应用是可以快速地改变表格中各字段的先后顺序，即改变表的结构。常用的方法是： 在表格数据最后一行后的第一个空白行中，输入所在列在按行排序后列的次序，再将此行作为“主要关键字”进行按行排序，最后删除此行，即可以实现按行排序，交换或者改变列的顺序。

下面以如图325“西宇公司2021年销售统计表”为例，实现将“销售日期”调至第一列，其余列按原来的顺序进行排序，说明按行排序的具体操作步骤。

(1) 在最后一行数据后的第一个空白行的各列依次输入值2、3、4、5、1、6、7、8、9、10、11。

(2) 选中工作表中数据区域任意一个单元格或者整个数据区域。

(3) 单击“数据”→“排序”按钮，弹出“排序”对话框。

(4) 在“排序”对话框中单击“选项”按钮，弹出“排序选项”对话框，在“方向”选项区中选择“按行排序”，如图335所示，单击“确定”按钮，关闭“排序选项”对话框。



图335“排序选项”对话框


(5) 返回“排序”对话框，在“主要关键字”下拉列表中选择“行17”(步骤（1）中输入数据的行)，“排序依据”下拉列表中选择“单元格值”，“次序”下拉列表中选择“升序”，如图336所示，单击“确定”按钮，关闭“排序”对话框。



图336按行排序“排序”对话框


(6) 适当调整列宽，并删除第17行，即完成按行排序。排序后如图337所示。可以发现，“销售日期”移动到第一列，其余列按原来的顺序进行排序。



图337按行排序后

3.4数  据 筛 选

通过筛选工作表中的信息，可以快速找到需要的值。可以对一个或多个列数据进行筛选。使用筛选，不仅可以控制想要查看的内容，还可以控制想要排除的内容。可以基于从列表中所做的选择进行筛选，或者可以创建特定的筛选器来精确定位你想要查看的数据。用“自动筛选”来显示需要的数据并隐藏其余部分。筛选单元格或表中的数据后，可以重新应用筛选器以获得最新结果，或者清除筛选器以重新显示所有数据。使用筛选器暂时隐藏表格中的部分数据，以便查看所需数据。

可通过使用筛选器界面中的搜索框来搜索文本和数字。

在筛选数据时，如果一个或多个列中的数值不能满足筛选条件，整行数据都会隐藏起来。可以对数值或文本值进行筛选，也可以对背景或文本应用了颜色格式的单元格按颜色进行筛选。

3.4.1自动筛选
1. 进入筛选状态

以实例31“西宇公司2021年统计销售表”为例，筛选“商品类别”。操作步骤如下。

(1) 选中工作表中数据区域内任意单元格，单击“数据”→“筛选”按钮。此时，在各列标题单元格出现下拉三角按钮，单击下拉按钮，弹出可进行筛选选择的列表，如图338所示。


(2) 在列表中取消勾选“全选”复选框。此时，将取消勾选所有复选框。然后，仅选择想要查看的值，如想筛选“冰箱”，则单击“冰箱”复选框，如图339所示，然后单击“确定”按钮，关闭对话框。



图338筛选下拉列表




图339筛选“冰箱”



(3) 筛选的结果如图340所示，“商品类别”单元格下拉按钮转变为，且除了“冰箱”之外的其他类别被隐藏，说明目前该列处于筛选状态。



图340筛选结果


2. 清除筛选

如果想清除筛选，回到筛选前的状态，可以单击列标旁的下拉按钮，弹出下拉列表，如图341所示。单击“从商品类别中删除筛选器”，即可清除筛选。另外，也可以单击“全选”复选框，此时将勾选所有复选框，单击“确定”按钮，即可回到筛选前的状态。判断是否处于筛选状态可观察列标旁的图标： 下拉三角表示还未筛选 ，表示已筛选状态 。

3. 数字筛选

依据单元格中数据值的类型，Excel在列表中将显示数字筛选器或文本筛选器。数字筛选可以对范围做进一步筛选，如等于、不等于、大于、大于或等于、介于、前10项等。以实例31“西宇公司2021年统计销售表”为例，筛选“销售额”大于50万的数据行，具体操作方法如下。

(1) 选中“销售额”列中的任意单元格，单击“数据”→“筛选”按钮，“销售额”单元格旁出现下拉三角按钮，单击该下拉按钮，在弹出的下拉列表中选择“数字筛选”→“大于”选项，如图342所示。



图341清除筛选




图342数字筛选



(2) 弹出“自定义自动筛选方式”对话框，在“大于”右边的文本框中输入500000，如图343所示，单击“确定”按钮。



图343数字筛选“自定义自动筛选方式”对话框


(3) 此时，“销售额”单元格旁的图标变为，且显示的记录满足销售额大于500000，如图344所示。



图344“销售额”大于500000的记录筛选结果


4. 文本筛选

文本筛选可以对文本做进一步筛选，如等于、不等于、开头是、结尾是、包含、不包含等。以实例31“西宇公司2021年统计销售表”为例，筛选“商品代码”以AC开头的数据。因为不同的字母开头代表不同的意义，通过文本筛选可以快速地筛选出需要的信息。具体操作方法如下。

(1) 选中工作表中“商品代码”列中的任意单元格，单击“数据”→“筛选”按钮，“商品代码”单元格旁出现下拉三角按钮，单击该下拉按钮，在弹出的下拉列表中选择“文本筛选”→“开头是”选项。

(2) 弹出“自定义自动筛选方式”对话框，在“开头是”右侧文本框输入AC，如图345所示，单击“确定”按钮，关闭对话框。



图345文本筛选“自定义自动筛选方式”对话框


(3) 此时，“商品代码”单元格旁的图标变为，且显示的记录满足“商品代码”开头是AC，如图346所示。



图346商品代码的开头是AC的记录筛选结果(局部)


提示： 如果筛选条件中含字符问号“？”或星号“*”时，需要用“~？”代表“？”，用“~*”代表“*”。比如，筛选以开头是AC*的商品代码，则需要在文本框中输入AC~*。在Excel中，“？”和“*”是通配符，有特殊含义，“？”代表单个字符，“*”代表任意多个字符。

5. 创建切片器筛选

切片器是一个非常实用的筛选器。在Excel中，切片器可以根据具体条件，快速筛选出数据。除快速筛选外，切片器还可以指示当前筛选状态，以便轻松了解当前显示的内容。

Excel的切片器只能在智能表格或者数据透视表中才可以使用。智能表格就是“套用表格格式”之后的表，可以使用Ctrl+T组合键快速应用智能表格。

例如，要求对图325“西宇公司2021年销售统计表”用切片器筛选出“品牌”的字段值为“HUAWEI”的记录。操作步骤如下。

(1) 选中表格数据区域，选择“开始”→“套用表格格式”→“表样式中等深浅1”，弹出“套用表格式”对话框，单击“确定”按钮。


图347“插入切片器”对话框(局部)




(2) 选定表格中任意单元格，单击“表格工具/设计”→“插入切片器”按钮。

(3) 弹出“插入切片器”对话框，勾选“品牌”复选框，如图347所示，单击“确定”按钮，关闭对话框。


(4) 弹出品牌切片器，单击“HUAWEI”，即可在表格中筛选出“品牌”字段值为“HUAWEI”的记录，如图348所示，可以发现字段名“品牌”单元格右侧的图标变为。



图348在切片器中选择筛选值(局部)


注意： 

 若要选择多个项，可按住Ctrl键的同时选择要显示的项。

 若要清除切片器筛选器，可在切片器中单击“清除筛选器”按钮，或按Alt+C组合键。

 切片器标题指示切片器中项的类别。

 以图347为例，若要删除(移除)切片器，在切片器任意位置右击，选择“删除‘品牌’”即可(‘’中为切片器标题)。

 未选中的筛选按钮指示该项未包含在筛选器中，选中的筛选按钮指示该项包含在筛选器中。

 切片器中拥有当前可见项之外的其他项时，可使用滚动条滚动进行查看。

3.4.2高级筛选

高级筛选适用于使用复杂条件进行筛选的情况，解决自动筛选无法解决的筛选中跨列的“条件或”或者“条件或”和“条件与”的结合的问题。使用高级筛选时，除了需要有要筛选的数据源(列表区域)，还需要创建合适的“条件区域”。

“条件区域”的建立需要满足如下的条件。

 “条件区域”应当与“列表区域”隔开，至少隔一个空白行或一个空白列，也可以建立在其他工作表中。

 “条件区域”至少包含两行，第一行为列名行，即参与筛选的列标题放在第一行； 第二行放条件参数，是对该列的限定条件。根据需要也可以再增加行。

 “条件区域”中除第一行外，如果条件参数在同行，代表“且关系”(同时满足)，不在同行代表与其他行是“或关系”。例如，图349所示，第一行是列名行，代表筛选的列包含“商品类别”“部门”和“销售渠道”。第二行代表筛选满足“商品类别”为“计算机”且“部门”为“总部”且“销售渠道”为“线上商城”的记录。第三行代表筛选满足“部门”为“一部”且“销售渠道”为“线下门店”的记录。其中，第二行和第三行的条件之间是或关系(代表满足第二行的条件或第三行的条件之一即可)。

1. 创建条件区域

将筛选条件限定的列的列名复制到条件区域中指定单元格(条件区域的第一行)，列名不分先后，在列名下输入该列需要满足的筛选条件，在同一行的下一列输入下一个条件。如果还有其他条件，可以继续重复上述过程。

提示： 在条件区域，除了可使用文本和数值(工作表中固定的值)之外。还可以使用比较运算符直接与文本或数值结合，组成“关系表达式”，如图350所示的条件区域表示筛选出“商品类别”不等于“计算机”(除计算机外的其他类别)且“销售额”大于100000且“销量”大于10的记录。



图349文本条件区域




图350表达式条件区域



2. 使用高级筛选

单击“数据”→“高级”按钮，弹出“高级筛选”对话框，如图351所示。在对话框中需设置“列表区域”和“条件区域”以及结果存放方式，根据需要可以勾选“选择不重复的记录”，避免筛选结果有重复记录。



图351“高级筛选”对话框1


默认情况下，选中的是“在原有区域显示筛选结果”，如果需要将结果复制到其他位置，则需要在“方式”下方选择“将筛选结果复制到其他位置”，并在“复制到”文本框中输入或选择单元格区域。这样做可以把筛选结果与源数据进行对比，这是自动筛选功能无法实现的。

以图325“西宇公司2021年统计销售表”为数据源，使用高级筛选选出“商品类别”为“计算机”且“销量”小于20或“商品类别”为“空调”且“销售额”大于100000的记录。操作步骤如下。

（1） 在数据源的右侧(至少空一列)建立条件区域，如图352所示。

（2） 选中数据源中任意单元格，单击“数据”→“高级”按钮。弹出“高级筛选”对话框，在“列表区域”中选择数据源区域(默认已选中工作表中数据源的区域),“条件区域”文本框中选择数据源右侧的条件区域所在的单元格区域，勾选“选择不重复的记录”，如图353所示。单击“确定”按钮，关闭“高级筛选”对话框。



图352条件区域




图353设置“高级筛选”参数



（3） 完成筛选，筛选结果如图354所示。



图354高级筛选结果(局部)


3. 清除高级筛选

清除高级筛选，只需要单击“数据”→“清除”按钮，如图355所示。清除高级筛选后，工作表可恢复到筛选前的状态。



图355清除筛选

3.5数据分类汇总

数据分类汇总的定义是： 按指定的分类变量对数据进行分组，对每组记录的各变量求指定的描述统计量。分类汇总是对数据先按照某一标准进行分类，然后对各类别相关数据分别进行求和、求平均数、求个数、求最大值、求最小值等方法的汇总。所以对数据进行分类汇总前，要分析清楚三个问题： 按什么分类(即分类字段是什么)，对什么进行汇总(即选定汇总项)，用什么方式进行汇总(汇总方式是什么)。

分类字段和汇总项的选项都是数据表中的列名，分类字段值的类型一般是文本类型，汇总项的数据类型是数字类型。汇总方式有： 求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差和总体方差。

提示： 如果工作表使用了“套用表格格样式”功能，则数据区域将变成Excel表格，Excel表格无法进行分类汇总，所以要特别注意这个问题。如果目前的工作表是Excel表格，则需要转换为数据区域再进行分类汇总，可选中表格任意单元格后右击，在弹出的快捷菜单中选择“表格”→“转换为区域”。

3.5.1简单分类汇总
1. 建立分类汇总

分类汇总是基于排序后的数据，因此分类汇总前要先按分类字段所在的列进行排序，再进行分类汇总。

下面以对如图356所示的“西宇公司2021销售统计表”要求统计不同销售渠道的销量总和及销售额总和为例。



图356示例数据


操作步骤如下。

(1) 选中G2单元格，单击“数据”→“升序”按钮。

(2) 选定工作表数据区域中任一单元格。

(3) 单击“数据”→“分类汇总”按钮，弹出“分类汇总”对话框。

(4) 在该对话框中，“分类字段”下拉列表中选择“销售渠道”，“汇总方式”下拉列表中选择“求和”，“选定汇总项”选项区域中选择“销量”和“销售额”，如图357所示。单击“确定”按钮，关闭对话框。



图357设置“分类汇总”参数


(5) 汇总结果如图358所示。图358采用的第2级显示。



图358“分类汇总”结果


分类汇总的特点是源数据跟分类汇总的数据在同一个表中，方便查看。简单的分类汇总结果可以显示为3个层级。第1级只显示所有记录汇总字段的“总计”，即所有字段的统计值。第2级如图358所示，显示分类字段和“总计”的统计值。第3级在显示分类字段和“总计”的统计值的同时，还有源数据明细。可以单击分级显示符号(图358左上角的“1”“2”“3”按钮)查看各级数据，也可以单击下方的“+”进行展开和“-”进行折叠数据。

2. 清除分类汇总

如果想清除分类汇总结果，恢复到分类汇总之前的源数据，可以单击“数据”→“分类汇总”按钮，弹出“分类汇总”对话框，在对话框中单击“全部删除”按钮，即可清除分类汇总结果。

3.5.2多级分类汇总

多级分类汇总也称为分类汇总的嵌套，只是在原来一级分类汇总的基础上，再进行一个分类汇总，按照这样的操作，可以进行两次或以上的操作。用户按照工作表中的一个字段进行分类是1级分类，如果想按照两个及以上字段进行分类，只需要创建多级分类汇总，分类汇总中多个分类字段之间的关系为： 下级分类字段从属于上级分类字段。

下面以对如图356所示的“西宇公司2021销售统计表”要求分销售渠道及产品类别对销量总和进行统计为例，来说明多级分类汇总的使用。操作步骤如下。



图359设置多级“分类汇总”参数

(1) 用自定义排序以“销售渠道”字段作为“主要关键字”进行升序(或降序)排序，以“产品类别”字段作为“次要关键字”进行升序(或降序)排序。


(2) 选定工作表数据区域中任一单元格。

(3) 完成1级分类汇总。单击“数据”→“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“销售渠道”，“汇总方式”下拉列表中选择“求和”，“选定汇总项”列表框中选择“销量”。单击“确定”按钮，关闭对话框。

(4) 在完成1级分类汇总的基础上，继续进行2级分类汇总。单击“数据”→“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“产品类别”，“汇总方式”下拉列表中选择“求和”，“选定汇总项”列表框中选择“销量”，并取消勾选“替换当前分类汇总”复选框，如图359所示，单击“确定”按钮，关闭对话框。


(5) 分类汇总的结果如图360所示，图360采用的第3级显示。



图360多级“分类汇总”结果


多级分类汇总的结果分4个层级，可以点开分级显示符号(图360左上角的“1”“2”“3”“4”按钮)查看各级数据，也可以单击下方的“+”进行展开和“-”进行折叠数据。

提示： 如果需要对分类汇总的结果进行复制再作图，不能直接对分类汇总的结果进行复制粘贴，因为会把表格中隐藏的源数据一并复制过去。因此需要先选中分类汇总结果所在的单元格区域，单击“开始”→“查找和选择”→“定位条件”，弹出“定位条件”对话框，单击选中“可见单元格”，单击“确定”按钮，关闭对话框。然后再进行复制粘贴就不会复制隐藏的单元格了。

3.6数据处理综合案例
3.6.1成绩单数据整理分析

小王是一位大学生助教，在学院教务处负责大二计算机专业学生的成绩管理。现在，第二学期期末考试刚刚结束，小王将大二计算机专业三个班的成绩均录入了文件名为“第二学期成绩单.xlsx”的Excel工作簿文档中，如图361所示。



图361“第二学期成绩单”(数据源)


根据下列要求帮助小王老师对该成绩单进行整理和分析。

(1) 对工作表“第二学成绩单”中的数据列表设置格式： 将所有成绩列设为保留两位小数的数值； 适当加大行高和列宽，改变字体、字号，设置对齐方式，增加适当的边框和底纹使工作表更加美观。

(2) 利用“条件格式”功能进行下列设置： 将“大学语文”“计算机组成原理”“操作系统原理”三门课程中不低于90分的成绩所在的单元格以一种颜色填充，其他四科中低于60分的成绩以红色文本标出。

(3) 利用sum和average函数计算每一个学生的总分及平均成绩。

(4) 学号第9位代表学生所在的班级，如“12020210305”代表2021级3班5号。请通过函数提取每个学生所在的班级并按表31所示的对应关系填写在“班级”列中。


表31学号与班级对应关系表


学号的第9位对应班级


1计科1班

2计科2班

3计科3班


(5) 复制工作表“第二学期成绩单”，将副本放置到原表之后； 改变该副本表标签的颜色，并重命名为“成绩分类汇总”。

(6) 通过分类汇总功能求出各个班各科的平均成绩，并将每组结果分页显示。

(7) 以分类汇总结果为基础，创建一个三维簇状柱形图，对每个班各科平均成绩进行比较，并将该图表放置在一个名为“每个班各科平均成绩柱状分析图”新工作表中。

具体分析操作步骤如下。

(1) 打开“第二学期成绩单.xlsx”工作簿，选中所有成绩列后右击，在弹出的下拉列表中选择“设置单元格格式”，弹出“设置单元格格式”对话框，在“数字”选项卡下选择“数值”后将小数位数设置为“2”，单击“确定”按钮，关闭“设置单元格格式”对话框。

选中整个工作表，将光标放在列之间拖动可调整列宽，同理调整行高。

选中表格，在“开始”选项卡的“字体”组中设置字体为“微软雅黑”、字号为“14”，单击“边框”下拉按钮，在下拉列表中选择“所有框线”，在“对齐方式”组中单击“居中”按钮。

选中标题，单击“开始”→“填充颜色”，在弹出的下拉菜单中选择任意颜色作为底纹。效果如图362所示。



图362“第二学期成绩单”设置格式后


(2) 选中D2:F19单元格区域，单击“开始”→“条件格式”→“突出显示单元格规则”→“其他规则”，弹出“新建格式规则”对话框，选择“单元格值”为“大于或等于”，并在文本框中输入“90”，单击“格式”按钮，弹出“设置单元格”对话框，在“填充”选项卡中选择任意填充色，单击“确定”按钮返回“新建格式规则”对话框，单击“确定”按钮，关闭对话框。

选中G2:J19单元格区域，单击“开始”→“条件格式”→“突出显示单元格规”→“小于”，在弹出的“小于”对话框的文本框中输入“60”，在“设置为”下拉列表中选择“红色文本”，单击“确定”按钮，关闭对话框。

(3) 选中K2单元格，输入公式“=SUM(D2:J2)”，选中L2单元格，输入公式“=AVERAGE(D2:J2)”，选中“L2”和“K2”两个单元格，将光标放在右下角的填充柄上后双击，实现自动填充。

(4) 选中C2单元格，输入公式“="计科"&MID(A2,9,1)&"班"”后按Enter键，将光标放在右下角的填充柄上双击，实现自动填充，如图363所示。



图363“第二学期成绩单”使用条件格式后




图364设置成绩“分类汇总”参数


(5) 按住Ctrl键不放，选中并往后拖动“第二学期成绩单”工作表，双击该表重命名为“成绩分类汇总”，按Enter键完成编辑，再右击“工作表表签”，选择任意颜色。

(6) 将光标定位在“班级”列下方的任意有数据的单元格，单击“开始”→“排序和筛选”→“升序”，将数据区域按班级列升序排列。

单击“数据”→“分类汇总”按钮，在弹出的“分类汇总”对话框中，“分类字段”下拉列表中选择“班级”，“汇总方式”下拉列表中选择“平均值”，“选定汇总项”选项框中勾选“大学语文”“计算机组成原理”“操作系统原理”“代数与逻辑”“数据分析语言程序设计”“局域网及组网技术”“计算机网络课程设计”复选框，再勾选“每组数据分页”复选框，如图364所示，单击“确定”按钮，关闭“分类汇总”对话框。


(7) 在“成绩分类汇总”工作表中选择分类汇总为2级，如图365所示。选中每个班各科平均成绩以及第1行标题行的相关标题单元格，单击“插入”→“插入柱形图或条形图”→“三维簇状柱形图”，图表被创建到了本工作表中。


单击“图表工具/设计”→“切换/列”。

单击“图表工具/设计”→“移动图表”按钮。弹
出“移动图标”对话框，选择“新工作表”，在右侧文

本框中输入“每个班各科平均成绩柱状分析图”，如图366所示，单击“确定”按钮，关闭“移动图表”对话框。


拖动该工作表放置到第3个工作表的位置，双击“图表标题”，重命名为“每个班各科平均成绩柱状分析图”，如图367所示。



图365“成绩分类汇总”2级显示




图366“移动图表”对话框




图367“每个班各科平均成绩柱状分析图”


3.6.2工资表的处理和计算

小张是西宇公司的会计，利用自己所学的办公软件进行记账管理，为节省时间，同时又确保记账的准确性，她使用Excel 2016编制了“西宇公司2021年度员工工资表.xlsx”，如图368所示，根据下列要求帮助小张对该工资表进行整理和分析(提示： 本题中若出现排序问题则采用升序方式)。



图368“西宇公司2021年度员工工资表”


(1) 将“基础工资”往右各列设置为会计专用格式、保留2位小数、无货币符号； 调整表格各列宽度、对齐方式，使表格更加美观； 设置纸张大小为A4、横向，整个工作表需调整在一个打印页内。

(2) 参考年度工资薪金所得税率表，如图369所示，利用IF函数计算“应纳个人所得税税额”列。(提示： 应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数； 应纳税所得额=年度收入额-准予扣除额； 准予扣除额=基本扣除费用60000元+专项扣除+专项附加除+依法确定的其他扣除。另外，如果应纳税所得额为负数，则应纳个人所得税税额=0。)



图369年度工资薪金所得税率表


(3) 利用公式计算“实发工资”列数据，公式为： 实发工资=应付工资合计-扣除社保-应纳个人所得税税额。利用“条件格式”功能进行下列设置： 将“实发工资”中高于100000所在的单元格以一种颜色填充。

(4) 复制工作表“西宇公司2021年度员工工资表”，将副本放置到原表的右侧，并重命名为“分类汇总”。在“分类汇总”工作表中通过分类汇总功能求出各部门“应纳个人所得税税额”“实发工资”之和，每组数据不分页。

具体分析操作步骤如下。

(1) 打开“西宇公司2021年度员工工资表.xlsx”工作表，选中E3:O17单元格区域后右击，在快捷菜单中选择“设置单元格格式”，弹出“设置单元格格式”对话框，在“数字”选项卡的“分类”选项区中选择“会计专用”，“小数位数”文本框中输入“2”，“货币符号”下拉列表中选择“无”，单击“确定”按钮。

注意： 如果单元格中的内容变为“###”，说明列宽较小，可拖动各列字母标号(A、B、C…)之间的分隔线适当调整列宽。


适当调整列宽，选择适应的对齐方式，如居中。

在“西宇公司2021年度员工工资表.xlsx”工作表中，单击“页面布局”→“纸张大小”→“A4”。

单击“页面布局”→“纸张方向”→“横向”。

单击“页面布局”选项卡“调整为合适大小”组右下角的对话框启动器，弹出“页面设置”对话框，在“页面”选项卡的“缩放”选项区中选择“调整为1页宽1页高”，单击“确定”按钮，关闭“页面设置”对话框。

(2) 在M3单元格中输入公式“=K3-L3”，将光标放在M3单元格右下角的填充柄上双击，完成自动填充。

在N3单元格中输入公式“=IF(M3<0,0,IF(M3<=36000,M3*0.03-0,IF(M3<=144000,M3*0.1-2520,IF(M3<=300000,M3*0.2-16920,IF(M3<=420000,M3*0.25-31920,IF(M3<=660000,M3*0.3-52920,IF(M3<=960000,M3*0.35-85920,M3*0.45-181920)))))))”后按Enter键，将光标放在N3单元格右下角的填充柄上双击，完成自动填充。

(3) 在O3单元格中输入公式“=I3-J3-N3”，将光标放在O3单元格右下角的填充柄上双击，完成自动填充。

选中O3:O17单元格区域，单击“开始”→“条件格式”→“突出显示单元格规则”→“大于”，在弹出的“大于”对话框中文本框内输入“100000”、“设置为”下拉列表中选择“黄填充深色黄色文本”，单击“确定”按钮，关闭对话框。

(4) 按住Ctrl键，单击选中并向后拖动“西宇公司2021年度员工工资表”工作表，双击该表重命名为“分类汇总”，按Enter键完成编辑。

将光标定位在“部门”列下方任意有数据的单元格，单击“开始”→“排序和筛选”→“升序”按钮，将数据区域按部门名称升序排列。

单击“数据”→“分类汇总”按钮，在弹出的“分类汇总”对话框中，“分类字段”下拉列表中选择“部门”，“汇总方式”下拉列表中选择“求和”，“选定汇总项”选项区域中勾选“应纳个人所得税税额”“实发工资”复选框，在对话框下方勾选“替换当前分类汇总”和“汇总结果显示在数据下方”复选框，单击“确定”按钮，关闭“分类汇总”对话框。

分类汇总结果如图370所示(分类汇总选择3级)。



图370“工资表分类汇总”结果3级显示


3.6.3数据的汇总分析

朱喆是海尔集团江苏分公司的战略规划人员，正在参与制订2022年度的生产与营销计划。为此，他需要对2021年度中三款热销产品的销售情况进行汇总和分析，从中提取出有价值的信息。根据下列要求，帮助朱喆运用已有的原始数据完成上述分析工作。

(1) 在工作表Sheet1中，从A3单元格开始，导入“销售记录.txt”中的数据，并将工作表名称修改为“2021年销售记录”。

(2) 在“2021年销售记录”工作表的A1单元格中输入文字“2021年销售数据”，并使其显示在A1:F1单元格区域的正中间(注意： 不要合并上述单元格区域，可使用跨列居中)； 适当调整字体大小和样式，使其美观大方，隐藏第2行； 将B列(日期)中数据的数字格式修改为如“20120314”的格式。



图371产品价格


(3) 在“2021年销售记录”工作表的E3和F3单元格中，分别输入文字“零售价”和“销售额”； 在“2021年销售记录”工作表的E4:E366中，应用VLOOKUP函数输入C列(产品类型)所对应的零售价，价格信息如图371所示； 然后将填入的零售价设为货币格式，并保留零位小数。


(4) 在“2021年销售记录”工作表的F4:F366中，计算每笔订单记录的销售额，并应用货币格式，不保留小数，计算公式为： 销售额=零售价×数量。将“2021年销售记录”工作表的A3:F366单元格区域中所有数据垂直居中对齐。对标题行区域A3:F3应用单元格的上框线和下框线，对数据区域最后一行A366:F366应用单元格的下框线，其他单元格无边框线，不显示工作表的网格线。

(5) 复制工作表“2021年销售记录”，将副本放置到原表的右侧，并重命名为“切片器筛选”，套用任意单元格样式，使用切片器筛选出“产品类型”字段值为“冰箱”的记录。

(6) 在“切片器筛选”工作表的右侧名为“数据透视表”新工作表中自A3单元格开始创建数据透视表，按照月份和季度对“2021年销售记录”工作表中的三种产品的销售数量进行汇总； 在数据透视表右侧创建数据透视图，图表类型为“带数据标记的折线图”，并为“冰箱”系列添加线性趋势线，显示“公式”和“R2值”，数据透视表和数据透视图的样式如图372所示。



图372“2021年销售记录”的数据透视表和数据透视图


(7) 复制“2021年销售记录”工作表到“数据透视表”工作表右侧，并重命名为“分类汇总”，通过分类汇总功能求出各产品“数量”“销售额”之和，每组数据不分页。

(8) 在“分类汇总”工作表右侧创建一个新的工作表，命名为“大额订单”； 在这个工作表中使用高级筛选功能，筛选出“2021年销售记录”工作表中“冰箱”数量在1280以上、“电视”数量在1500以上以及“空调”数量在1500以上的记录(请将条件区域放置在1~4行，筛选结果放置在从A6单元格开始的区域)。

具体分析操作步骤如下。

(1) 单击Sheet1工作表，选中A3单元格，在“数据”→“自文本”，在弹出的“导入文本文件”对话框中选择“销售记录.txt”，单击“导入”按钮，弹出“文本导入向导第1步，共3步”对话框，保持默认设置，单击“下一步”按钮，弹出“文本导入向导第2步，共3步”对话框，单击“下一步”按钮，弹出“文本导入向导第3步，共3步”对话框，在“列数据格式”选项区域中选择“日期”，单击“完成”，返回“导入数据”对话框，单击“确定”按钮。双击Sheet1，重命名为“2021年销售记录”。

(2) 选中“2021年销售记录”工作表的A1单元格，输入“2021年销售数据”，选中A1:F1单元格后右击，在弹出的快捷菜单中选择“设置单元格格式”，弹出“设置单元格格式”对话框，在“对齐”选项卡的“水平对齐”下拉列表中选择“跨列居中”，单击“确定”按钮。选中A1单元格中的“2021年销售数据”，在“开始”选项卡的“字体”组适当调整字体大小和样式。选中第2行后右击，在弹出的快捷菜单中选择“隐藏”。

使用Ctrl+Shift+↓组合键选中B4:B366单元格区域后右击，在弹出的快捷菜单中选择“设置单元格格式”，弹出“设置单元格格式”对话框，在“数字”选项卡中选择“日期”，“类型”中选择“20120314”，单击“确定”按钮。

(3) 在“2021年销售记录”工作表的E3单元格中输入“零售价”，F3单元格中输入“销售额”。

从A369单元格开始创建如图370所示的产品价格。在E4单元格中输入公式“=VLOOKUP(C4,$A$369:$B$372,2,FALSE)”(其中，“$A$369:$B$372”是对刚创建的数据单元格的绝对引用)，按Enter键完成E4单元格值的计算。将光标放到E4单元格右下角的填充柄上双击，完成自动填充。

选中E列后右击，选择“设置单元格格式”，弹出“设置单元格格式”对话框，在“数字”选项卡中选择“货币”，“小数位数”文本框中输入“0”，“货币符号”选择“￥”，单击“确定”按钮。

在“视图”选项卡的“显示”组中，取消勾选“网格线”复选框。

(4) 选中“2021年销售记录”工作表的F4单元格，输入公式“=D4*E4”，按Enter键，完成F4单元格值计算，将光标放到F4单元格右下角的填充柄上双击，完成自动填充。

选中E列，在“开始”选项卡的“剪贴板”组中，单击“格式刷”按钮，再单击选中F列。

选中A3:F366单元格区域，在“开始”选项卡的“对齐方式”组中，单击“垂直居中”按钮。

选中A3:F3单元格，单击“开始”→“框线”→“上框线”，同理选择“下框线”。选中A366:F366单元格，单击“开始”→“框线”→“下框线”按钮。

(5) 选中“2021年销售记录”工作表标签，按住Ctrl键不放，单击选中并拖曳至“2021年销售记录”工作表右侧，双击该表重命名为“切片器筛选”，按Enter键完成编辑。

选中A3:F366单元格区域，单击“开始”→“套用表格格式”按钮，在下拉列表中选择任意一个样式，弹出“套用表格式”对话框，勾选“表包含标题”复选框，单击“确定”按钮，关闭“套用表格式”对话框。

选中表格中任意单元格，单击“插入”→“切片器”按钮，弹出“插入切片器”对话框，勾选“产品类型”复选框，单击“确定”按钮，弹出“产品类型”切片器，用鼠标单击列表中的“冰箱”，即可显示所有“产品类型”为“冰箱”的记录，如图373所示。



图373产品类型“切片器”(局部)


(6) 选中“2021年销售记录表”中任意有数据的单元格，单击“插入”→“数据透视表”按钮，弹出“创建数据透视表”对话框，在“选择放置数据透视表的位置”区域中选择“新工作表”，单击“确定”按钮，关闭“创建数据透视表”对话框。

双击新工作表标签，重命名为“数据透视表”，按住“数据透视表”工作表的工作表标签不放，向右拖曳到“切片器筛选”工作表的右侧。

在“数据透视表”工作表右侧的“数据透视表字段”任务窗格中，拖动“销售日期”字段到“行”标签区域、“产品类型”字段到“列”标签区域、“数量”字段到“值”数值区域。

右击A5单元格，在弹出的快捷菜单中选择“组合”，弹出“组合”对话框，“步长”选择“季度”和“月”，单击“确定”按钮，关闭“组合”对话框。

选中数据透视表的任意一个单元格，在“数据透视表工具/分析”→“数据透视图”按钮，弹出“插入图表”对话框，在“所有图表”选项卡中，选择“折线图”→“带数据标记的折线图”，单击“确定”按钮，关闭“插入图表”对话框。调整图表的大小和位置，使其位于数据透视表的右侧。

单击插入的折线图，选中代表“冰箱”的折线后右击，在弹出的快捷菜单中选择“添加趋势线”命令，在右侧的“设置趋势线格式”任务窗格中，在“趋势线选项”区域中选择“线性”，勾选“显示公式”和“显示R平方值”复选框。关闭“设置趋势线格式”窗格，单击折线图右上角的“图表元素”按钮，在弹出的“图表元素”选项区的“图例”级联菜单中选择“底部”。

(7) 选中工作表“2021年销售记录”工作表标签，按住Ctrl键不放，选中并拖曳至“数据透视表”工作表右侧，双击该表重命名为“分类汇总”，按Enter键完成编辑。

选中“分类汇总”工作表中字段名为“产品类型”下任一有数据的单元格，单击“数据”→“升序”按钮，完成对字段“产品类型”进行升序排序。

选中“分类汇总”工作表中任意有数据的单元格，单击“数据”→“分类汇总”按钮，在弹出的“分类汇总”对话框中，“分类字段”下拉列表中选择“产品类型”,“汇总方式”下拉列表中选择“求和”，“选定汇总项”中勾选“数量”“销售额”复选框，但不勾选其他复选框，在对话框下方勾选“替换当前分类汇总”和“汇总结果显示在数据下方”复选框，但不勾选“每组数据分页”复选框，单击“确定”按钮，关闭“分类汇总”对话框。

分类汇总结果如图374所示(分类汇总为2级)。



图374“2021年销售数据”分类汇总




图375大额订单条件区域

(8) 单击“分类汇总”工作表的工作表标签右侧的，新建工作表，双击该表重命名为“大额订单”，在A1:B4单元格区域创建如图375所示的“条件区域”。


选中“分类汇总”工作表的A6单元格，单击“数据”→“高级”按钮，弹出“高级筛选”对话框，在“方式”选项区中选择“将筛选结果复制到其他位置”； “列表区域”选择“2021年销售记录”工作表的A3:F366单元格区域(选中A3单元格按Ctrl+A组合键)，也可以输入“2021年销售记录'!$A$3:$F$366”； “条件区域”选择“大额订单”工作表的A1:B4单元格区域，也可以输入“大额订单!Criteria”； “复制到”选择“大额订单”工作表A6单元格(A6是起始位置，只需要填入起始位置)，也可以输入“大额订单!$A$6”。单击“确定”按钮，关闭“高级筛选”对话框。

高级筛选的结果如图376所示。



图376“大额订单高级筛选结果”(局部)

3.7习题

在“销售记录.txt”中有如图377所示的信息，该信息是某公司在2022年2~5月的销售记录，按要求完成操作。



图3772022年2~5月销售记录


(1) 从“销售记录.txt”导入excel文件中，并保存为“销售记录.xlsx”。

(2) 按照以下品牌顺序对数据排序： 华为HUAWEI、华硕、小米。

(3) 用高级筛选筛选出“华为HUAWEI”品牌“单价”超过20000或“小米”品牌“数量”超过20或“华硕”品牌“金额”超过“200000”的所有记录。

(4) 统计每个品牌的销售总额，分页保存汇总结果。

(5) 统计每个销售人员的销售数量总和和销售总额。