首页办公网络 › VLOOKUP函数是工作中最常用的一种查找函数,alt+拖动照片

VLOOKUP函数是工作中最常用的一种查找函数,alt+拖动照片

问题:有两份Excel表格,A表上记载着许多客户的姓名和地址,B表格上记载着众客户的姓名和电话。请问如何把A表上的姓名与B表上的电话,一一对应的整合在一起?

VLOOKUP函数是工作中最常用的一种查找函数,掌握好VLOOKUP函数能够极大提高工作的效率。

关于VLOOKUP函数的用法

工作问题:

快捷键:

https://weibo.com/2309846073/G8JRJ2ySp

F4:单元格的相对引用和绝对引用的切换。【非常重要】

ctrl+enter:自动填满

alt+拖动照片,自动吸附

true是1,False是0,根据此可以完成很多有趣的事情。

写公式以“白”单元格来写(当前编辑单元格)。

常用公式:

iserror

round():四舍五入,可以控制小数点后第几位

roundup(),无条件进位,-6.2会变为-7

rounddown(),无条件舍去

INT:小于数值的最大整数。

mod:mod(1.23,1)结果为0.23,获得小数部分

row

column

count:对数字个数计数

countif(区域,“”或者单元格代号):比较前15位;=COUNTIF(A2:A3,A2&"*")与=COUNTIF(A2:A3,A2)

图片 1

countifs(区域1,“”或者单元格代号,区域2,“”或者单元格代号,。。。)

图片 2

可用于找重复

sumif在判断时也有15位的限制,解决办法:&“*”

sumifs

图片 3

图片 4

vlookup(匹配值):写false(或者0),准确匹配。并使用绝对引用

第二项的区域的第一列必须保证是纽带(通过它来找)

图片 5

使用通配符     =VLOOKUP(A2&"*",数据源!B:E,4,0)

vlookup匹配时包括单元格的格式

将F4由数值转化为文本匹配   =VLOOKUP(F4&"",$A$2:$C$6,3,0)

将F4由文本转化为数值匹配=VLOOKUP(F12+0,$A$10:$C$14,3,0)

