Excel数据汇总示例及技巧

在Excel中进行数据处理时，数据汇总是日常的操作内容。通过SUM函数进行无条件汇总是用户易于理解和常用的方法。但当面临各种限制条件的汇总时，则其甚为棘手。本节将介绍满足多条件的数据汇总方式，包括分类汇总和函数汇总方式。它们各有应用情形，用户可相机选择。

6.4.1.1　函数汇总

本教材第三章讨论过统计汇总的相关函数的语法格式及作用，本节只对其用于汇总的方法作进一步的示例和说明。

1.利用单条件统计函数实现单条件汇总

SUMIF（）、COUNTIF（）和AVERAGEIF（）的功能分别是根据指定条件对若干单元格求和、计数和计算均值。

【例6-12】　图6-19中左边区域为某公司的销售合同及其履行记录，右边P2:R19是用上述函数统计出的对各省的合同金额、合同数及平均每笔合同的销售金额。要实现以上功能，其操作步骤如下。

图6-19　利用函数实现单条件汇总示例

Step 1：按图6-19的格式设计表格，并输入相关文字。

Step 2：在P2中 输 入 公 式“=SUMIF（A:A,O2,G:G）”，在Q2中输入公式“=COUNTIF（A:A,O2）”，在R2中输入公式“=AVERAGEIF（A:A,O2,K:K）”。

Step 3：选中P2:R2，将鼠标置于单元格R2右下角，此时出现黑十字架，双击鼠标左键，实现向下填充，即求出对各省的合同金额、合同数及平均每笔合同的销售金额。

2.利用多条件统计函数实现多条件汇总（图6-20）

图6-20　利用函数实现多条件汇总示例

SUMIFS（）、COUNTIFS（）和AVERAGEIFS（）的功能分别是根据指定条件对若干单元格求和、计数和计算均值。其功能类似于用SUM、COUNT、AVERAGE与IF的嵌套计算，限于篇幅及实用性，本篇只讲前者。

【例6-13】　仍以图6-19中左侧的数据为例。若要统计对不同省份、不同产品规格的销售额，其操作过程如下。

Step 1：按图6-19的格式设计表格，并输入相关文字。

Step 2：在P2中输入公式“=SUMIFS（$K$2:$K$70,$A$2:$A$70,$O2,$E$2:$E$70,P$1）”。

Step 3：按住P2单元格右下角的填充柄，拖曳至U2单元格；再按住U2单元格右下角的填充柄拖曳至U19单元格即得到全部统计数据。

若要计数、求均值，则分别用COUNTIFS（）和AVERAGEIFS（）即可，其操作过程与方法同上。

3.利用SUMPRODUCT函数实现多条件汇总

SUMPRODUCT函数的功能是计算几个数组间对应元素乘积之和。

【例6-14】　同例6-12的数据和要求。除Step 2中，在P2单元格输入的公式为“=SUMPRODUCT（（$A:$A=$O2）*（$E:$E=P$1）,$K:$K）”，在确认时按“Ctrl+Shift+Enter”组合键外，用SUMPRODUCT函数进行操作的做法与例题6-12一致。但这种方式只适用于求和汇总方式，不能用于计数和求均值。

4.利用数据库函数实现多条件汇总

数据库函数DSUM、DCOUNT、DAVERAGE、DMAX等也可以用来实现多条件求和、计数、求均值、最大值等形式的汇总。只是在应用其实现多条件汇总时要单独设立条件区。

【例6-15】　仍以图6-19中的数据为例。要求汇总“用户名称”为“甘肃省”，“产品规格”为“ABS-QQ-128”的销售额。则只需要在单元格P2中输入公式“=DSUM（A:M,K1,O4:P5）”，单击【确认】即可（图6-21）。

图中O4:P5区域为设立的条件区，第一行为汇总字段，其后各行为满足的条件。条件区各行的意义与自定义筛选的条件框一致，同行为与条件，不同行间为或条件，条件值可以为空，也可以用通配符“?”和“*”。

图6-21　用DSUM（）函数对数据库表格汇总

6.4.1.2　分类汇总

分类汇总是Excel中常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。为了避免分类汇总计算的错误，在执行分类汇总命令时，应将数据清单中的记录按某一关键字进行排序，然后才能根据此关键字进行汇总。

1.简单的分类汇总

【例6-16】　以例6-7的数据为例。要求采用分类汇总的方法，计算出不同运货商的订单数、运货费用。其操作过程如下。

Step 1：单击数据区任一单元格→单击数据标签中的升序按钮，把数据表按照“运货商”进行排序（图6-22）。

图6-22　数据表按分类字段排序

Step 2：在数据标签中，单击分类汇总按钮。在这里的分类字段的下拉列表框中选择分类字段为“运货商”→选择汇总方式为“计数”→汇总项选择“订单ID”→单击【确定】（图6-23左“分类汇总”对话框）。

Step 3：在数据标签中，单击分类汇总按钮。在这里的分类字段的下拉列表框中选择分类字段为“运货商”→选择汇总方式为“求和”→汇总项选择“运货费”→取消【替换当前分类】前面的默认勾选→单击【确定】（图6-23右“分类汇总”对话框）。

在分类汇总中我们的数据是分级显示的，现在工作表的左上角出现了这样的一个区域，单击3的结果如图6-23中下总表所示。单击4，则显示所有的内容。

Step 4：复制汇总结果。按“Alt+；”组合键选取当前屏幕中显示的内容→复制→粘贴到指定位置。

2.删除分类汇总(www.xing528.com)

如果要删除分类汇总，可单击【数据】→【分级显示】→【分类汇总】→【全部删除】。此时，数据清单恢复成原来的初始状态。

3.多重分类汇总

