有序集合指的是数组内的元素排列是有顺序的。
我举个小栗子。
={1,2,"星光",TRUE}
这是一个常量数组。包含了数值(1,2),文本(“星光”),逻辑值(TRUE),3种类型的元素。元素有序排列,第1个是1,第2个是2,第3个是星光,第4个是TRUE。如果我们要取出该数组内第3个元素的值,可以使用INDEX函数。
=INDEX({1,2,"星光",TRUE},3)
有序是数组运算的基础,如果数组元素是无序的,摊手,我们很难高效控制数组间元素的运算和准确获取运算后的完整结果。……
按照数据来源,数组又可以分为常量数组、内存数组和区域数组。
……
常量数组顾名思义是由常数构成的数组,典型标志是在首尾有一对大括号{}例如咱们上面举的例子:
={1,2,"星光",TRUE}
常量数组只能由常数组成,不能存在单元格引用、嵌套函数等形式。比如下面的常量数组中存放了sum函数,是规则绝对不允许的,钱再多权再大故宫开大G茅坑倒茅台都没用——暂时。
={2,sum(a2),1}
在Excel中输入上面的公式,系统会显示以下错误信息。
,它是某个公式的计算结果,然后又嵌套在另一个公式中继续参与运算。这话听起来有点儿绕,我举个例子。
如上图所示,我们需要计算人头售出总金额。H1单元格公式如下:
=SUMPRODUCT(B2:B5*C2:C5)B2:B5*C2:C5是指B2:B5的单价分别乘以C2:C5的数量,得到的是4个元素的数组{12;70;30;15}。
但这4个元素保存到哪儿去了呢?
显然没有保存到单元格中,而是保存到了电脑的内存里。它作为SUMPRODUCT函数的一个参数,继续参与公式运算,像这样的情况,我们称之为内存数组。
……
区域数组就比较简单,是在公式中对单元格区域的引用,比如公式:=A2:A10
单行数组又被称为水平数组,例如单元格区域A1:D1,常量数组{1,2,3,4}。
单列数组又被称为垂直数组,例如单元格区域A1:A4,常量数组{1;2;3;4}。
很明显,
单行数组由多列数据构成,比如A1:D1,有A/B/C/D四列;单列数组由多行数据构成,比如A1:A4,有第1/2/3/4四行。在常量数组中分号代表行(分号有两层,只有行才分层,所以它代表行),逗号代表列。
={1,2,3,4}*2
运算过程是数组中的每个元素都乘以2。1*2、2*2、3*3、4*2,结果为内存数组{2,4,6,8}。
再举一个实战的小案例。
如上图所示,由于B列的工资为文本值,直接SUM函数求和结果会返回0。B6单元格改用公式如下。
=SUMPRODUCT(B2:B5*1)
B2:B5*1,B2:B5是一个垂直数组,运算过程是B2:B5中的每一个元素均乘以1,通过数学运算将文本型数值转换为纯数值。此时生成一个内存数组{900;100;9999;99999},SUMPRODUCT再执行求和运算返回正确结果。如果把数组和单值运算比作男女关系,
单值就像皇帝,数组是它的后宫,数组内每一个人都要给皇帝生娃娃……所以说皇帝这个职业确实很——辛苦呐!
由于一维数组有两种形式,这种运算就又产生了两种情况。
一种是同方向一维数组之间的运算。比如垂直数组和垂直数组或者水平数组和水平数值间的运算。这种情况的数组运算是比较单纯的男女关系,两个数组内的每个元素按照先后顺序、一夫一妻制结婚。
举个例子。
以上图所示数据为例。计算商品售出总金额。公式如下:
=SUMPRODUCT(B2:B5*C2:C5)B2:B5是垂直数组,C2:C5也是垂直数组,其运算过程中是B2*C2、B3*C3、B4*C4、B5*C5……你看,是不是按照先后顺序、一夫一妻制的规则进行运算的?它的计算结果是4个元素的垂直数组。
{12;70;30;15}这规则似乎看起来让单身汪感到特别美好,但其实并不尽然。男多女少或者女多男少的情况了解一下?
比如公式:
=SUMPRODUCT(B2:B5*
C2:C4)B2:B5是
4个元素构成的垂直数组,C2:C4是3个元素构成的垂直数组;如果前者是男人,后者是女人,那就属于男多女少了。
{12;70;30;#N/A}同样的道理,公式:
=SUMPRODUCT(B2:B4*C2:C5)
B2和C2结婚,B3和C3结婚,B4和C4结婚。剩下一个女孩C5,但没有男孩了,怎么办呢?没办法,男女平等,也返回错误值补位吧。
{12;70;30;#N/A}……
总结一下。
同方向一维数组之间的运算,必须具有相同的元素数量,否则结果中会产生错误值进行补位,它的运算结果依然是同向的一维数组。……
另外一种情况是不同向的两个一维数值之间的运算,也就是垂直数组和水平数组之间的运算,这种情况男女关系比较复杂,身经百战的居委会大妈看了都得哭。我举个例子。
如上图所示的数据。B6单元格输入公式:
=SUMPRODUCT(A2:A4*B1:C1)
A2:A4是3个元素构成的垂直数组,B1:C1是2个元素构成的水平数组,它俩之间做乘法运算,结果返回了一个3行2列的二维内存数组:
A2先和B1运算,也就是A2*B1,然后再和C1做运算,也就是A2*C1。
然后轮到A3。A3先和B1运算,也就是A3*B1,然后再和C1做运算,也就是A3*C1。
最后轮到A4。A4先和B1运算,也就是A4*B1,然后再和C1做运算,也就是A4*C1。
……有朋友说,这不是一夫多妻制吗?同志,我说你是不是对一夫多妻有啥误解?你的意思是A2娶了两个老婆,B1和C1;新婚第2天,A2出门遇见了A3,不聊不知道一聊吓一跳,原来A3昨天也新婚了,也娶了两个老婆,这俩老婆也是B1和C1,你说这尴尬不尴尬?
垂直数组中每一个元素分别与水平数组的每一个元素一一运算,返回X行Y列的二维数组。
由多行多列元素构成的数组是二维数组,比如单元格区域B2:D4是一个3行3列的二维数组。一维数组和二维数组之间的运算是什么情况呢?
还是举个例子。
如上图所示数据为例,A1:B4是不同次数考试成绩的加权系数,D1:G4是该班成绩明细,如果需要计算所有人考试成绩加权系数后的总分,可以使用以下公式。
=SUMPRODUCT(B2:B4*E2:F4)
B2:B4是一维垂直数组,E2:F4是3行2列的二维数组,两者之间做乘法运算。根据有序原则,首先运算的是B2。
B2先和E2运算,B2*E2,然后再和F2运算,B2*F2。
B4先和E4运算,B4*E4,然后再和F4运算,B4*F4。
有些男同胞又蠢蠢欲动,以为这是盛世复兴的景象……那可就又未必了,什么是先来后到了解一下?
比如公式:
=SUMPRODUCT(B2:B5*E2:F4)
B2:B5是4行元素的垂直数组,E2:G4是三行两列元素的二维数组。前者比后者多了一行;当B2娶走了E2和F2,B3娶走了E3和F3,B4娶走了E4和F4……剩下一个B5,没有老婆可娶了,怎么办呢?——老办法,返回错误值补位吧。
{48,83;27.6,19.2;10.4,18.2;#N/A,#N/A}错误值是无法统计求和的,因此这条SUMPRODUCT函数最后会返回了错误值。
总结一下,一维数组和二维数组做运算的过程是
一维数组的每个元素和同方向二维数组的每个元素一一对应运算,最后结果返回一个二维数组。如果两个数组相同方向的元素数量不一致,会产生错误值补位。……
第4种情况是二维数组和二维数组之间的运算。
二维数组相互运算,要求两者具有完全相同的尺寸,也就是行数和列数都要相同。运算的过程是
将每个相同位置的元素两两对应,返回一个与它们尺寸一致的二维数组结果。
如上图所示,A1:D4是成绩表;需要汇总大于等于60分的成绩之和。
公式如下。
=SUMPRODUCT((B2:D4>=60)*B2:D4)
公式首先运算B2:D4>=60部分,B2:D4是3行3列的二维数组,60是单值,因此这是二维数组和单值做比较运算,数组的内每个元素分别和60分比大小,大于等于60返回TRUE,否则返回FALSE,结果返回一个3行3列的由逻辑值构成的二维内存数组。
{FALSE,FALSE,TRUE;FALSE,TRUE,TRUE;TRUE,TRUE,TRUE}将该数组映射到单元格中,如下图所示。
公式继续运算,将这个二维数组和B2:D4做乘法运算,这就属于两个二维数组之间的运算了。按照有序原则,系统会将两个数组相同位置的元素一一运算,是的,又回到一夫一妻制。A数组的第1个元素和B数组的第1个元素结婚,A数组的第2个元素和B数组的第2个元素结婚……直至两个数组的元素用完;如果两个数组元素不一样多,照例用错误值补位。
TRUE在数学运算中视为1,FALSE视为0,因此这一步返回内存数组如下。
{0,0,91;0,74,89;65,80,60}
最后SUMPRODUCT执行求和运算,返回结果459.
什么是数组公式?这个问题很有意思,非常有意思,在ExcelHome论坛,它引发了一次又一次口水战。打个广告,稍后回来,嘿嘿嘿。
如上图所示的数据,需要统计人头销售总金额,可以使用公式:
=SUMPRODUCT(B2:B5*C2:C5)也可以使用数组公式:
{=SUM(B2:B5*C2:C5)}
上面这个公式前后的大括号不是手工输入的,而是在公式编辑结束时,同时按下<Ctrl+Shift+Enter>组合键后系统自动产生的。
=SUM(B2:B5*C2:C5)
结果会怎么样呢?如果你不是Excel2021+MS365版本(
这个版本默认执行数组运算),SUM函数只会按照正常模式运算,也就是只运算每个数组的首个元素,返回B2*C2的结果。这就是数组三键的意义。
……
什么是数组公式?按照正常的思维逻辑,
执行了数组运算的就是数组公式,对不对?但微软公司说,不不不,只执行数组运算还不能算数组公式,做人得有仪式感,做函数也是一样的,什么是数组公式?
执行了数组运算,同时公式自身还得包括在大括号中的才算。微软这么说,也有一点道理。就像前面所说,虽然有的函数天生就默认执行数组运算,但绝大部分函数确实没有这个特性,它需要数组三键才能打开数组运算的开关。
于是问题就来了。比如说,下面这个公式……
=SUMPRODUCT(B2:B5*C2:C5)它默认执行了数组运算,它是不是数组公式?微软说不是,因为它没有包含在一对大括号中。
那好,我们使用数组三键给它加上大括号。
{=SUMPRODUCT(B2:B5*C2:C5)}这样算数组公式了吗?微软说,是的,这就符合我们制定的数组公式的概念标准了。
但这个公式和上面的公式两者的运算过程有什么区别吗?没有,没有任何区别…
于是口水就飞起来了。
甲:我软,你说你这是不是有病?
乙:我没病,我顶多有点仪式感。
甲:你这是典型的形式主义。
乙:请不要将仪式感和形式主义混为一谈。
甲:你就是有病。
乙:卧槽,产品是我家的,我说了算,我的地盘我做主,懂不?
甲:你霸权主义。
乙:嗯?
甲:你虚伪
乙:呵呵
甲:你心虚了。
乙:鸽吻!
甲:我去,你流氓,居然非礼我!
乙:gewengunok?
如果需要显示数组公式的全部元素呢?——可以使用区域数组公式。那么什么是区域数组公式?
在一个单元格中输入的公式被称为数组公式,在多个单元格中输入同一数组公式就被称为多单元格数组公式,也就是区域数组公式。区域数组公式可以有序返回结果数组中的每个元素。举个简单的例子(以后见面请尊称我举栗子大力星光上士)
如上图所示的表格,选中D2:D5单元格区域,在编辑栏编写公式=B2:B5*C2:C5,然后按数组三键结束公式输入,也就在D2:D5区域内输入了同一条数组公式,这就是区域数组公式。
所选择的单元格个数必须与公式最终返回的数组元素个数相同,如果所选区域单元格的个数大于公式最终返回的数组元素个数,多出部分将显示为错误值。老规矩,人头不够,错误值来凑。
比如选中D2:D7输入数组公式=B2:B5*C2:C5,D2:D7有6个房间,返回的内存数组有4个元素,多出的房间显示为错误值。但如果所选区域单元格的个数小于公式最终返回的数组元素个数,则结果会显示不完整,毕竟规则是先来后到,一人一个单元格。
除此之外,区域数组公式还有一个特点,它们作为一个整体,系统不允许单独更改其中一个单元格的公式。
={"ID","姓名","地址","电话"}
之后A:D列的数据就不能单独删除了,否则系统会弹出警告信息。
可以选择任意一个存在区域数据公式的单元格,然后按<Ctrl+G>快捷键调出定位对话框,依次单击【定位条件】→【当前数组】功能,如下图所示。
微信扫一扫加关注,Office自学网官方微信公众号,
专注分享软件安装和视频教程,让你一号在手,办公无忧!