Excel数组公式及运用

第一部分：了解数组公式在开始讲数组公式之前，我们先来认识几个必要的概念。

1、数组什么是数组？仁者见仁，智者见智。

我个人的感觉是：数组是具有某种联系的多个元素的组合。

某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。

当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。

放到Excel里，班级就相当于工作表，而学生就相当于工作表里的单元格数值。

所以，Excel里的数组，我还把它理解是为多个单元格数值的组合。

2、公式如果你在使用Excel，如果你说你还没听过“公式”这个名词，我只能说：“你太OUT了！”什么是公式？我的理解是：在Excel里，凡是以半角符号“=”开始的、具有计算功能的单元格内容就是所谓的Excel公式。

如：=SUM(B2:D2)，=B2+C2+D2这些都是公式。

3、数组公式数组公式是相对于普通公式而言的。

普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一个单元格，只返回一个结果。

而数组公式可以占用一个单元格，也可以占用多个单元格。

它对一组数或多组数进行多重计算，并返回一个或多个结果。

集合在教室外面的学生，老师把他们叫进教室。

老师说：“第一组第一桌的同学进教室。

”于是第一组第一桌的同学走进教室。

老师接着叫：“第一组第二桌的同学进教室。

”然后是第二桌的同学进教室。

老师再叫：“第一组第三桌的同学进教室。

”然后第三桌的同学走进教室。

接着是第四桌，第五桌……，就这样一个学生一个学生的叫，这就是普通公式的做法，学生回到座位，就像数值回到工作表的单元格里，一个座位叫一次，就像一个单元格输入一个公式。

如果老师说：“第一组的全部进教室。

”学生听到命令后，第一桌的同学走进去，然后是第二桌，第三桌……，老师不用再下第二个命令，这是数组公式的处理方法。

4、数组公式的标志在Excel中数组公式的显示是用大括号对“{}”来括住以区分普通Excel公式。

如图：（1）数组公式：（2）普通公式：输入数组公式：用Ctrl+Shift+Enter结束公式的输入。

特别提醒：这是最关键的，这相当于用户告诉Excel：“我不是一般人，爷我是数组公式，你得对我特别关照。

”于是，Excel明白了，不能用常规的逻辑来对待这位大爷。

当你按下三键后，Excel会自动给公式加上“{}”以和普通公式区别开来，不用用户输入“{}”，但如是是想在公式里直接表示一个数组，就需要输入“{}”来把数组的元素括起来。

如：=IF({1,0},D2:D8,C2:C8)这个公式里的数组{1,0}的括号就是用户自己输入的。

5、数组的维数“维数”是数组里的又一个重要概念。

数组有一维数组，二维数组，三维数组，四维数组……在公式里，我们更多接触到的只是一维数组和二维数组。

一维数组我们可以简单地看成是一行的单元格数据集合，比如A1:F1。

