基于数字与范围的Excel复杂姓名查找公式开发需求

需求说明 #

需要为**查找表（Lookup Table）中的姓名查找（Name Lookup）**列创建Excel公式，满足以下条件：

• **姓名表（Names Table）**存在空行，需过滤空值提取有效姓名
• 查找表的N列支持三种输入格式：
  • 输入a：提取姓名表中所有非空姓名
  • 逗号分隔的无序数字（如1,3,4）：按数字对应姓名表中非空姓名的序号提取（序号为跳过空行后的有效计数）
  • 范围+逗号分隔数字（如1-3,5、2,4-5,1）：展开范围后按有效姓名序号提取，自动去重重复序号

公式实现（Excel 365/2021+） #

在**查找表（Lookup Table）的姓名查找（Name Lookup）**列第一行输入以下公式，表格会自动适配行数增减：

=LET(
    valid_names, FILTER(Names Table[姓名（Name）], Names Table[姓名（Name）]<>"", ""),
    input, Lookup Table[@N],
    IF(input="a", TEXTJOIN(", ", TRUE, valid_names),
        LET(
            items, TEXTSPLIT(SUBSTITUTE(input, " ", ""), ","),
            expand_ranges, REDUCE("", items, LAMBDA(a, b,
                IF(ISNUMBER(SEARCH("-", b)),
                    VSTACK(a, SEQUENCE(RIGHT(b, LEN(b)-SEARCH("-", b)),,LEFT(b, SEARCH("-", b)-1))),
                    VSTACK(a, VALUE(b))
                )
            )),
            unique_numbers, UNIQUE(VALUE(expand_ranges)),
            selected_names, FILTER(INDEX(valid_names, unique_numbers), unique_numbers<=ROWS(valid_names), ""),
            TEXTJOIN(", ", TRUE, selected_names)
        )
    )
)

公式解释 #

1. valid_names：通过FILTER过滤姓名表中的空单元格，生成仅包含有效姓名的动态数组
2. input：引用当前行的N列输入内容
3. 输入为a的场景：直接用TEXTJOIN合并所有有效姓名，用逗号加空格分隔
4. 数字/范围输入处理：
   • items：去除输入中的空格后，按逗号拆分出单个项（如"1-3, 5"拆分为{"1-3","5"}）
   • expand_ranges：利用REDUCE遍历每个项，遇到范围格式（含-）则用SEQUENCE展开为连续数字，非范围项直接转为数值
   • unique_numbers：对展开后的序号去重，避免重复提取同一姓名
   • selected_names：用INDEX根据序号提取对应姓名，并用FILTER过滤超出有效姓名总数的无效序号
   • 最终用TEXTJOIN合并提取到的姓名

注意事项 #

• 仅支持Excel 365/2021及以上版本，需兼容动态数组函数
• 姓名表和查找表必须是Excel结构化表格（插入→表格），确保公式自动适配行数变化
• 序号规则：以跳过空行后的有效姓名为计数基准，而非表格物理行号

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