ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-4-25 00:25 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖已被收录到知识树中,索引项:VLOOKUP
本帖最后由 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
Vlookup第一参数为数组~2.png
Vlookup第一参数为数组~3.png
Vlookup第一参数为数组~4.png
写贴过程中,其他推荐贴:
自动重算、易失函数与IS类函数对参数多维之返回结果影响探讨(更新最新测试结果)——hjj0451
http://club.excelhome.net/thread-775214-1-1.html
天外飞仙--F9看不到的函数特性验证——hustnzj
http://t.excelhome.net/thread-11824-1-1.html






评分

11

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-4-25 00:26 | 显示全部楼层
自己占楼,以备以后如有其他发现再补充

TA的精华主题

TA的得分主题

发表于 2014-4-25 06:15 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
认真学习了,非常不错。

TA的精华主题

TA的得分主题

发表于 2014-4-25 08:14 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
先占个板凳听课,稍后做小结,嘻嘻

TA的精华主题

TA的得分主题

发表于 2014-4-25 08:28 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
研究精神值得肯定,謝謝樓主無私提供分享 !!!

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

点评

谢谢提供公式。不过这里不考虑使用sumif方式,见表格43-47行说明  发表于 2014-4-25 08:55

TA的精华主题

TA的得分主题

发表于 2014-4-25 08:31 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-4-25 09:59 | 显示全部楼层
请教楼主,用IF我能理解,为什么一定要加上 T 呢?用F9看,有T和没有T返回的结果是一样的呀?

TA的精华主题

TA的得分主题

发表于 2014-4-25 10:38 | 显示全部楼层
本帖最后由 Bodhidharma 于 2014-4-25 10:54 编辑
danny-fei 发表于 2014-4-25 09:59
请教楼主,用IF我能理解,为什么一定要加上 T 呢?用F9看,有T和没有T返回的结果是一样的呀?

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

一式
  1. VLOOKUP(T(IF({1},C19:C21)),$B$2:$C$14,2,)
复制代码

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

二式
  1. 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

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

四式
  1. 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

五式
  1. VLOOKUP({"A004";"A010";"B005"},$B$2:$C$14,2,)
复制代码
的「多單元格數組公式」
這個和四式類似,不過因為沒有使用引用,因此F9就不存在二式中的「引含交叉」情形
在這種情況下,F9返回的會是最左上的值的結果,因此所有的F9結果都會是14


文字說明比較不清楚,可見附件 explanation.rar (5.77 KB, 下载次数: 125)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-4-25 11:39 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
收藏学习!

TA的精华主题

TA的得分主题

发表于 2014-4-25 12:36 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
谢谢8楼的解说,我想明白这个道理:IF({1},C19:C21)这不是一个数组吗?T(IF({1},C19:C21))这也是一个数组,有什么区别呢?用F9,这两个得出的结果是一样的?就这个想不明白。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-6-19 09:43 , Processed in 0.047911 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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