谷歌表格_如何计算Google表格中的空白或空单元格

谷歌表格

When you’re analyzing data in a spreadsheet, counting empty or blank cells could help you focus on specific areas. This is why functions like COUNTBLANK, COUNTIF, COUNTIFS, and SUMPRODUCT are so important in Google Sheets.

在分析电子表格中的数据时，对空单元格或空白单元格进行计数可以帮助您专注于特定区域。 这就是为什么COUNTBLANK，COUNTIF，COUNTIFS和SUMPRODUCT之类的功能在Google表格中如此重要的原因。

A word of warning, however. If you have a cell that contains an empty text string (“”) or has a formula that returns a similar result, this cell would be blank, but it wouldn’t technically be empty. If you want to know the number of truly empty cells, you’ll need to use a combination of the SUM, ROWS, COLUMNS, and COUNTIF functions.

但是，请注意。 如果您的单元格包含一个空的文本字符串(“”)或具有一个返回相似结果的公式，则此单元格将为空白，但从技术上来说不会为空。 如果您想知道真正空单元格的数量，则需要使用SUM，ROWS，COLUMNS和COUNTIF函数的组合。

使用COUNTBLANK (Using COUNTBLANK)

You can try the COUNTBLANK function to count the number of blank cells in a Google Sheets spreadsheet. This is the quickest way to find the number of blank, but not empty, cells.

您可以尝试使用COUNTBLANK函数来计数Google表格电子表格中的空白单元格数量。 这是查找空白而不是空单元格数量的最快方法。

Cells that contain numbers or text won’t be counted, including cells with the number zero. As we’ve mentioned, however, if a cell looks empty but contains an empty text string (“”), this will be counted.

包含数字或文本的单元格将不计算在内，包括数字为零的单元格。 但是，正如我们已经提到的，如果一个单元格看起来是空的，但是包含一个空的文本字符串(“”)，则将被计算在内。

To use it, open your Google Sheets spreadsheet. Click on an empty cell and type =COUNTBLANK(range). Replace range with your cell range.

要使用它，请打开您的Google表格电子表格。 单击一个空单元格，然后输入=COUNTBLANK(range) 。 更换range与您的单元格范围。

For instance, if you wanted to count the number of blank cells between columns A and C, you’d type =COUNTBLANK(A:C).

例如，如果您想计算A列和C列之间的空白单元格数，则可以输入=COUNTBLANK(A:C) 。

In the example above, cells from A3 to H24 are used within the range. This range contains four blank cells (B4, C4, D4, and E4), which is the same figure COUNTBLANK reports in cell A1.

在上面的示例中，在此范围内使用了从A3到H24的单元格。 此范围包含四个空白单元格(B4，C4，D4和E4)，这与单元格A1中COUNTBLANK报告的数字相同。

使用COUNTIF和COUNTIFS (Using COUNTIF and COUNTIFS)

While COUNTBLANK returns the number of blank cells, you can also use COUNTIF or COUNTIFS to achieve the same result.

虽然COUNTBLANK返回空白单元格的数量，但您也可以使用COUNTIF或COUNTIFS来获得相同的结果。

COUNTIF counts the number of cells that meet the criteria you define within the formula itself. Because you want to count empty cells, you can use a blank text string as your criteria.

COUNTIF对满足您在公式本身中定义的条件的单元格进行计数。 因为您要计算空单元格，所以可以使用空白文本字符串作为条件。

To use COUNTIF, open your Google Sheets spreadsheet and click on a blank cell. Type =COUNTIF(range,""), replacing range with your chosen cell range.

要使用COUNTIF，请打开您的Google表格电子表格，然后单击一个空白单元格。 输入=COUNTIF(range,"") ，将range替换为您选择的单元格范围。

The example above has three blank cells (B4, C4, and D4) within the range A3 to H24, with the COUNTIF function in cell A1 returning the same number of blank cells.

上面的示例在A3到H24范围内有三个空白单元格(B4，C4和D4)，单元格A1中的COUNTIF函数返回相同数量的空白单元格。

The COUNTIFS function can be used as an alternative to COUNTIF. Use =COUNTIFS(range,""), replacing range with your selected cell range.

COUNTIFS函数可以替代COUNTIF。 使用=COUNTIFS(range,"") ，将range替换为您选择的单元格范围。

In the example above, four blank cells within the A3 to H24 cell range were found.

