EXCEL 2007函数SUMIFS 和COUNTIFS的深入理解来源:互联网 作者:佚名 时间:03-27 17:32:39 【大 中 小】 点评:今天突然对sumifs的应用有了点兴趣。个人觉得可以取代部分sumproduct的多条件求和功能。- 今天突然对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更加简洁易懂) 大家一起来探讨一下,这个新函数的还有什么新特性。。。。。 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列到更多列。。。。。
详细出处参考:http://www.jb51.net/office/excel/42621.html
|