我与数组公式有个约会之相识

原文地址：我与数组公式有个约会之相识 作者：XTiger

前言：这个约会很偶然因为想报的班都满员了，又不想错过培训学习的机会就报了数组班（以前没接触数组还自认为EXCEL还过得去）但听完了课看到作业我完全蒙了无法下手，才发现自已原来真的很差很差。这个发现让我更加用心的再次听课，更加努力，希望能得到一次质的飞跃。

概念解析：

一、   数组：数组，就是元素的集合，按行、列进行排列。单行或单列的数组是一维数组，多行多列（含2行2列）的数组是二维数组。
数组公式，仅仅是对按了ctrl+shift+enter结束公式的编辑方式的一种称呼，与数组完全是两码事。（这是在论坛中借用的一句话）

1.      引用数组：对单元格区域的值进行引用（向量即有方向的量）

1.1   水平引用 A1：A5

1.2   垂直引用 A1：E5

1.3   二维引用 A1：E5

（transpose公式解析：transpose是将垂直引用转置为水平引用，例=transposn(A1:A5)ààA1:E1）

2.      常量数组 

2.1   水平数组：｛1,2,3,4,5,6,7,8,9 ……,N｝英文逗号区分。

2.2   垂直数组：｛1;2;3;4;5;6;7;8;9……;N｝英文分号区分。

2.3   二维数组：｛1,2;3,4;5,6;7,8;9……;N｝

3.      内存数组由公式返回的值（向量即有方向的量）

3.1   垂直向量：ROW（1:5）

3.2   水平向量：COLUMN（A:E）或=TRANSPOSE（ROW（1：5））

3.3   二维数组：TRANSPOSE（ROW（1：3））+3*（ROW（1：3）-1）

                 à公式解析ROW（1：3）={1;2;3}

                           TRANSPOSE(ROW(1:3))={1,2,3}

                           ROW(1:3)-1={0;1;2}

                           3*(ROW(1:3)-1)={0;3;6}

                           {1,2,3}+{0;3;6}={1,2,3;4,5,6;7,8,9;}

二、数组运算

单值运算  A1+1 à F(X)

数值运算  A1+｛1;2;3;｝àF{X1;X2;X3}

当我们的数值运算中操作数为数组时，即数组运算。当函数的参数为数组时，也进行数组运算。

但我们要区分，不是参数为数组形式时就是数组运算，如SUM()函数，参数本来就可以引用数组的方式，但这个不是数组运算。

当我们将函数中的其一参数改为数组时就进行数组运算。例VLOOKUP()函数，我们分别将第一参数或第三参数抽象为数组时就时行数组运算。

二、   数组公式

    数组公式就是包含数组运算的公式

 区别：数组公式输入完后须按CTRL+SHIFT+ENTER结束。

  如公式=SUM（2+ROW（1：3）），当录入结束后只按ENTER，那么结果为3；而当按CTRL+SHIFT+ENTER结束时，返回的结果为12。

 

数组公式有两种分类：

  单单元格公式：数组公式应用单个单元格，一般只返回一个运算结果。

  多单元格公式：数组公式应用多个单元格，一般返回多个运算结果，对应相应的单元格内

三、   运行机制

    代数解释

     自变量：X      返回值：F（X）

    将自变量改为一个数组，那么上面的对应关系就会变成如下：

     自变量：｛X1;X;2;X3;……;XN｝    返回值：｛F(X1);｝

    在时域上就是一个循环，我们可用每个节拍来考虑，

    在空间上就是一个并行处理，对于除SUM之外的函数外基本上一一对应的

并行处理

 SUBSTITUTE(TEXT,X,NEW TEXT，F(X))

 A1234Z，1，“”àA234Z

 当把X的参数修改为｛1；2；3；4｝后，结果是不是会等于AZ呢？

 经过测试，SUBSTITUTE（）函数的第二参数并无法支持数组方式的参数，所以上面的｛1；2；3；4｝只会被处理为1,那么计算结果仍然是一样的。

 

四、   参数驱动

驱动参数就是函数里的必要参数值。

函数的参数或运算符的操作数从单值升级为数组，那么就得到相应的数组返回值形式。

就是数组公式是由某个参数或运算符数组化而发生的公式性质的变化。

 

例：比如说=COUNTIF（A1：A5，A）这样的下拉公式，我们可以直接将第二参数数组化，从而得到数组公式

=COUNTIF（A1:A5，B1：B5），也就是说，当我们需要构造数组公式时，可以从某个参数数组化开始，让参数驱动公式，升级为数组的运算。

 

