第7章-轻松管理Excel数据课件.pptx

1、7.1 处理“平面设计师提成统计表”数据7.2 处理“楼盘销售记录表”数据 目录/Contents轻松管理Excel数据第2部分 Excel 应用/本章导读 Excel 2010 不仅具有强大的计算功能，还拥有强大的数据管理功能。当表格中数据繁多时，可以使用 Excel 对其进行排序，将数据依次排列；如果只需查看表格中的某些数据，则可通过筛选功能筛选有用记录；而汇总功能可将某类记录汇总到一起，更利于对整体数据的查看、对比以及分析；同时还可突出显示重要的数据内容。本章将主要介绍管理数据的常用手段，包括数据排序、数据筛选、数据汇总、数据验证、数据第7章 轻松管理 Excel 数据4提成统计表是对公

2、司业务员的业绩提成情况的统计，业务提成一般通过业绩签单乘以提成率获得，由于业务员的职务不同，其业绩签单和提成率也不同，在管理数据时，就需要利用 Excel 的数据排序、数据筛选功能对数据大小进行依次排列，或筛选出需要查看的数据，以便快速分析数据。本节“平面设计师提成统计表”包含 5 月提成统计和 2015 年度提成统计两部分，是针对广告行业进行的数据管理。7.1 处理“平面设计师提成统计表”数据7.1.1 数据排序数据排序是较为基本的管理方法，可将表格中杂乱的数据按一定的条件进行排序，如在提成表中按提成额进行排序，在销售表中按销售额的高低进行排序等，以便更加直观地查看、理解数据并快速查找需要的

3、数据。排序有 5 种方式：简单排序、按关键字排序、自定义排序、按行排序和按字符数量进行排序。1简单排序简单排序是数据排序管理中最基本的一种排序，选择该方式，系统将自动对数据进行识别并排序，包括升序和降序两种方式。下面在“平面设计师提成统计表”工作簿中，使用简单排序将签单总金额进行降序排列，然后将提成率按升序排列，其具体操作步骤如下。STEP 1 降序排列1 打开“平面设计师提成统计表”工作簿，选择要进行排序的“签单总金额”列中的任意单元格；2 在【数据】/【排序和筛选】组中，单击“降序”按钮。STEP 2 降序效果此时，所选单元格所在的“签单总金额”列将自动按照“降序”方式进行排列。STEP

4、3 升序排序1 选择“提成率”列中的任意单元格，单击鼠标右键；2 在弹出的快捷菜单中选择“排序”命令；3 在弹出的子菜单中选择“升序”子命令。STEP 4 升序效果此时，“提成率”数据列内容将自动按照“升序”方式进行排列。2按关键字排序按关键字排序通常将该方式分为按单个关键字排序与按多个关键字排序，主要通过“排序”对话框实现。按单个关键字排序，只需在“排序”对话框中指定排序的列单元格内容；按多个关键字排序主要针对简单排序后仍然有相同数据的情况。下面在“平面设计师提成统计表”工作簿中按关键字排序相关数据，其具体操作步骤如下。4STEP 1 打开“排序”对话框打开“平面设计师提成统计表”工作簿，在

5、【数据】/【排序和筛选】组中，单击“排序”按钮。STEP 2 设置关键字与排列方式1 打开“排序”对话框，在“主要关键字”下拉列表中选择“提成率”选项；2 在“次序”下拉列表中选择“降序”选项；3 单击“确定”按钮。STEP 3 查看排序结果此时，“提成率”列将按照“降序”方式进行排列。STEP 4 设置多个关键字排序1 继续打开“排序”对话框，单击“添加条件”按钮；2 在自动添加的“次要关键字”下拉列表中选择“获得的提成”选项；3 在“次序”下拉列表中选择“升序”选项；4 单击“确定”按钮。STEP 5 查看排序结果此时，当“提成率”相同时，“获得的提成”列中的数据将按照“升序”方式进行排列

