sum函数的应用(你想要的SUM函数的应用)
你想要的SUM函数的应用,这里都有
继上一篇文章说了一下SUM函数基本的运算逻辑,这一篇文章有更多的例子来阐述它的各种用法。
SUM函数以引用的运算作参数如下表所示:
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单元格的数字为可变的。
A1为可输入的单元格
我们使用sum indirect row的组合来完成它。
公式如下:
SUM(ROW(INDIRECT("1:"&A1)))
当A1输入你想要输入的数值时,C1会得到对应的结果。
这里我们用到了indirect,我们用”1:”&单元格引用的形式,构造了一个动态的引用,A1可以是5,也可以是105。而Indirect的意思就是将一个文本转化成一个单元格引用。
6.Sum函数的文本求和。这与篇一讲的文本是不一样的,这里只是单元格的值是数字和文本的组合,如下表:
要求和的数据
当我们要求总金额的时候,是无法得到你想要的结果的。
在这里我们引入SUBSTITUTE函数,具体的公式如下:
SUM(--SUBSTITUTE(A2:A10,"元",""))
我们得到结果353
前篇文章中也说过了,文本是没有办法求和的。而SUBSTITUTE函数的作用为:用指定的新字符串替换原有字符串中的旧字符串。语法结构如下:
SUBSTITUTE (数据区域,旧字符串,新字符串,[替换位置])。
公式中,我们首先利用SUBSTITUTE函数将“元”替换为空值,并强制转换(--)成数值类型,最后用Sum函数求和。
7. SUM函数在数组公式中的一些应用现有如下数据表:
计数和求和的数据表
- 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个以上并列条件,可将几个条件式相乘。
- 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="男")作为并列条件。
- 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。
从上面的三个典型的例子,我们可以看出,逻辑值在数组运算中有着绝妙用处。
- A部门女性员工的工资总额是多少?这是多条件求和了。公式如下:{=SUM((B2:B11="A")*(C2:C11="女")*D2:D11)}同样基于:FALSE*任何数=0;TRUE*任何数=原来的数,(B2:B11="A")*(C2:C11="女")为并列条件,* D2:D11后就是满足条件的工资。
- 所有女性员工的工资和男性员工工资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函数判别。否则会多计数量的。大家可以记住这样的应用方法。
- 统计奇数行的工资总和是多少?公式如下:{=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