外链论坛

 找回密码
 立即注册
搜索
查看: 20|回复: 1

别再粘粘粘了!3步,搞定excel跨表提取数据!【Excel教程】

[复制链接]

3003

主题

119

回帖

9915万

积分

论坛元老

Rank: 8Rank: 8

积分
99159222
发表于 2024-10-2 14:16:18 | 显示全部楼层 |阅读模式

点击蓝字发送【目录】

送你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极速贯通班」

↓ 点击阅读原文,可直接购买。

回复

使用道具 举报

3046

主题

2万

回帖

9909万

积分

论坛元老

Rank: 8Rank: 8

积分
99098928
发表于 2024-10-9 13:03:13 | 显示全部楼层
交流如星光璀璨,点亮思想夜空。
回复

使用道具 举报

3046

主题

2万

回帖

9909万

积分

论坛元老

Rank: 8Rank: 8

积分
99098928
发表于 2024-10-31 21:09:38 | 显示全部楼层
“BS”(鄙视的缩写)‌
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

站点统计|Archiver|手机版|小黑屋|外链论坛 ( 非经营性网站 )|网站地图

GMT+8, 2024-11-9 03:41 , Processed in 0.069328 second(s), 19 queries .

Powered by Discuz! X3.4

Copyright © 2001-2023, Tencent Cloud.