excel计算至少一列中满足条件的行数

本文讨论了一种在至少一列中计算满足指定条件的行数的方法。示例工作表如下图1所示，其中详细列出了各国不同年份的废镍出口水平。

图1

假设我们想确定2004年出口总额大于或等于1，000的国家数量，我们可以使用以下公式：

=COUNTIF(B2:B14，"=1000 ")

或者：

=SUMPRODUCT(N(B2:B14=1000))

如果要计算2004年和2005年有多少国家的出口总额大于或等于1000，可以使用以下公式：

=COUNTIFS(B2:B14，"=1000，C2:C14，"=1000)

或者：

=SUMPRODUCT(N(B2:B14=1000)，N(C2:C14=1000))

现在，如果我们要计算2004年和2005年至少有一项数据符合这一标准的国家数量，会怎样？由于缺少数据，我们可以从工作表中清晰地标记出满足条件的数据，如下图2所示。

图2。

显然，“标准”的COUNTIF(S)公式结构无法满足要求，因为我们必须确保不重复计数。事实上，在这种情况下，大多数人倾向于使用SUMPRODUCT函数，即：

=SUMPRODUCT(N((b 2: b14=1000)(c 2: C14=1000)0))

但是，如果我们选择，我们可以使用COUNTIFS函数来构造一个解，因为考虑到这个函数相对于SUMPRODUCT函数的优势(一般来说，COUNTIFS函数引用整个列的能力更有效)，在某些情况下可能是值得的。

回到我们刚刚解决的问题，我们实际上应该考虑以下三种互斥的情况：

1)2004年的数字=1000，而2005年的数字是1000。

2)2004年的数字是1000，而2005年的数字是=1000。

3)2004年的数字=1000，2005年的数字=1000。

然后，把每种情况的统计结果加起来。转换成Excel的公式为：

=COUNTIFS(B2:B14，"=1000，C2:C14，"=1000) COUNTIFS(B2:B14，"=1000，C2:C14，" 1000) COUNTIFS(B2:B14，" 1000，C2:C14，"=1000)

这个公式既不优雅也不简洁。但是，我们可以将其缩写为：

=SUM(COUNTIFS(B2:B14，{"="，"="，"" }1000，C2:C14，{"="，" "，"="}1000))

这样，成功实现了基于COUNTIFS函数的求解，取代了通常的SUMPRODUCT函数公式构造。

接下来，考虑期望结果中涉及的列数不仅是两列，甚至是多列的情况。例如，假设您想确定从2004年到2012年每年至少有一个大于或等于1000的国家数量。如下图3所示，我们可以在工作表中标记符合条件的数据，除了2个国家之外，其他国家都符合条件。

图3。

此时，我们根本无法按照上述方法构造出与SUMPRODUCT函数解等价的COUNTIFS。使用SUMPRODUCT函数的公式：

=SUMPRODUCT(N)((b 2: b 14=1000)(c 2: c 14=1000)(d 2:d 14=1000)(e 2: e 14=1000)(f 2: f 14=1000)(g 2:g 14=1000)(h 2:h 14=1000)(I 2: I 14=1000)(j 23:0)

你可以得到正确的结果。但是，公式太笨拙了。如果考虑的列数是30而不是9呢？

幸运的是，因为示例中的列区域是连续的，所以您可以在单个表达式中查询整个区域(B2: j14)，然后适当地操作结果数组。

此解决方案的数组公式如下：

=sum(n(mmult(n(b2:j141000),transpose(column(b2:j14)^0))0)

在公式中，比较区域中的每个元素是否大于或等于1000:

B2:J141000

结果是一个包含以下布尔值的数组：

{真，真，真，真，真，真，真，真，真，真；假，假，真，真，真，真，真，真，真，真，真；假，假，真，真，假，真，真，真，真，真；假，假，假，假，假，真，真，真，假；真，真，真，真，真，真，真，真，真，真；真，真，真，真，真，真，真，真，真，真；假，真，假，假，假，假，真，假，真；真，真，真，真，真，真，真，真，真，真；假，假，假，假，假，假，假，假，假，假；真，真，真，真，真，真，真，真，真，真；真，真，真，真，真，真，真，真，假，真；假，假，假，假，假，假，假，假，假，假；真，真，真，真，真，真，真，真，真，真}

使用普通函数强制转换成数字：

N(B2:J141000)

得到：

{1,1,1,1,1,1,1,1,1;0,0,1,1,1,1,1,1,1;0,0,1,1,0,1,1,1,1;0,0,0,0,0,1,1,1,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1;0,1,0,0,0,0,1,0,1;1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1}

现在，为了计算每一行中一的数量，我们使用MMULT。并且，由于上述数组（一个13行乘9列的数组)包含9列，因此我们用来形成乘积的矩阵的行数必须等于该数组的列数。这样，形成第二个矩阵的公式构造为：

TRANSPOSE(COLUMN(B2:J14)^0)

转换为：

transpose({2,3,4,5,6,7,8,9,10}^0)

转换为：

转置({1,1,1,1,1,1,1,1,1,1})

由于必须确保由9个一组成的数组由9行组成，因此使用移项函数来转换：

{1;1;1;1;1;1;1;1;1}

这样，就可以将上述两个数组传递给求不同单价下的利润函数，因此：

mmult(n(b2:j141000),transpose(column(b2:j14)^0)

转换为：

MMULT({1，1，1，1，1，1，1，1，1，1，1；0,0,1,1,1,1,1,1,1;0,0,1,1,0,1,1,1,1;0,0,0,0,0,1,1,1,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1;0,1,0,0,0,0,1,0,1;1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1},{1;1;1;1;1;1;1;1;1})

得到：

{9;7;6;3;9;9;3;9;0;9;8;0;9}

然后，确定该数组中哪些元素大于0,然后将结果求和。因此，公式：

=sum(n(mmult(n(b2:j141000),transpose(column(b2:j14)^0))0)

转换为：

=SUM(N({ 9；7;6;3;9;9;3;9;0;9;8;0;9}0))

转换为：

=SUM(N({ TRUE；真；真；真；真；真；真；真；假；真；真；假；真}))

转换为：

=SUM({ 1；1;1;1;1;1;1;1;0;1;1;0;1})

结果为：

11

如果不想使用数组公式，可以使用下面的公式替换：

=sum(n(mmult(n(b2:j141000),row(index(a:a,1):index(a:a,columns(b2:j14)))^0)0)