【技巧】 篇二十九:放大招了!学会这个技巧把240小时的工作变成1秒

2021-01-12 20:38:18 15点赞 164收藏 2评论

关于打工人的故事有很多版本,以下就是其中一个......

我是一名在上海一家私业工作的员工,工作压力很大,同事们都加班成瘾。面对面对成百上千的日报表、周报表、月报……年报,我依旧摸了一条大鱼。工作5年的我,一直泡在各类报表里,即便学会了五花八门的函数,但还是逃不过加班的命运。因为总有猪队友用难以统计的模板给你做数据,很多数据还得手动操作。

放大招了!学会这个技巧把240小时的工作变成1秒


(好端端的表加什么标题,还把日期、地区单独放出来,这很难统计的知道吗?)
2019年,我也感到日复一日、年复一年地收集数据、核对调整、统计数据太乏味,我想如果Excel可以自己搞定这些统计,我不就能搞点事情啦。

放大招了!学会这个技巧把240小时的工作变成1秒



刚萌生摸鱼之心,我是有点慌张的,在苛刻的工作环境中,在时间的夹缝中,学琴都来不及,考研还复习个毛线呀。于是我抱着幻想在网站上搜索各种能让表格自己虐自己的方法。后来我发现了两个办法,一个是对于非计算机专业的我比复习考研还难得Python,另一个是一秒超神的方法, 升级Excel、参加简单秒懂的学习。当然,我选择了后面这个,仅用不到一个皮肤的价钱,享受人民币玩家带来的福利。老板觉得我是这样做日报、周报、月报、季报、年报的。。。每天把左边的报表,用函数计算到右边的报表中。

放大招了!学会这个技巧把240小时的工作变成1秒


然而,学习15天后,我是这样做报表的。。。把新的报表放到文件夹里,刷新,搞定。只要前期用了几分钟做一遍PQ清理,后续的工作就变成了每天摸鱼7小时,每月摸鱼25天。

放大招了!学会这个技巧把240小时的工作变成1秒


由于空下来的时间太多,某校的研究生新版证书太好看,我将空下来的时间拿来考研了。既然证书到手了,老板管不着我了,我来把这个方法告诉小伙伴们哟。

放大招了!学会这个技巧把240小时的工作变成1秒


(我就想提醒一点,都能考研了,可见是空了好多时间,所以你千万不要让老板知道,万一他给你工资翻倍不让你考研就难了)
这个方法主要用到了Excel一项新功能PowerQuery,Office2016以上的版本就能使用,当然最推荐的是使用Office365版本。我们的目标是把分在各个表格中的数据整理为数据透视表可以使用的格式,并汇总成一张总表,最终用透视表完成统计。

放大招了!学会这个技巧把240小时的工作变成1秒

放大招了!学会这个技巧把240小时的工作变成1秒



01

前序准备


1、把需要将至少2个工作簿放到一个文件夹中。该分享就将文件夹命名为【每日订到报表】,后续的每日报表都会放到该文件夹中进行现自动汇总。

放大招了!学会这个技巧把240小时的工作变成1秒



2、在该文件夹外,新建一个Excel表,可以取名叫【数据汇总】。

放大招了!学会这个技巧把240小时的工作变成1秒


注意事项:千万不要把汇总表放到【每日订到报表】文件夹内,不然会形成死循环。

02

使用PQ获取数据

1、打开【数据汇总】Excel表,【数据】功能选卡中,Office365版本找到【获取数据】-【来自文件】-【从文件夹】。office2016版本在【数据】-【新建查询】-【从文件】-【从文件夹】。

放大招了!学会这个技巧把240小时的工作变成1秒


2、点击【从文件夹】后,通过【浏览按钮】选择文件存放的文件夹【每日订到报表】,选择好文件夹,虽然里面看不到文件,但是不要慌,点击打开就好。最终地址会被记录下来,这个是,点击确认。

放大招了!学会这个技巧把240小时的工作变成1秒


3、在弹出来的数据加载框的右下角,选择【转换数据】对数据进行整合。

放大招了!学会这个技巧把240小时的工作变成1秒



03

使用PQ解析合并数据


1、点击转换数据后,进入到PowerQuery的操作界面,和Excel差不多。红色的是功能区,对整列数据进行转换、提取、判断等操作。蓝色的是数据区,在这里可以看到数据情况,并且通过鼠标右键进行一些快捷操作。
紫色的是步骤区,可以用来看到每一步操作,可以修改或撤销做错的步骤

