ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[讨论] 自动重算、易失函数与IS类函数对参数多维之返回结果影响探讨(更新最新测试结果)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2011-10-14 20:30 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:ISERROR
本帖最后由 hjj0451 于 2011-10-16 11:33 编辑

  自动重算、易失函数、IS类信息函数与引用或参数多维用法似乎是风马牛不相及的问题,本文就尝试来触及一下该话题(目前仅在03/07版上测试),诸君先看一段动画:
   1.gif
   2.gif
2003--自动重算和易失函数对引用返回结果的影响.rar (8.35 KB, 下载次数: 240)

2007-自动重算和易失函数对引用返回结果的影响.rar (17.03 KB, 下载次数: 293)

总结(暂时总结、欢迎大家测试和讨论,目前已在2003/2007版本测试,欢迎2010版本的朋友继续测试):
1.上面的动画或者附件中的第一个表:
  A2:E14的复制粘贴造成了区域G2:K14的重算(G2:K14是对A2:E14采用多单元格数组公式的引用),对VLOOKUP第二参数的区域引用产生了影响,造成了"分列"现象;否则,若VLOOKUP第二参数的区域引用是死的A2:E14,则列参数不能分开产生列效应。

  在另一个例子里,有整行引用,我没有采用复制粘贴,而是在相关行的空白区域直接输入=1,并按CTRL+SHIFT+ENTER结束,就触发了重算,让VLOOKUP返回了正确结果。
  这个地方还有待继续测试。本来想研究易失函数在此处能不能替代上面的复制粘贴操作而发挥作用,从而有了附件中第2个工作表,结果又阴差阳错地意外发现去掉易失函数而只保留IS类函数就可以产生上面的类似功能。
  关于易失函数能不能在此处发挥作用,有待进一步测试。
  关于该点的最新测试结果:07版本上亦适用,但区域引用要借助易失函数来重算,只借助复制粘贴操作不能产生列效应。
2.附件中第二个表:
  人为强行采用IS类的信息函数的IF结构来分离数值列与文本列,从而赋予列参数本来的各列效应,避免只有列参数的第一列发挥作用。此处用易失函数是多余的。
看来此处是意外的发现。
  此处能返回正确结果的IS类函数有islogical、ISNONTEXT、ISTEXT、ISNUMBER、ISBLANK、ISERROR、ISERR、ISNA,另外的ISEVEN\ISODD\ISREF经过测试不行。
  用ISl类信息函数(除上面的三个外),此处可驱动第一个参数(行方向)或第三参数(列方向)产生对应的偏移,以免列参数失去列偏移功能。以istext为例子,假设查找值lookup_value为行方向的文本,col_index_num为一个多列的行数组,其结构为=vlookup(if(istext(lookup_value),lookup_value),table_array, col_index_num, [range_lookup]),或者
=vlookup(lookup_value,table_array, if(istext(col_index_num),,col_index_num), [range_lookup]),其他的IS类函数的用法见后面的讨论。
  以上结构也适用于易失函数rand().
  关于该点的最新测试结果:07版本上也适用,HLOOKUP函数也适用,正测试别的函数。
3.附件已更新,请已下载的朋友重新下载和覆盖。
4.更新最新测试结果并上传2007动画.









评分

11

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 20:31 | 显示全部楼层
本帖最后由 hjj0451 于 2011-10-16 09:35 编辑

2007-自动重算和易失函数对引用返回结果的影响.rar (17.03 KB, 下载次数: 102) 以下内容为附件内容。

温馨提示:以下步骤进行之前,请在工具-选项里勾选自动重算。                                                                                
                                                                                
①.A4:E16有关于水果进口的相关数据如下(见附件)

②.在G4:K16输入多单元格数组公式,=A4:E16,当A4:E16进行复制粘贴等操作时,G4:K16以进行自动重算。        
                        
③.在B20:D23输入多单元格数组公式,=VLOOKUP($A$20:$A$23,$A$4:$E$16,COLUMN(C:E),),VLOOKUP第一参数为多行、第三参数为多列,VLOOKUP如此多维参数用法是不行的。因此只能返回第3列单价对应的结果,第4列产地和底5列生产结果无法在单元格正确返回。        
                                                        
④.下面是见证奇迹的时候了,在B27:E30单元格区域键入多单元格数组公式,=VLOOKUP($A$27:$A$31,$G$4:$K$16,COLUMN(C:E),)。注意在第②步,G4:K16是等于A4:E16的。复制A2:E14表格里任意单元格的数据,原地粘贴,我们会惊喜地发现公式结果完全正确了!

⑤.以上就是自动重算触发后对引用返回结果的影响,原来A4:E16的复制粘贴等常规操作让VLOOKUP的引用区域进行了自动重算,从而改变了VLOOKUP多维引用返回的结果。经过我进一步研究发现,此处仍有隐含交叉引用在作怪,如下的例子将返回错误值。        

⑥.同时在区域的选择上,④中的公式若用=VLOOKUP($A$27:$A$30,$G$4:$K$16,COLUMN(C:E),),将不能执行交叉,第一个或最后一个查询值的结果不能正常返回(取决于你触发重算的行) 。分析此处,应该是区域G4:K16的重算对VLOOKUP第二参数的区域引用产生了影响,造成了"分列"现象;否则,若VLOOKUP第二参数的区域引用是死的A4:E16,则列参数不能分开产生列效应。      
                                                                                
