如何从最后一个非空单元格减去范围直到第一个非空单元格？

Question

我是google sheets的初学者，但我无法理解这个公式。 我有一组单元格，我想要减去最后一个非空单元格和第一个单元格的值（Z-A），以下是图片：

由于列C、D、E等中的值会更新，我希望能够获取最后一个非空单元格 (从右往左)，并通过向后移动 (向左) 来减去值。 就像这样:

sub = 10(Column G)-0(Column F)-10(Column E)-0(Column D)-10(Column C)

我们能否设计一个公式，它可以获取最后一个非空单元格，并减去第一个值之前的所有值？这里是示例表格的链接。谢谢。

- EagleEye

---

你介意我使用Google Apps Script提供一个自定义函数来替代表格公式来解决这个场景吗？ - PatrickdC

请分享您的表格副本/样本，并提供所需输出的示例。 - player0

@player0 在问题描述的末尾添加了示例表格。 - EagleEye

如果你在问题中添加一个表格，那么你的问题就可以大大地改善。表格是展示数据结构的更好替代品，而不是电子表格。如果你分享电子表格，请确保还添加了表格的图像，以避免你的问题被关闭，因为这里的问题必须是自我包含的。当你分享谷歌文件时，你的电子邮件地址也可以被公开访问。 - TheMaster

Answer 1

尝试:

=LOOKUP(1, INDEX(1/(C2:F2<>"")), C2:F2)-(SUM(C2:F2)-
 LOOKUP(1, INDEX(1/(C2:F2<>"")), C2:F2))

- player0

---

谢谢，它适用于所有条件，只有一个小请求，我们能否使范围通用，而不仅限于从C2:F2开始，我们可以使用'C2:2'吗？这样它就可以适用于整行。 - EagleEye

@Sohaib435 是的，你可以使用=LOOKUP(1, INDEX(1/(C2:2<>"")), C2:2)-(SUM(C2:2)-LOOKUP(1, INDEX(1/(C2:2<>"")), C2:2))，但是公式需要在A或B列中。 - player0

我猜等于 =2*LOOKUP(1, INDEX(1/(C3:F3<>"")), C3:F3)-SUM(C3:F3)，但这是一个很好的答案。 - Tom Sharpe

Answer 2

建议：使用自定义函数

您可以使用以下脚本作为自定义函数，以获取最后一个单元格的值与其他单元格的总和之间的差异：

function SUBTRACTFROMLASTNUMBER(data) { //you can rename the custom function name
  var sum = 0;
  var data2 = data[0].filter(x => {
    return (x != "") ? x : null;
  }); //filtered data
  var lastNumber = data2.pop(); //last number
  data2.map(x => sum += x); //sums the remaining values
  return (lastNumber - sum); //returns the output
}

这个自定义函数从表格中提取选定的数据，然后使用pop()分离最后一个单元格的值，并使用filter()和map()过滤和求和其余的数据，然后从最后一个单元格的值中减去总和。

用法

您可以将此函数用作： =SUBTRACTFROMLASTNUMBER(<range>)

参考资料：

• 如何在JavaScript中操作数组

- PatrickdC

---

如有任何疑问，请随时提出。 - PatrickdC

谢谢，它几乎可以工作，但我认为有一个限制，我在这个公式=SUBTRACTFROMLASTNUMBER(C5:5)中给出了整行，但是如果列C=10，列D=0，列E=10，则从“最后一列（E）”开始输出应该是“（10-0-10=0）”，但它给出的输出是“-20”。 - EagleEye

我的错，我刚意识到在使用 filter 之前我先使用了 pop，这将导致如果最后一个单元格为空，则会得到一个空值。让我稍后修复它。感谢您指出这个问题。 - PatrickdC

更新了自定义函数。 - PatrickdC

列C = 10，列D = 0，结果应该是0-10=-10，但输出却为10。 - EagleEye

再次道歉，这一次我添加了一个条件语句，其中零值不会被过滤掉，并且可以被视为最后一个数。感谢您的耐心等待。如果还有其他问题，请随时提出。 - PatrickdC