使用 Python 自动化 Excel 公式和函数：完整指南

在日常数据处理工作里，Excel 公式和函数无疑是最核心的计算工具，但手动一条条去输、去管理，既费时间又容易出岔子。要是能用 Python 来帮我们批量插入和管理公式，那就能做到自动化计算逻辑的动态生成，还能确保数据处理的准确性。这篇内容就来聊聊，怎么用 Python 往 Excel 工作表中塞进各种公式和函数——不管是基础算术运算，还是内置函数、数组公式、命名范围，都一并覆盖。这些方法在实际的财务报表自动化、数据分析管道搭建、批量数据处理等等场景里，都非常实用。

环境准备

开工前，先把依赖库装好：

 复制代码pip install Spire.XLS

这个库提供了完整的 Excel 文件操作 API，支持公式插入、计算执行和文件格式转换，基本搞定了我们需要的所有能力。

插入基本公式

Excel 里公式长什么样？以等号（=）开头，后面跟着常量、单元格引用、运算符和函数调用。用 Python 插入公式时，其实只需要把公式的字符串赋值给单元格的 Formula 属性就行。

下面的例子展示了如何插入各种基础公式：

 复制代码from spire.xls import *
from spire.xls.common import *# 创建工作簿
workbook = Workbook()
sheet = workbook.Worksheets[0]# 设置测试数据
sheet.Range["B2"].NumberValue = 7.3
sheet.Range["C2"].NumberValue = 5
sheet.Range["D2"].NumberValue = 8.2# 插入字符串公式
sheet.Range["A4"].Text = '="hello"'
sheet.Range["B4"].Formula = '="hello"'# 插入算术运算公式
sheet.Range["A5"].Text = "=1+2+3+4+5-6-7+8-9"
sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"# 插入乘法运算
sheet.Range["A6"].Text = "=33*3/4-2+10"
sheet.Range["B6"].Formula = "=33*3/4-2+10"# 引用其他单元格
sheet.Range["A7"].Text = "=Sheet1!$B$2"
sheet.Range["B7"].Formula = "=Sheet1!$B$2"# 引用单元格区域并计算平均值
sheet.Range["A8"].Text = "=A VERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=A VERAGE(Sheet1!$B$2:$D$2)"# 保存文件
workbook.Sa veToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

在这个示例里，其实展示了几种常用的方式：

• 字符串常量：用双引号把文本包起来就行
• 算术运算：加减乘除和括号优先级，都支持
• 单元格引用：加个 $ 符号就成了绝对引用
• 区域引用：冒号指定连续单元格范围

使用内置函数

Excel 自带的几百个内置函数覆盖了数学、统计、日期时间、逻辑判断、文本处理等等领域。那么怎么用 Python 把这事儿办了？