⑦.如上的发现实属偶然,还不是研究的很成熟,里面的机理有点神秘。就算是BUG,只要为我所用就能发挥价值,所谓的正确用法就是主流用法,也许有一天会不正确,学习和研究也是个证伪的过程。   
                                                                                                                                                                           
⑧.利用上面的重算原理,我想到了易失函数,因此来两个很另类的方法解决上面参数多维的问题,见另外一个表。
  1. =IF(ISTEXT(VLOOKUP($A$18:$A$21,$A$1:$E$13,COLUMN(C:E),)),VLOOKUP($A$18:$A$21,$A$1:$E$13,COLUMN(C:E),)&T(NOW()),VLOOKUP($A$18:$A$21,$A$1:$E$13,COLUMN(C:E),)+0*NOW())
复制代码
  1. =IF(ISTEXT(VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),)),VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),)&T(RAND()),VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),)+0*RAND())
复制代码
进一步测试发现,此处是用ISTEXT OR NOT 来对文本列和数值列强行地分开产生列参数效应,与NOW()\RAND()等易失函数没有关系。用下面的公式依然行。
  1. =IF(ISTEXT(VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),)),VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),),VLOOKUP($A$26:$A$29,$A$1:$E$13,COLUMN(C:E),))
复制代码
或者把ISTEXT换成isnumber\islogical\isnontext\iserror\iserr\isblank\isna都能得到正确的结果,遗憾的是iseven/isodd/isref不行。另外,此处可以简写IF第二参数,第三参数则不可以简写。

这也算是一个额外收获吧。
.
实际上,解决VLOOKUP参数三维的方法还有不少(T\N\INDEX作为第三参数),或用INDEX/MATCH也行。以上解决办法只是个尝试。例如:=INDEX(A1:E13,MATCH(A18:A21,A1:A13,),MATCH(B17:D17,A1:E1,))、=VLOOKUP($A$18:$A$21,$A$1:$E$13,index(row(1:5),COLUMN(C:E)),)、单个参数的T(if({1},…和N(if({1},…用法
以及offset甚至mmult用法都能解决上述问题。





2003-自动重算和易失函数对引用返回结果的影响.rar

7.93 KB, 下载次数: 80

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 20:48 | 显示全部楼层
更新完毕,欢迎各位来拍砖。

TA的精华主题

TA的得分主题

发表于 2011-10-14 20:56 | 显示全部楼层
这个包含三维引用么?没电脑,暂时无法测试,占个楼先,呵呵

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 21:01 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 hjj0451 于 2011-10-14 20:05 编辑
shaowu459 发表于 2011-10-14 19:56
这个包含三维引用么?没电脑,暂时无法测试,占个楼先,呵呵

欢迎SHAOWU测试,本贴是从自动重算和易失函数的角度来解决多维引用的返回结果问题、实际上是交叉引用在作怪。在里面用了VLOOKUP($A$18:$A$21,$A$1:$E$13,COLUMN(C:E),)这样的参数多维的例子。

TA的精华主题

TA的得分主题

发表于 2011-10-14 21:05 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
真是善于钻研!
学习。。。。。。

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 21:05 | 显示全部楼层
xpm130 发表于 2011-10-14 20:05
真是善于钻研!
学习。。。。。。

谢谢XPM老师,请多指导和拍砖~

TA的精华主题

TA的得分主题

发表于 2011-10-14 21:06 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
现在哪还有砖可拍,拿起板儿砖拍丫的,那是20世纪60-80年代的事情,那时候遍地都是建筑工地,遍地都是烧的红砖,可谓最为物美价廉且常见的武器了。

现在都是混凝土了,砖好久都没见过了。不过你还真别说现在的砖跟以前的砖还真是不一样,前一段时间我们单位下面修岗亭,用的砖都是带窟窿眼儿的,我还饶有兴致的拿起来看看,挺软的,拍头上也不见得出血,也不知道有啥用?隔音?隔热?

跑题了。看了一会儿,我有个疑问:火龙果在SHEET1里面是美国产的,跑到SHEET2里面怎么变成越南产的了? 到底是哪儿产的你给个准信儿啊。

这个发现还挺不错,不知道能解决大部分的三维引用不能?

我继续研究……

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 21:08 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
smupsu 发表于 2011-10-14 20:06
现在哪还有砖可拍,拿起板儿砖拍丫的,那是20世纪60-80年代的事情,那时候遍地都是建筑工地,遍地都是烧的 ...

谢谢兄弟,我里面叫3维引用有点欠妥,这是我偶然的一个发现。

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-14 21:09 | 显示全部楼层
smupsu 发表于 2011-10-14 20:06
现在哪还有砖可拍,拿起板儿砖拍丫的,那是20世纪60-80年代的事情,那时候遍地都是建筑工地,遍地都是烧的 ...

看一楼动画。是美国产的,呵呵
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-7-20 02:11 , Processed in 0.049704 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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