当在混合数字和文本时，使用COUNTIF函数出现意外行为（EXCEL）

Question

这个问题涉及到Excel的COUNTIF函数在使用作为数组公式时，如何处理不同的数据类型。

有很多好的帖子详细介绍了如何使用COUNTIF来执行任务，比如从列表中提取唯一值，例如this post。我已经成功地使用了这些和其他帖子中的例子来解决特定的问题，但我正在尝试深入了解数组公式，以便根据新的需要调整我的公式。

我发现了COUNTIF的一个奇怪行为。通常，Excel似乎将字符串视为"大于"数字，所以下面的示例是有效的：

Cell Formula      Returns
=1<2              TRUE
="a"<"b"          TRUE
="a">"b"          FALSE
=1<"b"            TRUE

现在，假设A1:A6范围包含以下数据集：

1
2
3
A
B
C   

针对该集合中的每个单元格，我想要检查有多少个集合中的所有单元格小于或等于该单元格（这是更复杂公式中的一种有用技术）。我在B1:B6范围内输入以下数组公式：{=COUNTIF($A$1:$A$6,"<="&$A$1:$A$6)} (CTRL + SHIFT + ENTER)。根据上面比较数字和字符串的示例（也在下面的D列中说明），我期望输出结果如C列所示。然而，数组公式返回了B列中显示的结果，这表明按数组方式计算COUNTIF时字符串和数字元素被分别计算。

Column A     Column B     Column C     Column D
1            1            1            A1<"C" = TRUE
2            2            2            A2<"C" = TRUE
3            3            3            A3<"C" = TRUE
A            1            4            A4<"C" = TRUE
B            2            5            A5<"C" = TRUE
C            3            6            A6<"C" = FALSE

问题是如何在C列中产生输出？（编辑：澄清一下，我特别寻求利用COUNTIF的数组属性来解决问题的解决方案。）

如果有关于为什么数组方式的COUNTIF表现似乎与单元格示例不同的任何见解，也将不胜感激。

注意：我已经将示例从非英语版本的Excel翻译成了英语，因此提前为任何拼写错误道歉。

附注：背景是当我尝试构建一个公式时遇到了这个问题，该公式将从可能重复的列表中提取唯一值，并按数字/字母顺序排序唯一值。 我目前的解决方案是分两步完成。 一个可以一步完成的解决方案在此提出。

- Egalth

---

我认为您可能错误地使用了 COUNTIF。 - Tim Biegeleisen

语法{=COUNTIF($A$1:$A$6,"<="&$A$1:$A$6)}实际上是从一个更复杂的公式中提取出来的，该公式的行为与预期完全一致，并且已被其他人用于解决类似的问题。本文说明了该公式的一个中间步骤。 - Egalth

Answer 1

首先，非常好的问题，而且还是关于一个有趣的主题。

当我第一次遇到COUNTIF(S)/SUMIF(S)函数的行为时，我也感到惊讶。不过，为它们辩护一下，我们可以构造一些情况，在这些情况下，我们实际上希望将字符串和数字分开。

为了构建所需的公式数组，您需要使用以下代码：

MMULT(0+(转置($A$1:$A$6)<=$A$1:$A$6),ROW($A$1:$A$6)^0)

但请注意，必要的转置意味着包含此构造的任何设置都需要使用CSE进行提交。

问候

- XOR LX

Answer 2

不同的行为可以通过比较以下两个公式轻松展示：

`=COUNTIF($A$1:$A$6,"<=A")`

和

`{=COUNT(IF($A$1:$A$6<="A",1))}`

第一个公式仅从 `$A$1:$A$6` 中获取文本值，因为它是明确要比较的文本，并且忽略其他值更快。由于相同的原因，`=COUNTIF($A$1:$A$6,"<=3")` 仅从 `$A$1:$A$6` 中获取数字值。即使标准是带有单元格引用的连接，连接也将是第一个处理过程，并导致 "<=A" 或 "<=3"。因此，很清楚要比较什么，文本还是数字。

第二个公式首先需要一个比较的数组，然后执行 `IF`，得到一个 1 或 `FALSE` 的数组，然后再计数。但是 "A" 也可以是单元格引用。因此，在开始时不清楚要比较什么，第一个数组必须比较 `$A$1:$A$6` 中的所有值。

因此，`COUNTIF(S)` 和 `SUMIF(S)` 不能用于比较混合文本和数字数据。

解决方案已经由 XOR LX 给出。

顺便说一句：关于你的 "PS. For a background"，你应该考虑来自德国 Excel 网站的以下解决方案：http://www.excelformeln.de/formeln.html?welcher=236。

在你的链接示例中：

`B2` 向下的公式

{=INDEX($A$2:$A$99,MATCH(LARGE(COUNTIF(A$2:A$99,">="&A$2:A$99)+99*ISNUMBER(A$2:A$99),ROWS($1:1)),COUNTIF(A$2:A$99,">="&A$2:A$99)+99*ISNUMBER(A$2:A$99),0))&""}

在这个解决方案中，COUNTIF与>=进行比较，因此最大的文本或数字将计算最低并获得最低位置。所有数字位置都加上99。因此，它们始终大于所有可能的文本位置。因此，我们有一个按降序排序的数组。然后，使用LARGE，列表从最高到最低位置创建。

- Axel Richter

---

谢谢，那有一些道理 :) - Egalth

Answer 3

我怀疑countif函数是否适合你在这里想要实现的目标。

尝试使用以下方法（ctrl+shift+enter）：

={SUM(IF(A1>=$A$1:$A$6,1,0))}

您将获得

1
2
3
4
5
6

PS：CountIf 在内部基本上是一个数组函数。在另一个数组函数中使用它会导致多个数组函数，其行为变得复杂。最好使用清晰的逻辑路径来使用数组函数。

在 Excel 2013 中测试，您将只在所有结果中获得 1，而不是在 B 列中提出的内容。

目前，在您提供的函数中，countif 无法确定要将哪个单元格与哪个单元格进行比较。数组函数扩展范围，然后执行提供的操作。因此，它将每个单元格与相同的单元格进行比较，并导致结果为 1。

- Som Shekhar

---

也许您可以澄清一下您的评论：“我怀疑COUNTIF函数是否适合您在此处想要实现的目标。”？您怀疑的依据是什么？是哪些见解导致了您得出这个结论，这对于帮助OP理解他们所见到的COUNTIF行为也可能很有用。 - XOR LX

另外，为什么单元格引用A1要加括号？ - XOR LX

parentheses are not required. - Som Shekhar

如果只在单元格B1中输入公式，然后复制粘贴下来，那么只会得到一个由1组成的向量。要重现整个B列，需要在输入公式之前先选中整个B1:B6范围。 - Egalth

是的，我明白了，在这个简单的例子中，COUNTIF并不是必需的。但在更复杂的公式中，它是一个有用的结构，例如当条件是动态范围时。 - Egalth

好的，明白了。你的目的是理解COUNTIF函数的问题而不是结果。我认为Axel已经完美地解释了它。 - Som Shekhar

Answer 4

尝试在 B1 中使用此 FormulaArray，然后复制到 B6：

=SUM(($A$1:$A$6<=$A1)*1)

- EEM

---

谢谢，这确实可以重现C列。(我应该更明确地说明我特别在寻找COUNTIF的解决方案，它可以扩展到具有动态区域的标准情况。) - Egalth