求高效Excel动态数组公式：按Code匹配、汇总Qty并转置排版

需求概述 #

• 以Code为触发条件，筛选对应Qty和Date数据
• 按日期汇总Qty后，将「日期+汇总Qty」的组合横向转置排版（参考目标效果）

现有问题 #

当前使用的公式过长，在数千行数据场景下导致Excel运行卡顿，且仅能生成单行结果，需手动复制到千余行中。现有公式：

=LET(trigger,U2,unit,L2,triggerdel,'Delivery Report'!AA:AA,delr,HSTACK('Delivery Report'!A:A,'Delivery Report'!N:O),data,FILTER(delr,triggerdel=trigger),date,UNIQUE(CHOOSECOLS(data,1)),LET(pcs,INDEX(data,,2),kgs,INDEX(data,,3),deldat,INDEX(data,,1),kotak,VSTACK(TRANSPOSE(date),TRANSPOSE(MMULT(--(TRANSPOSE(deldat)=date),pcs))),TEXTSPLIT(TEXTJOIN("|",,CONCATENATE(CHOOSEROWS(kotak,1),"|"&CHOOSEROWS(kotak,2))),"|")))

原始数据 #

目标效果 #

解决方案 #

1. 优化单行公式（降低卡顿） #

核心优化点是缩小数据引用范围（避免整列引用）、简化逻辑层级，公式如下：

=LET(
    trigger,U2,
    // 替换为实际数据范围，比如原始数据在Delivery Report的A2:E8
    rawData,'Delivery Report'!$A$2:$E$8,
    // 筛选当前Code对应的Date和Qty
    filtered,FILTER(CHOOSECOLS(rawData,5,4),CHOOSECOLS(rawData,3)=trigger),
    // 提取唯一日期并排序
    uniqueDates,SORT(UNIQUE(CHOOSECOLS(filtered,1))),
    // 按日期汇总Qty
    sumQty,BYROW(uniqueDates,LAMBDA(d,SUM(FILTER(CHOOSECOLS(filtered,2),CHOOSECOLS(filtered,1)=d)))),
    // 转置后拆分输出
    combined,HSTACK(uniqueDates,sumQty),
    TEXTSPLIT(TEXTJOIN("|",,TOCOL(TRANSPOSE(combined))),"|")
)

• 用具体数据范围替代A:A这类整列引用，减少不必要的计算
• 用BYROW替代MMULT，逻辑更直观，降低计算复杂度
• 新增SORT确保日期顺序统一

2. 批量生成所有结果（无需逐行复制） #

如果要一次性生成所有Code的汇总结果，使用动态数组公式直接输出整个表格，避免千余行复制操作：
假设目标表格的Code列表在D2:D3，在E2输入公式：

=LET(
    rawData,'Delivery Report'!$A$2:$E$8,
    // 获取唯一Code列表
    uniqueCodes,SORT(UNIQUE(CHOOSECOLS(rawData,3))),
    // 定义处理单个Code的逻辑
    processCode,LAMBDA(code,
        LET(
            filtered,FILTER(CHOOSECOLS(rawData,5,4),CHOOSECOLS(rawData,3)=code),
            uniqueDates,SORT(UNIQUE(CHOOSECOLS(filtered,1))),
            sumQty,BYROW(uniqueDates,LAMBDA(d,SUM(FILTER(CHOOSECOLS(filtered,2),CHOOSECOLS(filtered,1)=d)))),
            combined,HSTACK(uniqueDates,sumQty),
            TOCOL(TRANSPOSE(combined))
        )
    ),
    // 生成所有结果并对齐列数
    results,BYROW(uniqueCodes,processCode),
    MAX_COLS,MAX(BYROW(results,LAMBDA(r,COUNTA(r)))),
    IFERROR(INDEX(results,SEQUENCE(ROWS(uniqueCodes)),SEQUENCE(MAX_COLS)),"")
)

该公式会自动扩展生成所有Code对应的横向转置结果，大幅提升批量处理效率。

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