arrays - 混合数字和文本时出现意外的 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 列中显示的结果，这表明字符串和数字元素通过 arraywise 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 中翻译了这些示例，因此对于任何拼写错误，我提前表示歉意。

PS。作为背景，当我尝试构建一个公式时，我遇到了这个问题，该公式 既从可能重复的列表中提取唯一值， 并且对数字中的唯一值进行排序/按字母顺序。我当前的解决方案是分两步完成此操作。如何一步完成的一种解决方案 is proposed here .

Answer 1

首先，问题的布局非常好，而且主题很有趣。

当我第一次遇到COUNTIF(S)的这种行为时，我也扬起了眉毛。/SUMIF(S)功能。在他们的辩护中，我想我们可以构建我们实际上希望分开考虑字符串和数字的情况。

为了构建所需的公式内数组，您将需要以下内容:

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

但请注意，必要的换位意味着任何包含此结构的设置都需要提交 CSE。

问候