6、。3自定义排序Excel 中的“降序”和“升序”排列方式虽然可满足多数需要，但对于一些有特殊要求的排序则需进行自定义设置，如按照“职务”“部门”等进行排序。下面在“平面设计师提成统计表”工作簿中设置自定义排序，按照职务大小进行排列，其具体操作步骤如下。STEP 1 自定义序列1 打开“平面设计师提成统计表”工作簿，在【数据】/【排序和筛选】组中单击“排序”按钮，打开“排序”对话框，在“主要关键字”下拉列表中选择“职务”选项；2 在“次序”下拉列表中选择“自定义序列”选项。STEP 2 设置自定义序列内容1 打开“自定义序列”对话框，在“自定义序列”选项卡的“输入序列”文本框中输入自定义的新序列

7、“设计师，资深设计师，专家设计师”；2 单击“添加”按钮；3 单击“确定”按钮。STEP 3 确认排序返回“排序”对话框，在“次序”下拉列表中即可看到自定义的排序方式，单击“确定”按钮。STEP 4 查看排序结果返回工作表，便可查看到按照职务大小进行排序的效果。4按行排序在 Excel 中默认的排序方式是按列排列，而某些场合需要对数据按行排序，此时可通过“排序选项”对话框设置按行排序。下面在“平面设计师提成统计表”工作簿中新建“2015 年度提成统计”工作表，然后将武侯区业绩提成以升序排列，其具体操作步骤如下。STEP 1 选择排序区域打开“平面设计师提成统计表”工作簿，新建一个“2015 年

8、度提成统计”工作表，输入数据并设置其格式，选择需要排序的单元格区域 A2:E10。STEP 2 按行排列1 在【数据】/【排序和筛选】组中，单击“排序”按钮，打开“排序”对话框，单击“选项”按钮；2 打开“排序选项”对话框，单击选中“按行排序”单选按钮；3 单击“确定”按钮。TEP 3 降序排列1 返回“排序”对话框，左上角的“列”栏变为了“行”栏，在下方的“主要关键字”下拉列表中选择武侯区所在的行“行 3”选项；2 在“次序”下拉列表中选择“降序”选项；3 单击“确定”按钮。STEP 4 排序效果返回工作表查看武侯区的提成额按行进行排序的效果，从中可看出第四季度的提成额最高。5按字符数量排序

9、日常习惯中，为了满足观看习惯，在对文本排序时，都是由较少文本开始依次向字符数量多的文本内容进行排列，如“姓名”“产品名称”等。下面在“平面设计师提成统计表”工作簿的“5 月提成统计”工作表中将姓名由 2 个字到 3 个字进行排列，其具体操作步骤如下。STEP 1 计算数据字符数量打开“平面设计师提成统计表”工作簿，在 I3 单元格中输入函数“=LEN（B3）”，按【Enter】键，拖动鼠标复制到 I19 单元格，返回所有员工姓名所包含的字符数量。STEP 2 按字符数量升序排列1 选择字符数量列中的单元格;2 在【数据】/【排序和筛选】组中单击“升序”按钮。STEP 3 查看排序结果此时，按照

10、员工姓名字符数量进行升序排列，完成后可将统计字符列删除。7.1.2 数据筛选在工作中，有时需要从数据繁多的工作簿中查找符合某一个或某几个条件的数据，这时可使用 Excel 的筛选功能，只需显示满足条件的数据，可暂时隐藏电子表格中不符合条件的数据信息。筛选功能主要有自动筛选、自定义筛选和高级筛选 3 种方式。1自动筛选自动筛选是数据筛选方法中最简单、最常用的一种，主要通过筛选命令或筛选器进行。使用自动筛选功能能够快速地查找到表格中的 10 个最大值、高于平均值或低于平均值等条件的数据。下面在“平面设计师提成统计表”工作簿中利用自动筛选功能筛选提成额低于平均值的选项，以及筛选出“曹仁孟”和“秦东”

11、的提成额，其具体操作步骤如下。STEP 1 开始筛选打开“平面设计师提成统计表”工作簿，在表格数据区域选择任意单元格，在【数据】/【排序和筛选】组中单击“筛选”按钮。STEP 2 筛选低于平均值选项1 单击“获得的提成”单元格旁边的下拉按钮；2 在打开的下拉列表中选择“数字筛选”选项；3 在打开的子列表中选择“低于平均值”选项。STEP 3 显示低于平均值的选项返回工作表，在表格中将筛选出低于平均值的提成额选项。STEP 4 筛选员工姓名1 单击“姓名”单元格旁边的下拉按钮；2 在打开的下拉列表的筛选器中单击选中“曹仁孟”和“秦东”复选框；3 单击“确定”按钮。STEP 5 显示筛选出的员工返

