formula-operations

Formula writing conventions

Core principle: use .Formula to write formulas, not .Value2 to write calculation results

// ✅ 正确：写入公式，Excel 自动计算
ws.Range("D2").Formula = "=B2*C2";
ws.Range("D20").Formula = "=SUM(D2:D19)";

// ❌ 错误：用 JS 算好再写入值（用户无法调参）
ws.Range("D2").Value2 = b2 * c2;

Formula fill-down (batch writing)

// 方法1: 逐行写入（最可靠）
for (var r = 2; r <= lastRow; r++) {
  ws.Range("D" + r).Formula = "=B" + r + "*C" + r;
}

// 方法2: 首行写入 + AutoFill
ws.Range("D2").Formula = "=B2*C2";
ws.Range("D2").AutoFill(ws.Range("D2:D" + lastRow));

Common formula templates

Scenario	Formula
Sum	=SUM(B2:B100)
Conditional sum	=SUMIF(A2:A100,"条件",B2:B100)
Multi-condition sum	=SUMIFS(C2:C100,A2:A100,"条件1",B2:B100,">0")
Count	=COUNTA(A2:A100)
Conditional count	=COUNTIF(A2:A100,"条件")
Average	=AVERAGE(B2:B100)
Lookup match	=VLOOKUP(查找值,表区域,列号,0)
Flexible lookup	=INDEX(返回区域,MATCH(查找值,查找区域,0))
Conditional evaluation	=IF(条件,"是","否")
Nested evaluation	=IF(A2>90,"优",IF(A2>60,"及格","不及格"))
Percentage	=B2/SUM(B$2:B$100)
Period-over-period growth	=(B3-B2)/B2
Ranking	=RANK(B2,B$2:B$100)
Text concatenation	=A2&"-"&B2

Formula error correction methods

// 读取公式内容（不是值）
var formula = ws.Range("D2").Formula;

// 常见错误及修复思路
// #VALUE! → 数据类型不匹配，检查是否文本混在数字中
// #REF!   → 引用了被删除的单元格
// #NAME?  → 函数名拼写错误或不存在
// #DIV/0! → 除数为零，用 =IF(B2=0,0,A2/B2)
// #N/A    → VLOOKUP 找不到值，用 =IFERROR(VLOOKUP(...),"未找到")

WPS-specific notes

• In WPS, .FormulaR1C1 may be unstable; prefer .Formula(A1 format)
• Array formula {=...} has limited support in WPS add-ins; prefer splitting with helper columns
• Cross-worksheet reference format:='表名'!A1
• Chinese quotation marks in formulas will cause errors; make sure to use English double quotation marks