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

5128799 · 发表于 2011-6-28 17:59

谢谢你的总结用心良苦
同胡剑版主同样值得敬佩！
谢谢

zoubo604 · 发表于 2011-6-30 00:38

我大致明白了你在53楼批注里说的.
我对OFFSET函数不熟悉,看了一下你的公式,然后自己默写了这个公式:=IF((ROW(A1:A3)-1)*4+COLUMN(A:D),OFFSET(A1,(ROW(A1:A3)-1)*4+COLUMN(A:F)-1,),"")
答案和你的不一样(有图)

,
但是同样实现了一一对应.答案也不是你在53楼批注的那样.
我从你在53楼说的"甚至！=IF(1,OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)也能实现"这句话理解到:
IF实际上不用在条件判断那部分构造几乘几的数组.后面OFFSET都能实现一一对应.关键在于OFFSET中构造数组的结构. 这样理解是否正确?
EXCEL的帮助文件太简单了,根本没说清数组公式应用时OFFSET参数的设置,例子也相当于没有数组公式应用的例子,所以学习起来有些困难.
OFFSET的第二,第三个参数相当于是横纵坐标,为什么在数组公式里实际上ROW(...)+COLUMN(...)只有一个参数(没有用逗号分隔参数),也不会使函数出错?
为什么COLUMN(A:F)-1这里还要减1,还不是很明白,
烦请版主顺便讲一下OFFSET的参数是怎么配置的.
还有一个问题:STEP BY STEP这个贴子删除了吗?我怎么一点链接就提示找不到这个贴子了.

[ 本帖最后由 zoubo604 于 2011-6-30 00:48 编辑 ]

胡剑0227 · 发表于 2011-6-30 06:09

呵呵，记得我那个图是有点问题的，应该是公式和结果没有匹配。

你的问题非藏典型，我想主要就是对数组不够熟悉导致的。

1.使用IF函数实现从空中到地面的说法很妙，它绕过了很多技术性术语，可以说这种解释没有把本质说出来，不过初学的同学一听倒听得很在理，而且马上能领会。
2.你所说的只要OFFSET第二、第三参数是数组就可以了，那么你应该剥离外层的IF函数才是，这个时候你会发现真正起作用的还是IF的第一参数...
3.数组分常量数组、内存数组（公式数组）、单元格区域，因此你所认为的没有逗号【,】分号【;】间隔的子公式就不是数组是不对的。你可以将这部分内容用F9求值试试。或者，有了参数驱动的概念，你应该明白 ROW(A1:A3)这里参数是三个单元格组成的区域，返回值就是三个行信息构造的数组...
4.OFFSET可以想象成一个直角坐标器，第一参数是原点，第二、第三参数是偏移，第四、第五参数用来指定返回区域的行列尺寸，第2，3，4，5参数都能使用数组，在大颗粒阶段会发现第一参数也能使用数组...理解过程就是，一组参数返回一个区域，一一对应，返回值由参数驱动。

5.第二、第三参数是偏移，你上二楼只要爬一层楼梯，就是这个意思...

不过下次最好上个你公式的附件，这样方便查看...

[ 本帖最后由胡剑0227 于 2011-6-30 07:18 编辑 ]

zoubo604 · 发表于 2011-6-30 10:41

胡老师起得真早啊，6点就起来发贴了。敬佩！
对于从天空到地面的例子，我还有以下几点问题及看法。
1、下载的附件是有点问题.中间一个例子公式是对的.上面和下面的公式偏了几个格子,但是仍然能够明白你的意思.
2、OFFSET的功能我试验了几下,大致明白参数的用法了.可以只有第二参数,省略第三.四,五参数.
3、我试了一下,把IF函数去掉,用OFFSET开头,写的公式,按F9数组显示是对的,但是单元格里显示错误,
而左边的公式中,加入了IF(1,......,"")就实现了成功落地.正如你说的实际控制参数还是IF的第一个参数.如图

我的疑惑就是,IF的第一参数使用ROW(A1:A3)-1)*4+COLUMN(A:D)还好理解.而这里IF的第一个参数就是1,怎么就实现了数组的结构控制.而且去掉IF,F9显示是对的,而格子里会出错.真是困惑啊....烦请胡老师进一步指导。
-------------------------------------------------------------------------------------------------------------------
写完上面的后，当我在60楼看到胡老师说的：由于大家对OFFSET有比较深的感情吧，所以喜欢用OFFSET还尝试，我平时使用数组公式时一般不会去碰OFFSET函数，还有像COUNTIF等，这些函数在数组公式应用时都是受限的，不能进行深度加工。
似乎我明白了一点，这是由于这个个函数的本身特性决定的。“Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。”

[ 本帖最后由 zoubo604 于 2011-6-30 11:38 编辑 ]

