何以整合Excel表格【永利皇宫】,VLOOKUP函数最常用的10种用法

问题:有两份Excel表格,A表上记载着不少客户的真名和地点,B表格上记载着众客户的姓名和电话。请问怎样把A表上的真名与B表上的电话机,壹壹对应的结缘在协同?

VLOOKUP函数是干活中最常用的1种检索函数,通晓好VLOOKUP函数能够十分的大提升办事的作用。

回答:其一能够用VLOOKUP函数解决,这么些函数是EXCEL在职场应用中的二个神器,一定要精通那一个函数。

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

要是如下四个表,一个表中存款和储蓄的是名址,二个表中存款和储蓄的是真名和电话。

VLOOKUP函数的语法如下:

永利皇宫 1

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

在仓库储存姓名和地方的劳作表C二单元格输入公式:

其中,第1参数lookup_value为要搜索的值,第二参数table_array为首列恐怕包涵查找值的单元格区域或数组,第3参数col_index_num为供给从table_array中回到的相配值的列号,第肆参数range_lookup用于钦定准确相称或看似相称格局。

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

当range_lookup为TRUE、被简单或行使非零数值时,表示近似相称格局,要求table_array第2列中的值必须按升序排列,并赶回小于等于lookup_value的最大值对应列的数量。当参数为FALSE时(常用数字0或保留参数前的逗号取代),表示只查找正确相配值,再次回到table_array的率先列中率先个找到的值,准确相配形式不必对table_array第3列中的值进行排序。

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

若果利用标准相配形式且第一参数为文本,则足以在第一参数中选择通配符问号(?)和星号(*)。VLOOKUP函数不区分字母大小写。

永利皇宫 2

案例一

回答:

A三:B柒单元格区域为字母品级查询表,表示伍21分以下为E级、60~69分为D级、70~79分为C级、80~86分为B级、捌拾陆分以上为A级。D:G列为初贰年级一班语文检查评定战表表,怎么着依照语文战绩重临其字母等第?

刚看了前头多少个答案,除了说用vlookup的方法基本答对之外,名气最高的答案貌似不是提问者所须要的,因为那么些答案是指四个职业表的汇总,而不是提问者所须要的横向的合并。

在H三:H一3单元格区域中输入=VLOOKUP(G叁,$A$三:$B$7,二)

如前方的答案,vlookup是方法之壹,然则,用vlookup有八个难题,正是不得不从一个表读取多少到另3个表,而不能够落成四个表的通通结合,举个例子假使A表上有姓名和地点而B表上从不数据的,从B表读取A表的数额就读不出去,相反也是同样。

永利皇宫 3

由此,个人感到最佳的法子是用Excel201六的新职能Power
Query(借使是Excel20拾或20一三得以到微软官方下载插件)。具体贯彻格局如下:

案例二

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

原创 大海 Excel到PowerBI

小勤:大海,以往厂家的数据量越来越大,今后有订单表和订单明细表,常常要将订单表的一部分新闻读取到订单明细表里,给有关的单位去用,原来只要几列数幸而,vlookup读一下就是了,但现行反革命,平日要很好些个,用vlookup要累屎了。那个订单表还算少的,还诸多其它的一张表里都或多或少十列了。

永利皇宫 4

永利皇宫 5

海洋:呵呵,大数据时期嘛。几十列算少的呐,笔者上次1个种类上的合同表,有大致300列,而且那还不算真的多的。

小勤:那怎么做啊!有时候按列顺序读幸亏,许多时候还不是按梯次的,简直就无法整啊。而且,满篇公式的时候,专门的工作表都要跑不起来了。

海洋:这一年用vlookup的确有点困难了,固然vlookup是Excel中极其首要的函数,不过,在大数据时期,已经很难承起数据涉嫌合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就怎么列。

小勤:这么奇妙?

大海:那段时间PowerQuery的巧妙你也不在少数见了,不用失惊倒怪。以后就告诉您怎么弄。

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

永利皇宫 6

Step-二:获取订单明细表数据并仅创造表连接上载(须求直接上传结果的能够选择表)

永利皇宫 7

Step-三:回到PowerQuery分界面(当然,前边一个手续若是未有平息并上载的话,不必要这一步)

永利皇宫 8

Step-四:选择要连接外部数据的询问,单击-

永利皇宫 9