文本和数值都有:=IF(ISNA(VLOOKUP(F20+0,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$18:$C$22,3,0),VLOOKUP(F20+0,$A$18:$C$22,3,0))

最后一项为true(或1),为模糊查找,永远在那一列找比自己小的最大值。

Hlookup:寻找第几行而不是vlookup的第几列。

lookup:没有精确匹配。。。。。。。但不会找到错误值。因此可以根据该特点完成精确查找

图片 6

lookup、的优势:匹配多个条件。(用数组也可以)

图片 7

match(返回在序列中是第几个)和index(寻找第几个数据)加起来可实现vlookup(只能匹配值和文本)的功能,还有其他更多的功能(各种引用);用定义名称和照相可以完成照片的引用。

PS.....criteria:字符串表示

数组:

公式写完后,ctrl +shift+enter
就会成功,如下图,如果手动加上去,是不能成功的。、

图片 8

indirect:

=indirect(“e5”),转化为对单元格的引用

跨表引用时的单引号问题,可以考虑加‘(单引号)

回答:这个可以用VLOOKUP函数解决,这个函数是EXCEL在职场应用中的一个神器,一定要掌握这个函数。

VLOOKUP函数用于首列查找并返回指定列的值,字母“V”表示垂直方向。

“Lookup”的汉语意思是“查找”,在Excel中与“Lookup”相关的函数有三个:VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。

通常,我们会遇到如下工作任务:在Excel中,两张表格(表一、表二)分别记录着很多货物信息,但两张表内的货物品名有一部分是重复的,也有一部分是各自独有的,现需快速将其标识出来。

日期

year(),month(),day(),date()

DATEDIF(start_date,end_date,unit)

=DATEDIF(A1,TODAY(),"Y")计算年数差

=DATEDIF(A1,TODAY(),"M")计算月数差

=DATEDIF(A1,TODAY(),"D")计算天数差

"MD" 起始日期与结束日期的同月间隔天数。 忽略日期中的月份和年份。

"YD" 起始日期与结束日期的同年间隔天数。忽略日期中的年份。

"YM" 起始日期与结束日期的间隔月数。忽略日期中年份

weeknum

图片 9

weekday

图片 10

text:整容函数

图片 11

today无需填参数,直接返回当天日期

=TODAY()2013-9-23now无需填参数,直接返回当天日期+时间

=NOW()

2013-9-23 13:32

date参数为分开的数字(年、月、日),返回日期格式

=DATE(2012,11,12)2012-11-12

EDATE函数和EOMONTH函数:当需要计算某个具体的日期时。例如计算指定日期往前或往后几个月的日期。或者计算指定日期往前或往后几个月的特定月份的月底日期。

假定如下两个表,一个表中存储的是姓名和地址,一个表中存储的是姓名和电话。

VLOOKUP函数的语法如下:

一、功能 
在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的数据。

表一数据

字符串处理

left

right

mid

len():字符长度,汉字算1.

lenb:字节长度,

图片 12

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

二、语法 
标准格式: 
VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

表二数据

表格格式

mmm:英文缩写月份

mmmm:月份英文全写

aaa:六(一二三、、、)

aaaa:星期六(。。。)

在存储姓名和地址的工作表C2单元格输入公式:

其中,第1参数lookup_value为要搜索的值,第2参数table_array为首列可能包含查找值的单元格区域或数组,第3参数col_index_num为需要从table_array中返回的匹配值的列号,第4参数range_lookup用于指定精确匹配或近似匹配模式。

解释:

解决方法:

分列

分列功能:常规与文本的强制转换;

或者使用0+或1*,也可以将文本强制转换为常规或者数值

=VLOOKUP(A2,姓名电话!$A:$B,2,0)

当range_lookup为TRUE、被省略或使用非零数值时,表示近似匹配模式,要求table_array第一列中的值必须按升序排列,并返回小于等于lookup_value的最大值对应列的数据。当参数为FALSE时(常用数字0或保留参数前的逗号代替),表示只查找精确匹配值,返回table_array的第一列中第一个找到的值,精确匹配模式不必对table_array第一列中的值进行排序。

VLOOKUP(‘你要检索的内容或指定单元格’,‘你要检索的范围,检索到内容时返回你检索表的第几列中的内容’,
‘真或假参数真代表查询的表已经排序,假代表没有排序’)

1.利用“Vlookup”查找函数,在表一中,通过表一的品名在表二中进行查找,若能找到匹配数据,返回标识值,否则返回错误值,即可在表一中标识出表一、表二共有数据和表一独有数据。

通配符

?:一个字符;??:两个字符。。。常和“单元格匹配”结合使用。

*:数个字符

“  张~*  ”意为搜索“ 张* ”

鼠标放在右下角变成黑色十字后双击向下填充公式,可得出结果。

如果使用精确匹配模式且第1参数为文本,则可以在第1参数中使用通配符问号(?)和星号(*)。VLOOKUP函数不区分字母大小写。

例:VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE)

操作:在表一D2单元格中输入公式“=VLOOKUP(A2,表二!$A$2:$C$8,3,0)”,回车确认,在将该公式复制到D3:D8单元格,即可得到结果,显示“1”的行为两表共有数据,显示“#N/A”的行则为表一独有数据。

定位

可直接输入定位

图片 13

单元格名字定位

图片 14

给单元格起名字定位

图片 15

根据一定的条件定位单元格

更改批注图形,

图片 16

图片 17

给批注添加图片。

图片 18

图片 19

图片 20

双击自动填满

图片 21

案例一


明:在表SHEET2中检索当前表中A2中的内容,如果检索到,就返回表SHEET2中B2中的内容,因为B2是表SHEET2中的第二列,所以
VLOOKUP的第三个参数,使用2,表示如果满足条件,就返回查询表的第二列,最后的参数FALSE表示‘假’,意思是被查询的表,没有排序,这种情况
下,会从被查询的表中第一行开始,一直查询到结束。

表一中查找后的结果

取消合并单元格后填充单元格

首先利用“定位条件”定位所有的空单元格,输入“=”+“向上箭头”,ctrl+enter,即可。

图片 22

回答:

