|
|
楼主 |
发表于 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，只要为我所用就能发挥价值，所谓的正确用法就是主流用法，也许有一天会不正确，学习和研究也是个证伪的过程。
⑧.利用上面的重算原理，我想到了易失函数，因此来两个很另类的方法解决上面参数多维的问题，见另外一个表。
- =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())
- =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())
- =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),))
这也算是一个额外收获吧。
.
实际上，解决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用法都能解决上述问题。
|
|
[复制链接]
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-10-14 20:30
发表于 2011-10-14 20:56
