Excel 之中国式排序（排名连续）

Excel 之中国式排序（排名连续）

原始文档：https://www.yuque.com/lart/tools/olm3oa

本文内容主要参考自：https://www.sohu.com/a/234253447_99914465

中国式排序

摘自：https://baike.baidu.com/item/%E4%B8%AD%E5%9B%BD%E5%BC%8F%E6%8E%92%E5%90%8D

在排名计算方法中，中国人的习惯是，无论有几个并列第 2 名，之后的排名仍应该是第 3 名，即并列排名不占用名次。
例如：对数列{3,2,2,1}从大到小进行排名，按国际通行的惯例是{第一名，第二名，第二名，第四名}，比如奥运会某场比赛出现并列银牌，则不发铜牌
而按中国式排名，结果是{第一名，第二名，第二名，第三名}。

使用 Excel 的公式实现中国式排序

这里我们只考虑SUMPRODUCT+COUNTIF的公式方法，这或许是最直接且简单的形式了。
之所以不考虑其他形式是因为直接使用公式可以方便的迁移到条件格式中，用来自定义数据单元格的显示格式。

为了便于解释，这里首先引入我们的数据：

注意这里展示了四种实际可以看到的情形：

1. A 列：正常数据，但是我们需要选择最小的前三个，分别使用红绿蓝色加粗标注。
2. B 列：正常数据，但是我们需要选择最大的前三个，分别使用红绿蓝色加粗标注。
3. C 列：存在缺失数据，但是我们仍然需要在剩下的数据集中选择最小的前三个，分别使用红绿蓝色加粗标注。
4. D 列：存在缺失数据，但是我们仍然需要在剩下的数据集中选择最大的前三个，分别使用红绿蓝色加粗标注。

组件函数的功能

SUMPRODUCT

即对各个参数数组对应元素的乘积累和。

• 当 sumproduct 函数中的参数只有一个数组时，即对数组{1;2;3;4;5;6;7}进行求和，1+2+3+4+5+6+7=28。
• 当 sumproduct 函数中参数为两个数组时，两个数组的所有元素对应相乘。
  • 公式=sumproduct(A2:A8,B2:B8)可转化为=sumproduct(数组1,数组2)=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。
• 当 sumproduct 函数中参数为三个数组时，三个数组的所有元素对应相乘。

高级用法——条件求和：

• 单条件求和：
  • =sumproduct((A2:A13="成都发货平台")*(B2:B13))用来统计成都发货平台的发货量。
    • 看到这公式你可能有疑惑，它跟语法格式好像不一样，其实把它看做是只有一个参数。
    • 公式分解=sumproduct({数组1}*{数组2})=sumproduct({TRUE;…..TRUE;…..TRUE}*{11012;…41568;…12506})=1*11012+1*41568+1*12506=65086。
    • sumproduct函数中，逗号分割的各个参数必须为数字型数据。如果是判断的结果逻辑值，就要*1转换为数字。如果不用逗号，直接用* 连接，就相当于乘法运算，就不必添加*1。所以以下两种形式等价：
      • 一种就是=sumproduct((A2:A13="成都发货平台")*(B2:B13))，直接将逻辑数组乘以数字数组。
      • 另一种是=sumproduct((A2:A13="成都发货平台")*1, (B2:B13))，乘以 1，把它转化成数组才能参与运算。
• 多条件求和：
  • =SUMPRODUCT((A2:A13="成都发货平台")*(C2:C13="重庆发货平台")*(D2:D13))用来求当发货平台为成都，收货平台为重庆的发货量。
  • 求成都发货平台和重庆发货平台的发货量，只要求满足其中的一个条件时，可以使用=SUMPRODUCT(((A2:A13="成都发货平台")+(A2:A13="重庆发货平台")),(B2:B13))。
  • 注意：SUMPRODUCT(条件1*条件2*条件3...条件N)中 * ：满足所有条件(all);SUMPRODUCT(条件1+条件2+条件3...+条件N)中 + ：满足任一条件(any)。

实用案例：

• 有条件计数
  • =SUMPRODUCT((A2:A13="成都发货平台")*(C2:C13="重庆发货平台"))
    • 利用 sumproduct 函数中 TRUE 和 FALSE 逻辑数组 1 和 0 的特效来计数。
    • 公式分解=sumproduct({1;0;0;0;0;0;0;0;0;1;0;1}*{1;0;0;0;0;0;1;0;0;1;0;0})=2
