Excel库存规划表中SUMIF与OFFSET结合排除月度列求和问题

表格背景 #

• 列结构：每6列一组（5个周列，标题为Wk 1/Wk 2等 + 1个月度汇总列，标题为Jan/Feb等），循环覆盖12个月，B列为首个数据列
• 核心参数：C2=按周计算的产品提前期（最大值26周），第6行=销售预测数据，第10行=计划订单（需实现动态求和）
• 当前问题：原公式=SUM(B6:OFFSET(B6,0,C2-1))会错误包含月度汇总列，需求是仅对前C2个周列的销售预测求和，自动跳过所有月度列

方案1：兼容旧版Excel（无动态数组） #

使用SUMPRODUCT结合列标题判断，精准筛选周列并限定提前期范围，公式输入到B10：

=SUMPRODUCT(
  --(LEFT($B$1:$XFD$1,2)="Wk"),
  --(COUNTIF($B$1:INDEX($B$1:$XFD$1,COLUMN()),"Wk*")<=$C$2),
  $B$6:$XFD$6
)

公式解释 #

1. LEFT($B$1:$XFD$1,2)="Wk"：筛选出所有标题以Wk开头的周列，返回布尔值数组
2. COUNTIF($B$1:INDEX($B$1:$XFD$1,COLUMN()),"Wk*")<=$C$2：对每一列统计从B列到当前列的周列总数，仅保留总数≤提前期C2的列
3. 两个--将布尔值转换为1/0，与第6行的销售预测数据相乘后求和，自动跳过月度汇总列

注意：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入；若标题存在大小写差异，可改为LEFT(UPPER($B$1:$XFD$1),2)="WK"

方案2：Excel 365/2021 动态数组方案（更简洁） #

利用FILTER+TAKE实现动态筛选与截取，公式输入到B10：

=SUM(TAKE(FILTER($B$6:$XFD$6,LEFT($B$1:$XFD$1,2)="Wk"),,$C$2))

公式解释 #

1. FILTER($B$6:$XFD$6,LEFT($B$1:$XFD$1,2)="Wk")：提取第6行中所有周列的销售预测数据，生成横向动态数组
2. TAKE(...,,$C$2)：截取该数组的前C2个元素（横向数组需留空第二个参数）
3. SUM对截取后的数组求和，完美匹配需求

验证示例 #

当C2=15时，两个公式都会自动跳过Jan/Feb/Mar三个月度列，求和B6:F6（第1-5周）+H6:K6（第6-10周）+N6:R6（第11-15周），与你给出的手动求和示例结果完全一致。

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