公式中的王者——数组公式

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。

数组公式示例

在完美Excel微信公众号(公众号名:excelperfect)中,有很多数组公式应用示例可供参考学习。下面列举3个简单的示例,帮助读者进一步理解数组公式原理。

示例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函数对数字数组求和。

结语

数组公式大大扩展了公式的能力,为Excel的应用开辟了一片新天地。然而,要想精通并熟练运用数组公式,需要大量深入的练习,但绝对值得学习。

(0)

相关推荐

  • 基础篇--为何不能用AND,OR替换*,+

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 我们以前提到过,*号和+好可以与逻辑判断函数AND和OR相互替换.咋数组公 ...

  • 公式中的组合拳-万金油公式

    前言 万金油公式是一套高能组合数组公式,应用了INDEX函数,MIN或SMALL函数,以及IF判断函数,ROW函数,可以返回一列数组中的不重复值或查找多个满足条件的值. 1列出不重复项 C2=INDE ...

  • 如何理解公式中的数组?

    Q:我知道数组公式很强大,很想学习,但有点想不明白公式中的数组,能够解释一下吗? A:数组公式确实非常强大,能够完成许多使用普通公式难以完成的任务.数组公式通常会生成中间数组,最终结果可能是一个值,也 ...

  • Excel从多个日期对应价格中查询产品最新价格的数组公式讲解

    Excel从多个日期对应价格中查询产品最新价格的数组公式讲解

  • 万金油公式在二维数组中的应用

    今天和小伙伴们学习下万金油公式在二维数组中的应用.对于万金油公式,相信常用excel函数的同学都很了解,我这里把它叫做筛选公式.如果不了解,也没有关系,可以慢慢积累. -01- 表格转换 1.将左表转 ...

  • Excel小技巧83:在Excel公式中插入双引号

    excelperfect 在Excel公式中,双引号用于指示文本字符串的开始和结束,例如: ='这是完美Excel公众号' 然而,假设需要在文本字符串中包括双号引,如何实现呢? 假如输入下面的公式: ...

  • Excel表格中求差函数公式怎么样使用

    在excel在使用中有时需要求两个数据的差,该怎么做呢?下面给大家分享Excel表格中求差函数公式的使用. 材料/工具 电脑,excel 方法 1 首先在电脑上找到Excel工作表. 2 双击点开ex ...

  • 数字9.99999999E+307在Excel公式中的妙用

    Excel可以识别的最大数字是9.99999999E+307,这也是Excel允许输入到单元格中的最大数.这个数字通常与LOOKUP函数和MATCH函数相结合,用来查找行或列中最后一个数字,或者用来查 ...

  • 学习理解Excel数组公式的2种方法

    对于初学者来说,数组公式也是够难理解的了,尤其是那些大型公式,看着头都大了!然而,如果我们能够在学习数组公式时,看到其计算过程,无疑会很有帮助. 方法1:使用F9键,查看数组公式的中间值 例如下图所示 ...

  • 如何理解Excel公式中的各种符号?

    Q:在Excel公式中,我们会使用各种符号连接数字.单元格或函数,从而获得想要的结果,例如,=SQRT((1234-234)/100)^2.能够总结一下这些符号的使用吗? A:下面是Excel公式中使 ...