基于表格数据实现重叠数组求和的技术需求

前提假设 #

假设你的表格数据结构如下：

• 存储数组的工作表（如Sheet1）：A列为数组名称，B到AA列为数组元素（最多26个），表头分别为数组名称、列1至列26
• 输入数组组合的工作表（如Sheet2）：A列为输入区域，支持单行输入（如array1 + array2）或多行单独输入数组名称

通用公式（无需辅助列） #

在Sheet2的B1单元格输入以下动态数组公式（Excel 365/2021及以上版本支持），结果会自动溢出到右侧单元格：

=LET(
    // 处理输入：拆分单行加号分隔或多行输入的数组名称
    raw_input, TOCOL(Sheet2!A:A, 1),
    input_arr, TEXTSPLIT(TEXTJOIN("+",, raw_input), "+",, TRUE),
    // 指向存储数组的数据区域
    arr_data, Sheet1!A:AA,
    arr_names, arr_data[数组名称],
    // 生成每个数组的偏移量：第一个偏移0，第二个偏移1，以此类推
    num_arrs, COUNTA(input_arr),
    offsets, SEQUENCE(num_arrs,, 0),
    // 自定义函数：根据数组名称获取对应元素数组
    get_elements, LAMBDA(name, XLOOKUP(name, arr_names, arr_data[[列1]:[列26]])),
    elements_list, MAP(input_arr, get_elements),
    // 计算结果数组的总长度
    max_len, SUM(MAP(elements_list, LAMBDA(x, COUNTA(x)))) - (num_arrs - 1),
    // 计算每个位置的求和结果
    result, BYCOL(SEQUENCE(1, max_len), LAMBDA(k,
        SUM(MAP(SEQUENCE(num_arrs), LAMBDA(i,
            LET(
                offset, INDEX(offsets, i),
                elem_arr, INDEX(elements_list, i),
                pos, k - offset,
                IF(AND(pos >= 1, pos <= COUNTA(elem_arr)), INDEX(elem_arr, pos), 0)
            )
        ))
    ))),
    result
)

方案优势 #

• 性能高效：完全使用Excel内置动态数组函数实现，比VBA/脚本执行速度快数倍，尤其适合大量数组计算场景
• 无辅助列：所有逻辑封装在单个公式中，无需额外单元格存储中间结果
• 兼容双场景：自动处理单行加号分隔、多行单独输入两种组合方式，无需调整公式

示例验证 #

假设Sheet1数据：

1. 单行输入场景：Sheet2A1输入array1 + array2，公式输出结果：5, 7, 9（对应位置元素直接相加）
2. 多行输入场景：Sheet2A1输入array1、A2输入array2，公式输出结果：1, 6, 8, 6（array2元素依次与array1的后续元素对齐相加）

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