解放表哥表姐们的早下班神器之 SPL

Excel是目前职场中应用最广泛的软件工具,各行各业都有大量“表哥表姐”们用它画表格,做计算。可以说,没有Excel,很多职场人员就无法正常工作。

不过,Excel也不是尽善尽美,它基本上没什么自动化处理能力,而且公式计算能力也还不够强,导致有不少日常工作只能在Excel上手工处理,当然效率就很低。结果,对着Excel加班加点就成为很多“表哥表姐”们的家常便饭。

我们来看看怎样解放表哥表姐们,让大家早点下班。

批量处理

有时我们需要处理批量的Excel文件,比如下级机构或员工提交上来的100Excel表格要合并成一个大的。Excel没提供什么好办法,只能一个个去打开、复制、粘贴,搞几十上百下,要累死人的。

如果只是简单地把表格按顺序拼到一起,那还只是机械动作,累虽然累,不费啥心思。然而,实际问题总是比期望的要麻烦。比如这些都是经常会发生的事情:

拼接时还要补上文件名,否则不知道这行数据原来是来自哪里的;

有些表格的列标准次序不一样,需要调整一下才能对齐;

某几个表格中有重复的数据,得找出来剔除掉;

这么一来,复制粘贴时还要死盯着这些格子,不仅费手还费眼,搞完了恨不得眼镜再深个300度,按时下班更是不可能的事了。

那咋办?

有人说,批量处理,那编程呗!

没问题,用啥来编呢?

有人再说,VBA

的确VBA搞这些事原则上没问题,它还是Excel自带的编程语言,不用安装即可使用,可是VBA太基础了,对于批量数据的处理并没提供多少帮助,随便一些动作就要写很长一大段代码,用来解决上面的问题并不轻松

那还有啥可用?

街上有无数培训班都会告诉你:学Python!

Python看上去很美,实际上却完全不是那么回事。想用它来协助Excel做数据处理,需要用一个叫pandas的第三方程序。我们且先略过安装Pythonpandas对于非专业程序员来讲有多困难了,更关键的问题在于pandas看待数据并不是我们熟悉的表格(一行行数据的集合),而是个矩阵,相关的运算和函数常常并不是以你期望的样子出现,理解起来很费劲。而且同样功能的运算会有N种表述方式,让人摸不着头脑,只能临时去网上找例子,写对写不对常常是凭运气;更不要再说它的调试了,你总不可能一次就把程序写对吧, Python 的调试功能本来就不太好,pandas 又不是 Python 的原生内容,调试就更费劲。

Python是个不错的编程语言,但并不是面向非职业程序员的,它的使用者都是重度专业人员。

Python 都不行,那其它还有啥?

还有SPL!这个世界上大概只有 SPL 是能让非专业程序员的职场人员学得会的程序语言了。对于大部分Excel不擅长的数据处理功能,它都只需要一两句代码即可解决。

比如上文提到的合并Excel的问题,如果要把一个目录下的所有Excel文件合并成一个,并把文件名转为列值,只要写如下三行代码:


A

1

=directory@p("tmp/*.xlsx")

2

=A1.conj((fn=filename@n(~),T(~).derive(fn:Commodity)))

3

=T("Amount.xlsx",A2)

列顺序不同也很简单:


A

1

=T("FruitsPriceStock.xlsx")

2

=T("MeatsPriceStock.xlsx")

3

=A1.insert@f(0:A2)

4

=T("FoodsPriceStock.xlsx",A3)

去除重复数据也不在话下:


A

1

=T("Customer1.xlsx").sort(Name,Times)

2

=T("Customer2.xlsx").sort(Name,Times)

3

=[A1,A2].merge@u(Name,Times)

4

=T("CustomerTimes.xlsx",A3)

看看,这些代码是不是非常简单!

类似地,有合并就有拆分,如果想把一个Excel文件按某个列分类,再拆成多个文件,手工复制粘贴,不停地另存,是会累死人的,而SPL只要几行:


A

B

1

=T("orders.xlsx")

=A1.group(Shippers)

2

for B1

=T(A2.Shippers+".xlsx",A2)

还有批量Excel表格转换的问题,比如把这样的成绩表:

undefined

变成交叉表:

undefined

