首页 文章

Google Spreadsheet总和，总是在上面的单元格上结束

提问于
浏览 次
28

如何创建一个总是在上面的单元格上结束的Google Spreadsheet sum（），即使添加了新单元格也是如此？我在每一列上都有几个这样的计算，所以solutions like this无济于事 .

例：

在B栏上，我有几个动态范围，必须加总 . B1..B9应在B10上求和，B11..B19应在B20上求和 . 我有几十个这样的计算 . 我不时地在最后一个求和行下面添加行，我希望将它们添加到总和中 . 我在第10行之前添加了一个新行（称之为9.1），在第20行之前添加了一个新的原始（我们称之为19.1） . 我希望B10包含B1到B9.1和B20之和以包含B11的总和： B19.1 .

在excel上，我有offset function，就像魅力一样 . 但是如何使用谷歌电子表格呢？我试着使用这样的公式：

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

但是在Google Spreadsheet上，它给出的只是一个#name错误 .

我浪费了几个小时试图找到一个解决方案，也许有人可以打个招呼？请指教

阿姆农

google-sheets

7 回答

  • 1

    您可能正在寻找以下公式：

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

    Google Spreadsheet INDIRECT返回对单元格或 area 的引用，而 - 我记得 - Excel INDIRECT返回始终引用单元格 . 鉴于Google的INDIRECT确实在您尝试在SUM中使用它作为单元格引用时遇到了一些困难，您想要的是在整个范围内提供SUM，以便在例如a1表示法："B1:BX" .

    您可以使用与EXCEL相同的方式获得所需的地址（注意“4”表示行/列相对，默认情况下，Google INDIRECT返回绝对值）：

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

    而不是通过与起始单元连接来使用它为SUM函数准备范围字符串 .

    "B1:"&

    然后用INDIRECT将它包起来，这将返回要汇总的区域 .

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

    每次插入/删除行以使所有行看起来像以下时，而不是耗时的公式更正：

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

    您可以在单独的工作表中保留一列用于保存变量（让我们将其命名为“def”），比方说Z，以定义起点，例如在Z1中写入“B1”，在Z2中写入“B11”等，并使用INDEX将其用作总和中的变量：

    SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - 从B1到计算行的总和，因为在Z1中我们有"B1"（ 1,1 在 INDEX(...,1,1) 中）

    SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - 从B11到计算行的总和，因为在Z2中我们有"B11"（ 2,1 在 INDEX(...,2,1) 中）

    请注意：

    • 单独的名为'def'的工作表 - 您不希望行插入/删除影响该数据，因此将其保持在一边 . 用于添加一些验证列表，以及公式中需要的其他东西 .

    • “Z：Z”表示法 - 整列 . 你说你有很多这样的公式;）

    因此，您可以灵活地为每个公式定义起始单元格，这不受计算表更改的影响 .

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

    Note: you might have to replace commas by semicolons

    回复于
  • 1

    NOTE 测试此答案后，只有在由于循环依赖性错误导致总和位于不同的列中时，它才会起作用 . 否则，解决方案有效 .

    这是一个代数，但我们可以利用Spreadsheets的右下角拖动 .

    =SUM(X:X) - SUM(X2:X)

    其中X是您正在使用的列，X2是您的终点 . 向下拖动公式，Sheets将增加X2，从而更改结束点 .

    *你提到过要进行数十次这样的计算 . 因此，为了满足您的确切需求，我们会减去您的最后总和，以获得我们想要的“中间”范围 .

    例如

    B1..B9应在B10上求和，B11..B19应在B20上求和

    由于前面提到的循环依赖性错误，我无法准确地解决它并将总和放在同一行上，但这可能适用于需要将总和存储在不同列中的其他情况 .

    =SUM(B:B) - SUM(B9:B) //Formula on C10 (Sum of B1..B9)
=SUM(B:B) - SUM(B19:B) - B10 // Formula on C20 (Sum of B11..B19)
    回复于
  • -1

    这是基于@PsychoFish，这是解决方案：

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

    只需将行的“3：”替换为开始总和即可 .

    @PsychoFish是正确的，但无法拖动和复制，因为该列是文字和硬编码的，并且@Druvision处于正确的方向但是错误...基本上最终会出现同样的问题，必须重新输入范围然后一遍又一遍地滑动公式 .

    回复于
  • 2

    一般语法：

    =SUM(INDIRECT(cell_reference_as_string1 &":"& cell_reference_as_string2)

    同例如：

    cell_reference_as_string1 = ADDRESS(ROW(),COLUMN(),4)
cell_reference_as_string2 = ADDRESS(ROW()-1,COLUMN(),4)
    回复于
  • 4

    我喜欢@abernier如何描述一般解决方案 . 到目前为止，仅使用基于字母的A1表示法（A是第一列，1是第一行） . 它让我感到困惑，特别是在考虑另一列的剩余列数时 . 我更喜欢基于数字的R1C1表示法 . 要为INDIRECT使用R1C1表示法，您需要传递FALSE，如下所示：

    =SUM(INDIRECT("R1C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(), FALSE))

    我希望你也觉得有帮助 .

    回复于
  • 48

    你们这比你们更努力 . 我只是在“sum”行上面留下几行空行（您可以将它们格式化为填充颜色或其他东西以防止它们被无意中使用），然后只需在这些特殊行上方添加新行 .

    回复于
  • 0

    @PsychoFish的答案以正确的方式引导我 . 唯一的问题是我必须从每列和每个总和再次重写公式 . 所以这里是改进的公式，它对同一列中的前9个单元格求和，而不对列号或行号进行硬编码：

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

    唯一的问题是，如果有人添加或删除一行，我必须重写公式 . 在这种情况下，我应该相应地改变9到10或8 .

    回复于

相关问题