选择列的最后一个值 [英] Selecting the last value of a column

问题描述

我有一个电子表格，其中 G 列中有一些值.其中一些单元格是空的，我需要将该列中的最后一个值提取到另一个单元格中.

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

类似于:

=LAST(G2:G9999)

除了 LAST 不是函数.

推荐答案

所以这个解决方案需要一个字符串作为它的参数.它查找工作表中有多少行.它获取指定列中的所有值.它从结尾到开头循环遍历值，直到找到一个不是空字符串的值.最后它返回值.

So this solution takes a string as its parameter. It finds how many rows are in the sheet. It gets all the values in the column specified. It loops through the values from the end to the beginning until it finds a value that is not an empty string. Finally it retunrs the value.

脚本:

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return values[lastRow - 1];
}

用法:

=lastValue("G")

针对要求自动更新功能的评论:

In response to the comment asking for the function to update automatically:

我能找到的最好方法是将其与上面的代码结合使用:

The best way I could find is to use this with the code above:

function onEdit(event) {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue(lastValue("G"));
}

不再需要在像用法部分所述的单元格中使用该函数.相反，您正在对要更新的单元格和要跟踪的列进行硬编码.可能有一种更雄辩的方法来实现这一点(希望不是硬编码的)，但这是我目前能找到的最好的方法.

It would no longer be required to use the function in a cell like the Usage section states. Instead you are hard coding the cell you would like to update and the column you would like to track. It is possible that there is a more eloquent way to implement this (hopefully one that is not hard coded), but this is the best I could find for now.

请注意，如果您像前面所说的那样在单元格中使用该函数，它将在重新加载时更新.也许有一种方法可以挂钩 onEdit() 并强制更新单元格函数.我只是在文档中找不到它.

Note that if you use the function in cell like stated earlier, it will update upon reload. Maybe there is a way to hook into onEdit() and force in cell functions to update. I just can't find it in the documentation.

这篇关于选择列的最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！