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

suidehan

胡剑0227 发表于 2009-5-8 09:59
如果是初学数组，请细细品味下图，最好能说出点什么，想到了就说吧，看看是否和大家的感觉一样。

[ 本帖最 ...

我的感悟是对本题统计条件是一维数组，则结果返回一维数组；统计条件为二维数组则返回二维数组
但为什么转置A1：A8得不到正确的结果，且编译都通不过？谢谢

suidehan

胡剑0227 发表于 2009-5-8 10:44
如果理解了，那么请实现以下数组变形，就作为巩固或作业吧。如果初学，并有志于提高数组技能，那么本帖会让 ...

LOOKUP(lookup_value,  lookup_vector,  [result_vector])
LOOKUP(lookup_value,  array)
此函数在查找过程中array，lookup_vector不能是单行或单列，但本题A!:A12为单列，故需要数组公式作为参数驱动，用到了前面的“行+列”生成二维数组的知识。受益匪浅啊。谢谢
我的一个公式“=LOOKUP(COLUMN(I1:N2)-8+(ROW(I1:N2)-1)*6,MATCH(A1:A12,A1:A12),A1:A12)"

cee1011

在基础公式那个帖子感叹最近很闲，上班可以逛论坛，第二天就忙翻了。晚上十点下班，赶紧补上第一份数组作业。
在帖子第一个附件“提升数组意识”学会通过ROW和column来转换需要几行几列的数组，茅塞顿开啊。作业做出来很有成就感，嘻嘻。
{:soso_e201:}希望能有毅力坚持下去，fighting~~

LOOKUP数组变形_cee.rar (9.12 KB, 下载次数: 11)

2011-12-11 00:33 上传

点击文件名下载附件

cee1011

84楼作业。这个星期忙的都没上来冒泡啊。补上。

cee1011

{:soso_e135:}我发现我只会依样画瓢，比如我想把“提升数组意识”中的数，不排序，只要对角的数对调（这组数组不是正方形的数组，不知可否实现对角数的对换、？？）；那如果是一组正方形的数组呢？？。{:soso_e103:}不知道能不能清楚的我表述。

PS：胡版，是否有公式可以实现行的数据的180度的转置。。。

majianxwyg

好东西，顶了

CheryBTL

今天周末加班不忙，兴趣很浓，正好有时间好好学习，

把今天学习的体会先发上来，再慢慢更新。

（2011-12-17）今天已学习151楼，周末和下周继续关注此贴，一定要把数组公式这块好好领悟下，向EH的前辈们学习～～～！！
——————————————————————————————
更新于2011-12-22，今天终于啃到171楼了～～～～
——————————————————————————————
更新于2011-12-23，今天对胡版的INDEX函数的数组进行分析，终于到211楼～～～～可以开始第二部分的学习，就另起一楼了
——————————————————————————————————————————————————

注：本楼所有的附件和说明都在最后的汇总附件中可以找得到。

——————————————————————————————————————————————————
第一部分——见原题、原题-1，原题-2工作表————————————————————————————————————————————
法1： =SMALL(OFFSET(A1:G1,ROW($A$1:$A$8),),COLUMN($A:$G))        
         =SMALL(OFFSET(A1:G1,ROW(1:8),),COLUMN(A:G))        
         =SMALL(OFFSET(A1,ROW(A1:A8),,,8),COLUMN(A:G))****CheryBTL
         =SMALL(OFFSET($A2:$G2,ROW()-2,),COLUMN(A2:G2))*****moon2778 135楼         
法2： =--RIGHT(SMALL(ROW(A2:G9)*1000+A2:G9,COLUMN($A:$G)+(ROW($A$2:$G$9)-2)*7),3) ***mxqchina
         ***此题将3改成1也行的        
         =--RIGHT(SMALL(ROW(A2:G9)*1000+A2:G9,COLUMN(A2:G9)+(ROW(A2:G9)-2)*7),1)
                下面是胡版的建议改的公式        
         =SMALL(ROW(A2:G9)*1000+A2:G9,COLUMN($A:$G)+(ROW($A$2:$G$9)-2)*7)-ROW(A2:G9)*1000
         =MOD(SMALL(ROW(A2:G9)*1000+A2:G9,COLUMN($A:$G)+(ROW($A$2:$G$9)-2)*7),10)   **********64楼jackeroo

胡版点评：
   希望能从更一般性的角度，从数组通用性出发再提升一下，那么你会收到更大的收获。比如，行和列的运算变成了区域；更重要的是要得到预定的结果矩阵，从源头着手，即从参数着手，参数是驱动，什么样的参数数组形式将得到什么样的返回效果。
   也可以去除公式层的 --RIGHT(,3),使用 -ROW(A2:G9)*1000。
这叫什么么，叫有借有还吧，开始利用 +ROW(A2:G9)*1000 添加行信息，实现排序时保持同一行，现在-ROW(A2:G9)*1000，得到初始的原始值。

个人体会：这个乘以1000，是要根据数据源的情况进行调整的，可以更大如9^9， 原则就是：ROW()与乘数之后的最小值要大于源数据的最大值，把源数据转换成一维数据，再通过SMALL\LARGE等函数进行相应的运算。

第二部分-Lookup数组变形问题—————————————————————————————————————————————————
见——Lookup数组变形问题——
CheryBTL                 =LOOKUP((ROW(I8:K11)-8)*3+COLUMN(I8:K11)-8,ROW(A1:A12),A1:A12)
     N +INDIRECT      =T(INDIRECT("R"&(ROW(I8:K11)-8)*3+COLUMN(I8:K11)-8&"C1",))
23楼-shengyulon       =LOOKUP((ROW($A$1:$A$4)-1)*3+COLUMN($A$1:$C$1),ROW($A$1:$A$12),$A$1:$A$12)

见——Lookup数组变形问题-IF——
转二列： 比葫芦画瓢的：
=IF((COLUMN(A:F)+2*(ROW(1:2)-1))^0,OFFSET(A1,COLUMN(A:F)+2*(ROW(1:2)-1)-1,),)
转三列： 已有公式
=IF((COLUMN(A:D)+4*(ROW(1:3)-1))^0,OFFSET(A1,COLUMN(A:D)+4*(ROW(1:3)-1)-1,),)
转四列： =IF((COLUMN(A:C)+4*(ROW(1:4)-1))^0,OFFSET(A1,COLUMN(A:C)+3*(ROW(1:4)-1)-1,),)

见——Lookup数组变形问题-IFII——中，对53楼zhanhewang的公式用区域数组公式进行重新验证

=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,),)

