Excel 数组特辑班--课时2

                

我与数组公式有个约会

 

 

之

相   知

  

讲师：胡剑             2010.11.02

  

整理：Jimmy Zeng  2010.11.10

  

http://t.excelhome.net

   

▣ 学习感言

  

学完课时2，才猛然发现，原来自己对数组的迷茫，还远不止课时1的感悟，还有二维变形、参数筛选、信息量调制、更有双参数驱动的高级应用。

  

而作业也着实让自己对数组的变化有了更深刻的了解和理解，体会最深的要数我们处理“思维瓶颈”的方式，从作业或练习题来看，我们的思路

  

有可能是天壤之别，而这个差距的源头是什么呢？有些同学可能使用很简洁的公式就可以解题，而自己，在Excel内作的辅助参考列，是刷完再刷，

  

但结果公式还是冗长+冗长！

  

胡版在数组公式的高效原理的分享上，确实是指明了一条很重要的道路、或者说是我们使用数组公式时必不可少的一个方向标，因为二者也是天差地别；

  

需要根据我们实际应用，来取舍，当然，多单元格数组公式必然是我们的首选，这才是体现数组公式价值的一个亮点。而自己之前对数组公式的误解，

  

也得到了纠正，原认为数组公式的运算速度都会比普通公式慢，尤其是公式量多的时候，而却没有想到，把公式归为一个时，速率又会是怎么样的呢？

  

本课时的前面部分，胡版就是告诉我们要“分步走”，而不要企图一步走完我们实际上一步难以走完的，让自己思路保证清晰的同时，也保证公式的质量，

  

很多时候，我们在做题还是实践，都是从无到有的过程，越难的问题则越是考验我们的思路是否清晰。

  

后面部分的课程内容，则是带领我们走向一个新的境界，给我们展示数组的强大、也带着我们一起挖掘函数的无所不能。

  

下面，请跟着我，回顾课程主要内容的同时，也体会一些不一样的东西，^_^           ■▨

   

▣本课时主要内容：

  

☞ 概念解析

  

☞ 运行机制

  

☞ 参数驱动

  

☞ 贴近实战

   

Ⅰ. 策略分享概念解析

  

1.1 处理瓶颈

  

“瓶颈”，是我们不管工作还是生活，尤其是学习中所无法躲避的，经常体现在我们的思维方式上。

  

这里胡版是给我们体现在处理数组公式时怎么一步步突破我们思维的“瓶颈”，从而达到我们所要求的目的。

  

这个其实课时1也略有提到，就是一种“先水平展开，再垂直扩展”所提到的实例，而课时2就是从这个开始，导出后面所有的，能突破我们

  

“思维瓶颈”的种种处理方法、函数应用等。

  

胡老师还是从MID()函数入手，告诉我们怎么从无到有，从简单到复杂的一个过程。

  

除了课时1的“先水平展开、再垂直扩展”，这里还另外再告诉我们，其实也可以“先垂直展开，再水平扩展”，殊途同归。

  

先水平再垂直，就是先从单个单元格入手，即从=MID(B6,TRANSPOSE(ROW(1:4)),1) 扩展单元格的区域为 =MID(B6:B9,TRANSPOSE(ROW(1:4)),1)

  

而先垂直再水平，就是从参数变量入手，即从=MID(B6:B9,1,1) 扩展第二参数到 =MID(B6:B9,TRANSPOSE(ROW(1:4)),1)

   

公式：=MID(待处理,COLUMN(A:D),1)

  

                          

1.2 流水线作业

  

跟上面的“瓶颈”一样，胡版给我们展示了一个生动的动画，就是“流水线”作业的过程，从无到有，从小到大的一系列作业，最终实现产出。

  

这一Part的示例，是使用了课时1的其中一个作业题，就是“不抛弃、不放弃”，提取第一次出现的字符，结果应该为“不抛弃、放”；

  

胡版的流水线封装工位，分了5步，分别为：拆分后、去重复、有效序号、序号集结、数组呈现；

  

