2.4 Excel高级应用
2.4.1 数据管理与分析
Excel为数据管理提供了强大的功能。本节主要介绍数据的获取，分析工作表中的数据编辑，汇总计算数据的方法以及数据透视表等知识。
2.4.1.1 管理数据清单
数据清单是包含有关数据的一系列工作表数据行。在前面的文章叙述中，对工作表中的数据进行各种操作时，已经使用了数据清单。此处的数据清单，是要将数据清单看作一个数据库。事实上，当对工作表中的数据进行排序、分类汇总等操作时，Excel会将数据清单看做数据库来处理，数据清单中的行被作为数据库中的记录，数据清单中的列被看做对应数据库的字段，数据清单中的列名称被作为数据库中的字段名称。
1．使用记录单
对数据清单的各种编辑操作经常用到记录单。学会使用记录单对于处理数据清单很重要。
1）使用记录单
记录单可以提供简便的方法在数据清单中一次输入或显示一个完整的信息行，即记录。在使用记录单向新数据清单添加记录时，该数据清单在每一列的最上面必须有标志。Excel使用这些标志来生成记录单的字段。
操作实例2-5：使用记录单在数据清单中添加数据。
（1）单击【Office】按钮，在打开的菜单中单击【Excel选项】按钮，打开【Excel选项】对话框。
（2）在【Excel选项】对话框中，单击【自定义】选项卡，在【从下列位置选择命令】下拉列表中选择【不在功能区中的命令】选项，在其下方的列表框中选择【记录单】选项，单击【添加】按钮，如图2-63所示，设置完成后，单击【确定】按钮。
图2-63 找到“记录单”命令
（3）选择需要向其中添加记录的数据清单中的任一单元格，如本例子中的“科目编码、账户名称”，选择【记录单】
，在弹出的窗口中选择【确定】，如图2-64所示。
图2-64 打开【记录单】
（4）在【科目编码】和【账户名称】文本框中分别输入1001和“库存现金”，然后单击【新建】按钮，如图2-65所示。⑤完成记录添加后，单击【关闭】按钮，完成会计科目的添加并关闭记录单。
图2-65 在记录单输入数据
2）建立数据清单的准则
Excel提供了一系列功能，可以使用户方便地在数据清单中处理和分析数据。在运用这些功能时，必须按照下述准则在数据清单中输入数据。
（1）数据清单应存放在工作表的一个连续区域中；
（2）数据清单中应避免空行和空列；
（3）一张工作表只建立一个数据清单；
（4）数据清单中的每一列包含相同类型的数据；
（5）数据清单中的第一行为列标志，表示每一列的名称；
（6）不要在单元格的前面或后面键入多余的空格。
3）修改记录
使用记录单不但可以在工作表中为数据清单添加数据，还可以对数据清单进行修改。
操作实例2-6：修改记录。
具体步骤如下：
（1）单击需要修改的数据清单中的任一单元格。
（2）点击【记录单】功能，打开【记录单】对话框。
（3）单击【上一条】或【下一条】，或者通过【条件】功能输入要修改的记录，在记录中修改信息，如本例题的【科目编码】、【账户名称】，然后再点击【下一条】按钮找到需要修改的记录，在该记录中修改信息，如图2-66所示。
图2-66 修改会计科目表
（4）完成会计科目修改后，单击【关闭】按钮，更新显示的记录并关闭记录单，完成会计科目的修改操作。
4）删除记录
使用记录单在数据清单中删除记录和修改记录的方法类似。如果会计科目表中的某个会计科目多余或者错误，可以利用记录单快速查找数据清单记录的功能，找到某个会计科目并进行删除。
操作实例2-7：删除记录。
（1）单击需要修改得会计科目表中的任一单元格。
（2）点击【记录单】功能，打开【记录单】对话框。
（3）单击【上一条】或【下一条】，或者通过【条件】功能输入要删除的记录，在记录中删除信息，如本例题的输入要查询的【科目编码】、【账户名称】，然后再点击【下一条】按钮找到其他需要删除的记录。
（4）单击【删除】按钮，将弹出如图2-67所示的提示对话框。
图2-67 删除提示对话框
（5）在该对话框中单击【确定】按钮，记录即被删除。
（6）单击【记录单】对话框中的【关闭】按钮，完成会计科目的删除操作。
5）查找记录
当数据清单比较大时，要找到数据清单中的记录比较困难。记录单提供了快速查找数据清单记录的功能。
如果要每次移动一个记录，可以使用【记录单】对话框中的滚动条箭头。如果要每次移动10个记录，可以单击滚动条与下箭头之间的空白。
使用记录单还可以对数据清单中的数据设置查找条件。
操作实例2-8：设置条件查找数据清单中的数据。
（1）单击需要修改的数据清单中的任一单元格。
（2）点击【记录单】功能，打开【记录单】对话框。
（3）单击通过【条件】功能输入要查找的条件，单击【上一条】按钮或【下一条】按钮进行查找，可以顺序找到满足查找条件的记录。如果要找到符合指定条件的记录就退出搜索，单击【表单】按钮。
（4）找到记录后可以进行各种编辑操作。操作完毕或者查找中要退出【表单】对话框，单击【关闭】按钮即可。
2．数据清单排序
数据排序是指按一定规则对数据进行整理、排列，这样可以为数据的进一步处理作好准备。Excel 2007提供了多种方法对数据清单进行排序，可以按升序、降序的方式，也可以由用户自定义排序。
对Excel 2007中的数据清单进行排序时，如果按照单列的内容进行排序，可以直接在【开始】选项卡的【编辑】组中完成排序操作。如果要对多列内容排序，则需要在【数据】选项卡中的【排序和筛选】组中进行操作。如图2-68所示。
图2-68 数据清单排序
3．数据的高级排序
数据的高级排序是指按照多个条件对数据清单进行排序，这是针对简单排序后仍然有相同数据的情况进行的一种排序方式。如图2-69所示。
图2-69 数据的高级排序
4．使用筛选
筛选就是一种用于查找数据清单中数据的快速方法。Excel中提供了两种数据的筛选操作，即自动筛选和高级筛选。经过筛选后的数据清单只显示包含指定条件的数据行，以供用户浏览、分析。数据【筛选】命令位于【数据】选项卡下的【排序和筛选】组中。如图2-70所示。
图2-70 使用筛选
1）使用自动筛选
自动筛选为用户提供了在具有大量记录的数据清单中快速查找符合某种条件记录的功能。使用自动筛选功能筛选记录时，字段名称将变成一个下拉列表框的框名。自动筛选功能可以暂时把不需要的数据隐藏起来，只显示符合条件的数据记录，这在管理工作表时相当有用。
单击工作表内任意单元格，再选择【数据】工具选项卡下的【筛选】命令，这时工作表的字段名称所在的单元格就会出现筛选按钮。单击该筛选按钮，在弹出的快捷菜单中勾选中符合筛选条件的选项，最后单击【确定】命令即可。如图2-71所示。
图2-71 自动筛选
2）使用自定义筛选
使用Excel 2007中自带的筛选条件，可以快速完成对数据清单的筛选操作。但是当自带的筛选条件无法满足需要时，也可以根据需要自定义筛选条件。如图2-72所示。
图2-72 自定义筛选
3）使用高级筛选
使用高级筛选功能，必须先建立一个条件区域，用来指定筛选的数据所需满足的条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据清单中的字段名必须完全一样。
单击工作表内任一单元格，选择【数据】|【排序与筛选】|【高级】命令。弹出【高级筛选】对话框，对话框中有3个区域，分别是筛选区域、条件区域、复制到区域（即结果区域）。
在【高级筛选】对话框中设置完成以后，单击【确定】按钮，就可以实现Excel的筛选功能。如图2-73所示。
图2-73 高级筛选
2.4.1.2 分类汇总数据
分类汇总是对数据清单进行数据分析的一种方法。分类汇总对数据库中指定的字段进行分类，然后统计同一类记录的有关信息。统计的内容可以由用户指定，也可以统计同一类记录的记录条数，还可以对某些数值段求和、求平均值、求极值等。
分类汇总的功能：
（1）告诉Excel如何创建数据组。
（2）在数据清单中显示一集组的分类汇总及总和。
（3）在数据清单中显示多集组的分类汇总及总和。
（4）在数据组上执行不同计算——计数项、总计项、平均项。
1）创建分类汇总
使用Excel的数据汇总命令进行数据分类汇总的操作。首先选择需要汇总类型的数据区域内任一单元格，在【数据】选项卡中选择【分类汇总】命令。弹出【分类汇总】对话框，在对话框中，输入分类汇总的字段、汇总方式、汇总项等项目。最后单击【确定】按钮即可完成。如图2-74所示。
图2-74 创建分类汇总
2）隐藏或显示分类汇总
为了方便查看数据，可将分类汇总后暂时不需要使用的数据隐藏起来，减小界面的占用空间。当需要查看隐藏的数据时，可再将其显示。如图2-75所示。
图2-75 隐藏或显示分类汇总
2.4.1.3 数据透视表
数据透视表是一种对大量的数据快速汇总和建立交叉列表的交互式表格。通过数据透视表可以更加容易地对数据进行分类汇总和数据筛选，可以有效灵活地将各种方式记录的数据，在重新进行组合和添加算法的过程中，快速地进行各种目标的统计与分析。
1．建立数据源
建立数据源是建立数据透视表的第一步，是为建立数据透视表做准备工作。建立数据源主要包括以下几方面的内容：
（1）在Excel表中建立数据清单或数据库作为数据源。
（2）外部数据源，即与建立数据透视表相关的外部数据库文件，例如产品销售情况统计、采购业务文件等，也可以使用Internet网上的数据。
（3）还可以是经过多重合并计算的数据区域。
（4）另一个数据透视表。
2．建立数据透视表
（1）在数据清单中单击任一单元格，然后选择【插入】|【数据透视表】命令，弹出【创建数据透视表】对话框。设置完成后单击【确定】命令。
（2）左边为数据透视表报表的生成区域，会随着选择的字段不同而自动更新。右侧为数据透视表编辑栏。如图2-76所示。
图2-76 建立数据透视表
（3）在默认情况下，数据透视表编辑栏显示两部分：上方的字段部分用于添加和删除字段；下方的布局部分用于重新排列和重新定位字段。可以将数据透视表的编辑栏摆放在工作表的任意一边，然后沿水平方向调整编辑栏的大小；也可以取消显示数据透视表编辑栏。
（4）数据透视表编辑栏的右下方的四个区域，分别是【报表筛选】、【列标签】、【行标签】区域用于放置分类字段，【数值】区域数据汇总字段。当将字段拖动到数据透视表区域时，工作表左侧会自动生成数据透视表。
下面分别介绍这四个区域的作用。
【报表筛选】区域：将字段拖动到【报表筛选】区域，则可以根据此字段实现对报表的筛选，可以显示每类项目相关的报表。我们可以将较大范围的字段拖动到此区域，快速地实现对报表的筛选。
【列标签】区域：将字段拖动到此区域，则此字段的每个项目都会成为列。可以将需要按列显示的字段拖动到此区域。
【行标签】区域：将字段拖动到此区域，则此字段的每个项目都会成为一行。可以将需要按行显示的字段拖动到此区域。
【数值】区域：将字段拖动到此区域，则会自动计算此字段的汇总信息（例如求和、平均值、计数、方差等等）。可以将任何需要汇总信息的字段拖动到此区域。
3．设置数据透视表选项
在【数据透视表工具】的【选项】选项卡中还可以设置其他常用数据透视表选项。如图2-77所示。
图2-77 设置数据透视表选项
4．设置字段
打开【数据透视表工具】的【选项】选项卡，在【活动字段】组中可以详细设置数据透视表中各字段的属性。如图2-78所示。
图2-78 设置字段
5．更改数据源
若要修改数据透视表的数据源，可以打开【数据透视表工具】的【选项】选项卡，在【数据】组中，单击【更改数据源】按钮，在弹出的菜单中选择【更改数据源】命令，打开【更改数据透视表数据源】对话框。如图2-79所示。
图2-79 更改数据源
6．设计数据透视表
在【数据透视表工具】的【设计】选项卡中，可以设置数据透视表的布局、样式以及样式选项等，帮助用户设计所需的数据透视表。如图2-80所示。
图2-80 设计数据透视表
7．设置数据透视表样式
在Excel 2007中预置了一些数据透视表的样式。在【设计】选项卡的【数据透视表样式】组中，可以设置数据透视表快速套用预置样式。如图2-81所示。
图2-81 设置数据透视表样式
2.4.2 图表
使用Excel2007对工作表中的数据进行计算、统计等操作后，得到的计算和统计结果还不能更好地显示出数据的发展趋势或分布状况。为了解决这一问题，Excel2007将处理的数据建成各种统计图表，这样就能够更直观地表现处理的数据。在Excel2007中，用户可以轻松地完成各种图表的创建、编辑和修改工作。
为了能更加直观地表达工作表中的数据，可将数据以图表的形式表示。通过图表可以清楚地了解各个数据的大小以及数据的变化情况，方便对数据进行对比和分析。Excel自带各种各样的图表，如柱形图、折线图、饼图、条形图、面积图、散点图等，各种图表各有优点，适用于不同的场合。图表的结构如图2-82所示。
图2-82 图表结构
2.4.2.1 创建图表
1．建立数据源
建立图表首先要建立相关的数据表。图2-83是在Excel上建立的某公司2005年至2008年度主要损益项目的变动情况。
图2-83 建立数据源
2．选择图表类型
选择工作表中数据所在的单元格区域A2:E5，然后选择【插入】|【图表】|【折线图】命令，Excel就会弹出折线图。如图2-84所示。
图2-84 选择图表类型
3．添加图表标题
首先在工具栏中选择【布局】|【标签】|【图表标题】命令，在弹出的下拉菜单中选择需要把图表标题置放在图表的“图表上方”，然后单击图表的标题处对图表标题进行编辑。
2.4.2.2 修改图表
如果已经创建好的图表不符合用户要求，可以对其进行编辑。例如，更改图表类型、调整图表位置、在图表中添加和删除数据系列、设置图表的图案、改变图表的字体、改变数值坐标轴的刻度和设置图表中数字的格式等。
1．改变图表类型
若图表的类型无法确切地展现工作表数据所包含的信息，如使用圆柱图来表现数据的走势等，此时就需要更改图表类型。如图2-85所示。
图2-85 改变图表类型
2．移动图表位置
在Excel 2007的图表中，图表区、绘图区以及图例等组成部分的位置都不是固定不变的，可以拖动它们的位置，以便让图表更加美观与合理。如图2-86所示。
图2-86 移动图表位置
3．调整图表大小
在Excel 2007中，除了可以移动图表的位置外，还可以调整图表的大小。用户可以调整整个图表的大小，也可以单独调整图表中的某个组成部分的大小，如绘图区、图例等。如图2-87所示。
图2-87 调整图表大小
4．修改图表中文字的格式
在Excel 2007中，默认创建图表的形状样式很普通，用户可以为图表各部分设置形状填充、形状轮廓以及形状效果等，让图表变得更加美观和引人注目。在图表中右击文字，在弹出的【格式】工具栏中可以设置文字的格式。如图2-88所示。
图2-88 修改图表中文字的格式
5．设置图表标签
在【布局】选项卡的【标签】组中，可以设置图表标题、坐标轴标题、图例、数据标签以及数据表等相关属性。如图2-89所示。
图2-89 设置图表标签
6．设置坐标轴
在【布局】选项卡的【坐标轴】组中，可以设置坐标轴的样式、刻度等属性，还可以设置图表中的网格线属性。如图2-90所示。
图2-90 设置坐标轴
7．添加趋势线
趋势线就是用图形的方式显示数据的预测趋势并可用于预测分析，也叫做回归分析。利用趋势线可以在图表中扩展趋势线，根据实际数据预测未来数据。打开【图表工具】的【布局】选项卡，在【分析】组中可以为图表添加趋势线。如图2-91所示。
图2-91 添加趋势线
2.4.2.3 使用图形和图片
1．使用现有形状
形状包括线条，基本集合形状，箭头，公式形状，流程图形状，星，旗帜和标注，Excel 2007提供了访问作为形状的各种各样的自定义图形与图像的方法，可以在Excel 2007的工作表绘图层或图表中添加一些形状，也可以合并多个形状生成一个图形或一个更为复杂的形状。
1）在工作表中插入形状
在工作表中插入形状的具体步骤如下。
（1）打开需要插入形状的工作表，单击【插入】选项卡中的【形状】按钮，然后选择类型。
（2）在工作表中，按住鼠标左键，拖拽至需要的大小后释放，此时就创建了一个选择的图形类型，然后在【名称框】内显示图形类型的名称及编号。
（3）选中插入的图形类型，执行【格式】选项卡中的【形状样式】命令，对刚刚插入的图形类型进行一些美化工作，如为形状填充颜色、设置轮廓、设置显示效果等。
2）在形状中添加文字
在形状中添加文字的具体操作为：选中形状，直接输入文字，并可以选择右键菜单中的命令。进行字体设置、文字效果设置等操作。
此外，还可以通过插入文本框在形状中添加文字，具体步骤如下。
（1）选择插入的自选图形，单击【格式】选项卡中的【文本框】按钮，在自选图形中输入文字，调整文本框位置，如图2-92所示。
图2-92 在“文本框”输入文字
（2）对文字进行显示效果设置，选中输入文字，右击，在弹出的快捷键菜单中执行【设置文字效果格式】命令，弹出【设置文本效果格式】对话框，在该对话框中可以对输入的文字效果进行调整。如图2-93所示。
图2-93 设置显示效果后的文字
3）选择多个对象
有时候，需要一次对多个自选图形同时进行相关的操作，具体操作为：按住Ctrl键，依次选择对象，如果需要选择工作表中的所有对象，可以单击【开始】选项卡中的【查找和选择】按钮，在弹出的菜单中选中【定位条件】，弹出【定位条件】对话框，然后选中【对象】单选按钮，单击【确定】按钮，即可选中工作表中的全部对象。
4）移动和复制对象
如果需要移动对象，只需选中要移动的对象，拖拽鼠标即可完成。
如果需要精确地控制对象的移动，可以使用方向键每次只移动一个像素。
如果需要复制对象，首先选择需要复制的对象，然后执行复制、粘贴命令。
2．绘图工具
当单击选中一个形状的时候，在菜单栏可以看到【绘图工具】的【格式】中有很多内容，如插入形状、形状样式、艺术字样式、排列、大小等。下面将介绍一些常用的操作。
1）对齐和平均分布
在一个工作表中绘制了多个图形对象，如果通过拖动选中的图形对象的方式进行对齐操作，不仅麻烦而且不太准确，使用对齐命令就很方便。具体操作为：选择图形对象（如图2-93），单击【格式】选项卡中的【对齐】按钮，其中对齐方式共有六种，即左对齐、水平居中、右对齐、顶端对齐、垂直居中、底端对齐，如果选择“底端对齐”，结果如图2-94所示。
图2-94 “底端对齐”的图形对象
如果需要对图形进行平均分布，选择【对齐】下拉菜单中的【横向分布】或【纵向分布】。
2）将一个形状变为另外一个形状
当用户希望能够将一个自选图形变成另外一个自选图形时，操作步骤如下。
（1）选择一个自选图形，单击【格式】选项卡中的【编辑形状】下拉菜单中的【更改形状】按钮。
（2）选择需要的形状，将原来的形状变为另一个图形，当自选图形变化时，其中原有的格式都会保留。
3）添加阴影和三维效果
为了美观，可以对自选图形使用阴影和三维效果，例如，为一个形状添加阴影和三维旋转，其操作为：选中该形状，单击【格式】选项卡中的【形状效果】按钮，再分别选择【阴影】和【三维旋转】进行设置。
其他设置阴影和三维效果的方法：右击图形，在弹出的快捷菜单中执行【设置形状格式】命令，弹出【设置形状格式】对话框，然后可以对自选图形添加阴影和三维格式。
3．使用其他图形类型
可以将许多类型的图形文件导入Excel工作表，向Excel工作表导入图形文件的方法有：执行【插入】选项卡中的【剪贴画】命令；执行【插入】选项卡中的【图片】命令；使用Windows的剪贴板复制并粘贴图形。
1）使用剪辑管理器
在Excel的剪辑管理器中，剪辑是指一个媒体文件，剪辑的类型包括图片、声音、动画或电影。具体操作步骤如下。
（1）单击【插入】按钮，选择【剪贴画】，窗口右侧出现【剪贴画】管理器。
（2）使用其中的搜索功能找到需要的剪贴画，单击该图片，工作表中即插入所选剪贴画。
如果要使用Office网上剪辑，需要连接到互联网。
2）插入图形文件
有时候用户希望能够在工作表中插入一些图形文件，具体操作步骤如下：单击【插入】按钮，选择【图片】，弹出【插入图片】对话框，找到需要插入工作表中的文件，单击【插入】按钮。
3）使用剪贴板复制图形
如果使用一个并没有存储在一个单独文件中的图形或遇到不能将图形文件导入Ex-cel时，可以使用剪贴板复制图形，例如，使用Windows的【画图】程序，执行【开始】→【程序】→【所有附件】→【画图】命令，启动画图程序后，选择图形，使用复制命令将图像复制到剪贴板。
2.4.2.4 添加文本框
下面介绍文本框的操作及特点。
1．使用【文本框】工具添加文本
单击【插入】选项卡中的【文本框】按钮。此时鼠标指针将变为十字形状。用鼠标将文本框拖动至所需的大小，然后输入文本。
如果要将文本框转变为【编辑形状】可选定相应的文本框，然后单击【格式】选项卡上的【编辑形状】按钮，在随后显示的菜单中指向【更改形状】，再指向其中的任一种分类，然后单击所需的最终选项。如图2-95所示。
图2-95 改变自选图形的形状
如果要在【自选图形】中添加文本，可单击需要添加文本的自选图形（线条，连接线和自由曲线除外），然后输入相应的文本。添加的文本将附属于相应的【自选图形】并作为该图形的组成部分。如果不需要直接在图形中添加文本，可以使用【绘图】工具栏上的【文本框】工具按钮添加说明文字。
2．文本框的特点
在Excel 2007中的文本框有如下特点：
（1）文本框不随文本的增加而自动增大。
（2）文本框可以放置在页面任意位置。
（3）文本框将像一般图形一样遮盖文字。
（4）在文本框中可以像在文字中一样进行格式编排以及插入图形、表格等对象。
（5）多个文本框可以连接在一起。
3．文本框的操作
文本框用于输入文本和对文本进行格式化，可以用于为工作表和图表添加注释性文字。并且常常与箭头和圆一起使用，指明信息所解释的对象。
当拖动鼠标在工作表中绘制完文本框后，文本插入点将在文本框中闪烁，准备接受用户输入的文本，文本框自动为文本换行。单击文本框，这时会出现如图2-96所示的对话框，可以设置文本框的形状、填充、形状轮廓、形状效果、文本填充、文本轮廓、文本效果、对齐等。
图2-96 设置【文本框】格式