Excel 数组特辑班--课时1

 

 

 

 

我与数组公式有个约会  之

 

 

相   识

 

 

 

 

 

讲师：胡剑               2010.10.12

 

 

整理：Jimmy Zeng  2010.10.29

 

 

http://club.excelhome.net

 

 

 

▣ 学习感言

 

 

说真的，原本对学习数组并没有太强烈的渴求，之所以报数组特辑班，完全是因为期望的班级开课时间太迟，才选择了这个课程。

 

 

当然，上面也说了是“原本”，最简单的原因就是数组特辑课时1的作业题，完全不是小菜一碟的问题，完全是把自己唬住了，所以也就让自己“原本”的想法，

 

得到质的转变，原来对数组的轻视，做作业的时候，才发现自己原来还差得老远老远。

 

 

而从同学的水平、胡老师在论坛上的讨论等等，都发现自己都有心无力，无法跟上，而让自己认识到，原来自己对数组只是一个入门者，之前也仅仅局限于使用

 

 

COUNTIF()计算不重复数量，使用INDEX()+SMALL()来查找满足条件的多条记录，其余的就是一窍不通，更不用说胡老师在论坛上经常讨论的原理、参数驱动、

 

模块化思维了！而也是这些发现，让自己更加的谨慎，更加的努力，希望自己的函数运用能在这里，得到一次质的飞跃。

 

 

按文竹一直跟我们说的话，就是跟胡老师，要学习他的模块化思维，也正是这点，让我们慢慢地让自己的惯性思维发生质变……▧◈

 

 

 

▣本课时主要内容：

 

 

☞ 概念解析

 

 

☞ 运行机制

 

 

☞ 参数驱动

 

 

☞ 贴近实战

 

 

 

Ⅰ. 数组相关概念解析

 

 

1.1 数组

 

 

数组的存在形式，有三种：

 

 

1.1.1 引用数组，对单元格区域的值进行引用

 

 

垂直引用：=A1:A10

 

 

水平引用： =A1:E1  或者使用“转置”，可以将垂直引用转置为水平引用，如：=TRANSPOSE(A1:A10)

 

 

二维引用：=A1:E10  这就是一个10行5列的二维引用区域

 

 

1.1.2 内存数组，由公式（函数）返回的值

 

 

垂直向量：=ROW(1:5)

 

 

水平向量： =COLUMN(A:E)  或者使用“转置”，可以将垂直数组转置为水平数组，如：=TRANSPOSE(ROW(1:5))

 

 

二维数组：如由转置得到的 =TRANSPOSE(ROW(1:3))+3*(ROW(1:3)-1)

 

 

1.1.3 常量数组，直接手工输入常数的数组，由{ }进行区分

 

 

水平数组：{1,2,3,4,5,6,7,8,9…,N}，使用英文逗号（,）作为区分符号。

 

 

垂直数组：{1;2;3;4;5;6;7;8;9…;N}，使用英文分号（;）作为区分符号。

 

 

二维数组：{1,2;3,4;5,6;7,8;9…;N}

 

 

根据胡老师的经验，发现Excel中都有一个规律，就是先横(行)后竖(列)，如index()函数、Offset()函数，其参数都是先行后列的排列。

 

 

 

 

 

1.2 数组运算

 

 

单值运算如： A1+1   →  f(x)

 

 

数组运算如：A1+{1;2;3}  →  f({x1;x2;x3})

 

 

当我们的数值运算中，操作数为数组时，则进行数组运算；同理，当函数的参数为数组形式时，也进行数组运算。

 

 

但我们要区分，并非参数为数组形式时，即为数组运算，比如说Sum()函数，其参数本来就可以为引用数组的方式，而这个并没有进行数组运算！

 

 

而当我们的函数参数为单值时，我们使用了数组，那么就会因参数驱动而进行数组运算。如下VLOOKUP()函数，我们分别将第一和第三参数进行数组化：

 

 

=VLOOKUP({1;2},$G$40:$H$45,2,0)

 

 

=VLOOKUP(2,$G$40:$H$45,{1,2},0)

 

 

 

 

 

1.3 数组公式

 

 

什么是数组公式？根据前面的介绍，我们可以这样下定义：

 

 

数组公式：包含有数组运算的公式。

 

 

