COUNTA是否在计算新的Google电子表格中的空白(空)单元格? [英] Is COUNTA counting blank (empty) cells in new Google spreadsheets?

问题描述

在新的Google电子表格中，COUNTA在引用作为arrayformula一部分(在arrayformula列内)的空单元格时对空白单元格进行计数.对于旧的Google电子表格，情况并非如此，COUNTA给出了预期的结果(0). 其他公式是否同样受到影响? 有什么解决方法吗?

In the new google spreadsheets, COUNTA is counting blank cells when referencing empty cells that are part of an arrayformula (within an arrayformula column). This is not the case with the old google spreadsheets, where COUNTA gives the expected result (0). Are other formulas equally affected? Any workaround?

推荐答案

是的，在COUNTA处理零长度文本字符串的方式上发生了变化.

在新的Google电子表格中，COUNTA在计算空白单元格时 引用作为arrayformula一部分的空单元格(在 arrayformula列).旧版Google并非如此 电子表格，其中COUNTA给出了预期的结果(0).

In the new google spreadsheets, COUNTA is counting blank cells when referencing empty cells that are part of an arrayformula (within an arrayformula column). This is not the case with the old google spreadsheets, where COUNTA gives the expected result (0).

假设您引用的空单元格在数组公式中填充了".在新版本的表格中，这些单元格已计数，您正确的认为在旧版本中不包含这些单元格.就其价值而言，新行为与MS Excel中发生的行为一致.

Presumably the empty cells you refer to are being populated with a "" in your array formula. In the new version of Sheets, these cells are counted, and you are correct that in the old version they were not. For what it's worth, the new behaviour is consistent with what occurs in MS Excel.

其他公式是否同样受到影响?

Are other formulas equally affected?

是的，SORT函数(并使用内置排序工具执行原位排序)现在将在数字和日期之后，在其他文本字符串之前，而在零长度之前对零长度文本字符串进行排序文本字符串被推到某种类型的底部(但在真正的空白单元格之前).同样，这种新的排序行为与MS Excel一致.

Yes, the SORT function (and performing an in-situ sort with the built-in sorting tools) will now sort a zero-length text string after numbers and dates, and before other text strings, whereas before the zero-length text strings were pushed to the bottom of a sort (but before truly blank cells). Again, this new sorting behaviour is consistent with MS Excel.

有什么解决方法吗?

Any workaround?

我认为最好的解决方法是在IF公式中删除"的用法.在新版的Sheets中，如果您完全省略了IF函数的第二个或第三个参数，并且该函数求值到该参数，则将返回一个真正的空白单元格(无论如何，在编写时).因此，不要使用类似的东西:

I think the best workaround would be to drop the usage of "" in IF formulae. In the new version of Sheets, if you omit the second or third argument of an IF function altogether, and the function evaluates to that argument, then a truly blank cell is returned (at the time of writing this, anyway). So instead of using something like:

=ArrayFormula(IF(A2:A>25, "something",""))

使用:

=ArrayFormula(IF(A2:A>25, "something",))

COUNTA不会计算空白单元格.

and the COUNTA won't count the blank cells.

这篇关于COUNTA是否在计算新的Google电子表格中的空白(空)单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！