如何修复Excel LAMBDA逆透视公式以返回全部SKU及价格数据

问题描述 #

现有用于逆透视价格数据的LAMBDA公式，仅能返回列表中3个SKU里的1个结果，需调整公式以返回全部SKU的目标逆透视格式。当前使用A列为SKU_col，B:D列为FL_cols，示例输入数据如下：

原公式代码：

=LAMBDA(SKU_col,FL_cols,
    LET(SCT,COUNTA(SKU_col)-2,      
        SKUA,INDEX(SKU_col,3,1):INDEX(SKU_col,SCT,1),
        FLC,INDEX(FL_cols,3,1):INDEX(FL_cols,SCT,1),     
        FLP,INDEX(FL_cols,3,2):INDEX(FL_cols,SCT,2),     
        FLU,INDEX(FL_cols,3,3):INDEX(FL_cols,SCT,3),
        SROWS,SEQUENCE(ROWS(SCT*3)),
        SR,CEILING(SROWS/3,1),
        MD,IF(MOD(SROWS,3)=0,3,MOD(SROWS,3)),
            VSTACK( HSTACK(INDEX(SKUA,SR,1),INDEX(FLC,SR,1)),
                    HSTACK(INDEX(SKUA,SR,1),INDEX(FLP,SR,1)),
                    HSTACK(INDEX(SKUA,SR,1),INDEX(FLU,SR,1))
            )))

问题分析 #

原公式核心问题在于VSTACK逻辑错误：将三列价格数据分别与SKU数组合并后堆叠，导致重复生成单SKU的结果，而非按每个SKU对应三行价格的格式输出。同时用COUNTA计算行数的方式不够灵活，冗余的序列计算增加了逻辑复杂度。

修正后的公式 #

=LAMBDA(SKU_col,FL_cols,
    LET(
        // 跳过前2行表头，提取有效SKU数据
        SKUA,DROP(SKU_col,2),
        // 跳过前2行表头，提取三列价格数据
        FL_data,DROP(FL_cols,2),
        // 生成每个SKU重复3次的数组，匹配价格列的行数
        SKU_repeat,TOCOL(INDEX(SKUA,CEILING(SEQUENCE(ROWS(SKUA)*3)/3,1),1)),
        // 将多列价格数据转为单列（逆透视核心）
        FL_unpivot,TOCOL(FL_data),
        // 合并SKU列与逆透视后的价格列
        HSTACK(SKU_repeat,FL_unpivot)
    )
)

修正说明 #

• 改用DROP函数直接跳过表头行，替代原公式中INDEX+COUNTA的复杂写法，适配不同数据行数。
• 使用TOCOL函数实现价格列的逆透视，将多列转为单列；同时生成对应重复的SKU数组，确保每个SKU与三行价格数据一一对应。
• 移除冗余的MOD、CEILING循环计算，简化公式逻辑，提升可读性与稳定性。

预期输出 #

修正后公式将输出如下格式的结果：

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