Excel_数组的使用

Excel 数组的使用

数组就是单元的集合或是一组处理的值集合。能写一个数组公式，即输入一个单个的公式，他执行多个输入的操作并产生多个结果??每个结果显示在一个单元中。数组公式能看成是有多重数值的公式。和单值公式的不同之处在于他能产生一个以上的结果。一个数组公式能占用一个或多个单元。数组的元素可多达6500个。

7.12.1 了解数组

首先我们通过几个例子来说明数组是怎么工作的。我们能从图7-35中看到，在“B”列中的数据为销售量，在“C”列中的数据是销售单价，需求计算出每种产品的销售额和总的销售金额，一般的做法是计算出每种产品的销售额，然后再计算出总的销售额。不过如果我们改用数组，就能只键入一个公式来完成这些运算。

输入数组公式的步骤为：

选定要存入公式的单元格，在本例中我们选择“D4”单元格。输入公式=SUM(B2:B4*C2:C4)，但不要按下[Enter]键（输入公式的方法和输入普通的公式相同）。按下[Shift]+[Ctrl]+[Enter]键。我们就会看到在公式外面加上了一对大括号“{}”，如图7-36所示。

在单元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范围内的每一个单元格和“C2：C4”内相对应的单元格相乘，也就是把每个地区的销售量和销售单价相乘，相乘的结果共有3个数字，每个数字代表一个地区的销售额，而“SUM”函数将这些销售额相加，就得到了总的销售额。

下面我们再以使用数组计算3种产品的销售额为例，来说明怎么产生多个计算结果。其操作过程如下：

(1) 选择“D2：D4”单元格区域，该区域中的每个单元格保存的销售金额。如图7-37所示。

(2) 在“D2”单元格中输入公式“=B2:B4*C2:C4”（不按[Enter]键）按下[Shift]+[Ctrl]+[Enter]”键，我们就能从图7-38中看到执行后的结果。同时我们能看到“D2”到“D4”的格中都会出现用大括弧“{ }”框住的函数式，这表示“D2”到“D4”被当作一个单元格来处理，所以不能对“D2”到“D4”中的任一格作所有独立处理，必须针对整个数组来处理。

7.12.2 使用数组常数

我们也能在数组中使用常数值。这些值能放在数组公式中使用区域引用的地方。要在数据公式中使用数组常数，直接将该值输入到公式中并将他们放在括号里。例如，在图7-39中，就使用了数组常数进行计算。

常数数组能是一维的也能是二维的。一维数组能是垂直的也能是水平的。在一维水平数组中的元素用逗号分开。下面是个一维数组的例子。例如数组：{10,20,30,40,50}。在一维垂直数组中的元素用分号分开。在下面的例子是个6×1的数组，{100;200;300;400;500;600}。

对于二维数组，用逗号将一行内的元素分开，用分号将各行分开。下一个例子是“4 ×4”的数组（由4行4列组成）：{100,200,300,400;110, ……;130,230,330,440}。

注意：不能在数组公式中使用列出常数的方法列出单元引用、名称或公式。例如：

{2*3,3*3,4*3}因为列出了多个公式，是不可用的。{A1,B1,C1}因为列出多个引用，也是不可用的。不过能使用一个区域，例如{A1:C1}。

对于数组常量的内容，可由下列规则构成：

数组常量能是数字、文字、逻辑值或错误值。

数组常量中的数字，也能使用整数、小数或科学记数格式。

文字必须以双引号括住。

同一个数组常量中能含有不同类型的值。

数组常量中的值必须是常量，不能是公式。

数组常量不能含有货币符号、括号或百分比符号。

所输入的数组常量不得含有不同长度的行或列。

7.12.3 数组的编辑

数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能独立编辑。在编辑数组前，必须先选取整个数组。

选取数组的步骤为：

(1) 选取数组中的任一单元格。

(2) 在“编辑”菜单中选择“定位”命令或按下[F5]键，出现一个“定位”对话框。按下“定位条件”按钮，出现一个定位条件对话框，如图7-40所示。

选择“当前数组”选项，最后按下“确定”按钮，就能看到数组被选定了。

编辑数组的步骤为:

选定要编辑的数组，移到数据编辑栏上按[F2]键或单击左键，使代表数组的括号消失，之后就能编辑公式了。编辑完成后，按下[Shift] +[Ctrl] + [Enter]键。

