来自 办公软件 2019-11-08 19:08 的文章
当前位置: 澳门三合彩票 > 办公软件 > 正文

一个表中存储的是姓名和电话,关于VLOOKUP函数的

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

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

关于VLOOKUP函数的用法

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

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

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

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

VLOOKUP函数的语法如下:

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

澳门三合彩票 1

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

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

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

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

解释:

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

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

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

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

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

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

澳门三合彩票 2

案例一

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

回答:

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。

刚看了前面几个答案,除了说用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 工 会

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

澳门三合彩票 3

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要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。

澳门三合彩票 4

澳门三合彩票 5

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

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

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

小勤:这么神奇?

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

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

澳门三合彩票 6

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

澳门三合彩票 7

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

澳门三合彩票 8

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

澳门三合彩票 9

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

澳门三合彩票 10

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

澳门三合彩票 11

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

澳门三合彩票 12

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

澳门三合彩票 13

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

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

澳门三合彩票 14

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

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

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

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

    澳门三合彩票 15

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

    澳门三合彩票 16

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

澳门三合彩票 17

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

澳门三合彩票 18

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

    澳门三合彩票 19

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

澳门三合彩票 20

欢迎关注

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

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

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

澳门三合彩票 21

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

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

澳门三合彩票 22

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

澳门三合彩票 23

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

澳门三合彩票 24

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

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

澳门三合彩票 25

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

澳门三合彩票 26

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

澳门三合彩票 27

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

澳门三合彩票 28

在点击。

澳门三合彩票 29

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

=Exel.Workbook([Content],true)

澳门三合彩票 30

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

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

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

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

澳门三合彩票 31

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

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

澳门三合彩票 32

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

澳门三合彩票 33

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

澳门三合彩票 34

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

删除一些不需要的列。

澳门三合彩票 35

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

澳门三合彩票 36

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

澳门三合彩票 37

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

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

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

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

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

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

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

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

一、利用函数法快速解决表格整合

如下图所示,如何快速将A表的内容快速地整合到B表中去呢?澳门三合彩票 38

可以说方法非常多,常见的有函数法(vlookup函数、lookup函数、index函数等)请看下面的公式:

Vlookup函数法:

=VLOOKUP($G3,$A$3:$C$14,MATCH(I$2,$A$2:$C$2,),)
或者
=VLOOKUP($G3,$A$3:$C$14,Column(B1),)

以上函数的难点在单元格的引用,巧妙之处在于利用match函数或者Column函数作为vlookup函数的第3参数,使其变得非常灵活。

Lookup函数:

=LOOKUP($G3,$A$3:B14)

此公式的难点依然在于单元格区域的引用。澳门三合彩票 39

Index函数:

=INDEX($A$3:$C$14,MATCH($G3,$A$3:$A$14,),MATCH(I$2,$A$2:$C$2,))

此函数为经典的Index+match函数嵌套,难点在于引用。要写这个公式,我们不仅要掌握Index函数的用法,而且还必须熟谙match函数的技巧。

因此我认为上面的这三个函数都不是最简单的解决此问题的技巧。

在Sheet1里面的C2:C4单元格输入=VLOOKUP(A2,折旧明细表!A$2:$G$12,7,0)

笔者觉得,若要精确查找数据,由于计算机运算速度很快,可省略排序操作,直接用第四个参数用FALSE即可。

二、最简单的复制粘贴表格整合法

其实,我们解决此问题,完全不用写任何公式,简单的排序+复制粘贴即可解决问题。澳门三合彩票 40

技巧:

1.选中A3:A14区域——按下快捷键Alt+F+T打开Excel选项对话框——单击高级——向下拖动最右侧的滚动条至底部——单击“编辑自定义列表”——在弹出的对话框中单击导入——确定——确定。

2.选中B表第一列中的任意单元格,按下快捷键Alt+H+S+U打开排序对话框,主要关键字选择学号,次序选择自定义,在打开的对话框中下拉到底部,找到第1步导入的序列,单击确定,再次单击确定。这样A、B两表的顺序都一样了。

3.复制A表中的B3:C14区域到B表中的I3:J14即可。

更多精彩内容,敬请关注我的头条号:傲看今朝。对于本篇回答有任何疑问之处,欢迎大家在评论区留言,我会抽时间给大家解答问题。

回答:这个问题比较简单,用函数就可以搞定,而且是Excel函数入门。

这里介绍2个函数抛砖引玉。

  • vlookup

澳门三合彩票 41

  • index+match组合

澳门三合彩票 42

这两个函数都可以跨表查询。

除了使用函数,透视表也可以解决该问题。

具体可以关注我的技巧文章,谢谢。

回答:这里提供两种方法来实现,想要一步到位的请使用vlookup公式,讨厌公式的请使用排序法。

首先不论是哪种方法,“姓名”是两张表共同的关键词,请先分别给两个表格按照“姓名”进行“升序”排列。澳门三合彩票 43

澳门三合彩票 44

=========================

vlookup公式法

  • 输入公式

如下图,先给C2单元格输入公式“=VLOOKUP(A2, $A$11:$B$16, 2, 0)”,然后再向下填充,将公式填充到其他单元格。澳门三合彩票 45

  • 公式解释

第一个参数(A2):表示要查找的内容。我们想在第二张表格中查找“李力”的电话,因此第一个参数自然就是A2了。

第二个参数($A$11:$B$16):表示查找的范围。我们要在第二张表格中先找到“李力”,然后再找到他的电话。因此,这个参数就是第二张表中所有包含姓名和电话的单元格。

