Python Excel自动化插入各种类型的公式和函数的完整指南

Excel 公式和函数，说白了就是数据处理的“灵魂”。手动往单元格里敲公式，一来效率低，二来容易敲错，尤其是遇到大批量报表或者动态计算场景，简直让人头大。如果用 Python 来接管公式的插入和管理，就可以一次性搞定批量处理、动态生成逻辑，还能保证数据加工的一致性。这对于日常的财务报表自动化、数据分析管道搭建，甚至是批量的数据清洗任务，都非常实用。

这篇文章会详细演示如何用 Python 在 Excel 里插入各种公式和函数，从最基础的四则运算，到内置函数（数学、统计、日期、逻辑、文本），再到数组公式、SUBTOTAL 聚合计算，以及用命名范围来提升公式的可读性。全程代码可直接跑，适合直接拿去做自动化工具。

环境准备

先装一个库：Spire.XLS for Python。安装命令很简单：

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()

这里涉及几个要点：

• 字符串常量：用双引号包裹文本，像 ="hello"。
• 算术运算：加减乘除、括号优先级都支持，跟手动写公式一样。
• 单元格引用：用 $ 表示绝对引用，例如 $B$2。
• 区域引用：用冒号 : 指定连续范围，比如 $B$2:$D$2。

使用内置函数

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 += 1

sheet.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 += 1

workbook.Sa veToFile("MathFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

代码里用 currentRow 变量动态控制行号，这样批量生成的时候更灵活。

日期和时间函数

日期处理是自动化报表的常见痛点。下面的代码演示了如何插入 NOW、DATE、TIME 以及提取年、月、日、时、分、秒等函数：

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()

注意这里对 B1 等单元格设置了 NumberFormat，这样才能让日期时间以我们想要的格式显示出来，否则可能是一串数字。

逻辑和条件函数

逻辑函数用来实现条件判断，比如 IF、AND、OR、NOT，是业务规则自动化的基础：

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()

使用数组公式

数组公式能同时对多个值执行计算，返回单个结果或多个结果。例如，线性回归分析 LINEST 就需要用数组公式。写法上改用 FormulaArray 属性，而不是 Formula。

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 = 3

sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6

sheet.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。
• 数组公式通常要占多个单元格区域（比如 A5:C6）。
• 调用 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.Thin

workbook.Sa veToFile("FormattedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

强制计算公式值

有些场景下，我们不想要公式本身，只想要计算后的结果（比如把值传给后续流程）。这时可以先计算，再读取 Value 属性：

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()

注意工作表名里如果有空格，需要用单引号括起来，比如 ='Data Sheet'!A1。

总结

整个流程走下来，可以看到用 Python 操作 Excel 公式其实非常直接——核心思路就是把公式写成字符串，交给 Formula 或 FormulaArray 属性，然后让库去解析和执行。具体来说：

• 插入基本算术公式和单元格引用，适用于简单的数值计算。
• 内置函数覆盖了数学、统计、日期、逻辑和文本处理，能满足绝大多数数据处理需求。
• 数组公式可以执行矩阵运算和高级统计（如线性回归）。
• SUBTOTAL 函数在筛选后仍然能正确聚合，适合做动态报表。
• 命名范围让公式更可读、更易维护，尤其是在大型工作簿中。

这些技术能帮你做到：

• 批量生成带复杂计算逻辑的 Excel 文件，不需要手工逐行写公式。
• 自动化财务模型、分析报告，减少人工出错的可能。
• 动态构建基于条件的公式，根据数据变化自动调整计算逻辑。
• 整体提升数据处理的效率和一致性。

把 Python 的编程能力和 Excel 的计算引擎结合起来，就是一套非常实用的数据处理自动化方案。希望这些代码能直接应用到你的项目里，少加班，多摸鱼。