Excel中基于日期范围与地点计算指定列库存总量的函数使用问题

我完全懂你试了COUNTIFS、SUMIFS却达不到预期的那种挫败感！咱们先把需求理清楚，再给你适配的解决方案：

需求拆解 #

你需要在「指标看板（Metrics Deck）」中，根据指定的周日期（比如Week19对应的4/08/2024）和地点，汇总「原始数据（Raw Data）」中每一行(列B - 列C)*-1的数值总和，对吧？

先说说为啥之前的SUMIFS没起效 #

SUMIFS的求和项只能直接引用单一列，没法直接把(B-C)*-1这种计算式作为求和项，所以得换用能处理动态计算的函数。

解决方案（分两种Excel版本） #

先假设：

• 指标看板工作表名：Metrics Deck
• 原始数据工作表名：Raw Data
• 看板中，周日期在A列（比如A2是Week19的4/08/2024），地点在B列（比如B2是Location A）

方案1：兼容所有Excel版本（用SUMPRODUCT）

在看板中需要填充结果的单元格（比如C2）输入以下公式，然后下拉/右拉填充：

=SUMPRODUCT(
    ('Raw Data'!$D:$D = $B2) *  // 匹配对应地点
    (YEAR('Raw Data'!$A:$A) = YEAR($A2)) *  // 匹配同一年份
    (WEEKNUM('Raw Data'!$A:$A, 2) = WEEKNUM($A2, 2)) *  // 匹配同一周（参数2表示周一是一周第一天，可按需改1为周日开头）
    ('Raw Data'!$B:$B - 'Raw Data'!$C:$C)*-1  // 计算每一行的库存值
)

如果你的周范围是固定的“起始日期+7天”，也可以把周匹配部分换成日期区间判断：

=SUMPRODUCT(
    ('Raw Data'!$D:$D = $B2) *
    ('Raw Data'!$A:$A >= $A2) *
    ('Raw Data'!$A:$A < $A2 + 7) *
    ('Raw Data'!$B:$B - 'Raw Data'!$C:$C)*-1
)

方案2：适合Excel 365/2021及以上（用SUM+FILTER，更直观）

动态数组函数用起来更清晰，同样在结果单元格输入：

=SUM(
    FILTER(
        ('Raw Data'!$B:$B - 'Raw Data'!$C:$C)*-1,
        ('Raw Data'!$D:$D = $B2) *
        (YEAR('Raw Data'!$A:$A) = YEAR($A2)) *
        (WEEKNUM('Raw Data'!$A:$A, 2) = WEEKNUM($A2, 2)),
        0  // 无匹配结果时返回0，避免显示错误
    )
)

补充说明 #

• 记得把公式里的单元格引用（比如$B2、$A2）换成你实际看板中的对应单元格位置
• 如果你的周定义不是“周一为第一天”，调整WEEKNUM函数的第二个参数即可（1=周日开头，2=周一开头）

截图内容说明 #

• 指标看板：展示了Week19至Week22的对应日期，以及不同地点列，需要填充各周各地点的库存总量
• 原始数据：包含日期、On Hand（列B）、Allocated（列C）、Location（列D）等字段，需计算每行(On Hand - Allocated)*-1后按周和地点汇总

备注：内容来源于stack exchange，提问作者Peter Del Sol