求可实现类数据透视表的Dynamic array formula以汇总每周数据

核心公式（Sheet2的E2单元格输入） #

=LET(
    srcData, Sheet1!A2:D43021,
    dates, INDEX(srcData,,1),
    artists, INDEX(srcData,,2),
    songs, INDEX(srcData,,3),
    values, INDEX(srcData,,4),
    targetArtists, Sheet2!A2:A,
    targetSongs, Sheet2!B2:B,
    targetDates, Sheet2!E1:XFD1,
    MAKEARRAY(
        COUNTA(targetArtists),
        COUNTA(targetDates),
        LAMBDA(r,c,
            XLOOKUP(
                1,
                (artists=INDEX(targetArtists,r))*(songs=INDEX(targetSongs,r))*(dates=INDEX(targetDates,c)),
                values,
                ""
            )
        )
    )
)

公式解析 #

• LET函数：提前提取Sheet1的核心数据列（日期、艺术家、歌曲、目标值），避免重复引用整个4万+行的大区域，减少Excel重复计算开销，同时提升公式可读性。
• MAKEARRAY：根据Sheet2的有效艺术家/歌曲行数、有效日期列数，生成对应大小的数组框架，自动实现向下、向右的双向溢出填充。
• LAMBDA+XLOOKUP：遍历数组的每个单元格位置（r=行号，c=列号），通过三个条件的乘积（仅当三个条件同时满足时结果为1）精准匹配Sheet1的对应记录，返回THIS WEEK值；无匹配时返回空文本。

性能优化要点 #

1. 预提取数据：LET函数将Sheet1的大区域拆解为独立列引用，避免Excel反复读取完整数据集，大幅降低计算负载，适配性能不足的设备。
2. 动态数组原生支持：MAKEARRAY属于Excel动态数组函数，一次性生成完整结果区域，无需逐个单元格设置公式，从根源解决批量公式的性能问题。
3. 高效条件匹配：XLOOKUP的多条件匹配采用数组运算，比嵌套IF或多次LOOKUP逻辑更简洁、计算效率更高。

替代方案（INDEX+MATCH组合） #

若对XLOOKUP兼容性有顾虑，可使用INDEX+MATCH实现相同效果：

=LET(
    src, Sheet1!A2:D43021,
    sourceKeys, CHOOSECOLS(src,1,2,3),
    sourceValues, CHOOSECOLS(src,4),
    targetArtists, Sheet2!A2:A,
    targetSongs, Sheet2!B2:B,
    targetDates, Sheet2!E1:XFD1,
    targetKeys, HSTACK(targetDates, targetArtists, targetSongs),
    INDEX(
        sourceValues,
        MATCH(
            TOROW(targetKeys,,TRUE),
            TOROW(sourceKeys,,TRUE),
            0
        )
    )
)

该方案通过TOROW将多条件组合为单行键值，再通过MATCH匹配位置，最终用INDEX返回对应值，同样支持双向溢出。

注意事项 #

• 确保Sheet2的A2:A（艺术家）、B2:B（歌曲）为连续非空列表，COUNTA会以此计算结果区域的行数；若存在空行，可改用TOCOL(targetArtists,1)提取非空值。
• 日期表头E1:XFD1需与Sheet1的DATE列格式完全一致（如均为日期格式而非文本），避免匹配失败。
• 若Sheet1存在重复的（DATE+ARTIST+SONG）组合，公式会返回第一个匹配的THIS WEEK值；如需返回最新/特定值，可先对Sheet1数据按日期排序后再使用公式。

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