A3:B7单元格区域为字母等级查询表,表示60分以下为E级、60~69分为D级、70~79分为C级、80~89分为B级、90分以上为A级。D:G列为初二年级1班语文测验成绩表,如何根据语文成绩返回其字母等级?

三、语法解释 
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为: 
VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False) 
1.Lookup_value为“需在数据表第一列中查找的数据”,可以是数值、文本字符串或引用。 
2.Table_array
为“需要在其中查找数据的数据表”,可以使用单元格区域或区域名称等。 
⑴如果 range_lookup 为 TRUE或省略,则 table_array
的第一列中的数值必须按升序排列,否则,函数 VLOOKUP
不能返回正确的数值。 
如果 range_lookup 为 FALSE,table_array 不必进行排序。 
⑵Table_array
的第一列中的数值可以为文本、数字或逻辑值。若为文本时,不区分文本的大小写。 
3.Col_index_num 为table_array 中待返回的匹配值的列序号。 
Col_index_num 为 1 时,返回 table_array 第一列中的数值; 
Col_index_num 为 2 时,返回 table_array
第二列中的数值,以此类推。 
如果Col_index_num 小于 1,函数 VLOOKUP 返回错误值 #VALUE!; 
如果Col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值
#REF!。 
4.Range_lookup 为一逻辑值,指明函数 VLOOKUP
返回时是精确匹配还是近似匹配。如果为 TRUE
或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value
的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP
将返回精确匹配值。如果找不到,则返回错误值 #N/A。

2.在表二中,通过表二的品名在表一中进行查找,若能找到匹配数据,返回标识值,否则返回错误值,即可在表二中标识出表一、表二共有数据和表二独有数据。

删除所有图片

使用“定位条件”

图片 23

或直接选择对象

图片 24

刚看了前面几个答案,除了说用vlookup的方法基本答对之外,人气最高的答案貌似不是提问者所需要的,因为这个答案是指多个工作表的汇总,而不是提问者所需要的横向的合并。

在H3:H13单元格区域中输入=VLOOKUP(G3,$A$3:$B$7,2)

四、应用例子 
A B C D 
1 编号 姓名 工资 科室 
2 2005001 周杰伦 2870 办公室 
3 2005002 萧亚轩 2750 人事科 
4 2005006 郑智化 2680 供应科 
5 2005010 屠洪刚 2980 销售科 
6 2005019 孙楠 2530 财务科 
7 2005036 孟庭苇 2200 工 会

操作:在表二D2单元格中输入公式“=VLOOKUP(A2,表一!$A$2:$C$8,3,0)”并进行与表一同样操作,得到所需结果。

排序

图片 25

用排序做工资条

图片 26

图片 27

每一页都打印第一行

图片 28

图片 29

利用“定位条件”的可见单元格——仅选中显示出来的表格

图片 30

高级筛选

图片 31

同行表示且,不同行表示或

图片 32

图片 33

若使用公司,其上一个表格一定不能写对,写错或者不写均可,但是“条件区域”必须要选择上面的那个单元格(无论错误还是没有写)。

分类汇总前,先排序

数据保护

图片 34

写0也行

分的过于详细:邮件--->创建组

取消分类汇总:

图片 35

自动求和与定位条件“空值”完成跳跃式求和

图片 36

数据条

图片 37

数据透视表:

可以插入->切片器

两级列表

定义名称 indirect

图片 38

如前面的答案,vlookup是方法之一,但是,用vlookup有一个问题,就是只能从一个表读取数据到另一个表,而不能实现两个表的完全整合,比如如果A表上有姓名和地址而B表上没有数据的,从B表读取A表的数据就读不出来,相反也是一样。

图片 39

A列已排序(第四个参数缺省或用TRUE) 
VLOOKUP(2005001,A1:D7,2,TRUE) 等于“周杰伦” 
VLOOKUP(2005001,A1:D7,3,TRUE) 等于“2870” 
VLOOKUP(2005001,A1:D7,4,TRUE) 等于“办公室” 
VLOOKUP(2005019,A1:D7,2,TRUE) 等于“孙楠” 
VLOOKUP(2005036,A1:D7,3,TRUE) 等于“2200” 
VLOOKUP(2005036,A1:D7,4,TRUE) 等于“工 会” 
VLOOKUP(2005036,A1:D7,4) 等于“工 会”