即先将我们的字符串使用MID()逐字拆分，然后用FIND()或者MATCH()将第二次出现的字符屏蔽，再用SMALL()将没有重复的字符序号集结起来

  

最后返回到MID()将符合我们要求的字符呈现出来。

  

这一过程其实就是对求解思路的逐步分解，逐步深入，同时也向我们展示“公式封装”的操作习惯，就是使用定义，将公式定义起来，

  

简化公式长度的同时，也容易、方便阅读。

     

1.2 内存数组

  

说到内存数组，这个也非常实用，胡版列举的可以生成友好内存数组的函数有ROW(), LOOKUP(), SMALL()等，

  

而无法生成友好内存数组的函数有INDEX(), VLOOKUP(), 某此情况下，OFFSET()也不可以。

  

以前在EH论坛看问题，答问题时，就经常遇到这种困惑，无法直接用公式来得出想要的结果，而需要借助定义名称或辅助列，而那时候并没有

  

去研究为什么不可以，也不知道哪些函数是可以的。现在，终于有一个系统化的了解，让自己知道对应不同的问题，不同的函数，应该采取不

  

一样的解决方式，来避开可能产生的公式错误和困惑。

  

当然，这里胡老师只列举了几个常用的函数，对于其它的函数，还有待我们发现、开发。

  

下面的OFFSET()就跟胡版第二个思考题一样，虽然按F9可以查看到相应的结果，但返回值不一样，奇怪的是它无法当内存数组使用，无法进行提取：

  

奇怪的是对OFFSET()返回值的提取方法，嵌套1的公式无法取值，但嵌套2的公式却可以，OFFSET()这个属性还真没搞明白，难怪zhangjimfu也在纠结，^_^

  

公式1：=OFFSET(索引,{0;3;2;4},)

  

嵌套1：=INDEX(OFFSET(索引,{0;3;2;4},),ROW(1:4))

  

嵌套2：=INDEX(OFFSET(索引,{0;3;2;4},),,)

  

                                         

1.4 高效原理

  

这个“高效原理”应该可以很直观地告诉我们，为什么要学数组公式，而且为什么建议我们尽可能使用多单元格数组公式，因为这涉及到效率的问题。

  

下面是胡版给我们展示的运算机制，因为只要按Ctrl+Shift+Enter结束的都为数组公式，但数组公式还分为单返回值和多返回值，他们的公式示意分别为：

  

                    

上面的core就是公式的核心计算部分，而参数 s 与 array 分别为计算的元素，当然后者居多，下面的T为各部分计算所需的时间，根据这个来看，

  

显而易见的，当使用多单元格数组公式时，所需要的时间为T+t*n，核心计算只需要进行一次运算，而返回值的数组公式，核心计算需要进行n次，

  

显然，这大大增加计算次数的同时，增加计算时间也降低这个计算效率，当数据量较多时，这个差异就会相当明显。

     

其实之前在EH论坛经常接触到数组公式，一般使用的也是单单元格的数组公式，所以也已经领略到它的“慢”，也因此总告戒自己与别人，数组公式尽量不要用，

  

或者说，能不用就别用~~~现在回想起来，是有点愚蠢，因为对于多单元格数组公式来说，那效率是无法比拟的！

  

先不说可以一步到位计算完毕，单单就输入公式时，一般的区域引用基本上都不需要作“绝对”或“相对”引用的区分，而是一步到位，相当方便。

     

对于高效的问题，自己的理解是从另一个比较的角度来看的，单返回值公式就像VBA里FOR…NEXT…之类的循环，我们有多少个值，就需要指定多少次循环，

  

而多单元格数组公式就相当于直接区域赋值，用“区域＝区域”就可以一个操作完成区域里N个单元格的赋值，比起FOR…NEXT…要高效N多倍啊！

     

Ⅱ. 运行机制解析

     

Ⅲ. 参数驱动解析

  

☞ 坐标的舞蹈

  

                  