Step-伍:选拔要接入的外部表、选用两表之间用于相称的列(能够是多列匹配,文末以另二个事例该手续的附图格局注解)

永利皇宫 10

Step-陆:张开要接入表的列新闻,接纳要连接的列以及列名显示情势(是不是加前缀)

永利皇宫 11

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

永利皇宫 12

Step-8:改动多少的加载格局(由“仅创设连接”方式改为“表”,若前方订单明细不是以“仅创制连接”的艺术开创,该步骤不必要)

永利皇宫 13

小勤:那样真是太便宜了,只要选一下11分要用的列,选用一下要接入哪些列就消除了!对了,刚才你不是说能够多列相称吗?原来用vlookup的时候可麻烦了,还得增援列先将那贰个列连接起来,然后再用辅助列来合营。

深海:是的。在PowerQuery里也不需求了,只要在挑选相配列时按住ctrl键就足以选用多列了。只是要小心多个表接纳匹配列的各样要平等。如下图所示:

永利皇宫 14

小勤:太好了,以往数据列多的时候同盟取数就太简单了。

如上是选择Power
Query取代vlookup完结的两表合并的为主用法(就算步骤看起来许多,实际关键步骤就3个,都以鼠标点点点就一下子达成的作业)。

那么,后边提到的,借使三个表间存在的歧异数据都要显示,如何做呢?只要对里面包车型大巴表间连接类型按以下境况开始展览适量的选料就能够:

  • 左外部:假诺订单表(左表)里部分数据,结果表里都会有,但有点因为明细表(右表)里不曾,所以匹配过来后会成为null(空值)

    永利皇宫 15

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

    永利皇宫 16

  • 一起外部:不论是哪个表里的多寡,全都进结果表,对于两岸都有一对对方并未有的,合并后展现为null值。

永利皇宫 17

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

永利皇宫 18

  • 左反:唯有订单表(左表)有而明细表(右表)未有的数目,才进结果表。那种用法平常用来检查如如何订单缺了明细表等。

    永利皇宫 19

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

永利皇宫 20

接待关注

自笔者是大洋,微软认证Excel专家,集团签署Power BI顾问

让大家共同学习,共同升高!

回答:月最终,各部门报过来的数额,怎么样统一到二个文书里?

永利皇宫 21

千古,大家只能选用VBA或编辑SQL语句。

到现在,大家只需点击几遍鼠标,书写五个公式。

永利皇宫 22

四个专门的工作簿,数据结构没什么分歧的,大家须要把她们统壹到三个专门的学问簿里。

永利皇宫 23

,找到需求统一的文书夹。

永利皇宫 24

文本夹下每2个专业簿都被联合在共同。首列“内容”显示,是二进制数据的意趣。

最后1列呈现那一个职业簿的地址。中间几列分别代表专门的工作簿名称、后缀名、访问时间、修改时间、创制时间和文件属性。

永利皇宫 25

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

永利皇宫 26

举个例子那时一贯点击2进制首列的”张开按键”,晤面世错误提醒。

永利皇宫 27

那是因为,二进制数据不只怕直接领取。大家须要书写一条公式。

永利皇宫 28

在点击。

永利皇宫 29

在对话框,保留暗中同意的,在列表框录入公式:

=Exel.Workbook([Content],true)

永利皇宫 30

小心,公式函数严苛分歧轻重缓急写(首字母大写)。

函数的第3个参数是亟需转移的二进制字段,那一个字段能够在左侧列表框双击选用,不必手工业录入。

函数的第2个参数是逻辑值,假若原数据有题目行,那里应该添写true。

点击后,新扩展一列,数据类型展现为,右边的列表彰显了正要进行的步子。

永利皇宫 31

不论选用数据的1个单元格,下方预览区会显得这么些表的构造。

点击新添列标签左边“张开按键”,采用。

永利皇宫 32

每三个表会按列方向拓展。在那之中Data数据类型如故显得。

永利皇宫 33

大家再一次点击数据列标签左侧的“张开开关”。

永利皇宫 34

开始展览的数额现已将文件夹下全部职业簿合并在一同。

剔除一些不要求的列。

永利皇宫 35

只留有效数据列,点击重临Excel。

永利皇宫 36

享有数据都已经济同盟并到三个职业簿中。

永利皇宫 37

