Google电子表格总和始终以上面的单元格结尾 [英] Google Spreadsheet sum which always ends on the cell above

问题描述

如何创建一个Google Spreadsheet sum（），它总是以上面的单元格结尾，即使添加了新的单元格也是如此？我有几个这样的计算在每个单列上做，所以解决方案

示例：

开B列，我有几个动态范围必须加以总结。 B1..B9应在B10上求和，而B11..B19应在B20上求和。我有几十个这样的计算。每隔一段时间，我在最后一个总结行下面添加行，并且我希望它们被添加到总和中。我在第10行之前添加一个新行（称为9.1），并在第20行之前添加一个新的行（我们称之为19.1）。我想让B10包含B1到B9.1和B20的和以包含B11的和： B19.1。

在Excel中，我有偏移量函数，它像魅力一样。但如何使用谷歌电子表格呢？我尝试使用这样的公式：

$ p $ = SUM（B1：INDIRECT（address（row（） - 1，column（） （B11：INDIRECT（address（row（） - 1，column（），false）））＃B20上的公式


但是在Google Spreadsheet中，它只给出了一个#name错误。

我浪费了几小时试图找到一个解决方案，也许有人可以calp？
请告知

解决方案

寻找公式如：
$ b $ p code> = SUM（INDIRECT（B1：& ADDRESS（ROW（） - 1，COLUMN（），4 ））

Google Spreadsheet INDIRECT返回对单元格或区域的引用， Excel INDIRECT返回始终引用单元格。
鉴于谷歌的INDIRECT确实有一些困难时，当你尝试在SUM内部使用它作为单元格引用时，你想要的是给SUM提供整个范围以在例如a1表示法：B1：BX。

您可以像使用EXCEL一样获取您想要的地址（注意4在这里对于行/列相对，默认情况下Google INDIRECT返回绝对值）：

p>

ADDRESS（ROW（） - 1，COLUMN（），4）

，并通过连接到起始单元格来为SUM函数准备范围字符串。

B1：&

并用INDIRECT将其包括起来，这将返回区域总和。

从Druvision回答下面的答案（我现在还没有评论，我不想多取答案）

每行插入/删除的更正使得全部看起来像：

= SUM（INDIRECT（ADDRESS（ROW（） - 9，COLUMN（ ），4）&：& ADDRESS（ROW（） - 1，COLUMN（），4）））

可以在单独的表格中留出一列用于保存变量（让我们将其命名为def），比如说Z来定义起始点eg Z1中的
在Z2中写入B1
写入B11
等
，并通过使用INDEX将其用作变量： $ b $ （INDEX（def！Z：Z，1,1）&：& ADDRESS（ROW（） - 1，COLUMN（），4（b，b

））） - 从B1到计算行之和，因为在Z1中我们有B1（ 1,1 在 INDEX（...，1,1））
$ b

SUM（INDIRECT（INDEX（def！Z：从B11到计算的行的总和，因为在Z2中我们有1和2）和 B11（ 2,1 in INDEX（...，2,1））

请注意：

1. 名为'def'的单独表格 - 您不希望行插入/删除对数据的影响，从而保持一侧。用于添加一些验证列表，您公式中需要的其他内容。

你说你有很多这样的公式;）




因此，你可以保留为每个定义起始单元的灵活性公式，它不受计算表变化的影响。



顺便说一下，编写自定义函数/脚本总结单元格上的所有行不是更容易吗？如果你觉得像javascripting，从我记得，谷歌电子表格现在有很好的脚本编辑器。您可以创建一个称为例如 sumRowsAboveMe（），而不只是在工作表中使用它，例如工作表单元格中的 = sumRowsAboveMe（）。


How to create a Google Spreadsheet sum() which always ends on the cell above, even when new cells are added? I have several such calculations to make on each single column so solutions like this won't help.

Example:

On column B, I have several dynamic ranges which has to be summed. B1..B9 should be summed on B10, and B11..B19 should be summed on B20. I have tens such calculations to make. Every now and then, I add rows below the last summed row , and I want them to be added to the sum. I add a new row (call it 9.1) before row 10, and a new raw (let's call it 19.1) before row 20. I want B10 to contain the sum of B1 through B9.1 and B20 to contain the sum of B11:B19.1.

On excel, I have the offset function which does it like charm. But how to do it with google spreadsheet? I tried to use formulas like this:

=SUM(B1:INDIRECT(address(row()-1,column(),false)))   # Formula on B10
=SUM(B11:INDIRECT(address(row()-1,column(),false)))  # Formula on B20

But on Google Spreadsheet, all it gives is a #name error.

I wasted hours trying to find a solution, maybe someone can calp? Please advise

Amnon

解决方案

You are probably looking for formula like:

=SUM(INDIRECT("B1:"&ADDRESS(ROW()-1,COLUMN(),4)))

Google Spreadsheet INDIRECT returns reference to a cell or area, while - from what I recall - Excel INDIRECT returns always reference to a cell. Given Google's INDIRECT indeed has some hard time when you try to use it inside SUM as cell reference, what you want is to feed SUM with whole range to be summed up in e.g. a1 notation: "B1:BX".

You get the address you want in the same way as in EXCEL (note "4" here for row/column relative, by default Google INDIRECT returns absolute):

ADDRESS(ROW()-1,COLUMN(),4)

and than use it to prepare range string for SUM function by concatenating with starting cell.

"B1:"&

and wrap it up with INDIRECT, which will return area to be sum up.

REFERRING TO BELOW ANSWER from Druvision (I cant comment yet, I didn't want to multiply answers)

Instead of time consuming formulas corrections each time row is inserted/deleted to make all look like:

=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

You can spare one column in separate sheet for holding variables (let's name it "def"), let's say Z, to define starting points e.g. in Z1 write "B1" in Z2 write "B11" etc. and than use it as variable in your sum by using INDEX:

SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sums from B1 to calculated row, since in Z1 we have "B1" ( the 1,1 in INDEX(...,1,1) )

SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sums from B11 to calculated row, since in Z2 we have "B11" ( the 2,1 in INDEX(...,2,1) )

please note:

1. Separate sheet named 'def' - you don't want row insert/delete influence that data, thus keep it on side. Useful for adding some validation lists, other stuff you need in your formulas.

2. "Z:Z" notation - whole column. You said you had a lot of such formulas ;)

Thus you preserve flexibility of defining starting cell for each of your formulas, which is not influenced by calculation sheet changes.

By the way, wouldn't it be easier to write custom function/script summing up all rows above cell? If you feel like javascripting, from what I recall, google spreadsheet has now nice script editor. You can make a function called e.g. sumRowsAboveMe() and than just use it in your sheet like =sumRowsAboveMe() in sheet cell.

这篇关于Google电子表格总和始终以上面的单元格结尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！