☰

为什么我的 Excel COUNTIF 函数总是返回值“0”？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
为什么我的 Excel COUNTIF 函数总是返回值“0”？

我有一个 Excel 工作簿，其中 B 列中的每个单元格都包含一个简短的寓言（故事）。这些单元格（格式为“常规”）包含的文本值按电子表格标准来说很大：除一个单元格外，其余单元格都超过了文本显示的 255 个字符限制，一个单元格包含超过 3,000 个字符，我猜大多数单元格都在 500 到 700 个字符之间。下面的第二张图显示了这样的单元格。

在另一个工作簿中，我有关于寓言的（元）数据。特别是，我对某些单词的出现频率感兴趣。在下面的插图中，C 列包含我感兴趣的单词列表。B 列包含全部的相应单词出现的次数（计算寓言中的多次出现）；这与这个问题无关。我还想计算每个单词出现的寓言的数量（至少一次）。我称之为“分散”（请参阅​​下面注释中的解释）。我一直在使用Ctrl+ F（“查找和替换”）并单击“查找全部”，它会报告包含搜索字符串的单元格的数量（参见下面的第二张图）。我一次对每个单词执行一次此操作，然后手动将数字输入到第 N 列中。

（您可能可以跳过本段。）  我需要单个、整个单词实例的计数，而不是词根的派生形式（甚至不是复数）。例如，我对“动物”的计数需要返回“动物”的计数，而不是“动物”或任何其他此类变体的计数。早些时候，我意识到简单地搜索一个单词可能会导致错误的计数，因为它会包含包含我搜索的单词。我通过在搜索词的开头和结尾处填充空格来解决这个问题——在 E 列（例如，“动物”），其中包含=" "&C2&" "——并确保我检查这些词的列也隔离了这些词。每当一个标点符号与寓言中单词的最后一个或第一个字母相邻时，我都会插入一个空格来消除任何这种相邻。例如，“todo esto, porque siendo”变成了“todo esto , porque siendo”。（这部分灵感来自JNevill's评论此问题： Excel 的 COUNTIF 不起作用.) 然后我的Ctrl+F搜索返回我所搜索单词的单个实例的计数。

当然，这很繁琐、耗时且容易出错，所以我开始怀疑是否有公式可以做同样的事情，但速度更快。一些帖子/网页建议该COUNTIF函数可以实现这一点，所以我一直在尝试，但到目前为止，我的尝试每次都失败了。在上图中，M 列包含

=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2)

如您所见，它显示为“0”，而它应该与 N 列中的数字相匹配（即使用Ctrl+找到的计数F）。（如上所述，E 列包含细绳我正在寻找的是单词我正在寻找，在开头和结尾用空格填充。）COUNTIF我尝试过的使用该功能的每种配置都失败了。

下面是我的寓言工作表中单元格 B23 的图像（即一个寓言）。视图被“查找和替换”对话框部分遮挡，显示 13 个单元格（寓言）至少包含一次单词“animal”（即，它们包含字符串“animal”，前导和尾随空格）。

（您可能需要放大图像才能清楚地看到这一点。

我要向您展示的最后一件事是当我使用该COUNTIF函数时会发生什么。我认为这可能是我无法使其工作的关键，因为一旦我从所需的列中添加范围，在我按下之前Enter，我就会看到#VALUE!; #VALUE!; …范围旁边的内容。它看起来像这样：

当我按下时Enter，我得到了该列单元格中的“0”。

我做错了什么？我如何计算每个单词出现（至少一次）的寓言数量？

如果您从屏幕截图中看不出来，我使用的是 Excel 2007。

答案1

@fixer1234 是正确的—— COUNTIF计算平等的为一个值，而不是单元格包含字符串。为此，您需要使用FIND或SEARCH。（它们是相同的，只是FIND区分大小写 和SEARCH不区分大小写。我只是假设您想要不区分大小写的那个。）

从做事开始

=SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)

这将在另一个工作表的单元格中查找 的值E2（在您的示例中为“ animal ”）  B1。如果该字符串值存在于该单元格中，则将返回单元格文本中搜索字符串第一次出现的位置（第一个字符为 1）。如果字符串不存在，它将返回#VALUE!。

接下来，

=IF(ISERROR(SEARCH(E$2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)), 0, 1)

如果字符串存在，则计算结果为 1，如果不存在，则计算结果为 0。下一步是：

=SUM(IF(ISERROR(SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

这将对另一张工作表的上一个公式沿列求和 B，从而得到所需的计数。请注意，以上是数组公式。这意味着，要使其工作，您必须 在输入公式后 输入++ Ctrl。现在您可以将其放入单元格并向下拖动。ShiftEnterM2

您实际上不需要有列 E- 您可以在SEARCH公式中处理它：

=SUM(IF(ISERROR(SEARCH(" "&C2&" ", '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

我在 Excel 2013 中测试了这一点，但我以前也做过类似的事情，我希望这个解决方案可以在 Excel 2007 中起作用。（并且我使用包含超过 750 个字符的单元格以及包含空格的工作簿文件名进行了测试。）

PS，我不知道为什么#VALUE!在“函数参数”对话框中会出现这些错误；它对我有用：

“函数参数”对话框

（尽管我的答案没有使用，但我还是对其进行了测试COUNTIF。）您在执行此操作时是否打开了其他工作簿？

答案2

您可以使用 COUNTIF() 来计算一个字符串是否包含另一个字符串，方法是利用 Microsoft 提供的通配符。星号 (*) 匹配零个或多个字符，而问号 (?) 匹配单个字符。

因此，不要=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2) 使用=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,"*"&E2&"*")。

星号允许在字符串前后放置任何其他文本。由于字符串已经有前导空格和尾随空格，因此复数和其他形式的单词应该不会有问题。

答案3

我在查看您的屏幕截图时遇到了一些问题，但由于您尝试计算其他函数的输出，因此我会尝试在 countif 语句中使用 value() 函数。 Value() 将告诉 excel 查看输出（您在单元格中看到的内容），而不是您写入其中的底层函数。

您的 countif 语句看起来像“=countif(value([other worksheet]range),criteria)”

答案4

如果长字符串在 B2 中，而您要查找的单词在 C2 中，则以下公式将计算该单词在字符串中出现的次数

  • =+IFERROR((LEN(B2)-LEN(SUBSTITUTE(B2,C2,"")))/LEN(C2),"")

零件如下

  1. iferror（可选）如果发生错误则将其留空
  2. Len(b2) 原始单元格的长度
  3. LEN(SUBSTITUTE(B2,C2,"") 删除所有出现的模式。如果模式在数据中，则该代码会更短
  4. Len(c2) 模式的长度，如果长度为 6 个字符，并且出现 3 次，则 18/6 将导致 3

相关内容