如何在Excel单个单元格生成单行多列数据的全排列？

• 数据为单行多列结构（最多5列），需在第6列单个单元格生成该行所有非空值的全排列，用分号分隔：
  • 2个非空值生成2种排列；4个非空值生成24种排列，每行非空值数量1-5个不等
• 需用Excel 365公式实现，支持LET、LAMBDA函数，禁用VBA和Power Query

• Stack Overflow现有方案仅适配二维数组，无法处理一维单行数据
• 自行编写的公式可生成无重复的1-5位数字序列，但无法高效转换为对应值的全排列

假设数据位于A1:E1区域，可直接使用以下公式：

=LET(
    原始数据, A1:E1,
    非空值, FILTER(原始数据, 原始数据<>""),
    元素数量, COUNTA(非空值),
    排列索引, PERMUTATIONA(SEQUENCE(元素数量), 元素数量),
    单个排列, BYROW(排列索引, LAMBDA(r, TEXTJOIN("",, INDEX(非空值, r)))),
    最终结果, TEXTJOIN(";",, 单个排列),
    最终结果
)

公式拆解 #

• 原始数据：定义单行数据源的单元格区域，可根据实际位置调整
• 非空值：过滤掉原始数据中的空单元格，提取有效元素组成数组
• 元素数量：统计有效元素的总个数
• 排列索引：通过PERMUTATIONA生成所有元素位置的全排列索引矩阵（Excel 365专属函数，直接输出全排列的位置序列）
• 单个排列：遍历每一组索引，用INDEX匹配对应元素并拼接成单个排列字符串
• 最终结果：将所有排列用分号分隔，合并为单个单元格的内容

兼容说明 #

若你的Excel 365版本未包含PERMUTATIONA函数，可使用以下替代公式（基于递归LAMBDA生成全排列）：

=LET(
    原始数据, A1:E1,
    非空值, FILTER(原始数据, 原始数据<>""),
    元素数量, COUNTA(非空值),
    全排列, LAMBDA(arr, n, IF(n=1, arr, TEXTJOIN(",",, BYROW(全排列(FILTER(arr, arr<>INDEX(arr,n)), n-1), LAMBDA(x, INDEX(arr,n)&x&","&x&INDEX(arr,n)))))),
    排列列表, IF(元素数量=1, 非空值, TEXTSPLIT(全排列(非空值, 元素数量), ",")),
    最终结果, TEXTJOIN(";",, UNIQUE(排列列表)),
    最终结果
)

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