设为首页收藏本站|繁體中文

Excel 技巧网

 找回密码
 注册

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 21651|回复: 79

[函数与公式] 2007版函数-SUMIFS和COUNTIFS的深入理解

  [复制链接]
发表于 2009-5-23 18:19:03 | 显示全部楼层 |阅读模式
  • 署名作者: mxqchina
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    今天突然对sumifs的应用有了点兴趣。个人觉得可以取代部分sumproduct的多条件求和功能。

    1、客户A的销售额
    =SUMIFS(C2:C22,A2:A22,"A")

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A"))
    =SUMIF(A2:A22,"A",C2:C22)

    2、客户A的1月份销售额
    =SUMIFS(C2:C22,A2:A22,"A",B2:B22,1)

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22=1))

    3、客户A的1月份和3月份销售额
    =SUM(SUMIFS(C2:C22,A2:A22,"A",B2:B22,{1,3}))

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3}))

    4、客户A和C的销售额
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"}))

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22={"A","C"}))
    =SUM(SUMIF(A2:A22,{"A","C"},C2:C22))

    5、客户A和C的1月份销售额合计
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,1))

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22={"A","C"})*(B2:B22=1))

    6、客户A的1月份和客户C的3月份销售额合计
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1,3}))

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22={"A","C"})*(B2:B22={1,3}))

    7、客户A和客户C的1月份和3月份销售额合计
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3}))

    *注意此公式7和公式6的差异仅为{1,3}和{1;3}中间的符号。

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3}))

    8、客户A和客户C的1月份\3月份\4月份销售额合计
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3;4}))

    可替换公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3,4}))


    9、客户A\B\C的1月份\3月份\4月份销售额合计
    =SUM(SUMIFS(C2:C22,A2:A22,{"A","B","C"},B2:B22,{1;3;4}))

    替代公式:
    =SUMPRODUCT(C2:C22*(A2:A22="A")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="B")*(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22*(A2:A22="C")*(B2:B22={1,3,4}))

    如果再次增多就可以看到SUMIFS的优势了。


    大家可以看到,SUMIFS在7和8的情况下,字符明显减少。(当然上面的情况好些还能用MMULT完成,但感觉SUMIFS更加简洁易懂)

    大家一起来探讨一下,这个新函数的还有什么新特性。。。。。

    *先在excelhome上发了,理睬的不多。发到这里,大家一起看看。。。

    10、客户A的数量
    =COUNTIFS(A2:A22,"A")

    替代公式:
    =SUMPRODUCT(--(A2:A22="A"))
    =COUNTIF(A2:A22,"A")

    11、客户A和B的数量
    =SUM(COUNTIFS(A2:A22,{"A","B"}))

    替代公式:
    =SUMPRODUCT(--(A2:A22={"A","B"}))
    =SUM(COUNTIF(A2:A22,{"A","B"}))

    12、客户A和B的1月份数量
    =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,1))

    替代公式:
    =SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=1))

    13、客户A和B的1\3月份数量
    =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1;3}))

    替代公式:
    =SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=1))+SUMPRODUCT((A2:A22={"A","B"})*(B2:B22=3))
    *如果条件更多,COUNTIFS的优势就显现出来了。

    14、客户A的1月份和客户B的3月份数量
    =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1,3}))

    替代公式:
    =SUMPRODUCT((A2:A22={"A","B"})*(B2:B22={1,3}))

    15、客户和月份的不重复个数
    =SUMPRODUCT(1/COUNTIFS(A2:A22,A2:A22,B2:B22,B2:B22))

    替代公式:
    =SUMPRODUCT(--(MATCH(A2:A22&B2:B22,A2:A22&B2:B22,)=ROW(A2:A22)-1))
    =SUMPRODUCT(1/COUNTIF(D2:D22,D2:D22))      (D列为辅助列)

    *感觉这个是今天最有收获的公式。这个公式可以适用3列、4列到更多列。。。。。

    SUMIFS的深入应用_mxqchina.zip

    8.99 KB, 下载次数: 560, 下载积分: 消费券 -5 Ti币

    2007版函数-SUMIFS和COUNTIFS的深入理解

    SUMIFS和COUNTIFS的深入应用_mxqchina.zip

    10.62 KB, 下载次数: 498, 下载积分: 消费券 -5 Ti币

    2007版函数-SUMIFS和COUNTIFS的深入理解

    评分

    参与人数 7魅力值 +33 收起 理由
    fxxying + 4 技法娴熟:)
    rex_xu + 4 one word-thanks
    微风 + 5 O(∩_∩)O谢谢
    xpm130 + 5 好文章
    无心为爱 + 5 谢谢分享:)

    查看全部评分

    发表于 2009-5-23 19:25:52 | 显示全部楼层
    呵呵,这里理睬的也不多。用2003的比较多,不过发现SUMIFS还是很好用的。

    我可是很常用SUMIFS哦,^_^ ——apolloh
    回复 支持 反对

    使用道具 举报

    发表于 2009-5-23 19:48:07 | 显示全部楼层
    呵,对新函数要多多挖掘,更高版本的Excel会慢慢为人们所接受只是一个趋势的问题。Office2010也增加了不少新函数,一些旧函数如RANK等将转化为“兼容性质”的函数了(还是拿RANK来说,它好像将被RANK.EQ所替代),尽早适应新函数的用法还是一个明智的选择。
    回复 支持 反对

    使用道具 举报

    发表于 2009-5-23 20:45:38 | 显示全部楼层
    很好!谢谢分享。ET最欢迎这样的文章,三天内如果无人提出异议,此文即加精华。
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2009-5-24 12:16:53 | 显示全部楼层
    更新了一下,加入COUNTIFS的应用。大家一起看看第15个公式,返回多列不重复的个数,个人觉得比较有用。
    回复 支持 反对

    使用道具 举报

    发表于 2009-5-31 18:45:02 | 显示全部楼层
    可替换的那两个我倒是很常用,SUMIFS这个都没用过。呵呵,有空的时候也要去试下
    回复 支持 反对

    使用道具 举报

    发表于 2009-6-1 16:02:25 | 显示全部楼层
    是的,旧的东西是会被替代的,不断地学习新的东西才能适应社会的发展。。谢谢LZ
    回复 支持 反对

    使用道具 举报

    发表于 2009-6-25 08:33:51 | 显示全部楼层
    初体验:比原来多了个S
    回复 支持 反对

    使用道具 举报

    发表于 2009-6-30 21:40:49 | 显示全部楼层
    很好,又学到了不少知识
    回复 支持 反对

    使用道具 举报

    发表于 2009-7-1 10:08:00 | 显示全部楼层
    虽然还不太懂,但还是先拿下来存起慢慢看,谢了前辈!
    回复 支持 反对

    使用道具 举报

    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Archiver|手机版|Excel技巧网 ( 闽ICP备08107682号-2 ) | 闽公网安备 35020302032608号  

    GMT+8, 2018-8-22 03:11

    Powered by Discuz! X3.3

    © 2001-2017 Comsenz Inc.

    快速回复 返回顶部 返回列表