计算具有可变范围的类似值的连续单元格的公式

计算具有可变范围的类似值的连续单元格的公式

我正在尝试想出一个公式,该公式对由另一列的文本值的变化决定的变量范围进行求和。我正在制作一个计算器,它可以告诉我何时对总存款进行了分类。以下是一个例子:

在此处输入图片描述

我将薪水单列在支票簿日志中,并将钱分成不同的类别。我希望 F5 中的计算仅对 E 列中连续出现 C 列“支票存款”描述的单元格的总和进行求和。只要 C 列中的单元格变为其他内容,无论它是 5 行还是 15 行,我都希望计算就此停止。

因此,在此示例中,F5 将是=F4-SUMIF(C4:C7,C4,E4:E7)。但是,如果下次使用时 C 列中的描述“支票存款”进一步下降,我需要 C4:C7 和 E4:E7 自动更改。

我希望这有意义。我知道有一种方法可以使用 VBA 代码,但如果可能的话,我想找到一个方程式来做到这一点。

答案1

对您要执行的操作做出一些假设,F5 中的以下公式可能会解决您的问题。

=F$4-(E$4+SUM((C$4:C$20=C$4)*(C$4:C$20=C$3:C$19)*(E$4:E$20)))

这是一个数组公式,因此必须使用 CTRL-Shift Enter 而不是直接按 Enter 键输入。如果输入正确,Excel 将在公式栏中用花括号 {} 括住公式。

其工作方式是创建一个数字数组,其中包含满足您所寻找条件的 E 列中的金额。

第一部分,(C$4:C$20=C$4)只要区域 C4:C20 等于“支票存款”,则为 TRUE。第二部分,(C$4:C$20=C$3:C$19)只要 C4:C20 中的单元格等于其上方的单元格,则为 TRUE。

将这两个逻辑值数组相乘相当于 AND() 运算,并将 TRUE/FALSE 值转换为 1 或 0。因此,此乘法的结果是数组{0;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0}。现在乘以(E$4:E$20)E 列中的金额数组,得到{0;50;50;50;0;0;0;0;0;0;0;0;0;0;0;0;0}

接下来,我们将这些值相加并添加回 E$4。(结果数组中缺少它,因为 C4 不等于其上方的单元格)。最后,从 F4 中的总数中减去所有这些,即可得出最终结果。

在此处输入图片描述

笔记:

  1. 您可以根据需要扩大或缩小范围。此公式检查至第 20 行。
  2. 您没有说明第一次中断后是否可以有更多连续的“支票存款”值。如果上面的表格在 C10 中有“支票存款”,则将计算 E10 中的值。

我希望这有帮助。

答案2

你的配方非常有效,布莱克伍德。

=F$4-(E$4+SUM((加元4:加元20=加元4)(4 加元:20 加元=3 加元:19 加元)(4 欧元兑 20 欧元)))

我只需将它插入现有公式的正确位置,它就能满足我的需要。非常感谢您抽出时间和帮助。我已经为此绞尽脑汁好几天了。我不知道为什么,但我就是很难理解数组。

再次感谢!

相关内容