Excel 数组特辑班--课时3

             

 

 

我与数组公式有个约会

           

 

之

           

 

 

相   恋

 

 

讲师：胡剑             2010.11.12

         

 

 

整理：Jimmy Zeng  2010.11.13

         

 

 

http://t.excelhome.net

          

 

             

 

 

▣ 学习感言

          

 

 

这个课时最有趣的要数胡版使用IF()和CHOOSE()进行“揉捏数组”了，当然，也是难度最高的，从作业题就深有体验。

  

 

 

说难可能也不算难，只要掌握数组串接时的一个“布袋原理”也就掌握了全部的精粹，只是多数组串接时，这个过程会显得繁琐，思路有可能

 

 

会出现混乱，而“名称定义”则是一个很好的解决方法，也就是上课时讲的“封装”。

    

 

 

对数组的串接应该也是相当有趣的，这个对于我们生成“内存数组”又多了一种方法，或者说又多了一把“利剑”，也就是说，对于我们解决

 

 

辅助列的问题，又有一种解决方案了，了解了这些之后，我们可能更会有这样的感慨，那就是Excel对于文本串接的不足，也就是说用连字符&

 

 

或CONCATENATE进行文本串连时，只能一个个进行，而无法直接多元素串连，更不用说内存数组，而即使后来大家发现的PHONIC()函数，

 

 

它也有不足处，就是只能对区域引用Ref或对OFFSET()引用进行串接，但仍然是无法串接内存数组。当然，这个也不是我们研究的内容……

 

 

 

另外一个讲解要点就是数组的“自适应”法则，基本上讲解数组的讲师都会涉及这方面的内容，而我对这方面的理解，纯粹是按矩阵运算法则

 

 

来解释，知道了矩阵运算法则，也就会觉得这个是理所当然的，所以不作深究。

     

 

 

下面，一起回顾本课程的主要内容，同时也作一些自己的体会，^_^           ■▨

 

             

 

 

▣本课时主要分四大块内容进行讲解：

        

 

 

☞ 概念解析

          

 

 

☞ 运行机制

          

 

 

☞ 揉捏数组

          

 

 

☞ 贴近实战

          

 

             

 

 

Ⅰ. 策略分享概念解析

         

 

 

1.1 多流水线作业

          

 

 

上节课，胡版才向我们生动地演示了“流水线作业”，今天已经把车间升级，改变为“多流水线作业”，提高作业效率，^_^

 

 

 

通过胡版的分叉图示，我们可以清晰看到，多流水线作业就是一种并行处理的过程，尤其是使用IF()的时候，这个就显得更明显了！

 

 

 

就拿lhx120824同学要用MMULT()对混合班级分段排名来说，其实就体现了这样一个过程，公式通过IF()并行判断成绩或学生归属的班级，

 

 

然后将成绩在相应班级里进行排序，虽然没有得出简洁通用的公式，但还是拿出来作一个示例，如下：

   

 

 

 =IF(B2:B28="a",AL,IF(B2:B28="b",BL,CL))

       

 

 

即并行判断B2:B28单元格的班级名称是a、b还是其它，从而分别选择要进行的排名运算公式。

   

 

             

 

 

这种多流水线作业的理念，在多条件求和或查找中，也多有体现，比如经常用到的SUMPRODUCT()或SUM()多条件查找或个数求和公式 ,

 

 

 =SUMPRODUCT((array1=条件1)*(array2=条件2)*(array3=条件3))

     

 

 

这里所要满足的3个条件，是以并行多流水线的方式，求出TRUE或FALSE之后，再三者进行复合运算，最后得到满足要求的个数。

 

 

             

 

 

1.2 自适应法则

          

 

 

自适应法则大家其实应该是经常接触的，只是原来并不知道这个是“自适应”运算的结果，只知道这个是理所当然的，呵呵！！

 

 

 

引用ggsmart的说法（理解），这个是数组的“自动扩展”特性，即当运算的前后数组维数不一致时，会自动将维数小的一方（或两方），

 

 

自动进行扩展，使得两个参与运算的数组维数一致，从而也会得到一个最大维数的运算结果，如胡版下面的向量式自适应：

 

 

 

            

 

             

 

             

 

             

 

 

一个3×1的矩阵，加一个1×3的矩阵，最后会得出一个3×3的结果矩阵，大家应该也清楚，其实这也就是矩阵乘法里的运算规则，

 

 

 

也就是两个矩阵（数组）参与运算时，矩阵的列数与行数必须有一对相等，即必须为M×N，与N×Q的形式，最后得到M×Q的结果；

 

 

 

同样，我们也可以用矩阵运算规则来解释胡版提到的“二维固化”法则，因为2×2的矩阵与4×4的矩阵进行运算是“非法”的！

 

 

 

当然，这个也适用到MMULT()函数里，因为这个就是Matrix Multiple（矩阵乘法），当两个矩阵的列与行不相等时，返回#VALUE。

 

 

 

这也是我们之前会理所当然接受这个“自适应”或“自动扩展”法则的原因，而没有深入探究数组运算过程中存在的变换规律。

 

 

             

 

 

Ⅱ. 运行机制解析

     

 

 

2.1 条件函数数组机制

         

 

 

记得刚开课的时候就在Q群讨论INDEX()和LiuguanSky提出的=IF({1,0,1},IF({1,0},"A","B"),"E")、以及=IF({1,0,1},IF({1,0,0},"A","B"),"E")

 

 

