A表上记载着许多客户的姓名和地址
问题:有两份Excel表格,A表上记载着许多客户的姓名和地址,B表格上记载着众客户的姓名和电话。请问如何把A表上的姓名与B表上的电话,一一对应的整合在一起?
回答:这个可以用VLOOKUP函数解决,这个函数是EXCEL在职场应用中的一个神器,一定要掌握这个函数。
假定如下两个表,一个表中存储的是姓名和地址,一个表中存储的是姓名和电话。
A表上记载着许多客户的姓名和地址。在存储姓名和地址的工作表C2单元格输入公式:
=VLOOKUP(A2,姓名电话!$A:$B,2,0)
鼠标放在右下角变成黑色十字后双击向下填充公式,可得出结果。
回答:
网赌正规网站网址,刚看了前面几个答案,除了说用vlookup的方法基本答对之外,人气最高的答案貌似不是提问者所需要的,因为这个答案是指多个工作表的汇总,而不是提问者所需要的横向的合并。
如前面的答案,vlookup是方法之一,但是,用vlookup有一个问题,就是只能从一个表读取数据到另一个表,而不能实现两个表的完全整合,比如如果A表上有姓名和地址而B表上没有数据的,从B表读取A表的数据就读不出来,相反也是一样。
因此,个人认为最好的办法是用Excel2016的新功能Power
Query(如果是Excel2010或2013可以到微软官方下载插件)。具体实现方法如下:
vlookup虽好,然难承大数据之重
原创 大海 Excel到PowerBI
小勤:大海,现在公司的数据量越来越大,现在有订单表和订单明细表,经常要将订单表的一些信息读取到订单明细表里,给相关的部门去用,原来只要几列数还好,vlookup读一下就是了,但现在,经常要很多数,用vlookup要累屎了。这个订单表还算少的,还很多其他的一张表里都好几十列了。
大海:呵呵,大数据时代嘛。几十列算少的啦,我上次一个项目上的合同表,有差不多300列,而且这还不算真正多的。
小勤:那怎么办啊!有时候按列顺序读还好,很多时候还不是按顺序的,简直就没法整啊。而且,满篇公式的时候,工作表都要跑不起来了。
大海:这个时候用vlookup的确有点吃力了,虽然vlookup是Excel中极其重要的函数,但是,在大数据时代,已经很难承起数据关联合并的重担了,所以微软才在Excel里加了PowerQuery的功能嘛,点点点,你想要哪些列就哪些列。
小勤:这么神奇?
大海:这段时间PowerQuery的神奇你也不少见了,不用惊讶。现在就告诉你怎么弄。
Step-1:获取订单表数据并仅创建表连接上载
Step-2:获取订单明细表数据并仅创建表连接上载(需要直接上传结果的可以选择表)
Step-3:回到PowerQuery界面(当然,前面一个步骤如果没有关闭并上载的话,不需要这一步)
Step-4:选择要接入外部数据的查询,单击-
A表上记载着许多客户的姓名和地址。Step-5:选择要接入的外部表、选择两表之间用于匹配的列(可以是多列匹配,文末以另一个例子该步骤的附图方式说明)
Step-6:展开要接入表的列信息,选择要接入的列以及列名显示方式(是否加前缀)
Step-7:查看接入的结果,上载数据
Step-8:改变数据的加载方式(由“仅创建连接”方式改为“表”,若前面订单明细不是以“仅创建连接”的方式创建,该步骤不需要)
小勤:这样真是太方便了,只要选一下匹配要用的列,选择一下要接入哪些列就搞定了!对了,刚才你不是说可以多列匹配吗?原来用vlookup的时候可麻烦了,还得增加辅助列先将那些列连接起来,然后再用辅助列来匹配。
大海:是的。在PowerQuery里也不需要了,只要在选择匹配列时按住ctrl键就可以选择多列了。只是要注意两个表选择匹配列的顺序要一致。如下图所示:
小勤:太好了,以后数据列多的时候匹配取数就太简单了。
以上是使用Power
Query代替vlookup实现的两表合并的基本用法(虽然步骤看起来很多,实际关键步骤就2个,都是鼠标点点点就瞬间完成的事情)。那么,前面提到的,如果两个表间存在的差异数据都要显示,怎么办呢?只要对其中的表间连接类型按以下情况进行适当的选择即可:
左外部:只要订单表(左表)里有的数据,结果表里都会有,但有些因为明细表(右表)里没有,所以匹配过来后会成为null(空值)
右外部:A表上记载着许多客户的姓名和地址。和左外部相反,即明细表(右表)里有的数据,结果表里都会有,但因为订单表(左表)里有部分数据没有,所以合并后用null值表示。
完全外部:不管哪个表里的数据,全都进结果表,对于双方都有一些对方没有的,合并后显示为null值。
- 内部:跟完全外部相反,只有两个表都有的数据,才进结果表。
左反:只有订单表(左表)有而明细表(右表)没有的数据,才进结果表。这种用法经常用于检查如哪些订单缺了明细表等。
右反:和左反相反,只有明细表(右表)有而订单表(左表)没有的数据,才进结果表。
欢迎关注
我是大海,微软认证Excel专家,企业签约Power BI顾问
让我们一起学习,共同进步!
回答:月末了,各部门报过来的数据,如何合并到一个文件里?
过去,我们只能使用VBA或编写SQL语句。
现在,我们只需点击几次鼠标,书写一个公式。
6个工作簿,数据结构都是一致的,我们需要把她们合并到一个工作簿里。
。
A表上记载着许多客户的姓名和地址。,找到需要合并的文件夹。
文件夹下每一个工作簿都被合并在一起。首列“内容”显示,是二进制数据的意思。
最后一列显示这些工作簿的地址。中间几列分别表示工作簿名称、后缀名、访问时间、修改时间、创建时间和文件属性。
点击,进入,中间那几列无用,所以右键单击。
如果此时直接点击二进制首列的”展开按钮”,会出现错误提示。
这是因为,二进制数据无法直接提取。我们需要书写一条公式。
在点击。
在对话框,保留默认的,在列表框录入公式:
=Exel.Workbook([Content],true)
注意,公式函数严格区分大小写(首字母大写)。
函数的第一个参数是需要转换的二进制字段,这个字段可以在右侧列表框双击选择,不必手工录入。
函数的第二个参数是逻辑值,如果原数据有标题行,这里应该添写true。
点击后,新增一列,数据类型显示为,右侧的列表显示了刚刚进行的步骤。
随便选择数据的一个单元格,下方预览区会显示这个表的结构。
点击新增列标签右侧“展开按钮”,选择。
每一个表会按列方向展开。其中Data数据类型仍然显示。
我们再次点击数据列标签右侧的“展开按钮”。
展开的数据已经将文件夹下所有工作簿合并在一起。
删除一些不需要的列。
只留有效数据列,点击返回Excel。
所有数据都已经合并到一个工作簿中。
得到的合并数据实际上是一个,右键单击可以数据。
当文件夹下原工作簿内容变更,合并工作簿只要一次,即可更新数据。
展开数据时,如果选择,得到的数据会将同类项求和或计数。
怎么样,是不是比VBA要简单的多啊。
更多财税职场学习资讯,关注秀财网
回答:对于这个问题的回答,都是仁者见仁智者见智的事情。看到题主的需求,我的第一反应就是使用vlookup、index、lookup等函数。然而哪种更为简单呢,这个要根据实际情况而定。在某些情况下,我们甚至一个函数都不用也能快速地将表格整合在一起,比如使用复制粘贴或者Power
Query。
一、利用函数法快速解决表格整合
如下图所示,如何快速将A表的内容快速地整合到B表中去呢?
可以说方法非常多,常见的有函数法(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)
此公式的难点依然在于单元格区域的引用。
Index函数:
=INDEX($A$3:$C$14,MATCH($G3,$A$3:$A$14,),MATCH(I$2,$A$2:$C$2,))
此函数为经典的Index+match函数嵌套,难点在于引用。要写这个公式,我们不仅要掌握Index函数的用法,而且还必须熟谙match函数的技巧。
因此我认为上面的这三个函数都不是最简单的解决此问题的技巧。
二、最简单的复制粘贴表格整合法
其实,我们解决此问题,完全不用写任何公式,简单的排序+复制粘贴即可解决问题。
技巧:
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
- index+match组合
这两个函数都可以跨表查询。
除了使用函数,透视表也可以解决该问题。
具体可以关注我的技巧文章,谢谢。
回答:这里提供两种方法来实现,想要一步到位的请使用vlookup公式,讨厌公式的请使用排序法。
首先不论是哪种方法,“姓名”是两张表共同的关键词,请先分别给两个表格按照“姓名”进行“升序”排列。
vlookup公式法
- 输入公式
如下图,先给C2单元格输入公式“=VLOOKUP(A2, $A$11:$B$16, 2,
0)”,然后再向下填充,将公式填充到其他单元格。
- 公式解释
第一个参数(A2):表示要查找的内容。我们想在第二张表格中查找“李力”的电话,因此第一个参数自然就是A2了。
第二个参数($A$11:$B$16):表示查找的范围。我们要在第二张表格中先找到“李力”,然后再找到他的电话。因此,这个参数就是第二张表中所有包含姓名和电话的单元格。
第三个参数(2):表示找到匹配项后要得到第几列的结果。这里我们要的是电话,所以是第2列。
第四个参数(0):表示要精确匹配,也就是必须找到姓名一模一样的单元格。
- 公式注意事项
- 使用前一定要给两张表格按照升序进行排序。
2.
由于查找的范围是固定的,第二个参数一定要加上$号(按F4键可以快速添加$),有$表示绝对引用,也就是向下填充公式时内容不会变。
排序法
- 思路
首先将两张表合并成一张表。接着按照姓名进行排序,这样就会看到上下两行都是同一个人的信息。然后使用条件格式下的重复项功能标记出重复的姓名,将重复项整行复制出来。最后,给重复项的奇数行自动填充序列,偶数行保留空白,再按照序列排序就提取出了所有的电话啦。看起来似乎很复杂,其实操作起来是很快的。
- 操作步骤
1.
如图,将两张表合并成一张,其中的关键字“姓名”放在同一列下,选中整张表,点击“数据”——“排序”,按照“姓名”列升序排列。
2.
选中所有数据,点击“开始”——“条件格式”——“新建规则”,如图,选择为重复值设置格式,点击“格式”按钮,设置好填充颜色。3.
条件格式标记出了所有有地址和电话的人。点击筛选按钮,选择按照颜色筛选,将这些有颜色的行全部复制出来。在F1和F3输入1和2,选中前面四个单元格,如图。4.
向下填充,使得所有的奇数行都填充上了序列,偶数行空白。5.
选中整张表格,按照F列升序排列,这样奇偶数行就分开了,然后复制电话到G列,删除辅助列F列即可。
如果不想改变原有表格的顺序,可以在操作前添加好编号。操作完毕后,再按照编号排序,就可以恢复最初的顺序了。
谢谢阅读,欢迎点赞和评论,关注或点击头像可以看更多的内容哦!
回答:首先,把A表和B表合成一个文件。
两表中的内容格式示例如下。
A表
B表
我们在A表后面的电话一列”C2″单元格中加入函数命令,与B表中的数据进行比对“=VLOOKUP(A2,B!A:B,2,FALSE)”
函数命令的解释:
-
“VLOOKUP”是纵向查找函数;
-
(
)中的“A2”代表A表的单元格A2;“B”代表的是B表(也就是表的名称);“!A:B”代表的是在B表的A列到B列之间查找;“2”代表查找列中的第2列;“FALSE”是判断命令; - 整句命令的含义是,在B表中的A列到B列之间查找,是否有A表中A2单元格的数据,如果有,那么就把第2列的数据显示在A表C2单元格中。也就是在B表中两列数据中查找有没有“张三”这个人,如果有,那么在A表的电话一栏显示B表中“张三”的电话,你要先算好电话一列是查找列的第几列。
- 输入函数后回车,即可得到结果,如下:
把鼠标移至C2单元格右下角,鼠标变成黑色十字,按住下拉,即可将函数格式复制到下面的单元格,结果如下图:
你可以根据表格的实际情况,修改函数中的相关字符来达到最终效果。
回答:第一反应是用VLOOKUP函数来匹配,
不过要注意的是:姓名很容易出现重复的情况
为了避免这种特殊情况,建议先用透视表,查看一下,两个表格哪些姓名有重复
然后给每位客户设置唯一的ID,
再使用ID,作为VLOOKUP函数的索引,来匹配电话号码
这样得出的结果会更加精确
回答:Index+Match函数结合也可完美解决,效果类似于Vlookup,不过后者更便捷。
思路大致如下:首先将两个工作簿放到一个表中(使用Microsoft Query)。
如果有重复项的话,要提前删除重复项。
然后用Index+Match函数或Vlookup函数就可以了。
get√
回答:第一反应,想到的就是引用和匹配函数,首选就是vlookup函数了。
因为A表和B表的共同点就是客户的姓名,通过姓名就可以互相引用对方表格里的地址或者电话了。
详细步骤就不说了,大神们已经给出答案了。
欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。