ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 17844|回复: 37

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2012-2-7 21:33 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:数组公式
本帖最后由 CheryBTL 于 2012-2-8 07:52 编辑

一直在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、原始数据区

原始数据.jpg

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

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

3-1.jpg

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

3-2.jpg

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

3-3.jpg

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

4-1.jpg

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

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

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

I-1.jpg

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

I-2.jpg

有了上面的认识,我就开始怀疑:
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})

C-1.jpg

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

C-2.jpg

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

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


我是因为纠结于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
等等等等精华贴。


评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-2-7 21:39 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
哦,来顶一顶贴!辛苦楼主!学习了!

TA的精华主题

TA的得分主题

发表于 2012-2-7 21:42 | 显示全部楼层
居然没有回帖。
不过说明一下:我晕【我的道行的差的太远】。

TA的精华主题

TA的得分主题

发表于 2012-2-7 22:04 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
微软技术服务中心给出关于狭义的跨表三维引用解释,再也没找到什么四维等等概念。
四维甚至以上的概念只是研究的人造出来的名词,便于应用和解释问题就可以借鉴,如果自己用别的方法也可以理解的话完全可以弃之。
我的思想里:引用止于三维。
不过纠结于几维没有意义,针对这个问题来讨论很难有信服的结论。
一般是结合某个函数来讨论函数用法。

TA的精华主题

TA的得分主题

发表于 2012-2-7 22:11 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
佩服楼主的钻研精神,相信对多维引用有了更深层的理解。钦羡。但是楼主引用了很多链接,自己没有做个附件,让读者在一个工作表中分享楼主的心得。谁愿意一个个打链接,而且每个链接都需要大量的时间和心力去阅读。不知我说得对不对?

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-2-7 22:41 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
hjj0451 发表于 2012-2-7 22:04
微软技术服务中心给出关于狭义的跨表三维引用解释,再也没找到什么四维等等概念。
四维甚至以上的概念只是 ...

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

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

点评

主贴的参考文献里的第2个链接不是黄朝版主,而是FDD版友。  发表于 2012-2-8 00:55

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-2-7 22:42 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
swyrlbq 发表于 2012-2-7 22:11
佩服楼主的钻研精神,相信对多维引用有了更深层的理解。钦羡。但是楼主引用了很多链接,自己没有做个附件, ...

swyrlbq老师,更新附件了

TA的精华主题

TA的得分主题

发表于 2012-2-7 23:43 | 显示全部楼层
占个位,先顶起来,再慢慢拜读

TA的精华主题

TA的得分主题

发表于 2012-2-7 23:22 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
真的很佩服~虽然我没看出个三四五六来,这种精神还是很敬佩的!
最近在学VBA,不怎么发帖

TA的精华主题

TA的得分主题

发表于 2012-2-8 00:09 | 显示全部楼层
本帖最后由 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:所谓三维交叉引用的提法也就是数组行/列对应问题。





评分

3

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-13 22:32 , Processed in 0.045867 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表