如何在SpreadJS中实现Excel的数据分析功能

背景

---

Excel中的“数据分析”功能是一个相当强大的工具，它用于进行各种数据分析和统计计算。这项功能通过加载数据分析工具包（Analysis ToolPak）实现，提供了多种分析工具，比如描述统计、直方图、回归分析、t检验、方差分析等。在不同行业中都有着深入的使用，例如财务和会计会借助这里的数据分析工具实现预算编制、财务分析和成本控制等、市场营销部门可以借助它实现市场调查数据分析、销售趋势分析和客户细分等。可能他们还用不到如BI那么强大的能力，Excel中的数据分析功能就足够他们的日常使用了，那么SpreadJS拥有Excel的基础功能，那么能否将Excel的高级”数据分析“能力也搬到线上来呢？答案是肯定的，只要能够理解并实现”数据分析“中各个高级功能的逻辑，就可以借助SpreadJS丰富的API实现和Excel一样的效果。

解释

---

本文以Excel的数据分析面板中的”回归“功能为例进行讲解，我们需要借助simple-statistics和jstat这两个公开数据分析库实现数据分析的逻辑处理部分。
simple-statistics和 jStat是两个不同的JavaScript库，它们都用于处理数据统计和数学计算，但有着不同的特点和使用场景。

Simple Statistics

simple-statistics 是一个轻量级的JavaScript库，专注于提供简单易用的数据统计函数。它适合那些不需要非常复杂数学运算的项目，比如基础的数据分析、教育用途或是简单的数据可视化。simple-statistics提供了如平均数、中位数、标准差等基本统计指标的计算方法，同时也支持线性回归等稍微高级一点的功能。这个库的设计哲学是保持接口的直观性和代码的可读性，让开发者能够快速上手。

jStat

jStat则是一个更为全面的统计计算库，它不仅提供了与 simple-statistics类似的功能，还支持更多的高级统计分析技术，例如假设检验、概率分布计算、矩阵运算等。jStat的目标用户是对统计学有较深了解的专业人士，或者是需要在Web应用中实现复杂统计分析功能的开发团队。由于其功能强大，jStat可以用于科研、数据分析等领域，帮助用户完成从数据预处理到模型构建等一系列任务。

步骤

一. 调用simple-statistics库创建回归模型

1. const linRegModel = ss.linearRegression(x.map((d, i) => [d, y[i]]));
   
2. const linRegLine = ss.linearRegressionLine(linRegModel);

复制代码

二. 将数据导入模型中计算预测值、回归平方和、残差平方和、总平方和、回归自由度等计算结果

1. // 预测值
   
2. const yPredicted = x.map(linRegLine);
   
3. const residuals = y.map((yi, i) => yi - yPredicted[i]);
   
4. 
   
5. // 回归平方和 (Regression SS)
   
6. const regressionSS = ss.sum(yPredicted.map(yi => Math.pow(yi - yBar, 2)));
   
7. 
   
8. // 残差平方和 (Residual SS)
   
9. const residualSS = ss.sum(residuals.map(res => res * res));
   
10. 
    
11. // 总平方和 (Total SS)
    
12. const totalSS = ss.sum(y.map(yi => Math.pow(yi - yBar, 2)));
    
13. 
    
14. // 回归自由度 (Regression df)
    
15. const regressionDF = 1;
    
16. 
    
17. // 残差自由度 (Residual df)
    
18. const residualDF = n - regressionDF - 1;
    
19. 
    
20. // 总自由度 (Total df)
    
21. const totalDF = n - 1;
    
22. 
    
23. // 回归均方 (Regression MS)
    
24. const regressionMS = regressionSS / regressionDF;
    
25. 
    
26. // 残差均方 (Residual MS)
    
27. const residualMS = residualSS / residualDF;
    
28. 
    
29. // F 统计量 (F Statistic)
    
30. const fStatistic = regressionMS / residualMS;
    
31. 
    
32. // Significance F
    
33. const significanceF = 1 - jstat.centralF.cdf(fStatistic, regressionDF, residualDF);
    
34. 
    
35. // R Square
    
36. const rSquare = regressionSS / totalSS;
    
37. 
    
38. // Multiple R
    
39. const multipleR = Math.sqrt(rSquare);
    
40. 
    
41. // Adjusted R Square
    
42. const adjustedRSquare = 1 - ((1 - rSquare) * (n - 1) / (n - regressionDF - 1));
    
43. 
    
44. // 标准误差 (Standard Error)
    
45. const standardError = Math.sqrt(residualMS);
    
46. 
    
