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

在本文中,将讨论一种解决方案,用于计算至少一列中满足指定条件的行数。下图1显示了一个工作表示例,其中详细列出了不同年份各国废镍的出口水平。

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

图1

假设我们要确定2004年出口总额大于或等于1000的国家的数量,我们可以使用公式:

=COUNTIF(B2:B14,& # 8221;& gt=1000″)

或者:

= sum product(N(B2:B14 & gt;=1000))

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

=COUNTIFS(B2:B14,& # 8221;& gt=1000″,C2:C14,& # 8221;& gt=1000″)

或者:

= sum product(N(B2:B14 & gt;=1000),N(C2:C14 & gt;=1000))

现在,如果我们想计算2004年和2005年的数据至少达到这一标准的国家的数量呢?由于数据较少,我们可以从工作表中清楚地标记出符合条件的数据,如下图2所示。

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

图2

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

= sum product(N((B2:B14 & gt;= 1000)+(C2:C14 & gt;=1000)>0))

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

回到我们刚才要解决的问题,我们实际上要考虑以下三种互相排斥的情况:

1)2004年的数字>:=1000,而2005年的数字< 1000

2)2004年的数字是:=1000

3)2004年的数字>:=1000,而2005年的数字>:= 1000

然后,加上每种情况的统计结果。转换为Excel的公式为:

=COUNTIFS(B2:B14,& # 8221;& gt=1000″,C2:C14,& # 8221;& gt=1000″)+COUNTIFS(B2:B14,& # 8221;& gt=1000″,C2:C14,& # 8221;& lt1000″)+COUNTIFS(B2:B14,& # 8221;& lt1000″,C2:C14,& # 8221;& gt=1000″)

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

=SUM(COUNTIFS(B2:B14,{ & # 8220;& gt=”,”& gt=”,”& lt“} & amp1000,C2:C14,{ & # 8220;& gt=”,”& lt“,”& gt=”} & amp1000))

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

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

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

图3

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

= sum product(N((B2:B14 & gt;= 1000)+(C2:C14 & gt;= 1000)+(D2:D14 & gt;= 1000)+(E2:E14 & gt;= 1000)+(F2:F14 & gt;= 1000)+(G2:G14 & gt;= 1000)+(H2:H14 >;= 1000)+(I2:I14 & gt;= 1000)+(J2:J14 & gt;=1000)>0))

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

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

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

= SUM(N(MMULT(N(B2:J14 & gt;1000),transpose(column(b2:j14)^0))&gt;0))

公式,比较面积中每个元素是否大于等于1000:

B2:J14 & gt;1000

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

{真实,真实,真实,真实,真实,真实,真实,真实,真实;假,假,真,真,真,真,真,真,真,真,真,真;假,假,真,真,假,真,真,真,真,真,真;假,假,假,假,假,真,真,真,假;真实,真实,真实,真实,真实,真实,真实,真实,真实;真实,真实,真实,真实,真实,真实,真实,真实,真实;假、真、假、假、假、假、假、真、假、真;真实,真实,真实,真实,真实,真实,真实,真实,真实;假、假、假、假、假、假、假、假、假;真实,真实,真实,真实,真实,真实,真实,真实,真实;真实,真实,真实,真实,真实,真实,真实,真实,虚假,真实;假、假、假、假、假、假、假、假、假;真,真,真,真,真,真,真,真,真,真}

使用n函数转换为一个数字:

n(B2:J14 & gt;1000)

获取:

{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的数量,我们使用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})

由于需要确保9个1的数组包含9行,因此转置函数用于转换:

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

这样,上述两个数组就可以传递给MMULT函数,所以:

MMULT(N(B2:J14 & gt;1000),transpose(column(b2:j14)^0))

转换为:

MMULT({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:J14 & gt;1000),transpose(column(b2:j14)^0))&gt;0))

转换为:

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

转换为:

= SUM(N({ TRUE;真实;真实;真实;真实;真实;真实;真实;假的;真实;真实;假的;TRUE}))

转换为:

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

结果是:

11

如果不想使用数组公式,可以使用以下公式代替:

= SUM(N(MMULT(N(B2:J14 & gt;1000),row(index(a:a,1):index(a:a,columns(b2:j14)))^0)&gt;0))

风险提示:请谨慎添加本网站联系方式,注意识别。本网站的观点和行为不代表大盘站。如果您联系本网站,请检查联系信息。版权声明:本网站所有文章,除非另有说明或标注,均匿名投稿。如果本站内容侵犯了原作者的合法权益,可以联系站长处理。