SPL XLL

增强Excel计算能力
  1. Excel缺乏必要的功能
  2. 旧版Excel使用新版功能
  3. 加强Excel的常用函数
  4. 简化表格数据的计算
  5. 解决分组后计算难题
  6. 批量合并拆分Excel

01Excel缺乏必要的功能

有些计算目标比较常见，但Excel没有提供相应的功能或函数

用分隔符"->"拆分A1，结果如下图的C1:F1
仅公式难以实现，通常要借助VBA
技术要求高，生产力低下
使用多字符拆分函数split，在C1格输入公式
=spl("=?.split(""->"")",A1)
不必借助VBA，直接用拆分函数，显著提高生产力
找到A列有但B列没有的数据（差集），结果如C列
不直接支持差集等集合运算，只能用现有函数拼凑（if、iserror、vlookup、filter等） ，公式复杂难懂
使用差集运算符\
=spl("=?1\?2",A1:A6,B1:B5)
支持各类集合运算符及集合运算函数，公式简单易懂，支持自动溢出

SPL更多功能

  • 对位置不定的单元格（如去掉最大最小值）求中位数
  • 对数量不定的单元格（如N是格值）求TopN
  • 返回标志串之前的子串
  • 将长串拆解为数字串组和字符串组
  • 从长串里拆分出所有的日期类型
  • 每隔N行插入空行
  • 数据变化时插入空行
  • 将片区内的键值对拆分成键和值两列
  • 计算出某时间段内所有的周日

02旧版Excel使用新版功能

很多人的Excel还是旧版本，无法直接使用新版Excel中的强大函数

旧版无法使用2019&365的函数unique（求唯一值）
升级或用复杂的公式代替
使用等价的函数id@u
=spl("=?.id@u()",A2:A17)

id函数说明

  • 默认对结果排序，等价于Excel的sort(unique())
  • id@u不对结果排序，等价于Excel的unique
不必升级，旧版Excel中可用大量新版功能，并支持自动溢出

更多可用于旧版本的新功能

  • filter
  • sort\sortBy
  • xlookup
  • maxifs\minifs
  • xmatch
  • sequence
  • randArray
  • switch
  • concat\textjoin

03加强Excel的常用函数

有些Excel的函数很常用，但能力不够强。

filter函数的过滤结果没有列名，条件中不能引用列名，不支持模糊查询
需手工复制列名，公式编写复杂（search\iferror等函数搭配使用）
使用更强大的select函数
=spl("=E(?).select(Salary>5000 && Salary< 10000 && like(Name,""*Jo*""))",A1:G499)

select函数更多功能：

  • 只返回第1条
  • 返回第1条之后的数据
  • 反向过滤
  • 返回不满足条件的数据
  • 二分查找
过滤结果带列名，条件中直接用列名，支持模糊查询

更多可加强的Excel函数

  • 加强Vlookup函数，返回匹配到的多条结果
  • 加强Xlookup函数，支持交叉匹配和双向匹配
  • 加强index函数，支持倒数序号
  • 加强avg，跳过非数字成员
  • 加强max，返回最大值所在序号
  • 加强count，先按公式计算成员，再返回非空的结果的数量
  • 加强datedif，计算两个日期相差的季度数、周数、周一数量、周日数量
  • 加强concat，指定连接符，给成员加引号
  • 加强find，不区分大小写，从后往前找

04简化表格数据的计算

同样的场景下，table data（带列名的多行记录）的计算对Excel来说难度更高

求一月份进了前10的产品，哪些没有进二月份的前10名，即左侧表格里有但右侧表格里没有的记录（差集）
不支持简单数据的差集，更不支持表格数据的差集，需要用多个函数组合实现，难度较高
使用记录的差集函数merge@od
=spl("=[E(?1),E(?2)].merge@od(ProductName)",Jan!A2:C12,Feb!A2:C12)

merge更多功能：

  • 默认合并后保留重复（和集），且数据已有序
  • @o表示尚未排序
  • @i求两个表格共同的记录，即交集
  • @u求两个表格所有的记录并去重，即并集
  • 可以按单列\多列\整行进行集合计算
