如何在 Excel 中统计非空单元格：5 种主要方法

我也可以横向按行统计，看看每个人的答题完整度：

=COUNTA(B2:F2)技术支持

对 Sara Ibrahim，这个公式返回 5，因为她把每个字段都填写了。对 Nour Mahmoud，=COUNTA(B6:F6) 只返回1，因为她只提供了邮箱，其他都没填。

何时只用 COUNTA()

当您只需简单计数且数据干净时，使用 COUNTA()。如果范围内每个空单元格都是真正的空（从未编辑、无公式）， COUNTA() 就能完美工作。

麻烦在于，当公式返回 "" （空字符串）时。稍后我会在“Excel 如何看待空白与空单元格”一节详细说明这个边界情况。

使用 COUNTIF() 统计非空单元格

COUNTIF() 提供了另一种统计非空单元格的方法。它不是统计所有存在的内容，而是统计满足某个条件的单元格。这个条件可以是“非空”。

“<>” 条件

公式如下：

=COUNTIF(B2:B13,"<>")技术支持

其中 "<>" 表示“不等于空”，也就是“非空”。对前面的邮箱列，这个结果也是10，与 COUNTA()一致。

为什么不使用 COUNTIF(range,"*")？

我在一些旧教程里见过这种写法，但这是个错误。通配符 "*" 只统计文本。它会完全忽略数字、日期和 TRUE/FALSE 值。如果您的列包含混合数据类型，结果会不正确。

请坚持使用 "<>"。无论数据类型如何，它都适用。

何时 COUNTIF() 优于 COUNTA()

使用 COUNTIF() 的真正优势在于它可以顺畅过渡到 COUNTIFS()。当您需要统计既非空又满足其他条件（如特定部门或日期范围）的单元格时，COUNTIFS() 就派上用场了。如果您预计以后会增加条件，先用 COUNTIF() ，转换会更容易。

对大多数数据， COUNTA() 与 COUNTIF(range,"<>") 的结果相同。两者统计的对象一致，包括错误和公式生成的空字符串。主要差别在于灵活性。 COUNTIF() 是连接到多条件计数 COUNTIFS() 的桥梁。

统计满足条件的非空单元格

这正是 COUNTIF() 大显身手的地方。COUNTIFS() 可以统计既非空又满足其他条件的单元格。

带非空逻辑的 COUNTIFS()

假设我在跟踪员工培训，想知道销售部有多少人提交了分数。公式如下：

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")技术支持

含义是：“统计部门为 ‘Sales’ 且分数不为空的行”。在我的表中，这个结果是3，因为 Mona Sayed 在销售部，但尚未提交分数。

多重条件

如有需要，您最多可堆叠 127 对条件。下面这个公式统计被标记为 "Complete" 且有分数的员工：

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")技术支持

结果为6，因为 Mona Sayed 完成了培训，但从未提交分数。

再举一例：统计工程部中已有完成日期的人数：

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")技术支持

这会返回3，因为 Omar Mostafa 的培训仍在进行中，还没有日期。

模式很简单：对检查非空的那一列使用 "<>" ，其他列填入您的具体筛选条件。

Excel 如何看待“空白”与“空单元格”

多数计数错误就发生在这里。Excel 对多种看起来一样“空”的情况有不同判断。

真正的空单元格

真正的空单元格从未被编辑，或已被完全清除。ISBLANK() 返回 TRUE，COUNTA() 会跳过，COUNTBLANK() 会计入。这是各函数唯一一致认为“空”的类型。

公式生成的空字符串

这一点经常让人栽跟头。当类似 =IF(A1>60,"Pass","") 这样的公式返回 "" 时，单元格看起来是空的，但实际上包含了一个公式结果。不同函数的判断如下：

• COUNTA() → 计入（非空）
• COUNTIF(range,"<>") → 计入（非空）
• COUNTBLANK() → 计入（空）
• ISBLANK() → 返回 FALSE

看到问题了吗？COUNTBLANK() 认为它是空的，但 ISBLANK() 认为不是。即便是资深 Excel 用户也常被这点绊倒。

SUMPRODUCT() + LEN() 的变通法

若只统计真正可见内容（不含公式空白、不含空格）的单元格，请用：

=SUMPRODUCT(--(LEN(B5:B10)>0))技术支持

此公式检查每个单元格的字符数。如果 LEN() 返回 0，则不论其他情况该单元格都视为“空”。-- 会把 TRUE/FALSE 转成 1/0，SUMPRODUCT()` 负责求和。

如果要更严格，也忽略仅包含空格的单元格：

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))技术支持

含有隐藏字符的单元格

有时单元格看似为空，其实含有一个空格（CHAR(32)）或不换行空格（CHAR(160)），多见于网页或 PDF 数据。COUNTA() 会计入，LEN() 返回 1 或以上，而 ISBLANK() 会返回 FALSE。

要在计数时排除这些情况，请用我前面提到的更严格公式：=SUMPRODUCT(--(LEN(TRIM(range))>0))。其中 TRIM() 会移除常规空格。对于不换行空格，请先用 =SUBSTITUTE(A1,CHAR(160),"") 清洗，或使用 CLEAN() 函数。

在筛选数据中统计非空单元格

经常有人被这点打个措手不及：您筛选了数据，写了 COUNTA() 公式，但结果依旧包含被隐藏的行。这是因为 COUNTA() 完全不理会筛选，它统计范围内的所有内容，无论您是否可见。

SUBTOTAL()：识别筛选的替代方案

SUBTOTAL() 可以解决这个问题。它在统计时会考虑筛选。

=SUBTOTAL(3,D2:D11)技术支持

其中 3 表示让 SUBTOTAL() 执行类似 COUNTA() 的计数。不同之处在于，它会跳过被筛选隐藏的行。

函数编号：3 与 103

SUBTOTAL() 有两种模式：

• 函数编号 3：跳过通过自动筛选隐藏的行，但仍统计手动隐藏的行。
• 函数编号 103：跳过所有隐藏行（包括筛选和手动）。

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rows技术支持

多数情况下，3 就够用了。只有在您手动隐藏了行（右键 > 隐藏）且也希望排除时，才使用 103。

AGGREGATE()：应对脏数据的替代

如果筛选后的数据包含错误值，SUBTOTAL() 会报错。AGGREGATE() 处理得更好：

=AGGREGATE(3,7,D2:D11)技术支持

这里 3 是函数编号（COUNTA()），7 表示同时忽略隐藏行和错误。这是处理脏数据最安全的选择，但多数人并不需要。

在 Excel 表格中统计非空单元格

如果您的数据在Excel 表格中（点击插入 > 表格，或按 Ctrl+T），您可以用结构化引用代替单元格地址。这样公式更清晰，表格扩展时也会自动更新。

COUNTA() 的结构化引用

与其写 =COUNTA(C2:C9)，不如改为：

=COUNTA(TaskTracker[Assigned To])技术支持

这会统计我的 TaskTracker 表中 Assigned To 列的非空单元格。该引用会自动跳过表头，并在新增行时自动扩展。

将 COUNTIFS() 与结构化引用结合

COUNTIFS() 与表格配合良好：

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")技术支持

这会统计标记为 "Done" 且具有完成日期的任务。在我的跟踪表中，结果是 4，因为所有已完成的任务都有日期。

为什么表格更适合计数

表格有两点很有用。其一，结构化引用会自动跳过表头，您无需担心从第 2 行开始。其二，当您向表格添加新行时，所有使用结构化引用的公式都会自动包含新数据，无需手动更新范围。

这非常适合会随时间增长的数据，如日志、跟踪器或定期导入的数据。

统计非空单元格的常见问题

计数高于预期

现象：COUNTA() 显示 6，但您只看到 4 个有数据的单元格。

原因：有两个单元格的公式返回了 ""。如前所述，这些看起来为空，但技术上并非空。

解决：用 =SUMPRODUCT(--(LEN(range)>0)) 替代 COUNTA()。它只统计可见内容，忽略公式生成的空白。

隐藏空格干扰计数

现象：单元格看似为空，但 COUNTA() 会计入，ISBLANK() 返回 FALSE。

原因：单元格中有空格，可能来源于从网站或其他文件复制的数据。

解决：用 =LEN(A1) 检查。如果看似空却返回大于 0，说明有隐藏字符。先 清洗数据（=TRIM(A1)），或使用我前面给出的更严格公式：=SUMPRODUCT(--(LEN(TRIM(range))>0))。

不想统计却被计入的错误值

现象：您想统计非空单元格，但像 #DIV/0! 或 #N/A 这类错误也被计入。

原因：COUNTA() 和 COUNTIF(range,"<>") 都将错误视为非空。

解决：如果您的数据也有筛选，请按筛选数据一节所述使用 =AGGREGATE(3,7,range)。参数 7 同时忽略隐藏行和错误。若无筛选，用 =SUMPRODUCT(--(LEN(IFERROR(range,""))>0))。

筛选后计数不更新

现象：您筛选了数据，但 COUNTA() 仍显示未筛选前的计数。

原因：COUNTA() 完全忽略筛选。

解决：使用 =SUBTOTAL(3,range) ，如筛选数据一节所述。它会根据可见内容自动调整。

统计非空单元格的最佳实践

在多次踩坑后，我现在的做法是：

• 对干净、简单的数据集，从 COUNTA() 开始。它快速且直观。

• 若将来可能添加条件，使用 COUNTIF(range,"<>")。它与 COUNTIFS() 的衔接很自然，便于做多条件统计。

• 在信任计数前先测试是否存在公式空白。随机检查几个“空”单元格，使用 =LEN(cell)。如果不为 0，请改用 SUMPRODUCT()+LEN() 的方法。

• 对筛选数据使用 SUBTOTAL(3,range)。它是唯一会考虑筛选的计数函数。

• 先清洗导入数据。对来自网站或其他来源的数据运行 TRIM() 并检查 CHAR(160)。

• 对会增长的数据使用 Excel 表格。结构化引用会在您新增行时自动更新。

• 为复杂公式写注释。如果您使用 SUMPRODUCT(--(LEN(TRIM(range))>0))，请添加说明原因的注释。未来的您会感谢现在的自己。

结语

Excel 提供了多种统计非空单元格的方法，正确选择取决于您的场景。COUNTA() 适用于大多数情况。COUNTIF() 和 COUNTIFS() 可添加条件。SUBTOTAL() 会尊重筛选。而 SUMPRODUCT() 配合 LEN() 则是在公式制造“隐形空白”时的备用方案。

关键不在于死记公式，而在于理解 Excel 对“空”的判断。公式返回 "" 看起来空却不是真的空；空格字符看不见却会被计入。理解了这些差异，您就不会再被错误计数惊到。

如果您想继续提升 Excel 技能，欢迎查看我们的 Data Analysis in Excel 课程。课程涵盖公式、数据透视表和数据清洗，与本文内容相辅相成。