谁能帮我解释一下这个execl函数?=VLOOKUP(B3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({"11","14","22","23","27","28","32","33","34","42","43","44","45","46","48","51","53","54","55","57","58","63","66","67"}&"!F:J"),B3)),{"11","14","22","23","2

来源:学生作业帮助网 编辑:六六作业网 时间:2024/12/23 18:40:50
谁能帮我解释一下这个execl函数?=VLOOKUP(B3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({"11","14","22","23","27","28","

谁能帮我解释一下这个execl函数?=VLOOKUP(B3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({"11","14","22","23","27","28","32","33","34","42","43","44","45","46","48","51","53","54","55","57","58","63","66","67"}&"!F:J"),B3)),{"11","14","22","23","2
谁能帮我解释一下这个execl函数?
=VLOOKUP(B3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({"11","14","22","23","27","28","32","33","34","42","43","44","45","46","48","51","53","54","55","57","58","63","66","67"}&"!F:J"),B3)),{"11","14","22","23","27","28","32","33","34","42","43","44","45","46","48","51","53","54","55","57","58","63","66","67"}&"!F:J")),3,FALSE)
谁推荐一本比较好的execl公式的学习的书嘛?
觉得这个太强大了.
这个太嵌套了,里面还有 & 都不知道在里面什么作用

谁能帮我解释一下这个execl函数?=VLOOKUP(B3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({"11","14","22","23","27","28","32","33","34","42","43","44","45","46","48","51","53","54","55","57","58","63","66","67"}&"!F:J"),B3)),{"11","14","22","23","2
这个公式具体的功能,需要联系原工作表来解答.
但是有几个地方还是可以告诉你是什么含义的
其中“{}”括起来的地方应该是数组部分.每一个“”引用的就是数组中的一个元素.
而你说的 & !其实是 &"! 中间有个 引号,如果你经常使用 EXCEL公式,你会发现,当用“!”的时候,表示前面的部分为一个工作表的名称比如 sheet1!A1 (表示Sheet1 中的单元格 A1)
在此例中 {.}&"!F:J "指的是 :
11!F:J (工作表“11”中的F列到J列)
14!F:J (工作表“14”中的F列到J列)
22!F:J .
依次类推,懂了吧?
Countif()当然是很基本的条件计数函数,你可以很简单的在Excel的公式帮助里明白它的含义
Indirect(address)是直接表示address上的值 比如address是$A$1,就反应A1的值.
LOOKUP 在此采用的是 LOOKUP(lookup_value,array)这样一个结构用来在数组中查询(听到数组时不是很陌生?对于你不需要了解的)
Vlookup 当然是最著名的查询函数了
本人没有比较好的EXCEL公式方面的书推荐给你 ,因为我从始至终也没有参考过Excel公式方面的书籍.
其实,如果你想要学习公式的用法,你可以分以下几步来走:
1.练习基础公式.当你对某个函数应用不理解的时候可以点Excel编辑栏上的“fx”,然后输入你所不理解的公式,然后在左下角点查看公式帮助,你可以得到完整的帮助.然后,你需要的就是多练习
2.进阶学习:当你觉得自己的运用公式的水品还需要提高的时候,你可以从网上下载Excel的公式范例,自己学习,或者参加Excel的学习班.你也可以经常浏览EXCEL方面著名的网站,论坛比如:Excel Home
3.走出误区:并不是掌握所有的公式都是必要的,你只需要掌握常用的一些,就足够应付日常工作了.如上的这个公式,虽然看起来很复杂,其实不算一个好的公式.因为太长了,可读性很低,因该可以有更简洁的方式来实现这些功能.
4.进入VBA世界:当你发现公式很难满足你的需求的时候,你可以开始学习VBA(Visual Basic For Application),通过这个编辑器,你可以编写属于自己的公式!当你成为VBA高手的时候,你会发现,复杂公式使用起来实在太麻烦了,还不如来一段VBA代码行的方便.
祝你早日成为EXCEL高手!