在 Excel 2010 中将数值集中到某个范围内的总计

Question

给定一列数值，是否可以对值进行求和，直到总数达到特定阈值，然后重置总和并继续？例如，

Val Sum
103 
52  155
47  
60  
103 210
100 
76  176
163 163
169 169
87  
103 190
84  
31  
59  174
87  
49  
67  203

这对数字组进行求和，直到值超过 150，然后重置计数器。

Answer 1

我将发布它更多的是为了概念证明，而不是真正的答案，或者为了我可以改进这个的情况（Issun 可能也有一个想法）。

这里是：
假设您的 Val 位于A 列，Sum 位于B 列，标题位于第 1 行。
在C 列中，我们将设置一个公式，该公式将告诉您求和的“最后一个”单元格 > >在单元格 C3 中，输入：

{=MAX(IF($B$2:B2=0;0;ROW($B$2:B2)))}

这是一个数组公式，您必须使用 CtrlShiftEnter 进行验证。

在单元格 B3 中，您必须输入第一个值（示例中为 155），抱歉，我没有找到合适的解决方法。
在单元格 B4 中，输入：

=IF(SUM($A$1:A4)-SUM(INDIRECT("$B$1:"&ADDRESS(C4;2)))>150;SUM($A$1:A4)-SUM(INDIRECT("$B$1:"&ADDRESS(C4;2)));0)

然后将其拖至删除公式直到值的末尾。

[编辑] 实际上，您可以将其全部放入单元格 B2 中的单个公式中：

{=IF(SUM($A$1:A2)-SUM(INDIRECT("$B$1:"&ADDRESS(MAX(IF($B$1:B1=0;0;ROW($B$1:B1)));2)))>150;SUM($A$1:A2)-SUM(INDIRECT("$B$1:"&ADDRESS(MAX(IF($B$1:B1=0;0;ROW($B$1:B1)));2)));0)}

并将其拖放到值的末尾。似乎也有效。

Answer 2

最简单的方法（不需要数组公式，更不用说 VBA）：

在 B2 中，输入 =IF(B1<150,B1+A2,A2) 并向下拖动。其总和将达到 150 或更多，然后从零重新开始。当然，中间的总和（即达到 150）也会显示。如果这让您烦恼，您会想到以下几个选项：

• 在 C2 中输入 =IF(B2>=150,B2,"") 并向下拖动，然后隐藏 B 列。
• 对列应用条件格式B 使得 150 以下的数字以白色字体显示。

Answer 3

这是 VBA 中的解决方案（抱歉，我知道您想要一个论坛）。显然，您可以将范围调整为您需要的任何值。请注意，我使用 LONG 作为数据类型（并且我将其转换为 long 以避免出现一些错误），但如果您有任何浮点数，则需要使用 DOUBLE。

Sub SumIt()

Dim cell As range
Dim currentCount As Long

For Each cell In range("A2:A100")
    currentCount = currentCount + CLng(cell.Value)
    If currentCount > 150 Then
        cell.Offset(0, 1).Value = currentCount
        currentCount = 0
    End If
Next

End Sub

工作原理：我循环遍历范围内的每个单元格，并将值添加到名为 currentCount 的变量中。然后我检查它是否超过 150，如果是，我将 currentCount 粘贴到 B 列（同一行）并重置计数并移至下一个单元格。简单的！