Excel公式需求：按完成百分比排名排序并将无目标值销售员数据后置

我来给你分享两种实用的实现方案，分别适配不同版本的Excel，你按需选就行：

一、适用于Excel 365/2021（支持动态数组，最省心） #

如果你的Excel是新版的，直接用SORTBY函数一步到位，简直不要太方便！

假设你的原数据范围是A2:D100（A列销售员姓名，B列目标值，C列实际完成值，D列是已计算好的完成百分比Achieved %），在新表格的起始单元格（比如F2）输入下面的公式，整组符合要求的数据会自动填充出来：

=SORTBY(A2:D100, ISBLANK(B2:B100), 1, D2:D100, -1)

给你拆解下逻辑：

• ISBLANK(B2:B100)会把没有目标值（B列空白）的销售员标记为TRUE，有目标的标记为FALSE
• 第一个排序条件ISBLANK(...) , 1是升序排序，FALSE（有目标）会排在TRUE（无目标）前面
• 第二个排序条件D2:D100 , -1是按完成百分比降序排列，这样有目标的销售员就会从完成率最高的开始排

要是你没有单独的完成百分比列，也可以直接在公式里计算，把第二个条件换成C2:C100/B2:B100, -1就行，不用额外列D列。

二、适用于旧版Excel（不支持动态数组） #

如果你的Excel是老版本，那就用INDEX+MATCH+SMALL的组合公式，需要按Ctrl+Shift+Enter三键结束输入（数组公式的要求）：

在新表格的F2单元格输入（同样替换成你实际的数据范围）：

=INDEX(A:A, MATCH(SMALL(IF(NOT(ISBLANK(B2:B100)), D2:D100, 10^10), ROWS(F$2:F2)), IF(NOT(ISBLANK(B2:B100)), D2:D100, 10^10), 0))

下拉这个公式就能提取所有排序后的销售员姓名，要是需要同步提取其他列数据，把公式里的A:A换成对应的列（比如B:B取目标值）就行。

逻辑解释：

• 用IF(NOT(ISBLANK(...)), D2:D100, 10^10)把无目标值的销售员的完成百分比替换成一个极大值（10^10）
• SMALL(..., ROWS(F$2:F2))依次取第1小、第2小……的数值，因为极大值会最后被取到，所以无目标的销售员自然就排在最后
• 再用MATCH找到对应的行号，最后用INDEX提取对应的数据

小提醒 #

• 记得把公式里的A2:D100这类范围换成你实际的 data 范围，别把表头包含进去哦
• 如果你的“无目标”是目标值为0而不是空白，把ISBLANK(B2:B100)换成(B2:B100=0)就行，灵活调整判断条件

备注：内容来源于stack exchange，提问作者KE MS