数学和统计函数

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]currentRow = 1# COUNT 函数 - 统计参数个数
sheet.Range[f"A{currentRow}"].Text = "=Count(3,5,8,10,2,34)"
sheet.Range[f"B{currentRow}"].Formula = "=Count(3,5,8,10,2,34)"
currentRow += 1# SUM 函数 - 求和
sheet.Range[f"A{currentRow}"].Text = "=SUM(18,29)"
sheet.Range[f"B{currentRow}"].Formula = "=SUM(18,29)"
currentRow += 1# A VERAGE 函数 - 平均值
sheet.Range[f"A{currentRow}"].Text = "=A VERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=A VERAGE(12,45)"
currentRow += 1# MAX 和 MIN 函数
sheet.Range[f"A{currentRow}"].Text = "=MAX(10,30)"
sheet.Range[f"B{currentRow}"].Formula = "=MAX(10,30)"
currentRow += 1sheet.Range[f"A{currentRow}"].Text = "=MIN(5,7)"
sheet.Range[f"B{currentRow}"].Formula = "=MIN(5,7)"
currentRow += 1# ROUND 函数 - 四舍五入
sheet.Range[f"A{currentRow}"].Text = "=ROUND(7,3)"
sheet.Range[f"B{currentRow}"].Formula = "=ROUND(7,3)"
currentRow += 1# SQRT 函数 - 平方根
sheet.Range[f"A{currentRow}"].Text = "=SQRT(40)"
sheet.Range[f"B{currentRow}"].Formula = "=SQRT(40)"
currentRow += 1workbook.Sa veToFile("MathFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

日期和时间函数

处理日期和时间是 Excel 自动化里很常见的需求。下面的代码演示了怎么用日期时间相关的函数：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# NOW 函数 - 当前日期时间
sheet.Range["A1"].Text = "=NOW()"
sheet.Range["B1"].Formula = "=NOW()"
sheet.Range["B1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"# DATE 函数 - 构造日期
sheet.Range["A2"].Text = "=DATE(2024,1,15)"
sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"# TIME 函数 - 构造时间
sheet.Range["A3"].Text = "=TIME(14,30,0)"
sheet.Range["B3"].Formula = "=TIME(14,30,0)"
sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"# YEAR、MONTH、DAY 函数 - 提取日期部分
sheet.Range["A4"].Text = "=YEAR(NOW())"
sheet.Range["B4"].Formula = "=YEAR(NOW())"sheet.Range["A5"].Text = "=MONTH(NOW())"
sheet.Range["B5"].Formula = "=MONTH(NOW())"sheet.Range["A6"].Text = "=DAY(NOW())"
sheet.Range["B6"].Formula = "=DAY(NOW())"# HOUR、MINUTE、SECOND 函数 - 提取时间部分
sheet.Range["A7"].Text = "=HOUR(NOW())"
sheet.Range["B7"].Formula = "=HOUR(NOW())"sheet.Range["A8"].Text = "=MINUTE(NOW())"
sheet.Range["B8"].Formula = "=MINUTE(NOW())"sheet.Range["A9"].Text = "=SECOND(NOW())"
sheet.Range["B9"].Formula = "=SECOND(NOW())"workbook.Sa veToFile("DateTimeFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

逻辑和条件函数

逻辑函数用来做条件判断和执行业务规则，很实用：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# IF 函数 - 条件判断
sheet.Range["A1"].Text = "=IF(10>5, "大于", "小于")"
sheet.Range["B1"].Formula = "=IF(10>5, "大于", "小于")"# AND 函数 - 逻辑与
sheet.Range["A2"].Text = "=AND(TRUE, TRUE)"
sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"# OR 函数 - 逻辑或
sheet.Range["A3"].Text = "=OR(FALSE, TRUE)"
sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"# NOT 函数 - 逻辑非
sheet.Range["A4"].Text = "=NOT(FALSE)"
sheet.Range["B4"].Formula = "=NOT(FALSE)"workbook.Sa veToFile("LogicFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

文本处理函数

文本函数在字符串操作和数据清洗上很有一手：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# LEN 函数 - 字符串长度
sheet.Range["A1"].Text = '=LEN("Hello World")'
sheet.Range["B1"].Formula = '=LEN("Hello World")'# MID 函数 - 提取子字符串
sheet.Range["A2"].Text = '=MID("Hello World",7,5)'
sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'# VALUE 函数 - 文本转数值
sheet.Range["A3"].Text = '=VALUE("123")'
sheet.Range["B3"].Formula = '=VALUE("123")'workbook.Sa veToFile("TextFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

使用数组公式

数组公式可以对多个值执行计算并返回单个或多个结果，在做矩阵运算和批量数据处理时尤其好用。

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# 准备数据
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["A3"].NumberValue = 3sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6sheet.Range["C1"].NumberValue = 7
sheet.Range["C2"].NumberValue = 8
sheet.Range["C3"].NumberValue = 9# 插入数组公式 - LINEST 函数执行线性回归分析
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"# 计算所有公式的值
workbook.CalculateAllValue()workbook.Sa veToFile("ArrayFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

这里有几个关键点要记住：

• 插入数组公式用的是 FormulaArray 属性，不是 Formula
• 数组公式通常需要跨越多个单元格区域
• 别忘了调用 CalculateAllValue() 方法，这样才能确保公式被正确计算出来

使用 SUBTOTAL 函数

SUBTOTAL 函数可以对数据列表做聚合计算，而且能自动忽略隐藏行——这在做交互式报表时特别有用。

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# 准备数据
for row in range(1, 4):
    sheet.Range[f"A{row}"].NumberValue = row
    sheet.Range[f"B{row}"].NumberValue = row + 3
    sheet.Range[f"C{row}"].NumberValue = row + 6# 插入不同的 SUBTOTAL 函数
# 1 表示 A VERAGE
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"# 2 表示 COUNT
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"# 5 表示 MIN
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"# 计算公式
workbook.CalculateAllValue()workbook.Sa veToFile("SubtotalFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

SUBTOTAL 函数的第一个参数决定了计算类型：

• 1: A VERAGE（平均值）
• 2: COUNT（计数）
• 3: COUNTA（非空计数）
• 4: MAX（最大值）
• 5: MIN（最小值）
• 9: SUM（求和）

使用命名范围和公式

命名范围这个功能，最直观的好处就是让公式变得好读又好维护。把复杂的单元格引用替换成有意义的名称，公式一看就懂。

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# 设置数据
sheet.Range["A1"].Value = "10"
sheet.Range["A2"].Value = "20"# 创建命名范围
namedRange = workbook.NameRanges.Add("SumRange")
namedRange.RefersToRange = sheet.Range["A1:A2"]# 在公式中使用命名范围
sheet.Range["C1"].Formula = "=SUM(SumRange)"# 也可以直接定义命名公式
namedFormula = workbook.NameRanges.Add("TotalCalc")
namedFormula.NameLocal = "=SUM(A1+A2)"
sheet.Range["C2"].Formula = "TotalCalc"workbook.Sa veToFile("NamedRangeFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

这么做的优势很明显：

• 提高可读性：=SUM(SumRange) 比 =SUM(A1:A2) 清晰得多
• 便于维护：改了命名范围的引用，所有用到了这个名称的公式都会自动更新
• 减少错误：不用再手动去输入那些复杂的单元格引用了

实用技巧

格式化公式单元格

要把公式单元格和数据单元格区分出来，可以给它们套上特殊的格式：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]# 插入公式
sheet.Range["A1"].Formula = "=SUM(B1:B10)"# 设置公式单元格背景色
sheet.Range["A1"].Style.Color = Color.get_LightYellow()# 添加边框
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thinworkbook.Sa veToFile("FormattedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

强制计算公式值

有些场景下，我们可能需要立马拿到计算结果，而不想保留公式本身：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()
sheet = workbook.Worksheets[0]sheet.Range["A1"].NumberValue = 10
sheet.Range["A2"].NumberValue = 20
sheet.Range["A3"].Formula = "=A1+A2"# 计算所有公式
workbook.CalculateAllValue()# 此时 A3 的值已经是 30
result = sheet.Range["A3"].Value
print(f"计算结果: {result}")workbook.Sa veToFile("CalculatedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

处理跨工作表引用

如果公式要引用其他工作表中的数据，语法就是用工作表名称加感叹号：

 复制代码from spire.xls import *
from spire.xls.common import *workbook = Workbook()# 创建两个工作表
sheet1 = workbook.Worksheets[0]
sheet1.Name = "Data"
sheet2 = workbook.Worksheets.Add("Calculation")# 在第一个工作表中设置数据
sheet1.Range["A1"].NumberValue = 100
sheet1.Range["A2"].NumberValue = 200# 在第二个工作表中引用第一个工作表的数据
sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"workbook.CalculateAllValue()
workbook.Sa veToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

总结

这篇文章梳理了用 Python 在 Excel 里自动化公式和函数的几种常用手法，包括：

• 插入基本算术公式和单元格引用
• 使用数学、统计、日期时间、逻辑和文本处理函数
• 用数组公式做复杂计算
• 利用 SUBTOTAL 函数搞灵活的聚合运算
• 借助命名范围让公式更好读、更好维护

掌握了这些技术，开发者就能：

• 批量生成带有复杂计算逻辑的电子表格
• 自动化财务模型和数据分析报告
• 动态构建基于条件的计算公式
• 大幅提升数据处理的一致性和准确性

把 Python 的编程能力和 Excel 的计算功能结合起来，确实能搭建出很强大的数据处理自动化方案——效率提上去了，人为错误也降下来了。可以说，这才是现代数据工作里值得投入的方向。