[学员作品]EXCEL催化剂 助力电商数据实现自动化

Excel催化剂的功能原型源于笔者在电商行业使用Excel进行数据分析过程中产生的场景需求，电商行业天生就大量的数据产生，非常适合数据分析师练手。 今天很高兴一位学员用户YiYuan，也是和笔者曾经的从业背景相似，以电商的日常数据收集过程为例，给我们输出一篇其工作内容的讲解，并将Excel催化剂的功能深入应用到整个流程当中，非常有启发意义。 在此也希望更多Excel催化剂的学员和用户们，本着乐于分享的精神，一起更多地分享日常工作中使用到Excel催化剂提升数据处理、分析的效率故事。众人拾火火焰高，你们的分享，将给后来者带来不少的学习资料和灵感触动。

以下是正文部分，中间也穿插一点笔者个人见解。

工作背景

在一个比较大的电商公司（小型电商公司一般没有数据岗位配置）,负责2个项目的数据，每天处理原始表大概在120个左右，数据主要来源生意参谋，其他平台也有，但是相对较少。

电商数据量虽然大但是还没有到海量，所以暂时没有用数据库，数据以文件和文件夹的方式存储。数据输出还是以透视表为主，有BI可视化，但目前就是一个摆设。

涉及软件工具

1、小旺神插件：用于行业数据的指数转化（很良心，完全免费）

