[开_14] 多单元格联合数组公式[已总结] ★★★★

gdliyy

以下是引用apolloh在2005-1-29 14:53:00的发言：

你是怎么验证的？偶附件里是对的呀。

为什么会有问题呢？原因是你用了N()函数，但却在Offset()中用了多单元格区域引用，所以它只能识别L11:L15区域的左上角单元格，所以导致错误。可以这样修改一下，但公式就变长了：)

=MMULT(TRANSPOSE(ROW(A11:A15)^0),N(OFFSET(L10,ROW(A11:A15)-10,(B10:K10="b")))*B11:K15)

[此贴子已经被作者于2005-1-31 12:56:40编辑过]

Bruce_xu

对于gdliyy版主函数公式的功力，小的只有佩服二字。不知版主是如何修炼的？

apolloh

以下是引用gdliyy在2005-1-30 22:58:00的发言：

为什么会有问题呢？原因是你用了N()函数，但却在Offset()中用了多单元格区域引用，所以它只能识别L11:L15区域的左上角单元格，所以导致错误。可以这样修改一下，但公式就变长了：)

=MMULT(TRANSPOSE(ROW(A11:A15)^0),N(OFFSET(L10:M10,ROW(A11:A15)-10,(B10:K10="b")))*B11:K15)

你给的这个不如偶前面修改过那个运算快，因为你多次只使用了每个区域的第一行，虽然公式看上去很不错，但效率。。。嘿嘿！

b23=MMULT(TRANSPOSE(ROW(A11:A15)^0),(L11:L15*(B10:K10="A")+M11:M15*(B10:K10="B"))*B11:K15)

另外经过测试：

OFFSET($L11:$L15,,(G$10="b")))在这里等效于OFFSET($L11:$L15,,(G$10="b"),,)) ，比如偶的单个单元格数组公式就是这么用的=SUM(B11:B15*OFFSET($L11:$L15,,(B$10="b")))，没有出现问题。偶想可能是以数组公式返回了，而不是在一个单元格中引用，所以OFFSET($L11:$L15,,(G$10="b")))的结果以二维的形式出现了。

所以在多单元格数组公式中，OFFSET($L11:$L15,,(G$10:K$10="b"))) 实际不是只引用了左上角的单元格，而是整列，只是因为出现了三维引用，偶用n（）转换成二维后，它只取了第一行。

如果如gdliyy兄所说的理由（只引用了左上角）成立，那么在多单元格数组公式中改成OFFSET($L11:$L15,,(G$10:K$10="b"),,))引用整列就应该可以了，但事实上不是。

由此可见offset的这一用法和我们之前所认知的有所不同。offset的引用范围在数组公式中和单个单元引用中的表现是不同的。

[此贴子已经被作者于2005-1-31 9:03:14编辑过]

gdliyy

apolloh 兄：这样改后，速度肯定没有你现在的公式运算快，所以一般迫不得已才会用N()、T()函数，因为他们必须要求单元格逐格引用，特别在数据多的情况下，速度肯定会慢得多。

其实最初我写的公式也很繁杂： =INdex(Mmult(Transpose(IF(B10:K10="A",L11:L15,M11:M15)),B11:K15),Column()-1,Column()-1) 后来修改： =MMULT(TRANSPOSE(ROW(A11:A15)^0),IF(B10:K10="A",L11:L15,M11:M15)*B11:K15)

或用Index()函数： =SUM(INDEX(IF(B10:K10="A",L11:L15,M11:M15)*B11:K15,,COLUMN()-1))

首公式主要想直接将L11:L15或M11:M15写入Mmult()的首参数来简化公式，未果，反而成为最繁杂的公式：（

apolloh

或用Index()函数： =SUM(INDEX(IF(B10:K10="A",L11:L15,M11:M15)*B11:K15,,COLUMN()-1))

呵呵，这个公式很神奇，佩服！index居然可以这样用。

