Excel中实现数值按行展开与均匀分配的方法求助

需求明确 #

现有三组横向数据：

• 第一行：基础数值（如月度起始日期）
• 第二行：对应基础值的展开数量（如当月天数）
• 第三行：对应基础值的总金额（如月度预算）

需将数据展开为两行纵向结果：

1. 序列数值：基础值 + 1 到展开数量的连续序列（如每日日期）
2. 对应金额：总金额除以展开数量，且展开后金额总和与原数据完全一致

问题分析 #

你之前使用的公式=HSTACK(BYCOL(R8C3:R8C5,LAMBDA(n,SEQUENCE(n,1))))仅处理了「展开数量」维度，未关联基础值和金额数据，因此无法实现完整需求。

解决方案 #

假设数据区域为A1:C3（A1:C1=基础值，A2:C2=展开数量，A3:C3=总金额），使用以下数组公式即可实现需求：

=LET(
    base_vals, A1:C1,
    expand_counts, A2:C2,
    total_amounts, A3:C3,
    // 遍历每一列生成对应展开数据
    group_data, BYCOL(HSTACK(base_vals, expand_counts, total_amounts), LAMBDA(col,
        LET(
            b, INDEX(col, 1),
            c, INDEX(col, 2),
            a, INDEX(col, 3),
            // 处理金额避免浮点误差，确保总和一致
            daily_amount, IF(SEQUENCE(c) = c, a - (a/c)*(c-1), a/c),
            // 生成日期序列+每日金额的组合
            HSTACK(SEQUENCE(c, 1, b+1), daily_amount)
        )
    )),
    // 合并所有分组数据并添加表头
    VSTACK({"序列数值","每日金额"}, TOROW(group_data, 2))
)

公式说明 #

1. LET函数定义变量，简化公式结构，提升可读性
2. BYCOL遍历每一组（基础值+数量+金额），针对每组生成对应的展开行
3. 金额计算加入了浮点误差修正：最后一行金额用总金额减去前面所有拆分金额的和，确保总和完全匹配原数据
4. TOROW将横向生成的各组数据转为纵向，VSTACK添加表头后输出最终结果

后续周分组建议 #

展开数据后，可通过以下步骤按周分组汇总：

• 添加辅助列，用WEEKNUM(序列数值, 2)提取周数（参数2表示周一为一周起始）
• 使用PIVOTBY函数或数据透视表，按周数汇总每日金额

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