2、RPA软件：负责数据的自动化采集(生意参谋数据大部分以直接下载为主）

3、PQ+PP：数据清洗+数据建模（从文件夹直接获取数据，实现文件下载后直接刷新出报表）

4、EXCEL催化剂:文件导入+文件转换+文件重命名+催化剂自定义函数

催化剂解说：这几年PowerQuery和RPA流行后，在数据采集、整理汇总的工作上带来很大的便利，笔者也说服了litrpa的开发者，将其RPA软件免费化给个人用户使用，日后有空闲时，也给大家分享下其利害之处，一个工作，灵活使用多个工具，不必吊死在一个工具，是笔者所倡议的，毕竟每个工具都有其最擅长的地方。

文件的储存方式

1、以excel文件存储：文件过大是其一，主要是每次都要打开文件复制粘贴数据，耗时耗力易出错

2、以文件夹的方式存储：表格太多，pq刷新会太卡

3、EXCEL+文件夹：最佳配合。定期梳理文件夹，以数据清单的形式保存到excel文件，以月或者年的方式存放一个excel文件（在学mysql，后期可能会用数据库+文件夹的方式）

催化剂解说：当数据量大起来，可以适当考虑数据库方案。当前Excel催化剂有与Sqlserver的数据库交互能力，可以在Excel环境下，零门槛查询数据库数据和上传Excel表格数据到数据库中，再往后打算重写此功能，将其扩展到主流数据库适配如单机版的Access/sqlite，服务器版本的MySQL、Oracle、Sqlserver等，敬请期待。

以市场排行数据为例为例

存放路径：E:\xxx\xxx数据\01行业大盘\2市场排行\品牌

这样看起来是非常清晰 ，pq直接文件夹获取文件刷新也非常方便，但是子文件夹藏的太深，每次要层层打开文件存放也是非常痛苦的事情，一不小心就放错了。今天的分享就是解决这一系列痛点。

…………………………………………………………………………………………………………………

进入正文

1、下载文件到指定的文件夹

日常工作第一步，肯定是下载文件，建议用一个专门浏览器，指定下载路径下载，文件保存在 E:\1

（题外话：此处我用两台电脑创建本地共享盘，下载电脑用RPA软件跑流程，工作电脑在下载的文件夹中处理）

2、创建文件对应的的文件夹路径辅组表

两个点

1、第3列：文件对应关键字，必须的所有文件中唯一的

2、第4列：指定文法路径。第1、2、5列起辅助作用

演示匹配表

3、用催化剂自定义函数获取文件夹下文件地址

=GetFiles("Z:\1","csv")

4、用LOOKUP全称匹配简称

结合文本函数创建文件全路径（函数用法就自行百度，也可以用催化剂自定义函数辅助）

=IFERROR(LOOKUP(1,0/FIND(演示匹配表!C2:C15,演示csv!C8),演示匹配表!D2:D15)&MID(C8,FIND("1\",C8)+1,150),"")

催化剂解说：复杂的函数嵌套，显出Excel的实力，但非常不利于作交接类工作，包括笔者在内，现在看到这一长串函数公式都会晕过去，更合适的是封装为自定义函数来使用，这个模糊反向查找，在Excel催化剂的自定义函数里已经有现成的，性能和可读性较原生函数来完成要好得多。

到这里基本就完成了，这个表格就相当于自己的工作台。

催化剂解说：前面几个步骤，就算再先进的软件，也没法替代人的工作完成，笔者定义这些步骤是配置步骤，将业务所需的逻辑固化为数据逻辑，将其以配置表（Excel一维表）的方式存储下来。软件读取到这些数据逻辑后，才能根据所需的逻辑进行其他自动化的操作。Excel催化剂相对其他软件的一大好处是，将配置和运行整合到一起，在Excel环境下无缝操作。目前为止将来也不会有比Excel环境更适合用户去操作配置更好的方式。

催化剂闪亮登场

生意参谋数据有三种

lcsv文件，这个一般是第三方软件转换出来的，pq可以直接加载转换

lxls文件，生意参谋的xls文件一般不能pq直接加载，需要转换成xls

l同名csv文件，比如市场-产品洞察下载的文件 ，文件名都一样，后期处理无法区分类目，需要重新命名

举例第三种

第一种，csv文件 或者xlsx文件

=GetFiles("E:\1","csv")

直接使用批量移动或者复制文件即可，有重复可以覆盖或者提醒

第二种 xls文件

=IFERROR(LOOKUP(1,0/FIND(演示匹配表!C4:C17,演示xls!B2),演示匹配表!D4:D17)&MID(B2,FIND("1\",B2)+1,150),"")&"x"

1、注意公式最后加的x

2、使用excel文件可是转换（不要使用文件相关-修改文件名）

xls转xlsx是打开另存为的过程，直接修改行不通

催化剂解说：非常惊讶于阿里的生意参谋的数据导出格式，这么长时间，一直存在问题都没改善过，因为这一缺陷，让笔者去开发了补救的方式，在Excel催化剂上转换这些非标准的文件格式，以便让PowerQuery这些工具可以读取。大厂的所谓客户第一口号喊得够响亮。

第三种同名文件

同名文件一次下载多个，最后会以（1）的方式存储

下载的时候需要按固定顺序下载，找出（1）（2）（3）……对应的类目

创建关键字匹配表

存储路径构造

=IF(C2="","","E:\XX\XX数据\01XX行业销售\004热水器热门产品\热门产品\"&MID(C2,6,FIND("热门产品",C2)-1)&VLOOKUP(RIGHT(C2,10),同名匹配表!C7,2,0)&MID(C2,FIND("热门产品",C2)+4,99))

催化剂解说：同样地上述的公式处理，更好的方式是用正则函数的方式来处理字符串，易读性和准确性都高出Excel原生的字符串函数不少。

批量修改文件路径

导入文件后，刷新报表就ok，省时，省心。

催化剂解说：在文件类场景中，Excel催化剂已经做到极致的，除了本篇提到的文件归档不同路径的场景所需的批量移动复制文件功能外，还有无数的其他功能等待大家去挖掘，只要敢想，Excel催化剂都有非常理想的解决方案给到大家（文件类场景是网页架构的解决方案所不可能给到大家的，它们没有办法读取本地的资源文件，同时其他一切的工具也不可能和Excel催化剂相提并论，因为如上述所说Excel催化剂的配置和后面的转换加工步骤完全在Excel环境中完成是其他方案不能有的）。

结语

Excel催化剂诞生至今三年时间里，非常多的功能已开发好供大家在数据采集、处理、分析、可视化等数据全链条中使用，非常期待大家多多结合自身行业的工作需要，挖掘出不同的精彩玩法，更欢迎大家多多参与，可以简单总结一下，供互联网广大用户们来学习参考，互联网精神就是分享的精神，您的分享，终必会以丰厚的各种回报方式反赠给您。