结果与区域显示结果相同！

个人的体会：
应该通过数组形式的验证才行，不能简单的sum函数进行验证！见Z8:AC10的验证完全符合。

见LOOKUP第一参数练习、LOOKUP讲解
（其实就是Excel2003的帮助了，只是我一直都没特别关注）
函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。当需要指定列或行的位置时，可以使用函数 LOOKUP 的其他形
=LOOKUP("C",{"a","b","c","d";1,2,3,4}) 在数组的第一行中查找“C”，并返回同一列中最后一行的值 (3)
=LOOKUP("bump",{"a",1;"b",2;"c",3}) 在数组的第一行中查找“bump”，并返回同一行中最后一列的值 (2)


题：求1～100各数平方各之各（用SUMX2PY2)  ===相关内容在——LOOKUP第一参数练习工作表中——————————————
338350                 =SUM(ROW(A1:A100)^2)        
338350                 =SUM(ROW(A1:A100)*ROW(A1:A100))        
338350                 =SUMPRODUCT((ROW(A1:A100)^2))        
338350                 =SUMPRODUCT(ROW(A1:A100),ROW(A1:A100))        
338350                 =SUMX2PY2(ROW(A1:A100),ROW(A1:A100)*0)        
338350                 =SUMX2PY2(ROW(1:50),50+ROW(1:50))        
338350                 =SUMX2PY2(IF(MOD(ROW(1:100),2),ROW(1:100),),IF(MOD(ROW(1:100),2),0,ROW(1:100)))        
338350                 =SUMX2PY2(ROW(A1:A100),ROUNDUP(ROW(1:100)/100,0)-1)
338350                 =SUMX2PY2(ROW(A1:A100),ROW(1:100)^0-1)