12、回工作表，此时将筛选出“曹仁孟”和“秦东”两个员工的签单业绩提成。2自定义筛选如果自动筛选方式不能满足需要，则可自定义筛选条件，即根据用户的自定义设置筛选数据。下面在“平面设计师提成统计表”工作簿中首先筛选签单总金额介于 30000到 100000 的设计师，然后在此基础上筛选提成额大于 2000 的设计师，其具体操作步骤如下。STEP 1 执行自定义命令1 打开“平面设计师提成统计表”工作簿，启动筛选功能，单击“签单总金额”单元格旁的下拉按钮；2 在打开的下拉列表中选择“数字筛选”选项；3 在打开的子列表中选择“介于”选项。STEP 2 设置介于筛选条件1 打开“自定义自动筛选方式”对话框，

13、在“大于或等于”右侧的文本框中输入“30000”；2 在“小于或等于”右侧的文本框中输入“100000”；3 单击“确定”按钮。STEP 3 查看筛选结果返回工作表，此时可查看到签单总金额介于 30000到 100000 的设计师信息。STEP 4 筛选提成额大于 2000 的数据1 单击“获得的提成”单元格旁的下拉按钮，在打开的下拉列表中选择“数字筛选”子列表中的“大于”选项，打开“自定义自动筛选方式”对话框，在“大于”右侧的文本框中输入“2000”；2 单击“确定”按钮。STEP 5 最终效果返回工作表，查看签单总金额介于 30000 到 100000基础上的提成额大于 2000 的设计师

14、。3高级筛选通过高级筛选可以筛选出同时满足两个或两个以上条件的记录，同时可将筛选出的结果输出到指定的位置。下面对“平面设计师提成统计表”工作簿进行高级筛选，筛选出满足签单总金额大于 50000、提成率等于 3%、提成额大于 2500 这 3 个条件的记录，其具体操作步骤如下。STEP 1 输入筛选条件打 开“平 面 设 计 师 提 成 统 计 表”工 作 簿，在B21:D22 单元格区域中输入筛选条件。STEP 2 筛选数据1 在【数据】/【排序和筛选】组中单击“高级”按钮，在打开的“高级筛选”对话框的“列表区域”文本框中输入需要被筛选的区域“A2:H19”；2 在“条件区域”文本框中输入设定

15、的条件“B21:D22”；3 单击“确定”按钮。STEP 3 查看筛选结果返回工作表，可查看到按照筛选条件筛选出的结果。4取消筛选如果要将未筛选出的项目显示出来，可取消筛选，其操作很简单，可通过“筛选”按钮、筛选器和“清除”按钮 3 种方式实现。下面在“平面设计师提成统计表”工作簿中使用不同的方法练习取消筛选的操作，显示出所有数据，其具体操作步骤如下。STEP 1 单击“筛选”按钮取消打开“平面设计师提成统计表”工作簿，在【数据】/【排序和筛选】组中单击“筛选”按钮。STEP 2 显示所有数据此时即可取消筛选，在工作表中重新显示出所有数据项目。STEP 3 使用筛选器取消1 按【Ctrl+Z】

16、组合键，返回筛选状态，在筛选过的字段旁边单击下拉按钮；2 在打开的下拉列表中的筛选器中单击选中“全选”复选框；3 单击“确定”按钮将隐藏的记录显示出来。STEP 4 使用“清除”按钮取消按【Ctrl+Z】组合键，返回筛选状态，选择【数据】/【排序和筛选】组，单击“清除”按钮，将隐藏的记录显示出来。7.2 处理“楼盘销售记录表”数据“楼盘销售记录表”用于公司当前销售状况的参考，一般包括开发公司名称、楼盘位置、开盘价格以及销售状况等信息，通过数据处理，从中突出显示所需数据，包括某项数据的汇总，如已售出楼盘的数量，使用数据工具保证数值的大小输入正确，以及对表格数据设置条件格式，用特殊颜色或图标来显示

