A表上记载着许多客户的姓名和地址

必赢棋牌 54

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

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

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

必赢棋牌 1

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

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

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

必赢棋牌 2

回答:

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

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

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

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

原创 大海 Excel到PowerBI

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

必赢棋牌 3

必赢棋牌 4

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

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

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

小勤:这么神奇?

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

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

必赢棋牌 5

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

必赢棋牌 6

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

必赢棋牌 7

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

必赢棋牌 8

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

必赢棋牌 9

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

必赢棋牌 10

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

必赢棋牌 11

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

必赢棋牌 12

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

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

必赢棋牌 13

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

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

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

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

    必赢棋牌 14

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

    必赢棋牌 15

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

必赢棋牌 16

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

必赢棋牌 17

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

    必赢棋牌 18

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

必赢棋牌 19

欢迎关注

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

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

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

必赢棋牌 20

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

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

必赢棋牌 21

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

必赢棋牌 22

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

必赢棋牌 23

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

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

必赢棋牌 24

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

必赢棋牌 25

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

必赢棋牌 26

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

必赢棋牌 27

在点击。

必赢棋牌 28

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

=Exel.Workbook([Content],true)

必赢棋牌 29

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

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

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

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

必赢棋牌 30

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

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

必赢棋牌 31

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

必赢棋牌 32

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

必赢棋牌 33

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

删除一些不需要的列。

必赢棋牌 34

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

必赢棋牌 35

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

必赢棋牌 36

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

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

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

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

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

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

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

如下图所示,如何快速将A表的内容快速地整合到B表中去呢?必赢棋牌 37

可以说方法非常多,常见的有函数法(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)

必赢棋牌,此公式的难点依然在于单元格区域的引用。必赢棋牌 38

Index函数:

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

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

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

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

其实,我们解决此问题,完全不用写任何公式,简单的排序+复制粘贴即可解决问题。必赢棋牌 39

技巧:

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

必赢棋牌 40

  • index+match组合

必赢棋牌 41

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

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

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

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

首先不论是哪种方法,“姓名”是两张表共同的关键词,请先分别给两个表格按照“姓名”进行“升序”排列。必赢棋牌 42

vlookup公式法

  • 输入公式

如下图,先给C2单元格输入公式“=VLOOKUP(A2, $A$11:$B$16, 2,
0)”,然后再向下填充,将公式填充到其他单元格。必赢棋牌 43

  • 公式解释

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

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

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

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

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

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

排序法

  • 思路

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

  • 操作步骤

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

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


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

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

必赢棋牌 49

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

A表

必赢棋牌 50

B表

必赢棋牌 51

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

必赢棋牌 52

函数命令的解释:

  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. 输入函数后回车,即可得到结果,如下:

必赢棋牌 53

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

必赢棋牌 54

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

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

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

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

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

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

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

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

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

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

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

get√

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

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

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

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

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图