如何在仅需满足某些条件的数组公式中包括“或"-Excel第2部分 [英] How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel Part 2
问题描述
这是此 XOR回答LX .
在原始问题中，我想计算矩阵中包含满足至少一个列特定条件的值的行数.引用的列是顺序的(例如，A1:D4)，但是这种情况需要索引不连续的列.
In the original question, I wanted to count the number of rows in a matrix that included values that fulfilled at least one column-specific criteria. The columns referenced were sequential (e.g., A1:D4), but the situation requires indexing columns that are not sequential.
获取数据矩阵:
A B C D E F G H
4 2 2 2 1 4 2 4
5 2 1 3 4 1
3 2 1 4 5 1
1 2 3 5 3 2 2 2
2 2 2 2 2 2 2 2
具有特定于列的条件:
- A列条件:> 2
- B列标准:> 2
- C列标准:< 2
- D列标准:> 4
- E列标准:> 2
- F列标准:> 3
- G列标准:> 2
- H列标准:> 3
为了计算具有所有列值的行数，并且至少满足特定于列的条件，我们可以使用
In order to count the number of rows that have values for all columns, as well as meet at least column-specific criterion, we could use XOR LX's code:
=SUM(N(MMULT(IF(MMULT(N(A1:H5=""),TRANSPOSE(COLUMN(A1:H5)^0))=0,COUNTIF(OFFSET(A1,ROW(A1:H5)-MIN(ROW(A1:H5)),COLUMN(A1:H5)-MIN(COLUMN(A1:H5))),{">2",">2","<2",">4",">2",">3",">2",">3"}),0),TRANSPOSE(COLUMN(A1:H5)^0))>0))
...在处理空格时可能会遇到一些问题，因此可以选择:
...which might have some problems with handling blanks, so the alternative:
=ROWS(A1:D4)-COUNTIFS(A1:A5,"<=2",B1:B5,"<=2",C1:C5,">=2",D1:D5,"<=4",E1:E5,">=2",F1:F5,">=3",G1:G5,">=2",H1:H5,">=3")-COUNT(1/N(MMULT(N(A1:H5=""),TRANSPOSE(COLUMN(A1:H5)^0))>0))
...据我测试，似乎可以处理空白，而不管其出现的频率或位置如何.
...which as far as I have tested seems to handle blanks regardless of their frequency or location.
但是，如果只计数包含某些数据的行怎么办，而忽略其他列.此外，如果仅应对某些列的条件进行评估怎么办?
But what if only rows with data for certain should be counted, ignoring the other columns. Furthermore, what if only certain columns should be evaluated for their criteria?
让我们以上面的示例为例.我们的目标是对行的数量进行计数，其中i)某些列中的完整数据，以及ii)满足至少一个列特定标准的某些列中的条目.
Let's take the example above. Our goal is to count the number of rows with i) full data in certain columns and ii) entries in certain columns that meet at least one column-specific criterion.
应具有完整数据的相关列是A:C和F:H.应根据其条件评估的列仅是A:C.解决方案是2，因为:
The relevant columns that should have full data are A:C and F:H. The columns that should be evaluated for their criteria are A:C only. The solution is 2 since:
- 总共有5行，但是只有第1、3、4和5行符合条件，因为它们在A:C和F:H列中具有完整数据.第2行在A列和G列中均缺少数据，但其中任一数据均应取消其包含的资格.请注意，即使第3行的D和E列中缺少数据，仍会包括在内，因为这些列并不重要.
- 第1行和第3行满足指定的条件，因为i)它们在A:C和F:H列中具有完整数据，并且ii)至少一个条目满足A，B和C列的条件之一；具体来说，是第1行，A列(4> 2)和第3行A列(3> 2)和C列(1 <2)中的值.请注意，即使满足2列的条件，这仍仅算作符合条件的一行.
Your thoughts would be most welcome. Please also show some love to XOR LX in the original post - I am sure you will find his solution helpful.
推荐答案
@PyjamaNinja鼓励我尝试为他们先前的问题发展我的引理".
@PyjamaNinja has encouraged me to try and develop my 'lemma' to their earlier question.
方法是:
A Count rows with entries in all specified columns (A-C and F-H)
B Count rows with entries in all specified columns that do _not_ meet any of the criteria in the columns being evaluated (A-C).
Subtract B from A.
=COUNTIFS(A1:A5,"<>",B1:B5,"<>",C1:C5,"<>",F1:F5,"<>",G1:G5,"<>",H1:H5,"<>")-COUNTIFS(A1:A5,"<=2",B1:B5,"<=2",C1:C5,">=2",F1:F5,"<>",G1:G5,"<>",H1:H5,"<>")
非常感谢@XOR LX.
With many thanks to @XOR LX.
这篇关于如何在仅需满足某些条件的数组公式中包括“或"-Excel第2部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