一维数组的各个元素间用英文的逗号“,”隔开(如果是单独的一列时，用英文分号“;”隔开）。

{1,2,3,4,5,6}，这就是一个有6个元素的一维数组，或者说，只有一行的数组。

数组的各个元素间用逗号“,”分隔。

如果想把这个数组输入到工作表的单元格里，同时选中同一行里相领的六个单元格，输入：={1,2,3,4,5,6}后，三键结束公式，你就可以看到这个一维数组被输入到工作表的单元格里了。

自己动手试一试。

二维数组可以看成是一个多行多列的单元各数据集合，也可以看成是多个一维数组的组合。

如单元格A1:D3，就是一个三行四列的二维数组。

我们可以把它看成是A1:D1、A2:D2与A3:D3这三个一维数组的组合。

二维数组里同行的元素间用逗号“,”分隔，不同的行用分号“;”分隔。

我们可以用上面的方法，在A1:D3区域输入数据，并引用地址，按F9来查看。

可以看到在数组里，换行的时候，元素间的分隔符是“;”，所以，要判断一个数组是几行几列的数组，只需要看里面的逗号和分号就知道了。

如果需要把数把数组返回到单元格区域里，首先得看数组是几行几列，然后再选择相应的单元格区域，输入数组，三键结束。

对了，是哪三键你还不要忘记了：Ctrl+Shift+Enter记住：（1）一维数组是单独的一行或一列。

二维数组是多行多列。

（2）数组里的元素，同一行内的各元素用英文逗号“,”分开，用英文分号“;”将各行分开。

（3）二维数组的元素按先行后列的顺序排列。

总是这样：{第一行的第一个,第一行的第二个,第一行的第三个……;第二行的第一个，第二行的第二个，第二行的第三个……;第三行的第一个……}第二部分：数组公式的初步认识在对数组公式有了一个简单的了解之后，这贴我们将通过一些简单的例子来进一步认识数组公式。

问题1：在D2:D4求出商品的销售金额。

现在你解决这个问题会用什么办法呢？我知道很小儿科，千万不要在心里骂我拿这种简单的问题来考你。

是的，很简单，在D2单元格输入公式“=B2*C2”，下拉公式即可。

在这里，D2:D4三个单元格输入了三个普通公式，分别返回了三个值在三个单元格里。

这就是老师在点学生进教室，第一组第一桌的同学进教室入座，第一组第二桌的同学进教室入座……我们试着用数组公式来解决这个问题，老师嗓子不好，让他叫一次我们就乖乖进教室去得了。

选中D2:D4输入公式“=B2:B4*C2:C4”，三键结束输入数组公式，即可得到同样的结果。

这就是一个多单元格的数组公式，多单元格数组公式是进行批量计算，可节省计算的时间，同时，它还有一个特点。

当你输入完数组公式后，请你尝试修改公式区域里其中一个单元格的公式，看看会有什么结果。

是的，你已经发现了，会弹出一个对话框，提醒你：不能修改数组的某一部分。

这就是多单元格数组公式的一个重要的特点：保证公式集合的完整性不被修改。

这可以防止用户在操作时无意间修改到表格的公式。

这是不是会安全得多？当然，如果你要修改公式的话，必须得选中公式所在的所有单元格。

问题2：在F1求出商品的销售总金额这一题如果你用普通公式又怎么解决呢？我想象中可能有两种方法：A、插入辅助列，先求出各商品的销售额，然后再求总和。

B、直接在F1输入公式“=SUM(B2*C2,B3*C3,B4*C4)”，这样看上去不错，可是，如果有100行数据，一千行号数据呢？先不考虑单元格能容纳多少字符的问题，就光输入公式，累也得把你累趴下，显然是行不通的。

这时候就需要用数组公式来完成了。

选中F1单元格，输入公式“=SUM(B2:B4*C2:C4)”，三键确认输入即可。

这是一个单个单元格的数组公式，B2:B4*C2:C4是两个一维数组相乘，返回一个新的一维数组，最后用SUM 函数对返回的数组进行了求和。

这里，用一个数组公式代替了多个公式的方式来完成了数据的计算。

做了这个问题，总结一下，什么时候会用到数组公式？是的，当运算中存在着一些只有通过复杂的中间运算过程才会等到结果的时候，就需要使用数组公式了。

这一贴的内容非常简单，记住几点：（1）三键输入数组公式。

（2）数组公式同时进行多个计算，可返回一个或多个结果。

（3）多单元格数组公式需选区多个单元格进行输入，多单元格数组公式具有保护公式的作用。

（4）数组公式可以完成复杂的中间运算得到最终想要的运算结果v第三部分：数组公式的计算学习继续，在对数组有了基本的认识后，这贴我们将通过一些例子来讲一讲数组公式是怎么计算的。

1、行列数相同数组的运算数组1+数组2，这是一个多单元格的数组公式，第一个数组的第一个元素与第二个数组的第一个元素相加，结果作为数组公式结果的第一个元素，然后第一个数组的第二个元素与第二个数组的第二个元素相加，结果作为数组公式结果的第二个元素，接着是第三个元素……直到第N个。

这是横向的一维数组的计算，原理同上。

这是二维数组与二维数组进行计算，生成一个新的二维数组的多单元格数组公式。

同样的计算过程，第一个数组的第一行的第一个元素与第二个数组的第一行的第一个元素相乘，结果为数组公式的结果的数组的第一行的第一个元素，接着是第二个，第三个……直到第N个。

规律很简单：两个同行同列的数组计算是对应元素间进行运算，并返回同样大小的数组。

正如穿鞋要穿合脚的才走得了路一样，在公式或函数中使用数组时，运算对象或参数的数组维数要匹配，否则计算会出错。

教室里，第一排的有8个同学，第二排有9个同学，老师说：“第一排和第二排的同学交换作业，互相检查。

”第二排的第9个同学和谁交换？这就是数组的不匹配。

数组不匹配时，工作就不能完成了。

你可以试着改一改数组的参数试试。

2、数组与单一的数据的运算这相当于在E42单元格输入公式=A42*$C$42，然后下拉复制公式实现。

等同于在B56输入公式“=B52+$B$54”，然后右拉复制公式实现。

等同于在C67单元格输入公式“=A60+$E$60”然后右拉下拉复制公式实现。

不难看出：一个数组与一个单一的数据进行运算，是将数组的每一元素均与那个单一数据进行计算，并返回同样大小的数组。

3、单列数组与单行数组的计算两个数组相加，查看结果是几行几列：在任意单元格输入公式“=A80:A83+B87:E87”，抹黑公式，按F9键，可看到公式的计算结果为数组“{110,210,310,410;120,220,320,420;130,230,330,430;140,240,340,440}”通看看分号与逗号，我们知道这是一个四行四列的数组，选择一个四行四列的单元格，输入公式“=A80:A83+B87:E87”，三键结束，可看到返回的结果为：相当于在E80输入公式“=$A80+B$87”右拉下拉复制公式的结果。

单列数组与单行数组的计算：A、计算结果返回一个多行列的数组；B、返回数组的行数同单列数组的行数相同、列数同单行数组的列数相同。

C、返回数组中第R行第C列的元素是单列数组的第R个元素和单行数组的第C个元素运算的结果。

4、行数（或列数）相同的单列（或单行）数组与多行多列数组的计算（1）单列数组的行数与多行多列数组的行数相同时：（2）单行数组的列数与多行多列数组的列数相同时：计算规律同单行单列的数组计算的规律大同小异：A、计算结果返回一个多行列的数组；B、返回数组的行、列数与多行多列数组的行列数相同；C、单列数组与多行多列数组计算时，返回的数组的第R行第C列的数据等于单列数组的第R行的数据与多行多列数组的第R行第C列的数据的计算结果；D、单行数组与多行多列数组计算时，返回的数组的第R行第C列的数据等于单行数组的第C列的数据与多行多列数组的第R行第C列的数据的计算结果。

＝＝＝＝＝＝＝留给你的思考题＝＝＝＝＝＝＝讲到这里，我们可以暂停一下进度。

课间休息，插播一段广告：你可以喝杯水，听听音乐，然后我们来看几个例子：图1：图2：图3：上面的三张图，第一个公式是我们前面讲的例子，第二个公式是在第一个公式的基础上对参与计算的数组区域进行了修改，但是，两个不同参数的公式，返回的结果却都是一样的。