选择列的最后一个值
我有一个电子表格，G 列中有一些值。有些单元格之间是空的，我需要将该列的最后一个值获取到另一个单元格中。
类似于：
=LAST(G2:G9999)
除了 LAST 不是一个函数。
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.
Something like:
=LAST(G2:G9999)
except that LAST isn't a function.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（25）
与 caligari 的答案类似的答案，但我们可以通过指定完整的列范围来整理它：
Similar answer to caligari's answer, but we can tidy it up by just specifying the full column range:
所以这个解决方案采用一个字符串作为其参数。它查找工作表中有多少行。它获取指定列中的所有值。它从末尾到开头循环遍历值，直到找到不是空字符串的值。最后它返回值。
脚本：
用法：
编辑：
响应要求功能自动更新的评论：
我能找到的最好方法是使用这个使用上面的代码：
不再需要在单元格中使用该函数，如用法部分所述。相反，您对要更新的单元格和要跟踪的列进行硬编码。可能有一种更雄辩的方法来实现这一点（希望不是硬编码的），但这是我目前能找到的最好的方法。
请注意，如果您像前面所述的那样在单元格中使用该函数，它将在重新加载时更新。也许有一种方法可以挂钩 onEdit() 并强制更新单元格函数。我只是在文档中找不到它。
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.
Script:
Usage:
EDIT:
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:
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.
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.实际上我在这里找到了一个更简单的解决方案：
http ://www.google.com/support/forum/p/Google+Docs/thread?tid=20f1741a2e663bca&hl=en
它看起来像这样：
Actually I found a simpler solution here:
http://www.google.com/support/forum/p/Google+Docs/thread?tid=20f1741a2e663bca&hl=en
It looks like this:
目前尚未实现 LAST() 函数来选择范围内的最后一个单元格。但是，按照您的示例：
我们可以使用函数 INDEX() 和 COUNT() 以这种方式获取最后一个单元格：
有一个 < a href="https://docs.google.com/spreadsheets/d/1_3UZ9SFEipyqe2LGngiXyS59gcK4sAWzFuYi9ZhMmsw/edit#gid=5" rel="noreferrer">实时示例在我找到的电子表格中（并解决了）同样的问题（工作表
Orzamentos，单元格I5）。请注意，即使引用文档中的其他工作表，它也能完美运行。LAST() function is not implemented at the moment in order to select the last cell within a range. However, following your example:
we are able to obtain last cell using the couple of functions INDEX() and COUNT() in this way:
There is a live example at the spreedsheet where I have found (and solved) the same problem (sheet
Orzamentos, cellI5). Note that it works perfectly even refering to other sheets within the document.摘要：
详细信息：
我浏览并尝试了几个答案，以下是我发现的内容：
如果没有空格，最简单的解决方案（请参阅 Dohmoose 的答案）有效：
如果有空格，则会失败。
您只需从
COUNT更改为COUNTA即可处理一个空白（请参阅 user3280071 的回答)：但是，对于某些空白组合，这将失败。 （
1 Blank 1 Blank 1对我来说失败了。）以下代码有效（请参阅 Nader 的回答 和 < a href="https://stackoverflow.com/questions/4169914/selecting-the-last-value-of-a-column/13356890#comment39886037_22798829">杰森的评论）：
但它需要考虑您是否想要对给定范围使用
COLUMNS或ROWS。但是，如果将
COLUMNS替换为COUNT，我似乎得到了LAST的可靠、防空实现：并且由于
COUNTA< /code> 内置了过滤器，我们可以进一步简化使用这有点简单，而且正确。而且您不必担心是否计算行数或列数。与脚本解决方案不同的是，它会随着电子表格的更改而自动更新。
如果你想获取一行中的最后一个值，只需更改数据范围：
Summary:
Details:
I've looked through and tried several answers, and here's what I've found:
The simplest solution (see Dohmoose' answer) works if there are no blanks:
If you have blanks, it fails.
You can handle one blank by just changing from
COUNTtoCOUNTA(See user3280071's answer):However, this will fail for some combinations of blanks. (
1 blank 1 blank 1fails for me.)The following code works (See Nader's answer and jason's comment):
but it requires thinking about whether you want to use
COLUMNSorROWSfor a given range.However, if
COLUMNSis replaced withCOUNTI seem to get a reliable, blank-proof implementation ofLAST:And since
COUNTAhas the filter built in, we can simplify further usingThis is somewhat simple, and correct. And you don't have to worry about whether to count rows or columns. And unlike script solutions, it automatically updates with changes to the spreadsheet.
And if you want to get the last value in a row, just change the data range:
为了从文本值列中返回最后一个值，您需要使用 COUNTA，因此您需要以下公式：
In order to return the last value from a column of text values you need to use COUNTA, so you would need this formula:
试试这个：
=INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))假设您要在其中查找最后一个列值是 B。
是的，它适用于空白。
try this:
=INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))Suppose the column in which you are looking for the last value is B.
And yes, it works with blanks.
这对我有用：
This one works for me:
看起来 Google Apps 脚本现在支持范围作为函数参数。此解决方案接受一个范围：
另请参阅 Google Apps 脚本帮助论坛中的讨论：如何强制公式重新计算？
It looks like Google Apps Script now supports ranges as function parameters. This solution accepts a range:
Also see discussion in Google Apps Script help forum: How do I force formulas to recalculate?
我看了之前的答案，他们似乎太努力了。也许脚本支持只是得到了改进。我认为该函数是这样表达的：
在我的电子表格中，我然后使用：
在函数中，我得到一个值数组，每个引用的单元格都有一个值，这只是从数组末尾向后迭代，直到找到一个非空值或元素耗尽。在将数据传递给函数之前应解释工作表引用。也不够花哨来处理多维度。该问题确实要求单列中的最后一个单元格，因此它似乎合适。如果你的数据用完了，它也可能会死掉。
你的里程可能会有所不同，但这对我有用。
I looked at the previous answers and they seem like they're working too hard. Maybe scripting support has simply improved. I think the function is expressed like this:
In my spreadsheet I then use:
In the function, I get an array of values with one per referenced cell and this just iterates from the end of the array backwards until it finds a non-empty value or runs out of elements. Sheet references should be interpreted before the data is passed to the function. Not fancy enough to handle multi-dimensions, either. The question did ask for the last cell in a single column, so it seems to fit. It will probably die on if you run out of data, too.
Your mileage may vary, but this works for me.
没有硬编码。
no hard coding.
在有空格的列中，您可以使用以下命令获取最后一个值
In a column with blanks, you can get the last value with
这将获取最后一个值并处理空值：
This gets the last value and handles empty values:
答案
在 LibreOffice 中无法正常工作。然而，只要稍加改动，它就可以完美运行。
仅当真实范围小于
(G2:G10000)时，它才始终有效The answer
doesn't work correctly in LibreOffice. However, with a small change, it works perfectly.
It always works only if the true range is smaller than
(G2:G10000)用严格偏离主题的答案来回答原始问题是否可以接受:)
您可以在电子表格中编写公式来执行此操作。也许很丑？但对电子表格的正常操作有效。
这是对脚本区域中一系列问题的思考，这些问题可以通过数组公式可靠地传递，数组公式的优点是在 excel 和 openoffice 中经常以类似的方式工作。
Is it acceptable to answer the original question with a strictly off topic answer:)
You can write a formula in the spreadsheet to do this. Ugly perhaps? but effective in the normal operating of a spreadsheet.
This is offered as a thought for a range of questions in the script area that could be delivered reliably with array formulas which have the advantage of often working in similar fashion in excel and openoffice.
我正在使用 @tinfini 给出的代码，并认为人们可能会从我认为稍微更优雅的解决方案中受益（注意，我不认为脚本在他创建原始答案时的工作方式完全相同）
...用法它看起来像这样：
I was playing with the code given by @tinfini, and thought people might benefit from what I think is a slightly more elegant solution (note I don't think scripts worked quite the same way when he created the original answer)...
In usage it would look like this:
关于@Jon_Schneider的评论，如果列有空白单元格，只需使用COUNTA()
Regarding @Jon_Schneider's comment, if the column has blank cells just use COUNTA()
我发现另一种方法可能会帮助您
=INDEX( SORT( A5:D ; 1 ; FALSE) ; 1 )- 将返回最后一行来自 anab 的更多信息：
https://groups.google.com/forum /?fromgroups=#!topic/操作方法文档/if0_fGVINmI
I found another way may be it will help you
=INDEX( SORT( A5:D ; 1 ; FALSE) ; 1 )-will return last rowMore info from anab here:
https://groups.google.com/forum/?fromgroups=#!topic/How-to-Documents/if0_fGVINmI
发现了一个细微的变化，可以消除桌子底部的空白。
=索引(G2:G,COUNTIF(G2:G,"<>"))
Found a slight variation that worked to eliminate blanks from the bottom of the table.
=index(G2:G,COUNTIF(G2:G,"<>"))
我很惊讶以前没有人给出过这个答案。但这应该是最短的，甚至可以在 Excel 中使用：
G2:G<>""创建一个 1/true(1) 和 1/false(0) 的数组。由于LOOKUP采用自上而下的方法来查找2，并且由于它永远找不到2，所以它会到达最后一个非空白行并给出该行的位置。正如其他人可能提到的，另一种方法是：
查找非空白行的
MAXimumROW并将其输入到INDEX在零空白中断数组中，使用
INDIRECTRC表示法和COUNTBLANK是另一种选择。如果 V4:V6 已被条目占用，则V18:
将给出 V6 的位置。
I'm surprised no one had ever given this answer before. But this should be the shortest and it even works in excel :
G2:G<>""creates a array of 1/true(1) and 1/false(0). SinceLOOKUPdoes a top down approach to find2and Since it'll never find 2,it comes up to the last non blank row and gives the position of that.The other way to do this, as others might've mentioned, is:
Finding the
MAXimumROWof the non blank row and feeding it toINDEXIn a zero blank interruption array, Using
INDIRECTRCnotation withCOUNTBLANKis another option. If V4:V6 is occupied with entries, then,V18:
will give the position of V6.
要从列中获取最后一个值，您还可以将 MAX 函数与 IF 函数结合使用
to get the last value from a column you can also use
MAXfunction withIFfunction对于特定列，我已经经历了太多最后一行的实现。许多解决方案都有效，但对于大型或多个数据集来说速度很慢。我的一个用例要求我检查多个电子表格中特定列中的最后一行。我发现将整个列作为一个范围然后迭代它太慢了，并且将其中一些添加在一起会使脚本变得缓慢。
我的“黑客”是这个公式：
示例：将其添加到单元格 A1，以查找 A 列中的最后一行。可以添加到任何位置，只需确保管理末尾的“-1”，具体取决于哪个放置公式的行。您还可以将其放置为另一列，而不是您要计数的列，并且不需要管理 -1。您还可以从起始行开始计数，例如“C16:C” - 将从 C16 开始计算值
这个公式可靠地给出了最后一行，包括数据集中间的空白
要在我的 GS 代码中使用这个值，我只需从 A1 中读取单元格值。据我所知，Google 很清楚读/写等电子表格功能很繁重（耗时），但根据我的经验（对于大型数据集），这比列计数最后一行方法快得多
为了提高效率，我我一次获取 col 中的最后一行，然后将其保存为全局变量并在我的代码中递增以跟踪我应该更新哪些行。每次循环需要更新时都读取单元格效率太低。读取一次，迭代该值，A1 单元格公式（上面）将“存储”更新后的值，供下次函数运行时使用
请告诉我这是否对您有帮助！如果我遇到任何问题，我会对此答案发表评论。
I have gone through way too many of these implementations of last-row for a specific column. Many solutions work but are slow for large or multiple datasets. One of my use cases requires me to check the last row in specific columns across multiple spreadsheets. What I have found is that taking the whole column as a range and then iterating through it is too slow, and adding a few of these together makes the script sluggish.
My "hack" has been this formula:
Example: Add this to Cell A1, to find the last row in column A. Can be added anywhere, just make sure to manage the "-1" at the end depending on which row the formula is placed. You can also place this is another col, rather than the one you're trying to count, and you don't need to manage the -1. You could also count FROM a starting Row, like "C16:C" - will count values C16 onwards
This formula is reliably giving me the last row, including blanks in the middle of the dataset
To use this value in my GS code, I am simply reading the cell value from A1. I understand that Google is clear that spreadsheet functions like read/write are heavy (time-consuming), but this is much faster than column count last-row methods in my experience (for large datasets)
To make this efficient, I am getting the last row in a col once, then saving it as a global variable and incrementing in my code to track which rows I should be updating. Reading the cell every-time your loop needs to make an update will be too inefficient. Read once, iterate the value, and the A1 cell formula (above) is "storing" the updated value for the next time your function runs
Please let me know if this was helpful to you! If I encounter any issues I will comment on this answer.
原始答案这里
现在可以使用最近发布
TOCOL和CHOOSEROWS函数。第二个参数设置为
1的TOCOL函数会删除A2:A范围内的空白值，并且第二个参数设置为-1的CHOOSEROWS函数返回底部的第一个值（即最后一个值）。相同的公式也可用于返回行中的最后一个非空值：
注意：为了使此公式发挥作用，范围内的空值必须是实际的空值，而不是空字符串
""。如果范围包含空字符串，请改用以下公式：Original answer here
This problem can now be easily solved using the recently released
TOCOLandCHOOSEROWSfunctions.The
TOCOLfunction with second argument set to1removes the blank values in the rangeA2:Aand theCHOOSEROWSfunction with second argument set to-1returns the first value from the bottom (i.e. the last value).The same formula can also be used to return the last non-empty value from a row:
Note: In order for this formula to work, the empty values in the range must be actual empty values, not empty strings
"". If the range contains empty strings, use the following formula instead:=QUERY({G2:G9999,ARRAYFORMULA(ROW(G2:G9999))},"Select Col1 where Col1 is not null Order By Col2 desc limit 1",0)在查询中，
Col1指的是列G，Col2指的是虚拟列，由ARRAYFORMULA(ROW(G2:G9999) 返回的行号填充））。我没有评估其他答案，所以我不能说这是否是最好的方法，但它对我有用。
奖励：返回第一个非空单元格：
QUERY({G2:G9999},"选择 Col1，其中 Col1 不为空限制 1",0)参考：查询，ARRAYFORMULA，行。
=QUERY({G2:G9999,ARRAYFORMULA(ROW(G2:G9999))},"Select Col1 where Col1 is not null Order By Col2 desc limit 1",0)In the query,
Col1refers to columnG, andCol2refers to a virtual column, populated with the row numbers returned byARRAYFORMULA(ROW(G2:G9999)).I haven't evaluated the other answers, so I can't say if this is the best way, but it worked for me.
Bonus: to return the first non-empty cell:
QUERY({G2:G9999},"Select Col1 where Col1 is not null limit 1",0)Refs: QUERY, ARRAYFORMULA, ROW.