有志于成为数组公式高手的不要错过（功力修炼）(胡剑开课了...)

sealandhong

一点学习体会，请指正。

[ 本帖最后由 sealandhong 于 2010-5-3 08:15 编辑 ]

michael_soon

{=SMALL(OFFSET(A1,(ROW(1:8)-1),0,1,7),COLUMN(1:7))}

chen2010

=MID(A2:A9,ROW(C2:I9)-ROW(C2:I9)+COLUMN(C2:I9)-2,1)
这样中不中？

人家早就有答案了，我还[。。。。

[ 本帖最后由 chen2010 于 2010-5-4 13:51 编辑 ]

chen2010

茫茫网海，人生何处不相逢，看相片像你，看拼音是你！老四，你什么时候也在这个网站啊，我是老二呀！

chen2010

以前没见过数组公式，但用过vlookup函数，都是先要找的值，接着确定在哪个区间找，找到返回对应列的值。受这个影响深了。谢谢LZ的指点，明白了不少！

lrlxxqxa

原帖由 piggy_19840215 于 2009-5-9 09:00 发表
猪猪悟语（一）

楼主您好：
我现在看贴到26楼，目前感觉明白了您所说的：“需要得到怎样的数组反馈类型就构建对应的参数数组”的意思了。以下是我将我的理解用常量数组表达出来。余下的问题就是如何建立与“常量数组”等效的“内存数组”。

针对《LOOKUP数组变形》，三个公式分别为：
=LOOKUP({1,2,3,4,5,6;7,8,9,10,11,12},ROW($A$1:$A$12),$A$1:$A$12)
=LOOKUP({1,2,3,4;5,6,7,8;9,10,11,12},ROW($A$1:$A$12),$A$1:$A$12)
=LOOKUP({1,2,3;4,5,6;7,8,9;10,11,12},ROW($A$1:$A$12),$A$1:$A$12)

由此我认识到，要得到什么样的返回值（即返回数组），就要对相应参数的数组结构进行什么样的设计。

这些没问题，到了下面这里我对lookup的用法就产生疑惑了，

1. =LOOKUP({1,2,3,4,5,6;7,8,9,10,11,12},{1;2;3;4;5;6;7;8;9;10;11;12},{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L"})

复制代码

这其中具体的运算过程就搞不懂了。

她的两种用法如下：
1、向量形式 LOOKUP(lookup_value,lookup_vector,result_vector)
2、数组形式 LOOKUP(lookup_value,array)

这是lookup的哪一种用法呢？希望飘过的高手帮忙解释，谢谢。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
自己想通了。不错不错。
http://club.excelhome.net/viewthread.php?tid=569913&page=1#pid3805131

[ 本帖最后由 lrlxxqxa 于 2010-5-4 21:35 编辑 ]

lrlxxqxa

原帖由 zhanhewang 于 2009-5-9 09:45 发表
请教：公式=IF((COLUMN(A:D)+4*(ROW(1:3)-1))^0,OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)返回的内存数组为何与显示不同？
见附件。

这个公式也可以写成
=IF(ROW($1:$3),OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)
或
=IF(TRANSPOSE(ROW($1:$4)),OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)
甚至！
=IF(1,OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)

都可以实现OFFSET的成功落地，先不展开了，否则会紊乱大家的思绪的。自己有兴趣可以悟一下内含什么规律。
当大部分同学对这部分都消化差不多了再继续。

就如林炳尧先生在《杨式太极拳架详解》中提到的，要练好太极，就要宁磨千遍，不改一招，慢就是快，快就是慢。

我的理解：
公式中用offset的三维引用构建了一个3行4列的内存数组，而if的第一参数就要跟它相对应，所以原公式也构建了相应的内存数组与他匹配。
在下面的几个公式中，if的第一参数分别是3行1列、1行4列、单值1，
理解方式一：这是利用了数组运算中可以自动扩容，即从3行1列、1行4列、单值1分别扩容成3行4列的内存数组参与与offset构建的数组的运算使之匹配；
理解方式二：这是利用了函数运算中的隐含交叉引用，即if的第一参数数组和offset构建的数组的交集点取值；
经过这个过程后，就是所说的“落地”过程，因为if不能返回内存数组，只能返回单值（我F9看结果发现的），于是if把offset的三维引用降维成一维的几个单值，填在对应区域的每一个位置，完成运算。也就是落地装包完毕。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
理解偏差之处，请指正，这对我后面的学习很重要。

IF函数能生成友好的内存数组，不过这和它的参数是有关的，毕竟参数才是源头，返回值只是对参数的加工。因此在这种情况下IF表现的特性是受制于OFFSET的。

[ 本帖最后由 胡剑0227 于 2010-5-5 07:53 编辑 ]

lrlxxqxa

原帖由 胡剑0227 于 2009-5-9 13:51 发表
像OFFSET这类函数在基础应用时却是显得非常的灵活，容易掌握，但在数组公式应用时就会受很大限制，即使使用IF函数进行了 从空中到地面的 保护，还是不能作为内存数组传递的。

由于大家对OFFSET有比较深的感情吧，所以喜欢用OFFSET还尝试，我平时使用数组公式时一般不会去碰OFFSET函数，还有像COUNTIF等，这些函数在数组公式应用时都是受限的，不能进行深度加工。

注意观察可以发现OFFSET的第一参数是  Refence，单元格或区域引用，不是array（数组），这就限制了OFFSET函数不能接受来自其他公式传递过来的数组，所以在数组公式的应用中这类函数将会收起往日绚丽的光彩。一般使用LOOKUP来实现。

单元格区域毕竟是很特殊的数组，只有能接受内存数组的函数在数组公式中才会得心应手。到这里我想你一定对参数的类型有了新的理解。

看到这里，发现自己对具体哪些函数能够接收内存数组，哪些不能，还真是没整理过。
能够的：match,mid,small,large,lookup(用得很少)当然还有row和column
不行的：if，index？脑子有点乱
至于offset和countif也经常会在数组公式中用到，是只能产生而不能传递吧。

发现原来自己看帮助总是很潦草，range,array,refence什么的都没仔细看过，结果到了关键时候是一塌糊涂，教训啊，希望大家也从我这个反面典型身上得到些借鉴。

lrlxxqxa

原帖由 胡剑0227 于 2009-5-10 06:35 发表
要提高数组功力，接下去的练习就是要提高数组变形能力和数组构造能力，相对来说变形能力简单些，运用你目前的领悟，抓住LOOKUP练内功，假日时日就能随心所欲了。熟练以后就不必先常数数组-内存数组这样按序进阶，一般可以直接用 ROW()函数来变形得到。
有了这些理论武装，就如练功时血脉畅通了，用心意写公式，不要拘泥函数本身的功能。
利用名称把内存数组打包，这样写的公式逻辑性就非常清晰了，而且名称又可以作为另一个逻辑块的输入再生产新的名称，使得公式模块化。模块化只需考虑输入和输出，这里输入输出都是数组。于是完成实际问题时，我们可以一个逻辑块，一个逻辑来解决，类似战术中的分割包围个个击破，这样就把复杂的问题解析成相对简单的问题了，最后就是一个组装的过程。
如果数组变形是内功的话，上面模块化的思想是解题策略。这也会在以后的跟帖中让大家体会到。

你的这席话，真是让我有种醍醐灌顶茅塞顿开的感觉。说得好啊，从战略、战策上把具体问题模块化再根据需要构建数组和变形。
看这帖有点上瘾的感觉了。

lrlxxqxa

原帖由 胡剑0227 于 2009-5-10 14:05 发表
尝试实现下图效果：
提示，可以使用MMULT函数。

初学数组的同学可能对这个函数还是比较生疏的，这里略去很多的注意点不提（例如参数数组不能使逻辑值，要用N函数转化为0，1），主要提一下功能，或更准确的说是运算机制。

MMULT(array1（m行*n列),array2(n行*t列))，返回一个array3(m行*t列）

其中array3的 i 行 j 列元素是这样得到的：array1 的 i 行 数组 和 array2 的 j 列经转置后的数组 进行相乘 然后求和。

即 sum(array1 的 i 行 数组 *  transpose(array2 的 j 列)),数组公式。
细细体会这个运行机制吧，这是可以用MMULT来思考问题的核心！

在数组公式中有特殊的地位，用来思考二维问题。

顺利通关后，请尝试解决106楼作业。

关于MMULT较细致和实战中的应用意义请看332楼及其一下几楼的图和附件说明

这个函数从来没有使用过，看了胡版的说明有了模糊的概念，既然想您所说她在数组公式中有特殊的地位，我就进一步在实战中运用和理解下