ArrayFormula 破坏了 getLastRow() 功能.可能的解决方法? [英] ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?

问题描述

在我的电子表格中，我有一个运行脚本，它使用 getLastRow() 函数作为其逻辑的重要部分.

In my spreadsheet, I have a running script, which is using the getLastRow() function as an essential part of its logic.

自从我在其中一列中应用数组公式后，getLastRow() 函数就无法正常工作.即使其他列中没有其他值，数组公式似乎一直应用"到工作表的底部，因此 getLastRow() 正在返回最后一行是一个数组公式，而不是实际的非空行.

Ever since I applied the array formula in one of my columns, the getLastRow() function doesn't work properly. It seems that the array formula is "applying" all the way to the bottom of the sheet even when there are no other values in the other columns and thus, getLastRow() is returning the last row where there is an array formula, instead of the actual non-empty row.

编写一个检查哪些单元格为空的慢速函数对我来说不是一个选择，因为脚本会在运行这样的事情时用完(它有数万行).

Writing a slow function which checks which cells are empty is not an option for me, since the script will run out of time with such thing running (it has tens of thousands of rows).

有人对解决方法有任何建议吗?

Does anyone have any suggestions for a workaround?

这是ARRAYFORMULA:

=ArrayFormula(IF(A2:A="",,WEEKNUM(A2:A, 2)))

推荐答案

问题:

• ARRAYFORMULA(IF(A:A="",...))
的传统用法在所有可用行中添加了不受欢迎的空字符串

Issue:

• Undesirable addition of empty strings in all the available rows by traditional usage of ARRAYFORMULA(IF(A:A="",...))

• 正确使用 ARRAYFORMULA 和 INDEX/COUNTA(以确定所需的最后一行)可确保公式仅填充到所需的行而不是伪装

• Using ARRAYFORMULA properly with INDEX/COUNTA(to determine the last row that's needed) ensures formula is only filled upto the needed row instead of a camouflage

INDEX/COUNTA:INDEX 返回一个值以及一个单元格引用.A2:INDEX(A2:A,COUNTA(A2:A)) =>如果 COUNTA(...) 返回 10 =>A2:INDEX(A2:A,10) =>A2:A11 是提供给 weeknum 的最终引用

INDEX/COUNTA: INDEX returns a value as well as a cell reference. A2:INDEX(A2:A,COUNTA(A2:A)) => If COUNTA(...) returns 10 => A2:INDEX(A2:A,10) => A2:A11 is the final reference feeded to weeknum

假设您的数据之间没有空格，

Assuming there are no blanks in between your data,

=ARRAYFORMULA(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)),2))

• 另一种选择是使用ARRAY_CONSTRAIN/COUNTA:

  =ARRAY_CONSTRAIN(ARRAYFORMULA(WEEKNUM(A2:A, 2)),COUNTA(A2:A))
  

• COUNTA 的用法假设中间没有空白单元格.如果有，您可能需要手动添加偏移量.如果有两个空白单元格，则在 COUNTA

• The usage of COUNTA assumes there are no blank cells in between. If there are any, you may need to manually add a offset. If there are two blank cells, add 2 to COUNTA

  A2:INDEX(A2:A,COUNTA(A2:A)+2)
  

除非 Google 进行内置优化，否则 INDEX/COUNTA 优于 ARRAY_CONSTRAIN

Unless Google does inbuilt optimizations, INDEX/COUNTA is preferred over ARRAY_CONSTRAIN

这篇关于ArrayFormula 破坏了 getLastRow() 功能.可能的解决方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！