excel图表统计至少在一列中满足条件的行数
发布时间:2023-07-11 14:24:23
在这篇文章中,探讨一种计算在至少一列中满足规定条件的行数的解决方案,示例工作表如下所示,其中详细列出了各个国家在不同年份废镍的出口水平。
假设我们希望确定2004年出口总额大于或等于1000的国家的数量,可以使用公式:
=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年的数据中至少有一个满足此标准的国家数量呢?由于数据较少,我们可以从工作表中清楚地标出满足条件的数据,如下所示。
显然,“标准的”COUNTIF(S)公式结构不能满足要求,因为我们必须确保不要重复计数。实际上,在这种情况下,大多数人倾向于使用SUMPRODUCT函数,即:
=SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)>0))
但是,如果选择的话,我们可以使用COUNTIFS函数构造解决方案,因为考虑到该函数相对于SUMPRODUCT函数的优势(通常,COUNTIFS函数引用整列的能力更有效),在某些情况下这可能是值得的。
回到我们刚才要解决的问题,实际上要考虑下列3种互斥情形:
1)2004年的数字> =1000,而2005年的数字<1000
2)2004年的数字是
3)2004年的数字> =1000,而2005年的数字> = 1000
然后,将每种情形统计的结果相加。转换成Excel公式为:
=COUNTIFS(B2:B14,”>=1000″,C2:C14,”>=1000″)+COUNTIFS(B2:B14,”>=1000″,C2:C14,”<1000″)+COUNTIFS(B2:B14,”
这个公式既不优雅也不简洁。但是,我们可以将其缩写为:
=SUM(COUNTIFS(B2:B14,{“>=”,”>=”,”
这样,成功地实现了基于COUNTIFS函数的解决方案来代替通常的SUMPRODUCT函数公式构造。
下面,考虑希望得出的结果涉及的列数不只是两列,甚至可能是多列的情况。例如,假设要确定从2004年到2012年每年至少有一个数字大于或等于1000的国家的数量。如下所示,我们可以在工作表中标出满足条件的数据,除了2个国家外,其他11个国家都满足条件。
此时,我们根本无法按照上述方法构造与SUMPRODUCT函数解决方案等效的COUNTIFS构造。使用SUMPRODUCT函数的公式:
=SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)+(D2:D14>=1000)+(E2:E14>=1000)+(F2:F14>=1000)+(G2:G14>=1000)+(H2:H14>=1000)+(I2:I14>=1000)+(J2:J14>=1000)>0))
可得出正确的结果11。然而,公式显得太笨拙了,如果考虑的列数不是9而是30,那会怎样!
幸运的是,由于示例中列区域是连续的,因此可以在单个表达式中查询整个区域(B2:J14),随后适当地操纵这个结果数组。
这个解决方案的数组公式如下:
=SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0))
公式中,比较区域中的每个元素是否大于或等于1000:
B2:J14>1000
结果是包含以下布尔值的数组:
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
使用N函数强制转换成数字:
N(B2:J14>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)
转换为:
TRANSPOSE({1,1,1,1,1,1,1,1,1})
由于必须确保由9个1组成的数组由9行组成,因此使用TRANSPOSE函数来转换:
{1;1;1;1;1;1;1;1;1}
这样,就可以将上述两个数组传递给MMULT函数,因此:
MMULT(N(B2:J14>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>1000),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;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}))
转换为:
=SUM({1;1;1;1;1;1;1;1;0;1;1;0;1})
结果为:
11
如果不想使用数组公式,可以使用下面的公式替换:
=SUM(N(MMULT(N(B2:J14>1000),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B2:J14)))^0)>0))
猜你喜欢
- 我们双击word文档的时候,有时会发现总是打不开,出现安全模式,那么这个时候应该怎么办呢?word出现安全模式打不开的解决方法其实很简单,接
- 在使用office 的 excel 或者是wps表格中制作各类统计二维表格时,在表头的位置我们往往需要一个斜线来划分项目,但是有了斜线我们的
- excel绘制的图形怎么填充文理?在excel中绘制的图形,为了更美观,我们可以填充颜色,图形等等,该怎么填充呢?下面我们来看看excel图
- 有用户反映在使用win10系统电脑过程中遇到了无法开机情况,而且提示0xc000000f的错误代码,这是怎么情况呢?一般是由于映像出现问题所
- 搭配 iPhone 12 系列使用的 MagSafe 充电器可以按照下面的方法检查当前运行的固件版本。苹果偶尔会通过 OTA 的方式向 Ma
- 首先安装好Beyond Compare,选中要比较的表格文件,右键,选择 比较 直接就能看到想要的比较结果 &nb
- Excel2007基础教程:通过搜索选择单元格选择单元格的另一种方式是使用Excel 的"开始"->"编
- 使用Word的自动保存功能,可以在断电或失机的情况下最大限度地减少损失。下面就为大家介绍Word2010怎么设置自动保存文档的方法,一起来看
- 下面小编为大家介绍将word文档转换为pdf文档方法,相信很多朋友都不是很清楚吧,其实方法很简单的,下面我们一起来看看吧pdf文档相对wor
- 在制作大量的Excel表格的时候,我们常常自己都可能搞忘记哪些数据是非常重要的,可能一个误操作就会导致一些自己辛苦的数据丢失了。所以,每当我
- 经常使用Word办公的伙伴,对文档进行编辑,修改,整理是不可少的步骤。下面教你word分栏怎么设置页码的小技巧,一起来学习下吧!1、Word
- 办公中,少不了使用word,这个是大家必备的软件,看似简单的软件,隐藏了很多学问和技巧,今天给大家分享在Word中常用的10个Ctrl+sh
- 如果不允许添加辅助列,单独用簇状柱形图或堆积柱形图无法完成,本文介绍一种不用辅助列在双坐标轴中并列显示两种子图表类型的方法,如图所示。 &n
- Win7旗舰版系统用户在升级到Win10专业版系统后发现设置不见了,想设置点什么不知道从何下手,相信很多小伙伴都有碰到这类的问题,下面小编告
- 我们在一些网站上阅读文章或资讯的时候总会看到文章中有些特定的词、句或图片带有超链接,点击以后就会跳到与这些特定的词、句、图片相关的页面中,这
- 很多朋友使用Word软件制作试卷等素材时都需要用到横线等图案,但是却不知如何在Word页面画出横线,下面小编就为大家介绍在word文档中怎么
- 既然都已经大手笔的购买了iPhone 12 手机,那就不要贪小便宜去省MagSafe的钱了,对吧?从网上购买的价格超便宜的山寨MagSafe
- 1、打开Word文档,点击“文件”-“打印”。2、在打印设置对话框,选择正确的打印机;在“缩放”选项中,确定每页的版数是“1版”(如果是“4
- 在公司上班的,特别是接触财务会计之类的朋友们一定随时做好安全工作,比如说:给文档加密,或者隐藏文字等。这样一来我们在打开这些加密文档的时候,
- word自动编号的优点是无需手动输入编号、添加和删除编号,但是在使用的过程中,经常会出现问题。 问题一编号错误,如:你要输入的编号