sum进阶篇

流浪铁匠

(这不是我计划内最后那贴
这篇原稿是我学函数时几年前的笔记,加了点料,因为其中sum的某些性质会在最后那贴用到，所以把以前的笔记完善了下分享出来,函数高手们就不用看前半部分了,有兴趣的看下最后sum统计工时的套路化公式与sum等多维问题把数组转为常量的性质即可)


sum/if/vlookup这3个函数据说是使用频率最高的函数
所谓的函数三板斧
新手对sum的了解仅限于这是个求和函数,但是,这个函数还是有很多性质是新手所不知道的

一，参数性质
二，引用区域与求和
  三，sum在序列/排名等方面的应用
   四，数组扩展
    五，何谓聚合函数

一，参数性质                                                
sum函数的参数基本可以归纳为引用,数组,常量 3类,支持最多255个参数(03版为30个)                                                
如果参数过多,可以借助一组括号将多个参数变为一个参数(区域联合引用)的方式增加参数(冷知识,因为对sum略有了解的都不可能用到这么多参数)                                                
之所以列出这部分,是因为sum这类函数有个特殊性质：                                                
如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。                                                
如果任意参数为错误值或为不能转换为数字的文本，Excel 将会显示错误                                                
因此,sum是忽略引用与数组内的文本型数字的,但会把作为常量的文本型数字(或可被识别的表达式)也进行计算【http://club.excelhome.net/thread-1484896-1-1.html 计划里最后这贴大家最关心的sum画画公式的实现,第一步就是靠sum这类函数的这个性质,179个非阿拉伯的数字字符在作为文本型常量时能被sum正常识别为数字计算,使sum结果正常返回对应数值,而这是画画的第一步(其实整个公式都是障眼法,目的是获取需要的数值)】                                                
                                                
以上部分性质在后续帖有相关应用,不是本文重点,这里只是简单提下                                                
不过既然提了就列下本文第1组2个让新手抓狂的公式               
               

其实很简单, (1)是 -1的特殊表达式,但需要为文本字符串方式,sum函数括号内的其他括号为区域联合用法
=sum(1,2)等效=sum((1,2))的,只是后者只用了1个参数

流浪铁匠

二，引用区域        
对于sum的求和区域，先介绍3个符号        
因为即便只是简单的区域求和，相同的引用区域，使用不同的运算符，也会导致结果有很大差别        



为了便于大家的理解，做了一个动态引用的模拟(截图较大请直接在附件手动切换控件参数查看)
sum的3种运算符的求和差别                        



然后请自行理解 =SUM(E1:E9 D3:G6,G9) 这公式的实际求和区域        

流浪铁匠

三，sum的非主流用法
序号与排名



sum可用于获取各种排名(不重复排名的常规思路只是在美式基础上+countif占位,不再列举)
另外从数组阶段，sum与sumproduct经常替换，多数情况下2个函数可以相互替代，但少数情况下不行

流浪铁匠

四，数组扩展                                                        
                                                        
因为sum函数支持数组，支持多维，所以在这个阶段，sum函数具有无尽的衍生用法                                                        
在这里只列一点点皮毛，希望对大家有所帮助(sum简单的条件求和/计数资料太多,这里尽量列点相对少见的)                                                        
                                                        
