注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

奕克

爱家人爱工作爱生活

 
 
 

日志

 
 

巧用excel数组公式完成条件求和  

2012-04-15 12:00:15|  分类: office |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
      今年是第二次上大学计算机二,果然重复是学习之母,我对某些内容的理解又进阶了,比如数组公式。把这几天思考的数组公式求条件和总结一下吧。
     我们平时一般条件求和用的都是sumif和countif,下图是一个实例:
巧用excel数组公式完成条件求和 - wucccsk - 天秤的存在与虚无博客
     比如求过期数额总计,用sumif写,就是:
=SUMIF(Difference,"<0",Amount)
这个公司可以利用sum和if函数嵌套加上数组公式解决,就是
{=SUM(IF(Difference<0,Amount))}

     这个计算过程实质上就是上图中G列中的情况,用IF(Difference<0,Amount)这个数组公式,对每行进行条件判断,符合的,G列写上对应的amount,不符合的,写上false,最后用sum的时候,由于不对false进行求和,所以可以求得满足条件的所有数额。
     那你可能会说了,那不是吃饱了撑的么,既然可以用sumif简单搞定,你为什么还要用复杂的数组公式?问题是,sumif和countif只是微软为简化计算实现的函数,而针对复杂的多条件求和,就只能用数组公式了,比如上图中的A公司或者 B公司过期总数。需要使用数组公式:
{=SUM(IF((Difference<0)*((Company="A公司")+(Company="B公司")),Amount))}

    在这里,其实别的地方与单条件求和都一样,变的只是条件的写法,在这里,*表示并且,+表示或者,用括号保证条件优先级。怎么理解呢?
    你可以把excel中的条件满足为true,表示为非0或者1;条件不满足为false,表示为0,这样的话,针对上述条件:
(Difference<0)*((Company="A公司")+(Company="B公司")) 
     比如一行记录为一个过期的A公司,它的条件表达式用加法和乘法计算为 1*(1+0)=1,结果就是非0,表示满足条件,因此应该进行求和。
    而什么样的公司不满足条件,只有0*(0+0)才等于0,因此必须不是A公司或者B公司,并且不过期。
     这样理解的话,相信大家对那几个数组公式的写法就了然于胸了。

      下面再看一个例子:
巧用excel数组公式完成条件求和 - wucccsk - 天秤的存在与虚无博客
 
     这里出现了一种新的情况,就是求满足条件的人的个数,而不是工资和了,解决方法很简单,就是if函数的结果,如果满足条件,写1,不满足,写false,最后还是用sum求和就可以了。G列是针对男性,高工或年龄大于50,对每个记录进行条件判断的结果。条件写法:
    (Sex="男")*((Position="高级工程师")+(Age>50))
    比如针对王2这个人,他的条件计算为 1*(1+0)=1,结果为非0,就是满足条件了,因此进行计数为1.
    所以整个数组公式的写法为:
   {=sum(IF((Sex="男")*((Position="高级工程师")+(Age>50)),1))}
   
    所以总结一下,多条件求和只要记住:
1)用if在内层先进行条件判断,再在外层用sum求和。
2)用*表示并且,+表示或者,1表示满足,0表示不满足,括号来保证优先级,如果计算结果非0,则进行计数或求和。
3)如果是计数,if条件成立,结果写入1;如果是求和,if条件成立,结果写入对应的求和字段。

   咱们最后来看个例子:
巧用excel数组公式完成条件求和 - wucccsk - 天秤的存在与虚无博客
  
  求某个分数段的人数,通常大家会写:
=COUNTIF(D2:D39,"<70")-COUNTIF(D2:D39,"<60")
    这样其实对excel来说,是做了2次遍历函数countif,第一次遍历求得所有小于70分人的个数,然后再减去少于60分人的个数,这样速度自然慢了,只是通常统计的人数不多,看不出来差别而已。其实可以用数组公式做一次遍历,就可以求得同时满足条件的人数:
=SUM(IF((D2:D39<70)*(D2:D39>60),1))

   特殊数组公式的简化:对于纯粹是与类型的数组公式多条件求和,我们可以简化里层的if条件,比如
=SUM(if((D2:D51="高等教育出版社")*(G2:G51<100),G2:G51,0))
这种与条件,条件逻辑计算结果,不是0就是1的形式的,里层if是可以省略的,即可以写成:
=SUM((D2:D51="高等教育出版社")*(G2:G51<100)*G2:G51)
因为条件成立结果为1,参与sum求和,不成立为0,不参与sum求和。
但对于
=SUM(if(((D2:D51="高等教育出版社")+(C2:C51="市场营销学"))*(G2:G51<100),G2:G51,0))
这种或形式,条件计算结果有可能是2,里层if就不能省略成:
=SUM(((D2:D51="高等教育出版社")+(C2:C51="市场营销学"))*(G2:G51<100)*G2:G51)
的形式,因为如果省略了if,有可能又是高教出版社同时又是市场营销学的记录被双倍计数了。。。所要要用if,把2变成1。

所以灵活运用数组公式,还是能帮助我们解决很多问题的。。。。
  评论这张
 
阅读(4275)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017