放大招了!学会这个技巧把240小时的工作变成1秒


2、现在蓝色区域展现的是文件夹中3张需要汇总的表单的信息,如表名、格式等。而表单中的实际数据是存在了第一列Content中。其他列可以右键删除。操作方法是点击选中Content列,点击鼠标右键,在弹出的退画框中点击删除其他列。

放大招了!学会这个技巧把240小时的工作变成1秒


3、通过添加列增加解析列,会用到本练习中唯一的函数

Excel.Workbook([content],false)

放大招了!学会这个技巧把240小时的工作变成1秒


注意事项:a、函数名称要注意首字母大写,括号为英文半角的符号;b、只有365版本才有提升,其他版本需要手动输入。
细节分享:函数有两个参数,一个是[content],鼠标在右框双击即可;第二个参数可以输入true或false,含义为是否要整合表头,具体区别如下:

放大招了!学会这个技巧把240小时的工作变成1秒


而我工作的表单就是后一种,T.T,所以用了false
4、解析完成数据已经在新添加的【自定义列】的Table里了,可以删除content列,然后点击自定义列右边的按钮,进行第一次展开该列数据。展开后的数据为每一张表里面,每一个Sheet的信息,其中【自定义.Data】中的Table存放了对应表单的数据。

放大招了!学会这个技巧把240小时的工作变成1秒


5、由于我们每个工作簿里,只有一张Sheet1表单,并且数据也存在里面。于是这里不用进行筛选,直接保留【自定义.Data】列,把其他的去掉,然后展开该列。

放大招了!学会这个技巧把240小时的工作变成1秒


注意事项:在点击确定展开时把【使用原始列名作为前缀】前面的勾去掉,不然每一列名字都会叫自定义.Data.xxxx这样一来所有数据就都汇总在一张表里啦,来看看初步成果,上海、北京、重庆都有了。

放大招了!学会这个技巧把240小时的工作变成1秒




04

使用PQ清理数据

1、虽然数据汇总了,但是想要用透视表来分析,还有3个需要调整的格式

将每一张表的标题【公司订单数据表】去掉;要将订单日期、区域、城市的信息形成单独的列;每张表都有表头,要把重复的表头去掉。


放大招了!学会这个技巧把240小时的工作变成1秒


2、通过筛选功能就可以把标题去掉,点击表头列1右边的箭头,打开筛选窗口,就和Excel中一样,将【公司订单数据表】前面的勾去掉机构。

放大招了!学会这个技巧把240小时的工作变成1秒


3、将订单日期、区域、城市数据转变成按列存储的方式,主要用到条件列功能。由于日期、区域、城市都在同一行,在PQ中用判断第一列是否包含【订单日期】就能取得对应的数据。首先在PQ中判断是否包含某个文字,需要先将对应的列转换为文本格式。点击表头前的【ABC123】,将第一列转换为【文本格式ABC】。

放大招了!学会这个技巧把240小时的工作变成1秒


4、通过判断将含有日期、区域、城市信息的行区分出来,这里用第一列是否含有【订单日期】作为区分,使用条件列进行判断,如果是订单日期,就返回第二列的订单日期数据,如果不是,就返回null。点击【添加列】-【条件列】,在弹出的对话框中进行输入:【新列名】可以修改名称if后面的;【列名】是指用哪一列参与判断,这里选择Column1(列1);【运算符】是判断的方式,这里选择包含;【值】是指运算符包含所要包含的内容;【输出】是指【列名】所选的列满足判断条件,将返回的值。这里可以通过点击【ABC123】来选择【输出一个值(返回一个固定的值)】,还是【选择列(返回所选列在对应行上的值)】。这里输出选择【选择列】,后面选择Column2(列2),这样当第一列为订单日期时,就会返回订单日期所在这一行中,第2列的数据,也就是2020/1/1。如果其他日期的数据导入后,也能取得对应的日期;【ELSE】是指【列名】所选的列不满足判断条件,将返回的值。这里填写null,null在PQ中代表空值,什么都没有。

放大招了!学会这个技巧把240小时的工作变成1秒


放大招了!学会这个技巧把240小时的工作变成1秒


5、用与上一步同样的操作,判断第一列是否包含【订单日期】,然后返回第5列区域的内容和第8列城市的内容。

放大招了!学会这个技巧把240小时的工作变成1秒


