Excel数组公式

目录

  1. 什么是数组
  2. 数组公式
  3. 数组公式的特性——对应数据顺序运算
  4. 数组公式特性——数组元素坐标的运用

Excel数组公式(上)

一、   什么是数组

数组可以简单理解为多个数的组合,例如:数字“1”就是一个数,但是多个1放在一起就成了数组{1,1,1,1,1……}。在这样的一个数组中,每个元素都是1。通常情况下,会用从0开始的整数对数组中的每个元素进行编号。例如数组{1,2,3,4,5}中,编号为0的元素是“1”,编号为1的元素是“2”。

对应在Excel工作表中,可以简单地把多个单元格的组合(单元格区域)视为数组,而每个单元格中的内容又不一定必须是数字,因此,数组中的元素可以不是数字。

二、   数组公式

如上图,一般要计算a,b两个商品的总金额时,我们会构建这样一个表格,先分别计算各个商品的金额再求和。如D2单元格的公式就表示a商品的数量×单价。

在数据量小的时候,这样做是毫无问题的,但是假如一张表格的数据太多(商品很多,有很多行),甚至有很多这样的表格放在一个Excel工作薄里时,由于设置了太多的计算公式,那么该Excel工作薄会占用更多的内存,打开或移动复制都会比较慢,稍微改一个数字电脑就要计算一会。而且一不小心就会删除或改动其中某一行的公式导致最终合计错误。为了解决这些问题,就可以使用数组公式。

具体步骤是:先同时选定D2:D3单元格,然后输入公式“=B2:B3*C2:C3”,最后同时按下三个键:Ctrl+Shift+Enter,如下图:

此时,D2和D3单元格的公式均为“{=B2:B3*C2:C3}”,并且如果你想单独删除或改动D2单元格的公式是不能成功的,系统会提示错误。

数组公式的特点是,系统只认为其是一个公式,而不是每行一个公式,因此必须选中全部实用该数组公式的单元格才能修改。同时因为只是一个公式,表格在计算时会更快,表格的内存占用量也会更少。一个实用的例子就是公司使用的《资金计划表》,其中有大量公式均使用数组公式。

需要注意的是:数组公式虽然可以理解为是把以前公式中的单个数字(单元格)替换为一个数组(区域),然后加上了大括号“{}”,但是该大括号直接输入是无效的,必须通过按下Ctrl+Shift+Enter添加。

数组公式其实是把各个数组中对应元素分别进行运算,然后得到一个新的数组。上列中,数组{5,4}(区域B2:B3)与{1,2}(区域C2:C3)的对应元素进行乘法运算,得到新的数组{5,8}(区域D2:D3)。因此,在一些本来就使用单元格区域的函数时,就可以直接利用这样的结果。

如下图,在计算合计金额时我们不再需要挨个商品计算金额在求和,只需输入下面的公式即可。

因为SUM函数其实对数组(平时仅仅认为是选定的单元格区域)求和,而数组公式计算的结果就是一个数组,于是该公式就等价于分别对应相乘后再把各个金额汇总到一起。

此外,数组公式在使用时,可以是多维数组,即单元格区域可以包括多行多列。大部分的Execl函数均支持数组公式的运用。

Excel数组公式(中)

通过前面的介绍大家对数组公式及其特殊的输入方式应该有了一个初步的概念,他的主要特点是:

  1. 内存占用小、计算速度快
  2. 可以防止公式被误删
  3. 简单高效

在Excel中,几乎所有的函数都可以用数组来代替单元格,并通过Ctrl+Shift+Enter的形式转变为数组公式,面对这么好的公式,下面主要针对它的特性结合一些函数进行举例说明。

三、    数组公式特性——对应数据顺序运算

1、什么是对应数据顺序运算?

数组{5,4,3,2,1}与数组{1,2,3,4,5}是如何对应的呢?根据每个人的习惯不同可能有多种顺序:从左至右、从右至左等等,但是在Excel表中是从左至右的顺序。这一点很重要,因为当使用数组公式时,就会按此顺序进行。例如将两数组相乘,则按从左到右的顺序是5×1,4×2,3×3,2×4,1×5,得到的结果是一个新的数组,即{5,8,9,8,5}。

如果是选择单元格区域作为数组,顺序则是从左至右对应,从上至下对应。对应位置的元素进行运算。

2、数组长度不同时如何对应运算?

如果{5,4,3,2,1}×{1,2,3,4}得到的结果是否为{5,8,9,8,0},还是其他?