如果需要，还可以在上述简单分类汇总的基础上再创建二级、三级乃至多级的分类汇总。多级分类汇总的关键是正确的分类汇总次序，即先按主要关键字段建立第一级分类汇总，再按次要关键字建立二级分类汇总，照此类推。在分类汇总前必须按主要关键字、次要关键字等排序。

图6-23　数据分类汇总

【例6-17】　如上例资料。如果除上述方式汇总之外，在此基础上还要求按“雇员ID”进行汇总。其操作过程如下。

Step 1：删除例6-16的分类汇总。

Step 2：单击数据区任一单元格→单击数据标签中的升序按钮，把数据表按照“运货商”“雇员ID”进行排序。

Step 3：按主关键字段“运货商”建立第一级分类汇总，方法同例6-16。注意在汇总方式上的选择，“订单ID”和“运货费用”分别以计数和求和的方式汇总。

Step 4：按次要关键字段“雇员ID”建立第二级分类汇总，方法同上。只是需要取消【替换当前分类汇总】的勾选。这样，就按“运货商”“雇员ID”分组进行二级分类汇总，结果如图6-24所示。

图中数据是级别按钮的汇总数据，如果单击其他级别按钮，则会出现交叉汇总数据。如单击则只显示按“运货商”分组的汇总结果。

6.4.1.3　特定情形的汇总

有时我们需要对数据作时时累计汇总，或者使汇总数据保持随时更新状态，以及数据不在连续区域等情况进行汇总时，就需要灵活地运用格式与函数。

1.数据的累加汇总

在日常工作中，常常需要将一列数据从上到下或者从左至右进行累加汇总。需要汇总之行或列并不固定，用户大多意在观察其随时间变化的情况。这类问题可用SUM、COUNT和AVERAGE等统计函数解决，但在单元格格式上需要作一些设置。通常第一个单元格格式设为绝对引用，第二个单元格格式应为相对引用。

图6-24　二级分类汇总结果

【例6-18】　如图6-25所示，左表是某公司甲原材料入库和出库统计情况，现需要在F列求出库存数量，在G列算出累计余额；右边是累计情况表，要求随时算出累计入库和累计出库数量和金额。该公司原材料领用采用移动平均法计价，并要求随时结出数量及金额。

图6-25　数据向下累计汇总示例

该问题就属于上面所说的累计加总。要实现用户目标，其操作过程如下。

Step 1：在E4、F4和G4单元格里分别输入公式“=C4/B4*D4”“=B4-D4”“=C4-E4”；在E5、F5和G5单元格里分别输入公式“=（G4+C5）/（B5+F4）*D5”“=F4+B5-D5”“=C5+G4-E5”。

Step 2：选中E5:G5，用鼠标按住G3右下角黑十字，一直向下拖曳到能满足用户需要的行号为止，完成数量金额账的计算。这样平日只要记录了出入库的数量和金额，其余额自动填入。

Step 3：在单元格J4、K4、L4、M4、N4和O4，分别输入公式“=SUM（$B$4:B4）”“=SUM（$C$4:C4）”“=SUM（$D$4:D4）”“=SUM（$E$4:E4）”“=J4-L4”“=K4-M4”。选中J4:O4，用鼠标按住O4右下角黑十字，一直向下拖曳到能满足用户需要的行号为止。这样，就得到每天的累计入库、出库及余库存的数量及金额数。

2.动态更新区域的数据汇总

有时，我们需要面对一个不断更新的数据源进行动态数据汇总。比如求出到某日或当天为止某指标的累计数，此时可用SUM或SUMIF函数来实现。

【例6-19】　如图6-26所示，A:D区域为某公司丙产品的销售量和销售额的每日记录，现在需要在F1中动态显示当天日期，然后在G2:G5中显示当天的销售量和销售额的当天报告数和累计报告数。实现上述目标的操作步骤如下。

Step 1：采用指定名称的方法，单击【公式】→【定义的名称】→【根据所选内容创建】→【首行】，分别将A～D列命名为“日期”“销售量”“单价”“销售额”。

Step 2：在单元格G1输入公式“=TODAY（）”，用来显示当天日期。

Step 3：在单元格G2～G5分别输入公式“=SUMIF（A:A,G1,B:B）”“=SUMIF（日期,"＜"＆G1,销售量）”“=SUMIF（A:A,G1,D:D）”和“=SUMIF（日期,"＜"＆G1,销售额）”，用来计算当天的销售量、累计销售量、当天的销售额和累计销售额。

图6-26　动态更新汇总数据区域的汇总实例

3.不连续区域的数据汇总

【例6-20】　如图6-27所示，为重庆天来股份有限公司2015年上半年销售计划完成情况汇总表，其中每月的数据都包括“计划指标”和“完成情况”两行数据，现在需要计算上半年合计的“计划指标”和“完成情况”，并根据这两个数据进行计划指标“完成情况”的计算。

图6-27　不连续区域的数据汇总

本例中，上半年合计“计划指标”和“完成情况”的计算，其实就是不连续区域的数据汇总问题，因为这里需要合并的各月“计划指标”和“完成情况”是间隔排列的。此时，如果各单元格逐一进行计算无疑非常烦琐。较便捷的操作方法如下。

Step 1：在单元格C15和C16中分别输入公式“=SUMIF（$B$4:$B$15,"计划指标",C3:C14）”和“=SUMIF（$B$4:$B$15,"完成情况",C3:C14）”，分别得出上半年产品1的合计计划指标和完成情况。

Step 2：在单元格C17中输入公式“=C16/C15”，得出上半年产品1的完成比例情况。

Step 3：选定C15:C17区域，向右复制一直到H15:H17。

Step 4：选定C17:H17区域，将其单元格的数字格式设置为带两位小数的百分比格式。