表二中查找后的结果

因此,个人认为最好的办法是用Excel2016的新功能Power
Query(如果是Excel2010或2013可以到微软官方下载插件)。具体实现方法如下:

案例二

若A列没有排序,要得出正确的结果,第四个参数必须用FALAE 
VLOOKUP(2005001,A1:D7,2,FALSE) 等于“周杰伦” 
VLOOKUP(2005001,A1:D7,3,FALSE) 等于“2870” 
VLOOKUP(2005001,A1:D7,4,FALSE) 等于“办公室” 
VLOOKUP(2005019,A1:D7,2,FALSE) 等于“孙楠” 
VLOOKUP(2005036,A1:D7,3,FALSE) 等于“2200” 
VLOOKUP(2005036,A1:D7,4,FALSE) 等于“工 会”

公式说明:

vlookup虽好,然难承大数据之重

原创 大海 Excel到PowerBI

小勤:大海,现在公司的数据量越来越大,现在有订单表和订单明细表,经常要将订单表的一些信息读取到订单明细表里,给相关的部门去用,原来只要几列数还好,vlookup读一下就是了,但现在,经常要很多数,用vlookup要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。

图片 40

图片 41

大海:呵呵,大数据时代嘛。几十列算少的啦,我上次一个项目上的合同表,有差不多300列,而且这还不算真正多的。

小勤:那怎么办啊!有时候按列顺序读还好,很多时候还不是按顺序的,简直就没法整啊。而且,满篇公式的时候,工作表都要跑不起来了。

大海:这个时候用vlookup的确有点吃力了,虽然vlookup是Excel中极其重要的函数,但是,在大数据时代,已经很难承起数据关联合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就哪些列。

小勤:这么神奇?

大海:这段时间PowerQuery的神奇你也不少见了,不用惊讶。现在就告诉你怎么弄。

Step-1:获取订单表数据并仅创建表连接上载

图片 42

Step-2:获取订单明细表数据并仅创建表连接上载(需要直接上传结果的可以选择表)

图片 43

Step-3:回到PowerQuery界面(当然,前面一个步骤如果没有关闭并上载的话,不需要这一步)

图片 44

Step-4:选择要接入外部数据的查询,单击-

图片 45

Step-5:选择要接入的外部表、选择两表之间用于匹配的列(可以是多列匹配,文末以另一个例子该步骤的附图方式说明)

图片 46

Step-6:展开要接入表的列信息,选择要接入的列以及列名显示方式(是否加前缀)

图片 47

Step-7:查看接入的结果,上载数据

图片 48

Step-8:改变数据的加载方式(由“仅创建连接”方式改为“表”,若前面订单明细不是以“仅创建连接”的方式创建,该步骤不需要)

图片 49

小勤:这样真是太方便了,只要选一下匹配要用的列,选择一下要接入哪些列就搞定了!对了,刚才你不是说可以多列匹配吗?原来用vlookup的时候可麻烦了,还得增加辅助列先将那些列连接起来,然后再用辅助列来匹配。

大海:是的。在PowerQuery里也不需要了,只要在选择匹配列时按住ctrl键就可以选择多列了。只是要注意两个表选择匹配列的顺序要一致。如下图所示:

图片 50

小勤:太好了,以后数据列多的时候匹配取数就太简单了。

以上是使用Power
Query代替vlookup实现的两表合并的基本用法(虽然步骤看起来很多,实际关键步骤就2个,都是鼠标点点点就瞬间完成的事情)。

那么,前面提到的,如果两个表间存在的差异数据都要显示,怎么办呢?只要对其中的表间连接类型按以下情况进行适当的选择即可:

  • 左外部:只要订单表(左表)里有的数据,结果表里都会有,但有些因为明细表(右表)里没有,所以匹配过来后会成为null(空值)

    图片 51

  • 右外部:和左外部相反,即明细表(右表)里有的数据,结果表里都会有,但因为订单表(左表)里有部分数据没有,所以合并后用null值表示。

    图片 52

  • 完全外部:不管哪个表里的数据,全都进结果表,对于双方都有一些对方没有的,合并后显示为null值。