支持表格的各类集合运算，公式简单易懂
表格Orders的SellerID列和表格Seller的ID有查找关系（多对1），但各有缺失的数据（蓝色和黄色），请对两个表格进行双向查找（全关联），用部分列合并成新表格，并显示出对方缺失的数据
xlookup功能不全，只能单向查找（内关联和左关联），无法进行双向查找（全关联）
使用记录的双向查找（全关联）函数join@f，输入公式
=spl("=join@f(E(?1),SellerID;E(?2),ID).new(_1.OrderNo,_1.Amount,_2.ID,_2.Name)",A2:D8,F2:H7)

join函数更多功能：

  • 默认单向查找不显示缺失（内关联）
  • @1表示单向查找显示缺失（左关联）
  • @p按表格内行号进行查找
  • 支持单列\多列\全行查找
支持表格的所有查找方法，公式简单易懂

更多可简化的表格数据的计算

  • 对两个表格进行交叉组合（笛卡尔积）
  • 遇到数据变化时进行分组
  • 遇到空行时进行分组
  • 遇到非空行时进行分组
  • 根据字段值（比如N）将一行扩展成N行
  • 过滤出连续上涨超过三天的记录
  • 指定列数（可选指定行数），将单列多行变表格
  • 指定行数（可选指定列数），将单行多列变表格

05解决分组后计算难题

Excel对分组后的表格数据进行计算时更加困难

找出各部门工资最少的前3名员工
不支持分组后计算，需要大量手工操作
使用group分组，使用~.top计算组内TopN
=spl("=E(?1).group(Dept).conj(~.top(3;Salary))",A1:G499)

top函数更多功能：

  • 可返回记录行或单列
  • -3表示工资最多的前3名
支持分组后的各类计算

更多分组后计算难题

  • 组内明细的排名rank
  • 组内取奇数/偶数/step位置
  • 组内相对位置的计算，如同比、环比、移动平均
  • 对组内明细按汇总值过滤，如大于平均值、小于汇总值的百分比
  • 在组内明细上进行与汇总值有关的计算，比如占汇总值一定比例
  • 用分隔符将组内多行合并为一行
  • 按分隔符将每一行拆分成多行

06批量合并拆分Excel

批量拆分或合并xls\sheet\table通常很繁琐

某目录下的xls以日期命名，每个文件存储一天的销售记录（下面是两个文件），现在要找出2020年1月份的xls，合并各文件数据，统计出每个销售员的销售额
手工实现工作量巨大
通配符匹配批量文件，conj合并批量数据，groups分组汇总
=spl("=directory@p(""d:/data/202001*.xlsx"").conj(T(~)).groups(SellerId;sum(Amount):Total)")

directory函数更多功能：

  • @d列出子目录
  • @s遍历子目录下的文件
支持xls\sheet\table的批量合并
订单table如下，按客户列拆分成多个sheet，每个sheet保存一个客户的订单，sheet名为客户名
公式不支持批量拆分，手工拆分工作量巨大
函数group分组，函数xlsexport写入xls并指定sheet
=spl("=E(?).group(Client).(file(""d:/Ordersm.xlsx"").xlsexport@ta(~;~.Client))",A1:E143)

xlsexport函数更多功能：

  • 继承原格式追加新行
  • 设定密码
支持xls\sheet\table的批量拆分
拆分结束后显示"finished!"，适合大文件拆分时间长的情况
=spl("=E(?).group(Client).(file(""d:/Ordersm.xlsx"").xlsexport@ta(~;~.Client))，""finished!""",A1:E143)
进一步改进：先编写SPL脚本文件，Excel再调用简短的文件名，适合步骤较多的情况。参考： http://c.raqsoft.com.cn/article/1649898057484

更多批量拆分合并难题

  • 遍历多层子目录下的xls
  • 将table data按固定行数拆分成多个sheet或xls
  • 将某一固定列和其他各列组合，每个组合分别写入sheet或xls
  • 对多个卡片式sheet，或一个sheet里的多个卡片式表格，将同样位置的格值进行汇总计算，生成汇总卡片
  • 将一个sheet里的多个卡片式表格，整理成一个规范的table data
  • 将多个卡片式sheet，合并成一个table data，每个卡片对应一行
  • 一个table data，拆分成多个卡片式sheet，每个卡片对应一行