Excel中查找满足特定条件的第n个单元格值的方法

核心逻辑 #

1. 动态适配数据区域：自动识别表格的有效行/列，无需手动调整范围
2. 精准筛选目标单元格：仅保留「自身非空白」且「正上方单元格为空白」的单元格（第一行无上方单元格，默认排除）
3. 按指定顺序排序：先按从下到上、左到右的规则整理筛选结果，再对值进行升序排列
4. 提取第n个结果：可直接获取升序后的第n个目标值

公式实现（获取第n个升序值） #

假设n的值放在单元格X1中，使用以下动态数组公式：

=LET(
    data, A1:INDEX(A:Z, MAX(ROW(A:A)*(A:A<>"")), MAX(COLUMN(1:1)*(1:1<>""))),
    rows, ROWS(data),
    cols, COLUMNS(data),
    // 整合单元格的行号、列号、值及上方空白判断
    cell_info, HSTACK(
        SEQUENCE(rows, cols),
        SEQUENCE(rows, cols, ,0),
        data,
        IF(SEQUENCE(rows, cols)=1, FALSE, INDEX(data, SEQUENCE(rows, cols)-1, SEQUENCE(rows, cols, ,0))="")
    ),
    // 筛选符合条件的单元格
    filtered, FILTER(cell_info, (INDEX(cell_info,,3)<>"")*(INDEX(cell_info,,4))),
    // 按从下到上、左到右排序
    sorted_by_order, SORT(filtered, {1,2}, {-1,1}),
    // 对筛选出的值升序排列
    sorted_values, SORT(INDEX(sorted_by_order,,3)),
    // 返回第n个值
    INDEX(sorted_values, X1)
)

简化公式（返回所有升序后的目标值） #

如果需要直接输出所有符合条件的升序值，可使用简化版：

=LET(
    data, A1:INDEX(A:Z, MAX(ROW(A:A)*(A:A<>"")), MAX(COLUMN(1:1)*(1:1<>""))),
    filtered, FILTER(data, (data<>"")*(IF(ROW(data)=1, FALSE, INDEX(data, ROW(data)-1, COLUMN(data))=""))),
    SORT(filtered)
)

注意事项 #

• 仅支持带动态数组功能的Excel版本（365/2021及以上）
• 表格存在合并单元格时，需先取消合并再使用公式
• 若需将第一行视为「上方空白」，可将公式中IF(SEQUENCE(rows, cols)=1, FALSE, ...)修改为IF(SEQUENCE(rows, cols)=1, TRUE, ...)

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