Excel数据处理技巧：任务四完美解决方案

子任务一：学会数据排序的基本方法

对数据排序作用在于快速规范、按顺序将数据排列显示，从而直观地找到数据中的最大记录、最小记录等。

打开“Excel基本操作系统”工作簿，在本工作簿中用复制工作表方法把“教职工基本工资表”复制一份，并重命名为“排序”，然后注释“按照所属部门为主要关键字的升序，总工资降序排列”，如图1－84所示。

图1－84 复制工作表

选中A2：K12单元格区域，单击【数据】菜单下的【排序】命令，如图1－85所示。

图1－85 排序设置对话框

按要求设置“主要关键字”为“所属部门”，升序，以及“次要关键字”为“总工资”，降序，“我的数据区域”设置为“有标题行”，如图1－86所示。

图1－86 设置排序关键字

单击【确定】后，表中的所有数据就会根据如此设置排列，完成保存。如图1－87所示。

图1－87 完成效果显示

子任务二：学会数据筛选的基本方法

在工作表中，一行有数据的单元格称为一个数据行。利用Excel中的“自动筛选”和“高级筛选”功能可以只显示工作表中符合条件的数据行。“自动筛选”通常用于简单的筛选条件，使用简便；“高级筛选”针对更复杂的筛选条件，功能更强大。无论使用哪一种功能，都要清楚多个筛选条件之间的逻辑关系。

在“Excel基本操作系统”工作簿中复制一份“教职工基本工资表”，并重命名为“筛选”，然后注释“利用自动筛选筛选出会计学院的讲师人员。利用高级筛选筛选出在2025年前参加工作的，且总工资在6000元以上的职工。”，如图1－88所示。

（1）利用自动筛选筛选出会计学院的讲师人员

图1－88 复制工作表

选择数据清单中的任意一个单元格，单击【数据】菜单中的【筛选】︱【自动筛选】命令，如图1－89所示，得到带有自动筛选的数据清单，如图1－90所示。

图1－89 打开自动筛选

图1－90 自动筛选按钮

单击“所属部门”右下拉箭头，弹出列表框，如图1－91所示。列表框中列出了全部、前10个、自定义以及该字段中出现的所有数据，选择“会计学院”。在“职称”列右下拉箭头弹出列表框中选择“讲师”，如图1－92所示。

图1－91 自动筛选1—部门

图1－92 自动筛选2—职称

注意：设置了筛选条件的列标题名右侧的数据筛选箭头按钮变成了蓝色，表示Excel根据这一列中的数据进行了筛选。如图1－93所示。

图1－93 自动筛选后显示效果

从下拉列表框中选择“全部”，则所有的记录都显示出来；如果需要取消列标的下拉箭头，则单击【筛选】选项中“自动筛选”以使其左边的取消。如图1－94所示。

图1－94 打开高级筛选

（2）利用高级筛选筛选出在2025年前参加工作的且总工资在6000元以上的职工

单击【数据】︱【筛选】下的“全部显示”，在A18：B19单元格区域中设置条件区域，如图1－95所示。

图1－95 设定条件

启动高级筛选，并设置高级筛选的“方式”、“列表区域”、“条件区域”和位置，如图1－96所示。

单击【确定】完成高级筛选，全部任务完成后保存。如图1－97所示。

图1－96 高级筛选设置

图1－97 高级筛选后显示效果

子任务三：掌握数据分类汇总的基本方法

分类汇总是数据分析的一种手段，就是将同类逐句放在一起，再进行数量的求和、计数、求平均值之类的汇总运算。分类汇总有三个基本要素：

分类字段：选定要进行分类的汇总的列，对数据表按这个列进行排序。

汇总方式：利用“平均”、“最大值”、“求和”、“计数”等汇总函数，实现对分类字段的计算。

汇总项：可以选择多个字段进行汇总。

在“Excel基本操作系统”工作簿中复制一份“教职工基本工资表”，并重命名为“分类汇总”。在此需要汇总每个部门的总工资的总和。

第一步：按照需要分类汇总的列进行排序，此处的分类字段为“所属部门”，即主要关键字为“所属部门”，升序与降序可任意选择，单击【确定】完成。如图1－98所示。

图1－98 按分类字段排序

第二步：选择单元格区域，单击【数据】︱【分类汇总】，打开“分类汇总”对话框，设定分类汇总选项，如图1－99所示。

图1－99 设定分类汇总选项

第三步：单击【确定】完成，并保存。如图1－100所示。

图1－100 分类汇总结果显示

注意：分类汇总后左边的“－”号可以单击，对分类汇总结果进行分级显示。

如要删除分类汇总，则单击任意一个单元格，单击【数据】菜单下的【分类汇总】命令，打开“分类汇总”对话框，单击“全部删除”，即可删除分类汇总。

子任务四：学会利用图表表达数据

