sum函数的应用(你想要的SUM函数的应用)

你想要的SUM函数的应用,这里都有

继上一篇文章说了一下SUM函数基本的运算逻辑,这一篇文章有更多的例子来阐述它的各种用法。

SUM函数以引用的运算作参数

如下表所示:

sum函数的应用(你想要的SUM函数的应用)(1)

1. 区域联合求和

SUM((A1:A10,C5,D2:D7))

我们得到结果287

我们仅对表中的几个区域进行求和。并没有全部求和。请注意区域联合运算外的一对括号,此运算在SUM函数中只算1个参数,当SUM中的参数超过30个时,就可以这样使用。

2. 区域交叉求和

SUM((B1:C10 A4:D6))

我们得到结果120。

注意括号及2个引用间的空格,它完成了交叉引用。在SUM函数中也只算1个参数,此处实际运算返回的是B1:C10和A4:D6相交的B4:C6区域

3. 交叉引用在数组公式中可以继续进行计算。

{=SUM(((A1:D10 B:B)>15)*(A1:D10 B:B))}

我们得到结果90。

这个公式实际上计算的是B1:B10区域大于15的所有值的和

4. SUM函数以三维引用作参数

假设sheet1、sheet2、sheet3三个工作表的A1单元格的值都为10。

SUM(Sheet1:Sheet3!A1)

我们得到的结果为30

其实公式就是对sheet1、sheet2、sheet3三个工作表的A1单元格求和。

三维引用更为复杂的用法,暂且就不说了。

5. SUM函数的累计求和

以前上小学的时候,老师就出过一个题目,如何快速的求1-100的和。对于SUM函数来说,公式如下:

SUM(ROW(1:100))

我们得到的结果5050。

实际应用中,可能你求的值不是1-100,或是1-505,每次要求的值都不固定。

如下表,A1单元格的数字为可变的。

sum函数的应用(你想要的SUM函数的应用)(2)

A1为可输入的单元格

我们使用sum indirect row的组合来完成它。

公式如下:

SUM(ROW(INDIRECT("1:"&A1)))

当A1输入你想要输入的数值时,C1会得到对应的结果。

这里我们用到了indirect,我们用”1:”&单元格引用的形式,构造了一个动态的引用,A1可以是5,也可以是105。而Indirect的意思就是将一个文本转化成一个单元格引用。

6.Sum函数的文本求和。

这与篇一讲的文本是不一样的,这里只是单元格的值是数字和文本的组合,如下表:

sum函数的应用(你想要的SUM函数的应用)(3)

要求和的数据

当我们要求总金额的时候,是无法得到你想要的结果的。

在这里我们引入SUBSTITUTE函数,具体的公式如下:

SUM(--SUBSTITUTE(A2:A10,"元",""))

我们得到结果353

前篇文章中也说过了,文本是没有办法求和的。而SUBSTITUTE函数的作用为:用指定的新字符串替换原有字符串中的旧字符串。语法结构如下:

SUBSTITUTE (数据区域,旧字符串,新字符串,[替换位置])

公式中,我们首先利用SUBSTITUTE函数将“元”替换为空值,并强制转换(--)成数值类型,最后用Sum函数求和

7. SUM函数在数组公式中的一些应用

现有如下数据表:

sum函数的应用(你想要的SUM函数的应用)(4)

计数和求和的数据表

  1. A部门的男性员工有几人?这是多条件计数。公式如下:{=SUM(IF((B2:B11="A")*(C2:C11="男"),1,0))}(B2:B11="A")*(C2:C11="男") 返回2个逻辑数组的乘积,基于TRUE*TRUE=1;TRUE*FALSE=0;FALSE*FALSE=0,所以此处是逻辑与的关系,在excel的IF函数的条件中,0表示FALSE,非0的数值表示TRUE。我们可以去除IF函数可以简化公式为:{=SUM((B2:B11="A")*(C2:C11="男"))}如有2个以上并列条件,可将几个条件式相乘。
  2. A、B两部门的男性员工有几人?公式如下:{=SUM(((B2:B11="A") (B2:B11="B"))*(C2:C11="男"))}基于TRUE FALSE=1;FALSE FALSE=0;TRUE TRUE=2,而(B2:B11="A")和(B2:B11="B")不可能同时满足,所以此处是条件或的关系,再乘以(C92:C105="男")作为并列条件。
  3. A部门所有女性员工和A部门工资5500以上的男性员工总数是多少?公式如下:{=SUM((B2:B11="A")*NOT(NOT((C2:C11="女") (D2:D11>=5500))))}因为(C2:C11="女")和(D2:D11>=5500)可能同时满足,所以再用NOT(NOT())转换,基于NOT(TRUE)=FALSE;NOT(FALSE)=TRUE;NOT(0)=TRUE;NOT(非0数值)=FALSE。

从上面的三个典型的例子,我们可以看出,逻辑值在数组运算中有着绝妙用处。

  1. A部门女性员工的工资总额是多少?这是多条件求和了。公式如下:{=SUM((B2:B11="A")*(C2:C11="女")*D2:D11)}同样基于:FALSE*任何数=0;TRUE*任何数=原来的数,(B2:B11="A")*(C2:C11="女")为并列条件,* D2:D11后就是满足条件的工资。
  2. 所有女性员工的工资和男性员工工资5500以上的工资总额是多少?公式如下:{=SUM(NOT(NOT((C2:C11="女") (D2:D11>=5500)))*D2:D11)}如加IF函数就可以不用NOT(NOT()):{=SUM(IF((C2:C11="女") (D2:D11>=5500),1)*D2:D11)}我们以(C2:C11="女") ( D2:D11>=5500)这样的形式表示条件或的关系,在条件可能同时满足时要用NOT(NOT())转换或用IF函数判别。否则会多计数量的。大家可以记住这样的应用方法。
  3. 统计奇数行的工资总和是多少?公式如下:{=SUM((MOD(ROW(D2:D11),2)=1)*D2:D11)}

{=SUM((MOD(ROW(D2:D11),2)=1)*D2:D11)}

其中的(MOD(ROW(D2:D11),2)=1)就是判别是否奇数行。

这两篇文章所列出的的公式,几乎涵盖了SUM函数在绝大多数情况下的典型应用。如果大家能够融会贯通,举一反三,必定能成倍地提高你的工作效率,化繁为简。

希望大家多关注点赞。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页