解决Vlookup第一个参数为数组的内存数组计算问题

cleverzhzhf · 发表于 2014-4-25 00:25

本帖最后由 cleverzhzhf 于 2014-4-25 00:44 编辑

对于Vlookup的第一个参数能否使用数组，并通过生成内存数组进行计算？在很多人心中，这基本上是一个否定的答案。如： http://club.excelhome.net/thread-544458-1-1.html
可能有人也有过解答，但目前还没人专题写过相应的帖子，在这里仅做一下初步的举例与提出我的解决方案，欢迎大家讨论。

思路源头：利用INDEX+N+IF结构的思想，对Vlookup的第一个参数进行内存数组的扩展。
这里面使用Vlookup+T+IF({1},区域)的结构，一定注意IF(1,***)与IF({1},***)是不同的。
Lookup是解决第一个参数为数组的一把好手，而且公式又短，效率又高。不过它要求查找的数据区域按照升序排列，就有了很大的局限性。

暂时无法解决第一和第三个参数均为数组的情况。

Vlookup第一个参数为数组的内存数组计算.zip (17.61 KB, 下载次数: 860)
Vlookup第一参数为数组~1.png

写贴过程中，其他推荐贴：
自动重算、易失函数与IS类函数对参数多维之返回结果影响探讨(更新最新测试结果)——hjj0451
http://club.excelhome.net/thread-775214-1-1.html
天外飞仙--F9看不到的函数特性验证——hustnzj
http://t.excelhome.net/thread-11824-1-1.html

cleverzhzhf · 发表于 2014-4-25 00:26

自己占楼，以备以后如有其他发现再补充

jimcomputer · 发表于 2014-4-25 06:15

认真学习了，非常不错。

sweetian · 发表于 2014-4-25 08:14

先占个板凳听课，稍后做小结，嘻嘻

zz5151353 · 发表于 2014-4-25 08:28

研究精神值得肯定,謝謝樓主無私提供分享 !!!

C22
=SUMPRODUCT(SUMIF(B3:B14,C19:C21,C3))

seki_100 · 发表于 2014-4-25 08:31

非常不错

danny-fei · 发表于 2014-4-25 09:59

请教楼主，用IF我能理解，为什么一定要加上 T 呢？用F9看，有T和没有T返回的结果是一样的呀？

Bodhidharma · 发表于 2014-4-25 10:38

本帖最后由 Bodhidharma 于 2014-4-25 10:54 编辑

danny-fei 发表于 2014-4-25 09:59
请教楼主，用IF我能理解，为什么一定要加上 T 呢？用F9看，有T和没有T返回的结果是一样的呀？

這個很複雜…我試著說明一下

一式

VLOOKUP(T(IF({1},C19:C21)),$B$2:$C$14,2,)

复制代码

T+IF，使VLOOKUP第一參數可以接受數組形式，因此返回{14;9;12}這個內存數組

二式

VLOOKUP(C19:C21,$B$2:$C$14,2,)

复制代码

的「一般公式」
這個在顯示結果同上，但是無法形式內存數組
這裡發生的是「隱含交叉」，也就是在第19行輸入的話，就自動變成VLOOKUP(C19,$B$2:$C$14,2,)
在21行輸入，就自動變成VLOOKUP(C21,$B$2:$C$14,2,)
但在第22行輸入的話，因為22和C19:C21無法形成交叉，因此對應不到，返回#VALUE!
關於「隱含交叉」的基本說明，可參考：http://club.excelhome.net/thread-592215-1-1.html

三式

VLOOKUP(C19:C21,$B$2:$C$14,2,)

复制代码

的「單元格數組公式」
由於VLOOKUP第一參數在一般形況下不接受數組形式，因此用數組輸入的話，C19:C21會自動取最左上的值，也就是C19的值
因此返回的結果都會是VLOOKUP(C19,$B$2:$C$14,2,)的值，也就是14
需注意如果以F9來看的話，結果和二式相同

四式

VLOOKUP(C19:C21,$B$2:$C$14,2,)

复制代码

的「多單元格數組公式」
這裡發生的是另一種「隱含交叉」
多單元格數組公式各單元格顯示的值，會依其單元格所在位置與「不被支援的數組」一一對應
因此在顯示值上面，雖然是在C20:C22輸入，但是C20會對應到VLOOKUP第一參數的C19、C21對應到C20、C22對應到C21
因此顯示的結果不管在哪裡輸入都會正確，但是需注意一樣無法形成內存數組，F9返回結果和前兩式一樣
這個現象比較少討論到，可以參考：http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=1065374&pid=7262923

五式

VLOOKUP({"A004";"A010";"B005"},$B$2:$C$14,2,)

复制代码

的「多單元格數組公式」
這個和四式類似，不過因為沒有使用引用，因此F9就不存在二式中的「引含交叉」情形
在這種情況下，F9返回的會是最左上的值的結果，因此所有的F9結果都會是14

文字說明比較不清楚，可見附件

explanation.rar (5.77 KB, 下载次数: 125)

laowuwyx · 发表于 2014-4-25 11:39

收藏学习！

danny-fei · 发表于 2014-4-25 12:36

谢谢8楼的解说，我想明白这个道理：IF({1},C19:C21)这不是一个数组吗？T（IF({1},C19:C21))这也是一个数组，有什么区别呢？用F9，这两个得出的结果是一样的？就这个想不明白。

		自动登录	找回密码
密码			免费注册

[原创] 解决Vlookup第一个参数为数组的内存数组计算问题

评分

点评

评分