excel表格强大而美妙的数组公式
发布时间:2023-12-15 16:22:55
Excel中有一类称作数组公式的公式,有点难以理解,但很强大,能够完成很多不可思议的任务。
然而,要想精通并熟练运用数组公式,需要大量的练习。本文只是简单地介绍数组公式,让感兴趣的朋友对其有所了解,为进一步的研究和应用打下基础。
为什么要学习和使用数组公式
既然数组公式比较复杂且难于理解,那为什么还要学习和使用数组公式呢?
使用数组公式,可以使Excel完成我们认为不可能的任务,或者说要使用VBA才能完成的任务,并且在有些情形下,数组公式可能是一个非常有效的解决方案。当然,好奇心也会驱使我们想要学习进阶去创建更高级的公式,并且在学会数组公式的基本使用后想要创建更有效的数组公式。在掌握数组公式后,在面对创建Excel解决方案时又多了一份新的武器。我想,这可能是想要学习数组公式的一些原因。
在某些情形下,除非使用VBA,使用数组公式可能是实现目的的唯一办法。在使用普通的公式时,我们可能需要辅助列或一些中间步骤,然而,数组公式可以提供一次性的解决方案,省掉中间步骤。当然,高级筛选、数据透视表、以及新增的一些数组函数能够解决使用数组公式实现的任务,但是当输入发生变化时它们不能自动更新或者有局限,然而数组公式能够立即更新,这是其一大优势。
我们可以使用数组公式来判断数据是否与指定区域中的数据相匹配,可以统计单元格区域中不重复值的数量,可以提取单元格区域中的不重复数据,将列数据转换为行数据,…,等等。
什么是数组公式
在认识数组公式之前,先看看通常对于下图1所示的工作表求总销售额的过程。
图1
正如图1中所看到的,要求这四种水果的总销售额,先使用公式求出每种水果的销售额,然后相加即可。总共使用了4个公式。其实,我们可以只使用一个公式来求总销售额,如图2所示。
图2
在单元格C7中输入公式:
=SUM(B2:B5*C2:C5)
然后同时按下Ctrl+Shift+Enter键完成公式输入。
这个公式就是数组公式,即有操作运算符,运算一组数据而不是单个数据,传递的结果也是一组数组,而最终的结果可能是单个的数据,也可能是一组数据。
注意,当我们按下Ctrl+Shift+Enter键完成输入后,Excel会自动在公式两侧添加上花括号{},无须手工输入它们。
数组公式原理
仍以上文所示的工作表为例,看看数组公式的计算过程,从而了解其运算原理。
首先,公式中的B2:B5与C2:C5分别被单元格数据替换成数组:
=SUM({5.8;1.2;1.1;3.5}*C2:C5)
=SUM({5.8;1.2;1.1;3.5}*{100;350;200;300})
然后,两个数组对应元素相乘得到:
=SUM({580;420;220;300})
最后,数组作为SUM函数的参数求和,得到最后的结果2270。
可以看到,数组公式是处理一组或一系列数据而不是单个数据的公式。它能够返回单个的值,如本例所示,也能够返回一组数据,如下面的例子。
选择一列中任意9个单元格,输入下面的数组公式:
=ROW(1:9)
结果如下图3所示。
图3
该公式产生一个数组{1;2;3;4;5;6;7;8;9},并将它们依次输入到单元格。
注意,输入完公式后,一定要记得按下Ctrl+Shift+Enter键。
本文中,凡是要求输入数组公式的,都是指在输入完公式内容后再按Ctrl+Shift+Enter键,这才完成了数组公式的输入。
再看一个例子,了解数组公式的逻辑运算。
如下图4所示的工作表,记录着各种产品由不同的销售人所售卖的数量。
图4
想要计算张三或者李四所销售的手机数量,可以使用数组公式:
=SUM((A2:A10=”手机“)*((B2:B10=”张三“)+(B2:B10=”李四“))*(C2:C10))
结果如下图5所示。
图5
这个公式创建了3个数组:
第一个数组是一系列的TRUE或FALSE值,是由单元格区域A2:A10中的数据与“手机”比较后的结果。注意,Excel会将“手机”的数量扩展到与所比较的单元格数量相同(下面的相同)。结果数组为:{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。
第二个数组由一系列的0或1组成,是由单元格B2:B10中的数据与“张三”和“李四”比较后的运算结果。其中,单元格区域B2:B10与“张三”比较生成一系列TRUE或FALSE值组成的数组:{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE},与“李四”比较也生成一系列TRUE或FALSE值组成的数组:{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}。将这两个数组相加,此时Excel将TRUE转换为1,FALSE转换为0,得到数组{1;0;1;1;1;1;1;1;0}。
第三个数组由单元格C2:C10中的数值组成,即{1200;200;300;120;220;50;600;100;800}。
然后,将这3个数组对应的元素相乘。与前面数组相加相同,在数组相乘时,Excel将TRUE和FALSE分别转换为1和0。因此,3个数组相乘的公式为:
{1;0;1;0;0;0;1;0;1}*{1;0;1;1;1;1;1;1;0}*{1200;200;300;120;220;50;600;100;800}
这3个数组相乘的结果也是一个数组,其每个元素为这3个数组对应元素相乘的结果,即:
{1200;0;300;0;0;0;600;0;0}
该数组作为SUM函数的参数得到最终的结果为2100(=1200+300+600),即张三和李四销售的手机数量。
详细的运算过程如下图6所示。
图6
可以看出:
数组公式中将乘法(*)用于逻辑与,即遵守与AND操作相同的规则;将加法(+)用于逻辑或,遵守与OR操作相同的规则。MOD运算模拟异或XOR操作。
在进行算术运算时,Excel会将TRUE和FALSE转换成数值1和0。
数组公式示例
下面列举两个简单的示例,更多的示例参见后面链接的内容。
示例1:计算及格的学生成绩的平均值
在图7所示的工作表中,求及格的学生成绩的平均值,使用数组公式:
=AVERAGE(IF(B2:B9>60,B2:B9,FALSE))
图7
这个数组公式使用IF函数来测试多个单元格。比较单元格区域B2:B9中的数值是否大于60,返回一个包含布尔值的数组{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},然后IF函数根据该数组中的值,如果为TRUE则返回B2:B9中的值,为FALSE则返回FALSE。扩展后的公式如下:
=AVERAGE(IF({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{89;92;78;56;88;58;95;55},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))
注意,在原公式最后的FALSE被扩展成一个与前面数组相匹配的适合大小的数组。
在IF函数测试完成后,得到下面的中间结果:
=AVERAGE({89;92;78;FALSE;88;FALSE;95;FALSE})
AVERAGE函数忽略布尔值(TRUE或FALSE),仅对数值求平均值。
示例2:计算排名前3的学生成绩之和
仍以图7所示的工作表为例,要求前3名的学生成绩之和。
可以使用数组公式:
=SUM(LARGE(B2:B9,ROW(1:3)))
其中,ROW(1:3)返回数组{1;2;3}。LARGE函数依次取单元格区域B2:B9中的最大值、第2大及第3大值,返回数组{95;92;89},然后将其作为SUM函数的参数求和。
示例3:计算数值单元格中各数字之和
在单元格A1中的数值为12345,要通过公式得出1+2+3+4+5的值。
可以使用数组公式:
=SUM(1*MID(A1,ROW(INDIRECT(“1:”& LEN(A1))),1))
LEN函数计算单元格A1中字符的长度,因此上面的公式变为:
=SUM(1*MID(A1,ROW(INDIRECT(“1:6”)),1))
INDIRECT函数返回对第1至6行的引用:
=SUM(1*MID(A1,ROW(1:6),1))
然后扩展成为:
=SUM(1*MID(A1,{1;2;3;4;5;6},1))
MID函数依次取单元格A1中的字符,每次1个:
=SUM(1*{“1”;”2”;”3”;”4”;”5”;”6”})
将1与数字文本相乘将其转换为数字:
=SUM({1;2;3;4;5;6})
最后,SUM函数对数字数组求和。


猜你喜欢
- 相信有了解的朋友都清楚,QQ音乐积分可以用来兑换腾讯视频85折优惠券、免费绿钻礼包、10.9元限时VIP、60元红包福利、100元大礼包、1
- 最新许多用户在使用电脑的时候总是收到Windows10的更新推送,提示离家重启电脑之类的提示。而我们都知道Win10是bug之王,每次更新完
- 小熊一键重装系统u盘怎么用?小熊一键重装系统u盘教程,现在的软件开发都让重装系统变的太简单了,直接在线重装就行了,可是殊不知有些在线重装会失
- win11正式版怎么u盘安装?很多用户在win11系统刚出后体验了预览版后,都在等待微软的推送升级通知,其实借助u盘工具,可以安装win11
- U盘容量由原来的4G变成了101M怎么办?不少人在使用U盘时都有遇到这问题。通常遇到这种问题只需下载个U盘低格工具处理就行了,具体的操作步骤
- Win10最新更新补丁kb5014699导致了热点打开无法上网的问题,让很多小伙伴非常烦恼,最简单的方法是卸载更新补丁,但是有些用户反应在控
- Win10系统的任务栏下方有一个小娜语音助手,我们安装了系统之后默认会显示在我们的任务栏上。有的用户不需要使用这个功能,觉得放在那里占用任务
- iCloud 云备份可以用于创建 iPhone、iPad 和 iPod touch 上的信息副本。在 iCloud 中备份设备数据后,可以轻
- 在带有蓝牙的笔记本电脑中,不少用户喜欢使用蓝牙来传输资料,只需配置好之后就可以进行传输了,无需使用数据线连接,相对来说还是比较方便的,但是确
- ppt怎么手绘蘑菇?ppt中想要画蘑菇,该怎么绘制黑白蘑菇图标呢?下面我们就来看看ppt蘑菇图标的画法,需要的朋友可以参考下ppt想要绘制像
- 常见端口关闭方法113端口木马的清除(仅适用于windows系统):这是一个基于irc聊天室控制的木马程序。1.首先使用netstat -a
- 有时候用excel 2003时,会出现乱码。Excel中文档乱码具体该如何解决呢?下面是由小编分享的excel 2003乱码的解决方法,以供
- windows7服务器上weblogic启动失败异常,很是疑惑,于是搜索整理下,接下来介绍解决方法感兴趣的朋友可以了解下环境: 操作系统:w
- 当你在使用Win11系统的时候,可能会遇到有些软件出现卡死的现象,那么只需要强制关闭就可以了,如果你不知道怎么更快强制关闭程序的话就来看下小
- xp自动更新问题,往往是发生在重装系统后的一些默认设置,针对这个问题,本文给出三种不错的解决方法,可以可以尝试操作下方法一鼠标左键点击桌面左
- 如何查看自己电脑的IP地址呢?最近有一个用户询问在WinXP系统电脑上怎么看自己的IP地址是多少,其实方法非常简单,今天就给大家分享两种查看
- Win10系统的任务栏有很多功能,其中固定的软件程序和一键快速打开功能可能是每个人都使用的。 但是,有时任务栏将无法修复软件图标。许多用户不
- 这篇文章主要介绍了苹果双系统如何切换开机选项?苹果mac和windows系统切换开机选项的方法的相关资料,需要的朋友可以参考下本文详细内容。
- 直接从微软服务器下载(包括通过应用商店下载或利用截获的直接下载链接通过其它下载工具下载)到的esd映像文件都是经过加密的。通过升级助手下载完
- wps软件一出现就受到了大批用户的关注和喜欢,因此wps软件成为了用户日常生活中的必备办公软件之一,在wps软件中用户可以没有太多限制的选择