 关于打工人的故事有很多版本，以下就是其中一个...... 我是一名在上海一家私业工作的员工，工作压力很大，同事们都加班成瘾。面对面对成百上千的日报表、周报表、月报……年报，我依旧摸了一条大鱼。工作5年的我，一直泡在各类报表里，即便学会了五花八门的函数，但还是逃不过加班的命运。因为总有猪队友用难以统计的模板给你做数据，很多数据还得手动操作。  （好端端的表加什么标题，还把日期、地区单独放出来，这很难统计的知道吗？） 2019年，我也感到日复一日、年复一年地收集数据、核对调整、统计数据太乏味，我想如果Excel可以自己搞定这些统计，我不就能搞点事情啦。  刚萌生摸鱼之心，我是有点慌张的，在苛刻的工作环境中，在时间的夹缝中，学琴都来不及，考研还复习个毛线呀。于是我抱着幻想在网站上搜索各种能让表格自己虐自己的方法。后来我发现了两个办法，一个是对于非计算机专业的我比复习考研还难得Python，另一个是一秒超神的方法， 升级Excel、参加简单秒懂的学习。当然，我选择了后面这个，仅用不到一个皮肤的价钱，享受人民币玩家带来的福利。老板觉得我是这样做日报、周报、月报、季报、年报的。。。每天把左边的报表，用函数计算到右边的报表中。  然而，学习15天后，我是这样做报表的。。。把新的报表放到文件夹里，刷新，搞定。只要前期用了几分钟做一遍PQ清理，后续的工作就变成了每天摸鱼7小时，每月摸鱼25天。  由于空下来的时间太多，某校的研究生新版证书太好看，我将空下来的时间拿来考研了。既然证书到手了，老板管不着我了，我来把这个方法告诉小伙伴们哟。  （我就想提醒一点，都能考研了，可见是空了好多时间，所以你千万不要让老板知道，万一他给你工资翻倍不让你考研就难了） 这个方法主要用到了Excel一项新功能PowerQuery，Office2016以上的版本就能使用，当然最推荐的是使用Office365版本。我们的目标是把分在各个表格中的数据整理为数据透视表可以使用的格式，并汇总成一张总表，最终用透视表完成统计。   01 前序准备 1、把需要将至少2个工作簿放到一个文件夹中。该分享就将文件夹命名为【每日订到报表】，后续的每日报表都会放到该文件夹中进行现自动汇总。  2、在该文件夹外，新建一个Excel表，可以取名叫【数据汇总】。  注意事项：千万不要把汇总表放到【每日订到报表】文件夹内，不然会形成死循环。 02 使用PQ获取数据 1、打开【数据汇总】Excel表，【数据】功能选卡中，Office365版本找到【获取数据】-【来自文件】-【从文件夹】。office2016版本在【数据】-【新建查询】-【从文件】-【从文件夹】。  2、点击【从文件夹】后，通过【浏览按钮】选择文件存放的文件夹【每日订到报表】，选择好文件夹，虽然里面看不到文件，但是不要慌，点击打开就好。最终地址会被记录下来，这个是，点击确认。 3、在弹出来的数据加载框的右下角，选择【转换数据】对数据进行整合。 03 使用PQ解析合并数据 1、点击转换数据后，进入到PowerQuery的操作界面，和Excel差不多。红色的是功能区，对整列数据进行转换、提取、判断等操作。蓝色的是数据区，在这里可以看到数据情况，并且通过鼠标右键进行一些快捷操作。 紫色的是步骤区，可以用来看到每一步操作，可以修改或撤销做错的步骤 2、现在蓝色区域展现的是文件夹中3张需要汇总的表单的信息，如表名、格式等。而表单中的实际数据是存在了第一列Content中。其他列可以右键删除。操作方法是点击选中Content列，点击鼠标右键，在弹出的退画框中点击删除其他列。 3、通过添加列增加解析列，会用到本练习中唯一的函数 Excel.Workbook([content],false) 注意事项：a、函数名称要注意首字母大写，括号为英文半角的符号；b、只有365版本才有提升，其他版本需要手动输入。 细节分享：函数有两个参数，一个是[content]，鼠标在右框双击即可；第二个参数可以输入true或false，含义为是否要整合表头，具体区别如下： 而我工作的表单就是后一种，T.T，所以用了false 4、解析完成数据已经在新添加的【自定义列】的Table里了，可以删除content列，然后点击自定义列右边的按钮，进行第一次展开该列数据。展开后的数据为每一张表里面，每一个Sheet的信息，其中【自定义.Data】中的Table存放了对应表单的数据。 5、由于我们每个工作簿里，只有一张Sheet1表单，并且数据也存在里面。于是这里不用进行筛选，直接保留【自定义.Data】列，把其他的去掉，然后展开该列。 注意事项：在点击确定展开时把【使用原始列名作为前缀】前面的勾去掉，不然每一列名字都会叫自定义.Data.xxxx这样一来所有数据就都汇总在一张表里啦，来看看初步成果，上海、北京、重庆都有了。 04 使用PQ清理数据 1、虽然数据汇总了，但是想要用透视表来分析，还有3个需要调整的格式 将每一张表的标题【公司订单数据表】去掉；要将订单日期、区域、城市的信息形成单独的列；每张表都有表头，要把重复的表头去掉。 2、通过筛选功能就可以把标题去掉，点击表头列1右边的箭头，打开筛选窗口，就和Excel中一样，将【公司订单数据表】前面的勾去掉机构。 3、将订单日期、区域、城市数据转变成按列存储的方式，主要用到条件列功能。由于日期、区域、城市都在同一行，在PQ中用判断第一列是否包含【订单日期】就能取得对应的数据。首先在PQ中判断是否包含某个文字，需要先将对应的列转换为文本格式。点击表头前的【ABC123】，将第一列转换为【文本格式ABC】。 4、通过判断将含有日期、区域、城市信息的行区分出来，这里用第一列是否含有【订单日期】作为区分，使用条件列进行判断，如果是订单日期，就返回第二列的订单日期数据，如果不是，就返回null。点击【添加列】-【条件列】，在弹出的对话框中进行输入： 【新列名】可以修改名称if后面的； 【列名】是指用哪一列参与判断，这里选择Column1（列1）； 【运算符】是判断的方式，这里选择包含； 【值】是指运算符包含所要包含的内容； 【输出】是指【列名】所选的列满足判断条件，将返回的值。这里可以通过点击 【ABC123】来选择【输出一个值（返回一个固定的值）】，还是【选择列（返回所选列在对应行上的值）】。这里输出选择【选择列】，后面选择Column2（列2），这样当第一列为订单日期时，就会返回订单日期所在这一行中，第2列的数据，也就是2020/1/1。如果其他日期的数据导入后，也能取得对应的日期； 【ELSE】是指【列名】所选的列不满足判断条件，将返回的值。这里填写null，null在PQ中代表空值，什么都没有。 5、用与上一步同样的操作，判断第一列是否包含【订单日期】，然后返回第5列区域的内容和第8列城市的内容。 6、按住Ctrl键，点击新添加的三列数据将其都选中，通过【转换】-【填充】-【向下填充】补全数据，再筛选去掉原来含有日期、区域、城市的行。 7、在去掉重复表头，选中【将第一行用作标题】，将标题上移，保留第一个表头。通过筛选功能，去掉重复的表头。 8、更改新添加的列的列名，双击表头列名即可修改。点击【关闭并上载】，得到新的表单。 9、这么一来数据就清理好啦 统计与更新 1、选中生成的数据表，在【插入】-【数据透视表】中插入数据透视表，该分享演示一下各城市每日销售金额的统计。 2、最后我来说说，这个PowerQuery超强的一点了。每天的数据来了，只需要将新的数据拖入之前的【每日订到报表】文件夹，刷新绿色的数据表、并刷新透视表可以更新了。 以上就是这个操作简单的分享~ |
|
|
