获取 Google 表格列中的最后一个非空单元格
我使用以下函数
=DAYS360(A2, A35)
来计算列中两个日期之间的差异。然而，该列不断扩大，我目前必须在更新电子表格时手动更改“A35”。
有没有办法（在 Google Sheets 中）找到此列中的最后一个非空单元格，然后在上述函数中动态设置该参数？
I use the following function
=DAYS360(A2, A35)
to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet.
Is there a way (in Google Sheets) to find the last non-empty cell in this column and then dynamically set that parameter in the above function?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（24）
可能有一种更雄辩的方法，但这是我想出的方法：
查找列中最后一个填充单元格的函数是：
因此，如果将其与当前函数结合起来，它将如下所示：
There may be a more eloquent way, but this is the way I came up with:
The function to find the last populated cell in a column is:
So if you combine it with your current function it would look like this:
要查找最后一个非空单元格，您可以使用
INDEX和MATCH函数如下：我认为这有点更快更容易一点。
To find the last non-empty cell you can use
INDEXandMATCHfunctions like this:I think this is a little bit faster and easier.
虽然这个问题已经有了答案，但有一个雄辩的方法可以做到这一点。
例如：
如果您的数据位于
A1:A100中，并且您希望能够向 A 列添加更多数据，则可以将其指定为A1:A105甚至 < code>A1:A1234 之后，您可以使用此范围：因此，为了获取某个范围内的最后一个非空值，我们将使用 2 个函数：
答案是
=INDEX(B3:B,COUNTA(B3:B))。解释如下：
COUNTA(range)：返回某个范围内的值的数量，我们可以使用它来获取行数。INDEX(range, row, col)：返回由row和column偏移量指定的单元格内容。如果省略列，则返回整行。示例：
对于上图，我们的范围为
B3:B。因此，我们首先通过COUNTA(B3:B)计算B3:B范围内有多少个值。在左侧，它将生成8，因为有 8 个值，而在右侧它将生成9。我们还知道最后一个值位于B3:B范围的第一列，因此INDEX的col参数必须为 1，并且row参数应为COUNTA(B3:B)。PS：请投票支持@bloodymurderlive的答案，因为他首先写了它，我只是解释一下这里。
Although the question is already answered, there is an eloquent way to do it.
For example:
If your data is in
A1:A100and you want to be able to add some more data to column A, say it can beA1:A105or evenA1:A1234later, you can use this range:So to get last non-empty value in a range, we will use 2 functions:
The answer is
=INDEX(B3:B,COUNTA(B3:B)).Here is the explanation:
COUNTA(range): Returns number of values in a range, we can use this to get the count of rows.INDEX(range, row, col): Returns the content of a cell, specified byrowandcolumnoffset. If thecolumnis omitted then the whole row is returned.Examples:
For the picture above, our range will be
B3:B. So we will count how many values are there in rangeB3:BbyCOUNTA(B3:B)first. In the left side, it will produce8since there are 8 values while it will produce9in the right side. We also know that the last value is in the 1st column of the rangeB3:Bso thecolparameter ofINDEXmust be 1 and therowparameter should beCOUNTA(B3:B).PS: please upvote @bloodymurderlive's answer since he wrote it first, I'm just explaining it here.
如果 A2:A 包含连续的日期，则 INDEX(A2:A,COUNT(A2:A)) 将返回最后一个日期。最终公式为
If A2:A contains dates contiguously then INDEX(A2:A,COUNT(A2:A)) will return the last date. The final formula is
我最喜欢的是：
所以，为了OP的需要：
My favorite is:
So, for the OP's need:
如果该列仅通过连续添加的日期进行扩展
就像我的例子一样 - 我只使用 MAX 函数来获取最后一个日期。
最终公式为：
If the column expanded only by contiguously added dates
as in my case - I used just MAX function to get last date.
The final formula will be:
这对我有用。获取 Google 表中 A 列的最后一个值：（
它还会跳过中间的空白行（如果有））
This works for me. Get last value of the column A in Google sheet:
(It also skips blank rows in between if any)
这是另一个：
最终的方程是这样的：
这里的其他方程都有效，但我喜欢这个方程，因为它使获取行号变得容易，我发现我需要更频繁地这样做。行号就像这样：
我最初试图找到这个来解决电子表格问题，但找不到任何有用的东西来给出最后一个条目的行号，所以希望这对某人有帮助。
此外，这还有一个额外的优点，即它适用于任何顺序的任何类型的数据，并且您可以在包含内容的行之间有空白行，并且它不会计算具有计算结果为“”的公式的单元格。它还可以处理重复值。总而言之，它与此处使用 max((G:G<>"")*row(G:G)) 的方程非常相似，但如果您是这样的话，则可以更轻松地提取行号后。
或者，如果您想将脚本放在工作表上，如果您打算经常这样做，那么您可以自己轻松完成此操作。这是该脚本：
则可以在此处键入以下内容
如果您希望当前正在编辑的同一工作表上的最后一行是最后一行，或者如果您希望该工作表中特定列的最后一行或特定的最后一行， ：从另一个工作表的列，您可以执行以下操作：
对于一般特定工作表的最后一行：
然后要获取实际数据，您可以使用间接：
或者您可以在最后两个返回行（最后一个）修改上面的脚本两个，因为您必须同时放置工作表和列从实际列获取实际值），并将变量替换为以下内容：
该脚本的一个好处是
您可以选择是否要包含计算结果为“”的方程。如果没有添加参数，则计算结果为“”的方程将被计数，但如果您指定工作表和列，它们现在将被计数。此外，如果您愿意使用脚本的变体，那么还有很大的灵活性。
可能有点矫枉过正，但一切皆有可能。
Here's another one:
With the final equation being this:
The other equations on here work, but I like this one because it makes getting the row number easy, which I find I need to do more often. Just the row number would be like this:
I originally tried to find just this to solve a spreadsheet issue, but couldn't find anything useful that just gave the row number of the last entry, so hopefully this is helpful for someone.
Also, this has the added advantage that it works for any type of data in any order, and you can have blank rows in between rows with content, and it doesn't count cells with formulas that evaluate to "". It can also handle repeated values. All in all it's very similar to the equation that uses max((G:G<>"")*row(G:G)) on here, but makes pulling out the row number a little easier if that's what you're after.
Alternatively, if you want to put a script on your sheet you can make it easy on yourself if you plan on doing this a lot. Here's that scirpt:
Here you can just type in the following if you want the last row on the same of the sheet that you're currently editing:
or if you want the last row of a particular column from that sheet, or of a particular column from another sheet you can do the following:
And for the last row of a particular sheet in general:
Then to get the actual data you can either use indirect:
or you can modify the above script at the last two return lines (the last two since you would have to put both the sheet and the column to get the actual value from an actual column), and replace the variable with the following:
and
One benefit of this script is that you can choose if you want to include equations that evaluate to "". If no arguments are added equations evaluating to "" will be counted, but if you specify a sheet and column they will now be counted. Also, there's a lot of flexibility if you're willing to use variations of the script.
Probably overkill, but all possible.
现在可以使用最近发布的解决方案轻松解决此问题
TOCOL和CHOOSEROWS函数。第二个参数设置为
1的TOCOL函数会删除A2:A范围内的空白值和 <代码>CHOOSEROWS函数第二个参数设置为-1返回底部的第一个值（即最后一个值）。相同的公式也可用于返回行中的最后一个非空值：
注意：为了使此公式发挥作用，范围内的空值必须是实际的空值，而不是空字符串<代码>“”。如果范围包含空字符串，请改用以下公式：
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:这似乎是我发现的检索不断扩展的列中最后一个值的最简单的解决方案：
This seems like the simplest solution that I've found to retrieve the last value in an ever-expanding column:
为了严格查找列中的最后一个非空单元格，这应该有效......
For strictly finding the last non-empty cell in a column, this should work...
这个获取最后值的公式怎么样：
这将是您原始任务的最终公式：
假设您的初始日期是 G10。
What about this formula for getting the last value:
And this would be a final formula for your original task:
Suppose that your initial date is in G10.
我走了一条不同的路。因为我知道我将逐一向行/列中添加一些内容，所以我通过首先计算包含数据的字段来找出最后一行。我将用一列来演示这一点：
所以，假设返回 21。A5 向下 4 行，所以我需要获取从第 4 行向下的第 21 个位置。我可以使用间接来完成此操作，如下所示：
它查找包含数据的行数，并返回我用作索引修饰符的数字。
I went a different route. Since I know I'll be adding something into a row/column one by one, I find out the last row by first counting the fields that have data. I'll demonstrate this with a column:
So, let's say that returned 21. A5 is 4 rows down, so I need to get the 21st position from the 4th row down. I can do this using inderect, like so:
It's finding the amount of rows with data, and returning me a number I'm using as an index modifier.
对于行：
对于列：
for a row:
for a column:
通过引入 LAMBDA 和 REDUCE 函数，我们现在可以通过单元格单次计算行号（上面的几个解决方案对范围进行两次过滤。），而无需依赖魔术文本或数值。
它可以很好地打包到命名函数中以供使用，例如
它适用于散布空白的列、多列范围（因为 REDUCE 在行优先的范围内迭代）和部分列（如 A20:A），仍然返回实际行号（不是范围内的偏移量）。
然后可以将其与 Index 结合起来返回值
（但事实上，我怀疑 OP 日期值是单调的（即使中间有空格），并且他可以逃脱
这个解决方案在上面被确定为依赖于日期是“连续的” - 这是否意味着“没有空白”或“没有缺失的日期”我不确定 - 但任何一个规定都是不必要的。）
With the introduction of LAMBDA and REDUCE functions we can now compute the row number in a single pass through the cells (Several of the solutions above filter the range twice.) and without relying on magic text or numeric values.
which can be nicely packaged into a Named Function for usage like
It works on columns with interspersed blanks, and multi-column ranges (because REDUCE iterates over the range in row-first), and partial columns (like A20:A), still returning the actual row number (not the offset within the range).
This can then be combined with Index to return the value
(In truth, though, I suspect that the OPs date values are monotonic (even if with blanks in between), and that he could get away with
This solution is identified above as relying on the dates being "contiguous" - whether that means "no blanks" or "no missing dates" I'm not certain - but either stipulation is not necessary.)
为了查找最后一个非空行号（行号之间允许有空格），我在下面使用了搜索列
A。To find last nonempty row number (allowing blanks between them) I used below to search column
A.这将给出最后一个单元格的内容：
这将给出最后一个单元格的地址：
这将给出最后一个单元格的行：
也许您更喜欢脚本。该脚本比其他人上面发布的庞大脚本要短得多：
转到脚本编辑器并保存此脚本：
完成后，您只需在单元格中输入以下内容：
This will give the contents of the last cell:
This will give the address of the last cell:
This will give the row of the last cell:
Maybe you'd prefer a script. This script is way shorter than the huge one posted above by someone else:
Go to script editor and save this script:
One this is done you just need to enter this in a cell:
要选择任意非空值列中的最后一个，忽略标题单元格 (A1)：
To pick the last in a column of arbitrary, non-empty values ignoring the header cell (A1):
获取最后一个非空行号可以通过以下方式完成：
替代方案：
getting the last non-empty row number can be done with:
alternatives:
计算 A 列中的最新日期与单元格 A2 中的日期之间的差异。
Calculate the difference between latest date in column A with the date in cell A2.
业余爱好者的做法是“=CONCATENATE("A",COUNTUNIQUE(A1:A9999))”，其中 A1 是列中的第一个单元格，而 A9999 比我预期的任何条目都位于该列的更下方。生成的 A# 可以根据需要与 INDIRECT 函数一起使用。
The way an amateur does it is "=CONCATENATE("A",COUNTUNIQUE(A1:A9999))", where A1 is the first cell in the column, and A9999 is farther down that column than I ever expect to have any entries. This resultant A# can be used with the INDIRECT function as needed.
本·柯林斯 (Ben Collins) 是 Google 表格专家，他的网站上有许多免费的提示，还提供课程。他有一篇关于动态范围名称的免费文章，我已将其用作我的许多项目的基础。
https://www.benlcollins.com/formula-examples/dynamic-named -ranges/
免责声明，我通过引用本的网站没有任何好处。
这是我使用动态范围的一个项目的屏幕截图：
单元格 D3 具有如上所示的公式，但这是一个数组公式：
单元格 D4 具有以下公式：
Ben Collins is a Google sheets guru, he has many tips on his site for free and also offers courses. He has a free article on dynamic range names and I have used this as the basis for many of my projects.
https://www.benlcollins.com/formula-examples/dynamic-named-ranges/
Disclaimer, I have nothing to gain by referring Ben's site.
Here is a screenshot of one of my projects using dynamic ranges:
Cell D3 has this formula which was shown above except this is as an array formula:
Cell D4 has this formula:
这可能有效：
=DAYS360(A2,INDEX(A2:A,COUNTA(A2:A)))This may work:
=DAYS360(A2,INDEX(A2:A,COUNTA(A2:A)))这对我有用：
=ArrayFormula(LOOKUP(2,1/(CELL_RANGE<>""),CELL_RANGE))只需将单元格范围放入
CELL_RANGE中即可。除了适用于单列之外，还适用于单行。
This worked for me:
=ArrayFormula(LOOKUP(2,1/(CELL_RANGE<>""),CELL_RANGE))Just put the range of your cells in
CELL_RANGE.In addition to this working on a single column, also works on a single row.