如何根据预计前置时间计算预计获批产品数量

我有一张记录不同产品生产信息的表格，每个产品从生产到获批销售有对应的预计审批前置时间（Expected approval Lead time），需要按日统计每天预计获批的产品数量。示例表格如下：

一、Excel/Google Sheets 实现方案 #

核心逻辑：当日获批数量 = 所有「生产日 + 前置时间 = 当前日」的生产数量之和

1. 计算常规日期的获批量 #

假设数据位于A2:E13区域，在空白列（如F列）输入数组公式：

=SUMIFS($C$2:$C$10, $A$2:$A$10, $A2 - $D$2:$D$10, $B$2:$B$10, $B2)

• Excel需按 Ctrl+Shift+Enter 确认数组公式；Google Sheets直接回车即可
• 公式说明：匹配同产品、生产日加前置时间等于当前日的记录，求和生产数量

2. 计算跨周期（next period）的获批量 #

统计所有生产日加前置时间超过最大统计日的生产数量之和，公式：

=SUMIFS($C$2:$C$10, $A$2:$A$10 + $D$2:$D$10, ">"&MAX($A$2:$A$10), $B$2:$B$10, $B11)

二、SQL 实现方案 #

通过自连接匹配审批日期，再聚合求和：

-- 统计常规日期的获批量
SELECT
    t1.day AS approval_day,
    t1.product,
    COALESCE(SUM(t2.produced_quantity), 0) AS approved_quantity
FROM
    your_table t1
LEFT JOIN
    your_table t2 
    ON t1.product = t2.product 
    AND t1.day = CAST(t2.day AS INT) + t2.expected_approval_lead_time
WHERE
    t1.day != 'next period'
GROUP BY
    t1.day, t1.product

UNION ALL

-- 统计跨周期的获批量
SELECT
    'next period' AS approval_day,
    product,
    SUM(produced_quantity) AS approved_quantity
FROM
    your_table
WHERE
    CAST(day AS INT) + expected_approval_lead_time > (SELECT MAX(CAST(day AS INT)) FROM your_table WHERE day != 'next period')
    AND day != 'next period'
GROUP BY
    product;

• 自连接部分匹配生产日加前置时间等于审批日的记录，聚合求和
• 第二部分筛选生产日加前置时间超过最大统计日的记录，作为跨周期获批量

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