【菜鸟】对EXCEL函数最多支持四维引用的理解及其验证

CheryBTL

一直在EH学习前辈的贴子，对函数公式尤其是数组公式比较痴迷，但还是水平有限，都是跟在前辈的后面学习。

今天也是一个学习和讨论，主要是关于我对Excel函数多维引用中四维引用的理解与验证，资料来自Excel应用大全(2003版）。

其实这些年前就打算写的，个人有些懒，今天终于狠下心来，小结下。其中要多谢胡版的耐心讲解！

如果不太熟悉数组公式，并对多维引用不熟悉的E友，可以先看看以下几个帖子：
gouweicao78 [讨论] 我对数组与多维引用的认识（欢迎探讨） http://club.excelhome.net/forum.php?mod=viewthread&tid=494347
gouweicao78 [原创] 我对数组与数组运算的认识（欢迎探讨） http://club.excelhome.net/thread-467862-1-1.html

之前无意中看到一个贴子，有位前辈讲Excel函数只能返回最多四维的引用（引用胡版的话：就是数组的数组）？就一直心存疑问，为什么EXCEL函数仅支持四维引用呢？
根据参数的变化完全可以实现所谓的五维、六维引用的！


下面就切入正题，先看看用OFFSET和INDIRECT生成的几个三维与四维引用的例子：B4：H9为原始数据。

图1、原始数据区



一、用OFFSET生成的三维及四维引用

图一.1用OFFSET生成区域的行



图一.2用OFFSET生成区域的列



图一.3用OFFSET生成变区域的列



图一.4用OFFSET生成区域的二维数组（即数组的数组），当时每个作为数组二维数组的大小也是可以进行调整。



二、用INDIRECT生成的多维引用：

说明：sheet3与sheet1在B4：H9位置数据相同。

图二.1、=INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}) 用INDIRECT分别返回sheet1!b4:c5及sheet3!b5:c7单元格区域



图二.2、用INDIRECT的返回结果作为OFFSET的参数，返回四维引用的结果
=OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}),{0;1;2},{0,1})



有了上面的认识，我就开始怀疑：
Excel函数是不是能像其它编程语言或VBA一样在数组的维度上不段的升级下去一样，支持四维以后，至少是五维、六维的应用呢？！

下面是本贴的重点，我们一起来看一下：Excel是否可以实现4维以上的引用呢？或者在4维以上引用时，会出现什么问题？
可以假设这样：如果Excel支持更多维的引用，那把INDIRECT函数的返回区域作为OFFSET的参数，再利用OFFSET本身行、列的偏移特性，就可以达到六维引用了！！！

验证思路：就是拿INDIRECT返回的结果作为OFFSET的第一个参数，然后再利用OFFSET本身的行、列偏移特性，我们就可以得到6维引用了？

那实际上是这样么？？ 下面就来验证下：

验证.1  把图二.2中OFFSET 行偏移量增加一个行（参数为2），变成以下公式看下结果：
=OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}),{0;1;2},{0,1,2})



验证.2  把图二.2中OFFSET 列偏移量增加一个列（参数为3），变成以下公式看下结果：
=OFFSET(INDIRECT({"sheet1!b4:C5","sheet3!b5:C7"}),{0;1;2;3},{0,1})



把行和列都扩展结果是一样的。大家有兴趣的可以看一下附件。

【菜鸟】对EXCEL函数最多支持四维引用的理解及其验证-基于应用大全-2003版.rar (34.38 KB, 下载次数: 282)

2012-2-7 22:37 上传

点击文件名下载附件

我是因为纠结于Excel的数组公式的引用维度很长时候，没有找到好的办法进行确认，所以才想把这些小结下发上来。

