☰

如何逐行评估 COUNTIFS（或类似）中的多个 OR 条件？

tag-icon • tag-icon microsoft-excel worksheet-function countif
如何逐行评估 COUNTIFS（或类似）中的多个 OR 条件？

考虑以下示例数据。

A 乙
1 2
8 4
2 1
3 7
4 8

分别地，A 中小于 5 的计数为=COUNTIF(A1:A5, "<5")-> 4，B 中小于 5 的计数为=COUNTIF(B1:B5, "<5")-> 3。

我知道如果我想要 A 的行数和B 小于 5，我可以使用 COUNTIFS: =COUNTIFS(A1:A5, "<5", B1:B5, "<5")-> 2，因为 COUNTIFS 逐行评估条件。但是，我无法找到一种优雅的方法来获取 A 的行数或者B < 5。

使用样本数据，答案应该是 5。到目前为止我已经尝试过：

  • =COUNTIF(A1:A5, "<5") + COUNTIF(B1:B5, "<5")-> 7，因为单元格不被视为行。这仅计算小于 5 的单元格数量。
  • =SUM(IF(A1:A5 < 5, 1, 0), IF(B1:B5 < 5, 1, 0))-> 7，执行与上述相同的操作。
  • =SUM(IF(OR(A1:A5 < 5, B1:B5 < 5), 1, 0))-> 1，因为单元格不被视为行，并且 OR 仅返回一个值，该值结合了每个单元格条件的结果。如果任何单元格小于 5，则计算结果为 1，如果没有单元格小于 5，则计算结果为 0。

我找到了一个可行的解决方案，它使用概率加法定理（请参阅我的回答），但如果可能的话，我希望找到一种更易于阅读和扩展的方法。我该如何实现这一点？

答案1

在 Excel 365 中，您可以使用 FILTER 函数：

=COUNT(FILTER(A1:A5,(A1:A5<5) + (B1:B5<5)))

用 * 分隔条件将起到“AND”的作用，而用 + 分隔条件将起到“OR”的作用。

答案2

从数学上讲x OR y，相当于x + y >= 1x 和 y 为布尔值。在 Excel 中，我们通常使用 来将 bool 转换为 int --。因此，以下任一方法都可以

=SUM(--(IF(A1:A5 < 5, 1, 0) + IF(B1:B5 < 5, 1, 0) > 0))
=SUM(--(--(A1:A5 < 5) + --(B1:B5 < 5) > 0))

它甚至可以缩短为

=SUM(--(-(A1:A5 < 5) + -(B1:B5 < 5) < 0))
=SUM(--(-(A1:A5 < 5) - (B1:B5 < 5) < 0))

答案3

这不是最漂亮的解决方案，当你有两个以上的条件时，它会变得非常混乱，但可以使用概率加法定理 P(A or B) = P(A) + P(B) - P(A and B)计算请求的数量：

  • =COUNTIF(A1:A5, "<5") + COUNTIF(B1:B5, "<5") - COUNTIFS(A1:A5, "<5", B1:B5, "<5")-> 5

答案4

如果所讨论的列是连续的，那么更灵活且可扩展的解决方案是：

=SUMPRODUCT(N(MMULT(N(A1:B5<5),TRANSPOSE(COLUMN(A1:B5)))>0))

或者，对于 O365：

=SUM(N(MMULT(N(A1:B5<5),SEQUENCE(COLUMNS(A1:B5)))>0))

相关内容