一个两个可以手工操作,如果手头十个八个甚至几十上百个班的成绩表要这么倒腾,那有多累也是可想而知的。但只要会SPL编程,也就是一两句话的事情。

表内计算和格式转换

日常工作中还有很多场景是Excel表格中的计算任务,Excel本身提供的公式和操作对于某些复杂的需求还是不够丰富,导致很多手工动作。

我们知道,Excel可以对着一个片区计算,也就是有集合的概念,但它不可以让单元格的值是个集合,也没提供集合的函数,涉及集合的运算就非常麻烦,比如下面这个例子:

每列是销售额排名前十的产品列表(随着时间的推移,月数会增加)

undefined

现在想找出这几个月全部进了前十的产品名单,这其实就是对着几列数据(几个月份的前十名单)求交集。但Excel没有直接提供这种函数,只能用数据操作来处理:选择一月份的名单列表,使用条件区域,将条件区域设置为二月份的名单列表,就能得到一月和二月前十名单的交集。接着,用一二月的交集,和三月份求交集,重复上面的步骤,有几个月就操作几次,最后得出所有月份的交集。

如果到了年底,有12个月的数据,按上述步骤操作,会把人逼疯

SPL这时候也能帮得上忙,它提供了一个插件,安装上之后,Excel就多了一个函数,而刚才这个运算,只要一句就搞定:

=spl("=transpose(?1).isect()",B2:E11)

Excel中选择你希望存放结果的单元格,写入这个公式即可:

undefined

月份多了,也就是把涉及的片区写大一点即可。

Excel的文字处理能力也不太强,比如下面这个文字拆分的例子(D列和E列有多行文字,两者行数相同且一一对应,比如F对应Fail):

undefined

现在要将D列和E列按换行符拆分,并扩展成多行,希望结果如下:

undefined

Excel 有分列功能可以拆分字符串,但只能把拆出来的结果放到多列里,现在这里希望拆到多行,而且要同时抄其它列的数据,用Excel就只能手工操作了。不过有了SPL,就还是一句话的事:

=spl("=E(?1).run(Grades=Grades.split(""\n""),Comment=Comment.split(""\n"")).news(Grades.len();Names,Class,Year,Grades(#):Grades,Comment(#):Comment)",A1:E4)

看起来长了一点,其实是把列名抄写了一遍,公式本身并不难,而且 SPL 还提供了界面可以很方便地编辑这种长公式。

除了计算问题外,Excel有些操作也不足,比如前文提到过的表格行列转换的例子:

要把这个表转换成下图所示的格式,要按照ChineseMathsEnglish的顺序列出:

这个需求很简单,说白了是个交叉表,但是左边多了ID列,也就是说,左边的IDName是个一一对应的关系。如果使用Excel的数据透视表功能,做出来是这个样子:

undefined

IDName变成了一对多的关系,而且MathsEnglish这两列也没法按指定的顺序排列。数据透视表搞不定这个事,转置功能也不行,只能手工调整格式了,数据量小还好说,要是数据量大,这个格式调整一样会让人想疯。

使用SPL,还是只需要一句公式搞定:

=spl("=E(?1).pivot(ID,Name;Subject,Score; ""Chinese"",""Maths"",""English"")",Sheet1!A1:D13)

是不是异常简单?

Excel中类似的困难问题还有很多,比如:

从阶梯价格表中找出当前购买数量对应的价格

按分数段分组统计人数

计算两个日期区间的重叠时长

找出前一天和后一天均比当天销售额低500以上的日期数据

用分类汇总值筛选

去除重复数据

把一段文字中的日期信息剥离出来

……

这些事在Excel大都要手工操作,费时费力。而用SPL可以弥补Excel的不足,一两句就轻松搞定。

总结一下

1. Excel 很好用,但是缺乏自动化处理能力和集合相关的函数,会造成某些稍复杂任务中的大量手工操作,效率低下。

2. SPL 编程可以批量自动化处理;插件解决表内计算和格式转换问题; 丰富的集合运算能轻松应对各种数据处理难题,常常一两句话搞定一个挠头的问题。

3. 学习 SPL,看 【程序设计】 前言及目录 ;只要高中知识就可以学会。

4. 碰到职场上的 Excel 难题,看 帮你早下班 - esProc 桌面版与 Excel 数据处理 ;这里有上百个例子,抄抄改改就行了,总有一款适合你,拿去不谢。