Excel公式技巧50： 根据条件来排序

excelperfect

有时候，我们想基于指定的条件对数据进行排序，如下图1所示，记录了不同区域员工的销售额。

图1

我们想要给不同区域的员工按销售额从大到小的顺序排序，即想要下图2所示的结果。例如单元格D2中的3表示员工1在华中区域销售额排在第3位。

图2

在单元格D2中输入公式：

=SUMPRODUCT((--(B2=$B$2:$B$24)),(--(C2<$C$2:$C$24)))+1

下拉至对应的数据单元格结束为止。

公式中：

(--(B2=$B$2:$B$24))

将单元格B2中的值与单元格区域B2:B24中的每个值相比较，得到：

(--{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

双减号将布尔值转换为0/1，即得到一个由0和1组成的数组：

{1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0}

其中1表示与单元格B2中的值相等，0表示不相等。

公式中：

(--(C2<$C$2:$C$24))

检查单元格C2中的数值是否小于单元格区域C2:C24中的值，得到：

(--{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

双减号将布尔值转换为0/1，即得到一个由0和1组成的数组：

{0;1;0;0;1;1;1;1;1;0;1;1;1;1;0;1;1;1;1;1;0;1;1}

其中1表示该单元格中的值大小单元格C2中的值。

将上述得到的两个中间数组传递给SUMPRODUCT函数，即求上述两个数组乘积之和。相乘后得到的数组中的1表示该区域中大于单元格C2中的值对应的单元格；将其相加得到该区域中大于单元格C2中的值对应的单元格数。

此时，公式转换为：

=SUMPRODUCT({1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0},{0;1;0;0;1;1;1;1;1;0;1;1;1;1;0;1;1;1;1;1;0;1;1})+1

即：

=2+1

加上1，表明单元格C2中的值在对应区域中从大到小排列的位置。示例中，2表示单元格C2中的值在对应区域中有2个的数值比它大，那么它排在第3位。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。

完美Excel社群2020.8.27动态

#VBA# 强制用户保护工作簿/工作表