data-analysis

Data Analysis Output Specifications

Core Principles

• Analysis results must be placed in a new worksheet; do not modify the original data
• Results include: Data Overview → Statistical Metrics → Visualization → Conclusion
• All calculations are written using .Formula so users can trace and verify them

Standard Structure of a New Analysis Sheet

function CL(c){var s="";while(c>0){c--;s=String.fromCharCode(65+(c%26))+s;c=Math.floor(c/26);}return s;}
var wb = Application.ActiveWorkbook;
var srcWs = Application.ActiveSheet;
var srcName = srcWs.Name;

wb.Sheets.Add(); var ws = Application.ActiveSheet; ws.Name = "Data Analysis"; ws.Activate();

// Area A: Title ws.Range("A1").Value2 = "Data Analysis Report — " + srcName; ws.Range("A1").Font.Size = 16; ws.Range("A1").Font.Bold = true; ws.Range("A1").Interior.Color = 0x8B4513; ws.Range("A1").Font.Color = 0xFFFFFF;

// Area B: Data Overview ws.Range("A3").Value2 = "Data Overview"; ws.Range("A3").Font.Bold = true; ws.Range("A4").Value2 = "Data Source"; ws.Range("B4").Value2 = srcName; ws.Range("A5").Value2 = "Total Rows"; ws.Range("B5").Formula = "=COUNTA('" + srcName + "'!A:A)-1"; // ... number of columns, number of empty values, etc.

// Area C: Descriptive Statistics Table // For each numeric column: mean/median/max/min/standard deviation/quartiles

### 描述性统计公式

| 指标 | 公式 |
|------|------|
| 均值 | `=AVERAGE('源表'!B2:B1000)` |
| 中位数 | `=MEDIAN('源表'!B2:B1000)` |
| 标准差 | `=STDEV('源表'!B2:B1000)` |
| 最大值 | `=MAX('源表'!B2:B1000)` |
| 最小值 | `=MIN('源表'!B2:B1000)` |
| 计数 | `=COUNTA('源表'!B2:B1000)` |
| 空值数 | `=COUNTBLANK('源表'!B2:B1000)` |

### 趋势与对比分析

```javascript
// 环比增长率
ws.Range("C" + r).Formula = "=('" + srcName + "'!B" + r + "-'" + srcName + "'!B" + (r-1) + ")/'" + srcName + "'!B" + (r-1);
ws.Range("C" + r).NumberFormat = "0.0%";

// 占比计算
ws.Range("D" + r).Formula = "='" + srcName + "'!B" + r + "/SUM('" + srcName + "'!B2:B" + lastRow + ")";
ws.Range("D" + r).NumberFormat = "0.0%";

Pivot Summary (Manually Implemented)

The PivotTable API is not supported in WPS add-ins, so aggregation must be done manually:

// 按分类列汇总
var groups = {};
for (var r = 2; r <= lastRow; r++) {
  var cat = String(ws.Range(CL(catCol) + r).Value2);
  var val = Number(ws.Range(CL(valCol) + r).Value2) || 0;
  if (!groups[cat]) groups[cat] = { sum: 0, count: 0 };
  groups[cat].sum += val;
  groups[cat].count++;
}
// 写入汇总表
var keys = Object.keys(groups);
for (var i = 0; i < keys.length; i++) {
  ws.Range("A" + (startRow + i)).Value2 = keys[i];
  ws.Range("B" + (startRow + i)).Value2 = groups[keys[i]].sum;
  ws.Range("C" + (startRow + i)).Value2 = groups[keys[i]].count;
  ws.Range("D" + (startRow + i)).Value2 = groups[keys[i]].sum / groups[keys[i]].count;
}

Analysis Conclusion Output

At the bottom of the analysis sheet, write the key findings in text form:

var conclusionRow = lastOutputRow + 2;
ws.Range("A" + conclusionRow).Value2 = "分析结论";
ws.Range("A" + conclusionRow).Font.Bold = true;
ws.Range("A" + conclusionRow).Font.Size = 14;
// 结论内容由 AI 根据计算结果生成
ws.Range("A" + (conclusionRow + 1)).Value2 = "1. 总销售额为 XXX 万元，环比增长 XX%";
ws.Range("A" + (conclusionRow + 2)).Value2 = "2. TOP3 品类占总额的 XX%";