若要删除数组，其步骤为：选定要删除的数组，按[Ctrl]+[Delete]或选择编辑菜单中的“清除”命令。

7.12.4 数组的扩充

在公式或函数中使用数组常量时，其他运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作需要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数相同，而列数也必须和含有最多列数对象的列数相同。

例如: = SUM({1,2,3}+{4,5,6}）内的第一个数组为1×3,得到的结果为1+4、2+5和3+6的和,也就是21。如果将公式写成= SUM({1,2,3}+4}）,则第二个数据并不是数组，而是个数值，为了要和第一个数组相加，Excel 会自动将数值扩充成 1 ×3 的数组。使用=SUM({1,2,3}+{4,4,4})做计算，得到的结果为1+4、2+4和3+4的和, 即18。

将数组公式输入单元格区域中时，所使用的维数应和这个公式计算所得数组维数相同。这样，Microsoft Excel 才能把计算所得的数组中的每一个数值放入数组区域的一个单元格内。

如果数组公式计算所得的数组比选定的数组区域还小，则Microsoft Excel会将这个数组扩展，以便将他填入整个数组区域内。例如：={1,2;3,4}*2扩充后的公式就会变为={1,2;3,4}*{2,2;2,2}，则相应的计算结果为“2,4,6,8”。再如：输入公式={1,2;3,4}*{2,3}扩充后的公式就会变为={1,2;3,4}*{2,3;2,3} ，则相应的计算结果为“2,6,6,12”。

如果Microsoft Excel 将一个数组扩展到能填入比该数组公式大的区域内，而没有扩大值可用的单元格内，这样就会出现#N/A错误值。例如：={1,2;3,4}={1,2,3} 扩充后的公式就会变为={1,2,#N/A;3,4,#N/A}*{1,2,#/A;1.2.#N/A} ，而相应的计算结果为“2,4,#N/A,4,6,#N/A”。

如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出目前工作表上。

则将追究法律责任。https://www.360docs.net/doc/e66973950.html,/1300305/306009

Excel中函数、公式大家都非常熟了，但“数组公式”你是否听过、用过？什么是数组公式呢？根据微软官方给出的解释，数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。

一、什么是数组公式？

直接看微软的解释，也许并不是很容易理解，根据个人的理解，让其更直白一点，可以这样简单理解，引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“{}”括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。

二、如何输入数组公式

既然数组公式是以“{}”括起来的，那是不是在编辑栏在公式的两端分别输入“{}”就可以了呢？答案是否定的，在Excel中要输入数组公式，必须以特定的方法来输入，算是告诉Excel，我们这里输入的是数组公式。

在某个单元格输入数组公式的方法如下：

1.在编辑栏输入完整的公式，并使编辑栏仍处在编辑状态；

2.按下Ctrl+Shift+Enter快捷键

经过以上两步操作以后，编辑栏会自动脱离编辑状态，并且选中单元格后，在编辑栏可以看到公式的两端有“{}”符号标记，而双击进入公式的编辑状态时，你会发现“{}”符号是不存在的。

三、数组公式有什么用？

这里做了一个类似微软官网上的例子，这里详细说明一下用法及好处。

以上面图片中的内容为例，假设我一共买了三支股票，其股份及买入价格分别如图中所示，现在我要计算我的总股本。

正常情况下我应该如何做？在B4输入“=B2*B3”，然后填充至D4单元格，这样B4、C4、D4就分别是每一支股票的股本了，然后在B5单元格再输入“=SUM(B4:D4)”，这样总股本就出来了。

上面的计算过程可以说一点问题没有，也绝对正确。但是试想一下，如果类似的数据有很多，如果不是计算我自己买的几支股票而是其他类似情况的数据处理的时候，采用上面的方法时，其工作量可想而知了。

我们再回到第一幅图中的公式“{=SUM(B2:D2*B3*D3}”，此公式便是一个典型的数组公式的应用，此公式的作用就是计算B2*B3、C2*C3以及D2*D3的和。而B2: D2*B3*D3便是一个数组，其中包含三个元素，各元素的值就分别是各项的乘积。

为了更好的验证数组说法，分别在B4、C4、D4单元格中分别输入=B2*B3、=C 2*C3、=D2*D3，B7、C7、D7单元格中全部输入=B2:D2*B3:D3，结果如下图所示。

从上面的图中可以看到，第4行和第7行的计算结果是一样的，这就是数组的效果，数组会根据当前单元格所在位置自动取数组中对应序列的数值，如果将“=B2:D2*B3:D 3”算式输入到其他的列中（非B、C、D列），这时你会看到“#VALUE!”的错误数值结果，因为在其他列时，Excel无法判断该取数组中的哪一个数值。

如果将第7行中的算式外面加上SUM，你会发现结果仍然一样，因为默认情况下，数组算式只取对应序列的值，再加上SUM也只是对应的值，如果使用数组公式，便是告诉Excel计算数组中所有数值的和，也就是单元格B5中的结果。

三、数组公式应用进阶

数组公式最典型的应用应该是使用SUM替代SUMIF，虽然SUMIF很好用，但在Office 2007之前，也就是SUMIFS函数出现之前，如果想利用SUMIF进行一次多重条件判断的求和计算是很难实现的。

为了更便于理解，这里再用上面的例子进行一个比较简单的运算，上面只是列出了三支股票，如果我同时购买了多支股票，现在想知道这些股票当中，股价小于5元的股票有几支（这一功能可以使用Countif函数来实现，这里为了让数组公式更便于理解，所以使用SUM、IF相结合的数组公式来实现。

在B10单元格输入公式“=SUM(IF(B3:D3<5,1))”，按钮按下Ctrl+Shift+Ente r组合键，使公式变成数组公式“{=SUM(IF(B3:D3<5,1))}”。

从上图中可以看到，计算结果为2，计算正确。如果对此怀疑，可以扩大数据区域，

从而更容易理解。

下面说一下公式的整个运算过程。

1.IF(B3:D3<5,1)，计算B3:D3区域内数值小于5的个数，因为使用IF判断，

数值小于5时，取值1，所以该公式计算的结果是1，false，1，然后SUM进行数组求

和也就是1+false+1，所以结果为2。SUM在求值时会自动忽略False，我们也可以把False直接当作0来处理。或者将公式改成IF(B3:D3<5,1,0)，这样计算的结果就是1，0，1了。

如果要统计股价低于5的股价和要怎么办呢？将上述公司修改成“{=SUM(IF(B3:

D3<5,B3:D5))}”即可，因为数组公式是一一对应的，也就是说IF条件满足时就取对应

的数值，所以此公式的计算结果为4.04，false，4.43，最后的结果是4.04+false+4.

43=8.47。

不知道看完了上面这些，大家对数组公式是否有所了解了，更进阶的应用，且听下

回分解。

上次写了篇《Excel数组公式从入门到精通之入门篇》，不觉已十多天过去了，今天补上“精通篇”。当然说“精通”可能有点过了，但是希望大家通过这两篇博文能够真正认识“数组公式”，并且在工作中使用数组公式帮我们解决实际问题。

Excel数组公式从入门到精通之精通篇

一、课程回忆

什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里重点提醒的一点就是，如果要使用数组公式，在编辑栏输入完公式以后一定要按下“Ctrl+Shift +Enter”组合键，使编辑栏的公式处在“{}”之中。

二、数组公式继续深入

印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例子真是太实用了。具体细节记不太清楚了，大致意思就是使用函数计算1到100的和。这里同样以此为例。

1.求1到100的和

在往下看之前，大家想一下，如果让你来处理该如何来处理呢？只用一个函数解决1到1 00的和，当然也可以是1000、10000甚至更多。讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进一步的了解数组公式的用法。

解答：{=SUM(ROW(1:100))}

问题分析：求1到100的和，答案是5050（小学生都知道^-^），但Excel必须是你告诉了它正确的方法，它才能知道。计算从1到100的和，实际上就是计算1+2+3+4+……+98+99+100，好了，答案出来了，在编辑栏中输入“＝sum(1+2+3+4+……+98+99+10 0)”。相信聪明的一定对此答案不满意，虽然能得到正确的结果，但很明显是“错误”的方法。

要得到1到100的正确数列，最简单的方法就是使用Row()或是Column()函数，由于个人习惯，我比较习惯于Row()，所以这里以Row()函数为例。

熟悉Row()函数：在A1单元格中输入“＝Row()”，使用填充柄填充至A5，看到什么结果？是不是每一个单元格中值就是其对应的行数。

惊喜：Row()表示单前行，如果使用Row(1:100)就表示一个数组，其中包含的便是第一到第一百行的行号，即1、2、3、……、98、99、100这些数值，现在我们就把这个数组应用到公式中。

在工作表的任意一个单元格中输入“=sum(Row(1:100))”，然后按Ctrl+Shift+Enter 组合键，你会惊喜的发现，我们要的结果出现了。

2.在Excel2003中享受“SUMIFS”

SUMIF函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Offi ce 2007开始，引入了SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Offic e 2003怎么办呢？其实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。

如下图所示，某教师有一张任教的几个班级的学生成绩表。

任务：统计出“一班”、“二班”共计多少人？

此题要如何解决？SUMIF用两次？或是COUNT用两次？这里还是演示数组公式的用法，所以先用SUM和IF组合的形式。

在任一单元格中输入“=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))”公式按下回车键，是不是发现结果是“#VALUE！”，再次进入编辑栏然后按下“Ctrl+Shift+Enter”快捷键，是不是发现正确的结果出来了？

