EXCEL2007函数SUMIFS和COUNTIFS的深入理解
今天突然对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列到更多列…
智能扫地机器人哪个好Proscenic智能扫地机器人特点详细介绍如今的居民生活是越来越智能化了,智能电视智能家电应有尽有。扫地当然也不用您亲自动手,您只需坐在沙发上尽情享受生活就行啦,这一切就交给Proscenic智能扫地机器人吧。作为智能家用
智能扫地机哪个牌子好扫地机器人品牌介绍详解我们现在的工作压力还是很大的,平时下班回来时间也不早了,还要做家务,实在是很烦心,不过这几年市场上卖得比较好的扫地机器人就能解放我们的双手,它可以自动清扫家里的环境,包括我们人工触
智能扫地机器人什么牌子好智能扫地机器人品牌推荐详解随着社会科技不断发展,如今,智能扫地机器人行业越发风生水起。渐渐地,很多智能机器人品牌不断涌现出来,这样的话,性能难免参差不齐,那么,什么品牌的扫地机器人好呢?小编为了让大家更顺利
光纤网卡价格介绍现在是已经步入信息化时代了,而在这个时代我们最为担心的就是网络速度了,有时候网络速度慢的让人捉急,相伴些什么事情也不能办,只能默默的干着急。而今天我将为大家介绍一款光纤网卡,可以有
上网卡设备多少钱上网卡设备价格及选购上网卡设备,也就是传统所说的无线上网卡。无线网卡其实与家庭联网所用的ldquo猫rdquo的功能和性质一样。一般电脑在没有无线网宽带的环境中不能连接互联网,但如果在无线电话信号覆盖
佳能增倍镜价格和参数配置介绍在用户的传统认识立,数码相机是使用于拍照,但是随着科技的发展,相机的应用范围越来越广泛,不仅使用于各种摄影拍照,还可以适用于专业的电影拍摄,以及时尚杂志等需要,这就要求有很高的镜头
索尼NEXC3是什么相机索尼NEXC3是一款超迷你的APSC画幅微单数码相机。索尼NEXC3配备了3。0英寸92万像素的XtraFine可折叠液晶屏,液晶屏可向上折叠与机身平面呈80deg角,向下呈45d
索尼NEX3N是什么相机索尼NEX3N是单电数码相机。索尼NEX3N配备了一块3。0英寸46万像素的180ordm可翻折寸XtraFine液晶屏,翻转式屏幕方便随时进行自拍,显示效果中规中矩。身三围尺寸为
索尼NEX5N相机画幅是什么索尼NEX5N相机画幅是APSC画幅。索尼NEX5N是2011年8月发布的一款替代经典NEX5的微单。外观上,索尼NEX5N基本上保持了前代NEX5的整体造像,新加入了纯白色外壳供
索尼NEX7相机画幅是什么索尼NEX7相机画幅是APS画幅。索尼NEX7采用了3英寸92万像素可折叠式的XtraFine液晶屏,并采用TruBlackldquo纯黑rdquo技术。索尼NEX7液晶屏最大可向
索尼NEXC3是什么卡口索尼NEXC3采用了索尼E卡口镜头,可更换镜头。索尼NEXC3配备了3。0英寸92万像素的XtraFine可折叠液晶屏,液晶屏可向上折叠与机身平面呈80deg角,向下呈45deg角