47. // 系数 (Coefficients)
    
48. const intercept = linRegModel.b;
    
49. const slope = linRegModel.m;

复制代码

三. 标准误差稍微复杂一点，没有直接的方法可以调用，我们可以通过一点基础的数学知识一步一步计算他

• 计算 的部分
  

1. const xtx = ss.sum(x.map(xi => Math.pow(xi, 2))) - (Math.pow(ss.sum(x), 2) / n);

复制代码

这里， 实际上是计算设计矩阵X中自变量x的平方和减去x的总和的平方除以样本量n，这相当于 个表达式在计算斜率的标准误差时会用到，它是 矩阵的一个组成部分。

• 计算截距和斜率的标准误差
  

1. const interceptSE = Math.sqrt(residualMS * (1 / n + Math.pow(xBar, 2) / xtx));
   
2. const slopeSE = Math.sqrt(residualMS / xtx);

复制代码

截距的标准误差(interceptSE)：这里的 residualMS是残差均方，即残差平方和除以残差自由度。xBar是自变量 的平均值。截距的标准误差公式为

斜率的标准误差 (slopeSE)：斜率的标准误差公式为

• 计算 t 统计量
  

1. const interceptTStat = intercept / interceptSE;
   
2. const slopeTStat = slope / slopeSE;

复制代码

截距的 t 统计量：
斜率的 t 统计量：

• 计算 p 值
  

1. const interceptPValue = 2 * (1 - jstat.studentt.cdf(Math.abs(interceptTStat), residualDF));
   
2. const slopePValue = 2 * (1 - jstat.studentt.cdf(Math.abs(slopeTStat), residualDF));

复制代码

p 值用于确定 t 统计量的概率，即在零假设成立的情况下观察到如此极端或更极端结果的概率。这里使用的是双尾检验，因此 p 值是两侧概率之和。jstat.studentt.cdf函数计算学生t分布的累积分布函数。

• 计算置信区间
  

1. calculateConfidenceInterval(coefficient, standardError, df) {
   
2.     if (this.ConfidenceLevel == null) {
   
3.         this.ConfidenceLevel = 95
   
4.     }
   
5.     const criticalValue = jstat.studentt.inv(1 - (1 - this.ConfidenceLevel / 100) / 2, df);
   
6.     return [
   
7.         coefficient - criticalValue * standardError,
   
8.         coefficient + criticalValue * standardError
   
9.     ];
   
10. }

复制代码

1. 设置置信水平：
默认置信水平为95%

2. 计算临界值：

• 临界值是通过学生t分布的逆累积分布函数（inverse cumulative distribution function, ICDF）计算得到的。对于双尾检验，临界值计算公式为：
• 其中： 是学生t分布的临界值，对应于显著性水平alpha （通常是0.05)的一半，自由度为df；alpha 和置信水平的关系是alpha = 1-ConfidenceLevel/100
  

3. 计算置信区间的上下限：

• 置信区间的下限为：lowerBound=coefficient−criticalValue×standardError
  

• 置信区间的上限为：upperBound=coefficient+criticalValue×standardError
  

四. 借助SpreadJS的公式框实现选择区域的功能

1. this.yArea = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById("y-input"), { rangeSelectMode: true });
   
2. this.xArea = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById("x-input"), { rangeSelectMode: true });
   
3. this.yArea.workbook(spread);
   
4. this.xArea.workbook(spread);

复制代码

五. 将计算结果整合并赋值到对应位置

1. // 新建工作表并添加计算结果
   
2. yes() {
   
3.     const y = this.getOneValueOrReference(this.yArea.text());
   
4.     const x = this.getOneValueOrReference(this.xArea.text());
   
5.     if (x.length != 0 && y.length != 0) {
   
6.         this.calculateStatistics(x, y);// 计算
   
7.         // 新增一个Sheet页，并通过数据绑定的方式将计算结果进行精准赋值
   
8.         this.spread.addSheet(0);
   
9.         var newSheet = this.spread.getSheet(0);
   
10.         newSheet.fromJSON(this.sheet2JSON);
    
11.         newSheet.setDataSource(
    
12.             new GC.Spread.Sheets.Bindings.CellBindingSource(this.result)
    
13.         );
    
14.         this.spread.repaint();
    
15.     }
    
16. },

复制代码

效果

---

完整代码

---

LinearRegression.rar (10.37 KB, 下载次数: 9)

2024-9-30 12:31 上传

点击文件名下载附件

参考链接

---

1. Excel 中的“分析数据”
2. SpreadJS区域选择器
3. Desinger中新建选项卡