大家可以在Excel中试一下,实际结果是{5,8,9,8,#N/A}(如下图单元格H18),在从左至右的第5个位置上由于第二个数组没有内容,因此会出现错误代码(Excel中用#N/A表示“不适用”的错误,英文为:Not applicable)。

顺便说一下,出现错误代码并非是坏事,理解不同错误代码的含义可以快速查清错误原因,甚至还可以针对不同的错误代码设置函数公式,对数据统计分析带来极大的方便。

3、数组公式结果的赋值问题

延续上面的例子,当两个数组长度不同时,根据图中的方式会出现一个错误代码。那么下图的方式呢,请注意图中选择进行公式设置的单元格区域是不同的

这时将不会出现错误代码。

这里涉及到的一个问题是,数组公式的值如何反应到单元格中。就如第一部分所说,数组公式在输入时是选择一系列要执行相同公式的单元格,并且输入后的数组公式其实是一个公式,无法单独删除某个单元格内的公式。这意味着,在输入公式之前选择的单元格数量实质是要呈现数组公式计算出的结果(一个新数组)中数组元素的数量。本例中只选择了4个单元格,那么就只会呈现出计算结果{5,8,9,8,#N/A}的前4个元素,也就是按顺序将结果数组的元素分别赋值到所选单元格。

但是,若其中一个数组只有一个元素,或者是数组乘以某一个数,则不会出现错误。相当于把数组中的元素都分别乘以该数后得到一个新的数组,如下图:

4、多维数组对应数据的顺序运算——99乘法表。

前面数组公式涉及的数组都是基本的一维数组,前面的例子中在通过选择单元格区域来构建数组时,通常都仅仅选择了“列”区域,在实际使用时还可以选择“行”区域。甚至还可以选择多行、多列的区域,当选择多行多列时构建的数组就叫“多维数组”,或称为“矩阵”。

多维数组的对应顺序依然遵行从左至右,从上至下,并且多维数组的运算结果依然是一个多维数组。有个最直观的理解多维数组的对应关系的例子是99乘法表。如下图:

在B2:J10单元格区域内设置了一个数组公式:A2:A10*B1:J1。这个列子中,使用了一行的数据乘以一列的数据进行运算,最后得到了一个与“乘数”相同的行数和列数的矩阵。可以理解为用列数组中每个数据分别乘以行数组中的左边第1个数据得到第1列,然后再乘以行数组第2个数据得到第二列……依次类推。

小结:

在进行数组运算时,是数组间对应元素分别进行运算后得到一个新的数组。该新数组的行、列与参与运算的数组最大行、列相同。

在数组与单个元素(数字)进行运算时,得到的结果数组与原数组形式一致,新数组各元素为原数组每个元素分别于单个元素(数字)的元素结果。

Excel数组公式(下)

通过第二部分99乘法表的例子,我们从一行、一列就得到了一个“矩阵”,在这个“矩阵”里,第一个元素(B2)是列数组编号为1的元素“1”与行数组编号为1的元素“1”相乘的结果。在“矩阵”中B2的坐标为(1,1),表示第1行,第1列,同理C2的坐标为(1,2),表示第1行,第2列。在很多函数嵌套的数组公式里,对坐标的运用是非常具有实用价值的。

四、    数组公式特性——数组元素坐标的运用

当我们要做一项成本预测的工作时,会有涉及很多的人工、材料和费用,并且又会涉及不同的月份,如果要把所有的内容放在一起,这将是非常大的一张表格。如下面的简图:

表格数据的关系是:具体的费用等于单位标准成本×每个月对应的工作量,现在需要计算每个月费用小计是多少。当然,可以在每一行分别设置公式(如在I6单元格输入“=C4*C6+D4*D6+E4*E6+F4*F6+G4*G6+H4*H6”),但是当表格数据很多(比如几十种人工费、上百种材料费、几十种其他费用)时,这样输入公式无疑太累,并且还会出现计算缓慢的问题。每一次打开或关闭表格、稍微修改一个表格的数据就会等待很久。这时最佳的选择应当是使用数组公式。

首先,需要梳理一下需要做什么,然后选择适当的函数来使用数组公式。

这个例子中需要把每个月各个具体费用的工作量乘以其对应的单位标准成本,然后在把每个月的所有费用求和。如果只针对2017年11月这一行的话,可以简单使用SUM函数,就像第一部分所介绍的。在I6单元格输入公式“=SUM(C4:H4*C6:H6)”,然后按下Ctrl+Shift+Enter就可以了。这也是在第二部分重点介绍的数组对应数据顺序运算的内容。但是剩下的其他月份怎么办呢?就只有往下拖动(复制)这个公式,拖动(复制)之前需要注意锁定对单位标准成本行的引用,公式改为这样“{=SUM(C$4:H$4*C6:H6)}”。但是这样虽然每一行的公式输入简单了不少,但是一列下来公式可能还是很多。最简单的方式就是使用一个数组公式来解决每一行取值的问题,具体怎么实现呢?

借鉴第二部分的列子,如果使用“{=C6:H13*C4:H4}”这样的数组公式,会得到一个矩阵,其结果就是每个月的工作量与对应单位标准成本的积,如下图:

在这个矩阵的基础上,再把每一行的数据求和填入对应的费用小计行里就可以了。可以把这些步骤都用一个数组公式来体现吗?选择所有费用小计行(蓝色部分),然后输入数组公式“{=SUM(INDEX(C6:H13*C4:H4,ROW()-5,))}”就可以了,如下图:

下面花一点时间解释一下这个多重嵌套的数组公式,这涉及到前面提到的数组元素的坐标问题。公式由里至外如下:

ROW()函数得到的结果是所在行的行号,比如在I6单元格,结果就是6,在I13单元格结果就是13。

INDEX函数有两种用法,这里使用了数组形式:INDEX(区域或数组,行号,列号),本例中列号为空,表示取一行的所有数据。其结果是由这一行所有数据组成的新数组。也就是从{C6:H13*C4:H4}形成的矩阵中取出某一行,矩阵的第一行的行号是1,因此在使用了ROW()-5来表示矩阵的行号。于是I6单元格就会取{C6:H13*C4:H4}矩阵的第一行(6-5=1){9500,8075,4750,7600,26,15},I13单元格就会取第8行(13-5=8){12200,10370,4500,7200,36.4,22.5}。

SUM函数就简单了,针对取出后的一行数据求和,于是就得出了图中所示的结果。这样就完美解决了所有问题。

虽然表格的公式弄完了,但是实际运用中如果表格的费用和月份太多(成百上千行、列),要想查找某一月某一种费用的金额是多少就比较麻烦了。这个时候可以使用MATCH函数与INDEX函数组合的数组公式来实现。如下图,期望在白色单元格里输入年、月以及费用名称,在蓝色单元格就会显示出其对应的费用金额是多少。

在E19单元格里的数组公式是这样的“{=INDEX(C6:H13*C4:H4,MATCH(C18&E18,A6:A13&B6:B14,0),MATCH(C19,C3:H3,0))}”

INDEX函数仍然是数组形式:INDEX(区域或数组,行号,列号);当行号和列号都不为空时,就会得到该区域或数组中的行号和列号交叉点的数据。可以理解为行号和列号就是该元素在数组中的坐标。

MATCH函数的格式是:MATCH(要搜索的值,要搜索的区域,查找方式);MACTH函数得到的结果是要搜索的值在要搜索的区域中排第几的位置,0表示精确查找。

例如:MATCH(C19,C3:H3,0)的结果是3,因为“材料费1”在{C3:H3}中从左至右是第三个元素。而MATCH(C18&E18,A6:A13&B6:B14,0)的结果则是1,“&”是链接符,可以把两个单元格的内容组合在一起形成一个新的数据,C18&E18的结果就是“201711”,当“&”链接两个区域时,在数组公式里就是把两个区域对应数据拼接在一起形成一个新的数组,如A6:A13&B6:B14。这样其实是实现了多条件筛选。

   整个公式的效果就是在{C6:H13*C4:H4}矩阵中取第1行与第3列交叉位置的元素,也就是4750。

总结:

通过整个三部分的介绍,简单地介绍了数组公式的特点,根据其对应数据顺序预算的特性,加上其数组中元素的坐标,实现了海量数据的复杂计算和数据筛选。而这也是数组公式化繁为简、提高效率的奇妙作用。


 本文2018年先发表于《诚业报》和微信订阅号,订阅号内容将陆续迁移到网站。

Leave a Reply

您的电子邮箱地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

希望和绝望

最新文章

归档
分类

打赏

可长按保存图片至本地后进行二维码识别。

若在微信内打开,也可以通过“跳转至公账号打赏“。

若想获取内容推送,请留下电子邮件。

(需要邮件验证确定)

内容推送订阅