第三个参数(2):表示找到匹配项后要得到第几列的结果。这里我们要的是电话,所以是第2列。

第四个参数(0):表示要精确匹配,也就是必须找到姓名一模一样的单元格。

  • 公式注意事项
  1. 使用前一定要给两张表格按照升序进行排序。

2. 由于查找的范围是固定的,第二个参数一定要加上$号(按F4键可以快速添加$),有$表示绝对引用,也就是向下填充公式时内容不会变。

澳门三合彩票 46

Lookup和Vlookup有哪些区别?2009-11-17 14:18Excel查询函数中,Lookup和Vlookup有哪些区别?它们在应用中应该如何把握?请看本文讲解。

排序法

  • 思路

首先将两张表合并成一张表。接着按照姓名进行排序,这样就会看到上下两行都是同一个人的信息。然后使用条件格式下的重复项功能标记出重复的姓名,将重复项整行复制出来。最后,给重复项的奇数行自动填充序列,偶数行保留空白,再按照序列排序就提取出了所有的电话啦。看起来似乎很复杂,其实操作起来是很快的。

  • 操作步骤

1. 如图,将两张表合并成一张,其中的关键字“姓名”放在同一列下,选中整张表,点击“数据”——“排序”,按照“姓名”列升序排列。澳门三合彩票 47
2. 选中所有数据,点击“开始”——“条件格式”——“新建规则”,如图,选择为重复值设置格式,点击“格式”按钮,设置好填充颜色。澳门三合彩票 483. 条件格式标记出了所有有地址和电话的人。点击筛选按钮,选择按照颜色筛选,将这些有颜色的行全部复制出来。在F1和F3输入1和2,选中前面四个单元格,如图。澳门三合彩票 494. 向下填充,使得所有的奇数行都填充上了序列,偶数行空白。澳门三合彩票 505. 选中整张表格,按照F列升序排列,这样奇偶数行就分开了,然后复制电话到G列,删除辅助列F列即可。澳门三合彩票 51

如果不想改变原有表格的顺序,可以在操作前添加好编号。操作完毕后,再按照编号排序,就可以恢复最初的顺序了。


谢谢阅读,欢迎点赞和评论,关注或点击头像可以看更多的内容哦!

回答:首先,把A表和B表合成一个文件。

澳门三合彩票 52

两表中的内容格式示例如下。

A表

澳门三合彩票 53

B表

澳门三合彩票 54

我们在A表后面的电话一列"C2"单元格中加入函数命令,与B表中的数据进行比对“=VLOOKUP(A2,B!A:B,2,FALSE)”

澳门三合彩票 55

函数命令的解释:

  1. “VLOOKUP”是纵向查找函数;

  2. ( )中的“A2”代表A表的单元格A2;“B”代表的是B表(也就是表的名称);“!A:B”代表的是在B表的A列到B列之间查找;“2”代表查找列中的第2列;“FALSE”是判断命令;

  3. 整句命令的含义是,在B表中的A列到B列之间查找,是否有A表中A2单元格的数据,如果有,那么就把第2列的数据显示在A表C2单元格中。也就是在B表中两列数据中查找有没有“张三”这个人,如果有,那么在A表的电话一栏显示B表中“张三”的电话,你要先算好电话一列是查找列的第几列。
  4. 输入函数后回车,即可得到结果,如下:

澳门三合彩票 56

把鼠标移至C2单元格右下角,鼠标变成黑色十字,按住下拉,即可将函数格式复制到下面的单元格,结果如下图:

澳门三合彩票 57

你可以根据表格的实际情况,修改函数中的相关字符来达到最终效果。

回答:第一反应是用VLOOKUP函数来匹配,

不过要注意的是:姓名很容易出现重复的情况

为了避免这种特殊情况,建议先用透视表,查看一下,两个表格哪些姓名有重复

然后给每位客户设置唯一的ID,

再使用ID,作为VLOOKUP函数的索引,来匹配电话号码

这样得出的结果会更加精确

回答:Index+Match函数结合也可完美解决,效果类似于Vlookup,不过后者更便捷。

思路大致如下:首先将两个工作簿放到一个表中(使用Microsoft Query)。

如果有重复项的话,要提前删除重复项。

然后用Index+Match函数或Vlookup函数就可以了。

get√

回答:第一反应,想到的就是引用和匹配函数,首选就是vlookup函数了。

因为A表和B表的共同点就是客户的姓名,通过姓名就可以互相引用对方表格里的地址或者电话了。

详细步骤就不说了,大神们已经给出答案了。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

案例三

  ★Lookup——数与行列比

如何实现通配符查找?

  Lookup的工作职责是什么呢?用一个数与一行或一列数据依次进行比较,发现匹配的数值后,将另一组数据中对应的数值提取出来。

在B2:B7区域中输入公式=VLOOKUP(A2&"*",折旧明细表!$B$2:$G$12,6,0)

  ·工资税率表:用数值比较

澳门三合彩票 58

  根据不同的工资进行不同的税率计算是一个常见的应用。我们来看这张“工资税率查询”表(见图1)。现在要在右侧根据“收入”(F列),直接得到 对应的“税率”(G列)。在计算第1个“税率”时,输入函数公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”,回车,便可得到 “36.00%”。

澳门三合彩票 59

澳门三合彩票 60

本文由澳门三合彩票发布于办公软件,转载请注明出处:一个表中存储的是姓名和电话,关于VLOOKUP函数的

关键词: