分类：数据分析篇

图6.126中包含某公司10名职工在4个季度的销量数据和退货数据，是否有办法对这4个季度的数据执行汇总？而且后期可以随时调整汇总的方式及汇总的对象。

图6.126 待汇总的销量与退货表

解题步骤

案例141的思路只能应付单个区域的数据汇总，对于本例的需求，要使用多重合并计算数据区域的透视表，具体操作步骤如下。

1.按组合键＜Alt+D+P＞“数据透视表和数据透视图向导”对话框，按图 6.127 所示的方式设置选项，然后单击“下一步”按钮，弹出如图6.128所示的对话框。

2.选择“自定义页字段”按钮，设置界面如图6.128所示，然后单击“下一步”按钮，弹出如图6.129所示的对话框。

图6.127 选择透视表的数据源类型

图6.128 设置页字段数量

3.在图 6.12 所示的对话框中单击浏览按钮（图标为），然后选中一季度工作表中的A1：C11区域，并将它添加到下方的“所有区域”中。

4.继续添加其他3个工作表的数据源，效果如图6.130所示。

图6.129 添加第一个数据源

图6.130 添加所有数据源

5.将对话框中间的页字段数目由默认的 0 修改为 1，然后选择列表中的“一季度！$A$1：$C$11”，并在下方将字段名称修改为“一季度”，效果如图6.131所示。

图6.131 修改区域对应的字段名称

6.继续修改其他3个区域的字段名称（选择“二季度！$A$1：$C$11”，并在下方将字段名称修改为“二季度”；选择“三季度！$A$1：$C$11”，并在下方将字段名称修改为“三季度”……），然后单击“下一步”按钮，弹出透视表的向导3对话框。

7.在该对话框中不做任何修改，直接单击对话框中的“完成”按钮，此时 Excel 会自动新建一个工作表，并在表中生成如图6.132所示的透视表。

图6.132 多重合并计算数据区域的透视表

图6.132是透视表的默认效果，对4个季度的退货量与销量进行合计，用户可以随意修改汇总的方式，如改为计算平均值、计算最大值等；也可以随意删除某个季度的数据，让它不参与计算。

例如，不计算三季度的数据，那么单击A2单元格的倒三角按钮，然后在弹出的对话框中取消选择“三季度”复选框即可，操作界面如图6.133所示。

图6.133 不计算三季度的数据

知识扩展

1.多重合并计算数据区域的透视表和通过单区域生成的透视表有所不同，操作时的步骤不同，生成的透视表显示效果也不同，对数据源的要求也不同。

2.多重合并计算数据区域的透视表的页字段主要用于筛选数据源，控制哪些工作表的数据可参与汇总，默认状态是所有数据都参与汇总。

3.多重合并计算数据区域的透视表还可以对多个不同工作簿的数据执行汇总，操作方法和本例一致。本例中选择同工作簿的多个工作表从而产生数据源，跨工作簿汇总时选择多个工簿中的待汇总区域即可。

在工作中，时常会遇到领导要求对工作表中某数据汇总的需求，但是当你提供汇总表后，领导临时决定要看看另一项数据的汇总，此时只能重新设计一个汇总表。更让人揪心的是还有可能突然要求再对另一项数据汇总，或者修改汇总方式，如将求和改成求平均。

正如图6.119所示的工资表，领导有可能随时要求查看加班时间汇总，也有可能要求查看奖金或实发工资的汇总。面对这种随时可能变化的需求，要如何才能快捷、轻松地实现呢？

解题步骤

Excel提供了多种汇总工具，其中数据透视表是最灵活的汇总工具，可以随时切换汇总方式，往往用鼠标单击两三次就能实现新的汇总需求，不需要重新做一份汇总表。具体的操作步骤如下。

1.单击A1单元格，然后单击功能区的“插入数据透视表”，弹出“创建数据透视表”对话框。

2.在“创建数据透视表”对话框中保持默认设置，包含透视表的数据源和透视表存放位置，直接单击“确定”按钮，进入下一步界面，如图6.120所示。

图6.120 设置透视表来源和存放位置

此时在工作表中已经产生了空白的透视表，只要根据需求指定字段位置即可实现汇总。

对于不同的汇总需求，字段的设置方式也不同，数据透视表允许用户随意拖动字段位置，从而改变汇总对象。

以按部门统计工资合计为例，执行以下步骤即可。

3.在工作表右方的“数据透视表字段”窗口中将“部门”拖到下方的行字段中，将“实发工资”拖到值字段中，此时工作表中的透视表会自动按部门对实发工资汇总，效果如图6.121所示。

图6.121 按部门对实发工资汇总

以上两次拖动完成了按部门对实发工资汇总，如果将需求改为按部门统计加班费，那么可按以下步骤操作。

4.将值字段中的“求和项＂实发工资＂”拖到工作表中，从而删除此求和项，然后将上方的“加班费”拖到值字段中，此时工作表中的透视表将会由汇总实发工资切换为汇总加班费，全程操作仅需2秒钟，汇总结果如图6.122所示。

图6.122 按部门汇总加班费

如果将需求改为按职务统计加班时间的平均值，那么可按以下步骤操作。

1.将行字段中的“部门”拖到工作表中，从而删除此字段，然后将值字段中的“求和项：加班费”也拖到工作表中。

2.在“数据透视表字段”窗口中将“职务”拖到行字段中，将“加班时间”拖到值字段中，此时透视表的汇总结果如图6.123所示。

图6.123 按职务汇总加班时间

由于实际需求是计算加班时间的平均值，因此还需要执行下一个步骤。

3.选择B9单元格，单击右键，从右键菜单中选择“值汇总依据”→“平均值”，此时透视表将变成按职务计算平均加班时间，显示效果如图6.124所示。

图6.124 按职务计算平均加班时间

如果此时将需求修改为同时计算加班时间和加班费的平均值，那么可按以下步骤操作。

在“数据透视表字段”窗口中将“加班费”拖到值字段中，然后右键单击C9单元格，从右键菜单中选择“值汇总依据”→“平均值”，此时透视表将显示为图6.125所示的效果。

图6.125 统计加班时间和加班费的平均值

以上所有操作可以证明通过透视表汇总数据足以应付灵活多变的需求，仅两三秒、拖两三次鼠标就能完成，不仅提升了工作效率，而且不会再因领导的需求变化而焦头烂额，还可以展示自己的制表才能。

知识扩展

1.使用透视表汇总数据的优点主要体现在三个方面：其一是不会破坏数据源格式（使用分类汇总工具汇总数据时会破坏数据源格式）；其二是支持多种汇总方式，包含求合计、求平均、求最大值、最小值和乘积；其三是在多种汇总方式之间切换仅需两三秒钟。

2.透视表的使用步骤比较简单，选择数据、单击菜单、设置字段，三个步骤加起来通常可几秒钟完成，其中重点在于设置字段。同一个字段放在不同地方会有不同的汇总结果，当多个字段名称放在一起时，字段名称的顺序也相当重要，会直接影响汇总结果。

3.当汇总方式由求和改为求平均后，往往会产生多位小数，影响透视表的美观，而且汇总表本身也不需要精确到那么多位小数，此时最好选择所有数据，然后将单元格格式设置为“0.00”。

图6.116中包含2014年全年和2015年1月到4月的销量，是否可以根据现在的数据预测未来8个月的数据发展趋势呢？

解题步骤

Excel提供了预测工作表的工具，可根据现有数据预测未来的发展趋势，操作步骤如下。

1.选择A2：B25区域。

2.单击功能区的“数据”→“预测工作表”，弹出“创建预测工作表”对话框。

3.将“预测结束”的时间由默认值改为 2015/12/1，然后单击“创建”按钮，从而创建趋势表，设置界面如图6.117所示，图6.118则是预测结果。

图6.117 修改预测结束时间

图6.118 预测结果

知识扩展

1.预测工作表工具是Excel 2016独有的功能，2016以下版本不支持。

2.预测数据的发展趋势是依照当前数据的规律来判断的，判断结果只能参考，不能作为结果，因为工作中很多事物并非都遵照规律发展，万事都有例外。

3.要预测数据，数据源必须符合两个规则：数据源的左边一列需要是等比的日期，如都间隔一日、间隔一周或间隔一月；数据源的右边需要是数值，不能是文本，文本不存在发展趋势。

图6.107所示的表中包含需要装车的产品明细，但是车子限载1279公斤，只能小于等于1279公斤，不能超重。现在的问题是：要将哪些产品装上车、每个产品装多少件，才能既不超载又确保剩下的货品最少？

解题步骤

本例的需求只能用规划求解完成，不过相对于以往的案例，本例更复杂一些，需要使用更多的约束条件，具体操作步骤如下。

1 .删除 C 列的数量，然后在 D1 中输入“剩余”，在 D2 中输入公式“=1279-SUMPRODUCT（B2：B7，C2：C7）”。由于此时所有产品的数量都是 0，因此剩余总量是1279公斤，公式效果如图6.108所示。

图6.108 设置公式

2.单击功能区的“数据”→“规划求解”，弹出“规划求解参数”对话框。

3.将目标设置为 D2，然后选中其下方的“最小值”，表示当 D2 单元格的值最小时停止求解。

4.将可变单元格设置为C2：C7，设置界面如图6.109所示。

图6.109 设置目标和可变单元格

5.单击右方的“添加”按钮，然后添加约束条件为 D2 大于等于 0，设置界面如图 6.110所示。

图6.110 添加约束条件1

6.继续单击“添加”按钮，然后添加两个约束条件，包含C2：C7区域大于等于0、C2：C7区域必须是整数，设置界面如图6.111和图6.112所示。

图6.111 添加约束条件2

图6.112 添加约束条件3

7.单击“添加”按钮，然后添加C2：C5小于等于4、C6：C7小于等于2这两个条件，设置界面如图6.113和图6.114所示。

图6.113 添加约束条件4

图6.114 添加约束条件5

8.返回“规划求解参数”对话框，单击“求解”按钮，当 Excel 弹出“规划求解结果”对话框时，不做任何设置，单击“确定”按钮关闭对话框，此时在工作表中将会看到图6.115所示的求解结果。

图6.115 规划求解结果

图6.115表示1件产品A、1件产品B、4件产品D的总重量等于1278公斤，与可装载上限1279仅差1公斤，这是最优的装车方式。

知识扩展

1.本例是本书中几个规划求解应用里最复杂的一个。由于在需求中限制了每样产品的数量，因而在设置规划求解的参数时也必须一一限制其范围——大于等于0且小于等于4，或者大于等于0且小于等于2。

2.当然，也可以按前面案例中讲的步骤操作，先设置目标值等于 0，如果找不到解再设置为目标最小值；还可以在E1单元格设置公式“=D2*1000”，然后将目标设置为E2单元格最小值，而非D2，从而提升求解的精确度。

3.设置规划求解参数时，一定要考虑全面，将所有可能性都考虑到，参数越多，得到的解越精确，但同时运算时间也会相应地延长。

公司有一钢材长4759mm，实际工作中需要用到图6.100中A2：A21的20种规格的材料，现在的问题是如何切割这根钢材，使其刚好满足需求，又不浪费材料。

解题步骤

钢材切割思路有两种：一是每种规格的材料只生成一件，组合长度等于 4759，用程序语言来描述就是“计算 A2：A21 中哪些单元格的值相加刚好等于 4759”；二是不限制每种规格的产品数量，只要满足“不浪费材料”这个需求即可。两种切割方式的实现方式不同。

此外还要注意一点，如果没有等于4759的值，那么找出合计最接近4759的值。

实现此需求的唯一工具是规划求解，以切割方式为例，具体操作步骤如下。

1.在D1中输入“剩余材料”，在D2中输入公式“=C2-SUMPRODUCT（A2：A21，B2：B21）”。

2.单击功能区的“数据”→“规划求解”，从而弹出“规划求解参数”对话框。

3.将目标设置为D2，然后在“最大值”、“最小值”和“目标值”3个选项中选择“目标值”，且在右方输入“0”，最后将可变单元格设置为B2：B21，设置界面如图6.101所示。

图6.101 设置目标和可变单元格

4.单击右方的“添加”按钮，从而弹出“改变约束”对话框，然后按图6.102所示的方式设置选项，表示B2：B21区域只能产生二进制的值。

图6.102 设置约束条件