6、按住Ctrl键,点击新添加的三列数据将其都选中,通过【转换】-【填充】-【向下填充】补全数据,再筛选去掉原来含有日期、区域、城市的行。

放大招了!学会这个技巧把240小时的工作变成1秒


7、在去掉重复表头,选中【将第一行用作标题】,将标题上移,保留第一个表头。通过筛选功能,去掉重复的表头。

放大招了!学会这个技巧把240小时的工作变成1秒


8、更改新添加的列的列名,双击表头列名即可修改。点击【关闭并上载】,得到新的表单。

放大招了!学会这个技巧把240小时的工作变成1秒


9、这么一来数据就清理好啦

放大招了!学会这个技巧把240小时的工作变成1秒




05

统计与更新


1、选中生成的数据表,在【插入】-【数据透视表】中插入数据透视表,该分享演示一下各城市每日销售金额的统计。

放大招了!学会这个技巧把240小时的工作变成1秒


2、最后我来说说,这个PowerQuery超强的一点了。每天的数据来了,只需要将新的数据拖入之前的【每日订到报表】文件夹,刷新绿色的数据表、并刷新透视表可以更新了。

放大招了!学会这个技巧把240小时的工作变成1秒


以上就是这个操作简单的分享~

设为头图
展开 收起

Office 365 家庭版

Office 365 家庭版

249元起

微软 Microsoft 365 个人版 彩盒包装 | 1年订阅 1人使用 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

微软 Microsoft 365 个人版 彩盒包装 | 1年订阅 1人使用 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

398元起

微软 Microsoft 365 家庭版 彩盒包装 | 1年订阅 至多6人 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

微软 Microsoft 365 家庭版 彩盒包装 | 1年订阅 至多6人 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

498元起

微软 Microsoft 365 家庭版 电子秘钥 | 1年订阅 多至6人 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

微软 Microsoft 365 家庭版 电子秘钥 | 1年订阅 多至6人 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

498元起

微软 Microsoft 365 个人版 电子秘钥 | 1年订阅 1人使用 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

微软 Microsoft 365 个人版 电子秘钥 | 1年订阅 1人使用 正版高级Office应用 1T云存储 PC/Mac/移动设备通用

204元起

微软(Microsoft)Office365 个人版 正版办公软件 可供1用户5设备 一年新订或续费 365个人版 在线发送

微软(Microsoft)Office365 个人版 正版办公软件 可供1用户5设备 一年新订或续费 365个人版 在线发送

199元起

微软 Office 家庭和学生版 2019 电子秘钥版 | 正版授权 含Word/Excel/PPT 适用Windows 10 PC/Mac 非商用

微软 Office 家庭和学生版 2019 电子秘钥版 | 正版授权 含Word/Excel/PPT 适用Windows 10 PC/Mac 非商用

748元起

微软(Microsoft)Office365 个人版 正版办公软件 可供1用户5设备 一年新订或续费 365个人版在线发送+电子发票

微软(Microsoft)Office365 个人版 正版办公软件 可供1用户5设备 一年新订或续费 365个人版在线发送+电子发票

229元起

微软 Office365(现已升级Microsoft365)家庭版正版一年新订或续费 6用户多设备 365 家庭版在线发送+电子发票

微软 Office365(现已升级Microsoft365)家庭版正版一年新订或续费 6用户多设备 365 家庭版在线发送+电子发票

269元起

Microsoft 微软 Office 365 个人版 1年订阅

Microsoft 微软 Office 365 个人版 1年订阅

398元起

微软 (Microsoft) Office 365 家庭版/个人版 激活密钥 1年订阅 正版办公软件 office 365个人版一年【支持5台设备】

微软 (Microsoft) Office 365 家庭版/个人版 激活密钥 1年订阅 正版办公软件 office 365个人版一年【支持5台设备】

229元起

微软Windows server 2019客户端 UsrCAL(R18-05776)

微软Windows server 2019客户端 UsrCAL(R18-05776)

501.72元起

《Nebo》触屏笔记类软件 Windows版

《Nebo》触屏笔记类软件 Windows版

暂无报价

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

329元起

微软(Microsoft)office 365 家庭版

微软(Microsoft)office 365 家庭版

389元起

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

749元起
2评论

发表评论请 登录
  • 最新
  • 最热
评论举报

请选择举报理由

相关好价推荐
查看更多好价

相关文章推荐

更多精彩文章
更多精彩文章
相关好价
最新文章 热门文章
164
扫一下,分享更方便,购买更轻松