17、销售记录表中的重点内容。7.2.1 将数据分类汇总Excel 的数据分类汇总功能是用于将性质相同的数据汇总到一块，根据表格中的某一列数据将所有记录进行分类，然后再对每一类记录分别进行汇总，以达到使工作表的结构更清晰的目的，使用户能更好地掌握表格中重要的信息。1单项分类汇总创建单项分类汇总，首先需要对数据进行排序，然后通过“分类汇总”对话框实现。分类汇总以某一列字段为分类项目，然后对表格中其他数据列中的数据进行汇总，如求和、求平均值、求最大值等。下面在“楼盘销售记录表”工作簿中首先按楼盘的“开发公司”进行分类，并按“总套数”进行求和汇总，然后按楼盘的“开发公司”进行分类，并按“已4STEP 1

18、对开发公司排序1 打开“楼盘销售记录表”工作簿，选择“开发公司”数据列中的任意单元格；2 在【数据】/【排序和筛选】组中单击“升序”按钮，对要进行分类的“开发公司”列进行排序。STEP 2 启用汇总功能排序完成后，在【数据】/【分级显示】组中单击“分类汇总”按钮。STEP 3 求和汇总1 打开“分类汇总”对话框，在“分类字段”下拉列表中选择“开发公司”选项；2 在“汇总方式”下拉列表中选择“求和”选项；3 在“选定汇总项”列表框中单击选中“总套数”复选框；4 单击“确定”按钮。STEP 4 查看求和汇总结果返回工作表，可查看按楼盘的“开发公司”进行的分类，并按“总套数”进行求和汇总的结果。ST

19、EP 5 设置最大值汇总1 再次打开“分类汇总”对话框，在“分类字段”下拉列表中选择“开发公司”选项；2 在“汇总方式”下拉列表中选择“最大值”选项；3 在“选定汇总项”列表框中单击选中“已售”复选框；4 单击“确定”按钮。STEP 6 查看最大值汇总结果返回工作表，可查看按楼盘的“开发公司”进行的分类，并按“已售”求最大值汇总的结果。2多项分类汇总多项分类汇总是在某列分类情况下，对其他多列数据列同时进行“求和”“最大值”或“平均值”汇总。下面在“楼盘销售记录表”工作簿中按楼盘的“开发公司”进行分类，同时对“总套数”和“已售”进行求和汇总，其具体操作步骤如下。STEP 1 设置多项汇总选项1

20、打开“楼盘销售记录表”工作簿，在【数据】/【分级显示】组中单击“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表中选择“开发公司”选项；2 在“汇总方式”下拉列表中选择“求和”选项；3 在“选定汇总项”列表框中单击选中“总套数”和“已售”复选框；4 单击“确定”按钮。STEP 2 多项汇总结果此时在表格中可查看到“总套数”和“已售”同时进行了求和汇总。3隐藏或显示分类汇总在表格中创建了分类汇总后，为了查看某部分数据，可将分类汇总后暂时不需要的数据隐藏起来，减小界面的占用空间。查看完成后可重新进行显示。下面在“楼盘销售记录表”工作簿中练习隐藏和显示分类汇总的方法，其具体操作步骤如下

21、。4STEP 1 隐藏“安宁地产”打开“楼盘销售记录表”工作簿，在“安宁房产”汇总项右侧单击“隐藏”按钮，将“安宁地产”信息隐藏。STEP 2 显示“安宁地产”使用相同的方法，将“都新房产”汇总项信息隐藏。隐藏后，对应的“隐藏”按钮将变成“显示”按钮模式，这里单击“安宁地产”汇总项目的“显示”按钮，将其信息全部显示。STEP 3 查看最终效果此时，即可查看分类汇总隐藏和显示后的最终效果。4清除和删除分类汇总清除是指将分级显示框删除，而保留数据汇总结果；删除则是指撤销分类汇总，重新显示源数据。下面在“楼盘销售记录表”工作簿中首先清除显示分级框，然后再删除分类汇总显示源数据，其具体操作步骤如下。S