在上面的示例中，找到了A3至H24单元格范围内的四个空白单元格。

使用SUMPRODUCT (Using SUMPRODUCT)

The SUMPRODUCT function offers a slightly more complex route to counting the number of blank cells. It counts the number of cells matching certain criteria which, in this case, would be an empty text string (“”).

SUMPRODUCT函数提供了一个稍微复杂的路由来计算空白单元格的数量。 它计算符合某些条件的单元格的数量，在这种情况下，该条件将是一个空文本字符串(“”)。

To use SUMPRODUCT, open your Google Sheets spreadsheet and click on an empty cell. Type =SUMPRODUCT(--(range="")), replacing range with your chosen cell range.

要使用SUMPRODUCT，请打开您的Google表格电子表格，然后单击一个空白单元格。 键入=SUMPRODUCT(--(range="")) ，将range替换为您选择的单元格范围。

The example above shows that within the A2 to H24 cell range, two blank cells (B4 and C4) were found.

上面的示例显示，在A2至H24单元格范围内，发现了两个空白单元格(B4和C4)。

计数空单元格 (Counting Empty Cells)

All of the functions listed above count cells that are blank but which aren’t technically empty. If a function returns a null or empty result, or if you have an empty text string (“”) in a cell, then those cells are counted as blank.

上面列出的所有功能都会对空白的单元格进行计数，但从技术上讲并不是空的。 如果函数返回的结果为null或为空，或者单元格中有空文本字符串(“”)，则这些单元格将被计为空白。

A workaround to this problem is to use COUNTIF to count the number of cells with a numerical value, then to use a second COUNTIF formula to count the number of cells containing text or empty text strings.

解决此问题的方法是使用COUNTIF来计算带有数值的单元格的数量，然后使用第二个COUNTIF公式来计算包含文本或空文本字符串的单元格的数量。

You can then add the results from these calculations and subtract them from the number of cells in your data range. You’ll need to know the number of cells in your range first. To find that out, you can use the ROWS and COLUMNS functions.

然后，您可以将这些计算的结果相加，然后从数据范围内的单元格数量中减去它们。 您首先需要知道您范围内的单元格数量。 要找出答案，您可以使用ROWS和COLUMNS函数。

To start, open your Google Sheets spreadsheet, click on an empty cell and type =ROWS(range)*COLUMNS(range), replacing the range value with your cell range.

首先，打开您的Google表格电子表格，单击一个空单元格，然后输入=ROWS(range)*COLUMNS(range) ，将range值替换为您的单元格范围。

In a second empty cell, type =COUNTIF(range,">=0") to count the number of cells with a numerical value. Once again, replace range with the appropriate cell range for your data.

在第二个空单元格中，键入=COUNTIF(range,">=0")来计算带有数值的单元格数。 再一次，将range替换为适合您数据的单元格范围。

To search for blank cells or cells containing text, type =COUNTIF(range,"*") in a third empty cell. Replace range as required.

要搜索空白单元格或包含文本的单元格，请在第三个空单元格中键入=COUNTIF(range,"*") 。 根据需要更换range 。

You can then use SUM to add up your two COUNTIF values, subtracting that figure from the number of cells in your range calculated using the ROWS and COLUMNS functions.

然后，您可以使用SUM将两个COUNTIF值相加，然后从使用ROWS和COLUMNS函数计算的范围内的单元格数量中减去该数字。

In our example, the total number of cells can be found in cell B8, the number of cells with a numerical value in B9, and the number of cells that contain text or an empty text string in B10.

在我们的示例中，可以在单元格B8中找到单元格的总数，在B9中找到具有数值的单元格的数量，在B10中找到包含文本或空文本字符串的单元格的数量。

Replacing these cell values with your own, you could use =B8-SUM(B9:10) to determine the number of truly empty cells in your range.

用自己的值替换这些单元格值，可以使用=B8-SUM(B9:10)确定范围内真正空单元格的数量。

As the example above demonstrates, in a range of 20 cells (A2 to E5), 19 cells were found to have either a number, text, or empty text string. Only one cell, E4, was completely empty.

如上面的示例所示，在20个单元格(A2至E5)的范围内，发现19个单元格具有数字，文本或空文本字符串。 只有一个单元格E4完全空了。

翻译自: https://www.howtogeek.com/447036/how-to-count-blank-or-empty-cells-in-google-sheets/

谷歌表格