• 有条件排名
  • =SUMPRODUCT((D2<$D$2:$D$8)*1)+1
    • 数组公式D2<$D$2:$D$8这里是由TRUE和FALSE组成的逻辑数组{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}，通过*1转化为 1 和 0 的数组{0;1;1;0;1;0;1}。
    • 这里将整体区域和特定单元格比较从而得到比该单元格的值大的单元格数量，由于这里表示实际排名，所以还需要将自身的位数加入，即额外加个 1。

函数使用时可能出现的问题：

• sumproduct函数后面的参数必须是数组，即行和列的维度是一致的。参数维数不一致所以返回错误值#VALUE!

参考自：https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html

COUNTIF

统计范围 range 中满足给定条件(criteria)的单元格的数量。
简单案例：

• 统计区域中大于 90 的单元格的数量：=COUNTIF(B2:B10,">90")
• 统计区域中字符串单元格中郑字开头的数量：=COUNTIF(A2:A10,"郑*")
• 统计姓名不重复个数=SUM(1/COUNTIF(A2:A14,A2:A14))，并按 Ctrl+Shift+Enter 组合键。
  • COUNTIF(A2:A14,A2:A14)：表示对每个单元格进行统计判断，这样遇到相同的姓名，出现的人数会是一样的。
  • 1/COUNTIF(A2:A14,A2:A14)：表示删除重复值，假如只有出现一个，那个 1 除以 1 就等于 1，若是出现 2，那么 1 除以 2 等于 1/2，而求和则等于 1，这样就可以获取不重复人数。（这个想法真的很妙）

开始实现

A 列数据

解决方法：=SUMPRODUCT((A$1:A$11<=A1)/COUNTIF(A$1:A$11,A$1:A$11))
公式解析：

• (A$1:A$11<=A1)获取区域中是否小于等于**A1**情况（因为选择的是最小的几个数，所以应该考虑的是小于等于的数）。
• /COUNTIF(A$1:A$11,A$1:A$11)表示是针对各个单元格值出现的次数取平均。所以当对最终的数组结果加和的时候，直接抵消了重复次数。

B 列数据

解决方法：=SUMPRODUCT((B$1:B$11>=B1)/COUNTIF(B$1:B$11,B$1:B$11))

C 列数据

错误方法：=SUMPRODUCT((C$1:C$11<=C1)/COUNTIF(C$1:C$11,C$1:C$11))

因为存在缺失值，所以这里最好借助IFERROR来处理。
解决方法：=SUMPRODUCT(IFERROR((C$1:C$11<=C1)/COUNTIF(C$1:C$11,C$1:C$11),FALSE))

D 列数据

解决方法：=SUMPRODUCT(IFERROR((D$1:D$11>=D1)/COUNTIF(D$1:D$11,D$1:D$11),FALSE))

可以看到，使用IFERROR的时候，不论是升序还是降序排，缺失值都会位于末尾。所以不影响取前面的数据。

Excel 中按enter键和shift+ctrl+enter键的区别

摘自：https://zhidao.baidu.com/question/576243347.html

• 不是所有公式都有必要加上 shift+ctrl+enter 三键，普通公式没必要用三键，但是如果是需要通过数组运算才能得出正确值的公式就一定要加上数组三键。
  • =A1-B1是普通公式，加不加三键得出的值是相同的，就没必要加。
  • =SUM(A1:A100-B1:B100) 公式的意思是A1 至 A100 单元格区域分别减去B1 至 B100 单元格区域的值的总和(即 =A1-B1+A2-B2+A3-B3+A4-B4........A100-B100)。这样就是数组计算了，必须要用三键，只简单的按下回车键是得不出正确值的。
    • 这个公式的计算目的也可以用公式=SUM(A1:A100)-SUM(B1:B100)得到结果。但这个公式就不是数组公式了，所以又不必用数组三键了。
  • 能用普通公式进行计算的尽量用普通公式进行设置,这样可以减少内存的占用。
• shift+ctrl+enter是对数组函数的运算，当我们在写完函数公式时候，单独enter是不会对数组函数起作用的，而当按下shift+ctrl+enter后，原有的函数两端就会出现{ }，表明是对一个数组区域运算