点击蓝字发送【目录】 送你200篇独家Excel精华教程
全套Excel视频教程,微X扫码观看
编按: 说到跨表提取数据,非常多小伙伴第1反应便是函数如VLOOKUP,或什么INDEX+SMALL+IF万金油公式。其实,倘若提取的是多列数据,有一个被非常多人丢在旮旯里许久许久的Microsoft Query才是王者!它不仅操作简易,轻易处理“一对多”,况且它生成的结果表能够与数据源形成动态链接,数据源变化了,结果亦会动态更新!
今天给大众分享一个很少人用但有奇效功能的Microsoft Query,来帮忙大众处理两个表格“一对多”的数据提取,或说处理用一个表去匹配另一个表生成特定数据的做法。
如下图所示,同一个工作簿里有两个工作表,“分部人员信息表”列出了各分部的员工姓名和对应的主管,“省份营销数据表”列出了每一个员工负责的多个省份以及对应省份的三个月营销数据。此刻需求把两个表按照姓名这列数据汇总到一个表里。
原表
需要的结果 函数咱们就不消了。在之前的《我折腾到半夜,同事用这个Excel技巧,30秒跨表核对数据交给领导!》中,Power Query就打败了函数实现多表匹配。这次Microsoft Query操作更简单,甩函数几条街~~~~~~操作过程
STEP 01 启用Microsoft Query并加载数据
(1)新建一个工作簿,点击【数据】选项卡下【获取外边数据】组里“自其他源自”下拉菜单的“来自Microsoft Query”。
在【选取数据源】窗口“数据库”选项下点击“Excel Files”,勾选下方的“运用[查找向导]创建/编辑查找”,点击确定。
在【选取工作簿】窗口右侧目录里找到数据源所在的位置,在左侧数据库名找到文件,点击确定。
(2)有时系统会提示如下窗口:“数据源中无包括可见的表格”,这个不消管,点击确定。
进入下方左侧的【查找向导】窗口,点击下面的“选项”按钮,打开右侧【表选项】窗口,勾选“系统表”点击确定。
这般【查找向导】窗口就会显现数据源里的工作表了。这是因为Excel把自己的工作表叫做“系统表”,勾选了之后在查找窗口就能看到了。
接下来选中两个工作表分别点击中间的“>”按钮把左侧的“可用的表和列”添加到右侧的“查找结果中的列”,点击下一步。
此时又会弹出一个窗口,提示“‘查询向导’没法继续,由于该表格没法链接到您的查找中。您必须在Microsoft Query中的表格之间拖动字段,人工链接。”这个亦不消管,点击确定。
STEP 02 按需要项匹配数据
此时咱们就进入Microsoft Query窗口,上方是类似EXCEL的菜单栏,中间是表区域,表示了当前咱们添加的两个表以及对应的字段。下方的数据区域便是融合了两个表的结果。
此时候数据区域的结果是杂乱无章的,原由是咱们无给两个表添加关系。两个表里是经过姓名列来一一对应的。
(1)用鼠标选中左边“分部人员信息表”中的“姓名”,将其拖曳到右表“省份营销数据表”中的“姓名”上面,而后松开鼠标。此时在两个表的“姓名”字段之间显现了一条两端带有细小节点的联接线。下方数据区域就立即更新了。
(2)因为有两列相同的姓名,咱们选中其中一列,点击菜单栏【记录】下方的“删除列”。
STEP 03 把结果数据返回到Excel工作表
最后要做的便是把结果返回到EXCEL。
(1)点击菜单栏“SQL”左侧的按钮,将数据返回到Excel。
(2)在EXCEL中显现【导入数据】窗口,咱们选取表示为“表”,位置安置在现有工作表。
返回结果如下:
到此简单的3步咱们完成为了需要的数据匹配,生成为了新的数据表。 优良
咱们发掘Microsoft Query生成的数据便是一张超级表,亦能够直接创建数据透视表或数据透视图。
同期,这张表是和数据源动态链接的。例如咱们修改一下原数据,点击保留关闭。
在返回结果上右键点击刷新。
这般数据就同步过来了。
运用要求
需要重视的是,运用这种办法,必须要保准数据源的规范性。需求工作表不可存在与数据源无关的数据,并且表格第1行径列标题。倘若要实现动态链接,那样工作簿和工作表的名字和位置不可修改。
怎么样,大众学会了吗?是不是比PQ简单,比函数简单? 扫一扫添加老师微X
在线咨询Excel课程
Excel教程关联举荐
月薪上万必学10大excel函数之十:它身负5大绝技,是动态统计之王
世界500强企业Excel求和面试题,我没过,你能过吗?
做聪明自动化表格,减少重复容易自己,非它不可
想要全面系统学习Excel,不妨关注部落窝教育的《1星期Excel直通车》视频课或《Excel极速贯通班》。
《1星期Excel直通车》视频课
包括Excel技巧、函数公式、
数据透视表、图表。
一次购买,永久学习。
最实用接地气的Excel视频课
《1星期Excel直通车》
风趣易懂,快速有效,带您7天学会Excel
38 节视频大课
(已更新完毕,可永久学习)
理论+实操一应俱全
主讲老师: 滴答
Excel技术大神,资深培训师;
课程粉丝100万+;
研发有《Excel小白脱白系列课》
《Excel极速贯通班》。
原价299元
限时特价99元,随时涨价
少喝两杯咖啡,少吃两袋零食
就能习得受用一辈子的Excel职场技能!
长按下面二维码立即购买学习
购课后,加客服微X:blwjymx2领取练习课件
让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文,可直接购买。
|