最后：附件Excel2003应该大全中关于OFFSET函数生成多维引用的规则：
1、参数rows、columns、height和width即可单独也可部分或全部使用数组；
2、在两个以上参数中使用数组，所有数组的维数必须一致；
3、在两个以上参数中使用相同维度的数组，则数组的尺寸必须相同；
4、在两个以上参数中使用一维数组，若数组的维度不同，则尺寸可以不同；
5、所以参数中的数组都呈纵向或横向排列，则函数返回的以各区域为元素的“数组”也是呈纵向或横向排列；
6、所有参数中的数组都是二维数组排列，则函数返回的以各区域为元素的"数组"也是呈二维排列。


附上各位前辈对数组公式、多维引用的精华贴。
apolloh  [分享] Apolloh 函数和图表原创贴结集 [20060115更新] http://club.excelhome.net/forum.php?mod=viewthread&tid=98880
fdd   [原创] 我也来谈谈：OFFSET多维引用及与数组运算法则的关系 http://club.excelhome.net/forum.php?mod=viewthread&tid=757839
apolloh[原创] 浅谈引用函数产生的多维引用及其应用 http://club.excelhome.net/forum.php?mod=viewthread&tid=98081
apolloh  [原创]RANK的三维引用和COUNTIF的四维引用   http://club.excelhome.net/forum.php?mod=viewthread&tid=146260
胡剑0227 [讨论]有志于成为数组公式高手的不要错过（功力修炼）http://club.excelhome.net/thread-431489-1-1.html
胡剑0227 [分享]为piny发现的INDEX+N+IF用法推波助澜  http://club.excelhome.net/thread-681243-1-1.html
胡剑0227 RAND函数让ROW和COLUMN神秘亮相 http://club.excelhome.net/thread-792409-1-1.html
胡剑0227 用数组基本知识浅析OFFSET数组化应用 http://club.excelhome.net/thread-624390-1-1.html
wangjguo44老师：[分享]我对矩阵乘积（MMULT）与数组相乘的理解 http://club.excelhome.net/thread-624614-1-1.html
等等等等精华贴。

Moxiu

哦，来顶一顶贴！辛苦楼主！学习了！

mschenshan

居然没有回帖。
不过说明一下：我晕【我的道行的差的太远】。

hjj0451

微软技术服务中心给出关于狭义的跨表三维引用解释，再也没找到什么四维等等概念。
四维甚至以上的概念只是研究的人造出来的名词，便于应用和解释问题就可以借鉴，如果自己用别的方法也可以理解的话完全可以弃之。
我的思想里：引用止于三维。
不过纠结于几维没有意义，针对这个问题来讨论很难有信服的结论。
一般是结合某个函数来讨论函数用法。

swyrlbq

佩服楼主的钻研精神，相信对多维引用有了更深层的理解。钦羡。但是楼主引用了很多链接，自己没有做个附件，让读者在一个工作表中分享楼主的心得。谁愿意一个个打链接，而且每个链接都需要大量的时间和心力去阅读。不知我说得对不对？

CheryBTL

hjj0451 发表于 2012-2-7 22:04
微软技术服务中心给出关于狭义的跨表三维引用解释，再也没找到什么四维等等概念。
四维甚至以上的概念只是 ...

hjj0451老师，您是高高手了，对多维引用的理解远在我们之上了
四维引用的概念，我也是在论坛上看到，在大全上逐步认识到的，其实就是数组的数组了！

但认识到这些需要一个过程呀，我就算是把我自己处理的过程写出来而已，因为之前纠结很久，也没找到相关的介绍，只是具体函数的多维引用的用法～～～～

CheryBTL

swyrlbq 发表于 2012-2-7 22:11
佩服楼主的钻研精神，相信对多维引用有了更深层的理解。钦羡。但是楼主引用了很多链接，自己没有做个附件， ...

swyrlbq老师，更新附件了

xinnem

占个位，先顶起来，再慢慢拜读

凤凰羽衣

真的很佩服~虽然我没看出个三四五六来，这种精神还是很敬佩的！
最近在学VBA，不怎么发帖

hjj0451

关于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：所谓三维交叉引用的提法也就是数组行/列对应问题。