blocks|key|591404|text|实际上，我只是想出了一种使用索引的方法，但我觉得应该有一种更结构化的引用方法。尽管如此，对于其他想要这样做的人，这里是我想出的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|591405|=SUM(INDEX([WTaskUnits],1):[[#This+Row],[WTaskUnits]])|offset|length|style|CODE|591406|我使用INDEX来获取列的第一个单元格(相当于我的行/列示例中的$A$2+)，并使用#This+Row获取当前行中的单元格(相当于我的行/列示例中的A2+)。|591407|entityMap^0|0|0|1I|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Actually, I did just figure out one way of doing it using INDEX, but feel like there should be a more structured reference way. Still, for others looking to do this here is what I came up with: 
<code>=SUM(INDEX([WTaskUnits],1):[[#This Row],[WTaskUnits]])</code>

I use INDEX to get the first cell of the column (equivalent of $A$2 in my row/column example) and use [#This Row] normally to get the cell in current row (equivalent of A2 in my row/column example).

blocks|key|807114|text|不确定你所说的更结构化是什么意思，你这样做是很好的。您也可以在表格标题下面的单元格中简单地开始您的范围：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|807115|OFFSET([[#Headers],[WTaskUnits]],1,0,1,1):|code-block|syntax|javascript|807116|但我不认为这更有条理，只是不同而已。对于列中的部分行范围，没有表引用，所以我的建议是坚持使用已有的表。|807117|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Not sure what you mean by more structured, the way you're doing it is fine. You could also simply start your range in the cell below the table header:

<pre><code>OFFSET([[#Headers],[WTaskUnits]],1,0,1,1):
</code></pre>

But I wouldn't call that more structured, simply different. There's no table reference for a partial range of rows in a columns, so my suggestion would be to stick with what you've got.

blocks|key|591501|text|=SUM(OFFSET([WTaskUnits],0,0,ROW()-ROW([[#Headers],[Running+Total]])))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|591502|该表有两列WTaskUnits和Running+Total。上面的公式对请求的单元格范围求和。|unstyled|591503|OFFSET函数的第一个参数定义sum的起始点。第四个学期|591504|ROW()-ROW([[#Headers],[Running+Total]]|591505|是表示表中当前行编号的一种有用的习惯用法。|591506|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>=SUM(OFFSET([WTaskUnits],0,0,ROW()-ROW([[#Headers],[Running Total]])))
</code></pre>

The table has two columns [WTaskUnits] and [Running Total]. The formula above sums the range of cells as requested.

The first arguments of the OFFSET function define the starting point of the sum. The fourth term,

<pre><code>ROW()-ROW([[#Headers],[Running Total]]
</code></pre>

is a useful idiom for the number of the current row in the table.

blocks|key|807241|text|我知道这是一个古老的主题，但我终于有了一个我想提供的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|807242|=IF(ISNUMBER(OFFSET([@Balance],-1,0)),OFFSET([@Balance],-1,0)%2B[@Amount],[@Amount])|code-block|syntax|javascript|807243|在第一个数据行的实例中，偏移量指向标题，而标题不是数字，因此结果仅为Amount列。|offset|length|style|CODE|807244|其余的行将提供来自OFFSET的前一个Balance加上当前行的Amount。|807245|entityMap^0|0|0|Y|6|0|9|6|J|7|W|6|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@$I|W|J|X|K|L]|$I|Y|J|Z|K|L]|$I|10|J|11|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|P|$]]

I realize this is an old thread, but I finally have a solution I would like to offer.

<pre><code>=IF(ISNUMBER(OFFSET([@Balance],-1,0)),OFFSET([@Balance],-1,0)+[@Amount],[@Amount])
</code></pre>

In the instance of the first data row, the offset points to the header, which is not a number, therefore the result is only the <code>Amount</code> column.

The remaining rows give you the previous <code>Balance</code> from the <code>OFFSET</code> plus the current row <code>Amount</code>.

blocks|key|807282|text|请注意，如果使用=SUBTOTAL(109，...)而不是=SUM(...)那么你的公式将依赖于表上的过滤器。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|807283|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Note, if you use =SUBTOTAL(109,...) instead of =SUM(...) then your formula will respect filters on the table.

blocks|key|591698|text|对于固定的数据表1.转到列中需要小计的最后一个单元格2.对其中包含数据的列进行小计并绝对引用第一行3.将公式向上复制到第一行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|591699|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For a fixed table of data
1. goto the last cell in the column you want the subtotal in
2. subtotal the column that has the data in it and absolute reference the first row 
3.copy the formula up to the first row

blocks|key|807424|text|使用以下公式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|807425|+++=SUM(+INDEX([Values],1)+:+[@Values]+)|code-block|syntax|javascript|807426|我们使用INDEX返回Values列中的第一个单元格，并简单地使用对当前行的结构化引用来返回我们想要求和的区域中的第二个单元格。|807427|Source|offset|length|807428|entityMap|0|LINK|mutability|MUTABLE|url|https://www.myonlinetraininghub.com/excel-table-running-total-formula^0|0|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Use the following formula:

<pre><code> =SUM( INDEX([Values],1) : [@Values] )
</code></pre>

We use INDEX to return the first cell in the Values column, and simply use the Structured Reference to the current row to return the second cell in the range we want to sum.

<a href="https://www.myonlinetraininghub.com/excel-table-running-total-formula" rel="nofollow noreferrer">Source</a>

I'm looking for a way to create a running total (total of the current row and above) using Excel table structured references.

I know how to do it using the old row/column based way: 
<code>=SUM($A$2:$A2)</code>

And I know how to total an entire column using structured references: 
<code>=SUM([WTaskUnits])</code>

And I know how to get the current cell using <code>[#ThisRow]</code>, but I'm not sure how to get the first row of the table to use it in a <code>SUM</code>.

How to create running total using Excel table structured references?

我正在寻找一种方法来创建一个使用Excel表结构化引用的运行合计(当前行及以上行的合计)。我知道如何使用旧的基于行/列的方法：=SUM($A$2:$A2)我还知道如何使用结构化引用对整个列进行汇总：=SUM([WTaskUnits])我知道如何使用[#ThisRow]获取当前单元格，但是我不确定如何让表格的第一行在SU...

问如何使用Excel表结构化引用创建运行合计？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Excel表结构化引用创建运行合计？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Excel表结构化引用创建运行合计？
EN