如何用Excel公式实现类似SQL CROSS APPLY的多列拆分展开？

假设原数据在A1:J50（第一行为表头，A=Calendar、B=PersonnelArea、C=PayScaleGroup为多值列，D-J为单值列），以下是纯公式实现方案：

核心逻辑 #

先计算每一行需要展开的总行数（三个多值列元素数量的乘积），生成展开行与原数据行的映射关系，再逐列拆分对应元素，全程严格保留原数据的行顺序。

公式实现 #

1. 生成展开行对应的原行号（示例放在K2单元格） #

=LET(
    rowCounts, BYROW(A$2:A$50, LAMBDA(r, 
        (LEN(TRIM(r))-LEN(SUBSTITUTE(TRIM(r),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,1)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,1)),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,2)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,2)),",",""))+1)
    )),
    cumTotal, SCAN(0, rowCounts, LAMBDA(a,b,a+b)),
    x, ROW()-1,
    XMATCH(x, cumTotal, 1)+1
)

下拉此公式直到出现#N/A，表示所有行已展开完成。

2. 拆分Calendar列（示例放在A2单元格） #

=LET(
    originalRow, $K2,
    splitVals, TEXTSPLIT(TRIM(A$originalRow),,","),
    calCount, ROWS(splitVals),
    paCount, LEN(TRIM(B$originalRow))-LEN(SUBSTITUTE(TRIM(B$originalRow),",",""))+1,
    psCount, LEN(TRIM(C$originalRow))-LEN(SUBSTITUTE(TRIM(C$originalRow),",",""))+1,
    offset, ROW()-1 - INDEX(SCAN(0, BYROW(A$2:A$50, LAMBDA(r, 
        (LEN(TRIM(r))-LEN(SUBSTITUTE(TRIM(r),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,1)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,1)),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,2)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,2)),",",""))+1)
    )), LAMBDA(a,b,a+b)), originalRow-2),
    i, QUOTIENT(offset, paCount*psCount)+1,
    INDEX(splitVals, i)
)

3. 拆分PersonnelArea列（示例放在B2单元格） #

=LET(
    originalRow, $K2,
    splitVals, TEXTSPLIT(TRIM(B$originalRow),,","),
    paCount, ROWS(splitVals),
    psCount, LEN(TRIM(C$originalRow))-LEN(SUBSTITUTE(TRIM(C$originalRow),",",""))+1,
    offset, ROW()-1 - INDEX(SCAN(0, BYROW(A$2:A$50, LAMBDA(r, 
        (LEN(TRIM(r))-LEN(SUBSTITUTE(TRIM(r),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,1)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,1)),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,2)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,2)),",",""))+1)
    )), LAMBDA(a,b,a+b)), originalRow-2),
    j, QUOTIENT(MOD(offset, paCount*psCount), psCount)+1,
    INDEX(splitVals, j)
)

4. 拆分PayScaleGroup列（示例放在C2单元格） #

=LET(
    originalRow, $K2,
    splitVals, TEXTSPLIT(TRIM(C$originalRow),,","),
    psCount, ROWS(splitVals),
    offset, ROW()-1 - INDEX(SCAN(0, BYROW(A$2:A$50, LAMBDA(r, 
        (LEN(TRIM(r))-LEN(SUBSTITUTE(TRIM(r),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,1)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,1)),",",""))+1)*
        (LEN(TRIM(OFFSET(r,0,2)))-LEN(SUBSTITUTE(TRIM(OFFSET(r,0,2)),",",""))+1)
    )), LAMBDA(a,b,a+b)), originalRow-2),
    k, MOD(offset, psCount)+1,
    INDEX(splitVals, k)
)

5. 提取单值列（以D列为例，放在D2单元格） #

=INDEX(D$2:D$50, $K2)

其他单值列（E-J）直接复制此公式，修改列标即可。

注意事项 #

• 需使用Excel 365/2021版本，依赖TEXTSPLIT、BYROW、SCAN、LET等函数
• 下拉公式时，直到出现#N/A即表示所有组合已展开完成
• 重复Calendar值不影响拆分逻辑，会严格保留原行对应的所有组合及顺序
• 空单元格或无逗号的单值单元格会自动处理为1个元素，拆分后保留原内容

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