22、TEP 1 清除分级显示1 打开“楼盘销售记录表”工作簿，在【数据】/【分级显示】组中单击“取消组合”按钮；2 在打开的下拉列表中选择“清除分级显示”选项。STEP 2 查看清除显示分级后的效果返回工作表，此时，在表格中将保留分类汇总项目，将左侧的分级显示列表框清除。STEP 3 删除分类汇总打开“分类汇总”对话框，单击“全部删除”按钮，撤销分类汇总，保留源数据。7.2.2 使用数据工具在 Excel 中，数据工具也是一项数据处理的重要功能，常用于删除数据重复项和数据验证。删除重复项是快速删除表格中多余数据的重要手段；而数据验证是指对某些重要数据区域做出某种限制，以确保数据准确输入并进行管理。

23、1快速删除重复项在表格中输入数据，有时由于长时间输入造成的视觉疲劳，难免碰到重复输入的情况。在进行数据核对时，则需删除重复数据，此时可使用 Excel 数据工具中的删除重复项来快速实现。下面将在“楼盘销售记录表”工作簿中快速删除重复数据，其具体操作步骤如下。STEP 1 执行删除重复项命令1 打开“楼盘销售记录表”工作簿，选择表格中任意一个数据单元格；2 在【数据】/【数据工具】组中单击“删除重复项”按钮。STEP 2 设置删除项1 打开“删除重复项”对话框，单击“全选”按钮；2 单击“确定”按钮。STEP 3 确认删除在打开的提示对话框中显示未发现的重复项，单击“确定”按钮确认删除。STEP

24、 4 查看删除后的结果返回工作表，即可查看删除重复项后的表格内容。2使用数据验证功能数据验证功能可在未输入数据时预先设置，使用数据验证可限制数据输入的范围，以保证输入数据的正确性。而数据验证主要分为“设置”和“出错警告”两种方式：“设置”用于提示输入非法值，“出错警告”则提示输入值的范围等。下面在“楼盘销售记录表”工作簿的“新楼盘登记”工作表中进行数据验证设置和数据验证出错警告，限制“开盘均价”和“总套数”的值，其具体操作步骤如下。STEP 1 启用数据验证功能1 打开“楼盘销售记录表”工作簿，新建“新楼盘登记”工作表，输入数据并设置格式，在其中选择 E3:E16单元格区域；2 在【数据】/【

25、数据工具】组中单击“数据有效性”按钮。STEP 2 设置数据限制信息1 打开“数据有效性”对话框，单击“设置”选项卡，在“允许”下拉列表中选择“整数”选项；2 在“数据”下拉列表中选择“介于”选项；3 在“最小值”与“最大值”数值框中分别输入“7000”和“15000”；4 单击“确定”按钮。STEP 3 非法输入的提示效果设置完成后，在设置过有效性的单元格中输入小于7000 或大于 15000 的数值，将打开提示对话框，提示输入值非法，然后单击“取消”按钮关闭对话框。STEP 4 设置警告出错信息1 选择 E3:E16 单元格区域，打开“数据有效性”对话框，单击“出错警告”选项卡；2 在“标

26、题”文本框中输入“提示”；3 在“错误信息”文本框中输入“开盘均价在7000-15000之间！”；4 单击“确定”按钮。STEP 5 弹出具体警告内容设置完成后，单击该单元格，当输入的数值不符合验证规则所设置的输入范围时将打开错误警告信息提示对话框，同样单击“取消”按钮，关闭对话框。7.2.3 设置数据的条件格式设置数据的条件格式是指规定单元格中的数据在满足某类条件时，将单元格显示为相应条件的单元格样式。使用条件格式功能，可突出显示单元格，或为选择的单元格应用图形效果，包括“数据条”“色阶”和“图标集”等。1按规则突出显示单元格使用条件格式功能，可使单元格在满足预置的规则条件时，呈突出显示。下