zoubo604 · 发表于 2011-6-30 11:10

原帖由 jackeroo 于 2009-5-9 14:11 发表
哈哈，版主果然功力深厚，经你这么一解释，我也明白了很多。

掿个顺风车，出道简单题目，利用SUMX2PY2函数，求1到100的自然数的平方和。不得利用常量数组，不用名称。字符无限制。

（本题目位于第61楼）

这个公式这样写对吗：=SUMX2PY2(ROW(1:50),TRANSPOSE(ROW(51:100)))
刚开始我两个参数都写成了1：100，结果一验证，结果多出一倍。
------------------------------------------------------------------------------------------------------
是啊,用SUM方便多了:答案如下:=SUM(ROW(1:100)*ROW(1:100))
================================================================================================
胡剑0227：
我还是那句，能不惊动SUMX2PYX它老人家就不要惊动了，直接用SUM结合基本的数组用法就能实现了。

[ 本帖最后由 zoubo604 于 2011-7-2 11:09 编辑 ]

unnamed · 发表于 2011-6-30 16:33

正在开始学习数组，特来拜读神贴。

qinhouyi · 发表于 2011-6-30 19:12

留个记号，慢慢学习。

zoubo604 · 发表于 2011-7-1 16:45

原帖由 胡剑0227 于 2009-5-10 13:45 发表
89楼同学的答案:
倒叙：=LOOKUP(13-ROW($A$1:$A$12),ROW($A$1:$A$12),$A$1:$A$12)
间隔取数：=LOOKUP(2*ROW($1:$6)-1,ROW($A$1:$A$12),$A$1:$A$12)
奇偶并列：=LOOKUP(2*ROW($1:$6)-{1,0},ROW($A$1:$A$12),$A$1:$A$12)
向版主讨教。
---------------------------------------------------------------------------------------
呵呵，正确呀。
如果这是本帖的收获那替你开心，呵呵

-----------------------------------------------------------------------------
前两个我写的都和这位同学一样了.但第三个,我觉得和前面的变形没区别,奇偶分列,实际上就是变成2列6行.因此我的公式如下
=LOOKUP((ROW(A1:A6)-1)*2+COLUMN(A:B),ROW(A1:A12),A1:A12),貌似我偷懒了.应该换种方式解答.他的这个方法我学习了...
图: 84楼作业.lookup变形补充.jpg

遗憾的是,好几个公式都不是一气写成.都是修改了一两次.还需多练习啊!

[ 本帖最后由 zoubo604 于 2011-7-1 17:23 编辑 ]

胡剑0227 · 发表于 2011-7-1 18:40

我的疑惑就是,IF的第一参数使用ROW(A1:A3)-1)*4+COLUMN(A:D)还好理解.而这里IF的第一个参数就是1,怎么就实现了数组的结构控制.而且去掉IF,F9显示是对的,而格子里会出错.真是困惑啊....烦请胡老师进一步指导。
-------------------------------------------------------------------------------------------------------------------
写完上面的后，当我在60楼看到胡老师说的：由于大家对OFFSET有比较深的感情吧，所以喜欢用OFFSET还尝试，我平时使用数组公式时一般不会去碰OFFSET函数，还有像COUNTIF等，这些函数在数组公式应用时都是受限的，不能进行深度加工。
似乎我明白了一点，这是由于这个个函数的本身特性决定的。“Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。”

先回答下面一个问题吧，认识是在不断递进的，随着对EXCEL函数公式特性理解的加深，我深深感到COUNTIF等函数还是独具魅力的。处在不同的阶段就会有不同的感受，呵呵。

再回答第一个问题，除了使用IF函数意外，将OFFSET定义名称后使用也能依照期望的形式返回函数结果。相同点都是在内存中走了一圈，提取了区域的值...

OFFSET的返回值是一个区域，把一个区域放置到单元格中显然是放不下的，除非EXCEL智能的以单元格为单位对区域进行切割，我们发现EXCEL并没有这么做，所以返回类型错误#VALUE!.(估计这个比较难理解，不接受也没有关系）
另外，我感觉你对这部分的穷追不舍有点为时过早。这部分有点后数组公式的味道，呵呵。你的数组公式才刚刚起步，所以我感觉先不用对这个问题过多思考，这个不是本帖的主要内容，呵呵。我印象中，帖子的这部分也提到过可以跨不过去，先不深究，呵呵。

[ 本帖最后由胡剑0227 于 2011-7-1 18:52 编辑 ]

正弋老兵 · 发表于 2011-7-1 18:48

J2:p9=SMALL(OFFSET(A1:G1,ROW(1:8),),COLUMN(A:G))
推荐二楼的公式

原因非常简单, 能简就不繁

		自动登录	找回密码
密码			免费注册

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

回复 742楼 Leo20100201 的帖子

我的从天空到地面

疑惑