本帖最后由 hjj0451 于 2012-2-8 07:52 编辑
关于SUBTOTAL-OFFSET、SUBTOTAL-OFFSET-INDIRECT、SUBTOTAL-OFFSET-OFFSET组合：
看了一下楼主的附件内容，部分需要楼主梳理下。
关于SUBTOTAL-OFFSET、SUBTOTAL-OFFSET-INDIRECT、SUBTOTAL-OFFSET-OFFSET组合，会不会出错#N/A与OFFSET各参数的大小对应关系有关。运算的基础还是数组运算法则，只不过是粗象化的，如整体化的行列对应、数组扩展性等，如果把参数整体地去看待就比较容易看的清楚。
楼主的附件主要涉及下面的问题，其它的相关帖子也有论述，归纳一下：
一：形如SUBTOTAL(9,OFFSET(B4:C5,{0;1;2},{0,1}))
B4:C5作为一个"1行1列"偏移基点，按数组自动扩展原则，当参数2和参数3都是单行或单列参数时，{0;1;2}参数2的行数可以任意,如果为行向量列数也可任意；{0,1}参数3列数可以任意，如果为列向量行数也可任意。
注意B4:C5作为一个"1行1列"整体偏移基点，其偏移基点其实只是B4，行数和列数只是决定了返回的引用的行列数，对第2/3参数的行列数大小没有任何要求。
但当OFFSET的第2、3参数为2维参数,要特别注意两个参数的行列数对应问题，如：
SUBTOTAL(9,OFFSET(B4:C5,{0,1,2;3,4,5},{0;1}))是可以的，第2参数从2行1列扩展到2行3列；
SUBTOTAL(9,OFFSET(B4:C5,{0,1,2;3,4,5},{0,1;2,3}))是错误的，第2、3参数无法正确扩展和对应；
SUBTOTAL(9,OFFSET(B4:C5,{0,1;3,4},{0,1;2,3}))则是可以的，参数2、3刚好一一对应。
二：形如SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}),{0;1;2},{0,1}))
一定要注意INDIRECT里两个整体引用之间是逗号还是分号。INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"})作为一个"1行2列"偏移基点，实际是两个偏移基点，要与第2、3参数的行列取得对应关系，以保证数组顺利扩展和对应。如上例子，按数组自动扩展原则，{0;1;2}参数2的行数可以任意,{0,1}参数3只能为1-2列，多余的列部分出错，无法扩展。
如果把偏移基点改为2行1列INDIRECT({"sheet1!b4:C5";"sheet3!b5:C7"}),则第2参数必须由原来的3行改为2行以保证对应关系，第3参数最多也只能是2行，但列数任意。
如果有更多个偏移基点同理，只要和OFFSET第1/2/3参数满足数组运算法则，对应和保证必要时的扩展性即可，举几个正确的例子：
=SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b3:C4";"sheet3!b5:C7","sheet1!b5:C7"}),{0;1},{0,1}))
=SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b3:C4";"sheet3!b5:C7","sheet1!b5:C7"}),{0,1;2,3},{0,1}))
=SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b3:C4";"sheet3!b5:C7","sheet1!b5:C7"}),{0,1},{0,1;2,3}))
=SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b3:C4";"sheet3!b5:C7","sheet1!b5:C7"}),{0,1;2,3},{0,1;2,4}))
如果把INDIRECT部分换为OFFSET($B$4,1,{1,4})与上同理。如SUBTOTAL(9,OFFSET(OFFSET($B$4,1,{1,4}),{0;1;2},{0,1}))，注意红色标点符号确定的大小的对应问题。
三：形如SUBTOTAL(9,OFFSET(INDIRECT({"sheet1!b4:C5";"sheet3!b5:C7"}),{0,1,2},{0;1}))
一定要注意INDIRECT里两个整体引用之间是逗号还是分号。INDIRECT({"sheet1!b4:C5";"sheet3!b5:C7"})作为一个"2行1列"偏移基点，实际是两个偏移基点，要与第2、3参数的行列取得对应关系，以保证数组顺利扩展和对应。如上例子，按数组自动扩展原则，{0,1,2}参数2的列数可以任意,{0;1}参数3只能为1-2行，多余的行部分出错，无法扩展。
如果把偏移基点改为1行2列INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}),则第2参数必须由原来的3列改为2列以保证对应关系，第3参数最多也只能是2列，但行数任意。
如果有更多个偏移基点同理，只要OFFSET第1/2/3参数满足数组运算法则，保证能对应起来、必要时的扩展性即可，可举例如第一部分。
如果INDIRECT部分换为OFFSET($B$4,1,{1;4})与上同理。如SUBTOTAL(9,OFFSET(OFFSET($B$4,1,{1;4}),{0,1,2},{0;1}))，同样注意红色标点符号确定的大小的对应问题。
四：上述问题的根本解释基础也就是数组运算的基本理论。BTW：所谓三维交叉引用的提法也就是数组行/列对应问题。
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-2-7 21:33
楼主