27、面在“楼盘销售记录表”工作簿中将“开盘均价”高于平均值和“总套数”大于“150”的数据单元格突出显示，其具体操作步骤如下。STEP 1 选择“高于平均值”选项1 打开“楼盘销售记录表”工作簿，在“新楼盘登记”工作表中选择 E3:E16 单元格区域，在【开始】/【样式】组中单击“条件格式”按钮；2 在打开的下拉列表中选择“项目选取规则”选项；3 在子列表中选择“高于平均值”选项。STEP 2 设置格式1 打开“高于平均值”对话框，在“针对选定区域，设置为”下拉列表中选择“黄填充色深黄色文本”选项；2 单击“确定”按钮。STEP 3 查看显示效果返回工作表，在 E3:H16 单元格区域中输入相关数

28、据，此时在 E3:H16 单元格区域中的“开盘均价”数据列中高于平均值的数据单元格将呈“黄填充色深黄色文本”格式显示。STEP 4 选择“大于”选项1 选择 F3:F16 单元格区域，在【开始】/【样式】组中单击“条件格式”按钮；2 在打开的下拉列表中选择“突出显示单元格规则”子列表中的“大于”选项。STEP 5 设置显示规则1 打开“大于”对话框，在“为大于以下值的单元格设置格式”栏下方的文本框中输入“150”；2 在“设置为”下拉列表中选择“绿填充色深绿色文本”选项；3 单击“确定”按钮。STEP 6 查看显示效果此时在“总套数”数据列中大于 150 的数据单元格将呈“绿填充色深绿色文本”

29、格式显示。2应用图形效果条件格式中的图形效果主要分为“数据条”“色阶”和“图标集”。下面在“楼盘销售记录表”工作簿中将“开盘均价”“总套数”和“已售”数据列分别以“数据条”“色阶”和“图标集”突出显示，其具体操作步骤如下。STEP 1 选择“其他规则”选项1 打开“楼盘销售记录表”工作簿，在“新楼盘登记”工作表中选择 E3:E16 单元格区域；2 在【开始】/【样式】组中单击“条件格式”按钮；3 在打开的下拉列表中选择“数据条”选项；4 在打开的子列表中选择“其他规则”选项。STEP 2 自定义数据条1 打开“新建规则类型”对话框，在“条形图外观”栏的“填充”下拉列表中选择“渐变填充”选项；2

30、 在“颜色”下拉列表中选择“橙色”选项；3 在“边框”下拉列表中选择“实心边框”选项；4 在“颜色”下拉列表中选择“红色，强调文字颜色 2，深色 50%”选项；5 在“条形图方向”下拉列表中选择“从左到右”选项；6 单击“确定”按钮。STEP 3 查看应用数据条效果返回工作表，可查看为“开盘均价”数据列应用数据条后的效果。STEP 4 选择色阶样式1 选择 F3:F16 单元格区域，在【开始】/【样式】组中单击“条件格式”按钮；2 在打开的下拉列表中选择“色阶”选项；3 在打开的子列表中选择“红-黄-绿色阶”选项。STEP 5 查看应用色阶效果返回工作表，可查看为“总套数”数据列应用色阶样式后

31、的效果。STEP 6 选择图标集样式1 选择 G3:G16 单元格区域，单击“条件格式”按钮；2 在打开的下拉列表中选择“图标集”选项；3 在打开的子列表中选择“等级”栏中的“四等级”选项。STEP 7 查看应用图标集效果返回工作表，可查看应用图标集后的效果。3删除条件格式删除条件格式，可分为删除所选单元格区域的条件格式或删除整个工作表的条件格式。下面在“楼盘销售记录表”工作簿中先删除“开盘均价”数据列的条件格式，然后删除整个工作表的条件格式，其具体操作步骤如下。STEP 1 清除所选单元格的规则1 打开“楼盘销售记录表”工作簿，选择 E3:E16 单元格区域；2 单击“条件格式”按钮；3 在打开的下拉列表中选择“清除规则”选项；4 在打开的子列表中选择“清除所选单元格的规则”选项。STEP 2 查看清除所选单元格规则的效果返回工作表，可查看到所选 E3:E16 单元格区域的规则被删除，显示为常规模式。STEP 3 清除所有规则单击“条件格式”按钮，在打开的下拉列表中选择“清除规则”子列表中的“清除整个工作表的规则”选项，将所有规则清除。