Python 操作 Excel：创建数组公式与 R1C1 公式


大多数公式一次只处理一个单元格：=A1+A2 引用两个坐标，写在另一个坐标上。数组公式不同——它把一整块区域当作输入，把结果写进一整块输出区域。线性回归的 LINEST、跨区域条件求和这类计算，用普通公式要拆成很多步，用数组公式一步就能完成。

R1C1 引用是另一把钥匙。"R1C1" 的意思是"第 1 行第 1 列"，公式里的区域不再是绝对坐标，而是相对于当前单元格的偏移量（如 R[-3]C[-2] 表示"往上 3 行、往左 2 列"）。把公式复制到一片区域时，这种相对描述让每个单元格都能算对，非常适合批量生成公式。这篇文章用 Spire.XLS for Python 演示两类公式的写法和读取方法。

环境准备

安装 Spire.XLS：

pip install Spire.XLS

脚本中引入模块：

from spire.xls import *
from spire.xls.common import *

用 FormulaArray 写入数组公式

数组公式写入的是一个区域而不是单个单元格。先填入一组测试数据（3 行 × 3 列），然后把 LINEST 回归函数写入一个 2×3 的结果区域：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# 写入测试数据
data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# 将数组公式写入 A5:C6 区域
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# 计算全部公式并保存
workbook.CalculateAllValue()
workbook.SaveToFile("UseArrayFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

输出结果文档：

几个要点：

• Range.FormulaArray 赋值时，区域的大小应当与公式的返回矩阵一致。LINEST 在两个自变量的四参数形式下返回 5 行 × 3 列的结果；上面的演示为了简短只取了 A5:C6 这 2 行×3 列，写入后 Excel 显示的会是完整结果矩阵的前几行。
• 写入公式后调用 workbook.CalculateAllValue()，让 Spire.XLS 立即计算一遍。不调用的话，结果要等 Excel 打开文件重算后才出现。

这个模式对所有返回矩阵的函数都适用：把输出区域整体选中，一条语句写入，一次计算完成。

用 R1C1 偏移量写数组公式

FormulaArray 使用的是常见的 A1 坐标；对应的 FormulaArrayR1C1 使用 R1C1 相对引用。下面的例子在 C4 计算上方 3 行 × 左侧 3 列区域的和：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# 标签靠右对齐，放在求和旁边
sheet.Range["B4"].Text = "Sum:"
sheet.Range["B4"].Style.HorizontalAlignment = HorizontalAlignType.Right

# R1C1 数组公式：从 C4 往上 3 行、往左 2 列，覆盖到当前列
sheet.Range["C4"].FormulaArrayR1C1 = "=SUM(R[-3]C[-2]:R[-1]C)"

workbook.CalculateAllValue()
workbook.SaveToFile("UseArrayR1C1Formula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

结果文档：

R[-3]C[-2] 读作"从当前单元格向上 3 行、向左 2 列"，也就是 A1；R[-1]C 读作"向上 1 行、同一列"，也就是 C3。负号表示向上/向左，不带负号的绝对形式如 R1C1 表示固定的第一行第一列。这种写法的价值在于：公式与它所在的绝对位置无关，把同一条语句写进循环、应用到整列的每一格，偏移量之间的关系保持不变——这正是批量生成公式时最想要的性质。

同样的偏移思想也用于普通公式：Range 对象上的 FormulaR1C1 接受 R1C1 风格的引用，用法与 Formula 一致，只是坐标表示方式不同。

循环中批量生成公式

把 R1C1 的相对引用放进循环，就不必为每一行手写不同的公式。下面的例子在每一行数据右侧的 D 列写入"本行求和"，并把公式文本记录在 E 列以便核对：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# 每行生成一个相对引用公式
for r in range(1, 4):
    sheet.Range[r, 4].FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    # 把同样的公式文本以纯文本形式记录到右侧
    sheet.Range[r, 5].NumberFormat = "@"
    sheet.Range[r, 5].Text = "=SUM(RC[-3]:RC[-1])"

workbook.CalculateAllValue()
workbook.SaveToFile("BatchR1C1.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

结果文档：

D 列每格的公式都是 =SUM(RC[-3]:RC[-1])——对 D 列而言，就是"同一行里，从左边第三格到左边第一格"，即 A 到 C。三行写入的是同一条字符串，计算时却各自落在本行的 A:C 上（结果分别为 12、15、18）；换成 A1 风格，每行的公式文本都得改一次。旁边的 E 列用 NumberFormat = "@"（文本格式）把同样的公式字符串以文本形式记录下来，方便打开文件后核对每格的实际引用。

使用 SUBTOTAL 做分区域统计

数组公式之外，SUBTOTAL 是处理表格区域的常用函数。第 1 到 11 号数字各自对应一种统计方式：1 为平均值、2 为计数、4 为最大值、5 为最小值、9 为求和。它的特点是会自动忽略被筛选器隐藏的行，所以适合放在数据区末尾做汇总：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# 1=平均，2=计数，5=最小值
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

workbook.CalculateAllValue()
workbook.SaveToFile("SubTotalFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

结果文档：

读取公式和计算结果

公式生成后往往还要在程序里核对。Range.Formula 返回公式字符串，FormulaNumberValue 返回计算后的数值：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("UseArrayR1C1Formula.xlsx")
sheet = workbook.Worksheets[0]

# C4 的公式会以 A1 风格返回
cell = sheet.Range["C4"]
print("Formula:", cell.Formula)
print("Value:", cell.HasNumber and cell.FormulaNumberValue)

workbook.Dispose()

一个值得注意的行为：文件里存的是 R1C1 数组公式，Formula 属性读回来时会转换成 A1 风格的等效形式。也就是说，写入时的表示方式和读出时的表示方式可以不同，做两项核对时要以转换后的结果为准。

实用技巧

• 区域大小要匹配：FormulaArray 的输出区域行列数应与公式返回的矩阵一致，否则结果被截断或位置错开。
• 先写数据、再写公式、最后 CalculateAllValue：这个顺序保证计算时引用的单元格已经有值。
• 批量生成用 R1C1：把一条 FormulaR1C1 / FormulaArrayR1C1 语句放进循环，比逐格拼接 A1 坐标字符串更不容易出错。
• 核对时注意表示方式转换：R1C1 写入的公式读出来是 A1 风格，做自动化校验时不要直接做字符串比较。
• 及时释放资源：每个工作簿用完调用 Dispose()。

结论

这篇文章演示了用 Python 给 Excel 写入数组公式和 R1C1 公式：FormulaArray 把矩阵函数（如 LINEST）的结果一次性写入整块区域，FormulaArrayR1C1 用相对偏移描述跨区域计算，FormulaR1C1 配合循环可以批量生成相对引用公式，SUBTOTAL 覆盖表格末端的汇总需求，Formula 与 FormulaNumberValue 负责核对写入的结果。把"数据、公式、计算、保存"四个步骤按顺序组织起来，这类公式就能稳定地出现在自动化生成的报告里。