你想要的SUM函数的应用,这里都有
继上一篇文章说了一下SUM函数基本的运算逻辑,这一篇文章有更多的例子来阐述它的各种用法。
SUM函数以引用的运算作参数如下表所示:
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函数在数组公式中的一些应用现有如下数据表:
计数和求和的数据表
从上面的三个典型的例子,我们可以看出,逻辑值在数组运算中有着绝妙用处。
{=SUM((MOD(ROW(D2:D11),2)=1)*D2:D11)}
其中的(MOD(ROW(D2:D11),2)=1)就是判别是否奇数行。
这两篇文章所列出的的公式,几乎涵盖了SUM函数在绝大多数情况下的典型应用。如果大家能够融会贯通,举一反三,必定能成倍地提高你的工作效率,化繁为简。
希望大家多关注点赞。
,