见 LOOKUP数组变形补充-III
CheryBTL:
倒叙:         =LOOKUP(LARGE(ROW(A1:A12),ROW(A1:A12)),ROW(A1:A12),A1:A12)
间隔取数：=LOOKUP(SMALL(ROW(A1:A12)*2,ROW(A1:A6)),ROW(A1:A12),A1:A12)
奇偶并列：=LOOKUP(SMALL(ROW(A1:A12),(ROW(A1:A6)-1)*2+COLUMN(A1:B1)),ROW(A1:A12),A1:A12)
89楼-weifaxiang:13-用的非常好，当时怎么也想不起来，就用了LARGE来搞定了
倒叙:         =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)
20111221添加（早上突然感觉Vlookup也可以实现，也挻有意思的，就增加到下信息当中，并附表）：
Vlookup 函数的用法达到上述效果：
倒叙:         =VLOOKUP(13-ROW($A$1:$A$12),IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
间隔取数：=VLOOKUP(ROW($A$1:$A$6)*2-1,IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
奇偶并列：=VLOOKUP((ROW($A$1:$A$6))*2-1+{0,1},IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
一排变多排的问题：
一排变二排：=VLOOKUP((ROW($A$1:$A$2)-1)*2+COLUMN(A1:F1),IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
一排变三排：=VLOOKUP((ROW($A$1:$A$3)-1)*3+COLUMN(A1:D1),IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
一排变四排：=VLOOKUP((ROW($A$1:$A$4)-1)*3+COLUMN(A1:C1),IF({1,0},ROW($A$1:$A$12),$A$1:$A$12),2,)
[讨论]_有志于成为数组公式高手的不要错过-VLOOKUP实现数据转换-CheryBTL.rar (4.39 KB, 下载次数: 26)

2011-12-21 10:05 上传

点击文件名下载附件

见——97楼Lookup函数题——
CheryBTL：
=LOOKUP(INT((ROW(C3:E15)-3)/3)+1+(COLUMN(C3:E15)-3)*5,ROW(A1:A18),A3:A20)
98楼wefaxiang方法：
=LOOKUP(INT(ROW($A$3:$A$20)/3)+{2,7,12},ROW($A$3:$A$20),$A$3:$A$20)

第三部分——提升数组意识进一步处理—————————————————————————————————————————————
见——提升数组意识进一步处理——数据合并
MMULT这个矩阵相乘的函数功能真是强大呀！！
数据叠加的，参考前辈的公式了：
=MMULT(J2:P9,10^(7-ROW($1:$7)))
=MMULT(行升序处理,10^(7-ROW(A1:A7)))
数据再分解的公式：
我是通过分解再组合的方式得到的
先用：=--(AF2:AF9=TRANSPOSE(AF2:AF9)) 得到一个8*8的矩阵
再与8*1矩阵相乘，得到8*8 X 8*1=8*1 即最终数据及公式：
=MMULT(--(MMULT(行升序处理,10^(7-ROW(A1:A7)))=TRANSPOSE(MMULT(行升序处理,10^(7-ROW(A1:A7))))),ROW(A1:A8)*0+1)
过程可见附件。

110楼的weifaxiang: 其中用到了N得到0/1、^0得到1是不错的办法！
=MMULT(N(MMULT(行升序处理,10^(7-ROW($1:$7)))=TRANSPOSE(MMULT(行升序处理,10^(7-ROW($1:$7))))),ROW($1:$8)^0)
119楼的piggy_19840215 :其中用到 ROW(1:7)/ROW(1:7) 得到1也值得学习！
=MMULT(10^(7-TRANSPOSE(ROW(1:7)))*J2:P9,ROW(1:7)/ROW(1:7))

见——提升数组意识-II-142楼——数据分解
一维数组： =INDEX(MID(A2,ROW($A$1:$A$8),1),COLUMN(A1:G8))
二维数组：=MID(A2:A9,ROW(A1:A8)^0*TRANSPOSE(ROW($A$1:$G$7)),1)
                =MID(A2:A9,TRANSPOSE(ROW($A$1:$A$7)),1) 根据146楼公式后来改的
过程也是：先由 =TRANSPOSE(ROW($A$1:$G$8))生成1*7列数组 再用 =ROW(A1:A8)^0的8*1的行数组相乘，得到：
8*1 X 1*7=8*7数组，与MID函数匹配得到结果。
二维数组----zhanhewang-146楼:公式特别好，自己在写时，要注意维度要一致才能返回正确的结果，可以超，但不能少！！
还在二维数组加入排序功能！
=MID(A2:A9,COLUMN(A:G),1)
=RIGHT(SMALL((MID(A2:A9,COLUMN(A:G),1)+ROW(1:8)*100),COLUMN(A:G)+(ROW(1:8)-1)*7))

见——106楼题目MMULT——数据出现次数问题统计
=MMULT(--(MMULT(行升序处理,10^(7-ROW(A1:A7)))=TRANSPOSE(MMULT(行升序处理,10^(7-ROW(A1:A7))))),ROW(A1:A8)*0+1)
也是分解得到：先由=--(Z2:Z9=TRANSPOSE(Z2:Z9))生成8*8数组，再与8*1数组相乘得到：8*8 X 8*1=8*1的结果。

对152楼提取的难度提高的练习题分析：
见表“152-难度提高练习”
法1/法2 FROM---157楼\162楼(TEXT函数）-weifaxiang
=IF(--RIGHT(SMALL(IF(ISERR(FIND((COLUMN(A:J)-1)*ROW(A2:A9)^0,A2:A9)),10,(COLUMN(A:J)-1)*ROW(A2:A9)^0)+ROW(A2:A9)*10^3,COLUMN(A:J)+(ROW(A2:A9)-2)*10),3)=10,"",--RIGHT(SMALL(IF(ISERR(FIND((COLUMN(A:J)-1)*ROW(A2:A9)^0,A2:A9)),10,(COLUMN(A:J)-1)*ROW(A2:A9)^0)+ROW(A2:A9)*10^3,COLUMN(A:J)+(ROW(A2:A9)-2)*10),3))
=TEXT(--RIGHT(SMALL(IF(ISERR(FIND((COLUMN(A:J)-1)*ROW(A2:A9)^0,A2:A9)),10,(COLUMN(A:J)-1)*ROW(A2:A9)^0)+ROW(A2:A9)*10^3,COLUMN(A:J)+(ROW(A2:A9)-2)*10),3),"[=10]!"&CHAR(32))
法3     FROM---163楼\165楼-zhanhewang
=TEXT(MOD(SMALL(IF(ISNUMBER(FIND(COLUMN(A:J)-1,A2:A9)),(COLUMN(A:J)-1),10.5)+ROW(1:8)*100,COLUMN(A:J)+(ROW(1:8)-1)*10),100),"[<>10.5]0;;;")

但我由于IF后面的")"位置错了，导致结果错的一塌糊涂。。。
错误位置：IF(ISERR(FIND((COLUMN(A:J)-1)*ROW(A2:A9)^0,A2:A9)),10,(COLUMN(A:J)-1)*ROW(A2:A9)^0+ROW(A2:A9)*10^3)
正确位置：IF(ISERR(FIND((COLUMN(A:J)-1)*ROW(A2:A9)^0,A2:A9)),10,(COLUMN(A:J)-1)*ROW(A2:A9)^0)+ROW(A2:A9)*10^3
分解步骤见附件所示。
[讨论]-提升数组意识-胡剑-EH-CheryBTL-20111223.rar (284.09 KB, 下载次数: 16)

2011-12-23 16:36 上传

点击文件名下载附件

关于174楼INDEX的数组公式的分析：
1、以下两个公式都可以生成两列的1~6;7~12数组，但注意TRANSPOSE的右括号位置是不同的；
=TRANSPOSE(ROW(A1:A6))+6*(ROW(A1:A2)-1)
=TRANSPOSE(ROW(A1:A6)+6*(COLUMN(A1:B1)-1))
2、以下公式都可以生成1\2\3\4;2\3\4\5等形式的内在数组，
胡版在180楼的点评，令我马上眼界开阔呀！：
COLUMN(A:G)这样的行数组 最好使用 transpose(row($1:$7))来实现。比如要生成一个100列的，column(A:X?)，不是很便利。
=COLUMN(A1:D1)+{0;1;2}
=TRANSPOSE(ROW(A1:A4))+{0;1;2} /注：{0;1;2} 可以用ROW(A1:A3)-1代替。
生成3排1/2/3/4;1/2/3/4;1/2/3/4;内在数组，可以用：
=COLUMN(A1:D1)
=COLUMN(A1:D1)+{0;0;0}
=COLUMN(A1:D1)+ROW(A1:A3)^0-1
总公式即为：
=INDEX($C$17:$F$22,TRANSPOSE(ROW(A1:A4))+{0;1;2},COLUMN(A1:D1))等方式即可。
174-胡剑-01-INDEX函数的数组变形-去除公式-分析-CheryBTL-20111223.rar (8.99 KB, 下载次数: 18)

2011-12-23 14:56 上传

点击文件名下载附件

  

[讨论]-提升数组意识-胡剑-EH-CheryBTL-20111223.rar (141.87 KB, 下载次数: 19)

2011-12-23 14:57 上传

点击文件名下载附件

CheryBTL

先再占一楼层提交下次作业了～～～
——————————————————————————————————
2011-12-23 今天终于可以上第二部分的答案了。

下午那会一点办法也没有，加班时看到胡版217楼的IF函数提示，突然灵光一闪，居然自己能写下来了：
不过还是要多亏前面的学习了。
详细分析见表"214-问题答案及分析"
=MOD(SMALL(IF(A2:M9,ROW(A2:M9)*10^3+A2:M9,10^4),(ROW(A2:A9)-2)*7+TRANSPOSE(ROW(A1:A7))),100)

217楼问题——
先用INDEX完成汇总：
=INDEX(A3:T10,ROW(A1:A8)*TRANSPOSE(ROW(A1:A10)^0),ROW(A1:A8)^0*TRANSPOSE(ROW(A1:A10)*2))IF函数嵌套，学习了-223楼-zhanhewang的公式并作了测试，没有问题，但公式运算机制还需要静下心来，再认真考虑！
根据胡版的提示，用CHOOSE函数确实简单了许多：
=CHOOSE(TRANSPOSE(ROW(A1:A10)),B3:B10,D3:D10,F3:F10,H3:H10,J3:J10,L3:L10,N3:N10,P3:P10,R3:R10,T3:T10)

IF函数的嵌套见1246层：
http://club.excelhome.net/forum.php?mod=viewthread&tid=431489&page=125#pid5540697

到377楼胡版的第二阶段小结：
http://club.excelhome.net/forum.php?mod=viewthread&tid=431489&page=38#pid2830881
按维度扩展理论：点可以随意扩展成对应维度的数组；一维列数组可以向列方向扩展的多列的二维数组，一维行数组可以向行方向扩展成多行的二维数组；原本是二维的不具有扩展能力。

数组揉捏技能：把多列合并成一个二维数组，把多列合并成一个更长的列数组。

用函数公式解决问题时，用心意来解题，先不要想具体的函数实现，而是把流程分析出来，功能制定出来，根据功能写模块，把模块串接后就是最后的公式，这样就能提高解决问题的能力。

这这个阶段由于问题复杂化，模块化思想已深入人心，如果第一阶段是血脉畅通内功修炼的筑基阶段，那么本阶段的学习，让大家具备了大硬仗的能力。


月亮同学提到了 迭代运算 这是个秘密武器吧，有兴趣可以看我的那个 循环精灵  的帖子，也可以搜一下其它相关帖子(废话，呵呵）

第二部分-212楼问题分析-胡剑-孕育理论-EH-CheryBTL-20111223.rar (10.41 KB, 下载次数: 11)

2011-12-23 18:31 上传

点击文件名下载附件

第三阶段的任务详细分解：
http://club.excelhome.net/forum.php?mod=viewthread&tid=431489&page=125#pid5579903

cee1011

{:soso_e105:}在mmult那边卡壳，我好讨厌这个函数噢。。爬了一个下午的贴起码想起了矩阵的乘积了【这个当初学矩阵的时候就让我特别头疼了】，可以说知道了是怎么一回事，但是啥情况下可以用mmult这个函数呢。。{:soso_e135:}{:soso_e105:}头痛啊啊啊啊！！
比如下图，为啥会用到mmult公式怎么想到了，虽然公式出来了，但是我不明白这样用到底是什么道理！！！胡版胡版打滚求解释啊啊啊啊啊。。。help~~~~看到我看到我啊啊啊啊啊啊啊
{:soso_e105:}

Excel_MMULT函数应用实例.rar (13.55 KB, 下载次数: 12)

2011-12-17 21:04 上传

点击文件名下载附件

cee1011

胡剑0227 发表于 2009-5-10 22:31
尝试一次定性的方式，体会模块化得优点。
一定定型好比木雕印刷，模块化有活字印刷的味道，这个功能性相对 ...

我对自己感到很难过，我这道题都看不懂~~题目要求都看不懂啊啊啊啊啊啊