如何在仅需满足某些条件的数组公式中包括OR-Excel [英] How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel

问题描述

我试图计算以下行的数量:1)所有列都有条目，并且ii)至少有一个满足列特定条件的值.

I am trying to count the number of rows that: 1) have entries for all columns, and ii) have at least one value that meets a column-specific criterion.

这是一个例子:

A B C D
4 4 3 5
2 2 2 2
3 1 2 5
1   3 2

• A列阈值:> 2
• B列阈值:> 2
• C列阈值:< 2
• D列阈值:> 4
• Column A Threshold: >2
• Column B Threshold: >2
• Column C Threshold: <2
• Column D Threshold: >4

此示例的答案是2，因为2/4行包含至少1个满足列阈值的值.具体来说，第1行有3个满足列阈值的值，第3行有2个值.第2行没有满足列阈值的值，而第4行则不计算在内，因为它不包含所有列的条目，例如:

The answer for this example is 2 because 2/4 rows include at least 1 value that meets a column threshold. Specifically, row 1 has 3 values that meet the column thresholds and Row 3 has 2 values. Row 2 has no values that meet the column-threshold, while row 4 should not be counted because it does not contain entries for all columns, e.g.:

A B C D
T T F T
F F F F
T F F T
F F F F

因此，我不想统计每行中满足特定列阈值的次数，而是每行是否至少满足1次.

So I don't want to count how many times a specific column threshold has been met in each row, but whether at least 1 has been met in each row.

我有一个预感，SUMPRODUCT可能对于此问题很有用，但是我不知道如何仅为某些条件添加OR条件(例如，示例，其中将SUMPRODUCTS中的"+"用作OR函数很重要这两个条件).

I have a hunch that SUMPRODUCT could be useful for this issue, but I do not know how to add an OR criterion for only some criteria (for instance, the example described here of using a "+" in SUMPRODUCTS as an OR function counts both the criteria).

最欢迎您的想法.

推荐答案

一个非常有趣的问题.

数组公式**:

=SUM(N(MMULT(IF(MMULT(N(A1:D4=""),TRANSPOSE(COLUMN(A1:D4)^0))=0,COUNTIF(OFFSET(A1,ROW(A1:D4)-MIN(ROW(A1:D4)),COLUMN(A1:D4)-MIN(COLUMN(A1:D4))),{">2",">2","<2",">4"}),0),TRANSPOSE(COLUMN(A1:D4)^0))>0))

编辑:如果范围内没有空格，可以将其大大简化为:

Edit: Without the presence of blanks within the range, this could be simplified greatly to:

=ROWS(A1:D4)-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")

因为从逻辑上讲，要获得至少一个条件成立的行数，我们可以计算没有一个条件的行数为真，并从总行数中减去该值.

since, logically, to obtain the number of rows for at which at least one condition is true, we can calculate the number of rows for which none of those conditions are true and subtract that value from the total number of rows.

实际上，很可能也可以对此设置进行调整以解决空白问题.会调查一下.

In fact, it may well be possible to adapt this set-up to account for blanks as well. Will look into it.

更新:的确，我相信这会起作用:

Update: Indeed, I believe this will work:

=ROWS(A1:D4)-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")-COUNT(1/N(MMULT(N(A1:D4=""),TRANSPOSE(COLUMN(A1:D4)^0))>0))

致谢

**输入数组公式的方式与使用标准"公式的方式不同.您不仅要按ENTER键，还需要先按住CTRL和SHIFT键，然后再按ENTER键.如果操作正确，您会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入大括号).

这篇关于如何在仅需满足某些条件的数组公式中包括OR-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！