在实际工作中有时需要直观地显示数据的处理结果，这时需要Excel的图表功能。数据以图表的形式显示，具有良好的视觉效果，会更清楚和易于理解，同时图表还能帮助用户查看数据的差异、走势和预测发展趋势。

选择“教职工基本工资表”作为当前工作表。

单击【插入】︱【图表】命令，打开“图表向导”对话框，在“图表向导－4步骤之1”对话框中，“图表类型”选择“柱形图”，在“子图表类型”下选择“簇状柱形图”，然后单击“下一步”，如图1－101所示。

在打开“图表向导－4步骤之2”对话框中设置“数据区域”为“教职工基本工资表中”的B2：B12，H2：H12，K2：K12不连续单元格区域，“系列产生在”选择“列”，单击“下一步”，如图1－102所示。

图1－101 插入图表选择图表类型

图1－102 选择数据区域

在打开的“图表向导－4步骤之3”对话框中设置标题，“图表标题”为“教职工基本工资图表”，“分类（X）轴”为“姓名”，“分类（Y）轴”为“工资”，单击“下一步”，如图1－103所示。

图1－103 设置图表选项

在打开“图表向导－4步骤之4”对话框中，单击“作为新工作表插入”单选按钮，单击“完成”，在其表前自动插入一个名称为“Chart1”的表，如图1－104所示。

图1－104 完成效果

分别对“图表标题格式”、“坐标轴格式”、“背景墙格式”、“图表区格式”进行设置，如图1－105所示。完成后对表Chart1重命名为“工资表图表”，保存并退出。

图1－105 设置后图表效果

子任务五：掌握数据透视表工具的基本应用方法

在Excel中，实现数据透视分析的工具是数据透视表和数据透视图，其中，数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，使用数据透视表可以汇总、分析、浏览和提供摘要数据。数据透视表可以把很多行和列数据快速转化为有意义的信息，节省很多烦琐的公式计算，并能生成一个基于Excel数据表甚至外部数据库的动态总结报告。

例如给出“食品保质期检验结果统计表”，如图1－106所示，利用数据透视表统计出“各产品厂家”的“检验结果”为“不合格”和“合格”的食品种类，数据透视表的位置为当前工作表的开始的D16单元格中，设置数据透视表的自动套用格式为“表1”。

图1－106 食品保质期检验结果统计表

