财务:多工作表财务数据,快速核对

作者: ladeng6666 分类: Excel 发布时间: 2019-08-07 00:39 阅读: 2,536

今日目标

学会使用Power Query合并工作表

场景

Excel中最伟大的发明是「数据透视表」

如果给这个最上再加一个最,那最最伟大的肯定是Power Query。

可惜,很多人还在用2013甚至更早版本的Excel,无法享受Power Query强大的威力。

这节课,我就带大家来感受一下。

文末有送书活动

1- 问题描述

在Excel基础训练营里,有个学员提问说:

在群里答疑的时候,我没好意思直接说,这里我要表达一下想法:

提问的方式,很容易暴露自己的职场表达能力、和逻辑能力。

学会Excel能升职加薪,都是蒙人的。

真正打动老板,让他有给你加薪想法的是,是职场的逻辑能力、表达能力,最终转化成解决问题的能力。

我尝试着,把问题重新梳理了一下。

1- 有4张原始数据表

每张表里,都包含了不同公司的还款统计信息

2- 有1张汇总的表格

这个汇总表里,汇总了4个「业务员」工作表里的信息,可以整体掌握各个公司的还款情况。

3- 如何跨表查询,而且自动更新

如何根据4个分表,核对「汇总」表里各个公司的还款状态?

如果4个分表里的数据发生了变化,如何实现自动更新?

2- 原因分析

WHAT 这是什么?

本质上,这是一个多表合并(多个还款记录合并),然后实现两个表格数据匹配的问题。

1- 多表合并

多表合并的话,方法还是蛮多的,最简单粗暴的就是手动复制粘贴。

还可以使用合并计算,添加多个区域实现数据汇总。

另外还可以使用方方格子等插件,进行傻瓜式的操作,更加高效。

2- 数据匹配

两个不同表格数据匹配的话,当然是使用万金油函数VLOOKUP了。

但是跟这位同学,分析清楚了思路之后,他还是不知道该怎么做?

WHY 为什么还是不会?

1- 不知道多表合并的方法

多表合并的操作,已经超出了提问者的认知边界。

很多Excel难题的根源,是我们不知道还可以这样做。

2- 不合并表格自造烦恼

提问者要求不能合并表格,又给这个问题平添了很多的困难。

很多时候,为了排版和打印好看一些,小白用户,经常给自己加一些奇葩的要求,把自己的整的死去活来。

HOW 怎么做呢?

方法还是有的。

如果说透视表是Excel最伟大的发明,那么比最伟大更大一点的发明,肯定是Power Query了。

我们可以用Power Query来解决这个问题。

解决方法

1- 合并多个工作表

1- 新建Power Query查询

在「数据」选项卡里,选择从工作簿文件导入数据

选择整个工作簿文件,点击「加载」。

然后就进入到了Power query的界面

 

2- 在Power Query中合并工作表

筛选需要合并的工作表

然后删除不需要的数据列

展开Data列,完成数据的合并

调整数据标题,提升第1行作为标题,并删除其他标题

3- 关闭并上载表格

数据合并之后,在「开始」选项卡里,点击「关闭并上载」,将结果加载到Excel表格中。

你注意到没有,数据合并之后,原始的工作还存在,没有影响原有的sheet。

2- VLOOKUP匹配数据

在「汇总表」中,添加VLOOKUP公式,匹配每个公司的还款状态

3- 数据自动更新

如果有数据更新的话,在Power Query合并后的数据上,点击右键,选择刷新即可。

4- 总结

关于Power Query

Power Query的好处就在于:

1- 原始明细表的内容,不会受到影响

2- 数据是链接的,可以一键刷新统计结果

关于数据理念

另外,数据库的理念也非常重要。

在小白用户眼里,「业务员」这几张表,只是不同的工作表,而在Power query高手眼里,是不同数据库的数据。

小白看到的是多工作表合并,Power Query高手看到的是「追加查询」。

理念不一样,方法和效率的起点千差万别。

公众号后台回复「759」,下载案例文件。

5- 聊5毛钱

好书推荐

关于Power Query,最近我在学习这本书,推荐给大家:

《从数据到Excel自动化报表:Power Query和Power Pivot实战》

作者:黄海剑(大海),公众号「Excel到Power BI」作者。

一本非常适合入门、打基础的Excel Power系列书籍,告别重复劳动,轻松驾驭数据,小白也能轻松实现报表自动化,综合Power Query 和Power Pivot的实战应用。

点击下面的图片,了解更多信息。

 

送书活动

感谢大家一致的关注,也支持一下我的好友海哥,转发本文送一本给大家。规则如下:

1- 评论写心得

评论区写下Power Query吸引你的地方(不少于20个字)。

2- 点赞数最高

评论点赞最高的,而且点赞数超过10个的小伙伴(我好没自信哦),可以获得本书。

 

我是拉小登,一个会设计表格的Excel老师

 

 

= = 推荐文章 = =

项目,如何统计标记黄色的项目数量

HR:按权重计算绩效一个公式搞定

HR:如何快速统计缺勤天数

销售:不能不知的数据拆分和提取神器

教师:这个排班表让我想辞职

财务:期初本金公式怎么写?

一个生日提醒案例,总结出长公式套路

 

 

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

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