对上面的IF()数组形式，除了IF({1,0}…比较容易理解之外，其余的都搞不懂，也没有去深究，但现在回过头来看，其实就很简单明了了！

 

 

因为现在有实例也有讲解，很容易就理解这里面的取值对应方法。

      

 

 

也就是说，当IF()的条件判断参数（数组）里，多于两个数组元素时，第三个数组元素会根据自己所在判断参数里的位置，取后面结果里

 

 

对应位置的结果，表示TRUE或FALSE结果的值为单元素时，则相对应返回。比如说：

    

 

 

 =IF({1,0,1},"A","B")     =IF({1,0,1},{"A"},"B")       =IF({1,0,1},{"A","C"},"B")         =IF({1,0,1},{"A","C","D"},"B")

  

 

 

对应返回的值如下：

         

 

 

 ={"A","B","A"}           ={"A","B","A"}                ={"A","B",#N/A}                      ={"A","B","D"}

   

 

 

从这一点出发，上面的两个公式就很容易理解了，对多元素数组的结果，只要按位找值即可，真正的“按图索骥”！

  

 

             

 

 

2.2 IF与Choose的基本机制及特性点拨

        

 

 

这两个函数的特性，是主要讲解的就是当第一参数是多元素数组时，后面二、三参数的value，对应取值时，会按位补足，自动适应位置。

 

 

也是后面讲“双驱十字剑”和“数组串接”与“并接”时一个重要的应用特性。

     

 

             

 

 

Ⅲ. 揉捏数组

          

 

 

这一部分才是课时3的重点，胡版用这四个字来形容这一部分内容，感觉很恰当，很到位，还有点“蹂躏数组”的感觉，所以也很有霸气，^_^

 

 

主要讲的就是利用前面提到的IF()和CHOOSE()函数的特性，调整串接的数组长度或宽度后，利用第一参数的值控制取值，从而进行接合。

 

             

 

 

☞ 数组并联

          

 

 

数组并联，这个比较简单，是使用IF和CHOOSE的第一参数，构造成并列的一维数组，再通过二、三或更多参数的指派，构造成一个并排的

 

 

二维数组，如下面的示例，公式为=IF(并联因子,A,B)，而对于CHOOSE()函数，只是简单的应用即可：CHOOSE(index_num,value1,value2,...)

 

 

            

 

             

 

             

 

             

 

             

 

             

 

             

 

 

☞ 数组串联

          

 

 

要了解串接的方法，我们要先了解一个很重要的“布袋原理”，也就是从IF()函数的特性导出的，因为当第一参数为两个元素以为上的数组时，

 

 

取值是位置一一对应的，所以我们不能直接用数组放到后面的参数，而要把数组进行拉伸或腾空，让元素的位置对应好，不然会出现#N/A，

 

 

如下图的两个数组串接，如果不进行调整，而使用“布袋”的参数进行接合，那只能得到{"A";"B";"C";"D";"I";"J";#N/A;#N/A;#N/A;#N/A}

 

 

所以，对“数组2”，我们要通过 =LOOKUP(ROW(1:10)-4,ROW(1:10),数组2))，把数组2从原来的6个元素拉伸为10个元素，

 

 

 

当然，“数组1”我们也可以进行拉伸，将后面的值填充为#N/A，即将上面的公式改为+即可，只是这个实用性不高，所以我们没有必要进行。

 

 

通过这样的构造，用=IF(ROW(1:10)<=ROWS(数组1),数组1,LOOKUP(ROW(1:10)-4,ROW(1:10),数组2)) 就可以得到下面的RESULT。

 

 

使用CHOOSE函数也是同样的道理，要将每个数组的长度拉伸到同等长度，使各数组的元素在纵向上没有重合，这样才可以得到正确的串接。

 

 

            

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

             

 

 

Ⅳ. 总结

           

 

 

数组特辑班的内容，每一节课的内容，基本上都让人无所适从，尤其是函数基础比较薄弱的同学，可能感受更深刻。

  

 

 

从作业的难度，也确实是逐步深入，慢慢有所增加，当然，所教授的内容，也让我们对“数组公式”有全新的认识，知识的增长也是固然少不了的。

 

 

这一点，自己的体会最为深刻，因为确实有很多思想，在学习完数组特辑之后，以前的很多思想都得到颠覆，比如说ROW()，比如说LOOKUP()，

 

 

还有最重要的一点，就是从中理解到，原来还有“内存数组”这一回事，而“内存数组”就是相当于“辅助列”，比如说，昨天晚上，sanim同学

 

 

在技术论坛的一个解题，他使用了辅助列，这让他很不满意，因为询问大家能否直接使用公式完成，从题目进行分析后发现，其实所谓的辅助列，

 

 

也是可以使用数组公式在内存数组时构造出来的，所以后面我们也直接使用一个公式完成那个提问。

   

 

 

还是前面所说的，对ROW()的感受是最为深刻的，因为数组公式里，无非就是元素的操作，而ROW()则恰恰能很好的诠释这个任务，当然，

 

 

有了ROW()就固然少不了TRANSPOSE()，MMULT()用来排序也是在特辑班才有微弱的体验和收获，而这个也少不了TRANSPOSE()的协助。

 

 

还有一个体验，就是现在写公式基本上都使用多单元格数组公式，这也是首要出发点，从自己在技术论坛上看提问来看，自己解决函数问题的

 

 

能力也大大提升了，复杂繁琐的问题，现在都有很清晰的解题思路及步骤，所以也时常迎刃而解。

    

 

 

真的很庆幸自己报了“数组特辑班”，为自己的学习也还感到满意，同时得感谢胡版、IF、小宝、文竹这段时间对函数的教授！

 

 

 

虽然自己对函数的理解和数学思想的能力还比较差，但学习的过程是漫长的，自己只能不断地锻炼、修练、实践……

  

 

                           

Jimmy Zeng @ 2010.11.21

          

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

        

http://club.excelhome.net