Excel高级应用
作为Office高级应用教程，本节主要介绍Office2000套件中的电子表格Excel2000的高级应用方法，要求读者已经有相当的Excel应用基础。
3.2.1Excel基础
⒈表格数据
Excel表格的数据有手工输入（原始数据）和计算求得（加工数据）的两种。加工数据是利用简单的数学运算符号和Excel提供的函数，在单元格中执行运算得到的数据。
保证原始数据输入的正确性和计算公式/函数引用的正确性即可保证整个Excel电子表格数据录入的正确性。改变原始数据，加工数据将根据公式/函数自动更新。
在Excel中，不仅可以设置单元格数字格式的类型，可以设置单元格格式，可以对单元格进行保护（锁定或隐藏）；还可以使用条件格式使表格数据显示预警信息，也就是当数据值达到不同范围时以不同形式显示（比如不同颜色的字体），以示警告。
⒉建立图表
建立Excel图表是为了帮助用户分析表格数据，Excel提供了建立图表的图表制作向导，具体的步骤如下：
①选定制作图表的数据所在的单元格区域。
②单击“插入”下拉菜单的“图表”命令。
③出现图表制作向导对话框的4个步骤的第1步，根据需要选择图表类型，也可以自己定义图表类型，单击“下一步”按钮。
④接下来依次出现制作图表对话框的第2步、第3步和第4步，按照你的需要在各步中设定参数。
⑤单击制表向导的最后一步中的“完成”按钮，插入图表。
3.2.2分析图表数据
⒈从不同角度审视数据
在创建表格时一定要合理进行设计，因为所有表格数据组织都是为了分析数据，为管理活动提供有效的信息。制作图表是为了对数据分析提供帮助，为了得到更多的管理信息，常常还需要对图表数据转置阅读，从不同的角度审视同一组数据。图表数据依据图表数据源进行转置，即以相同的一组数据从不同方向取值成图。
为了从不同角度审视同一组数据，可以使用图表转置或表格转置两类方法。但是，表格转置后将导致诸如结构、格式、计算失误等一系列变化；而图表转置显得相对灵活、方便且转置后不会影响表格结构。所以，本书只介绍图表的转置。
图表转置方法如下：
①启动Excel，新建一张工作表如图3.42所示。
?
图3.42表格??????????????????????????图3.43图表
②选中表格数据区“A3:D6”，单击工具栏中的“图表向导”命令按钮，或单击“插入”下拉菜单中的“图表”命令，快速制作簇状柱形图（直方图）图表。
③在图表框内单击鼠标右键，在弹出的快捷菜单中选择“数据源”命令，打开数据源对话框，如图3.44。
④在数据源对话框中通过改变“系列产生在行”或“列”的设置来达到图表转置的目的。这里单击“列”单选按钮，“确定”即可实现图表转置。
?
图3.44数据源对话框???????????????????图3.45转置后的图表
以上方法通过快捷菜单实现图表转置，除此之外，还可以通过单击图表工具栏中的“按列”按钮或“按行”按钮实现图表转置（这里“按列”按钮呈按下状态，表示当前图表是以列为X轴的）。
为了从更多的角度审视同一组数据，可以将表格设置为三维视图效果，利用三维效果的不同视角（平视、仰视、俯视）、旋转特性，可以在角度的变化中透视数据间的内容。
2.图表类型与数据管理
在数据处理过程中不同的图表类型反映不同的管理目标，用直方图可以突出显示数据间差异（多少和大小）的比较情况；用线形图表可以分析数据间变化的趋势；用饼形图表可以描述数据间比例分配关系的差异。
以表3.1为例，从不同角度（管理目标）形成不同的图表以便于数据分析。
部门名称 一月 二月 三月 合计 产品一 35696 52467 45668 133831 产品二 45643 54112 44454 144209 产品三 5102 6198 7099 18399 合计 86441 112777 97221 296439 表3.1
（1）按一季度三个月份，比较三种产品的销售情况。
利用图表制作向导生成柱形图（按行，即系列产生在行）（图3.46）进行分析：如“产品一”在一月、二月的销售额居领先地位，产品三的销售额比较低。
（2）比较三种产品在一季度的销售额。
利用图表制作向导，或者直接在已生成的柱形图（按行）的图表中转置图表生成柱形图（按列，即系列产生在列）（图3.47）进行分析：如“产品三”在整个销售收入中所占比例较低。
?
图3.46柱形图（按行）???????????????????图3.47柱形图（按列）
（3）查看三种产品在一季度的销售额的变化趋势。
利用图表制作向导，或者直接在已生成的柱形图中改变图表类型生成数据折线图（图3.48）进行分析：如“产品三”呈现销售额上升的趋势。
?
图3.48数据点折线图（按行）???????图3.49堆积柱形图（按行）
（4）按月份查看销售情况。
利用图表制作向导或者直接在已生成的柱形图中改变图表类型生成堆积柱形图（图3.49）进行分析：如一季度中二月份销售业绩最佳，而“产品一”和“产品二”在整个销售额中所做贡献最大。
（5）查看一月份、三种产品销售额的比例分配关系。
利用图表制作向导或者直接在已生成的柱形图中改变图表类型生成分离形三维饼图（图3.50）进行分析：如在一月份中“产品一”和“产品二”是销售收入的主要来源，所占比例很大。
图3.50分离形三维饼图（按列）
3.比较两组数据
前面讲了同一组数据可以通过不同的角度和图表类型进行分析。现在要在同一个图表中利用次坐标轴比较两组数据（它们可以使用不同图表类型的组合）。分以下两个步骤来完成：
（1）向图表中添加数据组
某行业三家公司同一产品一季度的销售情况如图3.51中表格所示，且已利用表格数据绘制出柱形图（按行）图表，在图表的下方是“同业同期累计”。现在需要通过图表比较“同业同期累计”这组数据同三家公司的销售情况，所以将该组数据添加到图表中。拖拉选择“A20:E20”表格区域，再将选中的区域拖动至图表中，当鼠标指针右边出现加号时释放就可以将选中表格区域的数据组添加到图表中。
?
图3.51两组数据???????????????????????????图3.52添加数据组
（2）设置第2Y轴
当添加好数据组后，图表呈如图3.52的形式，在数据“同业同期累计”列上单击鼠标右键，在弹出的快捷菜单中选择“数据系列格式”命令。在“数据系列格式”对话框中单击“坐标轴”标签，设置“系列绘制在”/“次坐标轴”如图3.53所示，按“确定”按钮即可。
?
图3.53“数据系列格式”对话框??????????图3.54两组数据的图表
在图表中“同业同期累计”的系列上单击鼠标右键，在弹出的快捷菜单中选择“图表类型”命令，在弹出的对话框中设置需要的图表类型（这里设为“数据点折线图”）即可，如图3.54所示。
3.2.3应用公式与函数
1.公式引用
在单元格中使用公式计算的时候，必须输入以等于号“=”后跟具体计算公式/函数的数学表达式，按回车键Enter后，在活动单元格中得到公式/函数计算的结果。
由于在Excel中可以对单元格的公式进行复制（/移动），这就使得在设置单元格公式的时候要考虑到公式被复制给（/移动到）其它单元格的情况，也即公式中引用的值的地址表示方式，包括相对引用、绝对引用和混合引用三种。
相对引用指在公式移动或复制时，值地址相对目的单元格发生变化。由列名行号来表示，如B4。
绝对引用指在公式移动或复制时，值地址不随复制或移动的目的单元格的变化而变化。在列名行号前都加上$符号来表示，如$A$2。
混合引用指在公式移动或复制时，值地址的一部分为相对引用，一部分为绝对引用。如A$3,$B5。
2.函数与高级算法
在Excel中，录入单元格内容时输入“=”号，在“名字框”中就会列出函数供用户选择，可以单击函数右边的向下箭头在函数列表中选择不同的函数，对于一些不常用较复杂的函数可以通过单击函数列表中的“其他函数”命令，打开“粘贴函数”对话框（图3.55）选择更多的函数。在对话框中可以看到Excel为用户提供了不同类型的函数，在选择好函数名后，在列表框下面还有对函数的简单说明，如果对函数用法不太了解，可以单击对话框中的帮助按钮。
图3.55“粘贴函数”对话框
3.2.4表格数据筛选与排序
???筛选指按一定的条件从表格中提炼显示满足条件的数据，暂时隐藏不满足条件的数据。排序指的是将表格中的数据按指定列的数据（字母按顺序、数值按大小、时间按顺序）递增（升序）或者递减（降序）进行排列。下面分别介绍有关筛选与排序的实现方法。
1.自动筛选数据
执行筛选数据，先打开要筛选数据的工作表进入筛选清单环境。
（1）进入筛选清单环境
将活动单元格移动到工作表清单的任意位置，单击“数据”下拉菜单的“筛选”中的“自动筛选”命令，进入筛选清单环境。
（2）筛选清单
按“列”标记旁边的下拉箭头出现筛选条件列表，选择筛选条件（包括全部、前十个、自定义以及该列中的所有项等）。各个筛选条件的含义给大家做一个简单的介绍：
全部：此时筛选清单列出所有的记录。
前十项：列出表单中前十项数据，也可以自行设定列出的数据项数，比如说列出前20项，前35项等等。
自定义：可以打开“自定义”对话框，你可以设定组合的筛选条件。
如果在某列的下拉列表中选定某一特定的数据，则列出与该数据相符的记录，也就是说其列数据的数值等于选定的该列的数据的数值的所有记录将会被列出来。
2.高级筛选
用户在使用电子表格数据时，经常需要查询/显示满足多重条件的信息，使用高级筛选功能通过“筛选条件”区进行组合查询以弥补自动筛选功能的不足。
（1）设置筛选条件
“筛选条件”区其实是一张表格，在表格中同一行列出的条件是“与”的关系，不同行列出的条件是“或”的关系。
现有一张公司人员情况的表格，要求筛选出其中年龄小于40岁的男性研究生以及年龄在40岁以上但有大学本科学历的人员。
将筛选条件区输入到表格任一空白区域，这里为了方便查看将条件录入到表格的前三行（方法：先在表格第一行前连续插入三行，输入筛选条件如图3.56）。
?
图3.56筛选条件???????????????图3.57高级筛选条件对话框
（2）进行筛选
公司人员情况表中共有20个职员的情况记录，下面根据筛选条件进行高级筛选：
①拖拉选择整个人员情况表（表格区域“A4:G24”）。
②单击“数据”下拉菜单中“筛选”菜单的“高级筛选”命令，出现高级筛选对话框，参见图3.58。
③单击条件区域文本框右边的选择条件按钮，拖拉选择前面输入的筛选条件区域“A1:C3”，则文本框中出现条件区域，如图3.57，单击按钮，回到高级筛选对话框。
④在高级筛选对话框中确定好筛选方式、数据区域及筛选区域等条件（如图3.58）后，单击“确定”按钮即可完成筛选。筛选结果见图3.59。
?
图3.58高级筛选对话框?????????????????????????3.59筛选结果
（3）撤消筛选
通过以上步骤，筛选出6条符合条件的人员记录，其他的人员情况都被屏蔽掉了。为了再次显示所有的人员记录，需撤消筛选。方法很简单，单击“数据”下拉菜单中“筛选”菜单的“全部显示”命令即可。
3.数据排序
利用菜单功能进行排序步骤如下：
①选定所要排序的表格数据；
②单击“数据”下拉菜单的“排序”命令。
③在弹出的“排序”对话框中（如图3.60所示）设定排序方法，排序的时候可以设定主要、次要、第三关键字，即按多重条件进行排序，每个关键字排序的时候可以是递增或是递减。
④单击“确定”按钮进行排序。
?
图3.60排序对话框???????????????????图3.61排序警告
另外，还可以利用工具栏中的排序按钮进行排序，选中要进行排序的列，单击按钮降序排列，单击按钮升序排列。
在进行排序时，如果选定的排序区域不够完整，Excel会进行排序警告。比如在上一小节的人员情况表中选定年龄列进行排序，则Excel会弹出如图3.61所示的警告对话框请用户进行选择。如果选择“以当前选定区域排序”，则排序的结果只调整当前选定的“年龄”列的顺序，而不是按年龄大小排列各个人员的情况记录，所以应该“扩展选定区域”。
3.2.5统计分析表格数据
1.分类汇总
在对数据进行分类汇总之前，应该将数据整理规范。比如在大发公司销售人员业绩统计表（见图3.62）中按照销售的日期进行销售情况的录入，则同一个销售人员可能出现在分散的几行或者说几条记录中。为了汇总同一个销售人员的销售业绩，需要先将这些记录进行整理使同一销售人员的销售记录紧接在一起。最方便快捷的方法就是对这些表格数据（记录）进行排序。
?
图3.62业绩表?????????????????????图3.63分类汇总对话框
整理好表格数据（在业绩表中按销售人员姓名进行排序）之后，就可以进行分类汇总，方法如下：
①选定表格区域“A3:C19”。
②单击“数据”下拉菜单中的“分类汇总”命令。
③出现“分类汇总”对话框，在对话框中设定汇总的方式及汇总参数，参见图3.63（按照姓名字段对同一个人的销售额进行求和方式的汇总）。
④单击“确定”按钮完成分类汇总的工作，汇总结果如图3.64。
2.数据透视
数据透视功能通过重新组合表格数据并添加算法，快速提取与管理目标相应的数据信息进行深入分析。如图3.65为某公司在2003年国庆期间（10月1日至10月7日）的加班时间统计表。现在想查看一下每个员工从10月1日至10月7日的加班情况及加班的总时间。步骤如下：
?
?图3.64汇总结果表???????????????????????图3.65加班时间统计表
（1）建立透视表报告
①选定表格区域“A3:D16”，单击“数据”下拉菜单中的“数据透视表和图表报告”菜单项命令。
②在“数据透视表和图表报告”向导的步骤1中使用默认设置（“请指定待分析数据的数据源类型”为“MicrosoftExcel数据列表或数据库”，“所需创建的报表类型”为“数据透视表”）。
③在“数据透视表和图表报告”向导的步骤2中指定数据源区域（由于已事先选定了表格区域，这里向导会自动设置成选定的表格区域）。
图3.66新工作表
④在“数据透视表和图表报告”向导的步骤3中单击“完成”按钮（“数据透视表显示位置”默认设置为“新建工作表”）。出现如图3.66所示的新工作表。
（2）在数据透视表中重组数据关系
在如图3.66所示透视表中根据需要拖动“数据透视表”窗体中相应的字段到合适的位置即可实现表格数据的合理重组，步骤如下：
①拖动字段到“请将行字段拖至此处”的列区域，拖动字段到“请将列字段拖至此处”的行区域，拖动字段到“请将数据项拖至此处”的区域。
②在拖动好数据项之后，透视表呈现如图3.67所示的形式，并且统计出了按人员和按日期的总的加班时数。
???图3.67数据透视表
（3）透视分析数据
为了更清晰的查看数据透视结果，可以通过数据透视表中“姓名”字段和“日期”字段右侧的下拉箭头，打开人员姓名列表和加班日期列表锁定你需要查看的人员或是日期的加班时数情况进行数据分析。注意：列表中复选项前面打勾选中的表示要显示的项，否则表示该项不显示。
同普通电子表格数据一样，可以利用数据透视表生成图表，或者也可以在“数据透视表和图表报告”向导步骤1中选择“所需创建的报表类型”为“数据透视图（及数据透视表）”建立透视图表以便更为直观的分析。
生成数据透视表之后，利用数据透视表生成图表，单击“数据透视表”窗体（如图3.68）中的图表向导按钮即可生成数据透视图表。在图表中单击鼠标右键利用“图表类型”命令将图表设置成你所需要的类型（如“堆积柱形图”，见图3.69）即可。
在数据透视图表中，也可以通过“姓名”字段和“日期”字段右侧的下拉箭头，打开人员姓名列表和加班日期列表锁定需要查看的人员或是日期的加班时数情况进行数据分析。
图3.68“数据透视表”窗体
图3.69数据透视图表
?
|
|
