Python Excel自动化插入公式和函数的完整实践指南

脚本专家

在现代数据处理工作中，Excel公式与函数是执行计算、数据分析和业务逻辑的核心工具。手动输入和管理大量公式不仅耗时，且易出错。通过Python自动化插入公式，开发者可批量处理电子表格、动态生成计算逻辑，确保数据处理的准确性与一致性。本文将基于Spire.XLS for Python库，详细介绍如何在工作表中插入各类公式与函数，包括基本运算、内置函数、数组公式、SUBTOTAL、命名范围及实用技巧。

2. pip install Spire.XLS

复制代码

该库提供完整的Excel操作API，支持公式插入、计算执行与文件格式转换。

## 插入基本公式

Excel公式以等号（=）开头，可包含常量、单元格引用、运算符与函数调用。将公式字符串赋值给单元格的Formula属性即可。

2. from spire.xls import *
3. from spire.xls.common import *
5. workbook = Workbook()
6. sheet = workbook.Worksheets[0]
8. # 设置测试数据
9. sheet.Range["B2"].NumberValue = 7.3
10. sheet.Range["C2"].NumberValue = 5
11. sheet.Range["D2"].NumberValue = 8.2
13. # 字符串常量公式
14. sheet.Range["B4"].Formula = '="hello"'
15. # 算术运算
16. sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"
17. # 乘法与除法
18. sheet.Range["B6"].Formula = "=33*3/4-2+10"
19. # 绝对引用其他单元格
20. sheet.Range["B7"].Formula = "=Sheet1!$B$2"
21. # 引用区域并计算平均值
22. sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"
24. workbook.SaveToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
25. workbook.Dispose()

复制代码

关键点：字符串常量用双引号；支持加减乘除与括号优先级；使用$创建绝对引用；冒号指定连续区域。

## 使用内置函数

### 数学与统计函数

2. sheet.Range["B1"].Formula = "=Count(3,5,8,10,2,34)"  # 统计参数个数
3. sheet.Range["B2"].Formula = "=SUM(18,29)"                # 求和
4. sheet.Range["B3"].Formula = "=AVERAGE(12,45)"            # 平均值
5. sheet.Range["B4"].Formula = "=MAX(10,30)"               # 最大值
6. sheet.Range["B5"].Formula = "=MIN(5,7)"                 # 最小值
7. sheet.Range["B6"].Formula = "=ROUND(7,3)"               # 四舍五入
8. sheet.Range["B7"].Formula = "=SQRT(40)"                 # 平方根

复制代码

### 日期与时间函数

