OFFSET函数参数“rows”为数组时的问题

chinjingo

坛子太火，一下子就沉了，大侠们请继续关注

chinjingo

各位路过的请帮忙

chinjingo

夜深了，好久得出一个数组公式，O20～R20=SUM(OFFSET($A20,-SUM(OFFSET($A20,{0,0,0,0},SMALL(IF($B20:$N20<"",COLUMN($B20:$N20)),{1,2,3,4})-1)),SMALL(IF($B20:$N20<"",COLUMN($B20:$N20)),{1,2,3,4})-1))

但加上large(上公式，{1,2,3,4})又不能得结果。

请高手继续指教！

鬼狐

想了1个多小时，愣是没想出来
只能作辅助列实现
就不传上来了
晚上占座，明天继续
跟楼主情况一样，做出了内存数组，但是套个SMALL下去之后就不行了，不知道怎么回事

[ 本帖最后由 鬼狐 于 2010-9-28 01:16 编辑 ]

chinjingo

多谢“鬼狐”，

路过的大侠帮忙！

gouweicao78

=LARGE(N(OFFSET($A20,-TEXT($B20:$N20,"0;;0;\0"),COLUMN($A:$M))),COLUMN(A:A))

gouweicao78

N(OFFSET($A20,-TEXT($B20:$N20,"0;;0;\0"),COLUMN($A:$M)))
是你要的内存数组。
其中，text将B20:N20的文本转为0。
【理解】

OFFSET(A20,-2,2)——得到C18单元格，
OFFSET(A20,-6,4)——得到E14单元格……

OFFSET(A20,{-2,-6},{-6,4})——第2、3参数是同向一维数组、尺寸一致，不会衍生其他枝节，因而分别返回C18、E14单元格

再大一点，就是把TEXT得到的13列的数组和COLUMN的13列数组代进去，分开的懂了，合起来的也就没什么难了。

【多维】
1、OFFSET得到多维引用
2、LARGE函数不支持函数产生的多维引用
因此外套N函数来取函数返回的每一个层面的左上角第1个单元格，并转换文本为0，数值返回数值本身。

chinjingo

高手！短短的几个字符“OFFSET($A20,-TEXT($B20:$N20,"0;;0;\0")”替代了我几乎半篇短文，只有佩服！
我都是按表面现象去理解，所以做的公式很长，并且不一定对。

太感谢了！！！

gouweicao78

chinjingo  
我写的，错的O20=LARGE(IF($B20:$N20<"",OFFSET($B20:$N20,-IF($B20:$N20<"",$B20:$N20),0,,),""),COLUMN(A:A))

【为什么错了】
1、-IF(……)得到的结果，与我的TEXT结果是一样的。这部分可以算没错。
2、OFFSET(B20:N20,{-1,-3},0)——效果是什么？
是将B20:N20向上分别偏移1、2行，分别得到B19:N19、B17:N17
这也是多维引用，因为LARGE不支持多维引用，所以需要N函数来取数值
3、为什么N(OFFSET($B20:$N20,-IF($B20:$N20<"",$B20:$N20),0))得到的都是0？
因为N函数只能取每一个引用区域的左上角第一个单元格。

chinjingo

谢谢详细指点！“TEXT($B20:$N20,"0;;0;\0")”，这个东西妙！