4.1 多工作表的数据汇总与分析

|第4章|

巧用辅助列

制作灵活多变的饼图

在日常工作中，我们建议大家尽可能地将数据源汇总在同一张工作表中，进行原始数据记录的管理。然而在实际工作中，因为个人制表习惯的不同，有时会将相同结构的数据分散到多个月份中进行记录。比如：将各部门、各月度的费用支出情况分散在多张以月份命名的工作表中。那么在制作年度费用的支出与预算对比情况时，这就比较麻烦了。首先要对数据源的多张表格进行汇总，然后进一步地查找各项目的预算情况，再做对比并制作商务图表。不仅如此，在实际业务中可能还要统筹地去展示整个公司的预算使用率等。在这样的商务图表应用环境中，如果单纯地基于原始数据表格，是无法有效完成数据的统计工作的。因此在图表的制作过程中，我们会根据图表呈现的需求，制作辅助数据源。尤其是饼图、圆环图的制作，会使用大范围的辅助数据作为制图数据源，以实现图表高质量呈现的目标。

4.1

多工作表的数据汇总与分析

在数据源（见图4-1）中，分4张工作表列示了各部门、各类费用的支出情况。对于只具备一行标题行、最左侧一列标题列的数据源汇总，可以采用Excel合并计算的功能，快速实现多工作表的汇总与计算。而超出该要求的复杂表格，在进行合并计算和数据分析时，还将用到SQL、VBA等高阶工具。

图4-1

下面我们来看看合并计算在制作多工作表的数据汇总与分析方面的具体使用方法。

①打开示例文件，在【数据】选项卡的【数据工具】功能组中单击【合并计算】按钮，在弹出的【合并计算】对话框中，在【函数】中选择【求和】，在【引用位置】处选中1～4表中的数据源区域，并依次添加。可在【所有引用位置】处浏览所添加的数据源汇总情况，勾选【标签位置】中的【首行】和【最左列】（见图4-2）。

图4-2

②双击【开始】选项卡中的【格式刷】按钮，启用重复使用格式刷的功能。将表1～4中的格式样式“刷”到汇总合并计算的工作表中（见图4-3）。

③在F1单元格中输入“小计”，在A27单元格中输入“总计”，然后选中A1：F27单元格区域（所有数据源区域），按Alt+=组合键进行快速求和（见图4-4）。

④查找各部门的预算值。在G1单元格中输入“预算值”，在G2单元格中输入公式=VLOOKUP（A2，部门预算！$A$2：$B$26，2，0），并双击单元格右下角的十字句柄，使其快速填充至整列，即可将部门预算表中该部门对应的预算值查找后的结果填写到目标位置中，如G列（见图4-5）。

⑤在H列新建一列预算判断的列，其公式=G列的预算值-F列的费用发生小计值。设置完成后，对该列的单元格显示格式进行进一步设置：选中H2：H26单元格区域，单击鼠标右键，选中【设置单元格格式】，在弹出的【设置单元格格式】对话框的【数字】选项卡中选择【自定义】，在【类型】中输入“＂预算内＂；[红色]＂超预算＂；＂＂；”即可（见图4-6）。

图4-3

图4-4

图4-5

图4-6

⑥为数据源设置条件格式。继续选中H2：H26单元格区域，单击【开始】选项卡，选择【条件格式】→【图标集】→【标记】中相应的图标集（见图4-7）。

图4-7

⑦默认生成的图标集规则并不匹配数据源中数值显示的要求，需要进一步设置。此时继续选中H2：H26单元格区域，单击【开始】选项卡中的【条件格式】，之后单击【管理规则】，在弹出的【条件格式规则管理器】对话框中选中第一个图标集规则后，单击【编辑规则】按钮（见图4-8）。

图4-8

⑧修改图标集的规则如下（见图4-9）。

图4-9

图标：黄色感叹号，当值是【＞0】时，类型为数字。

图标：无单元格图标，当值是【＜1】且【＞=0】时，类型为数字。

图标：红色×号，当值是【＜0】时，类型为数字。

⑨在H27单元格中输入公式=SUM（H2：H26），并将显示样式修改为【常规】（效果参见图4-10）。

图4-10

至此，已经完成在表格内汇总并比较预算的执行情况一览表。下面根据实际费用与预算的情况来制作预算执行率统计图，效果如图4-11所示。在图4-11中可见，实际发生值99%用蓝色圆环图呈现，未完成的差额部分1%用橘黄色的细圆环图标识在内圈，呈现出一种“剑”与“剑鞘”套叠在一起的效果。制作方法如下。

图4-11

①按Ctrl键，依次选中F27和H27单元格，单击【插入】选项卡中的【饼图】进行设置（见图4-12）。

②选中图表区域后，单击【设计】选项卡【数据】功能组中的【选择数据】，在弹出的【选择数据源】对话框中单击【添加】按钮，在弹出的【编辑数据系列】对话框的【系列值】中添加F27和H27两个单元格（见图4-13）。

图4-12

图4-13

③依次重复上一步骤，即在数据源中添加了4个一模一样的数据源后，单击【确定】按钮（见图4-14），在图表区域中便出现了4层圆环图。

图4-14

④在【格式】选项卡下利用【形状填充】和【形状轮廓】进行圆环图各元素的颜色设置，将图表设置为如图4-15所示的配色方案，即可将图表设置为具有剑鞘样式的图表类型。注意，差额1%的部分，实际上把最外层和最内层的圆环设置为无填充颜色、无边框填充的效果了。

图4-15

⑤为图表添加数据标签。选中图表区域中的圆环图以后，单击鼠标右键，选择【添加数据标签】→【添加数据标签】，并在右侧的【设置数据标签格式】窗格中将【标签选项】修改为【百分比】。设置完成后，调整标签的位置、字体大小等，即可完成图表设计（见图4-16）。

图4-16