小E为大家准备了100+Excel模板
领取直接关注公棕号【秋叶Excel】,回复【头条】!
大家好,我是大数据处理技术兔。
昨天,公司的副经理谭笑来找兔哥帮忙。原来经理想见去年公司职员的销售明细,,但谭笑去年只有公司的销售名单和员工名单,所以他不知道该怎么办。
她太担心了,以至于没有考虑茶和饭,她来找我寻求建议。
职工表数据如下:
要完成员工的销售业绩统计,需要用到员工的个人信息和销售信息,但现在这两个数据实际上分布在两个表中(为什么?啊,销售部和人事部都很任性!)。
这两个表之间的唯一联系是员工编号和销售人员编号(员工编号包括销售人员编号)。
虽然用Vlookup找函数可以解决这个问题,但是谭笑记不住这么麻烦的事情,如果表中增加了新的信息,他还要重新复制粘贴函数,操作起来太复杂了。
我一直追求“工作不累,吃得好,睡得好”,所以我决定找一些更容易的方法来帮助她完成任务。
#解决方案:
最近刚学了动力查询(Office 2016)。
以及上面版本的内置功能),它的合并查询功能非常惊人,两个表可以水平连接,只需点击几下鼠标就可以轻松完成。
最重要的是有一个易用的合并查询功能,可以是根据源表信息变化刷新数据,一次操作,多次使用,OK!就用它吧!
什么事?大家都没听说过合并查询。
好吧,我们先实际演示一下操作,然后再解释!当心!
# 01获取数据。
首先,我们将两个表中的数据导入到Power Query中。
单击数据-获取数据-从文件-从工作簿导入两个表所在的公司绩效统计工作簿,并调用导航器。
在导航器中选择“多个”,选择两个工作表“销售表”和“员工表”,然后点击“加载”,加载两个表的数据。
加载后,您可以在左侧的查询和连接窗口中看到加载的两个表的数据。
# 02合并表格。
我们需要查询员工的销售业绩,所以需要根据员工人数字段横向整合销售表和员工表。
依次点击数据-获取数据-合并查询-合并,弹出合并菜单,在第一个选择框中选择员工表。
在第二个选择框中选择“销售表”;
我们只需要有销售记录的员工信息,所以在第三个连接条件选择框中点击“内部连接(仅限匹配行)”。
在
职工表中选中「职工号」列,在销售表中选中「销售人员号」列,点击【确定】开始连接;合并表结果如下:
点击最后一列右上角的符号,将合并表展开;
这时我们会发现表中只有「商品单价」和「销售数量」,没有「销售金额」,看着不方便,那就新增一列吧~
点击【添加列】-【自定义列】,并命名为销售金额,填入公式,用销售单价乘以销售数量,点击【确定】,就得到了一张每个销售人员售货的明细表。
# 03 查询信息并保存结果
在右边的查询设置内把名称改为「销售明细表」,点击右上角【文件】-【关闭并上载】选项,即可保留合并查询结果。
合并操作的「销售明细表」最终版如下:
接下来我们就可以按自己的需求对表格进行明细查询、数量汇总、金额汇总或者分类汇总等操作啦。
# 04 合并表的后续管理
小谭做好了销售明细表,兴高采烈地复命去了,可是不一会又愁眉苦脸地来找我,说又多了一个 1203 号员工的销售信息,要不要重新操作?
不需要!这时候销售明细表的刷新功能就有用武之地了!
我让她把 1203 的销售信息更新到「销售表」里:
然后进入「销售明细表」,点击【数据】-【全部刷新】,1203 号销售人员王慧的全部销售明细就出来了!
以后凡是有数据变动,都可以这样更新信息!
通过以上操作,大家都明白合并查询是怎么回事了吧?
合并查询就是把两个表里面,具有相同列值的一行数据连接在一起!
比如王慧在职工表中的职工号是 1203,合并查询就会把销售表中销售号为 1203
的数据行都找出来,然后把王慧的职工信息跟它们连接在一起(放在同一行),这样我们就能得到完整的信息了!
怎么样?很方便吧,大家都学会了吗?快去试试新学的技能吧!
# 05 总结一下
以上只是一个简单的合并示例,只要两个不同类型表内有内容相同的数据列,如身份证号、工号等,就可以依靠它们作为连接词,横向做成合并表,对比并查询信息,对于日常办公很有用处。
两表的横向连接方法有 6 种(左外部、右外部、完全外部、内部、左反、右反),今天我们使用的就是内部连接,它合并表格后
返回的是第一个表和第二个表的所有匹配行。
每种连接方法对应不同的合并表结果,以后我们再作一一介绍!
小E为大家准备了100+Excel模板
领取直接关注公棕号【秋叶Excel】,回复【头条】!