《2007函数SUMIFS和COUNTIFS的深入理解》由会员分享，可在线阅读，更多相关《2007函数SUMIFS和COUNTIFS的深入理解（8页珍藏版）》请在金锄头文库上搜索。
1、2007函数 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:C
2、22,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月份和客户
3、 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和客户
4、 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、客户 ABC的 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:
5、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)替代公式：
6、=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的 13月份数量=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
7、的 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
8、列、4 列到更多列。 本帖最后由 mxqchina 于 2009-5-24 12:19 编辑 附件: 您所在的用户组无法下载或查看附件把最复杂的变成最简单的,才是最高明的。-达芬奇若是数组公式，按 CTRL+SHIFT+ENTER 三键结束公式。 原文是 COUNTIFS($M$6:$M$2001,= & $AV14,$M$6:$M$2001,0,$M$6:$M$2000,=& $AV6, $M$6:$M$2000,= & $AW6)。sumifs 的函数参数分别为：sum_range，criteria_range1，criteria1，criteria_range2，criteria2，su
9、m_range 自然是求和的目标列，criteria_range1 是条件数据列，criteria1 是条件本身。大于数值，就直接用双引号；大于某单元格数值，就在双引号后用&连接单元格名称。最佳答案假如这列数据在 A 列并从 A1 开始。 在 B1 输入取汉字公式：=LEFT(A1,LENB(A1)-LEN(A1) 在 C1 输入取数字公式：=RIGHT(A1,2*LEN(A1)-LENB(A1) 向下复制公式即可。 时间:2008-3-10 7:51:14作 者: UMVSOFT 整理摘要:将阿拉伯数字转换为汉字数字, 支持到百万亿正文:例子: Debug.Print UpNumber(-1
10、0556765765555.45,0,True ) 显示为: 负壹拾万伍仟伍佰陆拾柒亿陆仟伍佰柒拾陆万伍仟伍佰伍拾伍圆肆角零分 Public Function UpNumber(ByVal Number As Double, Optional ByVal Typ As Long, Optional ByVal IsMoney As Boolean) As String * - 将阿拉伯数字转换为大写字符串 Version 1.0 2002-02-06 Version 1.1 2002-04-05 修改到支持到千亿 Version 1.2 2004-08-14 修改为支持 Typ,IsMoney
11、 参数,转换结果可以不是金额,支持到百万亿 Roadbeg - - 参数说明: Number 待转换的数字,可以是小数. Typ 转换类型,可选值 0,1 0 转换为 零,壹,贰 等 1 转换为 一,二,三 等 IsMoney 是否是金额,如果是 ,则转换为多少元,小数后转换为多少角,分, 反之则转换为类似于二点三这种形式 - - 返回值说明: 如果成功,返回转换后的字符串 如果失败,返回空字符串 - - 注意,此函数最大只支持到百万亿 没有对 Typ 的值进行检查,如果 Typ 不为 0,1 之一,将会引发错误. 另,由于 Double 类型数值范围的原因,超过百万亿, 将不能显示小数 ,同
12、样的超过十万亿只能显示一个小数,以此类推. - * On Error GoTo Doerr Dim Result As String 返回值 Dim strNumber As String 文本型的 Number Dim lngNumberLen As Long 文本型的 Number 的 Len Dim strTmp As String Dim strFirst As String, strEnd As String Dim lngI As Long, lngJ As Long, lngTmp As Long Dim strNum(10) As String 大写数字 Dim strUnit(16) As String 单位, 比如 十,拾, 万等 Dim strUnitB(2) As String 小数后的单位 初始化 Select Case Typ Case 0 strNum(0) = 零: strNum(1) = 壹: strNum(2) = 贰: strNum(3) = 叁: strNum(4) = 肆 strNum(5) = 伍: strNum(6) = 陆: strNum(7) = 柒: strNum(8) = 捌: strNum
