统计Excel中不等于多个值的单元格数量
Author: Xiaoyang Last Modified: 2025-07-31
在Excel中,您可以轻松使用COUNTIF函数获取不等于特定值的单元格数量,但您是否尝试过统计不等于多个值的单元格?例如,我想获取A列中所有产品的总数,但排除C4:C6中的特定项目,如下截图所示。本文将介绍一些解决此问题的公式。
使用公式1统计不等于多个值的单元格数量
要统计不等于多个指定值的单元格,可以结合使用MATCH、ISNA和SUMPRODUCT函数,通用语法为:
= SUMPRODUCT ( -- ( ISNA ( MATCH ( 范围, 排除值, 0)))
- 范围: 您要统计的单元格列表;
- 排除值: 要从范围列表中忽略的值。
1. 在您希望输出结果的空白单元格中输入或复制以下公式:
=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))
2. 然后,按Enter键以获得正确结果,见下图:
公式解释:
- MATCH(A2:A13,C2:C4,0): 此MATCH函数返回每个匹配值的位置(数字),未找到时返回#N/A错误值。这意味着,当A2:A13中的值等于C2:C4中的值时,它将返回一个数字,否则返回#NA错误。这里的结果是:{1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}。
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): 此ISNA函数将错误值强制转换为TRUE,将数字转换为FALSE,因此您将得到:{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}。
- --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}): 双负号运算符将所有TRUE转换为1,将FALSE转换为0,如下:SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1;})。
- 然后使用SUMPRODUCT函数对数组中的数字求和,并得到结果:7。
使用公式2统计不等于多个值的单元格数量
要在Excel中完成此任务,还可以应用COUNTA、SUMPRODUCT和COUNTIF函数的组合,通用语法为:
=COUNTA(范围)-SUMPRODUCT(COUNTIF(范围,排除值))
- 范围: 您要统计的单元格列表;
- 排除值: 要从范围列表中忽略的值。
1. 请在空白单元格中输入或复制以下公式以放置结果:
=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))
2. 然后,按Enter键以获得如下截图所示的结果:
公式解释:
- COUNTA(A2:A13): 此COUNTA函数返回范围A2:A13中所有非空单元格的数量。并将得到结果:12。
- COUNTIF(A2:A13,C2:C4): 此COUNTIF函数统计范围A2:A13中等于C2:C4中条件的单元格数量,因此您将得到结果:{2;1;2},这意味着Apple出现两次,Grape出现一次,Lemon出现两次。
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): 此SUMPRODUCT函数对数组中的所有项求和并返回最终结果:5。
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 :SUMPRODUCT函数的结果(5)从所有非空单元格的计数(12)中减去,以获得所需的最终结果。
注意:上述两个公式均不区分大小写。
相关功能说明:
- MATCH:
- MATCH函数在一系列单元格中搜索特定值,并返回该值的相对位置。
- ISNA:
- ISNA函数用于检查公式是否返回#N/A错误。
- SUMPRODUCT:
- SUMPRODUCT函数可用于将两列或多列或数组相乘,然后获取乘积的总和。
- COUNTIF:
- COUNTIF函数是Excel中的统计函数,用于计算满足条件的单元格数量。
- COUNTA:
- COUNTA函数返回非空单元格的数量。
更多文章:
- 统计Excel中等于多个值之一的单元格数量
- 假设我在A列中有产品列表,现在我想获取C4:C6范围内列出的特定产品Apple、Grape和Lemon的总数,如下截图所示。通常,在Excel中,简单的COUNTIF和COUNTIFS函数在此场景中不起作用。本文将讨论如何通过结合SUMPRODUCT和COUNTIF函数快速轻松地解决此问题。
- 统计Excel中两个值/日期之间的单元格数量
- 您是否尝试过获取或统计Excel中两个给定数字或日期之间的单元格数量,如下截图所示?本文将讨论一些处理此问题的有用公式。
- 统计Excel中不在两个给定数字之间的单元格数量
- 统计两个数字之间的单元格数量是我们常见的任务,但在某些情况下,您可能需要统计不在两个给定数字之间的单元格。例如,我有一个产品列表及其从周一到周日的销售数据,现在我需要获取不在特定低值和高值之间的单元格数量,如下截图所示。本文将介绍一些在Excel中处理此任务的公式。
最佳的办公生产力工具
Kutools for Excel - 助您脱颖而出
🤖 | Kutools AI 助手:基于智能执行革新数据分析:智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数… |
热门功能:查找、高亮或标记重复项 | 删除空行 | 合并列或单元格而不丢失数据 | 无需公式四舍五入 ... | |
超级 VLookup:多条件 | 多值 | 跨多表 | 模糊查找... | |
高级下拉列表:简易下拉列表 | 级联下拉列表 | 多选下拉列表... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较列以 选择相同和不同单元格 ... | |
特色功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...) ... | |
排名前 15 的工具集:12 个文本工具(添加文本,删除特定字符 ...) | 50+ 种图表 类型(甘特图 ...) | 40+ 实用 公式(基于生日计算年龄 ...) | 19 个插入工具(插入二维码,从路径插入图片 ...) | 12 个转换工具(小写金额转大写,汇率转换 ...) | 7 个合并和拆分工具(高级合并行,拆分 Excel 单元格 ...) | ... 更多功能 |
使用您偏好的语言来操作 Kutools - 支持英语、西班牙语、德语、法语、中文等 40 多种语言!
Kutools for Excel 拥有超过 300 项功能,确保您所需的一切都仅需一键之遥...
Office Tab - 在 Microsoft Office(包括 Excel)中启用标签式阅读和编辑
- 一秒内在数十个打开的文档之间切换!
- 每天为您减少数百次鼠标点击,告别鼠标手。
- 在查看和编辑多个文档时,您的生产力可提升 50%。
- 为 Office(包括 Excel)带来高效标签页,就像 Chrome、Edge 和 Firefox 一样。