五、   实例演示

ROW（）函数

  ROW()函数主要起标记作用，比如说下面的例子，我们可以用ROW（）辅助进行标记，

 比如说=MATCH(E7,代码)，我们可以查找到E7在“代码”里的序列，我们可以修改为下面的公式：

=LARGE((代码=E7)*ROW($1:$6),ROW(1:1)),将前面的条件为TRUE（=1）时，ARRAT参数就可以得到ROW（1：6）相应的行值，其余均为0

为什么MATCH（）可以处理的问题，我们为什么要用这么长的公式来代替

因为MATCH（）函数有自身的特殊性，就是它只能返回第一个对应值的位置，当有多个数值时，就无法找到后面的数值，因此我们可以用ROW来辅助完成操作。

 

筛选算子

 主要起逻辑强化的作用。

这里的筛选算子，就是通过多条件的“与”运算，最后将满足条件的参数赋为TRUE或1，不满足条件的得到FALSE或0，也就是我们能直接使用的算子，然后，根据我们筛选要具体要求，借助IF或其它的运算（如*ROW()），再配合MAX()这类的取值函数就可以得到我们需要的筛选结果。

比如说，上面的LARGE()+ROW()函数取位置的公式，其中也运用了筛选算子，其运算步骤如下：

=LARGE(($B$6:$B$11=$E$7)*ROW($1:$6),ROW(1:1))  →  =LARGE({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6},{1})

=LARGE({0;0;1;0;0;0}*{1;2;3;4;5;6},{1})  → =LARGE({0;0;3;0;0;0},{1}) →  =3

再举一个两条件的筛选例子，如：

=(B17:B22=F18)*(C17:C22=G18) 我们可以演化为=({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE})*({FALSE;TRUE;TRUE;FALSE;TRUE;FALSE})

上面的两个布尔数组相乘后，就可以得到={0;0;1;0;1;0}，也就是说1就是满足我们筛选条件的值。

同样的道理，多条件时，我们只需要增加相应的判断子参数，再与前者进行乘积即可，最后总能将满足条件的以1展示出来。

不重复算子

不重复的原理，主要是使用MATCH()函数的特性，这个在前面的ROW()函数有略有提到，因为MATCH()函数查找时只会返回相同值的第一个位置，

根据这个条件，我们让需要确认是否重复的每个值与在自身内部进行MATCH()运算，那么没有重复的值，得到的MATCH()结果就与其位置是一致的，

相反，那些重复的值，得到的结果就是其第一次出现会值的位置，而不是重复值所在的位置。

=MATCH(A1:A6,A1:A6,)   →  ={1;1;3;4;1;3}

当上面的结果，与每个值或字符所在的位置进行比较时，即={1;1;3;4;1;3}=ROW(1:6)  运算的结果为 ={1;0;1;1;0;0}，也就是说第1、3、4是不重复的。

同样的道理，当我们查找不重复值是在一个字符串里，我们还可以使用FIND()函数来生成不重复算子，因为FIND()函数对于字符串来说，

 

参数驱动

主要传递给我们的思想就是向导，从基础入手，逐步展开，逐步将参数数组化从而实现参数驱动，一步步达到我们的数组公式要求。

比如说一个例子，是将B列的四个字符串或数字串在后面的四列里分别显示出来，他的作法是先水平扩展，再作垂直扩展：

即先在第一行，完成我们初期的目标，也就是说先将B1分别展示出来，公式如：=MID(B1,COLUMN(A:D),1)

现在我们是完成了第一步，下一步就是修改参数，将B1扩展为B1:B5，如=MID(B1:B5,COLUMN(A:D),1)，那么就可以一步完成我们的展示要求。

 

INDEX(array,row_num,column_num)

Array   为单元格区域或数组常量。

·        如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选。

·        如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。

• 如果同时使用 row_num 和column_num，函数INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。

SMALL(array,k)

Array   为需要找到第 k个最小值的数组或数字型数据区域。

K   为返回的数据在数组或数据区域里的位置（从小到大）。

说明

• 如果 array 为空，函数SMALL 返回错误值 #NUM!。
• 如果 k ≤ 0 或 k 超过了数据点个数，函数 SMALL 返回错误值 #NUM!。
• 如果 n 为数组中的数据点个数，则SMALL(array,1)等于最小值，SMALL(array,n)等于最大值。

语法

MOD(number,divisor)返回两数相除的余数。结果的正负号与除数相同。

 

Number   为被除数。

Divisor   为除数。

ExcelHome免费在线培训中心：http://t.excelhome.net