图片 53

  • 内部:跟完全外部相反,只有两个表都有的数据,才进结果表。

图片 54

  • 左反:只有订单表(左表)有而明细表(右表)没有的数据,才进结果表。这种用法经常用于检查如哪些订单缺了明细表等。

    图片 55

  • 右反:和左反相反,只有明细表(右表)有而订单表(左表)没有的数据,才进结果表。

图片 56

欢迎关注

我是大海,微软认证Excel专家,企业签约Power BI顾问

让我们一起学习,共同进步!

回答:月末了,各部门报过来的数据,如何合并到一个文件里?

图片 57

过去,我们只能使用VBA或编写SQL语句。

现在,我们只需点击几次鼠标,书写一个公式。

图片 58

6个工作簿,数据结构都是一致的,我们需要把她们合并到一个工作簿里。

图片 59

,找到需要合并的文件夹。

图片 60

文件夹下每一个工作簿都被合并在一起。首列“内容”显示,是二进制数据的意思。

最后一列显示这些工作簿的地址。中间几列分别表示工作簿名称、后缀名、访问时间、修改时间、创建时间和文件属性。

图片 61

点击,进入,中间那几列无用,所以右键单击。

图片 62

如果此时直接点击二进制首列的"展开按钮",会出现错误提示。

图片 63

这是因为,二进制数据无法直接提取。我们需要书写一条公式。

图片 64

在点击。

图片 65

在对话框,保留默认的,在列表框录入公式:

=Exel.Workbook([Content],true)

图片 66

注意,公式函数严格区分大小写(首字母大写)。

函数的第一个参数是需要转换的二进制字段,这个字段可以在右侧列表框双击选择,不必手工录入。

函数的第二个参数是逻辑值,如果原数据有标题行,这里应该添写true。

点击后,新增一列,数据类型显示为,右侧的列表显示了刚刚进行的步骤。

图片 67

随便选择数据的一个单元格,下方预览区会显示这个表的结构。

点击新增列标签右侧“展开按钮”,选择。

图片 68

每一个表会按列方向展开。其中Data数据类型仍然显示。

图片 69

我们再次点击数据列标签右侧的“展开按钮”。

图片 70

展开的数据已经将文件夹下所有工作簿合并在一起。

删除一些不需要的列。

图片 71

只留有效数据列,点击返回Excel。

图片 72

所有数据都已经合并到一个工作簿中。

图片 73

得到的合并数据实际上是一个,右键单击可以数据。

当文件夹下原工作簿内容变更,合并工作簿只要一次,即可更新数据。

展开数据时,如果选择,得到的数据会将同类项求和或计数。

怎么样,是不是比VBA要简单的多啊。

更多财税职场学习资讯,关注秀财网

回答:对于这个问题的回答,都是仁者见仁智者见智的事情。看到题主的需求,我的第一反应就是使用vlookup、index、lookup等函数。然而哪种更为简单呢,这个要根据实际情况而定。在某些情况下,我们甚至一个函数都不用也能快速地将表格整合在一起,比如使用复制粘贴或者Power
Query。

在Sheet1里面如何查找折旧明细表中对应编号下的月折旧额?(跨表查询)

五、关于TRUE和FALSE的应用 
先举个例子,假如让你在数万条记录的表格中查找给定编号的某个人,假如编号已按由小到大的顺序排序,你会很轻松地找到这个人;假如编号没有排序,你只好从上到下一条一条地查找,很费事。 
用VLOOKUP查找数据也是这样,当第一列已排序,第四个参数用TRUE(或确省),Excel会很轻松地找到数据,效率较高。当第一列没有排序,第四
个参数用FALSE,Excel会从上到下一条一条地查找,效率较低。

VLOOKUP函数主要用于在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值,其语法形式为:VLOOKUP(需要查找的值,查找的目标范围,返回目标范围的第几列值,逻辑值)。

转载本站文章请注明出处:vns威尼斯城官网登入 http://www.tiec-ccpittj.com/?p=455

上一篇:

下一篇:

相关文章