好难,相同排名后名次不连续了.Excel sumproduct函数用法教程,按月份求和、模糊条件求和教程.

SUMPRODUCT函数是一个数学函数，和它的名字sum+product一样，用于在给定的几组数据中，将数组间对应的元素相乘，并返回乘积之和。当然，这是在默认使用逗号分割数组的情况下，但其实也可以执行加减除运算。将分隔数组参数的逗号替换为所需的算术运算符 (*、/、+、-) 。 执行所有操作后，结果将像往常一样进行求和。

    用法：SUMPRODUCT (数组1， [数组2]， [数组3]， ...)

    至少需要一个数组元素，如果是多个，每个数组内的元组数量相同。另外，如果使用算术运算符，需要将数组参数括在括号中，并使用括号对数组参数进行分组以控制算术运算的顺序。

    示例中，一个常见的错误是，当数据源中含有文本数据时，默认逗号分隔仍然可以返回正确结果，如果是使用其他算术运算符，就会返回错误值#VALUE!因为，文字不能进行乘法计算。

    sumproduct函数的第一个案例就是按月份求和，也是一个常见的多条件求和问题。如果使用SUMIFS函数，判断时间是否属于7月份，对应的公式位“=SUMIFS(E2:E1089,D2:D1089,">=2023/07/01",D2:D1089,"<=2023/07/31")”；对应地，借助SUMPRODUCT函数的按月求和公式就方便多了，且不用担心每个月最后一天日期不固定的问题，公式为为“=SUMPRODUCT((MONTH(D2:D1089)=7)*(E2:E1089))”。

    两个公式之所以差这么多，核心原因就在于SUMPRODUCT支持数组间运算，而sumif和sumifs的条件范围只支持区域引用，其实不止是条件求和，后续的条件计数countif(s)函数也存在只支持区域不支持数组的情况。

牛奶产品不止一个，要查找所有牛奶的合计销售额，就要按照关键字“牛奶”查找。说到模糊查找，可能很多人都想到了使用通配符，试过之后却发现求和结果为0，因为sumproduct函数不支持通配符。这一点上，sumifs函数做得就更好。

    不支持通配符，并不代表不能做，通过find查找的方法也是可以曲线救国的。B11单元格的公式为“=SUMPRODUCT(ISNUMBER(FIND("牛奶",A2:A5))*(B2:B5))”。公式的大概逻辑是这样的。

    FIND("牛奶",A2:A5)在A2：A5单元格值中查找“牛奶”，如果能找到，返回“牛奶”在单元格值中位置；如果找不到，则返回错误值#VALUE！。

    接着，利用ISNUMBER函数判断参数是否为数字，如果是，返回True，否则返回FALSE。

    然后就是sumproduct函数乘积求和。

无论是旧版的rank,还是新版的rank.eq和rank.avg，在进行数据排名时，都有一个共同的现象，就是遇到数值相同的情况，会出现名次间断的情况。而实际工作中，很多时候需要的是相同排名但名次不间断。虽然可以通过添加影响因子来解决，但公式比较麻烦。推荐使用咱们今天的主角sumproduct函数来解决。

    D2单元格的公式为“=SUMPRODUCT((B$2:$B$5>B2)/COUNTIF(B$2:B$5,B$2:B$5))+1”，先看一下结果，是符合领导预期的。再来研究一下这个公式的逻辑。

    (B$2:$B$5>B2)，排位区域是否大于单元格，返回一个逻辑值True和False的数组，也就是1和0组成的数组，以示例中小明的数据为例，有三个人的销售额比他好，所以结果为{0,1,1,1},直接求和相加再加上1，得到小明的排名为4，和rank函数结果一致。

    COUNTIF(B$2:B$5,B$2:B$5)，判断排位区域中每个单元格重复出现的次数，同样返回一个常量数组，如果没有重复的，该数组所有元素均为1，相除后不影响结果，如果有重复的，示例中结果为{1,1,2,2}。

    对于相同排名之前的数据，包括它自己，因为被除数为0,0除以任何数结果都为0，不影响求和结果；如果是之后的数据，被除数为1，除数为重复出现的次数，示例中为2，等于把1平均分成2份，再相加，结果还是1，所以小明的排名为3。

SUMPRODUCT这么全能厉害，但有一个就是计算效率低，你没看错，就是慢，所以各位要酌情使用哦。

    官方给的建议是为获得最佳性能，SUMPRODUCT 不应与完整列引用一同使用，应尽可能限定范围。举的例子是=SUMPRODUCT (A:A,B:B) ，此公式将 A 列中的 1048576 个单元格乘以 B 列中的 1048576 个单元格，然后再求和。 这样的公式要是来个几十个，电脑还不得崩溃。