Excel 2019如何动态排序筛选Top/倒数N值（兼容365）

Excel 2019不支持365专属的动态数组溢出、FILTER函数，无需VBA即可用原生函数实现需求，所有公式适配2019版本规则，操作步骤如下：

1. 计算倒数N位阈值 #

在原365版本存放阈值的E22单元格输入公式，直接回车即可：
=SMALL(Metrics!$AL$2:$AL$41,5)

作用是从Metrics表AL列（产能列）的有效数据中，取从小到大排第5位的数值作为筛选阈值，和365版本的判断基准对齐。如果需要调整筛选数量，直接把公式里的5改成对应数值即可。

2. 批量提取符合条件的部门（替代FILTER函数） #

在原365版本存放部门结果的起始单元格（即原D25位置）输入以下公式，输入完成后不要直接按回车，按住Ctrl+Shift组合键再按回车确认数组公式，之后把公式向下拖动填充4行，凑够5行结果位：

=IFERROR(INDEX(Metrics!$B$2:$B$41,SMALL(IF(Metrics!$AL$2:$AL$41<=$E$22,ROW(Metrics!$B$2:$B$41)-ROW(Metrics!$B$2)+1),ROW(A1))),"")

公式逻辑：

• 内层IF逐行判断产能是否符合阈值要求，符合条件的返回该行在B列数据区域内的相对位置，不符合的返回逻辑值FALSE
• SMALL函数按顺序依次提取第1、2…5个符合条件的位置序号，向下填充时ROW(A1)会自动递增，实现批量取值
• INDEX根据位置序号从部门列返回对应部门名称，IFERROR将无匹配值的行返回空文本，避免出现错误码

这里用-ROW(Metrics!$B$2)+1计算偏移量，不管数据从第几行开始都能自动适配，不需要手动算偏移值。如果要完全对齐你原来365版本的<E22判断逻辑，把公式里的<=改成<、把SMALL的第二个参数改成6即可。

3. 批量提取对应产能数值（替代带溢出引用的VLOOKUP） #

在部门列右侧相邻的结果起始单元格（原数值列E25位置）输入以下公式，同样按Ctrl+Shift+Enter三键确认，向下填充和部门列对齐即可：

=IFERROR(INDEX(Metrics!$AM$2:$AM$41,MATCH(D25,Metrics!$B$2:$B$41,0)),"")

• 用INDEX+MATCH组合替代原带溢出引用#的VLOOKUP，不需要计算列号，后续调整源表列顺序也不会导致匹配错误
• 因为部门名称唯一，MATCH不会出现匹配偏差，IFERROR同样处理空行的错误值
• 后续做图表直接引用这5行部门、数值的结果区域即可，源数据更新时公式会自动重算，动态效果和365版本一致。

使用注意事项 #

• 所有数组公式在Excel 2019中编辑、修改后都需要重新按Ctrl+Shift+Enter三键确认，否则会返回#VALUE!错误
• 如果需要取Top N值，只需要把SMALL函数换成LARGE函数，判断逻辑完全一致
• 结果区域不要手动输入内容，避免公式被覆盖导致取值错误

备选VBA方案（按需选用） #

如果不想手动处理三键数组公式，可以用VBA实现，按Alt+F11打开VBA编辑器，插入新模块后粘贴以下代码，绑定按钮触发即可一键更新结果：

Sub UpdateBottomNDept()
    Dim sourceSht As Worksheet, resSht As Worksheet
    Dim dataArr, resArr(1 To 5, 1 To 2)
    Dim threshold As Double, i As Long, resCnt As Long
    '配置参数，可按需修改
    Const BOTTOM_NUM As Integer = 5 '取倒数N位
    Const SOURCE_SHT_NAME As String = "Metrics" '源数据表名
    Const RES_START_CELL As String = "D25" '结果输出起始单元格
    Const DEPT_COL As String = "B" '部门列
    Const VAL_COL As String = "AL" '产能判断列
    Const OUTPUT_VAL_COL As String = "AM" '要提取的数值列
    
    Set sourceSht = ThisWorkbook.Worksheets(SOURCE_SHT_NAME)
    Set resSht = Application.Caller.Parent
    '计算阈值
    threshold = Application.WorksheetFunction.Small(sourceSht.Range(VAL_COL & "2:" & VAL_COL & "41"), BOTTOM_NUM)
    '读取源数据到数组提升计算效率
    dataArr = sourceSht.Range(DEPT_COL & "2:" & OUTPUT_VAL_COL & "41").Value
    resCnt = 0
    For i = 1 To UBound(dataArr)
        If dataArr(i, sourceSht.Range(VAL_COL & ":" & VAL_COL).Column - sourceSht.Range(DEPT_COL & ":" & DEPT_COL).Column + 1) <= threshold Then
            resCnt = resCnt + 1
            resArr(resCnt, 1) = dataArr(i, 1)
            resArr(resCnt, 2) = dataArr(i, UBound(dataArr, 2))
            If resCnt = BOTTOM_NUM Then Exit For
        End If
    Next
    '输出结果
    resSht.Range(RES_START_CELL).Resize(BOTTOM_NUM, 2).Value = resArr
End Sub

内容的提问来源于stack exchange，提问作者Matt G