实战之路《Excel 数据处理与分析实战宝典_第2版》_7.2 求和、统计函数应用案例

【摘要】 本书摘自《Excel 数据处理与分析实战宝典_第2版》一书中第7章，第2节，耿勇著。

7.2 求和、统计函数应用案例

7.2.1 多条件求和公式

H5 单元格定义公式多条件求和的多种解决方法如下。

方法1: SUM 多条件求和数组公式

在H5 单元格中输入=SUM(IF(($B:SB6=H2)*(SD:$D6=H4),SES2:SE6)),同时按 Ctrl+Shift+Enter 组合键，在整个公式的外围出现数组公式的标志“{}”。

以下是此数组公式的解析：

(1)(SB:SB6=H2) 和($D:SD6=H4)通过“*”连接，表示这两个条件需要同时具备， 从逻辑函数相关基础知识可知，($B:$B6=H2)、($D:SD6=H4)这两个逻辑条件等式返回的结果是逻辑值，而逻辑值TRUE 或 FALSE 在公式运算中通过“*”连接会转换成1或0。

(2)此公式中利用了当数据源区域中的科目和月份同时等于所查询的科目和月份的条件时， 就返回E 列区域中对应单元格区域中的数值，当只要其中有一个条件不满足时，就返回0(即在 数组区域中将对应E 列单元格的数据替换成0),然后将所有的返回数据结果进行累加。

方法2: SUMIFS 多条件求和公式

SUMIFS 多条件求和语法：

=SUMIFS (求和区域，条件区域1,条件1,条件区域2,条件2,……,条件区域n,条件n)

作用：对同时满足条件1、条件2一直到条件n 的记录指定区域进行求和。其中求和区域总 是位于第1个参数上，后面的参数均依次输入条件区域和条件，条件区域和条件都是成对出现的。 其实质是多个条件同时具备时求和，相当于AND 条件。

在H5 单元格中输入：

=SUMIFS(SES2:SES366,SBS2:SB9366,H2,SDS2:SDS366,H4)

提示 对于单一条件求和，我们大多使用 SUMIF 函数。事实上，SUMIFS 也能实现单一条件求和， 读者不妨试一试。

上面 SUMIFS 函数中的求和区域、条件区域都是通过鼠标选取的，当数据记录增加时需要重 新手工选择数据区域范围，在处理方式上不够灵活。下面我们以定义名称的方法让所选择的数据 源能实时更新，实现方法如下。

在数据源中对“会科”按首行标题定义名称，在定义名称对话框的引用位置文本框中输入公 式：=OFFSET($B$2…,COUNTA($B:$B)), 接着按照同样的方法依次定义“月份”和“金额”这两 个名称。在H5 单元格中定义这个实例的多条件求和公式为=SUMIFS(金额，会科，H2,月份，H4)。

从结构上讲，使用名称可以使公式所引用的数据区域随着数据源增减而自动扩展或伸缩，极 具灵活性。这种公式更加简洁，也更清晰易懂，可读性更强。

方法3:SUMPRODUCT 多条件求和公式

SUMPRODUCT 多条件求和语法：

=SUMPRODUCT( (条件1)*(条件2)*(条件3)*……(条件n)* 某区域)

作用：对同时满足条件1、条件2一直到条件n 的记录指定区域进行求和。

现需要查询出5月的电费，在定义名称方式的情况下，SUMPRODUCT 多条件求和公式如下： =SUMPRODUCT((会科=H2)*(月份=H4)*金额)。

我们知道，逻辑值TRUE 、FALSE分别对应于1和0,经过SUMPRODCT 函数中两个“*” 的连接就实现了对E 列单元格中的数据进行连乘计算。只有当两个条件均满足时才返回1,并且 与 E 列对应单元格中的值相乘，而不满足条件的则返回0,0与E 列中对应单元格中的值相乘后 得出结果为0。之后对所有连乘后的结果进行求和即可。

提示 本实例中的SUMPRODUCT多条件求和运算机制与前面所述SUM多条件求和数组公式的运算 逻辑有些相似。

如果上述定义名称的 OFFSET 函数中第1个参数选择首行的单元格，则

“会科”= OFFSET($B$1,,,COUNTA(SB:SB)-1)

“月份”= OFFSET(SD$1,,,COUNTA(SD:SD)-1)

“金额”=OFFSET(SES1,,,COUNTA($E:SE)-1)

此时公式“=SUMPRODUCT((会科-H2)*(月份=H4)*金额)”计算出现了错误值#VALUE!, 但 公式“=SUMPRODUCT( 会 科 =H2)*(月份=H4),金额)”却仍可以计算出正确的结果。前一个出现 错误的原因是求和区域包含“金额”这个文本值。

注意事项：

★ SUMPRODUCT 函数多条件求和时使用“,”和“*”的区别——当拟求和的区域中无文本 时，两者无区别。当有文本时，使用“*”时会出错，返回错误值#VALUE!; 而使用“,”

时，SUMPRODUCT 函数会将非数值型的数组元素作为0处理，故不会报错。

★数组参数必须具有相同的维数，否则函数 SUMPRODUCT 将返回错误值#VALUE!。 ★ SUMPRODUCT 函数将非数值型的数组元素作为0 处理。

★ SUMPRODUCT 函数不能像 SUMIF、SUMIFS、COUNTIF 等函数一样支持“*”和“?” 等通配符。要实现此功能，可以用变通的方法，如使用LEFT 、RIGHT 、ISNUMBER(FINDO)或 ISNUMBER(SEARCHO) 等函数来实现通配符的功能。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。