这里再次解释一下这个公式“{=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1, 0))}”，外侧SUM没什么好用的了，就是求（）内各数的和。中间的“IF((A2:A12="一班")+ (A2:A12="二班"),1,0))”的运算过程是这样的，判断A2:A12区域内单元格的值是否是“一班”，如果是则结果为1，则此公式计算的结果依次是“1、0、0、1、0、0、1、0、0、1、0”，因为第一个条件为真，第二个条件肯定就不为真了，因为一个单元格不可能同时等于“一班”和“二班”，所以第一个数组就是“1、0、0、1、0、0、1、0、0、1、0”。这时再判断A2:A12区域内单元格的值是否是“二班”，如果是结果则为1，否则为0，所以这个数组条件计算的结果就是“0、1、0、0、0、1、0、1、0、0、0”，中间的加号就是将这两个数组相加，也就是说最终的数组为“1、1、0、1、0、1、1、1、0、1、0”，然后使用SUM求和，结果就为7了。

从上面的图中标注可以看出，所以的公式我全部使用了数组（A2:A12这就是一个数组），并且上图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替代，之所以全部列出，还是希望大家更好的理解一下数组。

在Excel中，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格所对应的一个值（数组中的一个元素），放在数组公式中使用时，通常整个数组元素都会参与运算。

