COUNTA未按我在新版Google表格中的预计工作 [英] COUNTA not working as I expect in new Google Sheets

问题描述

我使用命令 = COUNTA（I31：I）来计算列中的所有非空单元格。列中的单元格包含以下代码行：

  = IF（ISNUMBER（D31），X，） 
  

结果是单元格是一个空字符串（没有添加任何内容的行<$ c $或者包含 X 。

现在的问题是 COUNTA 在此过程之后统计所有单元格，为空或不为空。如果我删除长命令行的单元格不计算在内，并且工作正常。因此，这条长长的命令行向单元格添加了一些东西，所以即使它看起来是空的，它也会显示为 not-empty 。

任何关于什么的线索那是？我的猜测是我应该使用某种 Null 运算符而不是来向单元添加任何内容。

注意：出于某种原因，这似乎适用于以前版本的Google电子表格。

作为潜在的解决方法，我可以通过特别检查<$ c $替换 = COUNTA（I31：I） c> x 与 = COUNTIF（I31：I;X）。但我仍然对COUNTA的问题感到好奇。

如果事实证明我的单元格对于这个命令来说不够空，那我怎么才能让它们完全空？

<不幸的是，这是Google设计的功能。

^{虽然我不确定为什么它的方式不同于 Excel会计算COUNTA 。}

根据 COUNTA 的Google电子表格文档：

3093991？hl = zh_CNrel =nofollow

COUNTA统计数据集中的所有值，包括不止一次出现的值和文本值（包括零长度字符串和空格）。

这意味着使[单元格]完全清空的唯一方法是删除整个内容，公式和所有。但不要害怕......

一些解决方法：

1. Hypothetically ，您应该可以通过 = COUNTIF（A3：A8，<>&） ，但Google电子表格不支持在 不等于 > COUNTIF函数： 1 ，< a href =https://productforums.google.com/forum/#!topic/docs/FIOttjtaPYY =nofollow> 2 ， 3

解决方法是创建一个真或伪数组根据条件并使用SUMPRODUCT来计算这样的真值：

$ $ $ $ $ $ c $ = SUMPR ODUCT（（A3：A8<>））* 1）





2. 可以尝试编写自定义功能并将其添加到云端硬盘。
   
   这实际上很容易。转到工具>脚本编辑器，并添加以下脚本：

   
   $ b

     / ** 
    *像COUNTA一样工作，但不包含空字符串
    * 
    * @param {cellRange} myArray计数时要考虑的值或范围。 
    * @return返回数据集中值的数量。 
    * @customfunction 
    * / 
   函数CountNotBlank（myArray）{
    var output = 0; 
    for（i = 0; i< myArray.length; i ++）{
    if（myArray [i]！=）{
    output + = 1 
   } 
   } 
   返回输出; 
   } 
     

   然后像这样使用：
   
   

     = CountNotBlank（I31：I）
     

I'm using the command =COUNTA(I31:I) to count all non-empty cells in the column. The cells in the column contain this line of code:

=IF(ISNUMBER(D31), "X", "")

The result is that the cells are either an empty string (the line with add nothing "") or contain an X.

The problem now is that COUNTA counts all the cells after this procedure, empty or not. If I delete the long command line the cell is not counted and it works fine. So this long command line is adding something to the cell, so it appears not-empty even though it looks empty.

Any clues to what that is? My guess is that I should use some kind of Null operator instead of the "" to add nothing to the cell.

Note: For some reason, this seems to work in a previous version of Google Spreadsheet I had.

As a potential workaround, I could replace =COUNTA(I31:I) by checking specifically for x with =COUNTIF(I31:I;"X"). But I'm still curious as to the problem with COUNTA.

If it turns out that my cells are not empty 'enough' for this command, how can I then make them completely empty?

解决方案

Unfortunately, this is functions as designed from Google.
^{Although I'm not sure why it's divergent from the way that Excel calculates COUNTA.}

According to the Google Spreadsheet Documentation on COUNTA:

COUNTA counts all values in a dataset, including those which appear more than once and text values (including zero-length strings and whitespace).

Meaning that the only way to "make [the cells] completely empty" is to delete the entire contents, formula and all. But fear not...

Some workarounds:

1. Hypothetically, you should be able to do this with =COUNTIF(A3:A8,"<>"&""), but Google spreadsheets doesn't support the not equal to operator in the COUNTIF function according to these forums: 1, 2, 3

2. A workaround is to create a truthy or falsy array based on the condition and use SUMPRODUCT to count the truthy values like this:

   =SUMPRODUCT((A3:A8<>"")*1)
   

3. Another option you could pursue would be to write a custom function and add it to Drive.
   It's actually pretty easy to do so. Go to Tools > Script Editor and add the following script:

   /**
    * Works like COUNTA except does not include null strings
    *
    * @param {cellRange} myArray The value or range to consider when counting.
    * @return Returns the a count of the number of values in a dataset.
    * @customfunction
    */
   function CountNotBlank(myArray) {
     var output = 0;
     for (i = 0; i < myArray.length; i++) { 
       if (myArray[i] != "") {
         output += 1
       }
     }
     return output;
   }
   

   Then use like this:

   =CountNotBlank(I31:I)
   

这篇关于COUNTA未按我在新版Google表格中的预计工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！