菜单

如何整合Excel表格

2019年11月7日 - 办公软件

问题:有两份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个函数抛砖引玉。

图片 40

图片 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方面的问题,可以私信交流,为你答疑解惑。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图