三、数组公式精通

这里是一个实际工作中的例子，只是我稍微变化了一下，还是SUM应用的例子。

需求：如下图所示，现在要统计员工张三在1号加工所有机器的“实绩”，也就是说在右侧的数据中先过滤日期为1，然后再过滤人员为张三的数据，最后统计实绩的结果。如果使用一个公式完成这一需求，你能想到吗？当然SUMIFS是除外的，因为SUMIFS是Office 2007以

后的产物。

答案：在上图所示的C2单元格中输入“=SUM((E2:E21=A2)*(G2:G21=B2)*(H2:H 21))”公式，然后按下“Ctrl+Shift+Enter”组合键，你会发现想要的结果已经出现了。数组公式就是这么简单，解决问题也是这么简单。

这次用的公式可以看出，比之前用的公式还要简单，连IF都不要了，实际上这里的“＝”符号就是起到了一个类似IF的效果。

这里再说明一下公式的执行过程，公式中E2:E21表示数组区域，这个相信已经不需要再说明了，放到数组公式中就是依次取数组中的各个数值，也就是依次取日期中的值。E2:E21 =A2，实际上就是拿日期中的每一个值依次与A2中的日期进行比对，如果相等则结果为True，即1，如果不相等则为False即为0。到了这里也许你有一点明白了，如果第一不相等，则后面的无需再继续下去了，因为公式里用的全部是“*”乘积符号，任何数乘0等于0。如果此项符合再继续判断G2:G21区域，也就是用姓名依次比对，如果和B2中的姓名相同，则为Ture，即1，如果为False，即0，继续下一个回合。如果此项也为Ture，很明显前面两项的结果为1* 1＝1，再乘以H2:H21数组中对应的数字，即符合条件的“实绩”，以第一个符合条件的第一条记录为例，在数组公式运行的第一个回合为SUM(1*1*234)，结果当然为234了，然后再依次完成整个数组的运算，我们最终的目的就达到了。

数组公式非常有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。但大家记住数组中的数据是一一对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。

希望大家能够慢慢的理解、贯通。