区别：数组公式在输入结束时，须按Ctrl+Shift+Enter结束，用以与普通公式的处理方式相区分。

 

 

如公式=SUM(2+ROW(1:3))，当录入结束时只按Enter，那么结果为3；而当按Ctrl+Shift+Enter结束时，返回的结果为12。

 

 

普通公式的运算过程只取数组的第一个元素参与运算，也就是SUM(2+1)=3;

 

 

而数组公式的计算过程刚将所有数组元素进行运算，也即SUM(2+1,2+2,2+3)=SUM(3,4,5)=12.

 

 

 

 

 

数组公式有两种分类：

 

 

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

 

 

多单元格公式：即数组公式应用于多个单元格区域，一般返回多个运算结果，对应存储在相应的单元格内。

 

 

 

 

 

 

 

 

Ⅱ. 运行机制解析

 

 

☞ 代数解释

 

 

自变量：x                              返回值：f(x)

 

 

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

 

 

{x1;x2;x3;…;xn}                    {f(x1);f(x2);f(x3);…;f(xn)}

 

 

 

 

 

☞ 动画演示

 

 

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

 

 

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

 

 

 

 

 

☞ 并行处理

 

 

SUBSTITUTE(text, x, new text, f(x))

 

 

A1234Z, 1, "",  →  A234Z

 

 

当我们把x的参数修改为{1;2;3;4}后，结果是不是会等于AZ呢？

 

 

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

 

 

 

 

 

Ⅲ. 参数驱动解析

 

 

☞ 参数驱动

 

 

驱动参数是什么呢？驱动参数就是函数里的必要参数值。

 

 

先了解这个之后，我们来看看什么叫做“参数驱动”？

 

 

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

 

 

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

 

 

e.g：

 

 

比如说=COUNTIF(A$1:A$5,criteria)这样的下拉公式，我们可以直接将第二参数数组化，从而得到数组公式：

 

 

=COUNTIF(A1:A5,B3:B6), 也就是说，当我们需要构造数组公式时，可以从某个参数数组化开始，让该参数驱动公式、升级为数组的运算。

 

 

 

 

 

☞ 实例演示

 

 

ROW()函数

 

 

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

 

 

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

 

 

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

 

 

当然，为什么MATCH()可以处理的问题，我们为什么要用这么长的公式来代替呢？

 

 

这是因为MATCH()函数有自身的特殊性，也就是说MATCH()只能返回第一个对应值的位置，当有多个相同时值时，无法返回第二或之后值的位置，

 

 

而这个缺陷我们就需要用ROW()的辅助，来加以弥补。

 

 

 

 

 

筛选算子

 

 

→ 主要起逻辑强化的作用。

 

 

这里的筛选算子，就是通过多条件的“与”运算，最后将满足条件的参数赋为TRUE或1，不满足条件的得到FALSE或0，也就是我们能直接使用的算子，

 

 

然后，根据我们筛选要具体要求，借助IF()或其它的运算（如*ROW(1:100)），再配合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()函数对于字符串来说，

 

 

也有与MATCH()函数一样的特性，即只返回第一个对应值的位置，引用胡版的话就是异曲同工——殊途同归。

 

 

 

 

 

参数驱动

 

 

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

 

 

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

 

 

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

 

 

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

 

 

 

Ⅳ. 实战招数演练

 

 

☞ Row函数

 

 

☞ 条件筛选

 

 

（这里讲述的实战例子在上面的讲述中已经穿插，不再重复。）

 

 

 

Ⅴ. 总结

 

 

其实跟同学或老师的讨论是蛮多的，小结就不一一陈列了，主要都发表在班级论坛里了，只是对于胡老师OFFSET()函数的讨论，确实显得有心无力，

 

因为自己的小结作品就是其中一个痛，本来是想用OFFSET()函数的多单元格数组公式来实现，但是试来试去，均不法达到自己预期的目的，就像胡老师

 

 

在提到的SUBSTITUTE()函数一样，所以没有参与其中，这是一个遗憾，希望后面可以抽更多的时间，让自己多思考，多提问，多体、多悟！^_^…..

 

 

 

 

 

 

Jimmy Zeng @ 2010.10.29

 

 

 

更多资讯及课程内容，请关注ExcelHome论坛：

 

 

http://club.excelhome.net