第37波-把Sqlserver的强大分析函数拿到Excel中用

本人一直钟情于使用Sqlserver数据库的一大原因是其提供了非常好用、高效的数据分析函数（窗口函数），可以在做数据清洗和数据分析场合等多个场景使用。只需简单的一个函数即可做出常规SQL语句很难以实现的效果。

这么好用的函数，如今Excel催化剂已部分将其搬到Excel的环境内使用，同样带来了极大的数据处理、分析的便利性和简化操作。

功能修复与更新

• 20181017 新增分组字符拼接函数，同样可满足按排序列来排序后拼接的效果

背景介绍

自从Excel催化剂推出了可返回多值的自定义函数，在函数的使用上已经突破了许多过往很难想像的功能突破，例如在过往教程中经常使用到的GetFiles函数，上一波的序列函数等。

只需一次计算就立马返回所有运算结果值。对于性能的提升也是立竿见影。如传统在一个数据表中加入SUMIF、COUTIF等函数，之所以每每运算效率低下，是因为其不断地在每一行中做了大量的重复计算。

例如第1，5，9行的值为A，统计A出现的次数，将重复统计3次（可能Excel内部会有一些性能优化的算法，但如果是二次开发的自定义函数就估计很难有优化空间）。

今天推出的众多分组计算函数中，同样采用了一次返回多值的方式，计算上仅需只算一次，对数据量大的情况下，性能改善明显。

一般来说，通用型函数的开发，没有说为哪个特定业务场景的需求而开发，但只要业务场景匹配到，通用型的函数发挥的可应用的场景是很丰富的。

若对今天介绍的函数想更进一步的扩展了解Sqlserver的原型函数，可百度上搜索Sqlserver窗口函数关键词即可有大量的文章。

函数介绍

此篇为分组计算函数，即对一列或多列的去重后出现的组成员中，通过排序列的排序依据，对某指标进行汇总聚合、生成序号、排名、和取其同一组内的某一列的某个值（上一个、下一个、开头、结尾）等功能。

可在部分场合完美替代SUMIF/COUNTIF/SUMIFS/COUNTIFS/MAXIF/MINIF/AVERAGEIF等常用函数的使用。

小缺点说明

一次多值返回的函数为数组函数，有以下的不足之处

• 不能在有数据公式的表中进行排序操作。
• 不能在智能表里输入数组函数
• 不能更改数组函数内的多个单元格的任一单元格函数内容。

若需要进行以上所提及的操作，请先对返回结果的自定义函数进行数值化处理或删除操作。

分组计数

类似COUNTIF函数的效果，若分组列为多列时，类似COUNTIFS函数的效果，但性能更优，因一次性返回多值，仅一次运算即可。

分组序号

分组序号函数特点，在分组内的记录数中，每一行返回从1开始的不重复的递增的序列，基于排序列定义的顺序，分组列，排序列可以为多列，当排序规则下的排序列相同，将从上往下填充递增序号。

**使用场景：**对于数据清洗工作尤其有用，当数据有重复时，需要去根据分组的唯一值进行去重是保留最开始出现的记录还是最后出现的记录时，只需配合排序列的升序或降序处理，即可仅保留序号为1的记录，其他记录删除的方式实现数据清洗。

当出现多个分组列时，因自定义函数参数位置固定的原因，只有第1参数才是分组列的输入参数，故需要嵌套FZJS分组列合并函数，用于合并多个分组列。

分组排名

类似以上的分组序号，返回递增的序列值，但此处对重复的值有相同的排名

同时对重复值排名区分了美式排名和中式排名两种

分组求和

结果类似SUMIF和SUMIFS函数的求和效果，因采用一次计算多值返回的手段，效率性能对大数据量有极大的提升

取某一位置值

在需要取某一位置的值时，可以灵活地提取上一元素、下一元素、首元素、末元素。

**使用场景：**可用于计算买家连续两次购买时间段的间隔时长、首次购买时间、末次购买时间等

总结

当Excel不再纯粹只是一个Excel时，大量的外界的力量可以给Excel插上翅膀，让它飞得更高更远。此篇借鉴了Sqlserver等数据库的分析函数的功能，在Excel上使其可供使用，大大地武装了Excel对数据的处理和分析能力。同时在使用传统COUNTIF/SUMIF等函数普遍感到效率性能低下时，提供了全新的解决方案，让性能得到极大的提升。