求Excel公式：累计H列至L列（上限172）并填补缺口显示用量

你的公式=MIN(SUM($H$2:H2), 172)只能将累计值截断到172，但无法单独标记出填补缺口的金额，也没法针对性地用后续数据补全缺口。以下是两种可行的解决方案：

方案1：Excel 365/2021 动态数组版 #

在L2单元格输入公式后回车，会自动填充至所有有数据的行：

=LET(
    累计序列, SCAN(0, H2:H100, LAMBDA(当前累计, 当前值, 当前累计+当前值)),
    上限值, 172,
    截断后序列, SCAN(0, 累计序列, LAMBDA(a,b, IF(b<=上限值, b, 上限值))),
    缺口金额, 上限值 - INDEX(截断后序列, ROWS(截断后序列)-1),
    IF(截断后序列=上限值, IF(ROW(截断后序列)=XMATCH(上限值, 截断后序列), 缺口金额, 上限值), 截断后序列)
)

逻辑说明： #

1. 用SCAN生成H列的逐行累计和
2. 将累计和超过172的部分截断为172，得到初步的累计序列
3. 计算最后一个未达上限的累计值与172的差值（即需要填补的缺口）
4. 对序列做最后调整：第一个达到172的单元格显示缺口金额，后续已达上限的单元格保持172，未达上限的显示累计值

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

在L2输入以下公式，手动下拉填充至所有行：

=IF(SUM($H$2:H2)<=172, SUM($H$2:H2), IF(SUM($H$2:H1)<172, 172-SUM($H$2:H1), 172))

逻辑说明： #

• 若当前行累计和≤172，直接显示累计值
• 若上一行累计和未达172但当前行累计超上限，显示填补缺口的金额（172减去上一行累计值）
• 若上一行已达172，当前行直接显示172

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