根据上面的自变量及返回值的关系，我们可以得到下面的对应关系，即返回值随着自变量的位置调整而调整，这也就是胡老师所谓的“舞蹈”。

  

{x4; x3; x2; x1}       →          {f(x4); f(x3); f(x2); f(x1)}

  

{x1; x3; x2; x4}       →          {f(x1); f(x3); f(x2); f(x4)}

     

而这里，主要是让我们了解，我们可以通过某些变量的位置变化来达到结果的位置变化，如下：

  

我们通过在“坐标”区域内定义自己的行数，再通过HLOOKUP()函数，就可以得到相应坐标上的内容，按相对应的矩阵方式展示出来。

  

 

公式1：=HLOOKUP("*",索引字段,坐标,)

                                  

☞ 惯性思维

  

胡版这个问题其实就是告诉我们，上面提到的坐标变化，其实并不是局限于行或列，而很多时候可以对于改变行的方式从而得到列的结果。比如说：

  

=INDEX(array, row_num , column_num)

  

其中的row_num一般都是垂直数组，而column_num一般为水平数组，

  

但是，我们可以在原来的思维上，通过对参数的原形态进行转换之后，就可以将原来的结果进行转置，如将水平与垂直的数组进行转换。

  

比如说我们对第三参数的列数进行不同的改造，就可以得到相对应不同的结果，如：

  

=INDEX(索引表,1,{1,2,3,4}) 与 =INDEX(索引表,1,{1;2;3;4}) 他们不同的就是column_num的不同，当然返回的结果也会对应地发生变化。

     

☞ 我形我变

  

这一节也是在“坐标”的内容上作文章，更深入地告诉我们，坐标可以随心所欲，变化无穷，这在作业题里有着重要的体现。

  

让我们自主构造目标二维数组，从而将源数据的排列方式作质变，从单行或单列可以直接变为多行多列，而位置的调整也是按我们构造的二维数组进行变化，

  

奇、偶；上、下；左、右的切换，都可以直接控制二维数组的值来产生变化。

  

这个重点也体现在实战招数演练的ROW函数操作上，变化无穷，对数据的颠倒，这是另外见识了一下，也检讨一下，自己对颠倒数据，喜欢使用LARGE()或

  

SMALL()来产生倒序的数列，但胡老师则是直接使用减法，来得到，这又是多少简单的，自己却想不到。

  

如：=TRANSPOSE(ROW(1:12)) 可以产生一行1~12的值，而=13-TRANSPOSE(ROW(1:12))就可以得到一行12~1的倒序数列。

  

自己之前的做法，则是使用=LARGE(TRANSPOSE(ROW(1:12)),TRANSPOSE(ROW(1:12))) 这么烦琐的公式来得到。

  

当然，胡版对二维矩阵构造方法的解析，让我们更清楚明白，需要我们先找到相应的规律，写出通用公式再定相应的参数。

  

比如之前群里讨论的一样，大家也是从简单入手，找出通用公式后提取公因式，再定参数，就轻而易举了！

  

                         

Ⅳ. 实战招数演练

  

☞ Row函数

  

☞ 筛选公式

  

☞ 信息量调制

  

☞ INDEX特性

  

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

    

Ⅴ. 总结

  

这一课时的知识点其实比上课时的多，但Q群或论坛上的讨论不如上课时的激烈、活跃，大家有不同的、不可抗拒的作息时间，但也可能是消化并不是很好，

 

导致无法表述出来或与大家分享。我也一样，即使作业做完了，练习也做了，但对课程后半部分的理解和吸收还不是很彻底，而希望胡版的课程安排能跟现在的

 

一样，下个课时会穿插这个课时的部分内容，再作详细深入的讲解，让我们回过头来的时候，才发现“不过如此”，当然，平时还是要响应胡版的多体、多悟！^_^…..

      

Jimmy Zeng @ 2010.11.10

  

更多资讯和培训内容，请关注ExcelHome论坛：

  

http://club.excelhome.net