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))

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