1,斜线求和                                                        
(第1个特意找了个不常见的，毕竟像简单的(多)条件求和/计数，稍微对数组有点了解的都知道怎么做                                                        
(斜线求和是我一个同事之前问我的问题，扩展了下难度，需要对图示几个颜色的区域分别求和                                                        
(图示颜色只是便于查看区域，不能使用宏表函数利用背影颜色来作为求和条件))(一个公式下拉完成)        






2，
多列条件求和



混过e圈的应该知道这题有个段子                                                                                       
                                                                                       
菜鸟公式为多个sumif列相加和sum无关故不列举                                                                                       
高手公式                =SUM(SUMIF(OFFSET(A$107:A$116,,ROW($1:$4)*0),G107,OFFSET(A$107:A$116,,ROW($1:$4))))                                                                        
可简化为        =SUM(SUMIF(A$107:A$116,G107,OFFSET(A$107:A$116,,ROW($1:$4))))                                                                        
高高手公式               
=SUM(SUBTOTAL(9,OFFSET(B$106:E$106,ROW($1:$10),))*(A$107:A$116=G107))                                                                        
高高高手公式               
=SUM(MMULT(B$107:E$116,ROW(1:4)^0)*(A$107:A$116=G107))                                                                        
                                                                                       
可是这题我一直奇怪难道不是一个sum就完事了么……                                                                                       
纯一维数组解法               
=SUM((A$107:A$116=G107)*(B$107:B$116+C$107:C$116+D$107:D$116+E$107:E$116))           只适合学习时使用,列多了和sumif相加一样属于低效解法        
常规二维数组解法               
=SUM((A$107:A$116=G107)*B$107:E$116)                                                                        
                                                                                       
当然这题如果你sum熟悉了还有很多别的解法……                                                                                       
=SUM(IF(A$107:A$116=G107,B$107:E$116))                                                                        
=SUM(DSUM(A$106:E$116,ROW($2:$5),G$106:G107))-SUM(H$106:H106)                                                                        
=SUM(COUNTIF(G107,A$107:A$116)*B$107:E$116)                                                            
                                                                                       
好吧我承认这题其实重点不是sum而是各种数组运算思路……，但sum在数组运算里的重要内容之一就是把各种内存数组聚合为求和结果不是么,                                                                                       
无论哪个解法最外面没sum(或sumproduct)也只是个内存数组而已                                                                                       

3        
区间取值/范围判断        
你们以为范围取值，只是 lookup/ vlookup/ match等函数的事情 ?!        
        
(1)求日期对应的季度        



(2)成绩判断

sum+常量数组的结构类似match
但match内需要排序
sum的常量数组没有这个限制
而且使用sum的另一个好处是比lookup等更适合左开右闭区间
(左开右闭时lookup等需要修正临界点的小数精度)




4，0& 在sum数组公式中的容错应用
严格来说这不是一个套路，而是一种容错的技巧，在sum的数组公式中常见，之前碰到一个题又用上了


=SUM(--TRIM(MID(SUBSTITUTE(A177,"/",REPT(" ",99)),(ROW(INDIRECT("1:"&(LEN(A177)-LEN(SUBSTITUTE(A177,"/",))+1))))*99-98,99)))        
因为"/"数量不一致，所以部分公式利用trim/substitute/mid的套路产生的用"/"分列的内存数组，会产生 "" 空文本，        
在用 --（减负运算）或*1等把文本型数值转化成真数值求和的过程中，--"" 会产生错误值,因为文本不能进行四则运算        
所以使用row+indirect+len的结构，保证分列数与"/"间隔数匹配        

可简化为        
=SUM(--(0&TRIM(MID(SUBSTITUTE(A177,"/",REPT(" ",99)),ROW($1:$5)*99-98,99))))        
而若使用0&的方式，把产生的"" 转成文本型的0&""即"0"        
亦可对公式进行容错处理，且公式较为简洁        
(这里只是介绍一个类似1/17，5^19的常用容错手段，短的公式计算量不一定小，切记)        
如果存在文本,经常会借助text来经常类似处理,因为之后案例有text更强大的应用,这里不再赘述        

另外,关于0& 还要说明下,这个除了处理空文本外还能处理单纯的空格
因为excel的计算有个性质是可以直接忽略数字两端的多余空格
所以 =sum("   123   ")等价 =sum("123")等价 =sum(123)

流浪铁匠

5，条件求和
1）合并单元格条件求和
2）条件下的筛选求和
3）去重计数

1)合并单元格条件求和


2）条件下的筛选求和


3)不重复重计数


这部分写了几个常规案例,目的是为了告诉大家
sum条件求和的各种数组公式，本质只是把条件用各类函数变成相应的内存数组来解决的
sum只是壳，核心在于条件的构造

流浪铁匠

6,        
阶梯求和        
1)阶梯电价计费        
网上摘取了下某市的夏季电费        


=SUM(TEXT(D270-{0,260,600},"0;!0")*{0.61,0.05,0.25})
阶梯式的计算公式

text的强大之处之一，就是可以对内存数组按照一定的条件进行转化
用常量数组，对3个阶段分别做减法，负值转成0，对正数乘以对应的电费价格段
最后用sum对3段进行求和
非常实用的思路

这是论坛帖子与公众号都有介绍的一个经典思路


2)工时计算  [本帖重点公式,之前利用sum+text阶梯计算与时间转数值计算思路配合逐步完善的套路解法]               
对excel比较熟悉的童鞋,应该知道                       
在excel里时间和日期是数值的一种特殊表现方式,所以很多时候可以使用数学思路来简单解决时间和日期的相关问题       




看懂该思路后这类计算工时问题只是个数学题而且只要对time部分(与int部分的每日全部工时)进行参数修改                       
无论每天是几段上班时间均能快速统计                       
这就是阶梯思路配合时间的数学处理思路下的配合                       
整个sum公式有2个参数，是大小不一致的内存数组(第1个是二维的，第2个是一维的）                       
但是在sum里作为不同参数，所以可以被统一求和                       
这也是这公式最后能变成只用1个sum的原因所在                       



利用这个思路可以快速解决n段上班时间的有效工时

sum的广泛性就是因为其支持对内存数组内的数值进行求和,而如何构造各种不同的内存数组,这就是学习函数的目的了

流浪铁匠

五,                                       
何谓聚合函数                                       
之所以提这点是因为多维里会出现的一个问题                                       
具体的原理只有微软自己能解释                                       
而直接表现的差异为                                       
row(a1)的结果本质还是数组 (单个元素的数组) {1}                                       
而一旦套嵌sum/max等，聚合函数会把数组变为常量结果                                       
这就是聚合函数的特点（有时是缺点），聚合函数会把所有参数最终聚合为1个常量结果                                       
无法用这种函数形成内存数组的                                       


这是一个比较深的坑，总能看到有人在问类似错误的原因
由于row结果为数组,在类似案例中需要最后套嵌一个聚合函数把结果变为常量
才能使offset（或indirect）等正常返回引起区域
当然这篇是sum篇所以用了sum，有些情况下max相对更适合

流浪铁匠

最后加个彩蛋，
因为看到这里可以说你还是不会sum



(这其实是之前在群里答题发现的一个简写表达式，并不是那种2013开始出现的诡异算式,把上图公式结果改为时间格式你就知道答案了)

单身深蓝

下载学习，楼主辛苦，楼主荡漾，楼主永垂不朽！

micch

仙福永享 寿与天齐