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

胡剑0227

原帖由 zmdzywl 于 2009-11-3 15:19 发表
有好几天没来了，突然间又看不懂了，还得继续重新爬楼，不过我相信，我每次重新爬楼后都会有所收获的，爬得多了慢慢就会明白吧。还有一个问题-ROW(A2:G9)*1000怎么生成初始的原始值？为什么加个-号？

先前为了排序时上下行之间互不影响，对元数据进行了 +ROW(A2:G9)*1000 处理，这里是一个逆过程，就是这样吧（是在说这个内容吧，呵呵）

inv007

对于我这个菜鸟，这样的贴真是及时雨

evillenlee

原帖由 胡剑0227 于 2009-5-17 17:11 发表
看看下图，立体数组原因猜想...

上面是胡剑版主279楼的“猜想”，其实我觉得这与278楼说的“index或是offset函数按F9查看返回值与多单元格数组公式的显示值不同”不属于统一问题。rand()是易失函数，返回值会随着任何一个单元格内容的变化而不断的重算，所以，当您用F9查看的时候看到的是最新的一次重算的结果，也就是3行4列相同的结果，如果要将看到的这3行4列相同的结果以多单元格数组公式方式去返回到单元格，是不可能的。因为以多单元格公式再次键入的时候每一个单元格里的返回值都会导致重算。这样，看到的每个单元格都是不同的结果。也就是说当键入3行4列的多单元格公式的时候，rand()函数计算了12次，其中包括重算11次。如果您用=IF({1;1;1}+{1,1,1,1},{58,61,52,46;29,56,28,14;92,79,46,39})公式去查看的话，按F9的结果就是正确的返回值了。我是这么理解的。


===================================================================
呵呵，朋友还是不要用您了，明白你的心意就是了，呵呵...

[ 本帖最后由 胡剑0227 于 2009-11-14 09:28 编辑 ]

胡剑0227

因为以多单元格公式再次键入的时候每一个单元格里的返回值都会导致重算。这样，看到的每个单元格都是不同的结果。也就是说当键入3行4列的多单元格公式的时候，rand()函数计算了12次，其中包括重算11次。

恩，本来我用RAND函数就是想证明 “因为以多单元格公式再次键入的时，在每一个单元格内都会进行重新计算”，不过你认为279的这个例子可能是因为RAND函数是易失性函数导致的，没有说服力。是吧，这里比较难区分是否是易失性的重算还是数组公式返回到各个单元格时本身就会重算....。

我只好再找其他的佐证，好在还有其他例子，呵呵。

我本来要表达意思是 多单元格数组公式的值返回到各个单元格内时都进行重新运算，不是简单的先计算出数组的函数值（就如F9 查看函数值）然后按序分配到各个单元格中。

比如你选中 A1:A10，在活动单元格为A1的情况下，在A1单元格中输入 =row()，然后按CTRL+SHIFT+ENTER，这个时候 A1:A10 的值就是1到10，而不是简单的先 计算出 1，然后把1分配到A1:A10中，从这里也可以看出，在多单元格数组公式将值分配到各个单元格时是重新进行了运算，这是一个水乳交融的过程。重要的ROW()函数应该不是易失性函数，呵呵。

上面的这个例子还是从一位同学中借用的，呵呵。

[ 本帖最后由 胡剑0227 于 2009-11-13 21:12 编辑 ]

胡剑0227

另外，大家应该注意到同属一个数组公式的各个单元格内的公式是一样的，但返回值却是不同的，因此可以猜想在数组公式返回到多个单元格内时其实还应用到了 位置 信息（就是当前单元格在在数组公式返回值中的序号），而直接用F9计算数组公式时没有这个信息的，所以也可以猜想到数组公式返回到各个单元格时不是简单的将 F9 以后的数值进行按序分配，而是要复杂一些。所以INDEX,OFFSET使用F9与返回到多单元格时效果不同应该可以理解了。

在http://club.excelhome.net/thread-447203-22-1.html的212楼也有类似的猜想，有兴趣可以看看

[ 本帖最后由 胡剑0227 于 2009-11-13 21:14 编辑 ]

gjy631101

（公式一）MAX(FREQUENCY(IF(区域="",ROW(区域)),IF(区域<>"",ROW(区域))))
（公式二）LOOKUP(9E+307,FREQUENCY(IF(区域="",ROW(区域)),IF(区域<>"",ROW(区域))))
如何在让这二个数组公式分别在去掉了区域中最后一个值后再计算结果？
请求高手发表看法。

[ 本帖最后由 gjy631101 于 2009-11-14 08:03 编辑 ]

胡剑0227

假设区域就是A1:A10，那么可以使用LOOKUP函数进行截取操作，大致思路如下。
=LOOKUP(ROW($1:$9),ROW($1:$10),$A$1:$A$10)

gjy631101

如果它是一个动态的区域呢？

胡剑0227

动态区域都没有关系，不过这里只适合时一列的单元格引用。

比如 定义 名称 区域就是一个动态的但与昂引用
=lookup(row(indirect("1:"&rows(区域)-1)),row(区域),区域)
只要能理解公式的思路就能轻松的进行改造。

evillenlee

原帖由 zhanhewang 于 2009-5-17 11:02 发表
用CHOOSE做一个。请指教。

并不是要合并的列都是一样长度的，也可以尝试把一个 一维的 列在 另一个列的指定位置插入进入，呵呵。

273楼的那个附件里IF+MID合并数组的方法好像有地方出错了吧，我看返回值有错误，"L"下面应该返回"M"，可是却返回"G"，我用笨方法也可以达到目的，公式如下
=MID(L1&L2&L3&L4,ROW(INDIRECT("1:"&LEN(L1&L2&L3&L4))),1)