如何在数组公式中包含 OR，其中只有一些条件需要为 TRUE - Excel [英] How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
问题描述
我正在尝试计算以下行数:1) 对所有列都有条目，并且 ii) 至少有一个满足特定列标准的值.
这是一个例子:
A B C D4 4 3 52 2 2 23 1 2 51 3 2- A 列阈值:>2
- B 列阈值:>2
- C 列阈值:<2
- D 列阈值:>4
此示例的答案是 2，因为 2/4 行包含至少 1 个满足列阈值的值.具体来说，第 1 行有 3 个满足列阈值的值，第 3 行有 2 个值.第 2 行没有满足列阈值的值，而第 4 行不应该被计算在内，因为它不包含所有列的条目，例如:
A B C DT T F TF F F FT F F TF F F F所以我不想计算每行满足特定列阈值的次数，而是计算每行是否至少满足 1 次.
我有预感 SUMPRODUCT 可能对这个问题有用，但我不知道如何只为某些条件添加 OR 条件(例如，示例 此处描述 在 SUMPRODUCTS 中使用+"作为 OR 函数计数两个标准).
非常欢迎您的想法.
非常有趣的问题.
数组公式**:
=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))
编辑:如果范围内不存在空格，这可以大大简化为:
=ROWS(A1:D4)-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")
因为，从逻辑上讲，要获得至少一个条件成立的行数，我们可以计算这些条件都不成立的行数为真并从总行数中减去该值.
事实上，很可能也可以调整此设置以解决空白.会研究一下.
更新:确实，我相信这会奏效:
=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 在公式周围放置了大括号 {}(尽管不要尝试自己手动插入这些).
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.
Here's an example:
A B C D
4 4 3 5
2 2 2 2
3 1 2 5
1 3 2
- Column A Threshold: >2
- Column B Threshold: >2
- Column C Threshold: <2
- Column D Threshold: >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
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.
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).
Your thoughts would be most welcome.
Very interesting question.
Array formula**:
=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))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
这篇关于如何在数组公式中包含 OR，其中只有一些条件需要为 TRUE - Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
