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

gdliyy

原帖由 胡剑0227 于 2009-5-8 20:31 发表
恩，有些确实不行，这只是个指导思想，具体看情况。

不过如INDEX，函数即使不能生产内存数组，不过在直接使用多单元格公式时 数组之间的关联性还是很好地遵循了参数驱动的原则。
常用的有，SMALL，LARGE，LOOK ...

好久没来了，胡兄这个帖子很火啊，辛苦 辛苦！
其实我们在考虑生成内存数组时，往往需要首先考虑如何利用行列关系来构造数组，再利用构造数组的尺寸来生成新的数组，正如前面胡版给的Lookup变形法，这也是大家需要重点考虑的，这也是对于学习数组公式的初学者们应该重点掌握的。。。
但最后可能需要补充一点，像INDEX函数，如果只给出行参数或行参数（单值），其结果即取得INDEX 区域中的某行或某列，也是可以算内存数组的，但可惜的是行、列参数不支持数组常量或内存数组，遗憾啊。。。
而且 目前在MSO2010中也没有解决，我想MS可能是不打算让它支持内存数组了，呵呵。。。

胡剑0227

感谢李版捧场...

gouweicao78

LOOKUP变形，在李兄这一帖就有了：[内存数组之数组构建应用详解]

对于“何谓内存数组”，胡兄心存异议，我仔细分析一下：
1、=SMALL(A1:A10,{1,2,3})——没有争议，内存数组
2、=INDEX(A1:C10,{1;2},0)——有争议，得到A1:C1，结果是一个1行3列的“数组”
3、选择B1:B3，输入=INDEX(A1:A10,{1;2;3})并按Ctrl+Shift+Enter结束，形成的“是一个多单元格数组公式”，但不是内存数组，基本上也没太多争议，因为结果是单值。

对于第2种情况，实际上与=INDEX(A1:C10,1,0)是一致的，也就是将{1;2}仅作为一个单值来考虑。
如果说{1;2}是连续的，换成{1;3}，可能似乎更支持胡兄的看法。但是，我想再把“隐含交叉”（也就是Excel官方称为“绝对交集”）这个概念说明一下：

帮助文件中的定义：
绝对交集：对单元格区域而不是单个单元格的引用按照单个单元格进行计算。如果单元格 C10 包含公式 =B5:B15*5，则 Excel 将单元格 B10 中的值乘以 5，这是因为单元格 B10 和 C10 位于同一行。


【注意】这里是没有按ctrl+shift+enter的计算情况，不妨用=SUM(B5:B15*5)来说更合适，按了三键，成为数组公式，下达多项计算命令之后，“公式求值”过程是完全不一样的。

这个很容易理解。

但是对于语句描述的重点，我抽出来：

对单元格区域而不是单个单元格的引用按照单个单元格进行计算

也就是说，对于B5:B15，只是按照单个B10来计算。

再返回来，INDEX(A1:C10,{1;3},0)——在第1行，仅仅是返回A1:C1，而没有按照LOOKUP数组变形“再利用构造数组的尺寸来生成新的数组”这一个原理，但是符合了“绝对交集”把{1;3}作为单个值来使用的原理。因而，此处的A1:C1作为“数组”并非彼“数组”，即，并非返回A1:C1、A3:C3。

【对比示例】
OFFSET(A1:C1,{0;2},0)——这个则不然，返回了A1:C1、A3:C3——与{0;2}构造的数组的尺寸相匹配，并且可以作为一个整体模块，在内存中调用，比如
=COUNTIF(OFFSET(A1:C1,{0;2},0),"张三"))——可以分别返回A1:C1中有几个张三、A3:C3中有几个张三，得到的还是与{0;2}匹配的数组。

胡剑0227

1. 再返回来，INDEX(A1:C10,{1;3},0)——在第1行，仅仅是返回A1:C1，而没有按照LOOKUP数组变形“再利用构造数组的尺寸来生成新的数组”这一个原理，但是符合了“绝对交集”把{1;3}作为单个值来使用的原理。因而，此处的A1:C1作为“数组”并非彼“数组”，即，并非返回A1:C1、A3:C3。

复制代码

只是没有一次性返回数组而已，INDEX他是分步来返回的...但每一步都是数组，当然这个数组确实很特别。这个数组（暂且称为数组吧，否则又要造概念了，呵呵），大家可以用ROWS,COLUMNS去测试的，可以测试到是多列的。

另外，我的感觉是，概念这个东西本身就是不断发展的，不能用固定的思维来“固化”下来，谁知道我们还会发现什么，以后函数公式又会有什么变化，这个本身就和自然规律、法则所不同的。而即使是自然法则，我们也是在不断认识，我们只能更接近真理，而无法确信我们已抓住真理。当然也请不要因此就认为我对概念的态度是很随意的，呵呵

因此，概念解析非常重要，但还有更重要的东西...


另外，当INDEX第一参数是普通的数组时，比如直接常量数组好了，那么应该和“绝对引用”扯不上任何关系。
改成 绝对交集，呵呵。

[ 本帖最后由 胡剑0227 于 2010-5-6 12:40 编辑 ]

gouweicao78

再一次更正：是“绝对交集”不是“绝对引用”。


另，我并非是纠缠于概念而放弃其他。
只是不希望读者在看到同样“术语”，理解成不一样的问题。
“用”才是真正的目标，如果读者认为“INDEX(A1:C19,{1;3},0)"与OFFSET(A1:C1,{0;2},0)产生的是一个东西，那么用起来就会发现错了。

我也不是坚持原有定义一定准确，我自己在书中也曾对微软帮助文件的描述进行修正。但原则是：下新的定义之前，应该与约定俗成的东西有所区分，避免误解，或者新旧定义放在一起，并予以比较，让人明白你所指的A是A，而不是B。

[ 本帖最后由 gouweicao78 于 2010-5-7 08:47 编辑 ]

leeyocen

需要认真学习一下

excel小徒

再复习一次，这次感觉比第一次学习要容易理解得多，收获较大

chensl01

郁闷,看不到附件.

excel小徒

216楼作业
=SMALL(MID(MMULT(IF(A2:M9="",0,A2:M9),10^(6-ROUNDDOWN(ROW(1:13)/2,0))),TRANSPOSE(ROW(1:7)),1)+100*ROW(1:8),7*(ROW(1:8)-1)+TRANSPOSE(ROW(1:7)))-100*ROW(1:8)

lhx120824

原帖由 胡剑0227 于 2009-5-8 09:43 发表
数组是有方向的，行数组 以半角逗号间隔，列数组以半角分号间隔。在二维数组中可以把分号当换行符理解，一个分号间隔一行。二维数组可以理解成多列合并，也可以理解为多行合并。数组和一堆数的区别是有序性，即有坐标 ...

看到楼主17楼的附图我想到了一个好的应用：
构造乘法口诀表
公式是（选定10*10区域后输入公式后按数组三键一次搞定。）
=ROW(1:10)&"*"&TRANSPOSE(ROW(1:10))&"="&ROW(1:10)*TRANSPOSE(ROW(1:10))
太简单了，多单元格数组公式真神奇，剩下的任务就是格式的问题了。看看实用吗？
构造乘法口诀表.rar (4.61 KB, 下载次数: 28)

2010-5-13 17:18 上传

点击文件名下载附件

====================================================
呵呵，很好，呵呵

[ 本帖最后由 胡剑0227 于 2010-5-13 18:28 编辑 ]