这里的column（）也起了很大作用，它强迫sum只加总IF(B10:K10="A",L11:L15,M11:M15)*B11:K15返回的数组中对应列的元素。不错，不错。

[此贴子已经被作者于2005-1-31 9:21:39编辑过]

gdliyy

以下是引用apolloh在2005-1-31 9:08:00的发言：

呵呵，这个公式很神奇，佩服！index居然可以这样用。

这里的column（）也起了很大作用，它强迫sum只加总IF(B10:K10="A",L11:L15,M11:M15)*B11:K15返回的数组中对应列的元素。不错，不错。

其实严格来说，我上面写的那个公式还不能算作多单元格联合数组公式，真正的公式应该为： =SUM(INDEX(IF(B10:K10="A",L11:L15,M11:M15)*B11:K15,,COLUMN(B10:K10)-1)) 为什么呢？因为我们将公式： =SUM(INDEX(IF($B$10:$K$10="A",$L11:$L15,$M11:$M15)*$B$11:$K$15,,COLUMN()-1)) 在B23单独输入后，再向右复制，也是可以得出正确结果的！实际上是将普通公式在多单元格中输入，系统会自动根据当前列号来判断取出哪一列来求和得出结果的。

提醒：Index()函数在这里的用法：由于Index()函数不支持直接生成内存数组，不象Offset()函数，但当我们在多单元格中采用联合输入，它的后两个参数是可以用数组来作为参数的。

另外： 1、对于N(+Offset())函数的用法理解，你前面所说的“在多单元格数组公式中，OFFSET($L11:$L15,,(G$10:K$10="b"))) 实际不是只引用了左上角的单元格，而是整列”这个观点我赞同，但是你可能忽略了一点：上面公式产生的引用是L11:L15;M11:M15;L11:L15....M11:M15这样的一个引用，但在外套N()函数的情况下，N()就只能将以上10个引用中的每个区域的左上角单元格进行转换，并不能直接将每列的5个单元格都转换成对应数值。 2、还有你所说的“单个单元格数组公式就是这么用的=SUM(B11:B15*OFFSET($L11:$L15,,(B$10="b")))，”这个公式能得出正确结果，是由于Offset()列偏移参数是一个值，而不是一个数组，如果是=SUM(B11:C15*OFFSET($L11:$L15,,{0,1}))，公式是不能得出正确结果的，所以我们才会考虑在Offset()外套N()函数来实现。

apolloh

另外： 1、对于N(+Offset())函数的用法理解，你前面所说的“在多单元格数组公式中，OFFSET($L11:$L15,,(G$10:K$10="b"))) 实际不是只引用了左上角的单元格，而是整列”这个观点我赞同，但是你可能忽略了一点：上面公式产生的引用是L11:L15;M11:M15;L11:L15....M11:M15这样的一个引用，但在外套N()函数的情况下，N()就只能将以上10个引用中的每个区域的左上角单元格进行转换，并不能直接将每列的5个单元格都转换成对应数值。 2、还有你所说的“单个单元格数组公式就是这么用的=SUM(B11:B15*OFFSET($L11:$L15,,(B$10="b")))，”这个公式能得出正确结果，是由于Offset()列偏移参数是一个值，而不是一个数组，如果是=SUM(B11:C15*OFFSET($L11:$L15,,{0,1}))，公式是不能得出正确结果的，所以我们才会考虑在Offset()外套N()函数来实现。

1、你说的第一点和我说的没有区别吧

2、第二点是因为单个单元格数据公式用=SUM(B11:B15*OFFSET($L11:$L15,,(B$10="b")))产生的是二维数组，所以不会出错。而=SUM(B11:C15*OFFSET($L11:$L15,,{0,1}))产生的是三维数组，所以需要转换。

wuaza

高层对话，小民旁观中，学习中，赞叹中……

shangyu

我看了头晕~~~[em06]

matsumi

这样可以吗？{=SUM(B11:B15*INDEX($L$11:$M$15,0,MOD(COLUMN(),2)+1))}