通过【数据】菜单下“数据透视表和数据透视图”打开“数据透视表和数据透视图向导”，如图1－107所示。(https://www.daowen.com)

图1－107 通过【数据】菜单打开透视表

在“数据透视表和数据透视图向导—3步骤之1”对话框中设定数据源类型和报表类型，单击“下一步”，如图1－108所示。

在“数据透视表和数据透视图向导—3步骤之2”对话框中设定数据源区域，单击“下一步”，如图1－109所示。

在“数据透视表和数据透视图向导—3步骤之3”对话框中设定透视表显示位置，单击【完成】，如图1－110所示。

图1－108 步骤1－设定数据源类型和报表类型

图1－109 步骤2－设定数据源区域

图1－110 步骤3－设定数据透视表显示位置

单击【完成】后，显示透视表设计界面，如图1－111所示。

图1－111 透视表设计界面

将相应项目拖至透视表对应字段中，如图1－112所示。

图1－112 设置数据透视表

用鼠标左键双击“计数项：”，可以打开“数据透视表字段”设置对话框，如图1－113所示。

图1－113 数据透视表字段设置对话框

打击“数据透视表”对话框中左侧的“数据透视表”右边下拉菜单，单击“设置报表格式”，打开自套用格式，选择“表1”，单击【确定】完成报表格式设置，如图1－114所示。保存并退出。

图1－114 数据透视表报表格式设置过程

子任务六：学会制作页面

打开“Excel基本操作系统”工作簿，把所有完成的工作表根据自己的要求排列好顺序，在教职工基本情况表前插入一个新工作表，并重命名为“页面”，给本工作簿所有工作表制作一个页面，如图1－115所示。

图1－115 页面

操作步骤如下：

（1）页面背景的设计

①单击菜单【插入】︱【对象】，打开【对象】对话框，在“对象类型”列表框中选择“Microsoft PowerPoint幻灯片”，单击【确定】按钮，就可以在Excel工作表中插入一个Microsoft PowerPoint幻灯片对象。如图1－116所示。

图1－116 插入对象

②单击【格式】工具栏上的【设计】按钮，打开【幻灯片设计】对话框，在【应用设计模板】中选择喜欢的幻灯片模板。双击选中的模板，该模板就会应用到幻灯片中，如图1－117所示。

图1－117 打开幻灯片设计

③单击工作表中幻灯片以外的任意地方，退出，返回Excel工作表（注意观察幻灯片边缘线条的变化），双击幻灯片，即可回到幻灯片设计界面。

④单击插入的幻灯片，用鼠标对准幻灯片四周的8个调节点，调整幻灯片大小到页面背景所需要的合适的尺寸。

（2）页面标题的设计

①单击菜单【插入】︱【图片】︱【艺术字】，打开【艺术字库】对话框。选择喜欢的艺术字样式，单击【确定】按钮，打开【编辑“艺术字”文字】对话框。如图1－118所示。

图1－118 艺术字库

②输入文字“EXCEL基本操作系统”，设置字体和字号，单击【确定】按钮。

③用鼠标对准“EXCEL基本操作系统”这一组艺术字，单击鼠标左键时会在Excel窗口中弹出【艺术字】工具栏。单击【设置艺术字格式】按钮，打开【设置艺术字格式】对话框，可以对艺术字的颜色、大小等格式进行设置。

④在【艺术字】工具栏，单击【艺术字形状】按钮，可以对艺术字形状进行设置。

⑤完成页面标题的设计效果图，如图1－119所示。

图1－119 标题艺术字

（3）设置页面到各个工作表的链接

①单击菜单【视图】︱【工具栏】︱【绘图】命令，在Excel左下角显示【绘图】工具栏。

②单击【绘图】工具栏的【自选图形】按钮右边的倒三角形，选择“基本形状”，展开图形集，选中喜欢的图形。选中后点击鼠标左键，使鼠标形状变为黑色小十字。

③移动鼠标到背景图片上，选择好适合的位置，按住鼠标左键不放，拖动鼠标画出适当大小的图形按钮。如图1－120所示。

图1－120 绘制图形

④选中刚才画好的图形，设置成三维效果1，并设置填充颜色，单击鼠标右键，在弹出的菜单中单击【添加文字】命令，则在自选图形中出现输入文字的光标，此时可以输入相关文字，这里输入“教职工基本情况表”。选中自选图形，单击鼠标右键，在弹出的快捷菜单中单击【设置自选图形格式】命令，打开【设置自选图形格式】对话框。可以对字体与对齐方式进行设置，如图1－121所示。

图1－121 设置自选图形

⑤将制作好的第一个自选图形对象进行复制，连续粘贴六次。移动粘贴后的按钮到合适的位置，然后分别修改其上的文字为对应工作表的名称。

⑥在背景图合适的位置插入文本框，输入作者的相关信息，版权申明等。也可以插入一些自选图形进行修饰，使页面效果更美观。如图1－122所示。

图1－122 页面效果

⑦选择“教职工基本情况表”按钮，单击鼠标右键，在弹出的菜单中选择【超链接】命令，打开【插入超链接】对话框。链接到本文档中的对应工作表位置。

⑧在“链接到”区域所显示的几个项目中单击【本文档中的位置】按钮图标，在“或在这篇文档中的选择位置”所示列表中选择“教职工基本情况表”。单击【屏幕显示】按钮，打开【设置超链接屏幕提示】对话框，在“屏幕提示文字”对话框中输入提示文字“单击打开教职工基本情况表”。这样当鼠标悬停在按钮上时，就能看到相应的文字提示。单击【确定】按钮，关闭【设置超链接屏幕对话框】，再单击【确定】按钮，关闭【插入超链接】对话框，返回Excel工作表，从而为“教职工基本情况表”自选图形对象建立了指向“教职工基本情况表”工作表的超链接，使其具备了导航按钮的功能。如图1－123所示。

⑨链接到“教职工基本情况表中”时，在“教职工基本情况表中”也制作一个返回的超链接。如图1－124所示。

⑩按照相同的方法设置其他六个按钮。

（4）页面的美化

由于“页面”工作表仍然有工作表的网格线、行号和列标等信息，使其不太美观。下面对该表视图进行美化，具体步骤如下：

①单击菜单【工具】︱【选项】，打开【选项】对话框。单击【视图】选项卡，取消“窗口选项”中的“网格线”、“行号列标”两个复选框。单击【确定】按钮，退出【选项】对话框。这时候“页面”工作表中就没有行号、列标和网格线。如图1－125所示。

图1－123 编辑超链接

图1－124 返回超链接

图1－125 设置视图效果

②为了美观，还应该在项目完成后取消工作表标签。由于项目制作中需要使用到这些标签，所以暂时不取消。

（5）保护工作表

为了避免操作者不小心移动或损坏页面工作表中的各个图形按钮及他人任意更改页面结构，应该对工作表进行保护，具体操作如下：

①单击菜单【工具】︱【保护】︱【保护工作表】，打开【保护工作表】对话框。如图1－126所示。

②在“取消工作表保护时使用的密码”文字框中输入工作表保护密码，单击【确定】按钮。

③在弹出的【确认密码】对话框中再次输入密码。单击【确定】按钮。退出【确认密码】对话框，再单击【确定】按钮，关闭【保护工作表】对话框。如图1－127所示。

图1－126 保护工作表

图1－127 设置保护密码

注意：工作表保护密码应该记牢，可以记录在本讲义上，以防忘记。如果忘记了保护密码，就无法取消对工作表的保护，且无法再对工作表进行修改。