收获的联结数据实际上是三个,右键单击可以数据。

当文件夹下原专门的学业簿内容改变,合并专门的学问簿只要1次,就能够更新数据。

进行数据时,如若接纳,得到的数据会将同类项求和或计数。

何以,是否比VBA要简单的多啊。

更加多财政与税收职场学习资源新闻,关心秀财网

回答:对此那些主题材料的对答,都以仁者见仁个抒几见的事体。看到题主的急需,笔者的率先反馈正是选择vlookup、index、lookup等函数。可是哪个种类特别简易吗,这么些要依照实情而定。在好几景况下,大家照旧三个函数都并非也能便捷地将表格整合在壹块,比如利用复制粘贴或然Power
Query。

在Sheet一内部如何搜索折旧明细表中对应编号下的月折旧额?(跨表查询)

一、利用函数法火速化解表格整合

正如图所示,怎么样高效将A表的内容急忙地结合到B表中去呢?永利皇宫 38

能够说方法11分多,常见的有函数法(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函数的第一参数,使其变得卓殊灵活。

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里面包车型大巴C二:C肆单元格输入=VLOOKUP(A2,折旧明细表!A$2:$G$12,7,0)

二、最简便易行的复制粘贴表格整合法

实则,我们缓和此难题,完全不用写任何公式,简单的排序+复制粘贴就可以化解难题。永利皇宫 40

技巧:

1.选中A3:A1四区域——按下快速键Alt+F+T展开Excel选项对话框——单击高等——向下拖动最左侧的滚动条至底部——单击“编辑自定义列表”——在弹出的对话框中单击导入——分明——显明。

2.选中B表第3列中的大四单元格,按下快速键Alt+H+S+U张开排序对话框,主要关键字采纳学号,次序选用自定义,在开发的对话框中下拉到底层,找到第壹步导入的连串,单击分明,再一次单击分明。那样A、B两表的相继都同一了。

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

更加多美丽内容,敬请关怀自己的头条号:傲看今朝。对于本篇回答有别的疑问之处,应接大家在商议区留言,作者会抽时间给大家解答难题。

回答:其一标题比较简单,用函数就可以消除,而且是Excel函数入门。

此处介绍二个函数投石问路。

  • vlookup

永利皇宫 41

  • index+match组合

永利皇宫 42

那四个函数都得以跨表查询。

除了运用函数,透视表也得以解决该难点。

切实能够关切自作者的才干作品,多谢。

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

首先不论是哪一类办法,“姓名”是两张表共同的关键词,请先分别给四个表格依据“姓名”实行“升序”排列。永利皇宫 43

永利皇宫 44

vlookup公式法

  • 输入公式

一般来讲图,先给C二单元格输入公式“=VLOOKUP(A2, $A$1一:$B$1六, 二,
0)”,然后再向下填充,将公式填充到任何单元格。永利皇宫 45

  • 公式解释

首先个参数(A二):表示要寻觅的剧情。大家想在其次张表格中查找“李力”的对讲机,由此首先个参数自然正是A2了。

其次个参数($A$1壹:$B$1六):表示查找的界定。大家要在第二张表格中先找到“李力”,然后再找到她的电话。因而,那个参数正是第一张表中具有包罗姓名和电话的单元格。

其八个参数(二):表示找到相称项后要获得第几列的结果。那里大家要的是电话,所以是第一列。

第多少个参数(0):表示要标准相称,也正是必须找到姓名一样的单元格。

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

2.
由于查找的限量是定点的,第二个参数一定要加上$号(按F四键能够便捷加多$),有$表示相对引用,也正是向下填充公式时内容不会变。

永利皇宫 46

排序法

  • 思路

先是将两张表合并成一张表。接着遵照姓名实行排序,那样就会看到上下两行都以同一个人的新闻。然后使用规范格式下的双重项职能标志出重复的人名,将另行项整行复制出来。最终,给重新项的奇数行活动填充体系,偶数行保留空白,再依据连串排序就领抽取了具备的电话呀。看起来就如很复杂,其实际操作作起来是连忙的。

  • 操作步骤

一.
如图,将两张表合并成一张,当中的主要性字“姓名”放在同等列下,选中整张表,点击“数据”——“排序”,遵照“姓名”列升序排列。永利皇宫 47
贰.
中选全数数据,点击“起首”——“条件格式”——“新建规则”,如图,选用为重复值设置格式,点击“格式”按键,设置好填充颜色。永利皇宫 48三.
标准化格式标志出了颇具有位置和电话的人。点击筛选按键,接纳服从颜色筛选,将那么些有颜色的行全体复制出来。在F一和F3输入1和贰,选中前边八个单元格,如图。永利皇宫 49四.
向下填充,使得全部的奇数行都填充上了类别,偶数行空白。永利皇宫 50五.
中选整张表格,根据F列升序排列,那样奇偶数行就分手了,然后复制电话到G列,删除协助列F列就可以。永利皇宫 51

假定不想更动原来表格的1一,能够在操作前加多好编号。操作结束后,再根据号码排序,就足以过来最初的逐条了。


多谢阅读,应接点赞和评价,关怀或点击头像能够看越来越多的故事情节哦!

回答:第二,把A表和B表合成一个文书。

永利皇宫 52

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

A表

永利皇宫 53

B表

永利皇宫 54

咱俩在A表前边的电话1列”C二”单元格中出席函数命令,与B表中的数据开始展览比对“=VLOOKUP(A贰,B!A:B,2,FALSE)”

永利皇宫 55

函数命令的解释:

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

  2. (
    )中的“A贰”代表A表的单元格A贰;“B”代表的是B表(约等于表的名号);“!A:B”代表的是在B表的A列到B列之间寻觅;“二”代表查找列中的第一列;“FALSE”是推断命令;

  3. 整句命令的意思是,在B表中的A列到B列之间寻找,是不是有A表中A二单元格的数额,假若有,那么就把第2列的多少显示在A表C贰单元格中。也便是在B表中两列数据中寻找有未有“张三”这厮,如果有,那么在A表的电话机壹栏显示B表中“张3”的电话,你要先算好电话一列是索求列的第几列。
  4. 输入函数后回车,就可以获得结果,如下:

永利皇宫 56

把鼠标移至C贰单元格右下角,鼠标变成浅紫蓝十字,按住下拉,就可以将函数格式复制到下边包车型地铁单元格,结果如下图:

永利皇宫 57

你能够遵照表格的实际上景况,修改函数中的相关字符来达到最后效果。

回答:首先反应是用VLOOKUP函数来协作,

唯独要小心的是:姓名很轻松并发重复的图景

为了防止那种奇特别情报况,提议先用透视表,查看一下,八个表格哪些姓名有重复

接下来给各位客户设置唯1的ID,

再利用ID,作为VLOOKUP函数的目录,来相称电话号码

如此那般得出的结果会越加正确

回答:Index+Match函数结合也可周详化解,效果类似于Vlookup,可是后者更便捷。

思路大概如下:首先将七个职业簿放到三个表中(使用Microsoft Query)。

假定有再度项的话,要超前删除重复项。

接下来用Index+Match函数或Vlookup函数就足以了。

get√

回答:先是影响,想到的就是援引和匹配函数,首荐正是vlookup函数了。

因为A表和B表的共同点正是客户的姓名,通过姓名就能够相互引用对方表格里的地址大概电话了。

详尽步骤就背着了,大神们曾经交由答案了。

应接关怀小编的头条号,假使有excel方面包车型大巴难点,能够私信交换,为您回答解惑。

案例三

什么样贯彻通配符查找?

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

永利皇宫 58

永利皇宫 59

案例四

哪些促成模糊查找?

在F一:F玖区域中输入公式=VLOOKUP(E2,$A$2:$B$7,2,1)

永利皇宫 60

案例五

怎么样通过数值查找文本数据、通过文件查找数值数据、同时落到实处数值与公事数据混合查找?

经过数值查找文本数据:在F三:F6区域中输入公式=VLOOKUP(E三&””,$A$二:$C$陆,三,0)

透过文件查找数值数据:在F1一:F13区域中输入公式=VLOOKUP(–E1一,$A$拾:$C$1四,3,0)

并且落到实处数值与公事数据混合查找:在F1玖:F二一区域中输入公式=IF(ISNA(VLOOKUP(E1玖*1,$A$18:$C$22,3,0)),VLOOKUP(E19&””,$A$18:$C$22,3,0),VLOOKUP(E19*1,$A$18:$C$22,3,0))

永利皇宫 61

案例六

网站地图xml地图