Note: The other languages of the website are Google-translated. Back to English

计算不包含很多值的单元格数量

通常,使用 COUNTIF 函数很容易对不包含一个特定值的单元格进行计数。 这篇文章将逐步介绍如何计算 Excel 中指定范围内不包含许多值的单元格数量。


如何计算不包含很多值的单元格?

如下图所示,要计算 B3:B11 中不包含 D3:D4 中列出的值的单元格,您可以执行以下操作。

通用公式

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

参数

Range (必需):要计算不包含很多值的单元格的范围。

标准范围 (必需):该范围包含您在计算单元格时要排除的值。

请注意: 此公式必须作为数组公式输入。 应用公式后,如果公式周围有大括号,则成功创建数组公式。

如何使用这个公式?

1. 选择一个空白单元格输出结果。

2.在其中输入以下公式,然后按 按Ctrl + 转移 + 输入 同时获得结果。

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

这些公式如何运作?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

  • TRANSPOSE(D3:D4):TRANSPOSE 函数旋转 D3:D4 的方向并返回 {“计数”,“空白”};
  • SEARCH({“count”,”blank”},B3:B11):这里的SEARCH函数从范围B3:B11中找到子串“count”和“blank”的位置,并返回一个数组作为 {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE! ;1,
    #VALUE!;1,7}.
  • 在这种情况下,B3:B11 中的每个单元格将被搜索两次,因为在计算单元格时您要排除两个值,因此您将在数组中获得 18 个值。 数组中的每个数字表示 B3:B11 的每个单元格中“count”或“blank”的第一个字符的位置。
  • ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
    #VALUE!;1,#VALUE!;1,7}
    :ISNUMBER 函数在数组中遇到编号时返回 TRUE,如果遇到错误则返回 FALSE。 这里返回结果为 {假,假;假,假;真,假;假,真;真,假;假,假;假,假;真,假;
    真,真}.
  • --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
    FALSE;TRUE,TRUE})
    :这两个减号将“TRUE”转换为1,将“FALSE”转换为0。在这里你会得到一个新的数组 {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) ROW(D3:D4)^0:ROW 函数返回单元格引用的行号:{3;4},然后乘幂运算符(^)返回数字 3 和 4 的 0 次幂的结果,最后返回结果为 {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):MMULT 函数返回这两个数组的矩阵乘积:{0;0;1;1;1;0;0;1;2} 以满足原始数据。 数组中的任何非零数字表示已找到至少一个排除字符串的值,零表示未找到排除字符串。

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

  • {0;0;1;1;1;0;0;1;2}>0:这里检查数组中的每个数字是否都大于0。如果一个数字大于0,则返回TRUE,否则返回FALSE。 然后你会得到一个新的数组 {假;假;真;真;真;假,假,真;真}.
  • 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}:由于我们只需要计算不包含指定值的单元格,我们需要通过从1中减去它们来反转数组中的这些值。这里数学运算符自动将TRUE和FALSE值转换为1和0,最后返回结果为 {1;1;0;0;0;1;1;0;0}.
  • SUM{1;1;0;0;0;1;1;0;0}:SUM 函数对数组中的所有数字求和并返回最终结果为 4。

相关功能

Excel SUM 函数
Excel SUM 函数添加值。

Excel MMULT 函数
Excel MMULT 函数返回两个数组的矩阵乘积。

Excel ISNUMBER函数
Excel ISNUMBER 函数在单元格包含数字时返回 TRUE,否则返回 FALSE。

Excel TRANSPOSE 函数
Excel TRANSPOSE 函数旋转区域或数组的方向。

Excel ROW功能
Excel ROW函数返回引用的行号。


相关公式

计算不包含错误的单元格
本教程提供了详细步骤,可帮助您计算 Excel 中指定范围内不包含错误的单元格数量。

计算不包含特定文本的单元格
您可以使用带有通配符的 COUNTIF 函数来计算区域中包含特定文本的单元格数量。 相反,使用 COUNTIF 函数对不包含特定文本的单元格进行计数也很容易。 本教程提供了帮助解决问题的详细步骤。

按星期几计算范围内的日期单元格数
Excel 提供了 SUMPRODUCT 和 WEEKDAY 函数的组合,可以帮助我们轻松计算某个范围内指定工作日的数量。 本教程提供了一个分步指南来帮助您完成它。

计算文本单元格的数量
要计算在指定范围内包含文本的单元格数量,COUNTIF 函数可以帮助轻松完成它。 本文将详细介绍该公式,以帮助您解决问题。


最佳办公效率工具

Kutools for Excel-帮助您从人群中脱颖而出

您想快速,完美地完成日常工作吗? Kutools for Excel具有300项强大的高级功能(合并工作簿,按颜色求和,拆分单元格内容,转换日期等),并为您节省80%的时间。

  • 专为1500个工作方案而设计,可帮助您解决80%的Excel问题。
  • 每天减少成千上万的键盘和鼠标点击,减轻您疲倦的眼睛和手部的疲劳。
  • 在3分钟内成为一名Excel专家。 不再需要记住任何痛苦的公式和VBA代码。
  • 30天无限制免费试用。 60天退款保证。 免费升级和支持2年。
Excel功能区(已安装Kutools for Excel)

Office选项卡-在Microsoft Office(包括Excel)中启用选项卡式阅读和编辑

  • 一秒钟即可在数十个打开的文档之间切换!
  • 每天为您减少数百次鼠标单击,告别鼠标手。
  • 查看和编辑多个文档时,可将您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一样,为Office(包括Excel)带来高效选项卡。
Excel的屏幕截图(已安装Office选项卡)
按评论排序
注释 (0)
还没有评分。 成为第一位评论!
这里还没有评论
留下你的意见
以访客身份发帖
×
评价此帖子:
0   产品特性
建议地点