每当在另一列中输入金额时，如何计算支付的总额 [英] how to calculate incremental total amount paid everytime an amount is entered in another column

问题描述

当在同一单元格（G2）中输入值时，总金额递增（单元格：H2）。例如今天如果您在G2中输入值30，则H2中的总金额为30;当您明天进入在G2（同一个单元格）中，值为50，H2中的总量应为80;当您在第二天再次进入同一单元格（G2）80时，总金额现在应为160 ...希望您可以清楚了解...非常感谢...

解决方案

更新：

我发现这是工作，不需要VBA。实际上应该很简单从您的文件开始：

  | G | H | 
 | --- + ------------- + ------------------- | 
 | 1 |支付金额|支付总额| 
 | 2 | 0 | 0 | 
  

现在转到您的设置（对于2010年文件 - >选项 - >公式，用于2003工具 - >选项 - >计算），勾选启用迭代计算，并将最大迭代设置为1.现在在 H2 中输入您的公式为 = G2 + H2 。这是一个循环引用（最简单的是，单元格 H2 在计算中指的是自己），除非你设置了正确的计算设置，否则它将无法正常工作。如果您将计算在相同的设置选项卡中设置为手动，则只有按F9时，总计才会更新。如果您将其设置为自动，则会在单元格 G2 中进行更改时自动更新。

原始答案

假设您的数据从 A1 开始，此公式将总结列中的所有数字 A

  = SUM（A：A）
  pre> 
 
 
如果您在此列中没有其他内容（并不介意明显的过度使用），这将会起作用。如果您需要更具体一些，并更新动态增长/缩小的范围，我们需要更加聪明一些，并使用 INDIRECT 或 OFFSET 如下
  = SUM（INDIRECT（A2：A& COUNTA A）））
 = SUM（OFFSET（A2,0,0，COUNTA（A：A）-1,1））
  
如果您不了解这些功能，请在帮助中查看它们，他们可以非常有用。
 
 
 
另一件事要清理事情就是用上面的例子来定义一个命名范围 OFFSET 公式，并在您的公式中使用它。这样可以更容易地反复使用指定的范围并提高公式的可读性。我将定义 PaidRange 如下
  = OFFSET（A2,0， 0，COUNTA（A：A）-1,1）
  
然后你的公式会读
 
 
 
  = SUM（PaidRange）
  
 
The Total Amount is incrementing(single cell:H2) when value is entered in the same cell(G2)..e.g today if you enter in G2 the value of 30 the total amount in H2 is 30;when you enter tomorrow in G2(same cell) the value of 50, the total amount in H2 should be 80;and when you enter again 80 in the same cell(G2)the next day, the total amount should now be 160...hope you could shed a light on this...Thank you very much...
解决方案
UPDATE:

I found this to be working, no VBA required. It should be quite simple actually. Start with your file like this:
|   | G           | H                 |
|---+-------------+-------------------|
| 1 | Amount paid | Total Amount Paid |
| 2 | 0           | 0                 |
Now go to your settings (for 2010 File -> Options -> Formulas, for 2003 Tools -> Options -> Calculation), tick 'Enable iterative calculation' and set 'Maximum Iterations' to 1. Now in H2 enter your formula as =G2+H2. This is a circular reference (the simplest one there is, cell H2 refers to itself in the calculation) which won't work properly unless you set your calculation settings right. If you set your calculation in the same settings tab to manual then the total will only update when you press F9. If you set it to automatic it will automatically update when you make a change in cell G2.

Original answer

Assuming your data starts in A1 this  formula would sum up all numbers in column A
=SUM(A:A)
If you won't have other stuff in this column (and don't mind the obvious overkill) this will work. If you need to be a little more specific and update a range that dynamically grows/shrinks we need to be a little bit more clever and use INDIRECT or OFFSET as follows
=SUM(INDIRECT("A2:A"&COUNTA(A:A)))
=SUM(OFFSET(A2,0,0,COUNTA(A:A)-1,1))
If you don't know these functions look into them in the Help, they can be very useful.

Another thing to clean things up would be to define a named range with the above e.g. OFFSET formula piece and use that in your formula(s). This makes it easier to repeatedly use the specified range and improves the readability of your formulas. I would define PaidRange as follows
=OFFSET(A2,0,0,COUNTA(A:A)-1,1)
and then your formula would read
=SUM(PaidRange)


                        
这篇关于每当在另一列中输入金额时，如何计算支付的总额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！