5.单击下方的“求解”按钮，当弹出“规划求解结果”对话框时直接单击“确定”按钮关闭对话框，然后在工作表中将产生图6.103所示的求解结果。

图6.103 规划求解结果

图6.103表示A9、A12、A13、A14、A15和A17的数值合计等于4759，应将钢材切割为957+945+670+928+620+639。

假设切割时不限制同一个规格的产品数量，那么应按以下步骤操作。

1.单击功能区的“数据”→“规划求解”，从而弹出“规划求解参数”对话框。

2.删除对话框中的约束条件，然后重新添加图6.104和图6.105所示的两个条件。

图6.104 添加约束条件一

图6.105 添加约束条件二

3.返回“规划求解参数”对话框，单击下方的“求解”按钮，当弹出“规划求解结果”对话框时直接单击“确定”按钮关闭对话框，然后在工作表中将产生图6.106所示的结果。

图6.106 规划求解结果

图6.106表示A15+A16+A17×4+A20刚好等于4759，此切割方式允许某个规格的产品切割多件。当然，如果需要也可以设置上限，如每种规格的产品不超过5件等，可以自行设置约束条件。

知识扩展

1.本例中要求“没有等于4759的值时，找出合计最接近4759的值”，由于已经找到了合计刚好等于4759的组合，因此不再测试合计最接近4759的组合。

2.求解过程就是逐一测试从而找到最优解的过程，数据量越大则运算过程越久，本例有可能用5秒钟也可能用半分钟以上，视计算机硬件的优劣而定。

3.本例中用到了bin二进制的数据，其实就是0和1，当B2：B21限定为二进制数据时，该区域只能产生0或1，当值为1时，表示该值为本例的解。

4.事实上本例有多组解，使用不同计算机执行以上步骤有可能得到不同的解。A12+A16×2+A17×2+A21，即945+862×2+639×2+812也等于4759。

XX 小卖部去进货，原本打算购买图 6-93 所示的产品。突然发现带的现金不够，身上只有1000 元钱，因此只能购买部分产品。现要求计算购买哪些产品、每个产品多少件才能刚好花完这1000元钱。

假设没有任何商品组合等于1000元，一定会产生零钱，那么应尽可能让剩余的零钱更少。

解题步骤

此问题属于通过规划求解获取最优解的应用，步骤如下。

1.在 C1 中输入“购买数量”，在 A8 中输入“总价格”，然后在 A8 单元格中输入公式“=SUMPRODUCT（B2：B6，C2：C6）”，此公式用于计算购买产品的总价格，效果如图6.94所示。

图6.94 补充购货表的数据与公式

2.单击功能区的“数据”→“规划求解”，弹出“规划求解参数”对话框。

3.将目标设置为B8，将可变单元格设置为C2：C6，设置界面如图6.95所示。

图6.95 设置目标和可更改单元格

4.单击右方的“添加”按钮，弹出“添加约束”对话框，然后按图6.96所示的方式设置条件。由于总资金只有1000元，因此B8单元格的值只能是小于等于1000的最大值。

图6.96 设置第一个约束条件

5.单击“添加约束”对话框中的“添加”按钮，继续添加图6.97和图6.98所示的两个条件，其功能是限制C2：C6区域只能生成大于等于0的整数。

图6.97 设置第二个约束条件

图6.98 设置第三个约束条件

6.返回“规划求解参数”对话框，单击“求解”按钮，当弹出“规划求解结果”对话框时直接单击“确定”按钮关闭对话框，然后在工作表中将产生图6.99所示的结果。

从表中可以看出A产品买3件、B产品买1件、C产品买2件、D产品买10件、E产品买11件，其价格之和等于999元，剩下1元，符合实际需求。

图6.99 求解结果

知识扩展

1.产品的购买数量一定是正整数或 0，因此在“规划求解参数”对话框中一定要添加两个条件来约束C2：C6区域的值，否则无法得到正确的解。

2.本例需求是“……假设没有任何商品组合等于1000元，一定会产生零钱，那么应尽可能让剩余的零钱更少”，翻译为程序语言其实就是“小于等于1000的最大值”，因此本例在设置条件时将目标的值设置为“最大值”，然后添加约束条件“＜=1000”。