Excel下用SQL语句实现AVEDEV函数功能

Excel下AVEDEV函数返回一组数据点到其算术平均值的绝对偏差的平均值。 AVEDEV 是对一组数据中变化性的度量。最常见的应用就是统计平均分差。

但是如果在Excel中写SQL进行一些复杂的统计的时候，SQL没有AVEDEV或者相同功能的函数，这就需要自己写SQL代码去曲线实现相同功能。

先来看看微软的excel帮助文档对AVEDEV函数的说明，https://support.office.com/zh-cn/article/AVEDEV-%E5%87%BD%E6%95%B0-58FE8D65-2A84-4DC7-8052-F3F87B5C6639，其中提到平均偏差的计算公式是，通俗点讲，就是一组数中，每个数逐个减去这组数的平均值，然后将减数的绝对值全部加起来，最后再除以这组数的个数取得平均值。

 

知道了如何计算，那也就容易用SQL代码实现了。

假设测试数据为：

部门	姓名	分数
财务处	张三	88.95
办公室	李四	89.68
后勤	王五	85.32
信息	小四	90.58
办公室	阿狗	86.34
信息	码农	89.99
后勤	美女	88.88
财务处	陈真	90
后勤	煮饭的	83.28

 

那么以下代码可以实现统计人数、平均分、最高分、最低分、最大分差：

SELECT 部门, COUNT(*) AS 人数, AVG(分数) AS 平均分, MAX(分数) AS 最高分, MIN(分数) AS 最低分,  (MAX(分数)-MIN(分数))AS 最大分差 FROM [Sheet1$]  GROUP BY 部门

统计结果如下：

部门	人数	平均分	最高分	最低分	最大分差
信息	2	90.285	90.58	89.99	0.59
办公室	2	88.01	89.68	86.34	3.34
后勤	3	85.82666667	88.88	83.28	5.6
财务处	2	89.475	90	88.95	1.05

 上面的统计都是比较基础的，下面就在上面的SQL语句的基础上，按照平均偏差的计算公式来统计平均分差：

SELECT A.部门, (SUM(ABS(A.分数-B.平均分)))/B.人数 AS 平均分差 FROM [Sheet1$] A  LEFT JOIN  (  SELECT 部门, COUNT(*) AS 人数, AVG(分数) AS 平均分 FROM [Sheet1$]  GROUP BY 部门  ) B ON A.部门 = B.部门  GROUP BY A.部门, B.人数

有点拗，主要是数组中每一个数要减去平均值，所以需要用到join。统计结果如下：

部门	平均分差
信息	0.295
办公室	1.67
后勤	2.035555556
财务处	0.525

看看AVEDEV函数统计的结果看是否一致：

部门	平均分差
办公室	1.67
信息	0.295
财务处	0.525
后勤	2.035556

统计出来平均分差，可以通过join再将开始的最大分差、平均分、人数等数据合并到同一张表，这里就不演示了。