如何在SpreadJS中实现Excel的数据分析功能
本帖最后由 Richard.Huang 于 2024-9-30 12:57 编辑背景
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库创建回归模型
const linRegModel = ss.linearRegression(x.map((d, i) => ]));
const linRegLine = ss.linearRegressionLine(linRegModel);
二. 将数据导入模型中计算预测值、回归平方和、残差平方和、总平方和、回归自由度等计算结果
// 预测值
const yPredicted = x.map(linRegLine);
const residuals = y.map((yi, i) => yi - yPredicted);
// 回归平方和 (Regression SS)
const regressionSS = ss.sum(yPredicted.map(yi => Math.pow(yi - yBar, 2)));
// 残差平方和 (Residual SS)
const residualSS = ss.sum(residuals.map(res => res * res));
// 总平方和 (Total SS)
const totalSS = ss.sum(y.map(yi => Math.pow(yi - yBar, 2)));
// 回归自由度 (Regression df)
const regressionDF = 1;
// 残差自由度 (Residual df)
const residualDF = n - regressionDF - 1;
// 总自由度 (Total df)
const totalDF = n - 1;
// 回归均方 (Regression MS)
const regressionMS = regressionSS / regressionDF;
// 残差均方 (Residual MS)
const residualMS = residualSS / residualDF;
// F 统计量 (F Statistic)
const fStatistic = regressionMS / residualMS;
// Significance F
const significanceF = 1 - jstat.centralF.cdf(fStatistic, regressionDF, residualDF);
// R Square
const rSquare = regressionSS / totalSS;
// Multiple R
const multipleR = Math.sqrt(rSquare);
// Adjusted R Square
const adjustedRSquare = 1 - ((1 - rSquare) * (n - 1) / (n - regressionDF - 1));
// 标准误差 (Standard Error)
const standardError = Math.sqrt(residualMS);
// 系数 (Coefficients)
const intercept = linRegModel.b;
const slope = linRegModel.m;
三. 标准误差稍微复杂一点，没有直接的方法可以调用，我们可以通过一点基础的数学知识一步一步计算他
[*]计算的部分
const xtx = ss.sum(x.map(xi => Math.pow(xi, 2))) - (Math.pow(ss.sum(x), 2) / n);这里，实际上是计算设计矩阵X中自变量x的平方和减去x的总和的平方除以样本量n，这相当于个表达式在计算斜率的标准误差时会用到，它是矩阵的一个组成部分。
[*]计算截距和斜率的标准误差
const interceptSE = Math.sqrt(residualMS * (1 / n + Math.pow(xBar, 2) / xtx));
const slopeSE = Math.sqrt(residualMS / xtx);截距的标准误差(interceptSE)：这里的 residualMS是残差均方，即残差平方和除以残差自由度。xBar是自变量的平均值。截距的标准误差公式为
斜率的标准误差 (slopeSE)：斜率的标准误差公式为
[*]计算 t 统计量
const interceptTStat = intercept / interceptSE;
const slopeTStat = slope / slopeSE;截距的 t 统计量：
斜率的 t 统计量：
[*]计算 p 值
const interceptPValue = 2 * (1 - jstat.studentt.cdf(Math.abs(interceptTStat), residualDF));
const slopePValue = 2 * (1 - jstat.studentt.cdf(Math.abs(slopeTStat), residualDF));p 值用于确定 t 统计量的概率，即在零假设成立的情况下观察到如此极端或更极端结果的概率。这里使用的是双尾检验，因此 p 值是两侧概率之和。jstat.studentt.cdf函数计算学生t分布的累积分布函数。
[*]计算置信区间
calculateConfidenceInterval(coefficient, standardError, df) {
if (this.ConfidenceLevel == null) {
this.ConfidenceLevel = 95
}
const criticalValue = jstat.studentt.inv(1 - (1 - this.ConfidenceLevel / 100) / 2, df);
return [
coefficient - criticalValue * standardError,
coefficient + criticalValue * standardError
];
}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的公式框实现选择区域的功能
this.yArea = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById("y-input"), { rangeSelectMode: true });
this.xArea = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById("x-input"), { rangeSelectMode: true });
this.yArea.workbook(spread);
this.xArea.workbook(spread);
五. 将计算结果整合并赋值到对应位置
// 新建工作表并添加计算结果
yes() {
const y = this.getOneValueOrReference(this.yArea.text());
const x = this.getOneValueOrReference(this.xArea.text());
if (x.length != 0 && y.length != 0) {
this.calculateStatistics(x, y);// 计算
// 新增一个Sheet页，并通过数据绑定的方式将计算结果进行精准赋值
this.spread.addSheet(0);
var newSheet = this.spread.getSheet(0);
newSheet.fromJSON(this.sheet2JSON);
newSheet.setDataSource(
new GC.Spread.Sheets.Bindings.CellBindingSource(this.result)
);
this.spread.repaint();
}
},
效果
完整代码
参考链接
1. Excel 中的“分析数据”
2. SpreadJS区域选择器
3. Desinger中新建选项卡
页:
[1]