2. sheet.Range["B1"].Formula = "=NOW()"
3. sheet.Range["B1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"
4. sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
5. sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"
6. sheet.Range["B3"].Formula = "=TIME(14,30,0)"
7. sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"
8. sheet.Range["B4"].Formula = "=YEAR(NOW())"
9. sheet.Range["B5"].Formula = "=MONTH(NOW())"
10. sheet.Range["B6"].Formula = "=DAY(NOW())"
11. sheet.Range["B7"].Formula = "=HOUR(NOW())"
12. sheet.Range["B8"].Formula = "=MINUTE(NOW())"
13. sheet.Range["B9"].Formula = "=SECOND(NOW())"

复制代码

### 逻辑与条件函数

2. sheet.Range["B1"].Formula = '=IF(10>5, "大于", "小于")'
3. sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"
4. sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"
5. sheet.Range["B4"].Formula = "=NOT(FALSE)"

复制代码

### 文本处理函数

2. sheet.Range["B1"].Formula = '=LEN("Hello World")'
3. sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'
4. sheet.Range["B3"].Formula = '=VALUE("123")'

复制代码

## 数组公式

数组公式可对多值执行计算，返回单个或多个结果。使用FormulaArray属性替代Formula，并调用CalculateAllValue()确保正确计算。

2. sheet.Range["A1"].NumberValue = 1
3. sheet.Range["A2"].NumberValue = 2
4. sheet.Range["A3"].NumberValue = 3
5. sheet.Range["B1"].NumberValue = 4
6. sheet.Range["B2"].NumberValue = 5
7. sheet.Range["B3"].NumberValue = 6
8. sheet.Range["C1"].NumberValue = 7
9. sheet.Range["C2"].NumberValue = 8
10. sheet.Range["C3"].NumberValue = 9
12. # 线性回归分析（数组公式）
13. sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"
14. workbook.CalculateAllValue()

复制代码

## SUBTOTAL函数

SUBTOTAL支持聚合计算且忽略隐藏行，第一个参数指定类型：1=AVERAGE、2=COUNT、5=MIN、9=SUM等。

2. for row in range(1, 4):
3.     sheet.Range[f"A{row}"].NumberValue = row
4.     sheet.Range[f"B{row}"].NumberValue = row + 3
5.     sheet.Range[f"C{row}"].NumberValue = row + 6
7. sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"   # 平均值
8. sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"   # 计数
9. sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"   # 最小值
10. workbook.CalculateAllValue()

复制代码

## 命名范围与公式

命名范围可提高公式可读性与可维护性。

2. sheet.Range["A1"].Value = "10"
3. sheet.Range["A2"].Value = "20"
5. namedRange = workbook.NameRanges.Add("SumRange")
6. namedRange.RefersToRange = sheet.Range["A1:A2"]
7. sheet.Range["C1"].Formula = "=SUM(SumRange)"
9. # 也可定义命名公式
10. namedFormula = workbook.NameRanges.Add("TotalCalc")
11. namedFormula.NameLocal = "=SUM(A1+A2)"
12. sheet.Range["C2"].Formula = "TotalCalc"

复制代码

## 实用技巧

### 格式化公式单元格

2. sheet.Range["A1"].Formula = "=SUM(B1:B10)"
3. sheet.Range["A1"].Style.Color = Color.get_LightYellow()
4. sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
5. sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

复制代码

### 强制计算公式值

2. sheet.Range["A1"].NumberValue = 10
3. sheet.Range["A2"].NumberValue = 20
4. sheet.Range["A3"].Formula = "=A1+A2"
5. workbook.CalculateAllValue()
6. result = sheet.Range["A3"].Value  # 此时为30
7. print(f"计算结果: {result}")

复制代码

### 跨工作表引用

使用工作表名称加感叹号语法：

2. sheet1 = workbook.Worksheets[0]
3. sheet1.Name = "Data"
4. sheet2 = workbook.Worksheets.Add("Calculation")
5. sheet1.Range["A1"].NumberValue = 100
6. sheet1.Range["A2"].NumberValue = 200
7. sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"
8. workbook.CalculateAllValue()

复制代码

## 总结

本文覆盖了Python自动化Excel公式插入的核心场景：基本算术、各类内置函数、数组公式、SUBTOTAL、命名范围及实用技巧。开发者可借此批量生成复杂计算电子表格、自动化财务模型与数据分析报告，提高数据处理准确性与效率。

热心网友6

感谢楼主的详细分享！这个实践指南对于经常用Excel做数据处理但又想提升效率的人来说太实用了。Spire.XLS的API看起来挺直观的，公式字符串直接赋给Formula属性，操作很简单。 另外，想请教一下：您示例里用的这个库是收费的吗？除了它，还有没有其他免费的开源库（比如openpyxl）也能实现类似的公式插入功能？如果方便的话，可以对比一下吗？再次感谢！

热心网友6

感谢楼主分享这么详细的实践指南！Spire.XLS 这个库我之前没怎么留意过，看了你的示例才发现插入公式的 API 设计很直观，直接把公式字符串赋给 `Formula` 属性就能生效，兼容性看起来也不错。特别是把日期、逻辑、文本函数都列全了，日常自动化报表生成应该够用。想请教一下：如果需要在公式中动态引用不同行数的区域（比如最后一行不固定），用 Python 拼接字符串生成公式时，有没有什么推荐的写法？或者库本身支持像 `INDIRECT` 这样的函数来动态构造引用吗？

热心网友6

感谢分享这么详细的实践指南！Spire.XLS 的 API 确实挺直观，直接把公式字符串赋给 Formula 属性就能用，省了不少手动拼字符串的麻烦。之前用 openpyxl 时对公式的支持没那么直接，看了你的代码后我决定也试一下这个库。 想请教个小问题：如果插入的是像 `=SUBTOTAL(9, A1:A10)` 这种带参数编号的函数，直接写成字符串就能被 Excel 正确识别吗？另外，对于包含动态区域（比如用 `OFFSET` 或 `INDIRECT` 构造的引用）的公式，这个库有没有什么限制？期待后续关于数组公式和命名范围的补充部分！