网站首页 编程技术 编程代码 正文

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

2026-07-11编程代码131369

本文将介绍如何使用 Python 在 Excel 工作表中插入各种类型的公式和函数,包括基本算术运算、内置函数、数组公式以及命名范围的使用,这些技术适用于财务报表自动化、数据分析管道构建以及批量数据处理等场景,有需要的可以了解下

目录
  • 环境准备
  • 插入基本公式
  • 使用内置函数
    • 数学和统计函数
    • 日期和时间函数
    • 逻辑和条件函数
    • 文本处理函数
  • 使用数组公式
    • 使用 SUBTOTAL 函数
      • 使用命名范围和公式
        • 实用技巧
          • 格式化公式单元格
          • 强制计算公式值
          • 处理跨工作表引用
        • 总结

          在现代数据处理工作中，Excel 公式和函数是执行计算、数据分析和业务逻辑的核心工具。然而，手动输入和管理大量公式不仅耗时，还容易出错。通过 Python 自动化 Excel 公式的插入和管理，开发者可以批量处理电子表格、动态生成计算逻辑，并确保数据处理的准确性和一致性。

          本文将介绍如何使用 Python 在 Excel 工作表中插入各种类型的公式和函数，包括基本算术运算、内置函数、数组公式以及命名范围的使用。这些技术适用于财务报表自动化、数据分析管道构建以及批量数据处理等场景。

          环境准备

          首先，需要安装 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 = "=AVERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"

# 保存文件
workbook.SaveToFile("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

# AVERAGE 函数 - 平均值
sheet.Range[f"A{currentRow}"].Text = "=AVERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=AVERAGE(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.SaveToFile("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.SaveToFile("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.SaveToFile("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.SaveToFile("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 = 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.SaveToFile("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 表示 AVERAGE
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.SaveToFile("SubtotalFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

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

          • 1: AVERAGE（平均值）
          • 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.SaveToFile("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.SaveToFile("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.SaveToFile("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.SaveToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

          总结

          本文介绍了使用 Python 在 Excel 中自动化公式和函数的多种技术，包括：

          • 插入基本算术公式和单元格引用
          • 使用数学、统计、日期时间、逻辑和文本处理函数
          • 执行数组公式进行复杂计算
          • 利用 SUBTOTAL 函数创建灵活的聚合计算
          • 通过命名范围提高公式的可读性和可维护性

          这些技术使开发者能够：

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

          通过结合 Python 的编程能力和 Excel 的计算功能，可以构建强大的数据处理自动化解决方案，显著提升工作效率并减少人为错误。

          以上就是Python Excel自动化插入各种类型的公式和函数的完整指南的详细内容，更多关于Python Excel插入公式和函数的资料请关注北冥有鱼【www.ufcn.cn】其它相关文章！

          您可能感兴趣的文章:

          • 从基础公式到动态函数生成详解Python操作Excel的完整指南
          • 使用Python删除Excel单元格公式并保留数值
          • Python实现读取、修改和计算 Excel 公式的示例详解
          • Python使用openpyxl设置Excel单元格公式和工作簿合并
          • 使用Python实现自动化移除Excel公式并保留纯净数值
          • 利用Python实现添加或读取Excel公式
          • Python保留数据并删除Excel单元格的函数和公式
          本文地址：https://www.ufcn.cn/article/2432496.html

          如非特殊说明，本站内容均来自于网友自主分享，概不代表本站观点，如有任何问题我们都将在收到反馈后的第一时间进行处理！